Teorey J., Lightstone S., Nadeau T. Database Modeling and Design: Logical Design

Подождите немного. Документ загружается.

current rental location of each car and its current

condition and histor y of repairs, alo ng with informa-

tion for a local office, including c ustomer inf ormation,

expected return date, return location, and car

status (rea dy, being-repaired, currently-rented, being-

cleaned). Select attributes from your intuition about

the situation, and list them separately from t he

diagram, but i n association with a particula r clas s in

the UML diagram.

Problem 3.2. Given the following assertions for a rela-

tional database that represents the current term enroll-

ment at a large university, draw a UML diagram for

this schema that takes into account all the assertions

given. There are 2000 instructors, 4000 courses, and

30000 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 ¼ 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.

• All courses are taught by only 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 data-

base 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.

286 EXERCISES

Transformation of the Conceptual

Model to SQL

Problem 5.1.

a. Transform your integrated ER diagram from Problem

4.1 into an SQL database with five to ten rows per table

of data you can make up to fit the database schema.

b. Demonstrate your database by displaying all the fol-

lowing queries:

1. Which video store branches have Shrek in stock

(available) now?

2. In what section of the store (film category) can you

find The Terminator?

3. For customer Annika Sorenstam, what titles are

currently being rented and what are the overdue

charges, if any?

4. (Any query of your choice—show what your system

can really do!)

works-in

carries

includes

empno video−id

custno

name

address

late−amt−due

last-date-out

formerly−

rented−to

copyno

empname

EMPLOYEE VIEW

CUSTOMER VIEW

currently−

renting−to

Employee

Customer

Video-store

branch

Video−copy

Movie−category

Movie

empaddr

br-addr

br-phone

cat−name

name

year

rating

branchno

categoryno

movie−id

EXERCISES 287

Normalization and Minimum

Set of Tables

Problem 6.1. Given the table R1(A, B, C) with FDs A ->

B and B -> C:

a. Is A a superkey for this table?

b. Is B a superkey for this table?

c. Is this table in 3NF, BCNF, or neither?

Problem 6.2. Given the table R1(A,B,C,D) with FDs AB -

> C and BD -> A:

a. What are all the superkeys of this table?

b. What are all the candidate keys for this table?

c. Is this table in 3NF, BCNF, or neither?

Problem 6.3. The following FDs represent a set of air-

line 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

288 EXERCISES

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.4. 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?

a. J-> KLMNP

b. JKL -> MNP

c. K-> MQ

d. KL -> MNP

e. KM -> NP

f. N-> KP

Logical Database Design

(Generic Problem)

Problem 7.1. Design and implement a small database

that will be useful to your company or student

organization.

a. State the purpose of the database in a few sentences.

b. Construct an ER or UML class diagram for the

database.

c. 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 inter-

esting queries. Use Oracle, DB2, SQL Server, Access,

or any other database system.

d. 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).

e. Demonstrate your working database by displaying the

results of four queries. Pick interesting and complex

queries (impress us!).

EXERCISES 289

OLAP

Problem 10.1. As mentioned in Chapter 10, hypercube

lattice structures are a specialization of product graphs.

Figure 10.16 shows an example of a three-dimensional

hypercube lattice structure. Figure 10.13 shows an

example of a two-dimensional product graph. Notice

the two figures are written using different notations.

Write the hypercube lattice structure in Figure 10.16

using the product graph notation introduced with

Figure 10.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 the “Selec-

tion of Materialized Views” section in Chapter 10.

290 EXERCISES

SOLUTIONS TO SELECTED

EXERCISES

Problem 2.2.

Instructor

directs-res

Student

teaches

Course

enrolled-in

term

has-prereq

Problem 3.2.

Student

Course

Enrollment

Instructor

1 ..

0 .. 2

prerequisite

term

1 .. *

teacher

advisor

researcher

Problem 4.1. Connect Movie to Video-copy as a 1-to-n

relationship (Video-copy at the n side), or use a general-

ization from Movie to Video-copy with Movie as the

supertype and Video-copy as the subtype.

291

Problem 6.1. Given the table R1(A, B, C) with FDs A ->

B and B -> C:

a. Is A a superkey for this table? Yes

b. Is B a superkey for this table? No

c. Is this table in 3NF, BCNF, or neither? Neither 3NF nor

BCNF.

Problem 6.3. Minimum set of 3NF (and BCNF) tables:

R1: ABCDEFHINQ with key A and FD A ->

BCDEFHINQ (and A -> P optional).

R2: DEGJKLMR with key DE and FD DE -> GJKLMR.

R3: FSTU with key F and FD F -> STU.

R4: UVW with keys U and W and FDs U -> VW and

W-> U.

R5: GQP with key GQ and FD GQ -> P.

Problem 6.4. Given these FDs, begin Step 1 (LHS

reduction):

a. J-> KLMNP

b. JKL -> MNP First, eliminate K and L since J -> KL in

(a), merge with (a).

c. K-> MQ

d. KL -> MNP Third, eliminate L since K -> MNP from

merged (c), (d) is redundant.

e. KM -> NP Second, eliminate M since K -> M in (c),

merge with (c).

f. N-> KP

