Entity-Relationship Model part3.

Data integrity

Data is accepted based on certain rules & there fore data is valid.

Enforcing data integrity ensures that the data in the database is valid and correct.

Keys play an important role in maintaining data integrity.

The various types of keys that have been identified are :

Candidate key

Primary key

Alternate key

Composite key

Foreign Key

Candidate key

An attribute or set of attributes that uniquely identifies a row is called a Candidate key.

This attribute has values that are unique

Vehicle

Primary Key

The Candidate key that you choose to identify each row uniquely is called the Primary key.

Alternate Key

A Candidate key that is not chosen as a Primary key is an Alternate key.

Composite Key

In certain tables, a single attribute cannot be used to identify rows uniquely and a combination of two or more attributes is used as a Primary key. Such keys are called Composite keys.

Purchase

Foreign Key

When a primary key of one table appears as an attribute in another table, it is called the Foreign key in the second table

A foreign key is used to relate two tables.

Weak entity:

A weak entity does not have a distinguishing attribute of its own and mostly are dependent entities, which are part of some another entity.

A weak entity will always be related to one or more strong entities. They can be also understood as multi-valued attributes.

Relationships

A relationship type is a meaningful association between entity types

A relationship is an association of entities where the association includes one entity from each participating entity type.

Relationship types are represented on the ER diagram by a series of lines. As always, there are many notations in use today...

In the original Chen notation, the relationship is placed inside a diamond, e.g. managers manage employees:

2.3.1 Degree of a Relationship

The number of participating entities in a relationship is known as the degree of the relationship.

If there are two entity types involved it is a binary relationship type

It is possible to have a n-array relationship (e.g. quaternary or unary).

Unary relationships are also known as a recursive relationship.

It is a relationship where the same entity participates more than once in different roles.

In the example above we are saying that employees are managed by employees.

If we wanted more information about who manages whom, we could introduce a second entity type called manager.

2.3.2 Replacing ternary relationships

When a ternary relationship occurs in an ER model they should always be removed before finishing the model. Sometimes the relationships can be replaced by a series of binary relationships that link pairs of the original ternary relationship.

This can result in the loss of some information - It is no longer clear which sales assistant sold a customer a particular product.

Try replacing the ternary relationship with an entity type and a set of binary relationships.

Relationships are usually verbs, so name the new entity type by the relationship verb rewritten as a noun.

The relationship sells can become the entity type sale.

So a sales assistant can be linked to a specific customer and both of them to the sale of a particular product.

This process also works for higher order relationships.

Cardinality

Relationships are rarely one-to-one

For example, a manager usually manages more than one employee

This is described by the cardinality of the relationship, for which there are four possible categories.

One to one (1:1) relationship

One to many (1:m) relationship

Many to one (m:1) relationship

Many to many (m:n) relationship

On an ER diagram, if the end of a relationship is straight, it represents 1, while a "crow's foot" end represents many.

2.3.4 Optionality

A relationship can be optional or mandatory.

If the relationship is mandatory

an entity at one end of the relationship must be related to an entity at the other end. The optionality can be different at each end of the relationship

For example, a student must be on a course. This is mandatory. To the relationship

`student studies course' is mandatory.

But a course can exist before any students have enrolled. Thus the relationship

`course is_studied_by student' is optional.

To show optionality, put a circle or `0' at the `optional end' of the relationship.

As the optional relationship is `course is_studied_by student', and the optional part of this is the student, then the `O' goes at the student end of the relationship connection.

It is important to know the optionality because you must ensure that whenever you create a new entity it has the required mandatory links.

2.4.1Entities

Bus - Company owns busses and will hold information about them.

Route - Buses travel on routes and will need described.

Town - Buses pass through towns and need to know about them

Driver - Company employs drivers, personnel will hold their data.

Stage - Routes are made up of stages

Garage - Garage houses buses, and need to know where they are. A bus is allocated to a route and a route may have several buses. Bus-route (m:1) is serviced by

A route comprises of one or more stages. route-stage (1:m) comprises

One or more drivers are allocated to each stage. driver-stage (m:1) is allocated .

A stage passes through some or all of the towns on a route. stage-town (m:n) passes-through

A route passes through some or all of the towns route-town (m:n) passes-through

Some of the towns have a garage garage-town (1:1) is situated

A garage keeps buses and each bus has one `home' garage garage-bus (m:1) is garaged

 

Bus (reg-no,make,size,deck,no-pass)

Route (route-no,avg-pass)

Driver (emp-no,name,address,tel-no)

Town (name)

Stage (stage-no)

Garage (name,address)

Example: Entity and Relationship sets for the hospital called General Hospital, Patients,

Doctors, Beds, Examines, Bed Assigned, Accounts, has Account.

patients, entity set with attributes SSNo, LastName, FirstName, HomePhone, Sex, DateofBirth, Age, Street, City, State, Zip.

doctors, entity set with attributes SSNo, LastName, FirstName, OfficePhone, Pager, Specialty.

examines, relational set with attributes Date, Time, Diagnosis, Fee. beds, entity set with attributes RoomNumber, BedNumber, Type, Status, PricePerHour.

Bed_assigned, relational set with attributes DateIn, TimeIn, DateOut, TimeOut, Amount.

accounts, weak entity set with attributes DateIn, DateOut, Amount. has_account, relational set with no Attributes

2.5 Constructing an ER model

Before beginning to draw the ER model, read the requirements specification carefully. Document any assumptions you need to make.

1. dentify entities - list all potential entity types. These are the object of interest in the system. It is better to put too many entities in at this stage and them discard them later if necessary.

2. Remove duplicate entities - Ensure that they really separate entity types or just two names for the same thing.

o Also do not include the system as an entity type

o e.g. if modelling a library, the entity types might be books, borrowers, etc.

o The library is the system, thus should not be an entity type.

3. List the attributes of each entity (all properties to describe the entity which are relevant to the application).

o Ensure that the entity types are really needed.

o are any of them just attributes of another entity type?

o if so keep them as attributes and cross them off the entity list.

o Do not have attributes of one entity as attributes of another entity!

4. Mark the primary keys.

o Which attributes uniquely identify instances of that entity type?

o This may not be possible for some weak entities.

5. Define the relationships

o Examine each entity type to see its relationship to the others.

6. Describe the cardinality and optionality of the relationships

o Examine the constraints between participating entities.

7. Remove redundant relationships

o Examine the ER model for redundant relationships.

ER modelling is an iterative process, so draw several versions, refining each one until you are happy with it. Note that there is no one right answer to the problem, but some solutions are better than others!

Overview

• construct an ER model

• understand the problems associated with ER models

• understand the modelling concepts of Enhanced ER modelling

Types of Data Integrity

Data Integrity falls into the following categories

Entity integrity

Entity integrity ensures that each row can be uniquely identified by an attribute called the Primary key. The Primary key cannot have a NULL value.

Domain integrity

Domain integrity refers to the range of valid entries for a given column. It ensures that there are only valid entries in the column.

Referential integrity

Referential integrity ensures that for every value of a Foreign key, there is a matching value of the Primary key.

2.7 Relational database

Relations can be represented as two-dimensional data tables with rows and columns The rows of a relation are called tuples.

The columns of a relation are called attributes.

The attributes draw values from a domain (a legal pool of values).

The number of tuples in a relation is called its cardinality while the number of attributes in a relation is called its degree

A relation also consists of a schema and an instance

Schema defines the structure of a relation which consists of a fixed set of attribute- domain pairs.

An instance of a relation is a time-varying set of tuples where each tuple consists of attribute-value pairs.