ClubStatsID OriginatingFacility TerminatingFacility PointsEarned OCH MatchResult TCH Goals Area Fouls OFC Yellow/RedCards STN BallPossession State Match EndTime Location Trunk_Channels t Date Pa r PortID Result Channel Referee State SIN Port_ID Address Office Play Routes 1 N 20 N Match LineId 1 StartTime MatchNumber
Name: Sanat D ixit
Student Number: 100775970 1 P ractice Questions
P roblem 1A Foreign Keys and E-R D iagram
Trunk_Routes Name
Table SERVICE_SUBSCRIBERS att ribute L I NE ID references Table L I NES att ribute Area Experience(Years) STATIONCODE Table SERVICE_SUBSCRIBERS att ribute SERVICECODE references Table SERVICES a tt r ibute SCODE Table CALL_FORWARD_NUMBERS att r ibute PORTID references Table SUBSCRIBERS PORTID Table SUBSCRIBERS att ribute PORTID references TABLE L I NES att ribute PORTID Table CALLS att r ibute ORIGINATING_FACIL I TY references Table FACIL IT IES att r ibute PORTID Table CALLS att r ibute TERM INATING_FACIL I TY references Table FACIL IT IES att ribute PORTID CALLS att r ibute OCH references Table TRUNK_CHANNELS att r ibute CHANNEL
Service_Subscribers PlayedIn ServiceCodett r ibute 1 a N Employees N Employs PortID N Name Has Address 1 Email N Subscribe MatchStats To Generates 1 1 1 1 Forward Table Officiated To By 1 1 1 5 Connect Results To In 1 1 N 1 PointsTable PositionNumber GamesPlayed GoalsFor GoalsAgainst GoalDifference Points 1 N
Subscribers 1
Table CALLS att r ibute TCH references Table TRUNK_CHANNELS att r ibute CHANNEL
E -R Diagram
P roblem 1B: Relational Algebra Queries 1) A
Name, Area Code, Office Code, Station Code (SUBSCRIBERS
(SUBSCRIBERS.Port ID = L I NES.Port ID) L I NES) 2) B
N ame, Address, Email (SUBSCRIBERS (Port ID = L ineID) AND
(ServiceCode = CW) SERVICE_SUBSCRIBERS)
3) C
Port ID, Foriegn_Switch(TRUN KS ((TRUN KS.Port ID =
TRUNK_CHANNELS.Port ID) A ND (State = Busy)) TRUNK_CHANNELS)
4 ) TEMP TEMP2
Call_ID, Name (CALLS (CALLS.Originating Facility =
SUBSCRIBERS.Port ID) SUBSCRIBERS)
Call_ID, Name (CALLS (CALLS. Terminating Facility = T EMP.Name, TEMP2.Name (TEMP (TEMP.Call_ID =
SUBSCRIBERS.Port ID) SUBSCRIBERS) D(Originator, Terminator) T EMP2.Call_ID) TEMP2) 5) T EMP
Port ID (CALL_FORWARD_NUMBERS (Port ID = L ineID)
A ND ((ServiceCode CFD ) OR (ServiceCode CFB ) OR E (ServiceCode CFN )) SERVICE_SUBSCRIBERS) (TEMP ( TEMP.Port ID = Lines.Port ID) L I NES) 6) F
A rea Code, Office Code, Station Code (CALLS (Call_ID = 101)
A ND (Terminating Facility = Port ID) CALL_FORWARD_NUMBERS)
7) IDLE
(CALLS (Call_ID = 202) A ND (OFC = Office Code = 235)
A ND (STN = Station Code = 7667) A ND (State = Idle) L I NES)
BUSY (CALLS (Call_ID = 202) A ND (OFC = Office Code = 235) A ND (STN = S tation Code = 7667) A ND (State = Busy) L I NES) 8) BUSY RESULT
Port ID (CALLS (State = Busy) A ND (Terminating Facility
= Port ID) L I NES)
Port ID (BUSY (BUSY.Port ID = TREATMENTS.Port ID) A ND (TCode =
BSY) TRATMENTS) 9) TEMP RESULT
TRUN KS.Port ID, TRUN KS.Foriegn_Switch(CALLS
(CALLS.Terminating Facility = TRUN KS.Port ID) TRUN KS)
(TEMP (TEMP.Port ID = TRUNK_ROUTES.Port ID) TRUNK_ROUTES)
10) TEMP
Service (SERVICE (SERVICE.Scode =
Service f COUNT LineID (TEMP)
SERVICE_SUBSCRIBERS.ServiceCode) SERVICE_SUBSCRIBERS) R (SERVICE, NUM_SUBSCRIBERS) 11) TEMP RESULT
ServiceCode (SERVICE_SUBSCRIBERS)
(SERVICE_SUBSCRIBERS T EMP)
P a r t 2 P roject Questions P roblem A Revised English Description of the Database Scenario The English Prem ier League is a soccer league in England. It comprises of 20 club teams. All the teams play against each other twice, which includes one home game p layed in the clubs own stadium and one away game played in the away clubs stadium. So, i n each round there are 10 matches. Each round is called a matchday. The clubs have an office locat ion, a stadium, a t raining facility, an owner and board of directors. The club employs players and a team manager. The stadium has a name, address and seat ing capacit y. There is an announcer w ho announces important match informat ion during matches. They have a security staff for safety measures. They have a catering service which provides for food and drinks during m atches. They also have a ground maintenance staff. A club can also have one or many t raining facilit y. The t raining facilit ies have t raining grounds, gyms, a swimm ing pool, rehabilitat ion center for inju red players. The players have a name, a social insurance number, an address. The players have t raining stats. Depending on their t raining stats they get selected to play on actual m atchdays. The players have a jersey number and a specialized posit ion they play on the f ield. The manager has a name, a social insurance number, an address. He has m anager stats which include games won, lost or drawn by his team. Each player has matchday stats which include goals scored, assists, fouls comm it ted, yellow/red cards, running t ime on field. Each team has team stats which include goals (by which player), match result, fouls comm it ted, yellow/red cards, ball possession. The league has a results table which lists t he teams according to points earned. A w in is 3 points, draw is 1 point and loss is 0 points. It lists the games played by each
team, games won, games lost and games drawn, the goals for and goals against and goal difference and total points.
P roblem B Revised E-R Diagram
Schema CLUB NAM E St r ing OFFICE_LOCATION St r ing OWNER St r ing BOARD_OF_D IRECTORS St r ing STAD IUM NAM E
St r ing
C LUB String ADDRESS String SEATING_CAPACITY Integer ANNOUNCER String SECURITY_PERSONNEL Integer CATERING String GROUND_MAINTAINENCE TRAINING_FACILITY String FACIL ITY_NUMBER NAME CLUB TRAINING_GROUNDS GYM SWIMMING_POOL REHAB_CENTER MANAGER SIN MANAGES_CLUB NAME ADDRESS YEARS_EXPERIENCE MANAGER_STATS PL AYER SIN PL AYS_FOR NAME ADDRESS J E RSEY_NUMBER F IE LD_POSITION TRAINING_STATS F ITNESS_STATS
Integer String String String String String String
Integer String String String Integer Integer
Integer String String String Integer String Integer Integer
REFEREE SIN
Integer
N AME String ADDRESS String ROLE String YEARS_EXPERIENCE Integer REFEREE_DECISION_STATS Integer MATCH MATCH_NUMBER LOCATION DATE HOME _RESU LT AWAY _RESU LT HOME_CLUB AWAY_CLUB STADIUM MATCH_OFFICIALS CLUB_MATCH_STATS CLUB_STATS_ID CLUB_NAME POINTS_EARNED MATCH_RESU LT GOALS GOALS_AGAINST GOALS_DIFFERENCE FOULS YE L LOW CARDS RED CARDS BALL_POSSESSION
Integer String String String String String String String String
Integer String Integer String Integer Integer Integer Integer Integer Integer Integer
PL AYER_MATCH_STATS PL AYER_STATS_ID Integer PL AYER_SIN Integer PL AYER_NAME String GOALS Integer FOULS Integer ASSISTS Integer YE L LOW CARDS Integer RED CARDS Integer M ILES_RUN Integer
POINTS_TABLE POSITION_NUMBER CLUB_NAME GAMES_PL AYED GOALS_FOR GOALS_AGAINST GOAL_DIFFERENCE POINTS
Integer String Integer Integer Integer Integer Integer
P roblem C Queries in English C.1 C.2 List the names of the stadiums and the t raining facilit ies for all the clubs. List the club name with their current standing in the points table, with the games t hey played and the total points they earned. C.3 List all the match numbers, name and result for the home and away teams, along w ith the goals scored by each team and each teams ball possession for all the nd m atches that took place on matchdays Saturday, October 2 2010 and Sunday, rd October 3 2010. C.4 List the referee names, their role, their years of experience and their decision stats for the matches that took place on Saturday, October 2 2010 and Sunday, October rd 3 2010. Also list the match number and the clubs playing the matches. C.5 List the players, their jersey numbers, the position they play at, the goals they scored, yellow / red cards they get, and the club they play for all the matches. C.6 List the manager for every club, along with their current manager stats and the years of experience they have. C.7 For every club, get the names of the players who scored the maximum number of goals.
nd
C.8
Make a table having all the columns of the points table, for the away games played by all clubs. Fu r t her, make a sim ilar table to show the home game record of all the clubs.
C.9
To help the clubs security policy, list the seating capacity and the number of security personnel at every stadium.
C.10
For the club to analyse player performance, compare the t raining stats and fi tness s tats of all players playing for a club along with their match stats att r ibutes.
P roblem D Relational Algebra Expressions for the Queries D.1 STAD
C lub, Name (STADIUM) C lub, Name (TRAIN ING_FACIL I TY)
STAD * TRAIN_FAC
TRAIN_FAC RESULT D.2 R ESULT
Position Number, Club Name, Games Played, Points
(POINTS_TABLE) D.3 TEMP1
M atch_Number, Home_Club, Home_Result, Goals, Ball_Possession
( MATCH ( Match_Number = Club_Stats_ID) A ND ((Date = 2 October 2010) O R ( Date = 2 October 2010)) CLUB_MATCH_STATS)
T EM P2
M atch_Number, Away_Club, Away_Result, Goals, Ball_Possession
( MATCH ( Match_Number = Club_Stats_ID) A ND ((Date = 2 October 2010) O R ( Date = 2 October 2010)) CLUB_MATCH_STATS) RESULT (Match_Number, Home_Club, Away_Club, Home_Result, Away _Result, Home_Goals, Away_Goals, Home_Ball_Possession, Away_Ball_Possession)
M atch_Number, Home_Club, Away_Club, Home_Result, Away _Result,
T EMP1.Goals, TEMP2.Goals, TEMP1.Ball_Possession, TEMP2. Ball_Possession ( TEMP1 (TEMP1.Match_Number = TEMP2.Match_Number) TEMP2) D.4 RESULT
M atch_Number, Home_Club, Away_Club, Name, Role, Years_Experience, Referee_Decision_Stats (MATCH ( Match_Officials = Name) A ND ((Date = 2 October 2010) O R (Date = 2 October 2010)) Referee) P layer_Stats_ID , Plays_For, Name, Jersey_Number, Field_Position, Goals, Yellow Cards, Red Cards (PLAYER (SIN = PLAYER_SIN)
P LAYER_MATCH_STATS)
D.5
TEMP
RESULT
P lays_For, Name, Jersey_Number, Field_Position, Goals, Yellow Cards, Red Cards (MATCH ( Match_ID = Player_Stats_ID) TEMP) M anages_Club, Name, Manager_Stats, Years_Experience (Manager)
D.6 D.7
RESULT TEMP
P layer_SIN, Name, Plays_For, Goals (PLAYER (SIN = PLAYER_SIN) P lays_For, Name f MAX Goals (TEMP)
P LAYER_MATCH_STATS) RESULT D.8 TEMP
M atch_Number, Away_Club, Goals, Goals_Against, Goal_Difference,
Points_Earned (MATCH ((Match_Number = Club_Stats_ID) A ND (Away_Club = C lub_Name) CLUB_MATCH_STATS) AWAY_RECORD( Club_Name, Games_Played, Goals For, Goals Against, Goal D ifference, Points) ( TEMP)
Away_Club f C OUN T M atch_Number, C OUN T Goals,
C OUN T Goals_Against, C OUN T Goal Difference, C OUN T Points_Earned
T EM P2
M atch_Number, Home_Club, Goals, Goals_Against, Goal_Difference,
Points_Earned (MATCH ((Match_Number = Club_Stats_ID) A ND ( Home_Club = C lub_Name) CLUB_MATCH_STATS) HOME_RECORD( Club_Name, Games_Played, Goals For, Goals Against, Goal D ifference, Points) ( TEMP) D.9 D.10 SECURITY
H ome_Club f C OUN T M atch_Number, C OUN T Goals,
C OUN T Goals_Against, C OUN T Goal Difference, C OUN T Points_Earned
C lub, Name, Seating_Capacity, Security_Personnel (STADIUM)
PLAYER_STATS
N ame, Plays_For, Training_Stats, Fitness_Stats, Miles_Run, Goals, Assists, Fouls, Yellow Cards, Red Cards (PLAYER (PLAYER.SIN =
P LAYER_MATCH_STATS.SIN) PLAYER_MATCH_STATS)