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