Project Two

CIS164AG - Project Two - Normalization

T. A. S. K. - Tim's Animal Services and Kennels

In this second project assignment you will be introduced to a new part of T.A.S.K. - our obedience and play classes (The information contained in this project SHOULD NOT be included in Project One) In order to complete this assignment you will examine the some raw data. Then you will use the raw data and a brief description to identify the functional dependencies. Based upon this information you will create several tables that have the correct attributes and primary keys. These tables must be in third normal form.

PART ONE:
Use the Raw Class Data to list all of the attributes for these tables. Examine each attributes relationship to each other attribute to determine which attributes are functionally dependent upon which other attributes. List ALL of the functional dependencies using the table provided. This listing should include all dependencies - not just your final normalized items. This should include any transitive dependencies or partial key dependencies.

For example if you were given:
Employee(EmpID, EmpSSNO, EmpAddress, EmpAge, EmpSalary)
Then the functional dependencies would be:

Attribute Determines these attribute(s)

EmpID EmpSSNO, EmpAddress, EmpAge, EmpSalary

EmpSSNO EmpID, EmpAddress, EmpAge, EmpSalary

Do not list any relationships that do not demonstrate a functional dependency.

PART TWO:
Write the tables that will be needed to model this data. Include all of the correct attributes in the correct tables. Identify the primary key of each table. All tables must be in third normal form.

i.e. Employee(EmpID, EmpAddress, EmpAge, EmpSalary)

Staple your work to the Project Two cover sheet.

Assumptions and Explanations

Id	This is the identifier of each class.
Description	A brief description of the class.
Level	A brief indicator of the level (degree of sophistication) of the class.
Sec.	The Section number that identifies a specific offering of a class.
Cost	The amount that the participant will pay.
Enr.	The number enrolled in a specific course offering.
Loc.	The location code where a specific class is taught.
Region	The specific region that a location belongs in.
Instructor	The name of the person teaching a specific course offering.
Avail	The instructor's availability.
Pre-req	The class (if any) is a prerequisite for this class.

A class may have one or more sections.
Every offering of a class has a unique section ID.
All sections of a particular class have the same cost.
Each class section will vary in the number of people taking that section, the length of that section, the place that that section is taught and the instructor for that section.
The prerequisite is based upon the class. These will not change from one section to the next within a class.
The places that the classes are taught have a location identifier and a region.
The person teaching a specific section has a unique name and may have only certain availability.
However, the instructor's availability will not vary from one class or section to the next. The instructor's availability is based only on the instructor.

Raw Class Data

The following is the raw data that we use for offering and keeping track of our classes.

Id	Description	Level	Sec.	Cost	Enr.	Loc.	Region	Instructor	Avail	Pre-req.
RDO	Dog Obed.	Remedial	103	45.00	20	K1	West	Phil Smith	Day	None
RDO	Dog Obed.	Remedial	105	45.00	13	K2	South	Kay Ciles	Any	None
RDO	Dog Obed.	Remedial	106	45.00	22	K1	West	Phil Smith	Day	None
RDO	Dog Obed.	Remedial	108	45.00	24	K3	North	Kay Ciles	Any	None
BDO	Dog Obed.	Beginning	111	55.00	15	K4	East	Phil Smith	Day	None
BDO	Dog Obed.	Beginning	112	55.00	22	K2	South	Kate Merril	Any	None
BDO	Dog Obed.	Beginning	113	55.00	17	K1	West	M Harrison	Night	None
BDO	Dog Obed.	Beginning	114	55.00	28	K3	North	Phil Smith	Day	None
BDO	Dog Obed.	Beginning	115	55.00	11	K5	West	T Tobark	Any	None
BDO	Dog Obed.	Beginning	116	55.00	29	K6	North	Dan Block	Night	None
BDO	Dog Obed	Beginning	117	55.00	33	K8	West	Phil Smith	Day	None
BDO	Dog Obed.	Beginning	119	55.00	12	K9	Central	Dan Gross	Any	None
IDO	Dog Obed.	Interm.	121	65.00	15	K5	West	Phil Smith	Day	BDO
IDO	Dog Obed.	Interm.	123	65.00	16	K9	Central	Phil Smith	Day	BDO
IDO	Dog Obed.	Interm.	124	65.00	11	K7	East	J Woods	Any	BDO
IDO	Dog Obed.	Interm.	126	65.00	19	K10	South	Dan Block	Night	BDO
ADO	Dog Obed.	Advanced	133	65.00	11	K6	North	Dan Block	Night	IDO
ADO	Dog Obed.	Advanced	135	65.00	14	K4	East	T Tobark	Any	IDO
ADO	Dog Obed.	Advanced	136	65.00	9	K2	South	Dan Block	Night	IDO
PUR1	Cat Obed.	Beginning	141	25.00	3	K1	West	Kate Merril	Any	None
PUR1	Cat Obed.	Beginning	142	25.00	5	K8	West	Kate Merril	Any	None
PUR2	Cat Obed.	Advanced	155	25.00	4	K11	Central	Kate Merril	Any	PUR1
PLAY	Play Time	None	161	FREE	15	K3	North	Kay Ciles	Any	None
PLAY	Play Time	None	162	FREE	18	K3	North	Kay Ciles	Any	None
VTV	Visit the Vet	None	171	FREE	7	K6	North	Deb Glass	Any	None
VTV	Visit the Vet	None	172	FREE	13	K2	South	Deb Glass	Any	None
VTV	Visit the Vet	None	173	FREE	9	K11	Central	Kate Merril	Any	None
SP	Senior Play	None	180	FREE	29	K3	North	Kay Ciles	Any	None
SP	Senior Play	None	190	FREE	19	K3	North	KayCiles	Any	None

Attribute	Determines these attribute(s)
EmpID	EmpSSNO, EmpAddress, EmpAge, EmpSalary
EmpSSNO	EmpID, EmpAddress, EmpAge, EmpSalary