改进我的SQL体系结构 [英] Making Improvements to my SQL Architecture
问题描述
我需要设计一个SQL架构来存储各种信息。这就是我想出的。我缺少任何可以大大增强表创建功能的SQL概念吗?也许有些表可以分成2个表,或者我的设计过于复杂,可以将两个表移动到一个表中。
I needed to design an SQL architecture to store the various information. This is what I have came up with. Is there any SQL concepts I am missing that could drastically enhance my table creation? Maybe some tables could be divided into 2 tables or I've over complicated my design and can move two tables into one.
drop table film_director;
drop table film_actor;
drop table film;
drop table studio;
drop table actor;
drop table director;
CREATE TABLE studio(
studio_ID NUMBER NOT NULL,
studio_Name VARCHAR2(30),
PRIMARY KEY(studio_ID));
CREATE TABLE film(
film_ID NUMBER NOT NULL,
studio_ID NUMBER NOT NULL,
genre VARCHAR2(30),
genre_ID NUMBER(1),
film_Len NUMBER(3),
film_Title VARCHAR2(30) NOT NULL,
year_Released NUMBER NOT NULL,
PRIMARY KEY(film_ID),
FOREIGN KEY (studio_ID) REFERENCES studio);
CREATE TABLE director(
director_ID NUMBER NOT NULL,
director_fname VARCHAR2(30),
director_lname VARCHAR2(30),
PRIMARY KEY(director_ID));
CREATE TABLE actor(
actor_ID NUMBER NOT NULL,
actor_fname VARCHAR2(15),
actor_lname VARCHAR2(15),
PRIMARY KEY(actor_ID));
CREATE TABLE film_actor(
film_ID NUMBER NOT NULL,
actor_ID NUMBER NOT NULL,
PRIMARY KEY(film_ID, actor_ID),
FOREIGN KEY(film_ID) REFERENCES film(film_ID),
FOREIGN KEY(actor_ID) REFERENCES actor(actor_ID));
CREATE TABLE film_director(
film_ID NUMBER NOT NULL,
director_ID NUMBER NOT NULL,
PRIMARY KEY(film_ID, director_ID),
FOREIGN KEY(film_ID) REFERENCES film(film_ID),
FOREIGN KEY(director_ID) REFERENCES director(director_ID));
INSERT INTO studio (studio_ID, studio_Name) VALUES (1, 'Paramount');
INSERT INTO studio (studio_ID, studio_Name) VALUES (2, 'Warner Bros');
INSERT INTO studio (studio_ID, studio_Name) VALUES (3, 'Film4');
INSERT INTO studio (studio_ID, studio_Name) VALUES (4, 'Working Title Films');
INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (1, 1, 'Comedy', 1, 180, 'The Wolf Of Wall Street', 2013);
INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (2, 2, 'Romance', 2, 143, 'The Great Gatsby', 2013);
INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (3, 3, 'Science Fiction', 3, 103, 'Never Let Me Go', 2008);
INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (4, 4, 'Romance', 4, 127, 'Pride and Prejudice', 2005);
INSERT INTO director (director_ID, director_fname, director_lname) VALUES (1, 'Martin', 'Scorcese');
INSERT INTO director (director_ID, director_fname, director_lname) VALUES (2, 'Baz', 'Luhrmann');
INSERT INTO director (director_ID, director_fname, director_lname) VALUES (3, 'Mark', 'Romanek');
INSERT INTO director (director_ID, director_fname, director_lname) VALUES (4, 'Joe', 'Wright');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (1, 'Matthew', 'McConnaughy');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (2, 'Leonardo', 'DiCaprio');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (3, 'Margot', 'Robbie');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (4, 'Joanna', 'Lumley');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (5, 'Carey', 'Mulligan');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (6, 'Tobey', 'Maguire');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (7, 'Joel', 'Edgerton');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (8, 'Keira', 'Knightly');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (9, 'Andrew', 'Garfield');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (10, 'Sally', 'Hawkins');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (11, 'Judi', 'Dench');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (12, 'Matthew', 'Macfadyen');
INSERT INTO film_actor (film_ID, actor_ID) VALUES (1, 1);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (1, 2);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (1, 3);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (1, 4);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (2, 2);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (2, 5);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (2, 6);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (2, 7);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (3, 5);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (3, 8);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (3, 9);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (3, 10);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (4, 5);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (4, 8);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (4, 11);
INSERT INTO film_actor (film_ID, actor_ID) VALUES (4, 12);
INSERT INTO film_director (film_ID, director_ID) VALUES (1,1);
INSERT INTO film_director (film_ID, director_ID) VALUES (2,2);
INSERT INTO film_director (film_ID, director_ID) VALUES (3,3);
INSERT INTO film_director (film_ID, director_ID) VALUES (4,4);
推荐答案
我建议替换广播
表,其中有两个表-电影演员
(每个演员每部电影有一行)和 filmdirector
(每位导演每部电影排成一行)。您的 casting
表中的 director_id
对于不需要的给定电影的每一行重复(在3NF中也不是) )。此外,如果电影中有多个导演(不可能但并非不可能),它将无法正确处理场景。
除此之外,该设计对我来说还不错。
I would suggest replacing casting
table with two tables - filmactor
(having one row per film per actor) and filmdirector
(having one row per film per director). Your casting
table has director_id
repeating for each row for a given film which is not needed (it is also not in 3NF). Also, it will not handle the scenario properly where there are more than one director on a film (improbable but not impossible).
Other than that the design looks fine to me.
这篇关于改进我的SQL体系结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!