End of Step 1; begin Step 2 (RHS reduction for

transitivities):

a. J-> KLMNP First, reduce by eliminating MNP since K

-> MNP.

b. K-> MQNP Second, reduce by eliminating P since N ->

c. N-> KP

End

of S

tep 2 and consolidation in Step 3:

a. J-> KL

b. K-> MNQ (or K -> MNPQ) First, merge (b) and (c)

for Superkey Rules 1 and 2.

c. N-> KP (or N -> K)

Steps 4 and 5:

a. J-> KL Candidate key is J (BCNF).

b. K-> MNPQ and N -> K Candidate keys are K and N

(BCNF).

292 SOLUTIONS TO SELECTED EXERCISES

Problem 10.1.

Customer Dimension

(first dimension)

0: cust id

1: all

(0, 0, 0)

Fact Table

Part Dimension

(second dimension)

0: part id

1: all

Supplier Dimension

(third dimension)

0: supplier id

1: all

(1, 0, 0)

(1, 1, 0)

(0, 0, 1)(0, 1, 0)

(1, 0, 1) (0, 1, 1)

(1, 1, 1)

SOLUTIONS TO SELECTED EXERCISES 293

Glossary

activity diagram (UML)—a process workflow model (diagram) showing

the flow from one activity to the next.

aggregation—a special type of abstraction relationship that defines a

higher-level entity that is an aggregate of several lower-level entities;

a “part-of” relationship. For example, a Bicycle entity is an aggregate

of Wheel, Handlebar, and Seat entities.

association—a relationship between classes (in UML). Associations can

be binary, n-ary, reflexive, or qualified.

attribute—a primitive data element that provides descriptive detail

about an entity; a data field or data item in a record. For example,

lastname would be an attribute for the entity Customer. Attributes

may also be used as descriptive elements for certain relationships

among entities.

automatic summary table (AST)—materialized (summary) views or

aggregates of data saved by OLAP for future use to reduce query

time.

binary recursive relationship—a relationship between one occurrence

of an entity with another occurrence of the same entity.

binary relationship—a relationship between occurrences of two

entities.

BLOB—binary large object, often containing multimedia types of data

or images.

Boyce-Codd normal form (BCNF)—a table is in Boyce-Codd normal

form if and only if for every functional dependency X -> A, where X

and A are either simple or composite attributes (data items), X must

be a superkey in that table. This is a strong form of 3NF and is the

basis for most practical normalization methodologies.

candidate key—any subset of the attributes (data items) in a superkey

that is also a superkey and is not reducible to another superkey.

CASE tool—computer-aided software engineering tool, or software

design tool, to assist in the logical design of large or complex

databases. For example, AllFusion ERwin Data Modeler, Rational

Data Architect, or PowerDesigner.

class—a concept in a real-world system, represented by a noun in UML;

similar to an entity in the ER model.

class diagram (UML)—a conceptual data model; a model of the static

relationships between data elements of a system (similar to an ER

diagram).

completeness constraint—double line symbol connecting a supertype

entity with the subtypes to designate that the listed subtype entities

represent a complete set of possible subtypes.

295

composition—a relationship between one class and a group of other

classes in UML; the class at the diamond (aggregate) end of the

relationship is composed of the class(es) at the small (component)

end; similar to aggregation in the ER model.

conceptual data model—an organization of data that describes the

relationships among the primitive data elements. For example, in the

ER model, it is a diagram of the entities, their relationships, and their

attributes.

connectivity of a relationship—a constraint on the count of the

number of associated entity occurrences in a relationship, either one

or many.

data item—the basic component of a data record in a file or database

table; the smallest unit of information that has meaning in the real

world. For example, customer last name, address, identification

number.

data mining—a way of extracting knowledge from a database by

searching for correlations in the data in order to present promising

hypotheses to the user for analysis and consideration.

data model—an organization of data that describes the relationships

among the primitive and composite data elements.

data warehouse—a large repository of historical data that can be

integrated for decision support.

database—a collection of interrelated stored data that serves the needs

of multiple users; a collection of tables in the relational model.

database administrator (DBA)—person in a software organization who

is in charge of designing, creating, and maintaining the databases of

an enterprise. The DBA makes use of a variety of software tools

provided by a DBMS.

database life cycle—an enumeration and definition of the basic steps in

the requirements analysis, design, creation, and maintenance of a

database as it evolves over time.

database management system (DBMS)—a generalized software system

for storing and manipulating databases. For example, Oracle, IBM’s

DB2, and Microsoft SQL Server or Access.

DBA—see database administrator.

degree of a relationship—the number of entities associated in the

relationship: recursive binary (1 entity), binary (2 entities), ternary

(3 entities), n-ary (n entities).

denormalization—the consolidation of database tables to increase

performance in data retrieval (query), despite the potential loss of

data integrity. Decisions on when to denormalize tables are based on

cost–benefit analysis by the DBA.

deployment diagram (UML)—shows the physical nodes on which a

system executes. This is more closely associated with physical

database design.

dimension table—the smaller tables used in a data warehouse to

denote the attributes of a particular dimension such as time,

location, customer characteristics, product characteristics, etc.

296 Glossary