SBM4102 Data And Information Management Assignment 1 Answer

Unit Code and Title: SBM4102 Data and Information Management Assessment OverviewAssessment 1: Database Interrogation

Assessment Details:

Database Interrogation are practical exercises that assess students’ ability to apply theoretical learning to practical database questions. This assessment will improve student’s ability to design databases and write SQL queries.

Students will not be assessed on work that the tutor has not seen them produce in class so that attendance is required as part of this assessment. Students are required to submit the work that they have completed during the lab session. The details of the lab work and requirements are provided on the online learning system.

Marking Information: The assessments will be marked out of 100 and will be weighted 40% of the total unit mark.

Assessment 2: Applied Project-1

Assessment Details:

The Blue Mountains Financial Services company provides financial services (banking, advisory, tax consulting, auditing, insurance, wealth management etc.) to customers.

The company has contracted an outsourcing IT company to develop an Information System. The System database should satisfy the following business rules:

• The company wants to keep employees and customers in the database under one entity Person. Each Person has at least one BankAccount. The Person attributes are: pID, pName, pAddress. The BankAccount attributes are: baID, baName, baType, baBalance.
• Each Person belongs to a category: Seller, Customer or Representative. Customer can also be Representative, i.e. he/she can provide services to customers.
• Seller can be one of the categories: category 1 or category 2.
• Customer can be one of two categories: private or corporate.
• Representative can be employed as casual or on a contract.
• Seller/Representative provide Services.
• Seller/Representative can have more than one Customer.
• Seller/Representative signs an Agreement with a Customer.
• Agreement contains at least one of the Services provided by the company.
• Agreement can be one of two types: a contract or an order.
• Seller/Representative issues Invoice to Customer for services
• Each Invoice contains Invoice Items.
• Each Invoice Item is one of the Services.

Your assignment consists of two parts.

Part 1. Conceptual Model

• Identify Entities
• Identify entity supertypes and subtypes and their types, inheritance, and discriminators.
• Describe relationships between entities and their cardinalities
• Identify entity primary keys
• Draw a Conceptual Model Entity-Relationship diagram

Part 2. Logical Model

• Describe data attributes and their types for each entity
• Identify foreign keys for each entity
• if there are any one-to-one relationships then describe them
• Redesign the Conceptual model removing many-to-many relationships (if any)
• Draw a Logical Model diagram.