CSE3055- Homework 7 Solved

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) [10 pts] Table creation and data insertion.

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

) 

 

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.

 

1/2           

 

2) [90 pts] 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 Goals.Minute.

•       TransactionLog.LogTime is the time of operation.

•       TransactionLog.LogType is “I” for insertion, “D” for deletion and “U” for update operation/transaction.

•       TransactionLog.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.