Exercises
Draw a detailed ER diagram for an car rental agency database (e.g., Hertz), keeping track of the current rental location of each car, its current condition and history of repairs, customer information for a local office, expected return date, return location, car status (ready, being-repaired, currently-rented, being-cleaned). Select attributes from your intuition about the situation and list them separately from the diagram, but associated with a particular entity or relationship in the ER model.
Given the following assertions for a relational database that represents the current term enrollment at a large university, draw an ER diagram for this schema that takes into account all the assertions given. There are 2,000 instructors, 4,000 courses, and 30,000 students. Use as many ER constructs as you can to represent the true semantics of the problem.
Draw UML class diagrams for an car rental agency database (e.g., Hertz), keeping track of the current rental location of each car, its current condition and history of repairs, customer information for a local office, expected return date, return location, car status (ready, being-repaired, currently-rented, being-cleaned). Select attributes from your intuition about the situation.
Draw one diagram showing the relationships of the classes without the attributes listed, then show each class individually with the attributes listed.
Given the following assertions for a relational database that represents the current term enrollment at a large university, draw an UML diagram for this schema that takes into account all the assertions given. There are 2,000 instructors, 4,000 courses, and 30,000 students. Use as many UML constructs as you can to represent the true semantics of the problem.
The following ER diagrams represent two views of a video store database as described to a database designer. Show how the two views can be integrated in the simplest and most useful way by making all necessary changes on the two diagrams. State any assumptions you need to make.
1. Transform your integrated ER diagram from Problem 4-1 into a SQL database with five to ten rows per table of data you make up to fit the database schema.
2. Demonstrate your database by displaying all the queries below:
a. Which video store branches have Shrek in stock (available) now?
b. In what section of the store (film category) can you find Terminator?
c. For customer “Anika Sorenstam,” what titles are currently being rented and what are the overdue charges, if any?
d. Any query of your choice. (Show me what your system can really do!)
Given the table R1(A, B, C) with FDs A -> B and B -> C:
Given the table R(A, B, C, D) with FDs AB->C and BD->A:
Table | Candidate Key(s) |
customer | cid |
order | orderno |
department | deptno |
salesperson | sid |
item | itemno |
order-dept-sales | orderno, sid AND orderno, deptno |
order-item-sales | orderno, itemno |
Table | FDs |
customer | |
order | |
department | |
salesperson | |
item | |
order-dept-sales | |
order-item-sales |
The following functional dependencies (FDs) represent a set of airline reservation system database constraints. Design a minimum set of BCNF tables, preserving all FDs, and express your solution in terms of the code letters given below (a time-saving device for your analysis). Is the set of tables you derived also BCNF?
reservation_no -> agent_no, agent_name, airline_name, flight_no, passenger_name
reservation_no -> aircraft_type, departure_date, arrival_date, departure_time, arrival_time
reservation_no -> departure_city, arrival_city, type_of_payment, seating_class, seat_no
airline_name, flight_no -> aircraft_type, departure_time, arrival_time
airline_name, flight_no -> departure_city, arrival_city, meal_type
airline_name, flight_no, aircraft_type -> meal_type
passenger_name -> home_address, home_phone, company_name
aircraft_type, seat_no -> seating_class
company_name -> company_address, company_phone
company_phone -> company_name
A: reservation_no
B: agent_no
C: agent_name
D: airline_name
E: flight_no
F: passenger_name
G: aircraft_type
H: departure_date
I: arrival_date
J: departure_time
K: arrival_time
L: departure_city
M: arrival_city
N: type_of_payment P: seating_class
Q: seat_no
R: meal_type
S: home_address
T: home_phone
U: company_name
V: company_address
W: company_phone
Given the following set of FDs, find the minimum set of 3NF tables. Designate the candidate key attributes of these tables. Is the set of tables you derived also BCNF?
Using only the given set of functional dependencies (FDs), find the minimum set of BCNF tables. Show the results of each step of Bernstein’s algorithm separately. What are the candidate keys for each table?
Given the FDs listed below, determine the minimum set of 3NF tables, preserving all FDs. Define the candidate keys of each table and determine which tables are also BCNF.
Given the following FDs, determine the minimum set of 3NF tables. Make sure that all FDs are preserved. Specify the candidate keys of each table. Note that each letter represents a separate data element (attribute). Is the set of tables you derived also BCNF?
10. L -> JK
11. PQ -> S
12. PS -> JKQ
13. PSR -> QT
Given the following FDs, determine the minimum set of 3NF tables. Make sure that all FDs are preserved. Specify the candidate keys of each table. Note that each letter represents a separate data element (attribute). Is the set of tables you derived also BCNF?
Answer each question yes or no, and justify each answer. In most cases, you will be given a table R with a list of attributes, with at most one candidate key (the candidate key may be either a single attribute or composite attribute key, shown underlined).
Given table R(A, B, C, D) and the functional dependency AB->C:
Given table R(A, B, C) and the set (R1(A, B), R2(B, C)} satisfies the lossless decomposition property:
Given a table “skills_available” with attributes empno, project, and skill, in which the semantics of “skills_available” state that every skill an employee has must be used on every project that employee works on:
a. Does the multivalued dependency B->>C hold?
b. Is R in 5NF?
Design and implement a small database that will be useful to your company or student organization.
As mentioned in Chapter 8, hypercube lattice structures are a specialization of product graphs. Figure 8.16 shows an example of a three-dimensional hypercube lattice structure. Figure 8.13 shows an example of a two-dimensional product graph. Notice that the two figures are written using different notations. Write the hypercube lattice structure in Figure 8.16 using the product graph notation introduced with Figure 8.13. Keep the same dimension order. Don’t worry about carrying over the view sizes. Assume the Customer, Part, and Supplier dimensions are keyed by “customer id,” “part id,” and “supplier id,” respectively. Shade the nodes representing the fact table and the views selected for materialization as indicated in Section 8.2.4.