Detailed University Schema
These attributes also have constraints that ensure that only numbers representing valid
time values appear in those attributes. This version of the schema for time...slot works
on all databases, including Oracle. Note that although Oracle supports the datetime
datatype, datetime includes a specific day, month, and year as well as a time, and is
not appropriate here since we want only a time. There are two alternatives to splitting
the time attributes into an hour and a minute component, but neither is desirable. The
first alternative is to use a varchar type, but that makes it hard to enforce validity con
straints on the string as well as to perform comparison on time. The second alternative
is to encode time as an integer representing a number of minutes ( or seconds) from
midnight, but this alternative requires extra code with each query to convert values be
tween the standard time representation and the integer encoding. We therefore choose
the two-part solution.
Sample Data
building I room_number capacity
Packard 101 500
Painter 514 10
Taylor 3128 70
Watson 100 30
Watson 120 50
Figure A.2 The classroom relation.
dept_name I building I budget
Biology Watson 90000
Comp. Sci. Taylor 100000
Elec. Eng. Taylor 85000
Finance Painter 120000
History Painter 50000
Music Packard 80000
Physics Watson 70000
Figure A.3 The department relation.
Sample Data 2
I course_id I title deptJzame I credits
BIO-101 Intro. to Biology Biology 4
BIO-301 Genetics Biology 4
BIO-399 Computational Biology Biology 3
CS-101 Intro. to Computer Science Comp. Sci. 4
CS-190 Game Design Comp. Sci. 4
CS-315 Robotics Comp. Sci. 3
CS-319 Image Processing Comp. Sci. 3
CS-347 Database System Concepts Comp. Sci. 3
EE-181 Intro. to Digital Systems Elec. Eng. 3
FIN-201 Investment Banking Finance 3
HIS-351 World History History 3
MU-199 Music Video Production Music 3
PHY-101 Physical Principles Physics 4
Figure A.4 The course relation.
ID I name I dept_name I salary
10101 Srinivasan Comp. Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein Physics 95000
32343 El Said History 60000
33456 Gold Physics 87000
45565 Katz Comp. Sci. 75000
58583 Califieri History 62000
76543 Singh Finance 80000
76766 Crick Biology 72000
83821 Brandt Comp. Sci. 92000
98345 Kim Elec. Eng. 80000
Figure A.5 The instructor relation.
A.3 Sample Data 1297
time....sloUd I day staruime end_time I
A M 8:00 8:50
A w 8:00 8:50
A F 8:00 8:50
B M 9:00 9:50
B w 9:00 9:50
B F 9:00 9:50
C M 11:00 11:50
C w 11:00 11:50
C F 11:00 11:50
D M 13:00 13:50
D w 13:00 13:50
D F 13:00 13:50
E T 10:30 11:45
E R 10:30 11:45
F T 14:30 15:45
F R 14:30 15:45
G M 16:00 16:50
G w 16:00 16:50
G F 16:00 16:50
H w 10:00 12:30
Figure A.11 The time...slot relation.
I course_id I prereq_id
BIO-301 BIO-101
BIO-399 BIO-101
CS-190 CS-101
CS-315 CS-101
CS-319 CS-101
CS-347 CS-101
EE-181 PHY-101
Figure A.12 The prereq relation.
