Exercises

ER and UML Conceptual Data Modeling

Problem 2-1

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.

Problem 2-2

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.

Assertions:

  • An instructor may teach one or more courses in a given term (average is 2.0 courses).
  • An instructor must direct the research of at least one student (average = 2.5 students).
  • A course may have none, one, or two prerequisites (average = 1.5 prerequisites).
  • A course may exist even if no students are currently enrolled.
  • Each course is taught by exactly one instructor.
  • The average enrollment in a course is 30 students.
  • A student must select at least one course per term (average = 4.0 course selections).

Problem 3-1

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.

Problem 3-2

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.

Assertions:

  • An instructor may teach none, one, or more courses in a given term (average is 2.0 courses).
  • An instructor must direct the research of at least one student (average = 2.5 students).
  • A course may have none, one, or two prerequisites (average = 1.5 prerequisites).
  • A course may exist even if no students are currently enrolled.
  • Each course is taught by exactly one instructor.
  • The average enrollment in a course is 30 students.
  • A student must select at least one course per term (average = 4.0 course selections).

Conceptual Data Modeling and Integration

Problem 4-1

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.

image

Transformation of the Conceptual Model to SQL

Problem 5-1

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!)

Normalization and Minimum Set of Tables

Problem 6-1

Given the table R1(A, B, C) with FDs A -> B and B -> C:

1. Is A a superkey for this table?____________
2. Is B a superkey for this table?____________
3. Is this table in 3NF, BCNF, or neither?____________

Problem 6-2

Given the table R(A, B, C, D) with FDs AB->C and BD->A:

1. What are all the superkeys of this table?____________
2. What are all the candidate keys for this table?____________
3. Is this table in 3NF, BCNF, or neither?____________
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
2. What is the level of normalization for each of these tables, based on the information given?

Problem 6-4

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

Problem 6-5

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?

1. J -> KLMNP
2. JKL -> MNP
3. K -> MQ
4. KL -> MNP
5. KM -> NP
6. N -> KP

Problem 6-6

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?

1. ABC -> DEFG
2. B -> CH
3. A -> HK
4. CD -> GKM
5. D -> CP
6. E -> FG
7. G -> CDM

Problem 6-7

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.

1. ABC -> H
2. AC -> BDEFG
3. ACDF -> BG
4. AW->BG
5. B -> ACF
6. H -> AXY
7. M -> NZ
8. MN -> HPT
9. XY -> MNP

Problem 6-8

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?

1. ABCD -> EFGHIJK

2. ACD -> JKLMN
3. A -> BH
4. B -> JKL
5. BH -> PQR
6. BL -> PS
7. EF -> ABCDH
8. JK -> B
9. MN -> ACD

10. L -> JK

11. PQ -> S

12. PS -> JKQ

13. PSR -> QT

Problem 6-9

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?

1. A -> BGHJ
2. AG -> HK
3. B -> K
4. EA -> F
5. EB -> AF
6. EF -> A
7. H -> J
8. J -> AB

Problem 6-10

FDs and MVDs

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:

1. Is R in 3NF?
2. Is R in BCNF?
3. Does the multivalued dependency AB ->>C hold?
4. Does the set {R1(A, B, C), R2(A, B, D)} satisfy the lossless join property?

Given table R(A, B, C) and the set (R1(A, B), R2(B, C)} satisfies the lossless decomposition property:

1. Does the multivalued dependency B->>C hold?
2. Is B a candidate key?
3. Is R in 4NF?

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:

1. Is the level of normalization of “skills_available” at least 4NF?
2. Given table R(A, B, C) with actual data shown below:

a. Does the multivalued dependency B->>C hold?

b. Is R in 5NF?

image

Logical Database Design (Generic Problem)

Problem 7-1

Design and implement a small database that will be useful to your company or student organization.

1. State the purpose of the database in a few sentences.
2. Construct an ER or UML class diagram for the database.
3. Transform your ER or UML diagram into a working database with five to ten rows per table of data you can make up to fit the database schema. You should have at least four tables, enough to have some interesting queries. Use Oracle, DB2, SQL Server, Access, or any other database system.
4. Show that your database is normalized (BCNF) using FDs derived from your ER diagram and from personal knowledge of the data. Analyze the FDs for each table separately (this simplifies the process).
5. Demonstrate your working database by displaying the results of four queries. Pick interesting and complex queries (impress us!).

OLAP

Problem 8-1

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset