Fall
Phase 1: Basic Data Modeling Noah Levin Creating an Entity Relationship Diagram, Database Design, and Database Dictionary for the Western Pennsylvania Chess Club (WPCC) online website.
67-202 Project :: Application Design and Development
08
Phase 1: Table of Contents 67-272 Project
Noah Levin Fall 2008
Table of Contents Entity-Relationship Diagram .............................................................................. 1 Database Design ................................................................................................. 2 Database Dictionary ............................................................................................ 3 Families ....................................................................................................................................... 3 Addresses ..................................................................................................................................... 3 Students .................................................................................................................................... 3, 4 Tournament Registrations............................................................................................................ 4 Tournaments ................................................................................................................................ 4 Sections ........................................................................................................................................ 5 Class Registrations ...................................................................................................................... 5 Classes ...................................................................................................................................... 5,6 Locations...................................................................................................................................... 6 Instructor Registrations ............................................................................................................... 7 Instructors .................................................................................................................................... 7
Entity Relationship Diagram 67-272 Project
Noah Levin Fall 2008
v
Bold + Underlined: Primary Key Italics: Foreign Key
1
Database Design 67-272 Project
Noah Levin Fall 2008
Database Design in 3NF Families (family_id, student_id, address_id, login, password, parent_first_name, parent_last_name, phone, email) Addresses (address_id, street_1, street_2, city, state, zip) Students (student_id, family_id, first_name, last_name, grade, date_of_birth, rank, rating, enroll_status) Tournament_Registrations (registration_id, student_id, tournament_id) Tournaments (tournament_id, section_id, location_id, name, date, entry_fee, blurb) Sections (section_id, tournament_id, student_id, name, rating_min, rating_max, clock_type) Class_Registrations (registration_id, student_id, class_id, waitlist) Classes (class_id, instruction_id, location_id, lead_instructor, title, blurb, start_date, end_date, start_time, end_time, class_size) Locations (location_id, name, facility_type, street_1, street_2, city, state, zip, lat, lon) Instructor_Registrations (registration_id, instruction_id, class_id) Instructors (instructor_id, first_name, last_name, bio, picture, email, phone)
Underlines: Solid underlined fields are primary keys; Dotted underlined fields are foreign keys; Double underlined fields are composite keys that are both primary and foreign keys.
Database Design Notes: 1.
Enroll status refers to whether a student has withdrawn from the WPCC or not.
2.
A blub refers to a short description about an entity.
2
Data Dictionary 67-272 Project
Noah Levin Fall 2008
Families Field
Data Type
Description
Example Data
family_id (pk)
INT
The ID for this table (Required; auto-increment)
2
student_id (fk)
INT
The ID of any student(s) in the family.
2
address_id (fk)
INT
The ID of the family’s address (Req.)
2
login
VARCHAR(10)
A form of ID that the family uses to login to the WPCC web page (Req.)
chess_champ
password
VARCHAR(12)
Password for the family’s login to the WPCC web page (Req; 4 – 12 characters
abc123
parent_first_name
CHAR(12)
First name of the primary parent of a family (Req.)
parent_last_name
CHAR(12)
Last name of the primary parent (Req.)
Smith
phone
INT(10)
Family phone number (Req.)
412-555-2131
VARCHAR(40)
Family email. Used to verify account and often used to contact a family (Req.)
bfischer@aol.com
Jane
Addresses Field
Data Type
Description
Example Data
address_id (pk)
INT
The ID for this table (Required; auto-increment)
2
street_1
VARCHAR(50)
Primary street address line (Req.)
5000 Forbes Ave
street_2
VARCHAR(50)
Secondary street address line
SMC 1234
city
CHAR(15)
Name of the city. (Req.)
Pittsburgh
state
CHAR(2)
Abbreviation of the state (Req.)
PA
zip
INT
Zip code of the city (Req.)
15238
Students Field
Data Type
Description
student_id (pk)
INT
The ID for this table (Required; auto-increment)
family_id (fk)
INT
The ID of the student’s family (Required)
Example Data 2 2
3
Data Dictionary 67-272 Project
Noah Levin Fall 2008
first_name
CHAR(12)
Student’s first name
John
last_name
CHAR(12)
Student’s last name
Smith
grade
INT(10)
Student’s grade in school (Req.; 0-12, 0 = Kindergarden)
6
date_of_birth
DATE
Date of birth, used to determine Student’s age (Req.)
02-25-88
rank
INT
USCF Ranking
120
rating
INT
USCF Rating
765
enroll_status
BIT
1 if active; 0 if withdrawn
1
Tournament_Registrations Field
Data Type
Description
Example Data
registration_id (pk)
INT
The ID for this table (Required; auto-increment)
student_id (fk)
INT
The ID for the student registering
2
tournament_id (fk)
INT
Minimum rating of student. Cannot be < 0 (Required)
2
2
Tournaments Field
Data Type
tournament_id (pk)
INT
The ID for this table (Required; auto-increment)
2
section_id (fk)
INT
The name of the curriculum (Required)
2
location_id (fk)
INT
The ID of the location where the tournament is taking place (Req.)
2
name
VARCHAR(50)
Title of the tournament (Req.)
Christmas Chess Tourney
date
DATE
Date of the tournament (Req.)
02-25-2008
entry_fee
SMALLMONEY
Cost of entry for the tournament. (Req.)
15
Brief summary of the tournament.
In each section, trophies to top 5 players. All players tied for ribbons will receive one.
blurb
VARCHAR(500)
Description
Example Data
4
Data Dictionary 67-272 Project
Noah Levin Fall 2008
Sections Field
Data Type
Description
Example Data
section_id (pk)
INT
The ID for this table (Required; auto-increment)
2
tournament_id (fk)
INT
The ID of the tournament the section is in (Req.)
2
student_id (fk)
INT
The ID for the student(s) enrolled in the section
2
name
VARCHAR(50)
Name of the section (Req.)
K-5 Beginners
rating_min
INT(4)
Minimum rating to play in the section (Req.)
0
rating_max
INT(4)
Maximum rating to play in the section (Req.)
499
clock_type
VARCHAR(4)
Time controls (Req.)
G/30
Class_Registrations Field
Data Type
Description
Example Data
registration_id (pk)
INT
The ID for this table (Required; auto-increment)
student_id (fk)
INT
The ID for the student registering for the class (Req.)
2
class_id (fk)
INT
The ID for the class the student or instructor is registering for (Req.)
2
waitlist
BIT
1 if waitlisted; 0 if enrolled
0
2
Classes Field
Data Type
Description
Example Data
class_id (pk)
INT
The ID for this table (Required; auto-increment)
2
intruction_id (fk)
INT
The ID for the instructor(s) teaching the course (Req.)
2
location_id (fk)
INT
The ID for the location of the course. (Req.)
2
lead_instructor
CHAR(25)
Name of the instructor assigned to lead role. (Req.)
Bobby Fischer
title
VARCHAR(50)
Title of the chess class (Req.)
Opening Moves 101
5
Data Dictionary 67-272 Project
Noah Levin Fall 2008
blurb
VARCHAR(500)
Brief summary of the class. Often includes information about recommended skill levels.
This class teaches gives a basic introduction to strategic opening moves in chess. Recommended for all skill levels.
start_date
DATE
Date of the first class (Req.)
02/02/08
end_date
DATE
Date of the last class (Req.)
02/25/08
start_time
TIME
Time the class begins for a given class day (Req.)
7:00 PM
end_time
TIME
Time the class ends for a given class day (Req.)
9:30 PM
class_size
TINYINT
Maximum occupancy of the class. Often based on location. (Req.)
15
Locations Field
Data Type
Description
Example Data
location_id (pk)
INT
The ID for this table (Required; auto-increment)
name
VARCHAR(50)
Name of the location (Req.)
Carnegie Library of Pittsburgh: Sq. Hill
facility_type
CHAR(10)
The type of facility for the location (Req; Examples: Library, Church, University)
Library
street_1
VARCHAR(30)
Primary Address Line of the location (Req.)
5801 Forbes Ave
street_2
VARCHAR(30)
Secondary Address Line of the location
P.O. Box 2051
city
CHAR(15)
City of the location (Req.)
Pittsburgh
state
CHAR(2)
Abbreviation for the State of the location (Req.)
PA
zip
INT
Zip code of the location (Req.)
15217
lat
INT
Latitude coordinates of the location
40.576586
lon
INT
Longitude coordinates of the location
-80.076606
2
6
Data Dictionary 67-272 Project
Noah Levin Fall 2008
Instructor_Registrations Field
Data Type
Description
Example Data
registration_id (pk)
INT
The ID for this table (Required; auto-increment)
2
instruction_id (fk)
INT
The ID of the instructor registering to teach the class (Req.)
2
class_id
INT
The ID of the class the instructor is registering to teach (Req.)
2
Instructor Field
Data Type
Description
Example Data
instructor_id (pk)
INT
The ID for this table (Required; auto-increment)
first_name
VARCHAR(25)
First name of the instructor (Req.)
Bobby
last_name
VARCHAR(25)
Last name of the instructor (Req.)
Fischer
2
bio
VARCHAR(500)
Short biography about the instructor.
Bobby Fischer is an American-born chess Grandmaster, and the eleventh World Chess Champion.
picture
IMAGE
Picture of the instructor
photo.jpg
VARCHAR(40)
Email of the instructor (Req; method of contact is necessary if a registered student/parent has questions)
bfischer@aol.com
phone
INT(10)
Phone number of the instructor
412-555-2131
7