Elliot Bay Sports Club operates three facilities They offer

Elliot Bay Sports Club operates three facilities. They offer 3-month and 1-year memberships. Members can use either of the 3 club facilities once they join. The sports club has personal trainers (PTs) who can schedule private sessions with club members. The trainers also teach yoga, Pilates, and other classes that club members can take. Classes are scheduled quarterly at each facility; however the club currently has no way of determining which class is at which facility. A class is taught by only one trainer each quarter. The sports club would like to track membership, PT private sessions, classes (including instructors and facility), and class attendance. Only current membership date(s) need to be tracked. The club currently uses the tables below:

*TrainerID is (PK) in PT_Session Table

Considering the above information, answer the following questions (be VERY specific in each answer):

(a) In what normal form is the PT_Session table in, and why?

(b) In what normal form is the Class table in, and why?

(c) In what normal form is the Club_Membership table in, and why? NOTE: for this assignment you do NOT need to consider the storing of Zip, City and State in the same table a violation of any normal form.

(d) Show the results of normalizing the Sports Club tables to 3NF. Use ONLY the columns depicted in the original tables PLUS whatever column is needed in the appropriate table to indicate the facility a class is scheduled for. Do a complete logical design of the above tables including 3NF tables, all primary and foreign keys, referential integrity specifications/constraints, and table names—you may omit sample data. Do not assume that all tables need normalizing. Use Word, Excel, or Access to design the tables and document the rest of your logical design.

Database 1 Database (Access 2007 2010) Microsoft Access Table Tools File Home Gina Test Tab Create External Data Database Tool Field Table Date & Time LT Name & Caption DI Data Type: Text Required AB 12 Yes/No Default value Format: Formatting Unique View Text Number Currency Delete a More Fields Field size 255 Modify Modify Memo Validation Lookups Expression Setting S Indexed Add & Delete Properties Formatting Field Validation PT Session Fee ClientLastName ClientFirstName Clientphone ClientEmail SessionDate SessionTime TrainerID hone Email Record: 1 of 1 Dl No Filter Search Datasheet View Num Lock

Solution

(a) In what normal form is the PT_Session table in, and why?

Solution : PT_Session is in first Normal Form(1NF) .A database table is said to be in 1NF if it contains no repeating fields/columns. As PT_Session Doesnot conatin any repeating fields so it is in 1NF.

(b) In what normal form is the Class table in, and why?

Solution : Clas table is in 2NF.A database table is said to be in 2NF if it is in 1NF and contains only those fields/columns that are functionally dependent on the primary key.

(c) ) In what normal form is the Club_Membership table in, and why?

Solution : Club_Membership is in 2NF(Second normal form).A database table is said to be in 2NF if it is in 1NF and contains only those fields/columns that are functionally dependent on the primary key.

Primary Key

Trainer ID

Phone

Email

Fee

Client Number

Composite Primary KEY

Class Name

Trainer ID

Start Date

End Date

Class time

DayOFWEEK

Cost

Primary Key

ClientNumber

ClientLasteName

Client First Name

Client Phone

Client Email

Address ID

Membership type

MembershipENDDate

Primary Key

Address ID

Street

City

State

ZIp

Trainer ID	Phone	Email	Fee	Client Number