There are three parts to this assignment, (PARTS A, B, and C). Be sure to complete all of the parts.

BELOW ARE THE FILES THAT YOU WILL SUBMIT FOR THIS ASSIGNMENT:

mod2Sql.sql createTruck.sql truckQuery.sql Word document with the answers to Part C of this assignment

Part A: Write SQL command on one table

Check off the box beside each function as you go through the process.

Write the SQL commands in a script file called mod2Sql.sql.

Make sure to drop the Employee table first in the script so that you can run the script multiple times.

That is:

drop table EMPLOYEE; create table EMPLOYEE ();

Note: Declare the primary key in the Employee table.

The following attribute definition will be used in creating this table:

Attribute (Field) Name

Data Declaration

EMPID

CHAR(6)

EMPLNAME

VARCHAR2(15)

EMPINIT

VARCHAR2(15)

EMPFNAME

CHAR(1)

EMPJOBCODE

CHAR(3)

Write SQL code to display all data in the Employee table. Write SQL command to save the data.

Write SQL code to display the employee first name, initial, and last name.

Write SQL command to update Marias last name to Smith.

Write SQL code to display the employee number, first name, and last name of the employee who has job code of 18.

If you are using Oracle Live SQL, copy your work to a notepad file, and save it as mod2Sql.sql. Submit this file to the Module 2 Dropbox as part of the Module 2 Assignment.

Part B: Write SQL command on more than one table

STEP 1: CREATE TABLE STRUCTURES

Check off the box beside each function as you go through the process.

Write the SQL commands to create the following 3 tables along with appropriate primary key and foreign key declaration in a createTruckCo.sql file.

Debug the script file until it is error free.

Use the following data type declaration to create the structures of the tables.

Note: The order in which these three tables are created matters in this assignment. Pay special attention to referential integrity among the tables. Create the BASE table first, then the TYPE table, and finally the TRUCK table (since the TRUCK table is the one that has foreign keys).

Table name: BASE

Primary key: BASENUM

Attribute (Field) Name

Data Declaration

BASENUM

CHAR(3)

BASECITY

VARCHAR2(20)

BASESTATE

CHAR(2)

BASEPHON

VARCHAR2(10)

BASEMGR

VARCHAR2(10)

Table name: TYPE

Primary key: TYPENUM

Attribute (Field) Name

Data Declaration

TYPENUM

CHAR(1)

TYPEDESC

VARCHAR2(30)

Table name: TRUCK

Primary key: TNUM

Foreign key: BASENUM, TYPENUM

Attribute (Field) Name

Data Declaration

TNUM

CHAR(4)

BASENUM

CHAR(3)

TYPENUM

CHAR(1)

TMILES

NUMBER(7,1)

TBOUGHT

DATE

TSERIAL

VARCHAR2(20)

STEP 2: POPULATE THESE THREE TABLES

Check off the box beside each function as you go through the process.

Continue to work on the createTruckCo.sql file and write SQL commands to insert records.

Debug the script file until it is error free. If you are using Oracle Live SQL, copy your work to a notepad file, and save it as createTruckCo.sql. Submit this file to the Module 2 Dropbox as part of the Module 2 Assignment.

STEP 3: DISPLAY

Check off the box beside each function as you go through the process.

Write SQL code to accomplish the following tasks in truckQuery.sql.

Debug the script file until it is error free. Turn in the truckQuery.sql file.

Display truck number; buy date, mileage, and serial number of all trucks.

Display truck number, buy date, and mileage of all trucks with Dallas as their base.

Display truck number and buy date of all trucks with Dallas as their base and with mileage lower than

30,000 miles.

Part C: Normalization

Briefly explain what the 1st, 2nd, and 3rd normal forms are, and identify each of these normal forms in the dependency diagram shown in the following figure:

Use the above dependency diagram as an example; draw the dependency diagram for the following student table:

Use Microsoft Word for this part (Part C) of the assignment. When you are done, submit this document to the Module 2 Dropbox as part of the Module 2 Assignment.