Results 1 to 2 of 2

Thread: Is this a decent database ? + Queries

  1. #1
    Join Date
    Apr 2014
    Posts
    1

    Question 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

    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
    );

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Just some observation.

    You could create one table for Employee instead of one for each type of employee and differentiate different roles by RoleType e.g. FinanceDirector, AssintantTrainer etc.

    Also You should change the data type from Text to VARCHAR for columns like name, nationality. Salary does not need Real data type.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •