Fitness Zone FZ Database Data Dictionary

Fitness Zone FZ Database

Data Dictionary

Version: / 1.0
Approval State: / Draft
Approved by:
Prepared by:
Reviewed by:
File Name: / Datadictionary.doc

iii

Fitness Zone FZ Database Data Dictionary

This page
intentionally
left blank

iii

Fitness Zone FZ Database Data Dictionary

Table of Contents

1 Table Name: Address iv

2 Table Name: EmergencyContacts v

3 Table Name: Guests vi

4 Table Name: Lockers vii

5 Table Name: MemberPrograms viii

6 Table Name: Membership ix

7 Table Name: Programs xi

8 Table Name: Persons xii

9 Table Name: PersonType xiv

10 Table Name: Relationships xv

11 Table Name: Rentals xvi

12 Table Name: Roles xvii

13 Table Name: Sessions xviii

14 Table Name: Terms xix

iii

Fitness Zone FZ Database Data Dictionary

1 Table Name: Address

Schema: Address (addressIndex, street, city, state, zipCode, country, personIndex)

· Meaning: Stores a Person’s address.

· Implementation of the association {Address, Persons}.

· Primary key: addressIndex

· Other candidate keys: personIndex

· Foreign keys: personIndex of Persons

· Indices: addressIndex,

· FD: addressIndex -> street, city, state, zipCode, country, personIndex

zipCode ->street, city, state, country

o Highest Normal Form: 3NF

Attributes

Attribute Name / Meaning / Data Type with Constraint / NULLABLE / Other constraints / Default Value
addressIndex / An integer to identify an address / Long Integer / No / No duplicates allowed / None
street / String that describes the street name where a person in Persons lives / Varchar(50) / No
city / String that describes the city where the street is found / Varchar(25) / No
state / String that describes the state of residence for a person in Persons / Varchar(25) / No
zipCode / String that describes the zip code for the city / Varchar(25) / No
country / Country of where the street is found / Varchar(25) / yes
personIndex / An integer to associate this address with a specific person in Persons / Long Integer / No / Foreign referential integrity / None

2 Table Name: EmergencyContacts

Schema: EmergencyContacts (contactIndex, firstName, lastName, personIndex, relationshipIndex, phoneNumber)

· Meaning: Stores a Person’s emergency contact information.

· Implementation of the association {EmergencyContacts, Persons} {EmergencyContacts, Relationships}

· Primary key: contactIndex

· Other candidate keys: personIndex

· Foreign keys: personIndex of Persons, relationshipIndex of Relationships

· Indices: contactIndex,

· FD: contactIndex -> firstName, lastName, personIndex, relationshipIndex, phoneNumber

o Highest Normal Form: 4NF

Attributes

Attribute Name / Meaning / Data Type with Constraint / NULLABLE / Other constraints / Default Value
contactIndex / An integer to identify a contact person / Long Integer / No / No duplicates allowed / None
firstName / String that describes the first name of a contact person / Varchar(50) / No / - / None
lastName / String that describes the last name of a contact person / Varchar(50) / No / - / None
personIndex / String that describes the city where the street is found / Long Integer / No / Foreign referential integrity / None
relationshipIndex / String that describes the state of residence for a person in Persons / Long Integer / No / Foreign referential integrity / None
phoneNumber / String that describes the zip code for the city / Varchar(15) / No / - / None

3 Table Name: Guests

Schema: Guests (guestIndex, checkInDate, personIndex)

· Meaning: Stores a Person’s guest visit record/count

· Implementation of the association: {Guests, Persons}

· Primary key: guestIndex

· Other candidate keys:

· Foreign keys: personIndex of Persons

· Indices: guestIndex,

· FD: guestIndex-> checkInDate, personIndex

o Highest Normal Form: 4NF

Attributes

Attribute Name / Meaning / Data Type with Constraint / NULLABLE / Other constraints / Default Value
guestIndex / An integer to identify a guest record / Long Integer / No / No duplicates allowed / None
checkInDate / Date that describes the date when a guest checked in, courtesy of a person in Persons / DateTime / No / Mm/dd/yyyy / None
personIndex / An integer that identifies the person in Persons, who invited a guest / Long Integer / No / Duplicates allowed
Foreign referential integrity / None

4 Table Name: Lockers

Schema: Lockers (lockerIndex, rented, section, lockerNumber)

· Meaning: Stores a particular locker’s information

· Implementation of the association

· Primary key: lockerIndex

