Your Perfect Assignment is Just a Click Away

Starting at $8 per Page

100% Original, Plagiarism Free, Customized to Your instructions!

glass
pen
clip
papers
heaphones

Database lab

Database lab

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 take 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 Documents” from Doc Sharing. This Zip file contains three documents: Lab 3 Relationship Diagram.docx, Lab 3 Meta Data Diagram.xlsx, and Lab 3 Input Data.docx.

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. Lastly, you will write SELECT statements to query the tables and verify the data was populated.

Narrative/Case Study

For this lab, you be working with command-line SQL to build a series of relational 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 YourName_Lab3.txt containing the following code:

  1. The drop table statements listed later in the specifications of this lab.
  2. The CREATE TABLE statements required to build the seven (7) tables.
  3. The INSERT statements necessary to insert all of the sample data.
  4. 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 website:

  1. A Relationship Diagram of the database showing in graphical form the 1:M relationships between the tables (336_Lab3_Relationship_Diagram.doc).
  2. 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).
  3. 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 – Do not change them as it will affect your grade.
  • Creating CONTRAINTS –
    • Create all constraints as indicated in the data dictionary.
    • Constraint suffixes must be as follows:
      • Primary key constraint – PK
      • Foreign key constraint – FK
      • Check constraint – CC
    • Explicitly name all constraints PRIMARY KEY, FOREIGN KEY, and CHECK constraints to reflect:
      • TableName_ColumnName_ConstraintSuffix (For example, to name a PRIMARY KEY constraint on the SID column in the student table you would use STUDENT_SID_PK.)
      • For this lab you do not have to explicitly name NOT NULL constraints
    • Create all PRIMARY KEY and NOT NULL constraints within the CREATE TABLE statements at the column level (line level), unless the Primary Key is a composite Primary Key (yes, there is one). Composite Primary Key constraints must be created at the table level.
    • Create all FOREIGN KEY and CHECK constraints at the table level using ALTER TABLE statements.
    • All constraints must be added prior to populating the tables with data.
  • Create all of the tables and all of the constraints before populating any of the tables with data.
  • Insert statements must be in the same order as the tables are created (follow the order in the sample data file).
  • ALL character strings must be enclosed in single quotes. This includes alpha strings and alphanumeric (remember that any formatting within a numeric strings makes it alphanumeric).
  • If you are inserting a NULL, do not enclose the word NULL in single quotes, as this will insert the word NULL into the row. To insert a null you simply use the word NULL.

Deliverables

The deliverable for this lab will include the following documents.

  1. Your script file. Create this file in Notepad not Word. Make sure your name is in a comment area at the top of the script file. Use a double dash to create a one line comment:

    –Jacob Smith

    –Lab 3

  2. Your lab output file created using a spool session in SQL*Plus.

Be sure to use the SET ECHO ON session command when creating the output file so that both the SQL commands and results will show in the output. Be sure your name is on all documents and that all documents have been included in a single Zip file for this week’s assignments.

LAB STEPS

STEP 1: The DROP statements

Back to Top

The following DROP TABLE statements must appear at the top of your script file. This will allow you to run and re-run your script file as often as you need to. The first time you run your script file you will get an ERROR on the drop statements saying the Table or View does not exist. This is true since it has not been created yet, but the error is more informational than anything. Once the tables are created, you will not get this error again.

DROP TABLE ENROLLMENT CASCADE CONSTRAINTS PURGE;
DROP TABLE COURSE_SECTION CASCADE CONSTRAINTS PURGE;
DROP TABLE COURSE CASCADE CONSTRAINTS PURGE;
DROP TABLE TERM CASCADE CONSTRAINTS PURGE;
DROP TABLE STUDENT CASCADE CONSTRAINTS PURGE;
DROP TABLE FACULTY CASCADE CONSTRAINTS PURGE;
DROP TABLE LOCATION CASCADE CONSTRAINTS PURGE;

STEP 2: The CREATE TABLE statements

Back to Top

Next, define the CREATE TABLE statements for the seven tables that you are to create based upon the ERD and Data Dictionary that you have been given for this lab. Be sure to follow the guidelines given above on how and where to create the different types of constraints for each table.

STEP 3: The INSERT statements for the data

Back to Top

The third step is to create the insert statements to insert the sample data into the tables created in Step 2. You might find it helpful to take the Word document containing the sample data and save it as a text file. After doing this, you could create the insert statements around the actual data that is in that file. The process for doing this should have been covered in one of the lectures for this unit. If it was not, then ask your instructor to discuss this process with the class.

STEP 4: The SELECT statements

Back to Top

The next step of the lab will be to create the select statements to verify the data was inserted correctly. You should have seven select statements; one for each table. The command is SELECT * FROM Table_Name; For example, to select all columns from the Student table, the command would be SELECT * FROM Student;

Be sure to save all of the above statements in your script file.

STEP 5: Testing and verifying your script

Back to Top

Now we come to the point of verifying that your script file works by creating all of the tables and inserting all of the data. To verify this you will need to execute your saved script file and create an output file to capture the results being returned to the session by using the spool COMMAND outlined in the tutorial “Using Oracle SQL*Plus.” This output file will be one of the files you will be turning in for grading.


"Place your order now for a similar assignment and have exceptional work written by our team of experts, guaranteeing you A results."

Order Solution Now

Our Service Charter


1. Professional & Expert Writers: Eminence Papers only hires the best. Our writers are specially selected and recruited, after which they undergo further training to perfect their skills for specialization purposes. Moreover, our writers are holders of masters and Ph.D. degrees. They have impressive academic records, besides being native English speakers.

2. Top Quality Papers: Our customers are always guaranteed of papers that exceed their expectations. All our writers have +5 years of experience. This implies that all papers are written by individuals who are experts in their fields. In addition, the quality team reviews all the papers before sending them to the customers.

3. Plagiarism-Free Papers: All papers provided by Eminence Papers are written from scratch. Appropriate referencing and citation of key information are followed. Plagiarism checkers are used by the Quality assurance team and our editors just to double-check that there are no instances of plagiarism.

4. Timely Delivery: Time wasted is equivalent to a failed dedication and commitment. Eminence Papers is known for timely delivery of any pending customer orders. Customers are well informed of the progress of their papers to ensure they keep track of what the writer is providing before the final draft is sent for grading.

5. Affordable Prices: Our prices are fairly structured to fit in all groups. Any customer willing to place their assignments with us can do so at very affordable prices. In addition, our customers enjoy regular discounts and bonuses.

6. 24/7 Customer Support: At Eminence Papers, we have put in place a team of experts who answer to all customer inquiries promptly. The best part is the ever-availability of the team. Customers can make inquiries anytime.