Question

Case:

A national football association needs to design a database to store information about all the players (identified by pid, with DOB, position and salary as attributes) registered under it, all the football clubs (identified by name, with city and asset as attributes) in the nation, and the stakeholders of each club (with name, age and percentage of holdings). Each player plays for one club, and each club has a captain player. Each stakeholder can only hold the shares of one club, and can be identified by name, given that his club is known.

Tasks:

1. Draw an ER diagram that captures this information.

2. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible.

For any additional and reasonable assumptions made, please state clearly.

Answer 1

CREATE TABLE Player
(
Pid int NOT NULL ,
Date_of_Birth date NOT NULL ,
Position varchar(50) NOT NULL ,
Salary numeric(18,0) NOT NULL ,
Club varchar(50) NOT NULL ,
PRIMARY KEY(Pid),
FOREIGN KEY (Club) REFERENCES Football_Club(Name)
);

CREATE TABLE Football_Club
(
Name varchar(50) NOT NULL ,
City varchar(50) NOT NULL ,
Asset decimal(15,3) NOT NULL ,
Captain int Unique,
PRIMARY KEY(NAME),
FOREIGN KEY (Captain) REFERENCES Player(Pid)
);
CREATE TABLE Stakeholder
(
Name varchar(50) NOT NULL ,
Age numeric(5,0) NOT NULL ,
Percentage_Holding decimal(5,2) NOT NULL ,
Club_Owned varchar(50) NOT NULL ,
PRIMARY KEY(NAME,Club_Owned),
FOREIGN KEY (Club_Owned) REFERENCES Football_Club(Name),
CHECK (Percentage_Holding>0 AND Percentage_Holding<=100)
);