The Entity Relationship (ER) data
model allows us to describe the data involved in a real world enterprise in
terms of objects and their relationships and is widely used to develop an
initial data base design. Within the larger context of the overall design
process, the ER model is used in a phase called Conceptual database design .
Database design and ER Diagrams:
The database design process can be
divided into six steps. The ER model is most relevant to the first three steps.
1. Requirement
Analysis:
The very first step in designing a database
application is to understand what data is to be stored in the database, what
application must be built in top of it, and what operations are most frequent
and subject to performance requirements. In other words, we must find out what
the users want from the database. 2.
Conceptual database Design:
The information gathered in the requirements analysis
step is used to develop a high-level description of the data to be stored in
the database, along with the constraints known to hold over this data. The ER
model is one of several highlevel or semantic, data models used in database
design. 3. Logical Database Design:
We must choose a database to convert the
conceptual database design into a database schema in the data model of the
chosen DBMS. Normally we will consider the Relational DBMS and therefore, the
task in the logical design step is to convert an ER schema into a relational
database schema.
Beyond ER Design
4. Schema Refinement:
This step is to analyze the collection of
relations in our relational database schema to identify potential problems, and
refine it. 5. Physical Database Design:
This step may simply involve building indexes
on some table and clustering some tables or it may involve substantial redesign
of parts of database schema obtained from the earlier steps.
6. Application and security Design:
Any software project that involves a DBMS must
consider aspects of the application that go beyond the database itself. We must describe
the role of each entity (users, user group, departments)in every process that
is reflected in some application task, as part of a complete workflow for the
task. A DBMS Provides several mechanisms to assist in this step.
Entity Types, Attributes and Keys:
Entities are specific objects or things
in the mini-world that are represented in the database.
For example, Employee or staff,
Department or Branch , Project are called Entity.
Attributes are
properties used to describe an entity.
For example an EMPLOYEE entity may
have a Name, SSN, Address, Sex, BirthDate and Department may have a Dname, Dno,
DLocation.
A specific entity
will have a value for each of its attributes.
For example a specific employee
entity may have Name=’John
Smith’, SSN=’123456789’, Address
=’731, Fondren, Houston, TX’,
Sex=’M’, BirthDate=’09-JAN-55
Each attribute
has a value set (or data type)
associated with it e.g. integer, string,
subrange, enumerated type,
Types of Attributes:
•
Simple (Atomic) Vs. Composite
•
Single Valued Vs. Multi Valued
•
S tored
Vs. Derived
•
Null Values
Simple Vs.
Composite Attribute:
l Simple
Attribute that are not divisible are
called simple or atomic attribute.
For example, Street_name or
Door_number. i.e. the division of composite attribute.
l Composite
The attribute may be composed of
several components.
For example, Address (Apt#,
House#, Street, City,
State, ZipCode, Country) or Name
(FirstName, MiddleName, LastName). Composition may form a hierarchy where some
components are themselves composite.
Single Valued
Vs. Multi Valued:
l Single
Valued
An
attribute having only one value.
For example, Age, Date of birth,
Sex, SSN
l Multi-valued
An entity may have multiple values for
that attribute.
For example, Color of a CAR or
Previous Degrees of a STUDENT. Denoted as {Color} or {PreviousDegrees}.Phone
number of an employee.
Stored Vs. Derived
In some cases two are more attributes values
are related for example the age and date
of birth of a person. For a particular person entity, the value of age can be
determined from the current (today s) date and the value of that person s Birthdate.
The Age attribute is hence called a derived attribute and is said to be
derivable from the Birthdate attribute , which is called stored attribute.
Null Values
In some cases a particular entity may not have
an applicable value for an attribute. For example, a college degree attribute
applies only to persons with college degrees. For such situation, a special
value called null is created.
Key attributes
of an Entity Type:
An important constraint on the entities of an
entity type is the Key or uniqueness
constraint on attributes. An entity type
the Key or uniqueness constraint on attributes. An entity type usually
has an attribute whose values are distinct for each individual entity in the
entity set. Such an attribute is called a Key
attribute, and its values can be used to identify each entity uniquely.
For exam ple name attribute is a key of the
company entity type, because no two companies are allowed to have the same
name. For the person entity type, a typical key attribute is
socialSecurityNumber (SSN). In ER diagrammatic notation, each key attribute has
its name underlined inside the oval. Relationships and Relationship sets:
A relationship is an association among two or
more entities. For example we may have the relationship that Antony works in
the Marketing department. A relationship
type R among the n entity types
E1,E2, ,En defines a set of associations or a relationship set- among entities from these entity types. Informally each relationship instance ri
in R is an association of entities, where the association includes exactly one
entity from each participating entity type. Each such relationship instance ri
represents the facts that the entities participating in ri are
related in some way in the corresponding mini world situation. For example
consider a relationship type Works_for
between the two entity types Employee and Department, which associates each
employee with the department for which the employee works. Each relationship
instance in the relationship set Works_for associates one employee entity and
one department entity.