Demo 2

Transforming an ER-diagram to a relation

If you haven't finished demo 1 yet then please continue with it before proceeding with this exercise.

Turning an ER-diagram into a relation has the following rules of thumb:

1. Every ordinary entity is turned into a relation (table)
2. Every attribute in the diagram will be added to the associated relation
3. Every Many-to-Many -relationship is turned into an ordinary relation.
• Define as many foreign keys to the relation as there are entities associatd with the relationship in question
• A composite key consisting of the foreign keys is defined as the primary key.
• Add the other fields belonging to the relation into it.
4. M-to-1 -relationships
   • No new relations are needed but a new foreign key should be added to the relation that already exists. The key should be added to the entity that has the mark "many" on its size.
   • The attributes connected to the relationship are added to the relation that already exists.
5. Multi-valued attributes are turned into new entities and references from them to the entities, that had the multi-valued attributes in the first place, are made.

Let's turn the ER-diagram we made last time to relations

1. Open the Excel document you made on the first round of excercises.
2. Last time you made tables where you listed every entity and the attributes connected to them. All these definitions function as a base for creating relations. Update those definitions to correspond to the tables in the database.

3. M-to-1 -relationships

   • Check every entity that has M-to-1 -relationships. All the entities that have M on their size in these relationships need a new attribute that refers to the other participant on the relationship. Such entities are:
     • Student (opiskelija) that has an M-to-1 -relationship with the department (laitos).
     • Department (laitos)that has an M-to-1 -relationship with the faculty (tiedekunta).
     • Course (kurssi) that has an M-to-1 -relationship with the department(laitos).
   • So, add a new attribute to the table that lists the attributes of the student:
     

     Entity	Datatype	Required	Default value	Restrictions
     Department	Integer	Yes		Only the values in the departmentID (laitosID) field are accepted. Enable update cascade for the departmentID (UPDATE CASCADE). Disable delete cascade for the departmentID (DELETE RESTRICT).

   • The datatype of the new attribute (department) must be the same as that of the attribute it refers to (departmentID).
   • To clarify the idea, you can write the meaning of referential integrity in this particular case to the restrictions -column. You can also mark if the update cascades and deletie cascades are allowed via the referential integrity.
   • Add the new attributes to the entities "department" and "course" in a similar way.

4. M-to-M -relationships

   • Find all the M-to-M -relationships in the diagram. Those are:
     • Takes an exam (Tenttii)
   • The M-to-M -relationship in question already has attributes connected to it and you listed them last time in the same way that the attributes connected to entities. This alreary indicates that all the M-to-M -relationships are turned into relations.
   • In addition to the attributes already connected to the relationship you need to define new ones that refer to the entities that have to do with the relationship (are connected to it). In this case, the following is added to the attribute list of "takes an exam":

     Attribute	Datatype	Required	Default value	Restrictions
     Student	CHAR(11)	Yes		Only the values in the ID -field of the Student -table are accepted. Enable update cascades for the ID (UPDATE CASCADE). Disable delete cascades for the ID (DELETE RESTRICT).
     Course	CHAR(6)	Yes		Only the values in the couseID -field of the Course -table are accepted. Enable update cascades fir the couseID (UPDATE CASCADE). Disable delete cascades for the courseID (DELETE RESTRICT).

   • And again, the datatypes of the new fields must be similar with the primary keys of the Course and Student -entities that they refer to.
   • When determinig the referential integrities for M-to-M -relationships, the cascades are set as a default to enabling update cascades and disabling delete cascades. Other possibilities are worth considering for every case, though.

5. 1-to-1 -relationships

   • 1-to-1 -relationships are rare. In case they are found in an ER-diagram, they are treated as M-to-1 -relationships. Then it's your own choice which one of the entities gets the new attribute.

6. Multi-valued attributes

   • A new relation has to be created for a multi-valued attribute. We have one example here:
     • The student has a multi-valued attribute phone number
   • Create a new table that has the attributes:
     • ID
     • Phone number
   • Create referential integrity between the ID and the Student
   • Both attributes together form a composite key

   • Phone number

     Attribute	Datatype	Required	Default value	Restrictions
     ID	CHAR(11)	Yes		Only the values in the ID -field of the Student -table are accepted. Enable update cascades (UPDATE CASCADE). Enable delete cascades (DELETE CASCADE).
     Phone number	CHAR(32)	Yes

   • Remember: The datatype of the ID -attribute must be similar with that of the ID -field in the Student -relation.
   • When determinig the referential integrities for multi-valued attributes, both of the cascades are enabled as a default.

Normalization

Let's make sure there is no unnecessary repetition or excess of information in the relations. By normalizating the database one can avoid problems in updating, adding or deleting data.

Check the following:

• Only the information closely linked to an entity is in the same relation with it.
• Certain data is updated in one place only.

In our student database there is one problematic thing:

• The "town" (postitoimipaikka) in the student table is not closely linked to the student but to the postal code
• Updatind the information about the town cannot be done in one location only. For example:

  Id	First name	Last name	Street address	Postal code	Town	e-mail	The year of starting
  111178-111K	Matti	Meikäläinen	Mutkatie 1	40700	Jyväskylä	foobar@hotmail.com	1999
  111277-222K	Ville	Meikäläinen	Mutkatie 2	40700	Jyväskylä	foobar2@hotmail.com	1999
  111176-333K	Kalle	Meikäläinen	Suorakuja 1	31600	Jokioinen	foobar3@hotmail.com	1999
  111175-444K	Jussi	Meikäläinen	Suorakuja 2	31600	Jokioinen	foobar4@hotmail.com	1999
  111172-999K	Maija	Meikäläinen	Mutkatie 3	40700	Jyväskylä	foobar5@hotmail.com	1999

• Create a new table for the postal codes and towns and give it the name "town", for instance. The postal code will become the primary key for the new relation. Remove the town -field from the student -relation and leave only the postal code -field. It turns into a foreign key that refers to the town -relation.

Company database

Next job: We'll transform the ER-diagram of the company database we made last time to relations. If you have no company database of you own, use the model solution. Fear not, if your own version is not similar to the model solution.

1. Treat every entity as a new relation. Every table of attributes connected to an entity means fields to be created to the relation.
2. First, find all the M-to-1 -relationships in the ER-diagram. Add the new fields (foreign keys) to the relation that is on the M-side of the relationship. Make sure you have the correct datatypes, that is the datatypes in the new fields are the same as in the field they refer to.
3. Find all the M-to-M -relationships in your diagram. Create a new relation for every M-to-M -relationship. As the name of the relation you can use the name of the relationship or derive a new name from it. If you have relationships that have attributes connected to them you already have a base for the relation (see entities).
4. Add new fields for foreign keys to every relation made of M-to-M -relationships (one field per one entity in the relationship). Remember the correct datatypes! Define the composite keys that consists of the foreign keys as the primary keys for the new relations.
5. If there are multi-valued attributes in your diagram, create new relations for them. Remove the multi-valued attribute from the entity it was connected to and name the new relation accordingly.
6. Define the rules for cascades of referential integrity for every foreign key you have.

Normalization

Check every relation in you company database and see if they meet the following requirements:

• Only the information that is closely linked to an entity is in the same relation with it.
• Updating cetrain data is made in one location only.

In case you find problematic relations, divide them as in the student database -example.

Soccer database

Transform the soccer tournament diagram you made last time to relations the same way as in previons examples. The model solution may be very different to your version but use your own if you have it.

After that, check the construction of your database by normalizing it. Fix what there is to fix.