NEED HELP ASAP!

Question

1. Create tables using SLQ statements with all the constraints. Choose appropriate SQL Server data types. Insert 2 rows in each of the tables. Assume relevant value in the rows of each table. Be sure the order of the table creation and insertion must be correct, to cover referential integrity constraints.

Product (Product ID, ProdName, ProdDescription, Price)

↑

Order (Product ID, Customer ID, Order Date)

↓

Customer (Customer ID, CustName, PostalCode)

*Other Constraints*

1. Customer must have postal code and name
2. no 2 products that have the exact same name
3. An order must have a date

2.In what normal form (NF) is the table below and convert it to the 3rds normal form if it is in a lower form. Mention relationships between the tables. Include all the types of dependencies (Total, Partial, & Transitive)

|¯¯¯¯¯¯¯¯¯¯|¯¯¯¯¯¯¯¯¯|¯¯¯¯¯¯¯¯¯¯¯•¯¯¯¯¯¯¯¯¯¯¯¯↓

Statement (Customer ID, Order ID, issue date, Customer_Name, statement_type)

|_______________________________↑

Answer 1

#2)

the cusomter_name field must be removed from the statement table.

If the Customer.CustName changes, then so must Statement.customer_name.

This is data redundancy that can lead to integrity inconsistencies.

Remember

Normal form: " the field depends the primary key (1st NF), the whole primary key (2nf),

and nothing but the primary key (3nf), so help me Codd"

1st normal form: each field belongs to 1 and only one column. YES

2nd normal form: each field depends on the primary key. NO

customer_name does not depend on orderID;

Since the orderID is the primary key for the ORder table and

there is one statement per order and the customer ID is already in the order table,

the customer ID can also be removed.

The statement table becomes:

orderID, issue date,statement_type

is now in 3NF, since each field column depends on the primary key (orderID),

the whole primary key, and nothing but the primary key

#1)

Product (ProductID,ProdName,ProdDescription,Price)

Order( order ID, customerID, order date, # of items, subtotal, tax, final amount)

OrderDetail( orderID, productID, qty)

Customer( customerID, custName,zipCode)

Statement(orderID,issue_date,statement_type)

create table product ( productID integer,

prodDescription varchar(255),

price decimal,

primary Key (productID) autoincrementing

);

create table ORder( orderID Long,

customerID integer,

orderDate date,

numitems integer,

subtotal decimal,

tax decimal,

final_amount decimal,

primary key (orderID) autoincrementing

foreign key (customerID) references CUstomer.CustomerID);

create table orderDetail ( orderID integer,

productID integer,

qty integer,

foreign key (orderiD) references ORder.orderID,

foreign key (productID) references product.productID

);

create table customer ( customerID integer,

custName varchar(255),

zipCode varchar(10),

primary key (customerID) autoincrementing

);

create table statement (orderid integer,

statement_type integer,

issue_date date,

foreign key (orderID) references order.OrderID)

);