SQL Table

drop database if exists University;

create database University;

use University;

create table department(

dept_name varchar(20),
building  varchar(15),
budget    numeric(12, 2) check (budget > 0),
constraint dept
    primary key(dept_name)

);

create table instructor(

dept_name varchar(20),
id varchar(5),
name varchar(20) not null,
salary numeric(8, 2),
constraint dept2
    foreign key(dept_name) references department(dept_name)
        on delete set null,
primary key(id)
);

create table course(

course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0) check (credits > 0),

constraint dept3
    foreign key(dept_name) references department(dept_name)
        on delete set null,
primary key(course_id)

);

create table classroom(

building varchar(15),
room_number varchar(7),
capacity numeric(4, 0),
primary key(building,room_number)
);


create table section(

course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year2 numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),

constraint coursecon
    foreign key(course_id) references course(course_id)
        on delete cascade,
constraint struct
    foreign key(building) references classroom(building)
        on delete set null,
constraint room
    foreign key(room_number) references classroom(room_number)
        on delete set null,
primary key(sec_id,semester,year2)
);

create table teaches(

id varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year2 numeric(4,0),

constraint course
    foreign key(course_id) references department(course_id)
        on delete cascade,

constraint section
    foreign key(sec_id) references department(sec_id)
        on delete cascade,

constraint sem
    foreign key(semester) references department(semester)
        on delete cascade,

constraint year1
    foreign key(year2) references section(year2)
        on delete cascade,

constraint identity
    foreign key(id) references instructor(id)
        on delete cascade,
primary key(id,course_id,sec_id,semester,year2)
);


create table student(

id varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0),
constraint dept4
    foreign key(dept_name) references department(dept_name)
        on delete set null


);

create table takes(

id varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),
constraint cours
    foreign key(course_id) references section(course_id)
        on delete cascade,
constraint sec
    foreign key(sec_id) references section(sec_id)
        on delete cascade,
constraint sem
    foreign key(semester) references section(semester)
        on delete cascade,
constraint time
    foreign key(year) references section(year)
        on delete cascade,
constraint iid
    foreign key(id) references student(id)
        on delete cascade,
primary key(id,course_id,sec_id,semester,year)
);

create table advisor(

s_id varchar(5),
i_id varchar(5),
constraint iid
    foreign key(i_id) references instructor(id)
        on delete set null,
constraint sid
    foreign key(s_id) references student(id)
        on delete cascade,
primary key(s_id)
);

create table prereq(

course_id varchar(8),
prereq_id varchar(8),

constraint courid
    foreign key(course_id) references course(course_id)
        on delete cascade,
constraint prereq2
    foreign key(prereq_id) references course(course_id),

primary key(course_id,prereq_id)

);

create table timeslot(

time_slot_id varchar(5),
day varchar(1),
start_hr int,
start_min int,
end_hr int,
end_min int,
primary key(time_slot_id,day,start_hr,start_min)	                                 	        	
);

load data infile 'i:/advisor.txt' into table advisor fields terminated by ';';
load data infile 'i:/classroom.txt'into table classroom fields terminated by ';';
load data infile 'i:/course.txt' into table course fields terminated by ';';
load data infile 'i:/department.txt' into table department fields terminated by ';';
load data infile 'i:/instructor.txt' into table instructor fields terminated by ';';
load data infile 'i:/prereq.txt' into table prereq fields terminated by ';';
load data infile 'i:/section.txt' into table section fields terminated by ';';
load data infile 'i:/student.txt' into table student fields terminated by ';';
load data infile 'i:/takes.txt' into table takes fields terminated by ';';
load data infile 'i:/teaches.txt' into table teaches fields terminated by ';';
load data infile 'i:/timeSlot.txt' into table timeSlot fields terminated by ';';

Select allOpen in new window