Relaatioiden luominen SQL:llä

In case you haven't finished the previous excercises, do that before starting up with these.

The student database

Last time we transformed the ER-diagrams to relations and defined the fields. This time we'll create the actual relations using SQL.

1. Use ODBC Query Tool to take a connection to the course database server. If you haven't used ODBC Query Tool before then you should read the instructions
2. Ready for writnig SQL! Start Excel and open the transformed relations you defined last time. in extreme emergencies you can use the model solution.
3. Let's create the relations step by step. Always start with relations that have no references to other relations. In out student database these are:
   • Postal code
   • Faculty
   First, create the Postal code relation by writing:

   CREATE TABLE Postal_code (
   Postal_code      CHAR(5)     NOT NULL,
   Town             VARCHAR(64) NOT NULL
   
   )
   ;

   The names of the fields, the datatypes and wether they are required or not are already written down in Excel. If the field is not required just skip the definition NOT NULL.
4. ODBC Query Tool will run the SQL-code when you clik the -icon in the toolbar or press F5. Try it. In case you got an error message, fix the code and try again.
5. Save the code you wrote as student.sql.
6. You can verify the creation of the table by examining the Tables-tree in the left panel of the ODBC Query Tool. You can update the list by pressing F8.
7. No add before the CREATE TABLE -clause a new clause, which removes the table you just created:

   DROP TABLE Postal_code
   ;
   

   Make sure that you have ;-letter on its own line after each SQL-clause. Otherwise ODBC Query Tool can not tell apart each SQL-clause. Run the SQL. If everything goes right you will not get any complaints as the database management system first removes the postal_code-table and immediately after that recreates it. Save your SQL.
8. The first version of the SQL-code that created a table was not perfect. Add the primary key by writing:

   DROP TABLE Postal_code
   ;
   
   CREATE TABLE Postal_code (
   Postal_code      CHAR(5)     NOT NULL,
   Town             VARCHAR(64) NOT NULL,
   CONSTRAINT Postal_code_PK
      PRIMARY KEY (Postal_code)
   )
   ;

   CONSTRAINT gives the primary key an unique name which is used when if referred to it later on. After the PRIMARY KEY -words you must list inside parentheses the field or fields (separated with commas) which make the primary key. Run the SQL and make sure that the new table appears in the Tables-list.
9. Create the Faculty relation in a similar way.

   DROP TABLE Postal_code
   ;
   
   CREATE TABLE Postal_code (
   Postal_code      CHAR(5)     NOT NULL,
   Town             VARCHAR(64) NOT NULL,
      CONSTRAINT Postal_code_PK
      PRIMARY KEY (Postal_code)
   )
   ;
   
   CREATE TABLE Faculty (
   FacultyID         INTEGER     NOT NULL,
   Name              VARCHAR(64) NOT NULL,
   CONSTRAINT Faculty_PK
      PRIMARY KEY (FacultyID)
   )
   ;

   Run the SQL. Save. Write a new DROP TABLE -clause which removes the Faculty-table. Write it before all other SQL-clauses.
10. Next you can create the relation Department:

    DROP TABLE Postal_code
    ;
    
    CREATE TABLE Postal_code (
    Postal_code      CHAR(5)     NOT NULL,
    Town             VARCHAR(64) NOT NULL,
       CONSTRAINT Postal_code_PK
       PRIMARY KEY (Postal_code)
    )
    ;
    
    CREATE TABLE Faculty (
    FacultyID         INTEGER     NOT NULL,
    Name              VARCHAR(64) NOT NULL,
    CONSTRAINT Faculty_PK
       PRIMARY KEY (FacultyID)
    )
    ;
    
    CREATE TABLE Department (
    DepartmentID          INTEGER     NOT NULL,
    Name                  VARCHAR(64) NOT NULL,
    Faculty               INTEGER     NOT NULL,
    CONSTRAINT Department_PK
       PRIMARY KEY (DepartmentID)
    )
    ;

    Run the SQL. Save. Remember to write also a new DROP TABLE -clause for Department.
11. Next you add referential integrity constraints to the Department-table:

    
    CREATE TABLE Department (
    DepartmentID          INTEGER     NOT NULL,
    Name                  VARCHAR(64) NOT NULL,
    Faculty               INTEGER     NOT NULL,
    CONSTRAINT Department_PK
       PRIMARY KEY (DepartmentID)
    CONSTRAINT Department_FK_F
       FOREIGN KEY (Faculty)
    	 REFERENCES Faculty (FacultyID)
    )
    ;

    Viite-eheys määritellään FOREIGN KEY -sanoilla joiden jälkeen sulkeissa kerrotaan luotavan taulun kenttä tai kentät, jotka viittaavat toiseen tauluun. REFERENCES-sanan jälkeen kerrotaan viitattavan taulun nimi ja sulkeissa tässä taulussa oleva kenttä johon viitataan.

    Run the SQL. Save.

