Lecture 3

▪ The task of creating a database application is a complex

one, involving
▪ design of the database schema,
▪ design of the programs that access and update the data,
▪ design of a security scheme to control access to data.
▪ The database designer must interact with users of the
application to understand the needs of the application
(Determine the Data Requirements).
▪ represent them in a high-level fashion (Conceptual
Schema) that can be understood by the users,
▪ and then translate the requirements into lower levels of the
design (Logical Schema and Internal Schema).
▪ A high-level data model serves the database designer by
providing a conceptual framework in which to specify, in a
systematic fashion,
▪ the data requirements of the database users,
▪ and a database structure that fulfills these requirements.
▪ These requirements should be specified in as detailed and
complete a form as possible.
▪ The conceptual schema includes detailed descriptions of
▪ The entity types, and the attributes of the entities.
▪ Relationships among the entities,
▪ and constraints on the entities and relationships;
▪ these are expressed using the concepts provided by the high-
level data model.
▪ The high-level conceptual schema can also be used as a reference to
ensure that all users’ data requirements are met and that the
requirements do not conflict.
▪ The entity-relationship (ER) model is typically used to represent the
conceptual design.
▪ the conceptual schema is transformed from the high-level data
model into the implementation data model using a DBMS
▪ that is often use an implementation data model, such as the
relational (SQL) model.
▪ so the conceptual schema is transformed from the high-level
data model into the implementation data model.
▪ This step is called logical design.
▪ The focus at this point is on describing the data and their
relationships
▪ In a physical design phase:
▪ The logical schema is transformed to the internal schema
in which the internal storage structures, file organizations,
indexes, access paths, and physical design parameters for
the database files are specified
▪ In designing a database schema, we must ensure that we
avoid two major pitfalls:

1. Redundancy: A bad design may repeat information.

2. Incompleteness: A bad design may make certain aspects

of the enterprise difficult or impossible to model.
▪ Entity relationship (ER) data model
▪ provides a means of identifying entities to be represented in the
database
▪ and how those entities are related.
▪ The E-R model is very useful in mapping the meanings and
interactions of real-world enterprises onto a conceptual
schema.
▪ The E-R data model employs three basic concepts:
▪ entity sets (entities),
▪ relationship sets (relationships),
▪ and attributes.
▪ In building a data model a number of questions must be
addressed:
▪ What entities need to be described in the model?
▪ What characteristics or attributes of those entities need to be
recorded?
▪ Can an attribute or a set of attributes be identified that will
uniquely identify one specific occurrence of an entity?
▪ What associations or relationships exist between entities?
▪ Entity - An entity is a thing that exists and is distinguishable
-- an object, something in the environment.
▪ Types of entities:
▪ Regular
▪ Weak
▪ Entity Instance - An instance is a particular occurrence of
an entity. It may be
▪ a real object, such as a person, car, house, employee, book
▪ an abstracted object with a conceptual existence, such as a course,
a course offering, a company, a job, or a flight reservation.
▪ A set of attributes describes the entity
▪ Attributes are descriptive properties possessed by each member of
an entity set.
▪ For each attribute, there is a set of permitted values, called the
domain, or value set, of that attribute
▪ each entity can be described by a set of (attribute, data value) pairs
▪ For example, a particular instructor entity may be described by the
set {(ID, 89563), (name, Ahmed), (dept name, Computer_Science),
(salary, 3500)}.
▪ Types of Attributes
▪ Composite VS simple or atomic attributes
▪ Multi-valued VS single value
▪ Derived
▪ Key or uniqueness constraint on attributes
▪ An attribute of an entity type for which each entity must have a unique value is
called a key attribute of the entity type.
▪ For example, SSN of EMPLOYEE.
▪ A key attribute may be composite. For example, ID is a key of the applicant
entity type with components (National_ID, Application_no).
▪ Candidate Key: An entity type may have more than one key.
▪ Relationships - A relationship is a connection between entity
classes
▪ The cardinality of a relationship indicates the number of
instances in entity class E1 that can or must be associated with
instances in entity class E2.
▪ One-One Relationship - (citizen – passport ,
▪ One-Many Relationship - (student-Advisor, Customer-
Order)
▪ Many- Many Relationship - (e.g. Student-Organization,
Order-Products)
▪ Recursive Relationships - A relationship in which the
same entity participates more than once.
▪ PARTICIPATION CONSTRAINT:
▪ specifies whether the existence of an entity depends on its
being related to another entity via the relationship type.
▪ constraint specifies the minimum number of relationship
instances that each entity can participate in and is
sometimes called the minimum cardinality constraint.

Employee Has Car

• An Employee may have a car.

• A Car must be assigned to particular employee
▪ PARTICIPATION CONSTRAINT

• A department may hire many employees ( Zero or more)

• An employee must be employed by a department
• (Department membership is Optional, Employee membership is
Mandatory)
▪ An employee MUST work for a department
▪ An employee entity can exist only if it participates in a WORKS_FOR
relationship instance,Thus its participation is TOTAL
▪ Only some employees manage departments, The participation is
PARTIAL
▪ Cardinality Ratio: for a binary relationship specifies the maximum
number of relationship instances that an entity can participate in.
▪ A formal constraint (min,max) where m, n are min and max number of
times an entity participates in a relationship instance.
▪ For example, (0,10) means partial participation, and (1,max)
means total participation.
▪ Weak Entity Types
▪ An entity that does not have a key attribute
▪ A weak entity must participate in an identifying relationship type
with an owner or identifying entity type
▪ Entities are identified by the combination of:
▪ A partial key of the weak entity type
▪ The particular entity they are related to in the identifying entity
type
▪ Rectangles represent ENTITY CLASSES
▪ Circles represent ATTRIBUTES
▪ Diamonds represent RELATIONSHIPS
▪ Arcs –
▪ Arcs connect entities to relationships.
▪ Arcs are also used to connect attributes to entities.
▪ Some styles of entity-relationship diagrams use arrows and double
arrows to indicate participation constraint (total or partial) and the one
and the many in relationships.
▪ Some use forks etc.
▪ Underline - Key attributes of entities are underlined.
Meaning

ENTITY TYPE

WEAK ENTITY TYPE

RELATIONSHIP TYPE

IDENTIFYING RELATIONSHIP TYPE

ATTRIBUTE

KEY ATTRIBUTE

MULTIVALUED ATTRIBUTE

COMPOSITE ATTRIBUTE

DERIVED ATTRIBUTE

TOTAL PARTICIPATION OF E2 IN R

CARDINALITY RATIO 1:N FOR E1:E2 IN R

STRUCTURAL CONSTRAINT (min, max) ON PARTICIPATION

OF E IN R
▪ An Example
▪ A company is organized into departments. Each department has a
unique name, a unique number, and a particular employee who
manages the department. A department may have several locations.

▪ A department may control a number of projects, each of which has a

unique name, a unique number, and a single location. A project must
controlled by department
▪ An Example (Cont’d)
▪ We store employee’s name, social security number, address, salary,
gender and birth date. An employee must be assigned to one
department and must work on one or more projects, which are not
necessarily controlled by the same department. We keep track of the
number of hours per week that an employee works on each project.
We also keep track of the direct supervisor of each employee.

▪ We want to keep track of the dependents of each employee for

insurance purposes. We keep each dependent’s first name, gender,
birth date and relationship to that employee.