CSE3055-Homework 5- queries to create the table transactionLog in your database Solved

35.00 $

Category:
Click Category Button to View Your Next Assignment | Homework

You'll get a download link with a: . zip solution files instantly, after Payment

Description

Rate this product

 

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.

 

  • HW5-bqapip.zip