Lab 3 of 7: Building the Physical Model
Your assignment is due by Sunday, end of week, 11:59 p.m. (MT). Submit your assignment to the Dropbox located on the silver tab at the top of this page. For instructions on how to use the Dropbox, please click here.
L A B O V E R V I E W
Scenario/Summary
Beginning with this lab, and continuing through the remaining weeks you will be doing all of your work in Oracle itself. The editor interface to the Oracle server is SQL*Plus, and by now you have received your logon id and password and should have at least made sure you can connect. If for any reason you have not done so, please do so now before continuing any further. If for any reason you cannot connect then you need to let the instructor know ASAP so that any problems can be resolved.
Before attempting Lab #3, you need to be sure you have read through the SQL*Plus tutorial which can be found in Doc Sharing as well as under the SQL*Plus Tutorial tab in Week 3. This tutorial describes the functionality of the editor and will step you through the process of setting up and using the SQL*Plus in the iLab environment so that it will best serve your needs for the remainder of the labs required for this course.
The lab for this week addresses taking a logical database design (data model) and now transforming that into a physical model (tables, constraints, and relationships). As part of the lab, you will need to download the ZIP file titled “Lab 3 Support Document” from Doc Sharing. This ZIP file contains three documents: Lab 3 Relationship Diagram.doc, Lab 3 Meta Data Diagram.xls, and Lab 3 Input Data.doc. Your job will be to take the meta data defined in the data dictionary spreadsheet document, and using the relationship diagram as a guide define the table structures and constraints using both CREATE TABLE and ALTER TABLE statements. Once this has been done, you will need to write the INSERT INTO TABLE statements to insert the data provided into the table. The data should verify that the constraints you have created are valid and define the correct referential and data integrity constraints asked for.
Narrative/Case Study
For this lab you be working with command-line SQL to build a series of relational of tables, using SQL CREATE statements in a script file format for the Student Database and then populate those tables through the use of INSERT statements with sample data.
You will need to create a script file and name it mystudentdb.txt containing the following code:
The drop table statements listed later in the specifications of this lab.
The CREATE TABLE statements required to build the seven (7) tables.
The INSERT statements necessary to insert all of the sample data.
Seven select statements to verify that the data is in the tables and accessible.
To help you accomplish this task successfully you are being supplied with the following three documents which can be found in the Doc Sharing tab on the course web site:
A Relationship Diagram of the database showing in graphical form the 1-M relationships between the tables (336_Lab3_Relationship_Diagram.doc).
A Meta Data chart providing information about table names, column names, etc. Be sure to follow this reference exactly when constructing your CREATE TABLE statements (336_Lab3_Data_Dict.xls).
A listing of sample data to use when writing the INSERT statements to populate the tables (336_lab3_input_data.doc).
The following guidelines are being provided to help assist you in creating your script file:
Create the tables in the order shown in the data dictionary. Use the names for the tables and columns as listed in the Meta Data chart