Please read the following instructions carefully:
This exam contains 3 different parts (Part I, Part II and Part III). In each part you have 4
... [Show More] questions
available. You must select and provide the answers to exactly two questions in each part. If you
provide answers for more than two questions in any part of the exam, only the first two questions
answered will be considered. The total number of questions to be answered is 6, with 2 mandatory
questions in each part.
Part I
1. (10%) A worldwide package delivery company named WorldDelivery wants to store information
in a database. The database must be able to keep track of:
Customers who ship items and customers who receive items; some customers may do
both. The customer ID, name and address must be stored.
Each package must be identifiable and trackable, so the database must be able to store
the location of the package and its history of locations. The package weight must also be
stored.
Locations include trucks, planes, airports, and warehouses. For each location the
database should store the corresponding ID, city, country and address.
Draw an ER diagram for the database described.
This study source was downloaded by 100000784424693 from CourseHero.com on 04-23-2021 02:22:39 GMT -05:00
https://www.coursehero.com/file/85003923/CSI2132-Practice-Exampdf/
This study resource was
shared via CourseHero.com
2. (10%) Consider the ER diagram below for modeling an online bookstore.
a. (6%) Translate the ER diagram into a relational model.
b. (2%) Provide the Tuple Relational Calculus (TRC) expression for the following query:
a. “List the names of all books published in the year 2020, by ‘Wiley’ publisher.”
c. (2%) Provide the Relational Algebra (RA) expression for the following query:
a. “Find the names of all books with price lower than $50 that are stored in the
warehouse with code ‘556677’.”
3. (10%) Consider a relation with schema R (A, B, C, D) and the set F of functional dependencies:
F= {AB→C, C→D, D→A}
a. (2%) Find all the candidate keys of R.
b. (4%) Indicate all BCNF violations for R and decompose the relations into collections of
relations that are in BCNF.
c. (4%) Indicate which dependencies, if any, are not preserved by the BCNF decomposition.
This study source was downloaded by 100000784424693 from CourseHero.com on 04-23-2021 02:22:39 GMT -05:00
https://www.coursehero.com/file/85003923/CSI2132-Practice-Exampdf/
This study resource was
shared via [Show Less]