Cw Comp1302 Essay

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 ……………………………………………………………………. 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.

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!


order now

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, MatchID, MinutePlayed, TwoPtsAttempt, TwoPtsMade, ThreePtsAttempt, ThreePtsMade, FreeThrowAttempt, FreeThrowMade, Foul, Rebound, HomeTeam); /*SSN referencing Player. SSN, MatchID referencing Match. MatchID*/ Match (MatchID, CourtID, AwayTeamID, HomeTeamID, Date, HomeTeamScore, AwayTeamScore); /*CourtID referencing PlayingCourt. CourtID, AwayTeamID and HomeTeamID referencing Team.

TeamID*/ PlayingCourt (CourtID, TeamID, CourtName, Province, District, Street, Number, Postcode); /*TeamID referencing Team. TeamID*/ Note: Colored arrows for distinguishing purpose only. Page 5 Database Design and Implementation – Coursework Report Class: GT60027 – Student Name: Bui Minh Quang 3rd NF Checking The Relational Schema satisfies 3NF criteria. Page 6 Database Design and Implementation – Coursework Report Class: GT60027 – Student Name: Bui Minh Quang SQL Script for A1 – A4 A1. SELECT Player. SSN, (Player. LastName + ‘ ‘ + Player. MidName + ‘ ‘ + Player.

FirstName) AS ‘Player Name’, Player. NoOfIntMatch, Player. NoOfNatMatch FROM Player WHERE (Player. NoOfIntMatch + Player. NoOfNatMatch) > 20 A2. DECLARE @CourtID int DECLARE @date Date SET @CourtID = 1 SET @Date = ‘2010-05-21’ SELECT SUM(Performance. Foul) AS ‘Number of Fouls’ FROM Performance WHERE Performance. MatchID IN (SELECT Match. MatchID FROM Match INNER JOIN Performance ON Match. MatchID = Performance. MatchID WHERE Match. CourtID = @CourtID AND Match. MatchDate = @date) A3. DECLARE @SSN int DECLARE @PlayerName varchar(62) SET @SSN = 013913476 SET @PlayerName = ‘Phan Anh Minh’

SELECT COUNT(Performance. SSN) AS ‘Number of Supposedly Played Match’ FROM Performance INNER JOIN Player ON Performance. SSN = Player. SSN WHERE Performance. MinutePlayed = 0 AND (Performance. SSN = @SSN OR (Player. LastName + ‘ ‘ + Player. MidName + ‘ ‘ + Player. FirstName = @PlayerName)) A4. DECLARE @MatchID int SET @MatchID = 8 SELECT Player. SSN, (Player. LastName + ‘ ‘ + Player. MidName + ‘ ‘ + Player. FirstName) AS ‘Full Name’, Performance. ThreePtsMade, Performance. TwoPtsMade, Performance. FreeThrowMade FROM Player INNER JOIN Performance ON Performance. SSN = Player.

SSN WHERE (Performance. ThreePtsMade*3 + Performance. TwoPtsMade*2 + Performance. FreeThrowMade) = (SELECT TOP(1)(Performance. ThreePtsMade*3 + Performance. TwoPtsMade*2 + Performance. FreeThrowMade) AS a FROM Performance WHERE Performance. MatchID = @MatchID ORDER BY a DESC) Page 7 Database Design and Implementation – Coursework Report Class: GT60027 – Student Name: Bui Minh Quang Screendump for A1 Page 8 Database Design and Implementation – Coursework Report Class: GT60027 – Student Name: Bui Minh Quang Screendump for A2 Page 9 Database Design and Implementation – Coursework Report

Class: GT60027 – Student Name: Bui Minh Quang Screendump for A3 Page 10 Database Design and Implementation – Coursework Report Class: GT60027 – Student Name: Bui Minh Quang Screendump for A4 Page 11 Database Design and Implementation – Coursework Report Class: GT60027 – Student Name: Bui Minh Quang Screendump for D7 Page 12 Database Design and Implementation – Coursework Report Class: GT60027 – Student Name: Bui Minh Quang Screendump for D8 Page 13 Database Design and Implementation – Coursework Report Class: GT60027 – Student Name: Bui Minh Quang Screendump for D9 Page 14