Chapter 2: Database Design Chapter 2: Database Design and Entity-Relationship Modeland Entity-Relationship Model Database DesignDatabase Design Entity SetsEntity Sets Relationship SetsRelationship Sets Design Issues Design Issues Mapping Constraints Mapping Constraints KeysKeys E-R DiagramE-R Diagram Extended E-R FeaturesExtended E-R Features Design of an E-R Database SchemaDesign of an E-R Database Schema

Database Design [Raghu-Chap. Database Design [Raghu-Chap. 2 & Silberchatz Sect 2.8.1] (1)2 & Silberchatz Sect 2.8.1] (1)

Part of a larger SW system designPart of a larger SW system design

Divided into eight steps:Divided into eight steps:

1.1. Requirements AnalysisRequirements Analysis: Understand what data is to be : Understand what data is to be stores, what applications must be built, what operations stores, what applications must be built, what operations are most frequent and require performance requirements. are most frequent and require performance requirements. Several methodologies can be used (Volere, etc)Several methodologies can be used (Volere, etc)

2.2. Conceptual Database DesignConceptual Database Design: High-level desciption of : High-level desciption of the data to be stored and the constraints known to hold the data to be stored and the constraints known to hold over the data. Semantic data models are used (ER, UML over the data. Semantic data models are used (ER, UML class diagrams, etc).class diagrams, etc).

3.3. Logical Database DesignLogical Database Design: Database schema specified : Database schema specified using the data model supported by the DBMS chosen using the data model supported by the DBMS chosen (Relational model).(Relational model).

Database Design [Raghu-Chap. Database Design [Raghu-Chap. 2 & Silberchatz Sect 2.8.1] (2)2 & Silberchatz Sect 2.8.1] (2)

1.1. Schema RefinementSchema Refinement: Modify the relational model : Modify the relational model according to normalization theory rules.according to normalization theory rules.

2.2. Physical Database Design: Physical Database Design: TakingTaking into account typical into account typical expected workloads, further refine the database design to expected workloads, further refine the database design to ensure that performance requirements are met. (Tuning ensure that performance requirements are met. (Tuning through redesign, construction of indexes, etc)through redesign, construction of indexes, etc)

3.3. Application and Security DesignApplication and Security Design: Identify the actors : Identify the actors (users, user groups,etc) and processses involved in the (users, user groups,etc) and processses involved in the application. Describe the role of each actor and identify the application. Describe the role of each actor and identify the parts of the database that are accessible for which actors parts of the database that are accessible for which actors (Database security mechanisms)(Database security mechanisms)

4.4. Implementation and TestingImplementation and Testing

5.5. TuningTuning

Entity SetsEntity Sets A A databasedatabase can be modeled as: can be modeled as:

a collection of entities,a collection of entities, relationship among entities.relationship among entities.

An An entityentity is an object that exists and is is an object that exists and is distinguishable from other objects.distinguishable from other objects. Example: specific person, company, event, plantExample: specific person, company, event, plant

Entities have Entities have attributesattributes Example: people have Example: people have names names and and addressesaddresses

An An entity setentity set is a set of entities of the same type that is a set of entities of the same type that share the same properties.share the same properties. Example: set of all persons, companies, trees, Example: set of all persons, companies, trees,

holidaysholidays

Entity Sets Entity Sets customercustomer and and loanloan

customer-id customer- customer- customer- loan- amount name street city number

AttributesAttributes An entity is represented by a set of An entity is represented by a set of attributesattributes, which are , which are

descriptive properties possessed by all members of an entity descriptive properties possessed by all members of an entity set.set.

Example: Example:

customer (customer-id, customer-name, customer-street, customer-city)customer (customer-id, customer-name, customer-street, customer-city)loan (loan-number, amount)loan (loan-number, amount)

DomainDomain – the set of permitted values for each attribute – the set of permitted values for each attribute Attribute types:Attribute types:

SimpleSimple and and compositecomposite attributes. attributes.• E.g. composite attributes: E.g. composite attributes: name = first name, middle name = first name, middle

name, last namename, last name Single-valuedSingle-valued and and multi-valuedmulti-valued attributes attributes

• E.g. multivalued attribute: E.g. multivalued attribute: phone-numbersphone-numbers Derived Derived attributesattributes

• Can be computed from other attributesCan be computed from other attributes• E.g. E.g. ageage, given date of birth, given date of birth

Composite AttributesComposite Attributes

Relationship SetsRelationship Sets A A relationshiprelationship is an association among several is an association among several

entitiesentities

Example:Example: HayesHayes depositordepositor A-102A-102

customercustomer entity entity relationship set relationship set accountaccount entity entity

A A relationship setrelationship set is a mathematical relation is a mathematical relation among among nn 2 entities, each taken from entity 2 entities, each taken from entity setssets

{({(ee11, , ee22, … , … eenn) | ) | ee11 EE11, , ee22 EE22, …, , …, eenn EEnn}}

where (where (ee11, , ee22, …, , …, eenn) is a relationship) is a relationship Example: (Hayes, A-102) Example: (Hayes, A-102) depositordepositor

Relationship Set Relationship Set borrowerborrower

Relationship Sets (Cont.)Relationship Sets (Cont.) An An attributeattribute can also be property of a relationship set. can also be property of a relationship set.

For instance, the For instance, the depositor depositor relationship set between entity sets relationship set between entity sets customer customer and and account account may have the attribute may have the attribute access-dateaccess-date

Degree of a Relationship SetDegree of a Relationship Set Refers to number of entity sets that participate in a Refers to number of entity sets that participate in a

relationship set.relationship set. Relationship sets that involve two entity sets are Relationship sets that involve two entity sets are

binarybinary (or degree two). Generally, most relationship (or degree two). Generally, most relationship sets in a database system are binary.sets in a database system are binary.

Relationship sets may involve more than two entity Relationship sets may involve more than two entity sets. sets.

• E.g. Suppose employees of a bank may have jobs (responsibilities) at multiple branches, with different jobs at different branches. Then there is a ternary relationship set between entity sets employee, job and branch

Relationships between more than two entity sets are Relationships between more than two entity sets are rare. Most relationships are binary. (More on this rare. Most relationships are binary. (More on this later.)later.)

E-R DiagramsE-R Diagrams

Rectangles represent entity sets.

Diamonds represent relationship sets.

Lines link attributes to entity sets and entity sets to relationship sets.

Ellipses represent attributes

Double ellipses represent multivalued attributes.

Dashed ellipses denote derived attributes.

Underline indicates primary key attributes (will study later)

E-R Diagram With Composite, Multivalued, and E-R Diagram With Composite, Multivalued, and Derived AttributesDerived Attributes

Relationship Sets with Relationship Sets with AttributesAttributes

RolesRoles Entity sets of a relationship need not be distinctEntity sets of a relationship need not be distinct The labels “manager” and “worker” are called The labels “manager” and “worker” are called rolesroles; they ; they

specify how employee entities interact via the works-for specify how employee entities interact via the works-for relationship set.relationship set.

Roles are indicated in E-R diagrams by labeling the lines that Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to rectangles.connect diamonds to rectangles.

Role labels are optional, and are used to clarify semantics of Role labels are optional, and are used to clarify semantics of the relationshipthe relationship

Database constraintsDatabase constraints

A database schema must represent A database schema must represent constraintsconstraints to which the contents of the to which the contents of the database must conform.database must conform.

Types of database constraints:Types of database constraints: Mapping cardinalitiesMapping cardinalities Participation constraintsParticipation constraints KeysKeys

Mapping CardinalitiesMapping Cardinalities Express the number of entities to which Express the number of entities to which

another entity can be associated via a another entity can be associated via a relationship set.relationship set.

Most useful in describing binary relationship Most useful in describing binary relationship sets.sets.

For a binary relationship set the mapping For a binary relationship set the mapping cardinality must be one of the following types:cardinality must be one of the following types: One to oneOne to one One to manyOne to many Many to oneMany to one Many to many Many to many

Mapping CardinalitiesMapping Cardinalities

One to one One to many

Note: Some elements in A and B may not be mapped to any elements in the other set

Mapping Cardinalities Mapping Cardinalities

Many to one Many to many

Note: Some elements in A and B may not be mapped to any elements in the other set

Cardinality ConstraintsCardinality Constraints

We express cardinality constraints by We express cardinality constraints by drawing either a directed line (drawing either a directed line (), ), signifying “signifying “oneone,” or an undirected line ,” or an undirected line (—),(—), signifying “ signifying “manymany,” between the ,” between the relationship set and the entity set.relationship set and the entity set.

One-to-one relationshipOne-to-one relationship

A customer is associated with A customer is associated with at most at most oneone loan via the relationship loan via the relationship borrowerborrower

A loan is associated with at most one A loan is associated with at most one customer via customer via borrowerborrower

One-To-Many RelationshipOne-To-Many Relationship

In the one-to-many relationship a loan is In the one-to-many relationship a loan is associated with associated with at most oneat most one customer customer via via borrowerborrower, a customer is associated , a customer is associated with with several (including 0)several (including 0) loans via loans via borrowerborrower

Many-To-One RelationshipsMany-To-One Relationships

In a many-to-one relationship a loan is In a many-to-one relationship a loan is associated with associated with several (including 0)several (including 0) customers via customers via borrowerborrower, a customer is , a customer is associated with associated with at most oneat most one loan via loan via borrowerborrower

Many-To-Many RelationshipMany-To-Many Relationship

A customer is associated with A customer is associated with several (possibly 0)several (possibly 0) loans via loans via borrowerborrower

A loan is associated with A loan is associated with several several (possibly 0)(possibly 0) customers via borrower customers via borrower

Mapping Cardinalities affect ER DesignMapping Cardinalities affect ER Design

Can make access-date an attribute of account, instead of a relationship attribute, if each account can have only one customer

I.e., the relationship from account to customer is many to one, or equivalently, customer to account is one to many

Participation constraints (of an Entity Set in Participation constraints (of an Entity Set in a Relationship Set)a Relationship Set)

Total participation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set

E.g. participation of loan in borrower is total which means that every loan must have a customer associated to it via borrower

Partial participation: some entities may not participate in any relationship in the relationship set

E.g. participation of customer in borrower is partial

Alternative Notation for Alternative Notation for Cardinality LimitsCardinality Limits

Cardinality limits can also express participation constraints

KeysKeys

A A super keysuper key of an entity set is a set of one or of an entity set is a set of one or more attributes whose values uniquely more attributes whose values uniquely determine each entity.determine each entity.

A A candidate keycandidate key of an entity set is a minimal of an entity set is a minimal super keysuper key customer-idcustomer-id is candidate key of is candidate key of customercustomer account-numberaccount-number is candidate key of is candidate key of accountaccount

Although several candidate keys may exist, Although several candidate keys may exist, one of the candidate keys is selected to be one of the candidate keys is selected to be the the primary keyprimary key..

Keys for Relationship SetsKeys for Relationship Sets The The combination of primary keyscombination of primary keys of the participating of the participating

entity sets forms a entity sets forms a super keysuper key of a relationship set. of a relationship set. ((customer-id, account-numbercustomer-id, account-number) is the super key of ) is the super key of depositordepositor

A pair of entity sets can have A pair of entity sets can have at most oneat most one relationship in relationship in a particular relationship set. a particular relationship set. • E.g. if we wish to track all access-dates to each account by each E.g. if we wish to track all access-dates to each account by each

customer, we cannot assume a relationship for each access. customer, we cannot assume a relationship for each access. We can use a multivalued attribute thoughWe can use a multivalued attribute though

