Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
Faculty of Business and Economics
IIMT3601 Database Management
Written Exam
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.
(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. (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.
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.
(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.
Questions 4 and 5 are based on the class schedule problem shown in the following figure: