02

Initialization¶

use 20200198db;
set foreign_key_checks = 0;
drop table if exists department, student, course, advisor, instructor, teaches, takes, prereq;
set foreign_key_checks  = 1;

Questions¶

1. Create a table for the following. department(dept_name, building, budget)

create table department(
  dept_name varchar(20),
  building int(4),
  budget float(10)
);

1. Alter the above table to add primary key.

   ALTER TABLE department ADD PRIMARY KEY (dept_name);
   

2. Create the following tables with primary key and foreign key constraints | Table | FK | | ----------------------------------------------------------- | ------------------------------------------------------------ | | Student(SID, name, dept_name, total_credit) | dept_name | | course(course_id, title, dept_name , credits) | dept_name | | instructor(IID, name, dept_name, salary) | dept_name | | teaches(IID, course_id, sec_id, semester, year) | course_id, instructor(IID) | | takes(SID, course_id, sec_id, semester, year, grade) | | | advisor(SID, IID) | IID references instructor (IID), SID references student (SID) | | prereq(course_id, prereq_id) | course_id references course(course_id), prereq_id references course(course_id) |

   create table student(
     SID int(4), 
     name varchar(20), 
     dept_name varchar(20), 
     total_credit int(4),
   
     PRIMARY KEY(SID),
     FOREIGN KEY (dept_name) REFERENCES department(dept_name)
   );
   
   create table course(
     course_id varchar(20),
     title varchar(20),
     dept_name varchar(20),
     credits int(4),
   
     PRIMARY KEY(course_id),
     FOREIGN KEY (dept_name) REFERENCES department(dept_name)
   );
   
   create table instructor(
     IID int(4),
     name varchar(20),
     dept_name varchar(20),
     salary int(4),
   
     PRIMARY KEY(IID),
     FOREIGN KEY (dept_name) REFERENCES department(dept_name)
   );
   
   create table teaches(
     IID int(4),
     course_id varchar(20),
     sec_id int(4),
     semester int(4) check (semester between 1 and 2),
     year int(4),
   
     PRIMARY KEY(IID),
     FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE CASCADE,
     FOREIGN KEY (IID) REFERENCES instructor(IID) ON DELETE CASCADE
   );
   
   create table takes(
     SID int(4),
     course_id varchar(20),
     sec_id int(4),
     semester int(4) check (semester between 1 and 2),
     year int(4),
     grade varchar(1),
   
     PRIMARY KEY(SID)
   );
   
   create table advisor(
     SID int(4),
     IID int(4),
   
     PRIMARY KEY (SID),
     FOREIGN KEY (IID) REFERENCES instructor(IID) ON DELETE CASCADE,
     FOREIGN KEY (SID) REFERENCES student(SID) ON DELETE CASCADE
   );
   
   create table prereq(
     course_id varchar(20),
     prereq_id int(4),
   
     PRIMARY KEY (prereq_id, course_id),
     FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE CASCADE,
     FOREIGN KEY (prereq_id) REFERENCES course(course_id) ON DELETE CASCADE
   );
   

   4. Alter the Instructor table to set 10,0000/- as default salary

alter table instructor ALTER salary SET DEFAULT 10000;

1. Insert some rows into all of the above tables

insert into department values
("Computer", 7, 20000),
("Biotech", 9, 50000),
("Mechanical", 3, 70000);

insert into student values
(198, "Thahir", "Computer", 100),
(199, "Someone", "Biotech", 90),
(200, "Blah", "Mechanical", 50);

insert into course values
("CS F111", "OOPS", "Computer", 3),
("Mech F111", "Thermodynamics", "Mechanical", 3),
("Bio F111", "Gen Bio", "Biotech", 3);

insert into instructor values
(102, "ABC", "Computer", 5000),
(104, "GHI", "Mechanical", 6050.9),
(103, "DEF", "Biotech", 7000);

insert into teaches values
(102, "CS F111", 3, 2, 2020),
(103, "Bio F111", 2, 1, 2020),
(104, "Mech F111", 1, 2, 2019);

insert into takes values
(198, "CS F111", 3, 2, 2020),
(199, "Bio F111", 2, 1, 2020),
(200, "Mech F111", 1, 2, 2019);

insert into advisor values
(198, 102),
(199, 103),
(200, 104);

insert into prereq values
("CS F111", "Bio F111"),
("Mech F111", "Bio F111");

1. Insert a row into ‘takes’ table with the semester value as 3 or above and view the effect of constraints.

insert into takes values
(201, 111, 3, 10, 2019, 'D');

1. Delete some rows to view the effect of foreign key constraints.

delete from student;

1. TRUNCATE and DROP tables.

drop table prereq;
TRUNCATE TABLE advisor;

Comments