Transforming Entity-Relationship Models into Relational Database Schemes, Summaries of Information Technology

Download Transforming Entity-Relationship Models into Relational Database Schemes and more Summaries Information Technology in PDF only on Docsity!

Geographic Information Technology Training Alliance (GITTA) presents:

The relational database model

Responsible persons: Anca Dobre, Dominique Schneuwly, Samuel

Wiesmann, Susanne Bleisch

1. The relational database model

The relational database model is used in most of today's commercial databases. It is used since the early 80ies and was developed 1970 by E. F. Codd. The relational database model is based on a mathematical concept where relations are interpreted as tables. The focus of this lesson lies in the conversion of a conceptual into a logical data scheme (the relational database model) using an entity-relationship-schema. You will find more information about schemas in the lesson about Database models, schemas and instances.

1.1. Concept of the relational model

In contrast to the entity-relationship-model (ERM) which is a conceptual model, the relational model is a logical data model. It can be seen as lying one step or layer below the ERM. The relational model is not about abstract objects but defines how data should be represented in a specific database management system. The goal of a logical data model is to arrange the data in such a form that it is consistent, non-redundant and supports operations for data manipulation.

1.1.1. Data organization in a relational data model

A logical data schema (model) is in most cases based on a conceptual data scheme which, with the use of certain guidelines and rules, is transformed into a relational scheme (model). The main organization unit in a relational data model is the relation. A relation can be represented as a table but the definition of the relation is not necessarily equal to the definition of the table and vica versa. Why use the relational model?

• Simplicity.^ Data in a relational model is represented through values that are structured with only one construct: the "relation".
• Classification.^ The relational model is based on mathematical fundamentals: the set theory.

1.1.2. Definitions

domain: A domain D is a set of atomic values that defines the value range of attributes.

Example domain

tupel: A tuple t is a list with n values t = <d1, d2, ..., dn> where each value di is either an element of the domain Di or NULL. A tuple is a record in a relation (row in a table).

Example tupel

Attribute:

Example relation

Relational database scheme: A relational database scheme is a set of relation schemes S = {R1, ..., Rn} together with a set of integrity conditions. A relational database is a relational database scheme together with a database instance.

Relational scheme terms

The relational scheme of an object (entity) can be represented as a table (relation). In this example the entity are grades. This entity is described with the attributes name, subject and the grade. The domain (or value range) for the attributes name and subjects are all lower- and upper-case characters of the alphabet, the domain for the grades are real numbers from 1 to 6. The structure of this entity without any content is called a relational scheme. Any value that is entered has to be within the defined value range or domain. A row in the table is also called a tuple. There is a small mistake in the above table. Do you find it?

The primary key (or identifier) is defined. Here it is the attribute CustomerNo: Customer (CustomerNo, FirstName, LastName, Street, StreetNo)

1.2.3. Rule 2

In this step the weak entity sets are transformed into the relational database scheme.

Definition rule 2 For each weak entity set S with owner G create a relational scheme R with the entity properties as attributes A. For multivalued attributes use rule 7. Use the primary key of G as foreign key in R. Choose a discriminator (combination of attributes) that, together with the foreign key, will act as primary key for R. Note that only the combination of the foreign key together with the discriminator can act as primary key for R. IIn this example you can see the application of rule 2:

Only pictures can be viewed in this version! For Flash, animations, movies etc. see online version. Only screenshots of animations will be displayed. [link]

A weak entity set is taken for the creation of the relation: Part All entity properties are used as attributes for the relation: Part(Name, Editor) The primary key of the owner's relational scheme is taken and added as a foreign key to the relation. In this example the owner is Newspaper(Name, Circulation, Price) and the primary key is Name which we use in the relation as NewspaperName (since Part already has a Name): Part(NewspaperName, Name, Editor) The discriminator attribute Name (of Part) together with the foreign key NewspaperName form the primary key of the relation Part: Part(NewspaperName, Name, Editor)

1.2.4. Rule 3