· Other candidate keys: {}

· Foreign keys:

· Indices: lockerIndex,

· FD: lockerIndex -> rented, section, lockerNumber

o Highest Normal Form: 5NF

Attributes

Attribute Name / Meaning / Data Type with Constraint / NULLABLE / Other constraints / Default Value
lockerIndex / An integer to identify a locker in Lockers / Long Integer / No / No duplicates allowed / None
rented / Boolean that determines whether this locker is rented or not / BIT
YES/NO / No / - / No
section / String that describes the section to which this locker belongs / Varchar(50) men/women / No / - / None
lockerNumber / String that describes the locker number used external to this database for renting purposes / Long Integer / No / - / None

5 Table Name: MemberPrograms

Schema: MemberPrograms (personIndex, programIndex, startDate, endDate, progress, fee, goals)

· Meaning: Stores a record for a person in Person, participating in a program in Programs

· Implementation of the association

- {MemberPrograns, Persons}

- {MemberPrograns, Programs}

· Primary key: {personIndex, programIndex}

· Other candidate keys: {}

· Foreign keys: personIndex of Persons, programIndex of Programs

· Indices: {personIndex, programIndex} -- compound key

· FD: {personIndex, programIndex} -> rented startDate, endDate, progress, fee, goals

o Highest Normal Form: 5NF

Attributes

Attribute Name / Meaning / Data Type with Constraint / NULLABLE / Other constraints / Default Value
personIndex, programIndex / Compound key consisting foreign keys from Persons and Programs to identify a participation by a Person in a Program / Long Integer, Long Integer / No / No duplicates allowed for the compound key
Individual foreign keys can be duplicated / None
startDate / Date that describes when the person joined the program / BIT
YES/NO / No / Mm/dd/yyy / No
endDate / Date that describes when the person leaves the program / Varchar(50) men/women / No / Mm/dd/yyy / None
progress / Describes the progress that a person has made based on the goal / Memo (long char) / No / - / None
fee / Us dollars amount to be paid by the person joining the program / Currency (+d.dd)
goals / A string describing the goals set by the person who is joining the program

6 Table Name: Membership

Schema: Membership (membershipIndex, personIndex, termIndex, joinDate, endDate,

membershipStatus, medicalCondition, paymentStatus,

paymentType, comment)

· Meaning: Stores a record for a person’s membership to the Fitness Zone center

· Implementation of the association

- { membershipIndex, Persons}, { membershipIndex, termIndex}

· Primary key: membershipIndex

· Other candidate keys: {}

· Foreign keys: personIndex of Persons, termIndex of Terms

· Indices: membershipIndex

· FD: membershipIndex -> personIndex, termIndex, joinDate, endDate, membershipStatus, medicalCondition, paymentStatus, paymentType, comment

· Highest Normal Form: 4NF

Attributes

Attribute Name / Meaning / Data Type with Constraint / NULLABLE / Other constraints / Default Value
membershipIndex / An integer which identifies a membership record for a person in Persons / Long Integer / No / No duplicates / None
personIndex / An integer which identifies a person in Persons who has membership at the Fitness Center / Long Integer / No / Foreign referential integrity / No
termIndex / An Integer which identifies the type of membership for this record as specified in the Terms table / Long Integer / No / Foreign referential integrity / None
joinDate / Date that describes when the person registered as member at the Fitness Zone / Datetime / No / Mm/dd/yyy / None
endDate / Date that describes when membership to the Fitness Zone expires / Datetime / No / Mm/dd/yyy / None
membershipStatus / String describing the current status of a membership, expired, pending, active etc / No / - / Pending
medicalCondition / Boolean which indicates whether the medical conditions for participating in the FZ activities are met / Bit
Yes/no / No / - / None
paymentStatus / Boolean which describes whether the member has paid the membership fee / Bit
Yes/no / No / - / None
paymentType / String describing how the fee was paid, cash/check/tuition/payroll deduct / Varchar(20) / No / - / None
comment / A string describing the goals set by the person who is joining the program / Varchar(50) / No / - / None

7 Table Name: Programs

Schema: Programs (programIndex, programName, offered, startDate, endDate, progressUnit)

· Meaning: Stores information about a Program that is offered at the Fitness Zone

· Implementation of the association

· Primary key: {programIndex}

· Other candidate keys: programName

· Foreign keys:

· Indices: programIndex

· FD: programIndex -> programName, offered, startDate, endDate, progressUnit

