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.