Create and Populate a DatabaseInstructions:Resources: "SQL CREATE DATABASE Statement," "SQL CREATETABLE Statement," "SQL PRIMARY KEY Constraint," "SQL FOREIGNKEY Constraint," and "SQL INSERT INTO Stat

DBM/380 v14

Create a Database Create and Populate a DatabaseInstructions:Resources: "SQL CREATE DATABASE Statement," "SQL CREATETABLE Statement," "SQL PRIMARY KEY Constraint," "SQL FOREIGNKEY Constraint," and "SQL INSERT INTO Stat 1

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.

Note: In the industry, even the most carefully thought out database designs can contain mistakes. Feel free to correct in your tables any mistakes you notice in your normalized database design. Also, note that in Microsoft® Access®, you follow the steps below to launch the SQL editor:

Create and Populate a DatabaseInstructions:Resources: "SQL CREATE DATABASE Statement," "SQL CREATETABLE Statement," "SQL PRIMARY KEY Constraint," "SQL FOREIGNKEY Constraint," and "SQL INSERT INTO Stat 2

Figure 1. To create a SQL query in Microsoft® Access®, begin by clicking the CREATE tab.

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.