- programName-> offered, startDate, endDate, progressUnit

o Highest Normal Form: 4NF

Attributes

Attribute Name / Meaning / Data Type with Constraint / NULLABLE / Other constraints / Default Value
programIndex / An integer which uniquely identifies a program in Programs / Long integer / No / No duplicates allowed for the compound key
Individual foreign keys can be duplicated / None
programName / A string describing the name of the program / Varchar(50) / No / unique / No
startDate / Date that describes when the program starts / Date time / No / Mm/dd/yyy / No
endDate / Date that describes when the program ends / Varchar(50) men/women / No / Mm/dd/yyy / None
progress / Describes the progress that a person has made based on the goal / Memo (long char) / No / - / None

8 Table Name: Persons

Schema: Programs (personIndex, firstName, middleName, lastName, birthdate, gender,

driverLicense, typeIndex, telephone, email, account, userPassword )

· Meaning: Stores information about a person who is a member of the fitness Zone, or other member participating in other programs at the Fitness Zone

· Implementation of the association {Person, Terms}

· Primary key: personIndex

· Other candidate keys: driverLicense

· Foreign keys: TermIndex of Terms

· Indices: ProgramIndex, driverLicense

· FD:

- personIndex -> firstName, middleName, lastName, birthdate, gender, driverLicense, typeIndex, telephone, email, account, userPassword, uhclID

- account --> personIndex, firstName, middleName, lastName, birthdate, gender, driverLicense, typeIndex, telephone, email, account, userPassword , uhclID

- driverLicense –>uhclID, account , personIndex, firstName, middleName, lastName, birthdate, gender, typeIndex, telephone, email, account, userPassword

o Highest Normal Form: 4NF

Attributes

Attribute Name / Meaning / Data Type with Constraint / NULLABLE / Other constraints / Default Value
personIndex / An integer which uniquely identifies a person in Persons / Long integer / No / No duplicates allowed / None
uhclID / String describing the UHCL ID of the member / Varchar(20) / Yes / No duplicates / none
firstName / A string describing the first name of a person / Varchar(50) / No / - / No
middleName / A string describing the middle name of a person / Varchar(50) / No / - / No
lastName / A string describing the last name of the person / Varchar(50) / No / - / No
birthdate / Date that describes when the person was born / Datetime / No / Mm/dd/yyy / No
gender / String indicating whether the member is a male or female / Varchar(15) / No / Male/female / None
driverLicense / String describing the driver license number for the person / Varchar(50) / Yes / - / None
typeIndex / An integer in PersonTypes describing the person’s category / Long Integer / No / Student/faculty/alumni etc
Foreign referential integrity / None
telephone / A valid USA telephone number / Varchar(50) / No / 999-999-9999 / None
email / String describing the email address for the person / Varchar(50) / No / unique / None
account / String describing the account name for this person to log into the system / Varchar(15) / No / unique / None
userPassword / A string describing the password to the account for this person / Varchar(15) / No / unique / None

9 Table Name: PersonType

Schema: Programs (personTypeIndex, description)

· Meaning: Stores information about types of persons at the Fitness Zone e.g. Student, faculty, staff, alumni etc

· Implementation of the association

· Primary key: personTypeIndex

· Other candidate keys:

· Foreign keys:

· Indices: personTypeIndex

· FD: personTypeIndex -> description

o Highest Normal Form: 5NF

Attributes

Attribute Name / Meaning / Data Type with Constraint / NULLABLE / Other constraints / Default Value
personTypeIndex / An integer which uniquely identifies the category or type of person in PersonTypeIndex as used in Persons / Long integer / Auto number / No duplicates allowed / None
description / A string describing the person type in detail / Varchar(50) / No / - / No

10 Table Name: Relationships

Schema: Programs (relationshipIndex, description)

· Meaning: Stores information about the relationships that are allowed between dependants or guests and a person in Persons

· Implementation of the association

· Primary key: relationshipIndex

· Other candidate keys: description

· Foreign keys:

· Indices: relationshipIndex , description

· FD: relationshipIndex -> description, description -> relationshipIndex

o Highest Normal Form: 5NF

Attributes

Attribute Name / Meaning / Data Type with Constraint / NULLABLE / Other constraints / Default Value
relationshipIndex / An integer which uniquely identifies the type of relationship in Relationships / Long integer / Auto number / No duplicates allowed / None
description / A string describing the name of the relationship type / Varchar(50) / No / - / No

11 Table Name: Rentals