2023 The following assignment is based on the business scenario for which you created both an | Assignment Collections

The following assignment is based on the business scenario for which you created both an entity-relationship diagram and a normalized database design in Week 2.

For this assignment, you will create multiple related tables that match your normalized database design.  In other words, you will implement a physical design (an actual, usable database) based on a logical design.

Refer to the linked W3Schools.com articles “SQL CREATE TABLE Statement,” “SQL PRIMARY KEY Constraint,” “SQL FOREIGN KEY Constraint,” and “SQL INSERT INTO Statement” for help in completing this assignment.

To Complete This Assignment:

Use the CREATE TABLE statement to create each table in your design. Note that a table in a RDMS corresponds to an entity in an entity-relationship diagram. Recommended tables for this assignment are CUSTOMER, ORDER, ORDER_DETAIL, PRODUCT, EMPLOYEE, and STORE.

As part of each CREATE TABLE statement, define all of the columns, or fields, that you want each particular table to contain. Give them short, meaningful names and include constraints; that is, describe what type of data each column (field) is allowed to hold and any other constraints, such as size, range, or uniqueness.

Note that any field you marked as a unique identifier in your normalized database design is a key field. Key fields must be described as both UNIQUE and NOT NULL, which means a value must exist for each record and that value must be unique across all records.

After you have created all six tables, including relationships between the tables as appropriate (matching the primary key in one table to a foreign key in another table), use the INSERT INTO statement to insert 10 records into each of your tables. You will need to make up the data you insert into your tables. For example, to insert one record into the CUSTOMER table, you will need to invent a customer number, a customer name, and so on—one value for each of the fields you defined for the CUSTOMER table—to insert into the table.

To ensure that your INSERT INTO statements succeeded in populating your tables, use the SELECT statement described in Ch. 7, “Introduction to Structured Query Language,” in Database Systems: Design, Implementation, and Management.to retrieve the records you inserted. For example, to see all 10 records you inserted into the CUSTOMER table, you might apply the following SQL statement: SELECT * FROM CUSTOMER;

After you have created all six tables and populated ten records in each table, submit to the Assignment Files tab the database containing all of the tables you created, or a Microsoft® Word document listing all of the SQL statements you used.

Brewton Order Customer number: 9876

Brewton Enterprises, Inc. Order Form

Customer name: John Doe

Customer address: 456 Bishop Street Honolulu, Hawaii 96813

Tel: (808) 8650990

Sales agent: Lloyd Johnson

Sales agent number: S99

Product No.

Description

Quantity

Unit Price

Normalization of Brewton Enterprises, Inc. Order Form (Brewton Business Scenario) 

UNF 

1NF 

2NF 

3NF 

Entity Name 

Product No.*  

Product No.* 

-No. of Employees) 

– No. Employee Positions) 

-Quantity) 

– Unit Price) 

-No. of Employees) 

– No. Employee Positions) 

-Quantity) 

– Unit Price) 

-Employees 

– Employee  

-Quantity 

– Price 

Employees 

Quantity 

Price 

No. of Employees) 

No. Employee Positions) 

Page Break 

Location of Business* 

Quantity) 

Unit Price)