Microsoft Word – CMPT 354 Assignment 2.docx
1
CMPT 354 Assignment 2
Due: 11:59 pm, November 17, 2021
100 points in total
Please submit your assignment in Coursys. All answers should be typed in a PDF file.
Every student has to complete the assignment independently. While you are encouraged to learn
through discussion with the instructor, the TAs and the peer students, any plagiarisms are serious
violation of the university’s academic integrity policy. We have absolutely zero tolerance of such
behavior. Using any tutoring platforms for this course, such as Course Hero or 51zuoyejun is
strictly NOT ALLOWED. If detected, a student using this kind of paid services in this course or
uploading materials of this course to such platforms will be regarded as dishonest and will be
reported to the university as a plagiarism case.
This assignment covers Chapters 6 and 7 in the textbook.
Question 1 (40 points)
This question is about database design using the E-R model.
1. (25 points) Construct an E-R diagram for a university library. The business requirements
are as follows.
• The library catalog records, for each book, the information about title, author(s),
publisher, published date, and price. A book may have one or multiple authors.
• A book may have multiple versions. Different versions of the same book have the
same title, but may have different authors, publishers, published dates, and prices.
Each version has a unique ISBN. By default, a book has one version.
• The library may purchase more than one copy of the same book (i.e., the same
version). The library uses a copy-number to identify a specific copy.
• Both faculty and students can borrow books from the library. Each student can
borrow up to 10 books at any time, and each faculty can borrow up to 15 books at any
time. A book can be borrowed for 2 weeks and can be renewed once.
• Each student/faculty has a 10-digit university id. The information about name, home
department, and primary contact phone number should also be maintained for
students and faculty. A student must belong to one home department, but a faculty
may have joint appointment of up to 3 departments. Each person has only one
primary contact phone number.
• A class may reserve some books as references. Only the instructor (a faculty) of the
course in the specific semester can reserve the reference books from the library. Once
a book is reserved, only the students enrolled in the class can borrow the book.
2
• If a person does not return a book on time, a fee of $1 per overdue day will be charged.
The library needs to maintain the information about unpaid overdue charges.
• A person can put a hold on a book if the book is currently borrowed by someone else
and thus is not in the library.
In your E-R diagram, please also specify the primary keys for the entity sets, the cardinality
and participation constraints. Your E-R diagram should have at least one weak entity set.
2. (15 points) Reduce the E-R diagram into relational schemas.
Question 2 (60 points)
This question is about relational database design.
1. Consider the schema R = (A, B, C, D, E) with the set of functional dependences
F = { A à BC, B à D, E àA, CD à E }
i. (5 points) Show that the decomposition of R into (A, B, C) and (A, D, E) is lossless,
but the decomposition of R into (A, B, C) and (C, D, E) is not lossless.
ii. (5 points) List the candidate keys for R.
iii. (10 points) Give a lossless decomposition of R into BCNF.
iv. (10 points) Give a lossless, dependency-preserving decomposition of R into 3NF.
2. Consider relation schema R = (A, B, C, D, E, G) and the set F of functional dependencies
F = { A à BCD, BC à DE, B à D, D à A }
i. (5 points) Compute B+.
ii. (5 points) Show that AG is a superkey using Armstrong’s axioms.
iii. (10 points) compute a canonical cover for F. Please show each step of derivation
with explanation.
iv. (5 points) Give a 3NF decomposition of R based on the canonical cover computed.
v. (5 points) Give a BCNF decomposition of R using the original set F of functional
dependencies.