Question

Using MYSQL in terminal, create the tables for your above designed relational schema and populate your tables with appropriate data. All except time slot and the relationship it participates in. Use the names for the tables and attributes from the ER schema. Use ON DELETE CASCADE for foreign keys.

Each basic table (corresponding to an entity in the ER schema developed for Part 1) should have 5-10 rows. Campus can have just 2. Building should have at least 6. At least 1 building name (Laurel) should be in both campuses.

The remaining tables should have at least 10 rows. Make sure that your tables are in compliance with the participation constraints.

Show the following:

·create table commands that you used

·any commands that you used to insert or load into the tables (in the order in which they were performed- order matters!)

·content of you table as a result as a select * from (each table)

-----------------------

Here is the schema:

building(pk(building_name), num_floors, num_classrooms)
campus(pk(campus_name), state_name, school_name)
classroom(pk(room_number), capacity)
course(pk(course_id), title, credits)
instructor(pk(iid), salary, inst_name)
department(pk(dept_name), budget)
student(pk(sid), stud_name, tot_cred)
time_slot(pk(time_slot_id), {day, start_time, end_time})
section(pk(section_id), pk(semester), pk(year))

has(pk(dept_name, building_name), fk(dept_name) ref department(dept_name), fk(building_name) ref building(building_name))
course_dept(pk(dept_name, course_id), fk(dept_name) ref department(dept_name), fk(course_id) ref course(course_id))
inst_dept(pk(dept_name, iid), fk(dept_name) ref department(dept_name), fk(iid) ref instructor(iid))
mentors(pk(iid, sid), fk(iid) ref instructor(iid), fk(sid) ref student(sid))
stud_dept(pk(dept_name, sid), fk(dept_name) ref department(dept_name), fk(sid) ref student(sid))
takes(pk(sid, section_id), grade, fk(sid) ref student(sid), fk(section_id) ref section(section_id))
sec_time_slot(pk(time_slot_id), pk(section_id, semester, year), fk(time_slot_id) ref time_slot(time_slot_id), fk(section_id, semester, year) ref section(section_id, semester, year))
sec_class(pk(section_id, room_number), fk(section_id) ref section, fk(room_number) ref classroom(room_number))
teaches(pk(iid), pk(section_id, semester, year), fk(iid) ref instructor(iid), fk(section_id, semester, year) ref (section(section_id, semester, year)))
prereq(pk(course_id), fk(course_id) ref course)

Answer 1

To create a table for building i.e. building(pk(building_name),num_floors,num_classrooms) following query is used :-

CREATE TABLE building(

building_name varchar(25) NOT NULL PRIMARY KEY,

num_floors int(20),

num_classrooms int(100)

);

Now to insert values in the above table created i.e. building table following query is used :-

INSERT INTO building VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM building;

