Chapter 7: Relational Database Design
Database System Concepts, 7th Ed.
© Silberschatz, Korth and Sudarshan
See [Link] for conditions on re-use
Chapter 6: Database Design Using the E-R Model
------------------------------------------------------------
Outline
Overview of the Design Process
The Entity-Relationship Model
Complex Attributes
Mapping Cardinalities
Primary Key
Removing Redundant Attributes in Entity Sets
Reducing ER Diagrams to Relational Schemas
Extended E-R Features
Entity-Relationship Design Issues
Alternative Notations for Modeling Data
Other Aspects of Database Design
------------------------------------------------------------
Design Phases
Initial phase -- characterize fully the data needs of the prospective database users.
Second phase -- choosing a data model
• Applying the concepts of the chosen data model
• Translating these requirements into a conceptual schema of the database
• A fully developed conceptual schema indicates the functional requirements of the
enterprise
• Describe the kinds of operations (transactions) that will be performed on the data
Final Phase -- Moving from an abstract data model to the implementation of the database
Logical Design – Deciding on the database schema
• Database design requires that we find a “good” collection of relation schemas
• Business decision – What attributes should we record in the database?
• Computer Science decision – What relation schemas should we have and how should the
attributes be distributed among the various relation schemas?
Physical Design – Deciding on the physical layout of the database
------------------------------------------------------------
Design Alternatives
In designing a database schema, we must ensure that we avoid two major pitfalls:
Redundancy: a bad design may result in repeat information.
Redundant representation of information may lead to data inconsistency among the various
copies of information.
Incompleteness: a bad design may make certain aspects of the enterprise difficult or
impossible to model.
Avoiding bad designs is not enough. There may be a large number of good designs from
which we must choose.
------------------------------------------------------------
Design Approaches
Entity Relationship Model
Models an enterprise as a collection of entities and relationships.
Entity: a “thing” or “object” in the enterprise that is distinguishable from other objects.
Described by a set of attributes.
Relationship: an association among several entities.
Represented diagrammatically by an entity-relationship diagram.
Normalization Theory
Formalize what designs are bad, and test for them.
------------------------------------------------------------
ER Model – Database Modeling
The ER data model was developed to facilitate database design by allowing specification of
an enterprise schema that represents the overall logical structure of a database.
The ER data model employs three basic concepts:
• Entity sets
• Relationship sets
• Attributes
The ER model also has an associated diagrammatic representation, the ER diagram, which
can express the overall logical structure of a database graphically.
------------------------------------------------------------
Entity Sets
An entity is an object that exists and is distinguishable from other objects.
Example: specific person, company, event, plant.
An entity set is a set of entities of the same type that share the same properties.
Example: set of all persons, companies, trees, holidays.
An entity is represented by a set of attributes; descriptive properties possessed by all
members of an entity set.
Example:
instructor = (ID, name, salary)
course = (course_id, title, credits)
A subset of the attributes form a primary key of the entity set; uniquely identifying each
member of the set.
------------------------------------------------------------
Relationship Sets
A relationship is an association among several entities.
Example: advisor relationship between student and instructor.
A relationship set is a mathematical relation among n ≥ 2 entities, each taken from entity
sets.
Attributes can also be associated with relationship sets.
Example: advisor may have attribute date.
------------------------------------------------------------
Roles
Entity sets of a relationship need not be distinct.
Each occurrence plays a role in the relationship.
Example: course_id and prereq_id.
------------------------------------------------------------
Degree of a Relationship Set
Binary relationship – involves two entity sets.
Most relationships are binary.
Ternary relationship – involves three entity sets.
Example: proj_guide between instructor, student, and project.
------------------------------------------------------------
Complex Attributes
Attribute types:
• Simple and composite attributes
• Single-valued and multivalued attributes
• Derived attributes (example: age from date_of_birth)
• Domain – set of permitted values
Composite attributes allow attributes to be divided into subparts.
Example: name → first_name, middle_initial, last_name.
Example: address → street_number, street_name, city, state, zip_code.
------------------------------------------------------------
Mapping Cardinality Constraints
Express the number of entities to which another entity can be associated via a relationship
set.
Types:
• One-to-one
• One-to-many
• Many-to-one
• Many-to-many
Participation Constraints:
Total participation – every entity participates in at least one relationship.
Partial participation – some entities may not participate.
Minimum and maximum cardinality notation l..h:
1 indicates total participation.
* indicates no limit.
------------------------------------------------------------
Primary Key
Primary keys provide a way to specify how entities and relations are distinguished.
For Entity Sets:
The attribute values of an entity must uniquely identify the entity.
A key is a set of attributes that suffice to distinguish entities.
For Relationship Sets:
Primary key consists of the union of the primary keys of the participating entity sets.
Example:
advisor = ([Link], [Link])
Choice depends on mapping cardinality.
------------------------------------------------------------
Weak Entity Sets
A weak entity set is one whose existence is dependent on another entity, called its
identifying entity.
Weak entity sets do not have sufficient attributes to form a primary key.
They use identifying entity + discriminator to uniquely identify.
Represented by double rectangle.
Identifying relationship shown with double diamond.
Example:
section (course_id, sec_id, semester, year)
------------------------------------------------------------
Redundant Attributes
Attributes that duplicate relationship information should be removed.
Example:
student(ID, name, tot_cred, dept_name)
department(dept_name, building, budget)
dept_name in student is redundant if relationship stud_dept exists.
------------------------------------------------------------
Reduction to Relation Schemas
Entity sets and relationship sets can be expressed as relation schemas.
Strong entity set:
student(ID, name, tot_cred)
Weak entity set:
section(course_id, sec_id, sem, year)
Composite attributes are flattened.
Multivalued attributes are represented by separate schemas.
Example:
inst_phone(ID, phone_number)
Many-to-many relationship:
advisor(s_id, i_id)
------------------------------------------------------------
Extended E-R Features
Specialization (Top-down design):
Subgroupings within entity set.
Uses ISA relationship.
Supports overlapping/disjoint and total/partial constraints.
Generalization (Bottom-up design):
Combine entity sets sharing common features.
Completeness Constraint:
Total – entity must belong to a lower-level entity set.
Partial – entity need not belong.
Aggregation:
Treat relationship as abstract entity.
Allows relationships between relationships.
Example:
eval_for(s_ID, project_id, i_ID, evaluation_id)
------------------------------------------------------------
Design Issues
Common design decisions:
• Entity vs Attribute
• Entity vs Relationship set
• Binary vs Non-binary relationship
• Strong vs Weak entity
• Use of specialization/generalization
• Use of aggregation
------------------------------------------------------------
Converting Non-Binary Relationships to Binary Form
Create artificial entity set E.
Replace n-ary relationship with binary relationships.
Add identifying attribute to E.
Translate constraints carefully.
------------------------------------------------------------
Alternative ER Notations
Chen notation
IDE1FX (Crow’s foot notation)
------------------------------------------------------------
UML
UML: Unified Modeling Language.
UML Class Diagrams correspond to ER diagrams with some differences in notation.
------------------------------------------------------------
Other Aspects of Database Design
Functional Requirements
Data Flow
Workflow
Schema Evolution
------------------------------------------------------------
End of Chapter 6