CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, customer_first_name VARCHAR(20) NOT NULL, customer_last_name VARCHAR(20) NOT NULL, customer_street VARCHAR(30) NOT NULL, customer_city VARCHAR(25) NOT NULL, customer_state CHAR(2) NOT NULL, customer_zip VARCHAR(5) NOT NULL, customer_email VARCHAR(50) NOT NULL, customer_phone VARCHAR(10) NOT NULL );

CREATE TABLE product ( product_id INTEGER PRIMARY KEY, product_name VARCHAR(255) NOT NULL, product_size VARCHAR(255) NOT NULL, product_price DECIMAL(10,2) NOT NULL, product_weight DECIMAL(10,2) NOT NULL, order_state VARCHAR(255) NOT NULL );

CREATE TABLE order_state ( order_state_id INTEGER PRIMARY KEY, order_state_name VARCHAR(2) NOT NULL ); CREATE TABLE order ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, order_date DATE NOT NULL, order_state_id INTEGER NOT NULL, order_shipping_date DATE NOT NULL, order_delivery_date DATE NOT NULL, warehouse_id INTEGER NOT NULL, FOREIGN KEY (customer_id) REFERENCES customer (customer_id), FOREIGN KEY (order_state_id) REFERENCES order_state (order_state_id), FOREIGN KEY (warehouse_id) REFERENCES warehouse (warehouse_id) ); CREATE TABLE warehouse ( warehouse_id INTEGER PRIMARY KEY, warehouse_name VARCHAR(255) NOT NULL, warehouse_location VARCHAR(255) NOT NULL );

Do the tables and non-key fields accurately reflect the data model? Are the data types appropriate? Provide a rationale.

Do foreign keys represent all one to many relationships properly? Provide a rationale.

Are the tables in third normal form? Provide a rationale.

You must create the Warehouse table with a primary key of Warehouse_ID prior to creating the Employee table. The Warehouse table must include at least the Warehouse_IDs 10 and 20.

The data type for Warehouse_ID must be the same in both the Employee and the Warehouse tables.

Columns that include numbers that are not used in arithmetic calculations are stored as character data types. Examples from the Employee table include E_Street_Addr, E_Zip, E_Job_Code, and Warehouse_ID.

Employee (Note: Attribute names are abbreviated to fit into the table columns)

Emp_ID	E_LName	E_Fname	E_St_Addr	E_St_Nm	E_City
E509	Jones	George	123	West Elm	Greeley
E302	Hayes	Marilyn	456	S Main	Newark
E244	Walker	Shondra	789	35th Ave	San Diego
E651	James	LaBron	321	123rd Street	Cleveland
E804	Your_Last_Name	Your_First_Name	Your_St_addr	Your_St_Num	Your_City
E002	Instr_Last_Name	Instr_First_Name	453	Spruce St	Denver

Employee (Cont.)

E_St	E_Zip	E_Job_Cd	E_Pay_Rate	E_St_Date	Warehouse_ID
CO	81001	100	45000	05/20/2020	10
NJ	12003	200	95000	04/15/2017	10
CA	90121	400	85000	08/09/2015	20
OH	34508	400	120000	11/11/2020	20
Your_St	Your_Zip	400	105000	Todays_Date	10
CO	80230	600	105000	1/15/2011	20

Complete the following tasks:

Use SQL Developer to enter the Create Table statements into the AWS Oracle database instance. Be sure to include ALL tables mapped from your UML Class diagram. Provide a screenshot of every create table statement and the resulting Table Created output in SQL Developer.

Include your name, the Instructors name and the date as a comment on each screen shot.

Use SQL Developer to create SQL insert commands to insert 46 data items into each table. Be sure to verify all referential (FK) integrity constraints in tables on the many side of all one to many relationships. Do not include screen shots for the insert commands.

Note:

You must include at least two Orders that have been placed by the Customer but not shipped, at least two Orders that have been shipped but not delivered, and at least two Orders that have been delivered.

At least four of the Orders must include more than one product.

Use SQL Developer to show all the data inserted into the tables using the Select * SQL command for each table you created. Provide a screenshot of every select statement and the resulting output in SQL Developer.

(e.g., Select * From Orders:)

Use SQL Developer to show the successful insertion of all foreign key attributes by executing a SQL Natural Join statement to show the successful linking of all tables included in one to many relationships. Provide a screenshot of every select statement and the resulting output in SQL Developer.

(e.g., Select * From Customer natural join Orders; Select * From Orders natural join Order_Line natural join Products;)