Is this a decent database ? + Queries
I have been trying to make my first ever database in SQL. I have a couple of questions for the code I posted at the end of this post.
Are some of the foreign keys redundant?
What would be a decent join on 4 tables?
What would be a good outer-join?
Any help will do :cool:
Regards,
Rowan
Code:
CREATE TABLE CLUB(
cl_id INT PRIMARY KEY NOT NULL,
naam TEXT NOT NULL,
adres VARCHAR(200) NOT NULL,
dtm_opricht TEXT NOT NULL
);
CREATE TABLE STADION(
sta_id INT PRIMARY KEY NOT NULL,
cl_id INT REFERENCES CLUB(cl_id),
naam TEXT NOT NULL,
adres VARCHAR(200) NOT NULL,
capaciteit INT NOT NULL,
dtm_bouw TEXT NOT NULL
);
CREATE TABLE TECHNISCHDIRECTEUR(
td_id INT PRIMARY KEY NOT NULL,
cl_id INT REFERENCES CLUB(cl_id),
naam TEXT NOT NULL,
adres VARCHAR(200) NOT NULL,
salaris REAL NOT NULL,
nationaliteit TEXT NOT NULL,
geslacht CHAR NOT NULL,
dtm_geboorte TEXT NOT NULL
);
CREATE TABLE FINANCIEELDIRECTEUR(
fd_id INT PRIMARY KEY NOT NULL,
cl_id INT REFERENCES CLUB(cl_id),
naam TEXT NOT NULL,
adres VARCHAR(200) NOT NULL,
salaris REAL NOT NULL,
nationaliteit TEXT NOT NULL,
geslacht CHAR NOT NULL,
dtm_geboorte TEXT NOT NULL
);
CREATE TABLE HOOFDTRAINER(
ht_id INT PRIMARY KEY NOT NULL,
cl_id INT REFERENCES CLUB(cl_id),
td_id INT REFERENCES TECHNISCHDIRECTEUR(td_id),
naam TEXT NOT NULL,
adres VARCHAR(200) NOT NULL,
salaris REAL NOT NULL,
nationaliteit TEXT NOT NULL,
geslacht CHAR NOT NULL,
dtm_geboorte TEXT NOT NULL
);
CREATE TABLE ASSISTENTTRAINER(
at_id INT PRIMARY KEY NOT NULL,
cl_id INT REFERENCES CLUB(cl_id),
ht_id INT REFERENCES HOOFDTRAINER(ht_id),
naam TEXT NOT NULL,
adres VARCHAR(200) NOT NULL,
salaris REAL NOT NULL,
nationaliteit TEXT NOT NULL,
geslacht CHAR NOT NULL,
dtm_geboorte TEXT NOT NULL
);
CREATE TABLE SPELER(
sp_id INT PRIMARY KEY NOT NULL,
cl_id INT REFERENCES CLUB(cl_id),
ht_id INT REFERENCES HOOFDTRAINER(ht_id),
naam TEXT NOT NULL,
adres VARCHAR(200) NOT NULL,
salaris REAL NOT NULL,
nationaliteit TEXT NOT NULL,
geslacht CHAR NOT NULL,
dtm_geboorte TEXT NOT NULL,
positie TEXT NOT NULL,
rugnummer INT NOT NULL
);