In this step the binary relationships of the following type (1,1)(1,1), (0,1)(1,1) or (0,1)(0,1) are transformed into the relational database scheme.

Definition rule 3 Search for all binary relationships B (1,1)(1,1), (0,1)(1,1) or (0,1)(0,1). Find the relational scheme S and T that are connected through relationship B. Choose one of them (eg. S) and insert the primary of the other relational scheme (eg. T) as a foreign key. Also add the properties of B as attributes into this relational scheme. In this example you can see the application of rule 3:

Only pictures can be viewed in this version! For Flash, animations, movies etc. see online version. Only screenshots of animations will be displayed. [link]

A binary relationship (type (1,1)(1,1)-, (0,1)(1,1)- and (0,1)(0,1)) is chosen: leads. Then we choose an entity set that is related to this relationship: Newspaper(Name, Circulation, Price) The primary key of the second entity set (eg. ChiefEditor) is inserted as foreign key in the first relational scheme: Newspaper(Name, Circulation, Price, ChiefEditor_PersNo.) Now the properties of the relationship are used as attributes in the relational scheme: Newspaper(Name, Circulation, Price, ChiefEditor_PersNo. , SinceDate)

1.2.5. Rule 4

In this step all binary relationships one to many/many to one (1,n)(1,1), (0,n) (1,1), (1,n)(0,1) or (0,n)(0,1) are transformed into the relational database scheme.

Definition rule 4 Search for all regular binary relationships B of type (1,n)(1,1), (0,n)(1,1), (1,n)(0,1) and (0,n)(0,1) and for their relational schemes S and T of the corresponding entity sets. Choose the relational scheme on the "(1,1)"/"(0,1)"- side (here S) and insert there the primary key of T as a foreign key. Also add the properties (if there are any) of B as attributes into this relational scheme. In this example you can see the application of rule 4:

Only pictures can be viewed in this version! For Flash, animations, movies etc. see online version. Only screenshots of animations will be displayed. [link]

A binary relationship of type one to many/many to one ((1,n)(1,1)-, (0,n)(1,1)-, (1,n)(0,1)- und (0,n)(0,1)) is chosen: "commissions" Then we choose the entity set that is on the "(1,1) or (0,1)"-side: Advertisment(AdNo, Size, Price, PublDate) The primary key of the second entity set (eg. Customer) is inserted as foreign key in the first relational scheme: Advertisment(AdNo, Size, Price, PublDate, ClientCustomerNo.)

1.2.6. Rule 5

In this step all binary relationships many to many (0,n)(0,n), (1,n) (0,n) or (1,n)(1,n) are transformed into the relational database scheme.

Definition rule 5 Search for all regular binary relationships B of type many to many and the according relational schemes S and T. For each B create a new relational scheme R. The primary keys of S and T are used as foreign keys in R. Together they form the primary key of this new relational scheme R. Also add the properties (if there are any) of B as attributes into this relational scheme R. In this example you can see the application

ChiefTelNo( ChiefEditor_PersNo, TelNo)

1.2.9. Rule 8

If in rule 1 you find subclasses then transform them according to this rule.

Definition rule 8 Define a relational scheme R for the superclass C with the attributes A(R)=(K, A1, A2, .., An), where K is the primary key. For each subclass create a new relational scheme Ri with their attributes and the primary key K of superclass C as an additional attribute. The primary key of Si is also K. In this example you can see the application of rule 8:

Only pictures can be viewed in this version! For Flash, animations, movies etc. see online version. Only screenshots of animations will be displayed. [link]

Using the superclass define a relational scheme R with a primary key K: Employee(ENumber) For each subclass create a relational scheme: Technician Engineer(Training) Add the primary key K of the superclass as attribute of the subclasses and use it also as primary key: Employee(ENumber), Technician(ENumber), Engineer(ENumber, Training)

1.2.10. Using the 8 rules

The following flash animation allows you to practice the 8 rules that you have now learned. Please click on the linked flash-graphic below and follow the instructions.

