1) Consider the Turkish Super League database that has been e-mailed to you.
player (playerID: int, firstName: nvarchar(25), lastName: nvarchar(25), nationality: varchar(25), birthDate:
smalldatetime, age: smallint, position: varchar(25))
team (teamID: int, name: nvarchar(50), city: nvarchar(25)) player_team (playerID: int, teamID: int, season: varchar(5))
match (matchID: int, homeTeamID: int, visitingTeamID: int, dateOfMatch: smalldatetime, week: tinyint) goals (matchID: int, playerID: int, isOwnGoal: bit, minute: tinyint)
Note that tables match and goals store data only for season 2013-2014.
- [2 pts] Table creation and data insertion.
Run the following queries to create the table transactionLog in your database.
create table transactionLog ( logID int identity(1,1) primary key, logTime datetime, logType char(1), beforeState nvarchar(500), afterState nvarchar(500),
- [58 pts] Implement a trigger trg_rearrange with the followings:
- When a record is inserted into, deleted from or updated on the table goals (any change for matchID, playerID and/or isOwnGoal) and insert a relevant record into the table transactionLog.
- logTime is the time of operation.
- logType is “I” for insertion, “D” for deletion and “U” for update operation/transaction.
- beforeState is null for insertion and transactionLog.afterState is null for deletion. For update operation, beforeState is the one before the operation and afterState is the one after the operation.
- For the fields beforeState and afterState in table transactionLog, concatenate all the related fields (matchID, playerID, isOwnGoal, minute) in table goals and separate them by a semicolon (e.g. ’306;324;0;58’) and enter this data in the fields beforeState and afterState, accordingly.
- [10 pts]
- [5 pts] Create view playerTeam_V as followsà List player’s Name, surname and team name for all players.
- [5 pts] Write the following query by using playerTeam_V view à
List player’s Name, surname and the total number of distinct teams that they play. Results must be ordered asc according to Name and surname.
[30 pts] Consider the unnormalized relation R with six attributes ABCDEF and the following functional dependencies:
AB à CDE
- à F
- à D
- [5 pts] What is the key(s) for the relation?
- [5 pts] What is the normal form of this relation? Explain it.
- [20 pts] Decompose R into 3NF relations step by step if it is not in 3NF.