The University of Hong Kong
Faculty of Business and Economics
IIMT3601 Database Management
Written Exam


Name: Ng Yee Nok

University Number: 3035484413


Please answer all questions.

1. (30 points) ER Diagrams
Draw an ERD for each of the following situations. If you believe that you need to
make additional assumptions, clearly state them. Follow good data naming guidelines.

(a) Each publisher has a unique name; a mailing address and telephone number are also
kept on each publisher. A publisher publishes one or more books; a book is published
by exactly one publisher. A book is identified by its ISBN, and other attributes are
title, price, and number of pages. Each book is written by one or more authors; an
author writes one or more books, potentially for different publishers. Each author is
uniquely described by an author ID, and we know each author’s name and address.
Each author is paid a certain royalty rate on each book he or she authors, which
potentially varies for each book and for each author. An author receives a separate
royalty check for each book he or she writes. Each check is identified by its check
number, and we also keep track of the date and amount of each check.






1
(b) A nonprofit organization depends on a number of different types of persons for its
successful operation. The organization is interested in the following attributes for all
of these persons: SSN, Name, Address, City/State/Zip, and Telephone. Three types of
persons are of greatest interest: employees, volunteers, and donors. Employees have
only a Date Hired attribute, and volunteers have only a Skill attribute. Donors have
only a relationship (named Donates) with an Item entity type. A donor must have
donated one or more items, and an item may have no donors, or one or more donors.
There are persons other than employees, volunteers, and donors who are of interest to
the organization, so that a person need not belong to any of these three groups. On the
other hand, at a given time a person may belong to two or more of these groups (e.g.,
employee and donor).







2
2. (15 points) Logical Design
Transform the following diagrams into relations. Your answer should be given in the
format: RELATION_NAME(Primary_Key1, …, Attribute_1, …, Foreign_Key1, …)
Show the arrows for foreign keys.











(See next page)










3


















































4
3. (15 points) Functional Dependencies and Normal Forms
The following shows a relation called GradeReport with StudentID and CourseID as the
primary key.


(a) Show the functional dependencies in the relation.

StudentID StudentName, CampusAddress, Major
CourseID CourseTitle, InstructorName, InstructorLocation
StudentID, CourseID Grade
InstructorName InstructorLocation


(b) In what normal form is this relation? Explain your answer.

First normal form. It is a table with no multivalued attributes, and every row is unique.

Yet, there are still partial dependencise, for example, StudentName, CampusAddress and
Major are ONLY dependent on StudentID (one of the primary keys in the relation). So, it
cannot be in second normal form.

There is also a transitive dependency – InstructorLocation is transitively dependent on
CourseID (CourseID InstructorName InstructorLocation). So, it cannot be in third
normal form.


(c) Decompose GradeReport into a set of 3NF relations.

Student (StudentID, StudentName, CampusAddress, Major)
Course (CourseID, CourseTitle, InstructorName)
Instructor (InstrcutorName, InstructorLocation)
CourseGrade (StudentID, CourseID, Grade)

5
Questions 4 and 5 are based on the class schedule problem shown in the following figure:




4. (25 points) Simple SQL
(a) Write an INSERT command to add a faculty member with a faculty ID of 2366 and
name Culler to the FACULTY table.

INSERT INTO FACULTY (FACULTY_ID, FACULTY_NAME)
VALUES ('2366', 'Culler');

(b) Write a command that will remove faculty 3467 from the FACULTY table.

DELETE FROM FACULTY WHERE FACULTY_ID = 3467;


6
(c) Write a command that will modify the name of course ISM 3113 from “Syst Analysis”
to “System Analysis”.

UPDATE COURSE
SET COURSE_NAME = 'System Analysis'
WHERE COURSE_ID = 'ISM 3113';

(d) Write a command that will create a table SOCIETY (Society_ID, Student_ID), in which
Student_ID is a 5-digit number and Society_ID is a 3-digit number, and Student_ID is
a foreign key from the STUDENT table.

CREATE TABLE SOCIETY
(SOCIETY_ID NUMERIC(3,0) NOT NULL,
STUDENT_ID NUMERIC(5,0) NOT NULL,
CONSTRAINT SOCIETY_FK FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT
(STUDENT_ID),
CONSTRAINT COMPOSITEKEY_SOCIETY PRIMARY KEY (SOCIETY_ID,
STUDENT_ID));


(e) Display the smallest STUDENT_ID from the STUDENT table.

SELECT MIN(STUDENT_ID) AS MIN_STUDENT_ID
FROM STUDENT;

(f) List all the STUDENT_IDs from the REGISTRATION table. Each STUDENT_ID
should be listed only ONCE.

SELECT DISTINCT STUDENT_ID
FROM REGISTRATION;

(g) List the ids of the students who are enrolled in each section in Semester I, 2006.
Organize the students by the sections in which they are enrolled. In each section, sort
the students by their student ids.

SELECT STUDENT_ID
FROM REGISTRATION
WHERE SEMESTER = 'I - 2006'
ORDER BY SECTION_NO, STUDENT_ID;

(h) List the ids and the number of courses qualified of all faculty members who are
qualified to teach at least three different courses.

SELECT FACULTY_ID, COUNT(FACULTY_ID) AS NUM_OF_COURSES_QUALIFIED
FROM QUALIFIED
GROUP BY FACULTY_ID
HAVING COUNT (FACULTY_ID) >=3;



7
5. (15 points) SQL for Multiple Tables and Subqueries

(a) Display the name and id of the student who has the largest student id.

SELECT STUDENT_NAME, STUDENT_ID
FROM STUDENT
WHERE STUDENT_ID = (SELECT MAX(STUDENT_ID) FROM STUDENT);

(b) Display the id, name, and faculty name for all courses that Professor Birkin has been
qualified to teach.

SELECT C.COURSE_ID, C.COURSE_NAME, F.FACULTY_NAME
FROM FACULTY F, COURSE C, QUALIFIED Q
WHERE F.FACULTY_ID = Q.FACULTY_ID
AND Q.COURSE_ID = C.COURSE_ID
AND F.FACULTY_NAME = 'Birkin';

(c) Display the names and ids of all students who were not enrolled in any courses during
semester I-2006.

SELECT STUDENT_ID, STUDENT_NAME
FROM STUDENT
WHERE NOT EXISTS
(SELECT * FROM REGISTRATION
WHERE STUDENT.STUDENT_ID = REGISTRATION.STUDENT_ID
AND SEMESTER = 'I - 2006');









--- End of Exam Paper ---














































































































































































































































































学霸联盟