Description

Consider the Turkish Super League database accompanied with this homework.

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))

PlayerTeam (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)

 

Notes:

• Table Match stores data only for season 2013-2014.
• Table Goals stores data only for season 2013-2014.

1) Table creation and data insertion.

1. a) [2 pts] Run the following queries to create the tables Standings and TransactionLog in your database. Create Table Standings (

Pos tinyint,

[Team Name] nvarchar(30),

GP tinyint,

W tinyint,

T tinyint,

L tinyint,

GF smallint,

GA smallint,

GD smallint,

Pts tinyint

)

 

Create Table TransactionLog ( LogID int identity(1,1) primary key,

LogTime datetime,

LogType char(1),

BeforeState nvarchar(500),

AfterState nvarchar(500),

)

 

1. b) [8 pts] In only one “insert into” statement; write a query to insert the output data of your stored procedure sp_GetStandingsUpToDate(‘20140715’) that you have in homework #6 into table Standings.

              

2)  Implement a trigger Trg_RearrangeStandings 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); then rearrange the table Standings, and insert a relevant record into the table TransactionLog.
• In all type of operations (insert, delete, update); PlayerID in table Goals must be a player of either the home team or the visiting team for that match in season 13-14. In any wrong match-team-player assignments, the transaction will be rolled back and any further executions will be stopped.
• A value less than 1 or greater than 90 cannot be entered in the field Minute.
• 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.