SQL-Scripts/setup.sql at master · jokaysu/SQL-Scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
-- NOTE: "insert" commands should be modified to create appropriate
-- test data.

connect to cs348

drop table course
create table course ( \
    cnum        varchar(5) not null, \
    cname       varchar(40) not null, \
    primary key (cnum) )

insert into course values ('CS448', 'Introduction to Databases')

drop table professor
create table professor ( \
    pnum        integer not null, \
    pname       varchar(20) not null, \
    office      varchar(10) not null, \
    dept        varchar(30) not null, \
    primary key (pnum) )

insert into professor values (1, 'Weddell, Grant', 'DC3346', 'CS')
insert into professor values (2, 'Ilyas, Ihab', 'DC3348', 'CO')

drop table student
create table student ( \
    snum        integer not null, \
    sname       varchar(20) not null, \
    year        integer not null, \
    primary key (snum) )

insert into student values (1, 'Jones, Fred', 4)

drop table class
create table class ( \
    cnum        varchar(5) not null, \
    term        varchar(5) not null, \
    section     integer not null, \
    pnum        integer not null, \
    primary key (cnum, term, section), \
    foreign key (cnum) references course (cnum), \
    foreign key (pnum) references professor (pnum) )

insert into class values ('CS448', 'S2006', 1, 1)
insert into class values ('CS448', 'S2006', 2, 1)

drop table enrollment
create table enrollment ( \
    snum        integer not null, \
    cnum        varchar(5) not null, \
    term        varchar(5) not null, \
    section     integer not null, \
    primary key (snum, cnum, term, section), \
    foreign key (snum) references student (snum), \
    foreign key (cnum, term, section) references class (cnum, term, section) )

insert into enrollment values (1, 'CS448', 'S2006', 2)

drop table mark
create table mark ( \
    snum        integer not null, \
    cnum        varchar(5) not null, \
    term        varchar(5) not null, \
    section     integer not null, \
    grade       integer not null, \
    primary key (snum, cnum, term, section), \
    foreign key (snum, cnum, term, section) \
    references enrollment (snum, cnum, term, section) )

drop table schedule
create table schedule ( \
    cnum        varchar(5) not null, \
    term        varchar(5) not null, \
    section     integer not null, \
    day         varchar(10) not null, \
    time        varchar(5) not null, \
    room        varchar(10) not null, \
    primary key (cnum, term, section, day, time), \
    foreign key (cnum, term, section) \
    references class (cnum, term, section) )

insert into schedule values ('CS448', 'S2006', 1, 'Monday', '09:30', 'MC4063')
insert into schedule values ('CS448', 'S2006', 1, 'Wednesday', '09:30', 'MC4063')
insert into schedule values ('CS448', 'S2006', 1, 'Friday', '09:30', 'MC4063')
insert into schedule values ('CS448', 'S2006', 2, 'Monday', '11:30', 'MC4058')
insert into schedule values ('CS448', 'S2006', 2, 'Wednesday', '11:30', 'MC4058')
insert into schedule values ('CS448', 'S2006', 2, 'Friday', '11:30', 'MC4058')

commit work