Question

The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model (tables, constraints, and relationships). As part of the lab, you will need to download the zip file titled CIS336Lab3Files from Doc Sharing. This zip file contains the ERD, Data Dictionary, and test data for the tables you create as you complete this exercise. Your job will be to use the ERD Diagram found below as a guide to 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. Please use exactly the data provided, without addition, deletion, or alteration except as directed, as your results may be evaluated against expected results generated using this exact data set. Narrative/Case Study For this lab, you will be creating SQL statements to build a series of relational tables, using SQL CREATE statements in a script file format for the Student Database. You will then populate those tables through the use of INSERT statements with sample data. You will need to create a script file and name it Your Name_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 six tables. 3. The INSERT statements necessary to insert all of the sample data. 4. Six 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 ERD Diagram which follows, and the exact data to be inserted into each table, which may be found via the Doc Sharing tab on the course website. The following guidelines are being provided to help assist you in creating your script file. • Use the names for the tables and columns as listed in the ERD. Do not change them as it will affect your grade. Creating Constraints . Create all NOT NULL constraints as indicated in the ERD. Create all PK constraints as indicated in the ERD.

Answer 1

If you have any doubts, please give me comment...

Step-1:

DROP TABLE IF EXISTS 'Enrollment';

DROP TABLE IF EXISTS 'Section';

DROP TABLE IF EXISTS 'Student';

DROP TABLE IF EXISTS 'Instructor';

DROP TABLE IF EXISTS 'ZipCode';

DROP TABLE IF EXISTS 'Course';

Step-2:

CREATE TABLE ZipCode(

    Zip INT NOT NULL PRIMARY KEY,

    City VARCHAR(50),

    State VARCHAR(10)

);

CREATE TABLE Student(

    Student_ID INT NOT NULL PRIMARY KEY,

    Saluation VARCHAR(5),

    First_Name VARCHAR(30),

    Last_Name VARCHAR(30),

    Street_Address VARCHAR(50),

    Phone VARCHAR(15),

    Employer VARCHAR(20),

    Registration_Date DATE,

    Zip INT,

    FOREIGN KEY(Zip) REFERENCES Zipcode(Zip)

);

CREATE TABLE Instructor(

    Instructor_ID INT NOT NULL PRIMARY KEY,

    Saluation VARCHAR(5),

    First_Name VARCHAR(50),

    Last_Name VARCHAR(50),

    Street_Address VARCHAR(50),

    Zip INT,

    FOREIGN KEY(Zip) REFERENCES Zipcode(Zip)

);

CREATE TABLE Course(

    Course_ID CHAR(6) NOT NULL PRIMARY KEY,

    Description VARCHAR(100),

    Cost FLOAT(6,2),

    Prerequisite CHAR(6)

);

CREATE TABLE Section(

    Section_ID CHAR(6) NOT NULL PRIMARY KEY,

    Course_Section_Num INT,

    Start_Date_Time DATETIME,

    Location VARCHAR(100),

    Capacity INT,

    Instructor_ID INT,

    Course_ID CHAR(6),

    FOREIGN KEY(Instructor_ID) REFERENCES Instructor(Instructor_ID),

    FOREIGN KEY(Course_ID) REFERENCES Course(Course_ID)

);

CREATE TABLE Enrollment(

    Section_ID CHAR(6),

    Student_ID INT,

    Enroll_Date DATE,

    Final_Grade CHAR(2),

    PRIMARY KEY(Section_ID, Student_ID),

    FOREIGN KEY(Section_ID) REFERENCES Section(Section_ID),

    FOREIGN KEY(Student_ID) REFERENCES Student(Student_ID)

);

Step-3:

-- data file is not provided, so here I am providing syntax of INSERT INTO statement

INSERT INTO table_name(column1_name, column2_name, ....) VALUES(column1_value column2_value, ....);

Step-4:

SELECT * FROM Zipcode;

SELECT * FROM Student;

SELECT * FROM Instructor;

SELECT * FROM Course;

SELECT * FROM Section;

SELECT * FROM Enrollment;

Step-5:

DESCRIBE Zipcode;

DESCRIBE Student;

DESCRIBE Instructor;

DESCRIBE Course;

DESCRIBE Section;

DESCRIBE Enrollment;

SHOW CREATE TABLE Zipcode;

SHOW CREATE TABLE Student;

SHOW CREATE TABLE Instructor;

SHOW CREATE TABLE Course;

SHOW CREATE TABLE Section;

SHOW CREATE TABLE Enrollment;