Database Design Using Normalization - Database Processing: Fundamentals, Design, and Implementation - page 176

Project Questions
The elliot Bay Sports Club owns and operates three sports club facilities in houston,
Texas. each facility has a large selection of modern exercise equipment, weight rooms,
and rooms for yoga and other exercise classes. elliot Bay offers 3-month and 1-year
memberships. Members can use the facilities at any of the three club locations.
elliot Bay maintains a roster of personal trainers who operate as independent
consultants. Approved trainers can schedule appointments with clients at elliot Bay
facilities, as long as their client is a member of the club. Trainers also teach yoga,
Pilates, and other classes. Answer the following questions, assuming you have been
provided the following three tables of data (PT stands for personal trainer):
PT_SESSION (Trainer, Phone, Email, Fee, ClientLastName, ClientFirstName,
ClientPhone, ClientEmail, Date, Time)
CLUB_MEMBERSHIP (ClientNumber, ClientLastName, ClientFirstName,
ClientPhone, ClientEmail, MembershipType, EndingDate, Street, City, State, Zip)
CLASS (ClassName, Trainer, StartDate, EndDate, Time, DayOfWeek, Cost)
4.39 Identify possible multivalued dependencies in these tables.
4.40 Identify possible functional dependencies in these tables.
4.41 Determine whether each table is either in BCNF or in 4NF. State your assumptions.
4.42 Modify each of these tables so that every table is in BCNF and 4NF. Use the assump-
tions you made in your answer to question 4.41.
4.43 Using these tables and your assumptions, recommend a design for an updatable
database.
4.44 Add a table to your answer to question 4.43 that would allow Elliot Bay to assign mem-
bers to particular classes. Include an AmountPaid column in your new table.
4.45 Recommend a design for a read-only database that would support the following needs:
A. Enable trainers to ensure that their clients are members of the club.
B. Enable the club to assess the popularity of various trainers.
C. Enable the trainers to determine if they are assisting the same client.
D. Enable class instructors to determine if the attendees to their classes have paid.
Case Questions
Marcia's Dry Cleaning Case Questions
Marcia Wilson, the owner of Marcia's Dry Cleaning, is in the process of creating databases to
support the operation and management of her business. For the past year, she and her staff
have been using a cash register system that collects the following data:
SALE (InvoiceNumber, DateIn, DateOut, Total, Phone, FirstName, LastName)