D1
Relational Schema
Team( Team_id(Pk), name, Club_id(Fk))
Managers_of_team( Manager_id(Pk), Team_id(Fk), manager_name)
D3
Normalization
Player_id → Player_id, firstname, surname, height, weight, DOB, Team_id
1NF: Each Customer has their own Player_id, there is on nested structure in this relation. Hence, Customer is in 1NF.
PK= { Player_id }
FK= { Player_id references Player(Player_id)}
one-attribute primary key, hence it’s automatically in 2NF.
3NF: There is no other possible functional dependency exists. The only FD is due to primary key, Experience is in 3NF.
Team_id → name, Club_id
1NF: Each Team has their own Team_id, there is on nested structure in this relation. Hence, Team is in 1NF.
PK= { Manager_id}
FK= {Team_id references Team(Team_id)}
one-attribute primary key, hence it’s automatically in 2NF.
3NF: There is no other possible functional dependency exists. The only FD is due to primary key, Managers_of_team is in 3NF.
Coach_id → Team_id, coach_name
1NF: Each Coach_of_team has their own Coach_id, there is on nested structure in this relation. Hence, Coach_of_team is in 1NF.
PK={Club_id}
Functional Dependency:
3NF: There is no other possible functional dependency exists. The only FD is due to primary key, Club is in 3NF.
7. Match( Match_id(Pk), date, place , winner(Fk), Team1(Fk), Team2(Fk))
Functional Dependency:
Match_id → date, place , winner(Fk), Team1(Fk), Team2(Fk)
8. Performance( Player_id(Fk)(Pk), Match_id(Pk)(Fk), 2 points attempts, 2 points made, 3 points attempts, 3 points made, free throws attempts, free throws made, minutes played, fouls made, rebounds)
PK={Player_id, Match_id}
1NF: Each Performance has their own Match_id and Player_id there is on nested structure in this relation. They are example trivial dependency. Hence, Performance is in 1NF.
2NF: Performance has Functional Dependency:
D4
------------------------------------------------
DROP TABLE Performance;
DROP TABLE Coaches;
DROP TABLE Manager;
--------------------------------------------------
----------------1. Clubs table--------------------
Address VARCHAR2(50) NOT NULL
);
Name VARCHAR2(40) NOT NULL,
Club_ID INTEGER NOT NULL,
(
Manager_ID INTEGER PRIMARY KEY,
--------------4. Coaches table--------------------
CREATE TABLE Coaches
FOREIGN KEY (Team_ID) REFERENCES Team(Team_ID)
);
Match_Date DATE NOT NULL,
Place VARCHAR2(30) NOT NULL,
Winner INTEGER,
FOREIGN KEY (First_Team) REFERENCES Team(Team_ID),
(
Player_ID INTEGER PRIMARY KEY,
Height NUMBER(6,2) NOT NULL,
Weight NUMBER(10,2) NOT NULL,
(
Player_ID INTEGER NOT NULL,
-------------8. Experience table--------------------
CREATE TABLE Experience
FOREIGN KEY (Player_ID) REFERENCES Player(Player_ID)
);
Player_ID INTEGER NOT NULL,
Attempts2points INTEGER NOT NULL,
Freethrowsmade INTEGER NOT NULL,
Minutesplayed NUMBER(10,2),
FOREIGN KEY (Player_ID) REFERENCES Player(Player_ID)
);
INSERT INTO CLUBS VALUES(1, 'Robin', 'US');
INSERT INTO CLUBS VALUES(2, 'Cann', 'US');
---------------2. Team table--------------------
INSERT INTO Team VALUES (1, 'abc',1);
INSERT INTO Manager VALUES (1,1,'Janny');
INSERT INTO Manager VALUES(2,2,'Kabin');
INSERT INTO Coaches VALUES(2,2,'Kabin');
INSERT INTO Coaches VALUES(3,3,'Cann');
INSERT INTO Matches VALUES(3, '05-JAN-2019','HJK',1,3,67.98,56.80,1);
INSERT INTO Matches VALUES(4, '05-JAN-2019','HJK',1,3,17.98,56.80,3);
INSERT INTO Player VALUES(2,1,'jiny','Raan','02-JAN-1994',5.9,56.90);
INSERT INTO Player VALUES(3,1,'vbin','Smith','03-JAN-1994',5.9,56.90);
INSERT INTO Player VALUES(8,2,'vinbu','Caan','08-JAN-1994',5.9,56.90);
INSERT INTO Player VALUES(9,3,'ady','Caan','01-JAN-1994',5.9,56.90);
INSERT INTO Player VALUES(14,4,'aio','Raan','06-JAN-1994',5.9,56.90);
INSERT INTO Player VALUES(15,4,'cinp','Caan','07-JAN-1994',5.9,56.90);
INSERT INTO Position VALUES(3,'1');
INSERT INTO Position VALUES(4,'1');
INSERT INTO Position VALUES(9,'1');
INSERT INTO Position VALUES(10,'1');
INSERT INTO Position VALUES(15,'1');
INSERT INTO Position VALUES(16,'1');
INSERT INTO Position VALUES(5,'2');
INSERT INTO Position VALUES(6,'2');
INSERT INTO Position VALUES(11,'6');
INSERT INTO Position VALUES(12,'5');
INSERT INTO Experience VALUES(4,03,10);
INSERT INTO Experience VALUES(5,06,20);
INSERT INTO Experience VALUES(10,04,20);
INSERT INTO Experience VALUES(11,03,10);
INSERT INTO Experience VALUES(16,04,20);
-------------9. Performance table--------------------
INSERT INTO Performance VALUES(2,5,3,3,6,4,3,2,6.5,15,7);
INSERT INTO Performance VALUES(2,6,2,5,4,3,2,5,6.6,4,7);
D5
Position.Position as "Position Name"
FROM Position , Player
SELECT Team_ID, NAME FROM TEAM WHERE TEAM_ID IN(SELECT WINNER FROM MATCHES
group by WINNER
WHERE ROWNUM<2));
group by Matches.Match_ID, Matches.Match_Date, Matches.Place
Having SUM(PERFORMANCE.FOULSMADE)>10 ;
concat(concat(p.FIRST_NAME,' '),p.SURENAME ) As Name,
e.INTERNATIONAL_MATCHES+e.National_matches As Total_Matches
--A5. For any specific match list the names of all players (i.e. from both teams).
select CONCAT(CONCAT(FIRST_NAME,' '),SURENAME) as Name from player where
-- This should include the main information about the player.
select * from
Player.DOB ,
Player.HEIGHT ,
on PLAYER.PLAYER_ID=performance.PLAYER_ID
order by Total_Points desc
D6
For the next step, I created the logical entity relationship diagram (ERD) by using the conceptual entity relationship diagram created in previous step. We are provided with the information related to each attribute of each entity. There is relationship between various tables and we have added the foreign key in our diagram for that.
Then we normalized the data up to third normal form. We have also added primary key and foreign key to the tables according to our understanding. After normalising the data there is no partial dependency and no transitive dependency left in our database.