Must consider the Must consider the mapping cardinalitymapping cardinality of the relationship of the relationship set when deciding which are the candidate keys set when deciding which are the candidate keys

Many-to-manyMany-to-many: primary key of relationship is union of the : primary key of relationship is union of the primary keys of entitiesprimary keys of entities

Many-to-oneMany-to-one: primary key of relationship is the key of the : primary key of relationship is the key of the “many” entity“many” entity

One-to-oneOne-to-one: primary key of relationship can be either primary : primary key of relationship can be either primary key.key.

E-RE-R Diagram with a Ternary Diagram with a Ternary RelationshipRelationship

Cardinality Constraints on Cardinality Constraints on Ternary RelationshipTernary Relationship

We allow We allow at most one arrow out of a ternaryat most one arrow out of a ternary (or (or greater degree) relationship to indicate a cardinality greater degree) relationship to indicate a cardinality constraintconstraint E.g. an arrow from E.g. an arrow from works-onworks-on to to job job indicates each employee works indicates each employee works

on at most one job at any branch.on at most one job at any branch. If there was more than one arrow, there were two If there was more than one arrow, there were two

ways of defining the meaning. ways of defining the meaning. E.g a ternary relationship E.g a ternary relationship R R between between AA,, B B and and C C with arrows to with arrows to B B

and and C C could mean:could mean:1.1. each each A A entity is associated with a unique entity from entity is associated with a unique entity from B B and and C C or or 2. each pair of entities from (2. each pair of entities from (A, BA, B) is associated with a unique ) is associated with a unique C C

entity, and each pair (entity, and each pair (A, CA, C) is associated with a unique ) is associated with a unique BB Each alternative has been used in different Each alternative has been used in different

formalisms. To avoid confusion we outlaw more than formalisms. To avoid confusion we outlaw more than one arrowone arrow

Binary Vs. Non-Binary Binary Vs. Non-Binary RelationshipsRelationships

Some relationships that appear to be non-Some relationships that appear to be non-binary may be better represented using binary may be better represented using binary relationshipsbinary relationships E.g. A ternary relationship E.g. A ternary relationship parentsparents, relating a , relating a

child to his/her father and mother, is best child to his/her father and mother, is best replaced by two binary relationships, replaced by two binary relationships, fatherfather and and mothermother• Using two binary relationships allows partial Using two binary relationships allows partial

information (e.g. only mother being known)information (e.g. only mother being known) But there are some relationships that are But there are some relationships that are

naturally non-binarynaturally non-binary• E.g. E.g. works-onworks-on

Converting Non-Binary Relationships to Converting Non-Binary Relationships to Binary FormBinary Form

In general, any non-binary relationship can be represented In general, any non-binary relationship can be represented using binary relationships by creating an artificial entity set.using binary relationships by creating an artificial entity set.

Replace Replace R R between entity sets A, B and Cbetween entity sets A, B and C by an entity set by an entity set EE, , and three relationship sets: and three relationship sets:

1. 1. RRAA, relating , relating E E and and A A 2. 2.RRBB, relating , relating E E and and BB

3. 3. RRCC, relating , relating E E and and CC Create a special identifying attribute for Create a special identifying attribute for EE Add any attributes of Add any attributes of R R to to E E For each relationship (For each relationship (aaii , b , bii , c , cii) in ) in R,R, create create

1. a new entity 1. a new entity eeii in the entity set in the entity set E E 2. add (2. add (eeii , a , ai i ) to ) to RRAA

3. add (3. add (eeii , b , bii ) to ) to RRBB 4. add ( 4. add (eeii , c , ci i ) to ) to RRCC

Converting Non-Binary Converting Non-Binary Relationships (Cont.)Relationships (Cont.)

Also need to translate Also need to translate constraintsconstraints Translating all constraints may not be possibleTranslating all constraints may not be possible There may be instances in the translated schema There may be instances in the translated schema

thatthatcannot correspond to any instance of cannot correspond to any instance of RR• Exercise: add constraints to the relationships RExercise: add constraints to the relationships RAA, R, RBB