12. Lisää vielä tarkemmat toimintasäännöt muutosten ja poistojen vyörymiselle viite-eheysmääritykseen.

    CREATE TABLE Department (
    DepartmentID          INTEGER     NOT NULL,
    Name                  VARCHAR(64) NOT NULL,
    Faculty               INTEGER     NOT NULL,
    CONSTRAINT Department_PK
       PRIMARY KEY (DepartmentID)
    CONSTRAINT Department_FK_F
       FOREIGN KEY (Faculty)
    	 REFERENCES Faculty (FacultyID)
          ON UPDATE CASCADE
         ON DELETE RESTRICT
    )
    ;

    ON UPDATE-määritys kertoo sallitaanko päivityksiä viitatussa taulussa. Jos päivitykset sallitaan niin ne on vyörytettävä (CASCADE) myös tähän tauluun. Jos päivityksiä ei sallita niin määritys on oltava RESTRICT.

    ON DELETE-määritys kertoo sallitaanko poistot viitatussa taulussa. Jos poistot sallitaan niin ne on vyörytettävä (CASCADE) myös tähän tauluun eli kaikki poistettuun tietueeseen viitanneet tietueet poistetaan tästä taulusta. Poistojen kieltäminen tapahtuu sanalla RESTRICT.

13. Next thing is to create the Student relation:

    CREATE TABLE Student (
    ID              CHAR(11)    NOT NULL,
    Email           VARCHAR(64),
    First_name      VARCHAR(32) NOT NULL,
    Last_name       VARCHAR(64) NOT NULL,
    Street          VARCHAR(64) NOT NULL,
    Postal_code     CHAR(5)     NOT NULL,
    Starting_year   SMALLINT    NOT NULL,
    Department      INTEGER     NOT NULL,
    CONSTRAINT Student_PK
       PRIMARY KEY (ID),
    CONSTRAINT Student_FK_D
       FOREIGN KEY (Department)
       REFERENCES Department (DepartmentID)
         ON UPDATE CASCADE
         ON DELETE RESTRICT,
    CONSTRAINT Student_FK_P
       FOREIGN KEY (Postal_code)
       REFERENCES Postal_code (Postal_code)
         ON UPDATE CASCADE
         ON DELETE RESTRICT
    )
    ;

    Testaa ja lisää myös vastaava DROP TABLE -lause. Tallenna.
14. Lisää aloitusvuosi-kentälle tarkistus ja oletusarvo:

    CREATE TABLE Opiskelija (
    Sotu              CHAR(11)     NOT NULL,
    Sahkopostiosoite  VARCHAR(64),
    Etunimi           VARCHAR(32)  NOT NULL,
    Sukunimi          VARCHAR(64)  NOT NULL,
    Lahiosoite        VARCHAR(64)  NOT NULL,
    Postinumero       CHAR(5)      NOT NULL,
    Aloitusvuosi      SMALLINT     NOT NULL DEFAULT 2002,
    Laitos            INTEGER      NOT NULL,
    CONSTRAINT Opiskelija_PK
       PRIMARY KEY (Sotu),
    CONSTRAINT Opiskelija_FK_L
       FOREIGN KEY (Laitos)
       REFERENCES Laitos (LaitosID)
         ON UPDATE CASCADE
         ON DELETE RESTRICT,
    CONSTRAINT Opiskelija_FK_P
       FOREIGN KEY (Postinumero)
       REFERENCES Postinumero (Postinumero)
         ON UPDATE CASCADE
         ON DELETE RESTRICT,
    CONSTRAINT Opiskelija_ch
    	 CHECK ( aloitusvuosi >= 1990 )
    )
    ;

    Oletusarvot voi määritellä lisäämällä pakollisuusmääritteiden jälkeen sanan DEFAULT ja heti perään halutun oletusarvon.

    Tarkistuksia lisätään CHECK-määritteellä. Tarkistuksena voi käyttää melkein mitä tahansa SQL-kyselyä tai ehtoa. Kyselyiden tekeminen opitaan myöhemmin.

15. Make the rest of the relations in the same way in the following order:
    1. Phone
    2. Course
    3. Takes_exam
    Add only one table at the time. Test and then proceed to the next one.

The company database

1. Remove the previously created tables by running the DROP TABLE -clauses.
2. Write the SQL code needed to create the relations of the database. Use the definitions you made last time as a base.

Extra:Soccer database

If you have a plenty of time and enthusiasm left, go on and create the soccer database using SQL.