To create a table for campus i.e. campus(pk(campus_name,state_name,school_name) following query is used :-

CREATE TABLE campus(

campus_name varchar(30) NOT NULL PRIMARY KEY,

state_name varchar(40),

school_name varchar(40)

);

Now to insert values in the above table created i.e. campus table following query is used :-

INSERT INTO campus VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM campus;

To create a table for classroom i.e. classroom(pk(room_number), capacity) following query is used :-

CREATE TABLE classroom(

room_number int(15) NOT NULL PRIMARY KEY,

capacity int(100)

);

Now to insert values in the above table created i.e. classroom table following query is used :-

INSERT INTO classroom VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM classroom;

To create a table for course i.e. course(pk(course_id), title, credits) following query is used :-

CREATE TABLE course(

course_id varchar(50) NOT NULL PRIMARY KEY,

title varchar(40),

credits varchar(40)

);

Now to insert values in the above table created i.e. course table following query is used :-

INSERT INTO course VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM course;

To create a table for instructor i.e. instructor(pk(iid), salary, inst_name) following query is used :-

CREATE TABLE instructor(

iid varchar(50) NOT NULL PRIMARY KEY,

salary int(80),

inst_name varchar(40)

);

Now to insert values in the above table created i.e. instructor table following query is used :-

INSERT INTO instructor VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM instructor;

To create a table for department i.e. department(pk(dept_name), budget) following query is used :-

CREATE TABLE course(

dept_name varchar(50) NOT NULL PRIMARY KEY,

budget varchar(50)

);

Now to insert values in the above table created i.e. department table following query is used :-

INSERT INTO department VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM department;

To create a table for student i.e. student(pk(sid), student_name, tot_cred) following query is used :-

CREATE TABLE student(

sid int(50) NOT NULL PRIMARY KEY,

student_name varchar(40),

tot_cred varchar(40)

);

Now to insert values in the above table created i.e. student table following query is used :-

INSERT INTO student VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM student;

To create a table for time_slot i.e. time_slot(pk(time_slot_id), day, start_time, end_time) following query is used :-

CREATE TABLE time_slot(

time_slot_id varchar(50) NOT NULL PRIMARY KEY,

day varchar(40),

start_time varchar(40),

end_time varchar(40)

);

Now to insert values in the above table created i.e. time_slot table following query is used :-

INSERT INTO time_slot VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM time_slot;

To create a table for section i.e. section(pk(section_id), pk(semester), pk(year)) following query is used :-

CREATE TABLE section(

section_id varchar(50) NOT NULL PRIMARY KEY,

semester varchar(40) NOT NULL PRIMARY KEY,

year varchar(40)NOT NULL PRIMARY KEY

FOREIGN KEY (dept_name) REFERENCES department(dept_name),

FOREIGN KEY (building_name) REFERENCES building(building_name),

);

Now to insert values in the above table created i.e. section table following query is used :-

INSERT INTO section VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM section;

To create a table for course_dept i.e. course_dept(pk(dept_name, course_id)) following query is used :-

CREATE TABLE course_dept(

dept_name varchar(50) NOT NULL PRIMARY KEY,

course_id varchar(50) NOT NULL PRIMARY KEY,

FOREIGN KEY (dept_name) REFERENCES department(dept_name),

FOREIGN KEY (course_id) REFERENCES course(course_id)

);

Now to insert values in the above table created i.e. course_dept table following query is used :-

INSERT INTO course_dept VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM course_dept;

To create a table for inst_dept i.e. inst_dept(pk(dept_name, iid)) following query is used :-

CREATE TABLE course_dept(

dept_name varchar(50) NOT NULL PRIMARY KEY,

iid varchar(50) NOT NULL PRIMARY KEY,

FOREIGN KEY (dept_name) REFERENCES department(dept_name),

FOREIGN KEY (iid) REFERENCES instructor(iid)

);

Now to insert values in the above table created i.e. inst_dept table following query is used :-

INSERT INTO inst_dept VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM isnt_dept;

To create a table for mentors i.e. mentors(pk(iid,sid)) following query is used :-

CREATE TABLE course_dept(

sid varchar(50) NOT NULL PRIMARY KEY,

iid varchar(50) NOT NULL PRIMARY KEY,

FOREIGN KEY (sid) REFERENCES student(sid),

FOREIGN KEY (iid) REFERENCES instructor(iid)

);

Now to insert values in the above table created i.e. mentors table following query is used :-

INSERT INTO mentors VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM mentors;

To create a table for stud_dept i.e. stud_dept(pk(dept_name, sid)) following query is used :-

CREATE TABLE stud_dept(

dept_name varchar(50) NOT NULL PRIMARY KEY,

sid varchar(50) NOT NULL PRIMARY KEY,

section_id varchar(50) NOT NULL PRIMARY KEY,

FOREIGN KEY (dept_name) REFERENCES department(dept_name),

FOREIGN KEY (sid) REFERENCES student(sid),

FOREIGN KEY (section_id) REFERENCES section(section_id)

);

Now to insert values in the above table created i.e. stud_dept table following query is used :-

INSERT INTO stud_dept VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM stud_dept;

To create a table for sec_time_slot i.e. sec_time_slot(pk(time_slot_id), pk(section_id, semester, year)) following query is used :-

CREATE TABLE sec_time_slot(

time_slot_id varchar(50) NOT NULL PRIMARY KEY,

section_id varchar(50) NOT NULL PRIMARY KEY,

FOREIGN KEY (time_slot_id) REFERENCES time_slot(time_slot_id),

FOREIGN KEY (section_id, semester, year) REFERENCES section(section_id, semester, year)

);

Now to insert values in the above table created i.e. inst_dept table following query is used :-

INSERT INTO sec_time_slot VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM sec_time_slot;

To create a table for sec_class i.e. sec_class(pk(section_id, room_number)) following query is used :-

CREATE TABLE sec_class(

section_id varchar(50) NOT NULL PRIMARY KEY,

room_number varchar(50) NOT NULL PRIMARY KEY,

FOREIGN KEY (section_id) REFERENCES section(section_id),

FOREIGN KEY (room_number) REFERENCES classroom(room_number)

);

Now to insert values in the above table created i.e. sec_class table following query is used :-

INSERT INTO sec_class VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM sec_class;

To create a table for inst_dept i.e. teaches(pk(iid), pk(section_id, semester, year)) following query is used :-

CREATE TABLE teaches(

section_id varchar(50) NOT NULL PRIMARY KEY,

iid varchar(50) NOT NULL PRIMARY KEY,

   FOREIGN KEY (section_id, semester, year) REFERENCES section(section_id, semester, year),

FOREIGN KEY (iid) REFERENCES instructor(iid)

);

Now to insert values in the above table created i.e. inst_dept table following query is used :-

INSERT INTO teaches VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM teaches;

To create a table for prereq i.e. prereq(pk(course_id) following query is used :-

CREATE TABLE prereq(

course_id varchar(40) NOT NULL PRIMARY KEY,

FOREIGN KEY (course_id) REFERENCES course(course_id)

);

Now to insert values in the above table created i.e. inst_dept table following query is used :-

INSERT INTO prereq VALUES(value1, value2, value3, ...........); -> whatever values are to inserted will be inserted here

Now to see/show the table we use the floowing query :-

SELECT * FROM prereq;