Cw Comp1302

Only available on StudyMode
  • Download(s) : 50
  • Published : November 7, 2012
Open Document
Text Preview
FPT University – University of Greenwich
Topup Program – Ho Chi Minh City Centre
590 Cach Mang Thang Tam, District 3, HCMC

DATABASE DESIGN AND IMPLEMENTATION
COURSEWORK REPORT

Student Name: Bui Minh Quang
Class: GT60027
Lecturor: Huynh Anh Vu
Date of Submission: November 8th 2010

Database Design and Implementation – Coursework Report

Class: GT60027 – Student Name: Bui Minh Quang

Table of Contents
1.
2.
3.
4.
5.
6.

Business Rules ………………………………………………………………………3 Entity Relationship Diagram ………………………………………………………4 Relation Schema …………………………………………………………………… 5 3rd NF Checking …………………………………………………………………….6 SQL Script for A1 – A4 …………………………………………………………….7 Screen dumps …………………………………………………………………...8 -14

Page 2

Database Design and Implementation – Coursework Report

Class: GT60027 – Student Name: Bui Minh Quang

BUSINESS RULES
- System will record one team as the “Home team” (the team owns the basketball court that will be played on) and the remaining team as “Away team”.
- Amateurs also have to sign a contract wit h the team but their salary is zero. System will also record total amount of expenses that the team spent for that player.
- Each player (including amateur and professional) will be identified by their SSN (Social Security Number, aka ID). - System also records the main manager, coach and owner (only one current person in each field) of each club. - The previous contracts information of any player will be saved in the system. - Each team can have many playing courts but must have at least one. - Only record matches in the tournament. For international and other national matches, we only count the number (Therefore the total national match may be different fro m total match in the tournament). - A team can play on other team’s court as “Home team”. - If a team causes a penalty, the opponent team may get two free throws. Each successful free throw will be rewarded one score.

- Each match consists of two 20-minutes halves.
- In a match, player can play in various positions.

Page 3

Database Design and Implementation – Coursework Report

Class: GT60027 – Student Name: Bui Minh Quang

ENTITY-RELATIONSHIP DIAGRAM
Note

AMATEUR

PROFESSIONAL

M

TwoPtsMade

TwoPtsAttempt

SSN

HAS

d

HomeTeamScore
ThreePtsAttempt

MinutePlayed

AwayTeamScore

ThreePtsMade

N
M

PLAYER

N

ATTENDS

MATCH

Foul

HOBBIES

FreeThrowMade
Rebound

M

StartDate

HobbyID

1

PLAY_IN

EndDate

FreeThrowAttempt
HomeTeam

SIGNS

N

N

ContractID

N

POSITION

PLAY_FOR

N

CONTRACT

1

HAS

N

1

Color note:
Entity
Entity with Subclasses
Relationship
Relationship with Attributes
Primary Key Attribute
Relationship Attribute
Derived Attribute

PLAY_ON

1

N
PositionID

MatchID

(2,2)

GIVES

1

TEAM

1

OWNS

N

COURT

TeamID
CourtID

Page 4

Database Design and Implementation – Coursework Report

Class: GT60027 – Student Name: Bui Minh Quang

RELATIONAL SCHEMA
Hobbies (HobbyID, HobbyName, Description);

HobbyDetails (HobbyDetailsID, HobbyID, SSN, Note); /*HobbyID referencing Hobbies.HobbyID, SSN referencing Player.SSN*/

Player (SSN, TeamID, FirstName, MidName, LastName, Height, Weight, DateOfBirth, NoOfIntMatch, NoOfNatMatch, PlayerType, Occupation, NameOfInstitute, TotalExpenses); /*TeamID referencing Team.TeamID*/

PostionDetails (PositionDetailsID, PositionID, SSN, StartDate, EndDate); /*PositionID referencing Position.PositionID, SSN referencing Player.SSN*/

Position (PositionID, PositionName, Description);

Contract (ContractID, SSN, TeamID, Type, DateOfStart, FinishDate, Salary, BindingCondition); /*TeamID referencing Team. TeamID, SSN referencing Player.SSN*/

Team (TeamID, TeamName, Province, District, Number, ManagerFName, ManagerMName, ManagerLName, CoachFName, CoachMName, CoachLName, OwnerFName, OwnerMName, OwnerLName);

Performance (PerformanceID, SSN,...
tracking img