and Rand RC C to ensure that a newly created entity to ensure that a newly created entity

corresponds to exactly one entity in each of entity sets corresponds to exactly one entity in each of entity sets A, BA, B and and CC

We can avoid creating an identifying attribute by We can avoid creating an identifying attribute by making E a weak entity set (described shortly) making E a weak entity set (described shortly) identified by the three relationship sets identified by the three relationship sets

Weak Entity SetsWeak Entity Sets An entity set that does not have a primary key is An entity set that does not have a primary key is

referred to as a referred to as a weak entity setweak entity set.. The existence of a weak entity set depends on the The existence of a weak entity set depends on the

existence of a existence of a identifying entityidentifying entity setset it must relate to the identifying entity set via a total, one-to-it must relate to the identifying entity set via a total, one-to-

many relationship set from the identifying to the weak entity many relationship set from the identifying to the weak entity setset

Identifying relationshipIdentifying relationship depicted using a double diamond depicted using a double diamond The The discriminatordiscriminator ( (oror partial keypartial key)) of a weak entity set is of a weak entity set is

the set of attributes that distinguishes among all the the set of attributes that distinguishes among all the entities of a weak entity set.entities of a weak entity set.

The The primary key of a weak entityprimary key of a weak entity set is formed by the set is formed by the primary key of the strong entity set on which the weak primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity entity set is existence dependent, plus the weak entity set’s discriminator.set’s discriminator.

ExampleExample We depict a weak entity set by double rectangles.We depict a weak entity set by double rectangles. We underline the discriminator of a weak entity set We underline the discriminator of a weak entity set

with a dashed line.with a dashed line. payment-numberpayment-number – discriminator of the – discriminator of the payment payment

entity set entity set Primary key for Primary key for payment:payment: ( (loan-number, payment-loan-number, payment-

numbernumber) )

SpecializationSpecialization Top-down design process; we designate Top-down design process; we designate

subgroupings within an entity set that are distinctive subgroupings within an entity set that are distinctive from other entities in the set.from other entities in the set.

These subgroupings become lower-level entity sets These subgroupings become lower-level entity sets that have attributes or participate in relationships that that have attributes or participate in relationships that do not apply to the higher-level entity set.do not apply to the higher-level entity set.

Depicted by a Depicted by a triangletriangle component labeled ISA (E.g. component labeled ISA (E.g. customercustomer “is a” “is a” personperson).).

Attribute inheritanceAttribute inheritance – a lower-level entity set – a lower-level entity set inherits all the attributes and relationship participation inherits all the attributes and relationship participation of the higher-level entity set to which it is linked.of the higher-level entity set to which it is linked.

Specialization ExampleSpecialization Example

GeneralizationGeneralization A A bottom-up designbottom-up design process – combine process – combine

a number of entity sets that share the a number of entity sets that share the same features into a higher-level entity same features into a higher-level entity set.set.

Specialization and generalization are Specialization and generalization are simple inversions of each other; they are simple inversions of each other; they are represented in an E-R diagram in the represented in an E-R diagram in the same way.same way.

The terms specialization and The terms specialization and generalization are used interchangeably.generalization are used interchangeably.

Specialization and Specialization and Generalization (Contd.)Generalization (Contd.)

Can have multiple specializations of an entity set Can have multiple specializations of an entity set based on different features. based on different features.

E.g. E.g. permanent-employee permanent-employee vs. vs. temporary-employeetemporary-employee, in , in addition to addition to officer officer vs. vs. secretary secretary vs. vs. tellerteller

Each particular employee would be Each particular employee would be

a member of one of a member of one of permanent-employee permanent-employee or or temporary-employeetemporary-employee, ,

and also a member of one of and also a member of one of officerofficer, , secretarysecretary, or , or tellerteller

The ISA relationship also referred to as The ISA relationship also referred to as superclass - superclass - subclasssubclass relationshiprelationship

