Database Management

Only available on StudyMode
  • Download(s) : 74
  • Published : January 1, 2013
Open Document
Text Preview
Coursework Header Sheet 172806-18

Course Coursework Tutor

COMP1302: DB Design & Implementation DBDI - COLOMBO - DEC 10 - AC A Al-Zobaidie

Course School/Level Assessment Weight Submission Deadline

CM/UG 50.00% 09/11/2010

Coursework is receipted on the understanding that it is the student's own work and that it has not, in whole or part, been presented elsewhere for assessment. Where material has been used from other sources it has been properly acknowledged in accordance with the University's Regulations regarding Cheating and Plagiarism.

000592677 Tutor's comments

Grade For Office Use Only__________ Awarded___________ Moderation required: yes/no Tutor______________________

Final Grade_________ Date _______________

[INOKA MEDAGODA COMP 1302]

Page 1

Table of Contents

Content

Page

D1. Business Rules D2. ER Diagram D3. Relational Schema D4. Normalization D5. Database View D6. SQL Codes D7. Player Registration Form D8. Report D9. Master detail form Reference

02 - 03 03 - 04 04 - 05 05 - 06 06 - 07 07 - 08 08 - 11 11 - 13 13 – 15 15 - 16

[INOKA MEDAGODA COMP 1302]

Page 2

D1. BUSINESS RULES

1. An amateur player can have only one hobby. 2. A Match can be uniquely identified by its match date and the place. Therefore only one match can be played in a particular court at a particular day. 3. A club will have only one team therefore a club can be identified by the team. 4. A player can have multiple positions but at a given time slot he should hold a particular position.

[INOKA MEDAGODA COMP 1302]

Page 3

D2. ER DIAGRAM

FName

LName Hobby Salary Start_date Team No

Name Age
Amateur Professional

M

1
Belongs To

Team

Address

Player ID D Street

City

1

Postcode

Player
1 M M
Holds Perform_In

M

Match_Date

M M

Matches
Match_Place

M
Position ID

Position

Winning Team

Player of the match

Experiance

[INOKA MEDAGODA COMP 1302]

Page 4

D3. RELATIONAL SCHEEMA

Match (match_date, match_Place, Type, Score,Team Id) Player_Team_History (PlayerID, TeamID, Start_date)

Team (TeamID, Name, Postal code, Street, City)

PLAYER (PlayerID, fname, lname, DOB, Height, Weight, Type, TeamID)

AMATURE (PlayerId, occupation, institute, hobby)

Performance (2PA, 2pm, 3PM, 3PA, 3PM, FTA, FTM, Rebounce, Fouls, PlayerId, Team_Id, match_date,match_place)

PROFESSIONAL (Start_date, End_date, Salary, PlayerID)

POSITION (PositionID, start_date, position, end_date, playerID)

[INOKA MEDAGODA COMP 1302]

Page 5

D.4 Normalization The Relational Schema provided above satisfies the 3NF Criteria.

[INOKA MEDAGODA COMP 1302]

Page 6

D5. Access View

[INOKA MEDAGODA COMP 1302]

Page 7

D6. SQL CODES

1. SELECT PERFORMANCE.PLAYER_ID, PLAYER.FIRST_NAME, PLAYER.LAST_NAME, Count(PERFORMANCE.PLAYER_ID) AS NO_OF_MATCHES FROM PLAYER INNER JOIN PERFORMANCE ON PLAYER.PLAYER_ID=PERFORMANCE.PLAYER_ID GROUP BY PERFORMANCE.PLAYER_ID, PLAYER.FIRST_NAME, PLAYER.LAST_NAME HAVING (((Count(PERFORMANCE.[PLAYER_ID]))>20));

2. SELECT Sum(FOULS) AS TOTAL_FOULS FROM PERFORMANCE WHERE (((PERFORMANCE.MATCH_DATE)=[ENTER MATCH DATE]) AND ((PERFORMANCE.MATCH_PLACE)=[ENTER MATCH PLACE]));

3. SELECT PERFORMANCE.PLAYER_ID, PERFORMANCE.MATCH_DATE, PERFORMANCE.MATCH_PLACE, PERFORMANCE.MIN_PLAYED FROM PERFORMANCE WHERE MIN_PLAYED=0 And (PERFORMANCE.PLAYER_ID=[ENTER PLAYER ID]);

4. SELECT P.PLAYER_ID, P.MATCH_DATE, P.MATCH_PLACE, P.TEAM_ID, (P.[2PM]*2+P.[3PM]*3+P.FTM) AS TOTAL_SCORE FROM PERFORMANCE AS P WHERE (P.[2PM]*2+P.[3PM]*3+P.FTM)=(SELECT Max([2PM]*2+[3PM]*3+FTM) FROM PERFORMANCE WHERE MATCH_DATE=[ENTER MATCH DATE] AND MATCH_PLACE=[ENTER MATCH PLACE]);

[INOKA MEDAGODA COMP 1302]

Page 8

D7. Player Registration Form
Design View

Run Time Views

[INOKA MEDAGODA COMP 1302]

Page 9



A particular player should be over 16 years of age

[INOKA MEDAGODA COMP 1302]

Page 10

[INOKA MEDAGODA COMP...
tracking img