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.
SBM4102 Data And Information Management Assignment-Asia Pacific International College Australia.
Assessment 1: Database Interrogation
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:
1.The company wants to keep employees and customers in the database under one entity Person. Each Person has at least one Bank Account. The Person attributes are: p ID, p Name, p Address. The Bank Account attributes are: ba ID, ba Name, ba Type, ba Balance.
2.Each Person belongs to a category: Seller, Customer or Representative. Customer can also be Representative, i.e. he/she can provide services to customers.
3.Seller can be one of the categories: category 1 or category 2.
4.Customer can be one of two categories: private or corporate.
5.Representative can be employed as casual or on a contract.
6.Seller/Representative provide Services.
7.Seller/Representative can have more than one Customer.
8.Seller/Representative signs an Agreement with a Customer.
9.Agreement contains at least one of the Services provided by the company.
10.Agreement can be one of two types: a contract or an order.
11.Seller/Representative issues Invoice to Customer for services
12.Each Invoice contains Invoice Items.
13.Each Invoice Item is one of the Services.
SBM4102 Data And Information Management Assignment-Asia Pacific International College Australia.
Your assignment consists of two parts.
Part 1. Conceptual Model
a.Identify Entities
b.Identify entity super types and sub types and their types, inheritance, and discriminators.
c.Describe relationships between entities and their cardinalities
d.Identify entity primary keys
e.Draw a Conceptual Model Entity-Relationship diagram
Part 2. Logical Model
1.Describe data attributes and their types for each entity
2.Identify foreign keys for each entity
3.if there are any one-to-one relationships then describe them
4.Redesign the Conceptual model removing many-to-many relationships (if any)
5.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 Applied Project2 Access file provided for you.
The database consists of 6 tables:
a. Company Dim – with the sales office address, state and email
b.Product Dim – product information
c.Ware house Dim – warehouse locations
d. Customer Dim – information about customers
e.Date Dim – sales dates
f.Sale Facts – 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:
1.1. Analyse and describe the tables.
1.2. 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).
1.3. Identify and describe primary and foreign keys.
1.4. Describe relationships and cardinalities between the tables
1.5. Draw the database ER diagram with primary keys, foreign keys, relationships and cardinalities.
1.6. Copy from the database all queries to the Word document and explain them.
SBM4102 Data And Information Management Assignment-Asia Pacific International College Australia.
2.In the SQLite Browser (Please note that you must save all queries and the project file as well!)
2.1. Open the ‘Applied Project 2.db’ database and save it as a project.
2.2. Create and run the queries to create primary and foreign keys. Save the queries in the project.
2.3. Insert a new customer. The record must contain your full name as a ‘Customer Name’, your state where you currently live (VIC, NSW), your APIC email address as a ‘Customer Email’.
2.4. Create a new sale fact related to the new customer. You can choose any existing product, warehouse, date and sales office.
2.5. Display all customer names, their addresses and states.
2.6. Display all customer names from NSW.
2.7. Display the total amount of sales for all companies in 2018.
2.8. Display total amount of sales for each customer separately.
2.9. 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.
2.10. Display products sold from warehouses located in QLD.
2.11. For each sale, display a customer name and a purchase date.
2.12. Display a total number of sales (NOT AMOUNT!) for each customer.
2.13. For each sales office (branch), display: branch Key, 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.
2.14. Calculate a number (not amount !) of sales in the first quarter of 2016.
2.15. Display the following sale information: Office branch Address, customer Name, product Name, Ware house Division, Sale Date for all offices in Victoria and between 15 June 2017 and 15 October 2017.
SBM4102 Data And Information Management Assignment-Asia Pacific International College Australia.
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 project file
c. All SQLite queries (*.sql files)
ORDER Now This SBM4102 Data And Information Management Assignment And Get Instant Discount