Design Constraints on a Design Constraints on a Specialization/GeneralizationSpecialization/Generalization

Constraint on which entities can be members of a Constraint on which entities can be members of a given lower-level entity set.given lower-level entity set. condition-definedcondition-defined

• E.g. all customers over 65 years are members of E.g. all customers over 65 years are members of senior-senior-citizen citizen entity set; entity set; senior-citizensenior-citizen ISA ISA personperson..

user-defineduser-defined Constraint on whether or not entities may belong to Constraint on whether or not entities may belong to

more than one lower-level entity set within a single more than one lower-level entity set within a single generalization.generalization. disjointdisjoint

• an entity can belong to only one lower-level entity setan entity can belong to only one lower-level entity set• Noted in E-R diagram by writing Noted in E-R diagram by writing disjointdisjoint next to the ISA next to the ISA

triangletriangle overlapping overlapping (default)(default)

• an entity can belong to more than one lower-level entity an entity can belong to more than one lower-level entity setset

Design Constraints on a Design Constraints on a Specialization/Generalization Specialization/Generalization

(Contd.)(Contd.) CompletenessCompleteness constraintconstraint -- specifies whether -- specifies whether

or not an entity in the higher-level entity set or not an entity in the higher-level entity set must belong to at least one of the lower-level must belong to at least one of the lower-level entity sets within a generalization.entity sets within a generalization. totaltotal

• an entity must belong to one of the lower-level an entity must belong to one of the lower-level entity sets. Specified as total participation entity sets. Specified as total participation (double line connecting the higher-level entity to (double line connecting the higher-level entity to the triangle symbol)the triangle symbol)

partialpartial (default)(default)• an entity need not belong to one of the lower-an entity need not belong to one of the lower-

level entity setslevel entity sets

AggregationAggregation Consider the ternary relationship works-on, which we saw earlier

Suppose we want to record managers for tasks performed by an employee at a branch

Aggregation (Cont.)Aggregation (Cont.)

Relationship sets Relationship sets works-on works-on and and managesmanages represent represent overlappingoverlapping information information Every Every managesmanages relationship corresponds to a relationship corresponds to a

works-onworks-on relationship relationship However, some However, some works-onworks-on relationships may not relationships may not

correspond to any correspond to any managesmanages relationships, so relationships, so we can’t discard the we can’t discard the works-onworks-on relationship relationship

Eliminate this redundancy via Eliminate this redundancy via aggregationaggregation Treat relationship as an abstract entityTreat relationship as an abstract entity Allows relationships between relationships Allows relationships between relationships Abstraction of relationship into new entityAbstraction of relationship into new entity

E-R Diagram With AggregationE-R Diagram With AggregationWithout introducing redundancy, the following diagram represents:

• An employee works on a particular job at a particular branch • An employee, branch, job combination may have an associated manager

E-R Design DecisionsE-R Design Decisions The use of an attribute or entity set to represent an The use of an attribute or entity set to represent an

object.object. Whether a real-world concept is best expressed by Whether a real-world concept is best expressed by

an entity set or a relationship set.an entity set or a relationship set. The use of a ternary relationship versus a pair of The use of a ternary relationship versus a pair of

binary relationships.binary relationships. The The use of a strong or weak entity setuse of a strong or weak entity set – strong and – strong and

weak entities can be seen as a single “object” in the weak entities can be seen as a single “object” in the databasedatabase

The The use of specialization/generalizationuse of specialization/generalization – contributes – contributes to modularity in the design.to modularity in the design.

The The use of aggregationuse of aggregation – can treat the aggregate – can treat the aggregate entity set as a single unit without concern for the entity set as a single unit without concern for the details of its internal structure.details of its internal structure.

E-R Diagram for a Banking E-R Diagram for a Banking EnterpriseEnterprise

Summary of Symbols Used in Summary of Symbols Used in E-R NotationE-R Notation

Summary of Symbols (Cont.)Summary of Symbols (Cont.)

End of Chapter 2End of Chapter 2