1 / 15

Chapter 4

Chapter 4. Elliot Bay Sports Club. Elliot Bay Sports Club.

uriah
Télécharger la présentation

Chapter 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 Elliot Bay Sports Club

  2. Elliot Bay Sports Club • The Elliot Bay Sports Club owns and operates three sports club facilities in Seattle, WA. Each facility has a large selection of modern exercise equipment, weight rooms, and rooms for yoga and other exercise classes. Elliot Bay offers three-month and one-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, aerobics, 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, ClientName, ClientPhone, ClientEmail, Date, Time) CLUB_MEMBERSHIP (ClientNumber, ClientName, ClientPhone, ClientEmail, MembershipType, EndingDate, Street, City, State, Zip) CLASS (ClassName, Trainer, StartDate, EndDate, Time, DayOfWeek, Cost)

  3. 4.39 Identify possible multivalued dependencies in these tables. PT_SESSION.Trainer  PT_SESSION.Phone PT_SESSION.ClientName  PT_SESSION.ClientPhone CLUB_MEMBERSHIP.ClientNumber  CLUB_MEMBERSHIP.ClientPhone CLUB_MEMBERSHIP.ClientName  CLUB_MEMBERSHIP.ClientPhone • PT_SESSION (Trainer, Phone, Email, Fee, ClientName, ClientPhone, ClientEmail, Date, Time) • CLUB_MEMBERSHIP (ClientNumber, ClientName, ClientPhone, ClientEmail, MembershipType, EndingDate, Street, City, State, Zip) • CLASS (ClassName, Trainer, StartDate, EndDate, Time, DayOfWeek, Cost)

  4. 4.40 Functional Dependencies • ASSUMPTIONS: In PT_SESSION, the Trainer’s Fee is a constant and is associated with the trainer. An alternate assumption might be that Fee depends on the Trainer and the type of training and length of the session. • PT_SESSION (Trainer, Phone, Email, Fee, ClientName, ClientPhone, ClientEmail, Date, Time) • CLUB_MEMBERSHIP (ClientNumber, ClientName, ClientPhone, ClientEmail, MembershipType, EndingDate, Street, City, State, Zip) • CLASS (ClassName, Trainer, StartDate, EndDate, Time, DayOfWeek, Cost)

  5. PT_SESSION (Trainer, Phone, Email, Fee, ClientName, ClientPhone, ClientEmail, Date, Time) 4.40 Functional Dependencies – PT_SESSION PT_SESSION.Trainer  PT_SESSION.EMail PT_SESSION.Trainer  PT_SESSION.Fee PT_SESSION.Trainer  PT_SESSION.Phone PT_SESSION.ClientName  PT_SESSION.ClientEMail PT_SESSION.ClientName  PT_SESSION.ClientPhone PT_SESSION.(Trainer, Date, Time)  PT_SESSION.ClientName

  6. CLUB_MEMBERSHIP (ClientNumber, ClientName, ClientPhone, ClientEmail, MembershipType, EndingDate, Street, City, State, Zip) 4.40 Functional Dependencies –CLUB_MEMBERSHIP: CLUB_MEMBERSHIP.ClientNumber  CLUB_MEMBERSHIP.ClientName CLUB_MEMBERSHIP.ClientNumber  CLUB_MEMBERSHIP.ClientEmail CLUB_MEMBERSHIP.ClientNumber  CLUB_MEMBERSHIP.MembershipType CLUB_MEMBERSHIP.ClientNumber  CLUB_MEMBERSHIP.EndingDate CLUB_MEMBERSHIP.ClientNumber  CLUB_MEMBERSHIP.Street CLUB_MEMBERSHIP.ClientNumber  CLUB_MEMBERSHIP.City CLUB_MEMBERSHIP.ClientNumber  CLUB_MEMBERSHIP.State CLUB_MEMBERSHIP.ClientNumber  CLUB_MEMBERSHIP.Zip

  7. CLASS (ClassName, Trainer, StartDate, EndDate, Time, DayOfWeek, Cost) 4.40 Functional Dependencies –Class: CLASS.(ClassName, Trainer, StartDate, Time)  CLASS.EndDate CLASS.(ClassName, Trainer, StartDate, Time)  CLASS.DayOfWeek CLASS.(ClassName, Trainer, StartDate, Time)  CLASS.Cost

  8. 4.41 Classify each table according to whether it is in BCNF or in 4NF. State your assumptions. • ASSUMPTIONS: • In PT_SESSION, the Trainer’s Fee is a constant and is associated with the trainer. An alternate assumption might be that Fee depends on the Trainer and the type of training and length of the session. • In PT_SESSION, Trainers can have multiple sessions with the same client on the same day. • In PT_SESSION and CLUB_MEMBERSHIP, ClientName is not unique. • In CLASS, ClassName is not unique. Further, sections of the same class with different trainers can be offered with the same StartDay and Time.

  9. PT_SESSION (Trainer, Phone, Email, Fee, ClientName, ClientPhone, ClientEmail, Date, Time) • Functional Dependencies • Contains information about three entities or themes – the Trainer, the Client and the PT_Session. • Not in BCNF • Multivalued dependencies PT_SESSION.Trainer  PT_SESSION.Phone • This table also has a problem since ClientName is not unique – the relationship should be established by using ClientNumber which is unique

  10. CLUB_MEMBERSHIP (ClientNumber, ClientName, ClientPhone, ClientEmail, MembershipType, EndingDate, Street, City, State, Zip) • Is it in BCNF? • Yes • Is it in 4NF? • No, because it contains two multivalued dependencies. • To be strictly in BCNF (City, Sate, and ZIP) would be moved into another table. These can actually both be resolved by moving just one of them into it’s own table. The best to move is: CLUB_MEMBERSHIP.ClientNumber  CLUB_MEMBERSHIP.ClientPhone

  11. CLASS (ClassName, Trainer, StartDate, EndDate, Time, DayOfWeek, Cost) • Is it in BCNF? • Yes • Is it in 4NF? • No multivalued dependencies. So, it is also in 4NF

  12. 4.42 Modify each of these tables so that every table is in BCNF and 4NF. Use the assumptions you made in your answer to question 4.41. • PT_SESSION • Break into each theme • Also use ClientNumber, not ClientName as the linking attribute PT_SESSION (Trainer, ClientNumber, Date, Time) TRAINER (Trainer, Email, Fee) TRAINER_PHONE (Trainer, Phone) • Club_Membership – Remove multivalued dependency MEMBER_PHONE (ClientNumber, ClientPhone) CLUB_MEMBERSHIP (ClientNumber, ClientName, ClientEmail, MembershipType, EndingDate, Street, City, State, Zip)

  13. 4.43 Using these tables and your assumptions, recommend a design for an updateable database.The design is: PT_SESSION (Trainer, ClientNumber, Date, Time) WHERE PT_SESSION.Trainer must exist in TRAINER.Trainer, AND PT_SESSION.ClientNumber must exist in CLUB_MEMBERSHIP.ClientNumber TRAINER (Trainer, Email, Fee) TRAINER_PHONE (Trainer, Phone) WHERE TRAINER_PHONE.Trainer must exist in TRAINER.Trainer CLUB_MEMBERSHIP (ClientNumber, ClientName, ClientEmail, MembershipType, EndingDate, Street, City, State, Zip) MEMBER_PHONE (ClientNumber, ClientPhone) WHERE MEMBER_PHONE.ClientNumber must exist in CLUB_MEMBERSHIP. ClientNumber CLASS (ClassName, Trainer, StartDate, EndDate, Time, DayOfWeek, Cost) WHERE CLASS.Trainer must exist in TRAINER.Trainer

  14. 4.44 Add a table to your answer to question 4.43 that would allow Elliot Bay to assign members to particular classes. Include an AmtPaid column in your new table. CLASS_MEMBER (ClassName, Trainer, StartDate, Time, ClientNumber, AmtPaid) WHERE CLASS_MEMBER.(ClassName, Trainer, StartDate, Time) must exist in CLASS.(ClassName, Trainer, StartDate, Time), AND WHERE CLASS_MEMBER.ClientNumber must exist in CLENT. ClientNumber

  15. 4.45 Recommend a design for a read-only database that would support the following needs • Enable trainers to ensure that their clients are members of the club. PT_SESSION (Trainer, ClientNumber, ClientName, Date, Time, MembershipType, EndingDate) • Enable the club to assess the popularity of various trainers. TRAINER_CLIENT (Trainer, ClientNumber, ClientName, Date, Time) TRAINER_CLASS_MEMBERSHIP (Trainer, ClassName, StartDate, Time, ClientNumber, ClientName) • Enable the trainers to determine if they are assisting the same client. TRAINER_CLIENTS (Trainer, ClientNumber, ClientName) • Enable class instructors to determine if the attendees to their classes have paid. CLASS_MEMBER (ClassName, Trainer, StartDate, Time, ClientNumber, ClientName, AmtPaid)

More Related