Creating relations using SQL

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.

Start Microsoft Access 2000.
Create a new database (Blank Access Database).
Name it as Student and save it to drive U: to folder Tietokannat (databases).
A dialog box opens in Acces where you can choose from different tasks. For writing SQL select Queries|Create query in Design view.
Close the Show Table dialog.
Select View|SQL View.
Ready for writnig SQL! Start Excel and open the transformed relations you defined last time. in extreme emergencies you can use the model solution.
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.
Access will run the SQL-code when you clik the !-icon in the toolbar or select Query|Run. Try it. If Access doesn't return any messages, your code was correct. In case you got an error message, fix the code and try again.
Save the code you wrote as 01_Create_Postal_code.
You can see the created table on the sheet Tables in the window that opened when you started Access. Delete the postal_code table you just created by selecting it and pressing the DELETE key.
The first version of the SQL-code that created a table was not perfect. Add the primary key by writing
```
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 refer to it later on. Try running the code again and see if the table appears inTables.
Defining default values or validity checks by SQL is unfortunately not possible in Access. Those will be added later by the tools Acces has.

Create the Faculty relation in a similar way. Start a new query and write:

CREATE TABLE Faculty (
FacultyID         INTEGER     NOT NULL,
Name              VARCHAR(64) NOT NULL,
CONSTRAINT Faculty_PK
   PRIMARY KEY (FacultyID)
)

Run the SQL-code. Save it as 02_Create_Faculty.

Next you can create the relation Deparment:

CREATE TABLE Department (
DepartmentID          INTEGER     NOT NULL,
Name                  VARCHAR(64) NOT NULL,
Faculty               INTEGER     NOT NULL,
CONSTRAINT Department_PK
   PRIMARY KEY (DepartmentID)
)

Save the code as 03_Create_Department.

Check that you succeeded in creating the Department relation. Delete the Department relation for now so that you can make it again with referential integrity definitions included.

Add the referential integrity stuff to the Department SQL code:

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)

)

Make sure it works.

Unfortunately, again, Access does not understand the the functioning of the referential integrity (the cascades) if they are written in SQL. Using SQL-92 one could write

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  (FacultyID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
)

It doesn't, however, work in Access so you have to skip the cascades now and define them later by the other tools in Access.

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 Sturent_PK
   PRIMARY KEY (ID),
CONSTRAINT Student_FK_D
   FOREIGN KEY (Department)
   REFERENCES Department (DepartmentID),
CONSTRAINT Student_FK_P
   FOREIGN KEY (Postal_code)
   REFERENCES Postal_code (Postal_code)
)

Save the code as 04_Create_Student and check if it works.

Make the rest of the relations in the same way in the following order:
1. Phone
2. Course
3. Takes_exam
Save all the SQL statements like you did before.
When you have finished the SQL codes for every relation, delete once more all the relations you've created. Create the whole database as a whole by running all the SQL querys you wrote in the correct order. Double click to run the query.
Now it's time to add the referential integrity stuff. Select Tools|Relationship.
There you can see a diagram of all the relations and their relationships. Organize the diagram if it doesn't make sense.
Click every line between the relations and use the dialog box to define the function of the referential integrity. You can see the correct function as the restrictions you wrote in Excel.
Close the Relationships window and let Accessin save the settings.

Relations in the Studentdatabase and the referential integrities

The company database

Create a new database in Access and name it as Company. Save it to folder Databases (Tietokannat) on drive U:.
Write the SQL code needed to create the relations of the database. Use the definitions you made last time as a base.
Create the company database.
Remember to define our dear friends referential integrity functions when you're done with the code.

Soccer database

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

http://