dq5GwF57

1drop database if exists pesudb;
2create database pesudb;
3
4
5\c pesudb
6
7drop table if exists student;
8create table student(id varchar(5) not null primary key, name varchar(20), dep_name varchar(20) , tot_credits numeric(3,0));
9
10drop table if exists dept;
11create table dept(dep_name varchar(20) , building varchar(15) , budget numeric(12,2),primary key(dep_name));
12
13
14drop table if exists classroom;
15create table classroom(building varchar(15),room_no varchar(7),capacity numeric(4,0),primary key(building,room_no));
16
17
18
19drop table if exists course;
20create table course(course_id varchar(8),title varchar(20),dep_name varchar(20),credits numeric(2,0),primary key(course_id),foreign key(dep_name) references dept);
21
22drop table if exists instructor;
23create table instructor(i_id varchar(5),name varchar(20),dep_name varchar(20),salary numeric(8,2),primary key(i_id),foreign key(dep_name) references dept);
24
25drop table if exists section;
26create table section(course_id varchar(8) , sec_id varchar(8),sem varchar(6),year varchar(15),building varchar(15),room_no varchar(7),time_slot_id varchar(4),primary key(course_id,sec_id,sem,year),foreign key(course_id) references course,foreign key(building,room_no) references classroom);
27
28drop table if exists time_slot;
29create table time_slot(time_slot_id varchar(4),day varchar(1),start_time numeric(2),end_time numeric(2),primary key(time_slot_id,day,start_time));
30
31
32drop table if exists takes;
33create table takes(id varchar(5) not null, course_id varchar(8),sec_id varchar(8),sem varchar(6),year varchar(15),grade varchar(4),primary key(id,course_id,sec_id,sem),foreign key(course_id,sec_id,sem,year) references section,foreign key(id) references student);
34
35
36
37
38
39
40
41
42
43
44
45
46drop table if exists teaches;
47create table teaches(i_id varchar(5),course_id varchar(8),sec_id varchar(6),sem varchar(6),year varchar(15),primary key(i_id,course_id,sec_id,sem,year),foreign key(i_id) references instructor,foreign key(course_id,sec_id,sem,year) references section);
48
49 
50
51
52
53
54
55
56
57drop table if exists advisor;
58create table advisor(id varchar(5),i_id varchar(5),primary key(id),foreign key(id) references student,foreign key(i_id) references instructor);
59
60
61
62
63
64
65drop table if exists prereq;
66create table prereq(course_id varchar(8),prereq_id varchar(8),primary key(course_id,prereq_id),foreign key(course_id) references course,foreign key(prereq_id) references course(course_id));