=========================preview======================
(COMP231)midterm96SU_sol.pdf
Back to COMP231 Login to download
======================================================
COMP 231 - DATABASE MANAGEMENT SYSTEMS
MIDTERM EXAM SOLUTIONS
Note that there can be multiple solutions for the questions, one solution is concisely presented.

I. Entity Relationship Diagram (Sub-Total 25 Points)
Consider a university database for the scheduling of classrooms for .nal exams.
Course has a unique course number, name, and department
Section of a course has a section number and enrollment. Each course has at least one section.
Room has a unique room number, capacity and a room name.
The name of the instructor and the department he/she belongs to are stored in the database.
A room is allocated for the exam on a section of a course on a given date and time, and the exam
is also invigilated by an instructor who teaches the section of a course during a semester of a year.

a) Draw an ER diagram to show the schema for the above set of data requirements. Explic-itly show the cardinality and participation constraints and the key attributes. (15 Points)

b) Translate the above ER diagram into a set of relations which are in Third Normal Form. (10 Points) Step1: Translate each Entity Type to a Relation.
COURSE
CourseNo Name Dept

ROOM

RoomNo RoomName Capacity


Step 2: Translate each Weak Entity Type to a Relation
SECTION
SectionNo CourseNo Enrollment


Step 3: Translate binary relationship types to Relations
TAUGHT
SectionNo CourseNo InsName Semester Year

Step 4: Translate n-ary relationship type to a relation
EXAM
SectionNo CourseNo InsName RoomNo Date Time

Third Normal Form:
List the FDs for each relation above:

CourseNo -> Name, dept
RoomNo -> RoomName, Capacity
InsName -> Dept
SectionNo, CourseNo -> Enrollment
SectionNo, CourseNo, InsName -> Semester, Year
SectionNo, CourseNo, InsName, RoomNo -> Date, Time There are no more FDs, all these FDs satisfy 3NF condition, therefore, the above relations are in 3NF.

II. Relational Algebra and Query Languages (Sub-Total 55 Points)
Consider the following relational database schema
S (S#, SNAME, STATUS, CITY) -- Supplier relation
P (P#, PNAME, COLOR, WEIGHT, CITY) -- Part relation
SP (S#, P#, QTY) -- Supplier provides Part relation

Write SQL SELECT QUERIES for the following data retrieval statements
a) Get supplier names for suppliers who supply parts with weight greater than 100 grams. (5 Points) This requires join of all three tables, hence two join conditions, and for parts with weight greater than 100 gms, you have condition Weight > 100.
SELECT SNAME
FROM S, P, SP
WHERE S.S# = SP.S# AND P.P# = SP.S# AND WEIGHT > 100

b) Get the part names which are not supplied by any of the suppliers (5 Points)
The P# in SP gives all the parts supplied by some supplier, hence to get part names not sup-plied by any supplier, we have to get P# from P relation which are not in SP relation.
SELECT PNAME
FROM P
WHERE P# NOT IN (SELECT P#

FROM SP)

c) Get the names of the suppliers who suppl