Reference no: EM132615078

SBM4102 Data and Information Management - Asia Pacific International College

Assessment 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.

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

Assessment 3: Applied Project-2

The Trade Master company sells various tools. It has sales offices and warehouses all over Australia. The company developed a data warehouse to store information about sales, customers and products. These data are stored in the AppliedProject2 Access file provided for you.
The database consists of 6 tables:
• CompanyDim - with the sales office address, state and email
• ProductDim - product information
• WarehouseDim - warehouse locations
• CustomerDim - information about customers
• DateDim - sales dates
• SaleFacts - information about sales
Please notice that all data (customers, sales, dates, warehouses and products) are randomly generated. Table and field names are self-explanatory.
The Global Trading company wants to retrieve information from the database for reporting and data analysis.
You have to do the following tasks
1. In the MS Word document:
Analyse and describe the tables.
Describe the dimension and fact tables. (You need to remember that the database tables are organised into a fact and dimension tables, and this structure is called a database star schema).
Identify and describe primary and foreign keys.
Describe relationships and cardinalities between the tables
Draw the database ER diagram with primary keys, foreign keys, relationships and cardinalities.
Copy from the database all queries to the Word document and explain them.
2. In the SQLite Browser (Please note that you must save all queries and the project file as well!)
Open the ‘AppliedProject2.db' database and save it as a project.
Create and run the queries to create primary and foreign keys. Save the queries in the project.
Insert a new customer. The record must contain your full name as a ‘CustomerName', your
state where you currently live (VIC, NSW), your APIC email address as a ‘CustomerEmail'.

Create a new sale fact related to the new customer. You can choose any existing product, warehouse, date and sales office.
Display all customer names, their addresses and states.
Display all customer names from NSW.
Display the total amount of sales for all companies in 2018.
Display total amount of sales for each customer separately.
Display total amount of sales for each product and state separately, i.e. the output should contain 3 columns: product, state and total amount for this product and state.
Display products sold from warehouses located in QLD.
For each sale, display a customer name and a purchase date.
Display a total number of sales (NOT AMOUNT!) for each customer.
For each sales office (branch), display: branchKey, branch state, a customer name (a customer purchased in this office), a product name purchased by this customer, and a month number of the purchase.
Calculate a number (not amount !) of sales in the first quarter of 2016.
Display the following sale information: Office branchAddress, customerName, productName, WarehouseDivision, SaleDate for all offices in Victoria and between 15 June 2017 and 15 October 2017.

Your submission should consist of the following documents:
1. MS Word document containing answers to the Task 1 (DO NOT ZIP IT!)
2. The following database related files (Zip these files in one file and submit the zip file):
a. Database file
b. SQLite projectfile
c. All SQLite queries (*.sql files)

*Note: Need Only Assessment 3

Attachment:- Data and Information Management.rar