Objectives
- Transformation of the entity-relational model into a relational data model.
- Transforming a relational model into a SQL script that creates a database.
Motivation
The aim of this lab class is to practice the transformation of the entity-relational model into the relational model used in relational database systems.
Instructions
Step 1
In the following figure you can see an ER model describing students and their enrollment on subjects.
Task 1.1
Use the relational modeling tool to create a logical relational model for the ER model of students in classes studying subjects.
During transformation, you need to specify the primary keys for the created tables. How can you uniquely identify for example student? Will the combination (name, surname) be enough? Or is it better to create artifical primary key?
Within the relational model, you need to specify data types of table columns and other domain specific constraints. Can you identify which attributes will be required?
Note that the relationship between the student and the subject has cardinality M:N. What it means for relational model? How do you express such a relationship? What will you need in relational model?
You should also notice the relationship between student entity and subject entity, such relationship is actually associative entity - this relationship also has attributes. It stores not just information about student James studying Physics, but also stores information about calendar year of study and grade which he received. When transferring such a relationship you need to remember to allow same student to repeat same subject in a different calendar year.
Step 2
In this step, you will be using the DDL part of the SQL language to create database.
Task 2.1
Use the DDL section of SQL language to create a database for storing information about students studying subjects. Make your script (sequence of SQL statements) work on Oracle database system.
Comment
In contrast to a logical relational model you need to take into account particular database system, as there can be different construction of SQL statements and other restrictions or features. You need to define specific data types for columns that are supported on Oracle. Also you need to apply reference integrity rules in ways it is possible on Oracle.
You can work on this task in an iterative way. Start with a single table and try to create it correctly. If you make a mistake (e.g. you enter the wrong data type), you can use the command ALTER TABLE in order to modify the structure of the database table (an alternative is to delete the table and creating a new one - keep in mind that this will delete all data stored in table you delete).
Your created database you can view in SQL Developer in your connection in the Tables folder. Alternatively, you can use the SQL statement DESC to describe database table, for example:
DESC studies;
Step 3
If you have worked on school database server, you have probably noticed in previous step that you already have some tables in the database. Each of us has in their own school database already created some database. We used Chinook database which serves as sample database for a song shop.
In the following figure, you can see the Chinook logical relational model.
Task 3.1
Study the Chinook relational model.
Comment
If you accidentally delete the database or somehow damage it, you can use following Chinook SQL script in order to reinstall it: SQL script for creating and inserting data to Chinook database
Comment
In case you want to work on your local Oracle DB installation, I recommend using the original script from Chinook webpage, as it will not only create tables and insert all the data, but also will create 'chinook' user with password 'p4ssw0rd'. Then you will be able to work on your database even without your previous administrator 'system' account. You can also find scripts for Chinook for other SQL implementations, such as MySQL, PostgreSQL, or SQL Server.
Resources
Additional Tasks
Task A.1
Company
Create table using SQL with the name Company with following columns:
Ev_number NUMBER (5)
Surname CHAR (15)
Name CHAR (10)
Birthdate DATE
Street CHAR (20)
City CHAR (15)
Num_children NUMBER (2)
Status CHAR (8)
Salary NUMBER (8, 2)
Comment
Some of the next additional tasks will be using this database even in next weeks, therefore I recommend not deleting it. All of such additional tasks will begin with name Company.
Task A.2
Company
Create table Vacation, which will have following columns (ev_number is foreign key to table Company):
Ev_number NUMBER (5)
Year NUMBER (4)
Vac_total NUMBER (2)
Vac_spent NUMBER (2)
Task A.3
Company
Create table 'account', which will contain information for accounting department about personal bank accounts of employees. Table 'account' will have following columns:
transaction_number NUMBER(5)
ev_number NUMBER(5)
account_number VARCHAR2(20)
bank_number VARCHAR2(4)
amount NUMBER(10,2)
Comment
Resulting database schema for company should look like this:
