Question
SCI would like you to design a database to replace an existing system based on pre-printed forms and spreadsheet tables. SCI would like a normalized
SCI would like you to design a database to replace an existing system based on pre-printed forms and spreadsheet tables. SCI would like a normalized database (3NF) based on the following sample forms and spreadsheet tables. There are 4 views to be normalized
REQUIREMENTS
You are to submit the following:
Normalize all views to 3NF in MS Word document
Create a SQL script with the following DDL and DML statements
All DDL statements to Create tables
All needed Constraints (Primary Keys, Foreign Keys, Not Null,)
To learn Referential Integrity Rules in practice, the following DML statements must be created per table
Minimum of 5 records per table to be inserted
Minimum of 1 record per table to be updated
Minimum of 1 record per table to be deleted
A Select Statement to retrieve all Employees (with a SALES Position) along with all the Customers that they are representing
A Select Statement to retrieve all Employees (with a SALES Position) regardless if they are representing any Customer. If Employees are representing any Customer, then show Customer information as well otherwise show Null values
A Select Statement to retrieve any Customer regardless if they have a Rep. If Customers have a Rep, then show Employees information as well otherwise show Null values.
A Select Statement to retrieve all Employees (with a SALES Position) and all Customers regardless if they have a rep or representing any Customer. Display all matching records from Employee and Customers as well
A Select Statement to retrieve all Employees that are not representing any Customers.
A Select Statement to retrieve all Customers that have no Rep
Please include all your business rules and assumptions typed within your Normalization solution
View 1: Invoice
Business Rules:
1. CUSTOMER NUMBER must always be entered on the invoice
2. INVOICE NUMBER is always unique and uses a counter for its value
3. You can ignore PST and GST attributes
SCI Customer Inv, - sample 1
Invoice #: 1 07-May-02
Customer #: 78
Customer Name: YORK UNIVERSITY
Items Purchased:
Prod Id Prod. Name and Edition Qty Price Total
100861 Database Systems: Design And 2 109.20 218.40
Implementation, Fourth Edition
100890 Understanding Microsoft Access, 3 72.80 218.40
Ninth Edition
102130 Oracle: Introduction To PL/SQL 5 59.15 295.75
Sub-Total: 732.55
PST (8%): 58.60
GST (7%): 51.28
Total: $842.43
View 2 SCI Product Spreadsheet
All the course books are maintained on the following Product spreadsheet. SCI is currently having difficulty searching for books written by the same author, and would like the new database system to list each author separately. There can be one or many authors for each book, but there is no limit to the number of authors of a book
PROD ID | TITLE | EDITION | Purchase Cost | Retail Price | AUTHOR |
100860 | DATABASE SYSTEMS: DESIGN AND IMPLEMENTATION | THIRD EDITION | $84.98 | $99.98 | CARLOS CORONEL, PETER ROB |
100861 | DATABASE SYSTEMS: DESIGN AND IMPLEMENTATION | FOURTH EDITION | $92.82 | $109.20 | CARLOS CORONEL, PETER ROB |
100871 | MODERN RETAILING | FOURTH EDITION | $88.36 | $110.45 | DANIEL CASTON, JENNY TAPSCOTT |
100890 | UNDERSTANDING MICROSOFT ACCESS | NINTH EDITION | $65.52 | $72.80 | PIERRE LEBLAND, ROBERT COBB |
102130 | ORACLE: INTRODUCTION TO PL/SQL |
| $53.24 | $59.15 | PHILIP ROBB |
200376 | ORACLE: ADVANCED PL/SQL DEVELOPMENT |
| $64.26 | $71.40 | PHILIP ROBB, DAN CHEN |
Business Rules:
1. PURCHASE COST the default value is 0
2. RETAIL PRICE must always have a value
View 3 SCI Employee Forms
All employee information for SCI is maintained on file forms. The following are sample employee information forms:
EMP_ID 7839
EMP_NAME JOHN CHIU
EMP_NAME 123-234-345
POSITION Manager
SUPERVISOR
HIREDATE Nov 13, 99
EMP_ID 7434
EMP_NAME PARVEZ RAI
EMP_NAME 234-123-432
POSITION SALES
SUPERVISOR JOHN CHIU
HIREDATE Jun 24, 80
EMP_ID 7654
EMP_NAME ANDRE MARTIN
EMP_NAME 678-345-123
POSITION SALES
SUPERVISOR JOHN CHIU
HIREDATE Sep 24, 99
EMP_ID 7435
EMP_NAME DANIEL CHOY
EMP_NAME 345-312-234
POSITION CLERK
SUPERVISOR
HIREDATE Aug 4, 98
Business Rules:
1. SOC_INS must always be different for each employee
2. EMP_NAME must always have a value
View 4 SCI Customer Forms
All customer information for SCI is maintained on file forms. The following are sample customer information forms:
CUST_ID 78
NAME YORK UNIVERSITY
LOCATION NORTH YORK
SALES_REP ANDRE MARTIN
CUST_ID 74
NAME RYERSON
LOCATION TORONTO
SALES_REP PARVEZ RAI
CUST_ID 54
NAME SENECA COLLEGE
LOCATION NORTH YORK
SALES_REP PARVEZ RAI
CUST_ID 43
NAME INDIGO BOOKS
LOCATION TORONTO
SALES_REP ANDRE MARTIN
Business Rules:
1. NAME must always be different for each customer
2. SALES_REP must always have a value
SALES_REP must be an existing employee
A customer only has one sales rep, but the sales_rep may have more than one customer
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started