Objective of this Assignment:

This assignment simulates a real-world project where data comes from another system and is used to determine the design for a new database system. You will take denormalized data from extracts and take business reports (queries) and use them to create a data model and database design. You will use PowerPoint to create the data model and database designs for your solution in Oracle DBMS. You will submit a report explaining your approach, assumptions, models, and SQL used. This assignment simulates the work environment of a Canadian based company.

Pre-Assignment Instructions: 

Background

You work for a small e-commerce business. When your company first started, it used a vendor and managed its data off-site. Today they’re moving all that data on-site and will manage that data using a new database. Your first step is to create a data model then use that data model to create a database design for Oracle DBMS.

You are provided with 2 spreadsheets of data. This data came from data dumps from the existing off-site system. The data includes: (see bottom of spreadsheet tabs)

Employee data

Purchase data

Based on the database design you will populate the tables with the clean and normalized data.

You WILL split data into multiple fields and fix values to clean and normalize data.

You WILL merge multiple columns into a single column to make your design more adaptive to change.

You will also create the following queries to support the reporting your company needs to do. Here are reports and their columns.

Employee Salary Report – Employee Name, Salary, Prorated Salary, Prorated Tax, Address, Department

oCreate a TAX table with the columns Province and TaxRate to calculate the prorated tax. DO NOT include this tax table in your data model nor database designs. The TAX Table should have the following columns. Here is an example with sample data.

  

ProvinceCode

 

TaxRate

 

NL

 

8.7

 

PE

 

9.8

 

NS

 

8.79

 

NB

 

9.68

 

QC

 

15

 

ON

 

5.05

 

MB

 

10.8

 

SK

 

10.5

 

AB

 

10

 

BC

 

5.06

 

YT

 

6.4

 

NT

 

5.9

 

NU

 

4

oTo facilitate your design separate your province from the address.

oUse the start of the year date and the current date function to implement this report. Prorate salaries to determine Prorated Salary and Prorated Tax. Prorated Salary is before taxes.

Employee is Customer Report – Employee Name, Product Purchased, Product Cost, Total Purchased For All Products Per Employee

Latest Year Monthly Purchases Report – Province, Product, Jan Total Sales, Feb Total Sales, Mar Total Sales, Apr Total Sales, May Total Sales, Jun Total Sales, Jul Total Sales, Aug Total Sales, Sep Total Sales, Oct Total Sales, Nov Total Sales, Dec Total Sales, Total Sales for All Products

oAll sales are in dollars

oAll Products must be included even when none of those products were sold that month

oEach month is determined by a its own subquery

oMonths are ONLY for the latest year.

oDetermine the latest year using a SELECT statement.

Competency Tracking Report – Skill, # employees

oCount the number of employees that applies / uses a particular skill

  

Assignment Tasks: 

1.Review the entities, relationships, and patterns you covered in Modules 4, 5, 6, and 7. Apply these concepts and Crowfoot notation to develop your data model using PowerPoint or some other drawing tool. DO NOT use the ERDs produced from your database tools unless you alter it completely to fit the notation covered in this course.

2.Complete your readings from the required textbook.

3.Explore the data in the spreadsheets using the spreadsheet features.

4.Clean the data so there are no misspelled and mismatched data (different names for the same thing) for departments and products. Remove duplicates. Split the data into atomic columns. That means one field per kind of data. FirstName, LastName, Company, Address, StreetNumber, Province, PostalCode, etc.

5.Import your data from your spreadsheets into ONE database so you can analyze the data. Use queries to determine multivalues, keys, relationships and constraints. Developing this skill will help you in later modules in the course. Include which queries you use and how you use them in your approach. Use the database and tools you feel most comfortable with.

6.Create your data model based on your data analysis and the reports your business is requesting. Use the Crowfoot notation covered in your required readings. Data models have entities and functional dependencies; NOT tables and keys. Name the entities using terminology that the business would actually use. Example: EMPLOYEE and CUSTOMER; not PERSON.

• Please limit your design to what you see in the data
• Cardinalities must be correct. Avoid M:N relationships.

7.Create your database design diagram based on your data model. Database designs have tables and keys; NOT entities and functional dependencies. Tables can realize one or more entities. 

8.Create your table characteristics from what you learned in Module 7. Create a matrix that includes the following columns: (for examples refer to the required textbook)

a.Table

b.Field

c.Key

d.NULL Status

e.Data Type of column in Oracle

9.Create your Oracle database and populate it with data. You can populate this data manually, use SQL, or use the database’s export/import functions. Note that the data will need to be normalized in BCNF and 4NF whenever possible. Data in columns may be split or merged according to the needed reports and good data design practices. 

10.Create your queries for your reports. Run and confirm your results.

11.You will create a single technical document summarizing your approach, assumptions, data model, data mapping, business reports, and tables 

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.