RSS
Twitter
Facebook
Showing posts with label Entity-Relationship Model. Show all posts
Database Management System × Entity-Relationship Model
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 attributedomain pairs.
An instance of a relation is a
time-varying set of tuples where each tuple consists of attribute-value pairs.
Database Management System × Entity-Relationship Model
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.
Database Management System × Entity-Relationship Model
Before beginning to draw the ER
model, read the requirements specification carefully. Document any assumptions
you need to make.
1. Identify 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
Database Management System × Entity-Relationship Model
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
Database Management System × Entity-Relationship Model
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
Database Management System × Entity-Relationship Model
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.
Database Management System × Entity-Relationship Model
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.
A one to one relationship - a man
can only marry one woman, and a woman can only marry one man, so it is a one to
one (1:1) relationship
A one to may relationship - one
manager manages many employees, but each employee only has one manager, so it
is a one to many (1:n) relationship
A many to one relationship - many
students study one course. They do not study more than one course, so it is a
many to one (m:1) relationship
A many to many relationship - One
lecturer teaches many students and a student is taught by many lecturers, so it
is a many to many (m:n) relationship
Database Management System × Entity-Relationship Model
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.
Database Management System × Entity-Relationship Model
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
If there are three entity types
involved it is a ternary 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.
Database Management System × Entity-Relationship Model
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:
Database Management System × Entity-Relationship Model
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.
Database Management System × Entity-Relationship Model
If a particular binary
relationship can be derived from a higher-degree relationship at all times, then
it is redundant.
For example, the TAUGHT_DURING
binary relationship in Figure 3.18 (see next slide) can be derived from the
ternary relationship OFFERS (based on the meaning of the relationships)
+notation.PNG)
+notation+for+relationship+constraints.PNG)
Database Management System × Entity-Relationship Model
Specified on each participation
of an entity type E in a relationship type R
Specifies that each entity e in E
participates in at least min and at most max
relationship instances in R
Default(no constraint): min=0,
max=n (signifying no limit)
Must have min_max, min_0, max _1
Derived from the knowledge of
mini-world constraints
Examples:
A department has exactly one
manager and an employee can manage at most one
department.
Specify (0,1) for participation
of EMPLOYEE in MANAGES
Specify (1,1) for participation
of DEPARTMENT in MANAGES
An employee can work for exactly
one department but a department can have any
number of employees.
Specify (1,1) for participation
of EMPLOYEE in WORKS_FOR
Specify (0,n) for participation of DEPARTMENT in
WORKS_FOR
Database Management System × Entity-Relationship Model
Cardinality ratio (of a binary
relationship): 1:1,1:N, N:1, or M:N
Shown by placing appropriate
numbers on the relationship edges.
Participation constraint (on each
participating entity type): total (called existence
dependency) or partial.
Total shown by double line, partial by single line
Database Management System × Entity-Relationship Model
A relationship type can have
attributes:
For example, HoursPerWeek of
WORKS_ON
Its value for each relationship
instance describes the number of hours per week that
an EMPLOYEE works on a PROJECT.
A value of HoursPerWeek depends
on a particular (employee, project) combination
Most relationship attributes are
used with M:N relationships.
In 1:N relationships, they can be
transferred to the entity type on the N-side of the
relationship.
