Lab: DDL, DML, and SQL Select Commands

Tables store data in the database. Their names should reflect the data stored to help users navigate the collection of tables. Each table can have one or more columns of specific data types such as number, character, or date, among others. The data type of a column is the first level of validation to ensure that the correct type of data is stored. Additional validation of columns comes in the form of constraints, such as PRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT or FOREIGN KEY. Data that is inserted into the tables must satisfy all table constraints or the DBMS will return an error.

Note:

• While it is important to learn the details of the syntax for creating tables, it should be noted that many database developers use applications that include a function to generate CREATE TABLE statements directly from the UML Class diagram.
• In addition, although we are using a client application with a SQL editor, end-users often use applications with familiar interface elements like drop-down lists and check boxes to easily interact with databases. At the foundation of these GUI interfaces is a layer that uses SQL to communicate with the database. You will learn how to build GUI interfaces (applications) in another course.

To Prepare for this Lab

• Review the resources included in the Explore section.
• Review the UML class diagram and attribute list from your Week 1 and Week 2 scenario and assignments.
• Create a list of all table combinations that are linked by a one-to-many relationship.
• Identify data types and constraints for each attribute.
• Develop the SQL CREATE TABLE statements required to create the tables based on the UML class diagram. The create statement must include all attributes, primary keys, and foreign keys (on the many side of all one-to-many relationships you identified).
• Include any additional constraints you think are appropriate with a justification for the inclusion of each constraint. Additional constraints include NOT NULL, DEFAULT, CHECK or UNIQUE.
• Develop a final list of tables that would be included in the database. Answer the following in a narrative summary:
  • 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.
• Note: The Employee table attributes and data to insert is provided in the table below
  • You will insert your name and your Instructors name into the table in the rows specified.
  • 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)

Employee (Cont.)

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 Instructor’s name and the date as a comment on each screen shot.
• Use SQL Developer to create SQL insert commands to insert 4–6 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;)

By Day 7

Submit the following in a single Word document:

• A listing of all attributes and constraints for each table. (You may submit your create table statements in a Word document to meet this assignment item.)
• The list of tables linked by one to many relationships.
• A summary narrative describing how the attributes satisfy the data needs of the scenario and the list of requirements.
• An explanation of how you know that each table is in third normal form.
• The SQL Developer screen shots showing
  • The creation of each table
  • The execution of one Select * statement for each table to show the data inserted into each table and the output
  • The Natural Join of all tables linked with one-to-many relationships and the output
  • All screen shots include the students name, the Instructors name, and the date.
  • Note: Do not include Insert commands, the select * statements will verify the appropriate insertion of all data.