1. Use the following Chen model ERD to answer questions 1 through 9.
1. List the entities shown.
2. What is the primary key for each entity?
3. Are there any multivalued attributes shown? If so, discuss how it/they should be handled in the implementation of the relational database.
4. Are there any derived attributes shown? If so, discuss how it/they should be handled in the implementation of the relational database.
5. Looking at the relationship cardinalities shown in the ERD, what is the minimum number of pets that a customer can own?
6. Looking at the ERD, is Pet an optional or mandatory participant in the "Owns" relationship?
7. Looking at the relationship cardinalities show in the ERD, what is the maximum number of pets that a customer can own?
8. What is the degree of the relationship?
9. Redraw the ERD using the Crow's Foot model.
2. Use the following case to complete questions 10 through 13.
Meridian News publishes a daily newspaper and would like a database developed to maintain information about subscribers and subscriptions. Each subscriber is uniquely identified by a subscriber number and is also characterized by first name, last name, address, and phone number. Each subscription is uniquely identified by a subscription number and is also characterized by subscription type (Daily or Sunday only), price, start date, and expiration date. The price paid for a subscription is input when the subscription is sold, since it may vary due to special offers or situations. Expired subscriptions and former subscribers that no longer have a current subscription remain in the database.
The example report below shows the subscriptions belonging to subscriber number 31893.
|
Jacob Smith 13 Milky Way, Phoenix, AZ 85001 Subscriber#: 31893 Printed: 07/15/2003
|
| Subsription |
Type |
Price |
Start |
End |
| 242034 |
S |
$96.00 |
1/1/2002 |
12/31/2002 |
| 245132 |
D |
$13.65 |
1/1/2003 |
6/30/2003 |
| 246328 |
D |
$81.90 |
7/1/2003 |
12/31/2003 |
10. Identify the entities discussed in the case.
11. What is the minimum number of current subscriptions for a subscriber?
12. What is the maximum number of subscriptions for a subscriber?
13. Using the Crow's Foot model, draw an ERD for the Meridian News database and state any assumptions you believe you have to make in order to develop a complete diagram.
3. Use the following information to answer question 14 through 16.
Nazca Cinemas is a small movie theater that would like you to design a movie scheduling database system for them. The theater has four rooms (screens). Each room has its own unique ID and the seating capacity and screen size for each are recorded. Each movie is assigned a unique movie ID and its title and runtime are recorded.
Assume the following:
• A movie can be shown more than once on a given date.
• A movie can be shown more than once in a given room.
• A movie can be shown in more than one room simultaneously.
• Several showings can be booked at different start times in a given room on a given date.
The following unnormalized relation has been developed based on this case. Do not add any additional attributes:
SHOWING (MovieID, Title, Runtime, RoomID, ScreenSize, Capacity, Date, StartTime)
14. Convert the unnormalized relation into 1NF and briefly discuss the changes that were necessary.
15. Convert the 1NF relation to 2NF and briefly discuss the changes that were necessary.
16. Convert the 2NF relation to 3NF and briefly discuss the changes that were necessary.
4. Use the following information to answer question 17 through 19.
The following unnormalized relation shows a modified version of the Nazca Cinemas case problem. A ShowingID attribute has been added to the relation. Each individual showing (a given movie in a given room on a given date at a given time) of a movie is given its own unique ShowingID. Using the modified version of the unnormalized relation, rework the normalization process. Do not add any additional attributes to those shown in this unnormalized relation:
SHOWING (ShowingID, MovieID, Title, Runtime, RoomID, ScreenSize, Capacity, Date, StartTime, EndTime)
17. Convert the modified unnormalized relation into 1NF and briefly discuss the changes that were necessary.
18. Convert the 1NF relation to 2NF and briefly discuss the changes that were necessary.
19. Convert the 2NF relation to 3NF and briefly discuss the changes that were necessary.