Flash exercise

1.2.11. Reducing an ERM to a relational scheme

Create a relational database scheme using the following conceptional data scheme (ERM - Entity Relationship Model) using the rules you here learned in this unit. Primary keys should be marked as underlined and foreign keys italic. Publish your solution as a Word- or PDF-document on the discussion board. Check out the solutions of the other students and comment them. Any problems with this exercise can also be published on the discussion board. The solutions and questions will be checked by a tutor and general feedback published.

A conceptional model (ERM)

A relationship between two schemes is established by using the domain of the primary key in one scheme as the domain of a foreign key in a second scheme (with the according attributes). Referential integrity constraints ensure that any foreign key value is always pointing on an primary key of an existing tuple. References to non-existing primary keys are not allowed. In such a case the foreign key value must be set to NULL. The relational scheme containing the foreign key is called the referencing scheme, the scheme with the primary key is called the referenced scheme.A foreign key can also point to its own scheme. In the early days of computer databases, only workstations were able to check for referential integrity. Today most PC-based database systems are able to check for referential integrity.

Example Referntial integrity

In the above table Division we have Div_No as a primary key. In the second table Employee this key is used as a foreign key to associate each member to one department. For each department number in the table Employee there exists a department in the table Division. These tables are integer. If we would insert a tuple "4, Weber, A5" into table Employee then a database system should refuse this operation because in table Division there exists no value A5 and therefore there is no integrity.

1.3.4. Integrity endangering operations

There are three types of operation that could potentially endanger referential integrity:

• Inserting of a new tuple
• Deleting an existing tuple
• Updating attribute values of existing tuples

Of course with all these operations there has to be a check on all integrity rules. Selecting values (browsing in the database) does not change any values and is therefore no integrity endangering operation.

Inserting of a new tuple With this operation all three integrity rules are concerned. The following problems can occur:

• Key integrity: The for this insertion used primary key value is already used in another tuple.
• Entity integrity: The primary key of the new tuple is NULL.
• Referential integrity: A foreign key without an associated primary key is used.

These operations must either be rejected by the database system or transformed into consistent operations according to defined rules.

Examlpe Inserting of tuples

Deleting an existing tuple With this operation only referential integrity is concerned. If a foreign key points on a primary key that has been deleted, then the foreign key becomes invalid. The database system should react on a delete operation with one of the following solutions: Abort the operation, cascading delete (also the referenced tuple is deleted!) or set the value of the foreign key to NULL.

By modifying the value Div_No in the relation Division from "A1" to "A4", the division number of ID "1" in the relation Employee would become invalid.

1.4. Normalization

Normalisationis a process which we analyze and alter a database relation in order to get more concise and organized data structures. Normalised data is stable and has a natural structure. We call a relation normalized if:

• it does not contain any redundancy
• it does not cause maintenance problems
• it is an accurate representation of the data

Relations that aren't normalised contain non-atomic attributes and therefore can contain redundant information. Detailed planning of the ERM can help creating normalised relations. The following steps will explain how existing relations can be normalised step by step.

1.4.1. Dependencies

In order to be able to normalise a relation according to the three normal forms, we must first understand the concept of dependency between attributes within a relation. Functional dependency: If A and B are attributes of relation R, B is functionally dependenton A (denoted A --> B), if each value of A in R is associated with exactly one value of B in R. Example:

ID Name S1 Meier S2 Weber

The attribute Name is functionally dependent of attribute ID (ID --> Name). Identification key: If every attribute B of R is functionally dependent of A, than attribute A is a primary key. Beispiel:

ID Name Surname S1 Meier Hans S2 Weber Ueli

Attribute ID is the identification key Full functional dependency: We talk about full functional dependency if attribute B is functional dependent on A, if A is a composite primary key and B is not already functional dependent on parts of A. Beispiel:

IDStudent Name IDProfessor Grade S1 Meier P2 5 S2 Weber P1 6

The attribute Grade is fully functional dependent on the attributes IDStudent and IDProfessor.