The entity integrity rule states that

• Logical Database design and Relational Model

• Chapter 4

Lesson Content:

• What is a ‘Relations’? • What is a ‘Relational Model’? • Component of a ‘Relational Model’? • How Relations are different to E-R Diagram? • Keys in Relations. • What is integrity constraints? • What is Referential Integrity? • Mapping E-R diagram to Relational Models:

• Unary • Binary • Ternary • Supertype/Subtypes

• Data Normalization: Form 1, Form 2, and Form 3. 2

What is a Relations?

• A relation is a named, two-dimensional table of data.

• A table consists of rows (records) and columns (attribute or field).

• Requirements for a table to qualify as a relation: • It must have a unique identifier (primary key).

• Every attribute value must be atomic (not multivalued, not composite).

• Every row must be unique (can’t have two rows with exactly the same values for all their fields).

• Attributes (columns) in tables must have unique names.

• The order of the columns must be irrelevant.

• The order of the rows must be irrelevant.

3

Correspondence with E-R Model

• Relations (tables) correspond with entity types.

• Rows correspond with entity instances.

• Columns correspond with attributes.

• NOTE: The word relation (in relational database) is NOT the same as the word relationship (in E-R model).

4

Integrity Constraints

1. Entity Integrity • No primary key attribute may be null. All primary key fields MUST have data.

2. Action Assertions • Business rules (Recall from Chapter 3)

3. Domain Constraints • Allowable values for an attribute (We shall see this clearly next)

5

6

1. Domain Constraints Allowable values for an attribute.

Referential Integrity:

• Referential Integrity–rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null) • For example: Delete Rules

• Restrict–don’t allow delete of “parent” side if related rows exist in “dependent” side

• Cascade–automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted

• Set-to-Null–set the foreign key in the dependent side to null if deleting from the parent side  not allowed for weak entities

7

8

Figure 4-5

Referential integrity constraints (Pine Valley Furniture)

Referential integrity

constraints are drawn via

arrows from dependent to

parent table

Transforming EER Diagrams into Relations

•Mapping Regular Entities to Relations • Simple attributes: E-R attributes map directly onto the

relation • Composite attributes: Use only their simple, component

attributes • Multivalued Attribute: Becomes a separate relation with a

foreign key taken from the superior entity

9

(a) CUSTOMER entity

type with simple

attributes

Figure 4-8 Mapping a regular entity

(b) CUSTOMER relation

10

Transforming EER Diagrams into Relations (cont.)

•Mapping Binary Relationships • One-to-Many–Primary key on the one side becomes

a foreign key on the many side • Many-to-Many–Create a new relation with the

primary keys of the two entities as its primary key • One-to-One–Primary key on mandatory side

becomes a foreign key on optional side

11

12

Figure 4-12 Example of mapping a 1:M relationship

a) Relationship between customers and orders

Note the mandatory one

b) Mapping the relationship

Again, no null value in the foreign

key…this is because of the mandatory

minimum cardinality.

Foreign key

13

Figure 4-13 Example of mapping an M:N relationship

a) Completes relationship (M:N)

The Completes relationship will need to become a separate relation.

14

new

intersection

relation

Foreign key

Foreign key

Composite primary key

Figure 4-13 Example of mapping an M:N relationship (cont.)

b) Three resulting relations

Transforming EER Diagrams into Relations (cont.)

•Mapping Unary Relationships • One-to-Many–Recursive foreign key in the same relation • Many-to-Many–Two relations:

• One for the entity type • One for an associative relation in which the primary key has

two attributes, both taken from the primary key of the entity

15

16

Figure 4-17 Mapping a unary 1:N relationship

(a) EMPLOYEE entity with

unary relationship

(b) EMPLOYEE

relation with

recursive foreign

key

17

Figure 4-18 Mapping a unary M:N relationship

(a) Bill-of-materials

relationships (M:N)

(b) ITEM and

COMPONENT

relations

