8JKXLK2w

1COSC265 Lab Test 2014
2Model solutions
3Question 1
4a) Two constraints are defined in the statement below – please note that there are other valid
5constraints.
6create table Enrolments (
7Student char(8),
8Course char(7),
9Mark integer check (mark between 0 and 100),
10Grade varchar(2),
11Year integer not null check (year between 2000 and 2014),
12primary key (Student, Course, Year),
13foreign key (Student) references Student,
14foreign key (Course) references Course);
15b) Select count(*) from enrolments;
16Result: 269 tuples
17Question 2
18a) Find titles of courses that have no current enrolments (i.e. enrolments without grades/marks).
19select title
20from course
21where not exists (select * from enrolments
22 where course=code and mark is null);
23b) For each college, show the number of courses and the number of lecturers. Assume that the
24course taught by a particular lecturer belongs to the same college the lecturer works in.
25select College, count(code) as No_courses, count(distinct lecturer) as no_lecturers
26from course join lecturer on lecturer = lecno
27group by College;
28c) Find the course from 2013 that had the highest number of enrolments. Show the course code
29only.
30select course
31from enrolments
32where year = 2013
33group by course
34having count(*) >= all (select count(*)
35 from enrolments
36 where year=2013
37 group by course);
38Result:
39COURSE COUNT(*)
40------- ---------
41COSC122 4
42d) Modify the database to show that Harry Watson has passed COSC364, and obtained the total
43mark of 79 and the grade of B+. Check that there is no mark/grade previously stored for the
44student for that course.
45set mark=79, grade='B+'
46where year=2014 and mark is null and grade is null and course='COSC364'
47 and student=(select stno from student where Name='Harry Watson');
48e) Find information about students (student number and name) who have done enough points for
49the degree. Assume that all degrees require the total of 360 points.
50select stno, name
51from student
52where 360 <= (select count(distinct course) * 15
53 from enrolments
54 where student = stno and grade is not null
55 and grade <> 'F');
56Result: 4 students
57f) Find how many students have taken (or are taking) courses taught by lecturers from a different
58college than their degree.
59select count (distinct student)
60from student,enrolments, course, lecturer
61where stno=student and lecturer=lecno and course=code
62 and degree != college;
63Result: 17
64Question 3
65a) Create the Summary view, which shows for each degree how many students there were doing
66that degree, per year. Is this view updatable? Explain why.
67create view summary
68as select degree, year, count(distinct stno) as Total_students
69from student join enrolments on stno=student
70group by degree,year
71order by degree;
72The view is not updatable, as it contains group by and count There are no primary keys.
73b) Using the Summary view, find the total number of students who studies during the period of
742012 to 2014. If a student has studied for two or three years during this period, he/she should be
75counted for each year of study separately.
76select sum(total_students)
77from summary
78where year between 2012 and 2014;
79Result: 32
80c) Any new enrolment must be for the current year. A student is allowed to enrol for a course two
81times only. Implement these constraints, and also any additional tests you think are
82necessary, via a trigger. Show the statements you used to test your trigger. Hint: for the second
83situation, you might want to try adding enrolments for William Ward (student number
8428778823): BIOL112, COSC122, ACCT211 and COSC265. Make sure your trigger would
85work for any year (i.e. not only for 2014).
86Helper query:
87select *
88from enrolments
89where student='28778823' and course in ('BIOL112','COSC122','ACCT211','COSC265');
90ENROLMENTS (Student, Course, Mark, Grade, Year)
91create or replace trigger PREVENT_ENROLMENT
92before insert on enrolments
93for each row
94declare
95 TimesTaken integer;
96 CurrentYear integer;
97begin
98 select count(*) into TimesTaken
99 from enrolments
100 where student=:new.student and course=:new.course;
101 select extract(year from sysdate) into CurrentYear
102 from dual;
103 if (TimesTaken = 2) then
104 raise_application_error (num=> -20001,
105 msg=> 'The student has already taken this course twice!');
106 elsif (:new.mark is not null) then
107 raise_application_error (num=> -20002,
108 msg=> 'The student cannot have a mark for a new course!');
109 elsif (:new.grade is not null) then
110 raise_application_error (num=> -20003,
111 msg=> 'The student cannot have a grade for a new course!');
112 elsif (CurrentYear <> :new.year) then
113 raise_application_error (num=> -20004,
114 msg=> 'The enrolment must be for the current year!');
115 end if;
116end;
117/
118Showing that the trigger works:
119ENROLMENTS (Student, Course, Mark, Grade, Year)
120insert into enrolments
121values('28778823','COSC122',null,null,2014);
122ERROR at line 1:
123ORA-20001: The student has already taken this course twice!
124insert into enrolments
125values('28778823','ENGL238',null,null,2013);
126ERROR at line 1:
127ORA-20004: The student has already taken this course twice!
128insert into enrolments
129values('28778823','SENG365',100,null,2014);
130ERROR at line 1:
131ORA-20002: 'The student cannot have a mark for a new course!'
132insert into enrolments
133values('28778823','SENG365',null,'B',2014);
134ERROR at line 1:
135ORA-20003: 'The student cannot have a grade for a new course!'
136insert into enrolments
137values('28778823','ACCT211',null,null,2014);
1381 row created.
139Question 4 Find the name of the author with the highest number of titles in the library.
140select student.Name
141from student
142where not exists (select code
143 from course join lecturer on lecturer=lecno
144 where college = 'COM' and not exists
145 (select * from enrolments
146 where student = stno and code=course));
147Result: Rick Carter