[SOLVED] CSE3055-Homework 5- queries to create the table transactionLog in your database

Description

Homework #5

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]
  1. [5 pts] Create view playerTeam_V as followsà List player’s Name, surname and team name for all players.

1. [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.

4)

[30 pts] Consider the unnormalized relation R with six attributes ABCDEF and the following functional dependencies:

AB  à  CDE

• à F
• à D

1. [5 pts] What is the key(s) for the relation?
2. [5 pts] What is the normal form of this relation? Explain it.
3. [20 pts] Decompose R into 3NF relations step by step if it is not in 3NF.