Transforming EER Diagrams into Relations (cont.)

•Mapping Ternary (and n-ary) Relationships •One relation for each entity and one for the associative

entity •Associative entity has foreign keys to each entity in the

relationship

18

19

Figure 4-19 Mapping a ternary relationship

a) PATIENT TREATMENT Ternary relationship with associative entity

20

b) Mapping the ternary relationship PATIENT TREATMENT

Remember that

the primary key

MUST be unique.

Figure 4-19 Mapping a ternary relationship (cont.)

This is why treatment

date and time are

included in the

composite primary

key.

But this makes a very

cumbersome key…

It would be better to create

a surrogate key like

Patient-Treatment#.

Transforming EER Diagrams into Relations (cont.)

• Mapping Supertype/Subtype Relationships • One relation for supertype and for

each subtype

• Supertype attributes (including identifier and subtype discriminator) go into supertype relation

• Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation

• 1:1 relationship established between supertype and each subtype, with supertype as primary table 21

22

Figure 4-21

Mapping supertype/subtype relationships to relations

These are implemented as one-to-one relationships.

Data Normalization

•Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data • The process of decomposing relations with anomalies

to produce smaller, well-structured relations

23

Anomalies in this Table

• Insertion–can’t enter a new employee without having the employee take a class (or at least empty fields of class information)

• Deletion–if we remove employee 140, we lose information about the existence of a Tax Acc class

• Modification–giving a salary increase to employee 100 forces us to update multiple records

24

Why do these anomalies exist?

Because there are two themes (entity types) in this one relation. This results in data duplication and an

unnecessary dependency between the entities.

Data Normalization

• Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data

• The process of decomposing relations with anomalies to produce smaller, well-structured relations

• When data does not look normal we normalize it! 25

Well-Structured Relations

• Characteristics: • A relation that contains minimal data redundancy and allows users to insert,

delete, and update rows without causing data inconsistencies

• Goal is to avoid anomalies • Insertion Anomaly–adding new rows forces user to create duplicate data.

• Deletion Anomaly–deleting rows may cause a loss of data that would be needed for other future rows (Remember referential integrity?).

• Modification Anomaly–changing data in a row forces changes to other rows because of duplication.

26

General rule of thumb: A table should not connect to more than one entity type.

27

Table with multivalued attributes, not in 1st normal form

Note: This is NOT a relation.

28

Table with no multivalued attributes and unique

rows, in 1st normal form

Note: This is a relation, but not a well-structured one.

Notice that we have more than one table here.

Anomalies in this Table

Insertion–if new product is ordered for order 1007 of existing customer, customer data must be re-entered, causing duplication

Deletion–if we delete the Dining Table from Order 1006, we lose information concerning this item’s finish and price

Update–changing the price of product ID 4 requires update in multiple records

29

Why do these anomalies exist?

Because there are multiple themes (entity types) in one relation. This results in duplication and an

unnecessary dependency between the entities.

Second Normal Form

•1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key • Every non-key attribute must be defined by the entire key, not

by only part of the key • No partial functional dependencies

•What they mean: Split the tables so each table has attributes related only to the primary key.

30

31

OrderID OrderDate, CustomerID, CustomerName, CustomerAddress

Therefore, NOT in 2nd Normal Form

CustomerID CustomerName, CustomerAddress

ProductID ProductDescription, ProductFinish, ProductStandardPrice

OrderID, ProductID OrderQuantity

Figure 4-27 Functional dependency diagram for INVOICE

32

Partial dependencies are removed, but there are still transitive dependencies.

- Transitive dependency means: find tables within tables.

- Clever students do sometimes find these tables from the first attempt so they

move from F2 to F3 immediately.

Getting it into Second Normal Form

Figure 4-28 Removing partial dependencies

Third Normal Form

• 2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes)

• Note: This is called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third

• Solution: Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table

33

34

Transitive dependencies are removed.

Figure 4-29 Removing partial dependencies