College course registration System ER
model
For the sake of simplicity, we assume that id's of all people involved, be it students, instructors,
TA's, or LA's are unique; e.g. we cannot have a student with student id 17 and an instructor with
instructor id 17.
Create an ER model for a database for a college to deal with
student course registrations. Any name in the model, be it name
of an entity or an attribute or a relationship, that is not self-
explanatory, must be explained in the notes at the bottom of
each page or at the end of the model. Thus, if a course has a title
and the attribute is called Title, you do not need to explain it.
However if you call it Titulek, you had better explain it in the
notes (BTW titulek is Czech for title, if you wandered). For the
sake of simplicity, we assume that id's of all people involved, be
it students, instructors, TA's, or LA's are unique; e.g. we cannot
have a student with student id 17 and an instructor with
instructor id 17.
All study programmes in the college take 4 years. In the
following unique means unique within our system we are
designing.
Each course has a unique designation, title, description, year (in
which year of study the course is to be taken, for instance 2nd
year course), and classroom. A course can have no or many
tutorial sections, and no or many lab sections. Each course is
taught by exactly one instructor. Each instructor has a unique id,
name, departmental affiliation, office room, phone extension,
and a unique email address. Each student has a unique id, name,
and the year of his/her study. A student cannot be an instructor.
A course can have zero or many tutorial sections unique to the
course (i.e. tutorial sections are not shared by different courses).
Each tutorial section has exactly one TA assigned. A TA can
tutor more than one tutorial section for the same course, and any
number of tutorials for different courses. A TA cannot be an
instructor, however a student can work as a TA (in that case
his/her student id is used as TA id). A course can have zero or
many lab sections unique to the course (i.e. lab sections are not
shared by different courses). Each lab section has exactly one
LA assigned. An LA can oversee more than one lab section for
the same course, and any number of labs for different courses.
An LA cannot be an instructor, however a student can work as a
LA (in which case his/her student id is used as the LA id). In
fact, a student can work as a TA and an LA simultaneously.
Thus, a TA may or may not be a student, an LA may or may not
be a student. A person can work as both, a TA and an LA. TA
has the same attributes as instructor, the same goes for LA.
Each course has 0 to many courses designated as its
prerequisites and zero to many courses designated s its
antirequisites. Prerequisite courses are of the same or lower
year, antirequisite courses are of the same year. In the system we
keep information of what courses a student has taken and what
courses the student is registering. All courses are either Pass or
Fail. A student can register a course only if he/she has passed all
the prerequisites and has not passed any or is not registered in
any of the antirequisites. A student can only register a course of
the appropriate year, i.e. a student in year X of study can only
register and take courses of year X.
Your task is to provide a ER model for a database for the above
system. Some of the aspects of the informal description of the
system to be designed will not be reflected in your ER model. At
the end of the model, in writing, you must identify which of the
aspects of the description are not captured in your model. Of
course, you must strive for as complete model as possible.
ER model 1: fewer tables, easier to work with, does not model
as much as model 2
ER model 2: more tables, harder to work with, models almost
everything required
College course registration System relational
model, db2 schema, data entry, and interactive
queries
The task is to create a relational model based on the ER-model,
a db2 schema script, the database will be populate by a script,
and queried by a csript (the 7 queries are described below), and
finally the database will be deleted by a script.
Datatypes required:
1. course designation : char(5)
2. course title : char(30)
3. course description : char(150)
4. course classroom : char(5)
5. course status : char(1)
6. instructor/TA/LA/student id : char(7)
7. department affiliation : char(4)
8. instructor/TA/LA room : char(5)
9. instructor/TA/LA extension : char(5)
10. instructor/TA/LA email : char(20)
11. student year : integer
12. course year : integer
13. tutorial/lab section : integer
14. tutorial classroom : char(5)
15. lab labroom : char(5)
The data to be entered (the values are designed so that they can
be created in a word processor as easily as possible): we have
altogether 60 different people in the system
they have id's ranging from '0000001' to '0000060'. A person
with id '00000x' has name 'PERSONx'. Persons with id's 1- 10,
22-24, 28-29 are TA's, persons 8-16, 25-29 are LA's, persons
17-21 are instructors, persons 22-30 are students in year 4,
persons 31-40 are students in year 3, persons 41-50 are students
in year 2, and 51-60 are students in year 1. Every
instructor/TA/LA has as departmental affiliation 'CAS', has
email 'ex@cas' where x is the id (i.e. e1.@cas for person with id
'0000001'). All instructors have distinct extension, the first has
extension '12345' the next '12346' etc. The first two instructors
are in room 'IT67' the next three in separate rooms 'IT68' ..
'IT70'. The first 5 TA/LA's are in rooms 'IT71a', 'IT71b' ...
'IT71e' and have the same extension '12350'. The next five
TA/LA's are in rooms 'IT72a', 'IT72b' ... 'IT72e' with extension
'12351'. The next three TA/LA's are in rooms 'IT73a'..'IT73c'
with extension '12352'. The next three TA/LA's are in rooms
'IT74a'..'IT74c' with extension '12353'. The next five TA/LA's
are in rooms 'IT75a'..'IT75e' with extension '12354'. The next
three TA/LA's are in rooms 'IT76a'..'IT76c' with extension
'12355'.
Every course has designation 'CSCx' where x is 1..16, and title
'COMPUTER SCIENCE COURSE x', and description 'x..
course in cs' (x.. stands for 1st, 2nd, 3rd, 4th ... 16th), and room
'IT-x' (i.e. 'IT-1'.. 'IT-16'). The first 4 courses are year 1 courses,
the next 4 courses are year 2 courses, the next 4 courses are year
3 courses, and finally, the last 4 courses are year 4 courses.
Prerequisites: 1->3, (CSC1 is a prerequisite for CSC3), 2->4, 3-
>6, 4->5, 6->7, 5->7, 5->8, 6->9, 5->10, 9->12, 10->11, 11-
>15,11->16,12->15,12->16,13->15,13->16,14->15,14->16
Antirequisites: 1->2 (CSC1 is an antirequisite for CSC2), 2->1,
3->4, 4->3, 9->10, 10->9, 11->12, 12->11, 13->15, 15->13, 14-
>16, 16->14
CSC1..CSC4 have 2 section labs in room 'LABx' (e.g. CSC1 has
two lab sections in LAB1), CSC5..CSC8 have 1 section labs in
rooms 'LAB1' .. 'LAB4' (e.g. CSC5 has single lab section in
LAB1), CSC1..CSC4 have 3 section tutorials in rooms 'TUT1'
and 'TUT2' (CSC1 in TUT1, CSC2 in TUT2, CSC3 in TUT1 ...,
the tutorial rooms alternate), CSC5..CSC16 have 1 tutorial
section in 'TUT1' or 'TUT2' (the tutorial rooms keep alternating).
Assignment of TA's to tutorials: (course, section, TA's id)
('CSC1',1,'0000001'),
('CSC1',2,'0000002'),
('CSC1',3,'0000003'),
('CSC2',1,'0000004'),
('CSC2',2,'0000005'),
('CSC2',3,'0000006'),
('CSC3',1,'0000001'),
('CSC3',2,'0000002'),
('CSC3',3,'0000007'),
('CSC4',1,'0000004'),
('CSC4',2,'0000005'),
('CSC4',3,'0000007'),
('CSC5',1,'0000022'),
('CSC6',1,'0000023'),
('CSC7',1,'0000024'),
('CSC8',1,'0000028'),
('CSC9',1,'0000029'),
('CSC10',1,'0000001'),
('CSC11',1,'0000002'),
('CSC12',1,'0000003'),
('CSC13',1,'0000004'),
('CSC14',1,'0000005'),
('CSC15',1,'0000006'),
('CSC16',1,'0000007');
Assignment of LA's to labs: (course, section, LA's id)
('CSC1',1,'0000008'),
('CSC1',2,'0000009'),
('CSC2',1,'0000010'),
('CSC2',2,'0000011'),
('CSC3',1,'0000008'),
('CSC3',2,'0000009'),
('CSC4',1,'0000010'),
('CSC4',2,'0000011'),
('CSC5',1,'0000025'),
('CSC6',1,'0000027'),
('CSC7',1,'0000028'),
('CSC8',1,'0000016');
Assignment of Instructors to courses: (course,Instructor's id)
('CSC1','0000017'),
('CSC2','0000018'),
('CSC3','0000019'),
('CSC4','0000020'),
('CSC5','0000021'),
('CSC6','0000017'),
('CSC7','0000018'),
('CSC8','0000019'),
('CSC9','0000020'),
('CSC10','0000021'),
('CSC11','0000017'),
('CSC12','0000018'),
('CSC13','0000019'),
('CSC14','0000020'),
('CSC15','0000021'),
('CSC16','0000017');
Students courses: (student id,course,status)
('0000022','CSC1','P'),
('0000022','CSC2','P'),
('0000022','CSC3','P'),
('0000022','CSC4','P'),
('0000023','CSC1','P'),
('0000023','CSC2','P'),
('0000023','CSC3','P'),
('0000023','CSC4','P'),
('0000024','CSC1','P'),
('0000024','CSC2','P'),
('0000024','CSC3','P'),
('0000024','CSC4','P'),
('0000025','CSC1','P'),
('0000025','CSC2','P'),
('0000025','CSC3','P'),
('0000025','CSC4','P'),
('0000026','CSC1','P'),
('0000026','CSC2','P'),
('0000026','CSC3','P'),
('0000026','CSC4','P'),
('0000027','CSC1','P'),
('0000027','CSC2','P'),
('0000027','CSC3','P'),
('0000027','CSC4','P'),
('0000028','CSC1','P'),
('0000028','CSC2','P'),
('0000028','CSC3','P'),
('0000028','CSC4','P'),
('0000029','CSC1','P'),
('0000029','CSC2','P'),
('0000029','CSC3','P'),
('0000029','CSC4','P'),
('0000030','CSC1','P'),
('0000030','CSC2','P'),
('0000030','CSC3','P'),
('0000030','CSC4','P'),
('0000031','CSC1','P'),
('0000031','CSC2','P'),
('0000031','CSC3','P'),
('0000031','CSC4','P'),
('0000032','CSC1','P'),
('0000032','CSC2','P'),
('0000032','CSC3','P'),
('0000032','CSC4','P'),
('0000033','CSC1','P'),
('0000033','CSC2','P'),
('0000033','CSC3','P'),
('0000033','CSC4','P'),
('0000034','CSC1','P'),
('0000034','CSC2','P'),
('0000034','CSC3','P'),
('0000034','CSC4','P'),
('0000035','CSC1','P'),
('0000035','CSC2','P'),
('0000035','CSC3','P'),
('0000035','CSC4','P'),
('0000036','CSC1','P'),
('0000036','CSC2','P'),
('0000036','CSC3','P'),
('0000036','CSC4','P'),
('0000037','CSC1','P'),
('0000037','CSC2','P'),
('0000037','CSC3','P'),
('0000037','CSC4','P'),
('0000038','CSC1','P'),
('0000038','CSC2','P'),
('0000038','CSC3','P'),
('0000038','CSC4','P'),
('0000039','CSC1','P'),
('0000039','CSC2','P'),
('0000039','CSC3','P'),
('0000039','CSC4','P'),
('0000040','CSC1','P'),
('0000040','CSC2','P'),
('0000040','CSC3','P'),
('0000040','CSC4','P'),
('0000041','CSC1','P'),
('0000041','CSC2','P'),
('0000041','CSC3','P'),
('0000041','CSC4','P'),
('0000042','CSC1','P'),
('0000042','CSC2','P'),
('0000042','CSC3','P'),
('0000042','CSC4','P'),
('0000043','CSC1','P'),
('0000043','CSC2','P'),
('0000043','CSC3','P'),
('0000043','CSC4','P'),
('0000044','CSC1','P'),
('0000044','CSC2','P'),
('0000044','CSC3','P'),
('0000044','CSC4','P'),
('0000045','CSC1','P'),
('0000045','CSC2','P'),
('0000045','CSC3','P'),
('0000045','CSC4','P'),
('0000046','CSC1','P'),
('0000046','CSC2','P'),
('0000046','CSC3','P'),
('0000046','CSC4','P'),
('0000047','CSC1','P'),
('0000047','CSC2','P'),
('0000047','CSC3','P'),
('0000047','CSC4','P'),
('0000048','CSC1','P'),
('0000048','CSC2','P'),
('0000048','CSC3','P'),
('0000048','CSC4','P'),
('0000049','CSC1','P'),
('0000049','CSC2','P'),
('0000049','CSC3','P'),
('0000049','CSC4','P'),
('0000050','CSC1','P'),
('0000050','CSC2','P'),
('0000050','CSC3','P'),
('0000050','CSC4','P'),
('0000051','CSC1','R'),
('0000051','CSC2','R'),
('0000051','CSC3','R'),
('0000051','CSC4','R'),
('0000052','CSC1','R'),
('0000052','CSC2','R'),
('0000052','CSC3','R'),
('0000052','CSC4','R'),
('0000053','CSC1','R'),
('0000053','CSC2','R'),
('0000053','CSC3','R'),
('0000053','CSC4','R'),
('0000054','CSC1','R'),
('0000054','CSC2','R'),
('0000054','CSC3','R'),
('0000054','CSC4','R'),
('0000055','CSC1','R'),
('0000055','CSC2','R'),
('0000055','CSC3','R'),
('0000055','CSC4','R'),
('0000056','CSC1','R'),
('0000056','CSC2','R'),
('0000056','CSC3','R'),
('0000056','CSC4','R'),
('0000057','CSC1','R'),
('0000057','CSC2','R'),
('0000057','CSC3','R'),
('0000057','CSC4','R'),
('0000058','CSC1','R'),
('0000058','CSC2','R'),
('0000058','CSC3','R'),
('0000058','CSC4','R'),
('0000059','CSC1','R'),
('0000059','CSC2','R'),
('0000059','CSC3','R'),
('0000059','CSC4','R'),
('0000060','CSC1','R'),
('0000060','CSC2','R'),
('0000060','CSC3','R'),
('0000060','CSC4','R');
('0000022','CSC6','P'),
('0000022','CSC7','P'),
('0000022','CSC8','P'),
('0000022','CSC5','P'),
('0000023','CSC6','P'),
('0000023','CSC7','P'),
('0000023','CSC8','P'),
('0000023','CSC5','P'),
('0000024','CSC6','P'),
('0000024','CSC7','P'),
('0000024','CSC8','P'),
('0000024','CSC5','P'),
('0000025','CSC6','P'),
('0000025','CSC7','P'),
('0000025','CSC8','P'),
('0000025','CSC5','P'),
('0000026','CSC6','P'),
('0000026','CSC7','P'),
('0000026','CSC8','P'),
('0000026','CSC5','P'),
('0000027','CSC6','P'),
('0000027','CSC7','P'),
('0000027','CSC8','P'),
('0000027','CSC5','P'),
('0000028','CSC6','P'),
('0000028','CSC7','P'),
('0000028','CSC8','P'),
('0000028','CSC5','P'),
('0000029','CSC6','P'),
('0000029','CSC7','P'),
('0000029','CSC8','P'),
('0000029','CSC5','P'),
('0000030','CSC6','P'),
('0000030','CSC7','P'),
('0000030','CSC8','P'),
('0000030','CSC5','P'),
('0000031','CSC6','P'),
('0000031','CSC7','P'),
('0000031','CSC8','P'),
('0000031','CSC5','P'),
('0000032','CSC6','P'),
('0000032','CSC7','P'),
('0000032','CSC8','P'),
('0000032','CSC5','P'),
('0000033','CSC6','P'),
('0000033','CSC7','P'),
('0000033','CSC8','P'),
('0000033','CSC5','P'),
('0000034','CSC6','P'),
('0000034','CSC7','P'),
('0000034','CSC8','P'),
('0000034','CSC5','P'),
('0000035','CSC6','P'),
('0000035','CSC7','P'),
('0000035','CSC8','P'),
('0000035','CSC5','P'),
('0000036','CSC6','P'),
('0000036','CSC7','P'),
('0000036','CSC8','P'),
('0000036','CSC5','P'),
('0000037','CSC6','P'),
('0000037','CSC7','P'),
('0000037','CSC8','P'),
('0000037','CSC5','P'),
('0000038','CSC6','P'),
('0000038','CSC7','P'),
('0000038','CSC8','P'),
('0000038','CSC5','P'),
('0000039','CSC6','P'),
('0000039','CSC7','P'),
('0000039','CSC8','P'),
('0000039','CSC5','P'),
('0000040','CSC6','P'),
('0000040','CSC7','P'),
('0000040','CSC8','P'),
('0000040','CSC5','P'),
('0000041','CSC6','R'),
('0000041','CSC7','R'),
('0000041','CSC8','R'),
('0000041','CSC5','R'),
('0000042','CSC6','R'),
('0000042','CSC7','R'),
('0000042','CSC8','R'),
('0000042','CSC5','R'),
('0000043','CSC6','R'),
('0000043','CSC7','R'),
('0000043','CSC8','R'),
('0000043','CSC5','R'),
('0000044','CSC6','R'),
('0000044','CSC7','R'),
('0000044','CSC8','R'),
('0000044','CSC5','R'),
('0000045','CSC6','R'),
('0000045','CSC7','R'),
('0000045','CSC8','R'),
('0000045','CSC5','R'),
('0000046','CSC6','R'),
('0000046','CSC7','R'),
('0000046','CSC8','R'),
('0000046','CSC5','R'),
('0000047','CSC6','R'),
('0000047','CSC7','R'),
('0000047','CSC8','R'),
('0000047','CSC5','R'),
('0000048','CSC6','R'),
('0000048','CSC7','R'),
('0000048','CSC8','R'),
('0000048','CSC5','R'),
('0000049','CSC6','R'),
('0000049','CSC7','R'),
('0000049','CSC8','R'),
('0000049','CSC5','R'),
('0000050','CSC6','R'),
('0000050','CSC7','R'),
('0000050','CSC8','R'),
('0000050','CSC5','R');
('0000022','CSC9','P'),
('0000022','CSC10','P'),
('0000022','CSC11','P'),
('0000022','CSC12','P'),
('0000023','CSC9','P'),
('0000023','CSC10','P'),
('0000023','CSC11','P'),
('0000023','CSC12','P'),
('0000024','CSC9','P'),
('0000024','CSC10','P'),
('0000024','CSC11','P'),
('0000024','CSC12','P'),
('0000025','CSC9','P'),
('0000025','CSC10','P'),
('0000025','CSC11','P'),
('0000025','CSC12','P'),
('0000026','CSC9','P'),
('0000026','CSC10','P'),
('0000026','CSC11','P'),
('0000026','CSC12','P'),
('0000027','CSC9','P'),
('0000027','CSC10','P'),
('0000027','CSC11','P'),
('0000027','CSC12','P'),
('0000028','CSC9','P'),
('0000028','CSC10','P'),
('0000028','CSC11','P'),
('0000028','CSC12','P'),
('0000029','CSC9','P'),
('0000029','CSC10','P'),
('0000029','CSC11','P'),
('0000029','CSC12','P'),
('0000030','CSC9','P'),
('0000030','CSC10','P'),
('0000030','CSC11','P'),
('0000030','CSC12','P'),
('0000031','CSC9','R'),
('0000031','CSC10','R'),
('0000031','CSC11','R'),
('0000031','CSC12','R'),
('0000032','CSC9','R'),
('0000032','CSC10','R'),
('0000032','CSC11','R'),
('0000032','CSC12','R'),
('0000033','CSC9','R'),
('0000033','CSC10','R'),
('0000033','CSC11','R'),
('0000033','CSC12','R'),
('0000034','CSC9','R'),
('0000034','CSC10','R'),
('0000034','CSC11','R'),
('0000034','CSC12','R'),
('0000035','CSC9','R'),
('0000035','CSC10','R'),
('0000035','CSC11','R'),
('0000035','CSC12','R'),
('0000036','CSC9','R'),
('0000036','CSC10','R'),
('0000036','CSC11','R'),
('0000036','CSC12','R'),
('0000037','CSC9','R'),
('0000037','CSC10','R'),
('0000037','CSC11','R'),
('0000037','CSC12','R'),
('0000038','CSC9','R'),
('0000038','CSC10','R'),
('0000038','CSC11','R'),
('0000038','CSC12','R'),
('0000039','CSC9','R'),
('0000039','CSC10','R'),
('0000039','CSC11','R'),
('0000039','CSC12','R'),
('0000040','CSC9','R'),
('0000040','CSC10','R'),
('0000040','CSC11','R'),
('0000040','CSC12','R');
('0000022','CSC13','R'),
('0000022','CSC14','R'),
('0000022','CSC15','R'),
('0000022','CSC16','R'),
('0000023','CSC13','R'),
('0000023','CSC14','R'),
('0000023','CSC15','R'),
('0000023','CSC16','R'),
('0000024','CSC13','R'),
('0000024','CSC14','R'),
('0000024','CSC15','R'),
('0000024','CSC16','R'),
('0000025','CSC13','R'),
('0000025','CSC14','R'),
('0000025','CSC15','R'),
('0000025','CSC16','R'),
('0000026','CSC13','R'),
('0000026','CSC14','R'),
('0000026','CSC15','R'),
('0000026','CSC16','R'),
('0000027','CSC13','R'),
('0000027','CSC14','R'),
('0000027','CSC15','R'),
('0000027','CSC16','R'),
('0000028','CSC13','R'),
('0000028','CSC14','R'),
('0000028','CSC15','R'),
('0000028','CSC16','R'),
('0000029','CSC13','R'),
('0000029','CSC14','R'),
('0000029','CSC15','R'),
('0000029','CSC16','R'),
('0000030','CSC13','R'),
('0000030','CSC14','R'),
('0000030','CSC15','R'),
('0000030','CSC16','R');
Sample of relational schema notation to be used in the
assignment (not the colours)
person ( Id : char(7), Name : char(20) ) PK(Id)
instructor ( Id : char(7) FK( person ), Dept : char(4), Room : char(5), Extension :
char(5), Email : char(20) ) PK(Id)
staff ( Id : char(7) FK( person ), Dept : char(4), Room : char(5), Extension : char(5),
Email : char(20) ) PK(Id)
student ( Id : char(7) FK( person ), Year : integer ) PK(Id)
lab ( Desig : char(5) FK( course ), Section : integer, Labroom : char(5) )
PK(Desig,Section)
tutorial ( Desig : char(5) FK( course ), Section : integer, Classroom : char(5) )
PK(Desig,Section)
TA ( Id : char(7) FK( staff ) ) PK(Id)
LA ( Id : char(7) FK( staff ) ) PK(Id)
hasTA ( Desig : char(5) FK( tutorial ), Section : integer FK( tutorial ), Id : char(7)
FK( TA ) ) PK(Desig,Section)
hasLA ( Desig : char(5) FK( lab ), Section : integer FK( lab ), Id : char(7) FK( LA ) )
PK(Desig,Section)
works_as ( Id1 : char(7) FK( Id of student ), Id2 : char(7) FK(Id of staff) )
PK(Id1,Id2), constraint: Id1=Id2
The queries:
Query 1
List all triples (student name, course designation, status)
of courses taken or registered by students with id '0000041' and
'0000042'. status is the status of each course (i.e. 'P' for
passed,
'F' for failed, 'R' for registered).
Query 2
List names of all students in year 1
Query 3
List names of all instructors teaching year 1 courses
Query 4
List designation of all courses that have tutorials
No designation can repeat
Query 5
List designation of all courses that have labs with more than 1
section
No designation can repeat
Query 6
List names of instructors that teach at least one course
with multiple-sections labs
No name can repeat
Query 7
List names of instructors that teach only courses
with single-sections labs or no labs
No name can repeat
This is what the 7 queries should produce as results:
Query 1
NAME DESIG STATUS
-------------------- ----- ------
PERSON41 CSC1 P
PERSON42 CSC1 P
PERSON41 CSC2 P
PERSON42 CSC2 P
PERSON41 CSC3 P
PERSON42 CSC3 P
PERSON41 CSC4 P
PERSON42 CSC4 P
PERSON41 CSC5 R
PERSON42 CSC5 R
PERSON41 CSC6 R
PERSON42 CSC6 R
PERSON41 CSC7 R
PERSON42 CSC7 R
PERSON41 CSC8 R
PERSON42 CSC8 R
Query 2
NAME
--------------------
PERSON51
PERSON52
PERSON53
PERSON54
PERSON55
PERSON56
PERSON57
PERSON58
PERSON59
PERSON60
Query 3
NAME
--------------------
PERSON17
PERSON18
PERSON19
PERSON20
Query 4
DESIG
-----
CSC1
CSC10
CSC11
CSC12
CSC13
CSC14
CSC15
CSC16
CSC2
CSC3
CSC4
CSC5
CSC6
CSC7
CSC8
CSC9
Query 5
DESIG
-----
CSC1
CSC2
CSC3
CSC4
Query 6
NAME
--------------------
PERSON17
PERSON18
PERSON19
PERSON20
Query 7
NAME
--------------------
PERSON21
Solution based on ER-model 1, solution based on ER-model 2.
College course registration System Application
Program in C:
The task is to write a simple C program that connects to the
database created previously and using the technique of
embedded SQL execute the 7 queries given above and display
the result of each query on the screen (for that, the use of
CURSOR is need).
Solution: the program, db2precomp, db2comp, db2bind,
Makefile
Course Registration System - Case Study
This case study is an excerpt from Rational's documentation at
[Link]
Problem Statement
At the beginning of each semester students may request a course catalogue containing a list of course
offerings for the semester. Information about each course, such as professor, department, and
prerequisites will be included to help students make informed decisions.
The new on-line registration system will allow students to select four course offerings for the
coming semester. In addition, each student will indicate two alternative choices in case a course
offering becomes filled or canceled. No course offering will have more than ten students. No
course offering will have fewer than three students. A course offering with fewer than three
students will be canceled. Once the registration process is completed for a student, the
registration system sends information to the billing system, so the student can be billed for the
semester.
Professors must be able to access the on-line system to indicate which courses they will be
teaching. They will also need to see which students signed up for their course offering.
For each semester, there is a period of time that students can change their schedules. Students
must be able to access the on-line system during this time to add or drop courses. The billing
system will credit all students for courses dropped during this period of time.
Use Case Model
List of Use Cases (grouped by actors)
Student--someone who is registered to take courses at the University.
o Register for courses.
Professor--someone who is licensed to teach at the University.
o Select courses to teach.
o Request course offering roster.
Registrar--someone who is responsible for the maintenance of the Registration System.
o Generate course catalogue.
o Maintain professor information.
o Maintain student information.
o Maintain curriculum.
Billing System--external system that bills students each semester.
o No use cases
Use Case Descriptions (brief)
Register for courses
o The use case is started by the student. It provides the capability to create, review,
modify, and delete a course schedule for a specified semester. All pertinent billing
information is sent to the Billing System.
Request class roster
o This use case is started by the professor. It provides the capability to request a printed
list of all students assigned to a specified course offering.
Select courses to teach
o This use case is started by the professor. It provides the capability to select, review,
modify, and delete a list of courses to teach for a specified semester.
Maintain professor information
o This use case is started by the registrar. It provides the capability to create, review,
modify, and delete professor information.
Maintain student information
o This use case is started by the registrar. It provides the capability to create, review,
modify, and delete student information.
Maintain curriculum
o This use case is started by the registrar. It provides the capability to create, review,
modify, and delete a list of course offerings for a given semester.
Generate catalogue
o This use case is started by the registrar. It provides the capability to generate a
catalogue containing a list of course offerings for a specified semester.
Register for Courses Use Case
Flow of events:
This use case begins when the student enters the student id number. The system verifies that the
student id number is valid and prompts the student to select the current semester or a future semester.
The student enters the desired semester. The system prompts the student to select the desired activity:
Create a schedule.
Review a schedule.
Change a schedule:
o Delete a course.
o Add a course.
The student indicates that the activity is complete. The system will print the student schedule and notify
the student that registration is complete. The system sends billing information for the student to the
billing system for processing.
Alternate flow
If an invalid id number is entered, the system will not allow access to the registration system.
If an attempt is made to create a schedule for a semester where a schedule already exists, the
system will prompt for another choice to be made.
Create a Schedule
The student enters 4 primary course offering numbers and 2 alternate course offering numbers.
The student then submits the request for courses. The system then:
1. Checks that prerequisites are satisfied for the requested course.
2. Adds the student to the course offering if the course offering is open.
Alternate flow
If a primary course offering is not available, the system will substitute an alternate course offering.
Review a Schedule
The student requests information on all course offerings in which the student is registered for a
given semester. The system displays all courses for which the student is registered including
course name, course number, course offering number, days of the week, time, location, and
number of credit hours.
Change Schedule - Delete a Course
The student indicates which course offerings to delete. The system checks that the final date for
changes has not been exceeded. The system deletes the student from the course offering. The
system notifies the student that the request has been processed.
Change Schedule - Add a Course
The student indicates which course offerings to add. The system checks that the final date for
changes has not been exceeded. The system then:
1. Verifies that the maximum course load for the student has not been exceeded.
2. Checks that prerequisites are satisfied for the requested course.
3. Adds the student to the course offering if the course offering is open.
