=========================preview======================
(COMP231)[2011](s)midterm~1980^_75071.pdf
Back to COMP231 Login to download
======================================================
COMP 231 - Spring 2011
MIDTERM EXAM
Name:
Student ID:
UST Email:
Lecture Section:
Write your final answers in pen or ballpoint pen.
PROBLEM 1 (20%) - ER Model
The university ARR offices exam information system (EIS) has the following requirements:
(1)
A student has an ID, a name, an email address, program enrolled, and year of study. The ID is used to uniquely identify a student.

(2)
An instructor has a staff ID, a name consisting of first name, middle initial, and last name, an email address, and an office phone number. The staff ID is used to uniquely identify an instructor.

(3)
A course has a course code, a course title, number of credits, and a set of section IDs (e.g., L1, L2). The course code is unique for each course.

(4)
An exam has an exam type (e.g., midterm, final), time, and venue. For a specific course, the exam type uniquely identifies an exam.

(5)
A student may enroll in zero or more courses. A course may be taken by zero or more students.

(6)
An instructor may teach zero or more courses. A course may be taught by zero or more instructors.

(7)
An exam is associated with exactly one course. It is taken by at least one student and must be overseen by exactly one instructor.


Draw an E-R diagram to capture all requirements on EIS. All relationships must be binary.
Grading criteria:
Three entity sets (with primary key underlined): 0.5*3=1.5; One weak entity set: 0.5 Five relationship sets: 1*5=5; Ten relationship lines: 1*10=10 One composite attribute 1*1=1;One discriminator (dashed underline): 1*1 = 1 One multivalued attribute (curly brace) 1*1=1 Additional points are deducted for missing attributes.
Instructor
ID
Name first_name mid_name last_name
Email
Phone
has
Take
Oversee Course


Coursecode Title
take teach
NumberOfCredits {sectionIDs}







PROBLEM 2 (48%) - SQL, Algebra, Calculus
Given the following tables, write queries in SQL, relational algebra, and domain relational calculus: Students (ID, name, groupID); Projects (title, description); Groups (groupID, projTitle, readerID) Professors (ID, name, email); Proposed (projTitle, profID); Supervision (groupID, profID)
Query 1: Find all distinct project titles proposed by Professor Qiong Luo.
SQL (3%): select distinct projTitle from Proposed, Professors where profID = ID and name = Qiong Luo Grading Criteria: Query structure (1 point): select distinct projTitle from Proposed, Professor predicates(21 point): Condition1 in where: profID = ID (1 point) Condition2 in where: name = Qiong Luo (1 point)
algebra (3%): " "projTitle (( name QiongLuo = Professors ) ID profID = Proposed ) Grading Criteria: 1). " "name QiongLuo = Professors (1 point) 2). ID profID = Proposed (1 point) 3). projTitle (...) (1 point)
calculus (3%): { | ( ,i it p t p <> . < >Proposed ( ," ", m i me p QiongLuo e. < >Professors ))} Grading Criter