Poena Poena - 1 month ago 6
SQL Question

Correct Table Design?

I need to design a table that has no repeating groups and I think I have done that but does the table look correct with how they are linked etc. or is there anything I can improve on? I ran all the queries and I had a few repeating groups for player & manager as I had them both in a squad table so I split them into team_player & team_manager and that way one or more managers can also manage a team, vice versa with stadium etc.

drop table football_manager;
drop table football_player;
drop table team;
drop table stadium;
drop table player;
drop table manager;

CREATE TABLE stadium(
stadium_ID NUMBER NOT NULL,
stadium_Name VARCHAR2(30),
PRIMARY KEY(stadium_ID));

CREATE TABLE team(
team_ID NUMBER NOT NULL,
stadium_ID NUMBER NOT NULL,
league VARCHAR2(30),
league_ID NUMBER(1),
match_Len NUMBER(3),
match_Title VARCHAR2(30) NOT NULL,
year_Released NUMBER NOT NULL,
PRIMARY KEY(team_ID),
FOREIGN KEY (stadium_ID) REFERENCES stadium);

CREATE TABLE manager(
manager_ID NUMBER NOT NULL,
manager_fname VARCHAR2(30),
manager_lname VARCHAR2(30),
PRIMARY KEY(manager_ID));

CREATE TABLE player(
player_ID NUMBER NOT NULL,
player_fname VARCHAR2(15),
player_lname VARCHAR2(15),
PRIMARY KEY(player_ID));

CREATE TABLE football_player(
team_ID NUMBER NOT NULL,
player_ID NUMBER NOT NULL,
PRIMARY KEY(team_ID, player_ID),
FOREIGN KEY(team_ID) REFERENCES team(team_ID),
FOREIGN KEY(player_ID) REFERENCES player(player_ID));

CREATE TABLE football_manager(
team_ID NUMBER NOT NULL,
manager_ID NUMBER NOT NULL,
PRIMARY KEY(team_ID, manager_ID),
FOREIGN KEY(team_ID) REFERENCES team(team_ID),
FOREIGN KEY(manager_ID) REFERENCES manager(manager_ID));

INSERT INTO stadium (stadium_ID, stadium_Name) VALUES (1, 'Bernabeu');
INSERT INTO stadium (stadium_ID, stadium_Name) VALUES (2, 'Old Trafford');
INSERT INTO stadium (stadium_ID, stadium_Name) VALUES (3, 'Celtic Park');
INSERT INTO stadium (stadium_ID, stadium_Name) VALUES (4, 'Wembley');

INSERT INTO team (team_ID, stadium_ID, league, league_ID, match_Len, team_Title, year_Released) VALUES (1, 1, 'BPL', 1, 180, 'Manchester', 2013);
INSERT INTO team (team_ID, studio_ID, league, league_ID, match_Len, team_Title, year_Released) VALUES (2, 2, 'Eire', 2, 143, 'Bray', 2013);
INSERT INTO team (team_ID, studio_ID, league, league_ID, match_Len, team_Title, year_Released) VALUES (3, 3, 'Conference', 3, 103, 'Leeds', 2003);
INSERT INTO team (team_ID, studio_ID, league, league_ID, match_Len, team_Title, year_Released) VALUES (4, 4, 'League 2', 4, 127, 'Swansea', 2009);

INSERT INTO director (manager_ID, manager_fname, manager_lname) VALUES (1, 'Martin', 'Miles');
INSERT INTO director (manager_ID, manager_fname, manager_lname) VALUES (2, 'Gaz', 'Lineker');
INSERT INTO director (manager_ID, manager_fname, manager_lname) VALUES (3, 'Miles', 'Teller');
INSERT INTO director (manager_ID, manager_fname, manager_lname) VALUES (4, 'Ian', 'Wright');

INSERT INTO actor (player_ID, player_fname, player_lname) VALUES (1, 'Mike', 'McDonnagh');
INSERT INTO actor (player_ID, player_fname, player_lname) VALUES (2, 'John', 'Ryan');
INSERT INTO actor (player_ID, player_fname, player_lname) VALUES (3, 'Robbie', 'Keane');
INSERT INTO actor (player_ID, player_fname, player_lname) VALUES (4, 'Ronnie', 'Tunner');
INSERT INTO actor (player_ID, player_fname, player_lname) VALUES (5, 'Larry', 'Dunne');
INSERT INTO actor (player_ID, player_fname, player_lname) VALUES (6, 'Tom', 'Maguire');
INSERT INTO actor (player_ID, player_fname, player_lname) VALUES (7, 'Joel', 'Ryan');
INSERT INTO actor (player_ID, player_lname, player_lname) VALUES (8, 'Kevin', 'Smith');
INSERT INTO actor (player_ID, player_lname, player_lname) VALUES (9, 'Andrew', 'Ward');
INSERT INTO actor (player_ID, player_lname, player_lname) VALUES (10, 'Gary', 'Hawkins');
INSERT INTO actor (player_ID, player_lname, player_lname) VALUES (11, 'Joey', 'Ward');
INSERT INTO actor (player_ID, player_lname, player_lname) VALUES (12, 'Matthew', 'Walsh');

INSERT INTO team_player (team_ID, player_ID) VALUES (1, 1);
INSERT INTO team_player (team_ID, player_ID) VALUES (1, 2);
INSERT INTO team_player (team_ID, player_ID) VALUES (1, 3);
INSERT INTO team_player (team_ID, player_ID) VALUES (1, 4);
INSERT INTO team_player (team_ID, player_ID) VALUES (2, 2);
INSERT INTO team_player (team_ID, player_ID) VALUES (2, 5);
INSERT INTO team_player (team_ID, player_ID) VALUES (2, 6);
INSERT INTO team_player (team_ID, player_ID) VALUES (2, 7);
INSERT INTO team_player (team_ID, player_ID) VALUES (3, 5);
INSERT INTO team_player (team_ID, player_ID) VALUES (3, 8);
INSERT INTO team_player (team_ID, player_ID) VALUES (3, 9);
INSERT INTO team_player (team_ID, player_ID) VALUES (3, 10);
INSERT INTO team_player (team_ID, player_ID) VALUES (4, 5);
INSERT INTO team_player (team_ID, player_ID) VALUES (4, 8);
INSERT INTO team_player (team_ID, player_ID) VALUES (4, 11);
INSERT INTO team_player (team_ID, player_ID) VALUES (4, 12);

INSERT INTO team_manager(team_ID, director_ID) VALUES (1,1);
INSERT INTO team_manager(team_ID, director_ID) VALUES (2,2);
INSERT INTO team_manager(team_ID, director_ID) VALUES (3,3);
INSERT INTO team_manager(team_ID, director_ID) VALUES (4,4);

Answer

I agree with sdmon - since director and actor have the same attributes, it makes sense to keep them all in a single table, and assign them roles in the "facts" tables.

Can a film have more than one director? If not, then you don't need the film_director table, you just need a column for "director" in the film table. Otherwise you do need the film_director table.

Can a film be tied to more than one studio? In your model, you have a column for studio_id in the film table, but also a film_studio table. You don't need both. You need the column (but not the table) if a film is always made by a single studio. If you need to show more than one studio for a film, then you need the extra table, but not the column.

Everything else looks fine. Good luck!

Comments