The Entity-Relationship (ER) Model The best known tools for - PDF document

The Entity-Relationship (ER) Model • The best known tools for database design. It aids • One of the main reasons for the popularity design by helping to analyse the structure of the of the e-r technique is its graphic data, the inter-relationships between the representation. components, and, in general the semantics of the data. • Introduced in 1976 by Chen and extended later. • Based on the view that the real world consists of a set of basic objects called entities and relationships amongst them. Department Supplier Name Employee Project Balance Acc No. Tel No. Part Dependent Cust. Acc Customer Account DepName DepName Dep# Dep# Department Department Supplier Supplier Emp# Emp# EName EName Employee Project Employee Project F M L F M L Part Part Salary Salary Dependent Dependent DName DName

DepName Dep# DepName Dep# Department Supplier Department Supplier 1 N 1 Emp# N Emp# N Qty EName M N M N Employee Project EName M M N Employee Project 1 F M L 1 N F M L N Part N Salary Part N Dependent Salary Dependent component include M N M N DName DName Entities DepName Dep# Department Supplier 1 Objects that have to be represented in the database N Emp# distinguishable from each other. Qty N EName M M N Employee Project Examples: 1 F M L N Concrete entities: Person, Student, Mature student, Part Computers. N Salary Dependent Abstract entities: Holiday, Job satisfaction. component include Note: Entity types vs. Entity instances M N DName e.g. Person vs. John Smith Attributes (Properties) Relationships Associations among entities. Properties or features of entities which we are interested in representing. Each entity has a Relationships may also have attributes. set of attributes. Note: Relationship types vs. Relationship instances Some properties may be designated as primary key. e.g. CustAcc vs. John Smith having account 567110.

Mapping Constraints • An e-r scheme may define certain constraints to • The entities involved in a given relationship are the participants in that relationship. which the contents of a db must conform. • One important constraint is mapping • The number of participants in a relationship cardinalities which specify the number of entity is called the degree of that relationship. instances to which an instance of another entity can be associated via a relationship. Mapping Cardinalities For binary relationships: One-to-One (1:1): An entity instance in A is One-to-Many (1:N): An entity instance in A associated with at most one entity instance is associated with any number of entity in B, and vice versa. instances in B. An entity instance in B is associated with at most one in A. a1 b1 a1 a2 b2 b1 a2 a3 b3 b2 a3 a4 b4 b3 a4 b4 Many-to-One: An entity instance in A Many-to-Many (M:N): An entity instance in associated with at most one in B. An entity A is associated with any number in B, and instance in B associated with any number of vice versa. entities in A. a1 b1 a1 a2 b2 a2 b1 a3 b3 a3 b2 a4 b4 a4 b3 a5 b4

Variations in Notational Conventions • Many-to-one relationships • Many-to-many relationships R R E1 E2 E1 E2 • One-to-One Relationships • Leaving out the Relationship 1 N R E1 E2 E1 E2 Existence Constraints Example Every employee is a member of at least one R E2 E1 department. EMP For every instance of entity E1 there is at least Department DEP Employee an instance of entity E2, related to it by R.

Every employee is a member of exactly one Operationally, the existence constraint means department. that whenever a department is deleted, then the employees working in it should either be deleted or be given new departments. N 1 EMP Department DEP Employee Roles Example The function that an entity plays in a relationship is called its role. Roles are normally implicit and are not usually 1 manager specified. They are useful, however, when works for Employee the meaning of a relationship needs N worker clarification, e.g. in a binary relation whose participants are the same entity. Type hierarchies • Weak and Strong Entities Also called: • Attributes versus Relationship+entity • Isa hierarchies • Subtypes • Specialisation hierarchies • Generalisation hierarchies

E# Employee Employee Salary Name isa isa Language Programmer Programmer isa isa isa isa Systems Systems Application Application Programmer Programmer Programmer Programmer Op System Domain E# 1 Pension N Employee Scheme Salary Name • An entity type E1 is a subtype of an entity type isa Type Provider E2 if every instance of E1 is also an instance of Language E2. Programmer • Isa: Special 1:1 relationship representing the isa isa relationship between a subtype and its parent type. Systems Application Programmer Programmer • Subtypes inherit the properties and relationships of Op System Domain their parent type but not vice versa. Converting Multiway Translating ER Models to Relationships to Binary Ones Relational Models It is possible to convert any relationship that 1. Entities Base Relations connects more than two entities to a collection of 2. Properties Attributes binary, many-to-one relationships without losing 3. M:N Relationships Base Relations any information. This is done by introducing a 4. I:N Relationships Extra Attribute new entity (called the connecting entity), and 5. I:1 Relationships Extra Attribute connecting this entity by many-to-one relationships to each of the entities that participated in the original multiway relationship. For 3,4,5 decide foreign keys and foreign key rules.

Entities Base Relations DepName Dep# Department Supplier Dept ( ) 1 N Emp# Emp ( ) Qty N Project ( ) EName M M N Employee Project 1 Supplier ( ) F M L N Part ( ) Part N Salary Dependent Dependent( ) component include M N DName Entities Base Relations Properties Attributes Properties Attributes Dept ( Dep # # # # , DepName ) Emp ( Emp # # # #, , , , F, M, L, Salary ) Dept ( Dep # # , DepName # # ) Project ( ) Emp ( Emp # , F, M, L, NatIns ) #, # # , , Supplier ( ) Project ( Proj # # , ProjName ) # # Part ( ) Supplier ( S # # # # , SName, Status, City ) Dependent( DName, Emp # # # # ) Part ( Part # # # # , PartName Dependent ( DName, Emp # ) # # # M:N Relationships M:N Relationships Base Relations Base Relations Emp-Proj ( ) Emp-Proj ( Emp # # , Proj # # ) # # # # SPP ( ) SPP ( S # # # # , Proj # # , Part # # # # , Qty) # # Structure ( ) Structure ( Part # # , Subpart # ) # # # # #

M:N Relationships Base Relations Designate Foreign keys Emp-Proj ( Emp # # # , Proj # # # # # ) Emp-Proj ( Emp # # , Proj # # # # # # ) EMP# and Proj# FKs SPP ( S # # , Proj # # # # # , Part # # # , Qty) # # Emp ( Emp # #, # # , , , F, M, L, NatIns ) S # # , Proj # # , Part # # FKs # # # # # # Structure ( Part # # , Subpart # # # # # # ) Project ( Proj # # # # , ProjName ) Part # # # # , Subpart # # # # FKs FK In Referring Nulls FK Rules Relation To Allowed R1 means Emp# Emp-Proj Emp NO - PK R1 Emp-Proj NO - PK R1 Proj# Project S# SPP NO - PK R1 Supplier Updates cascade & Proj# SPP NO - PK R1 Project Part# Deletions delete SPP Part NO - PK R1 NO - PK Part# Structure Part R1 NO - PK Subpart# Structure Part R1 I:N Relationships Extra Attribute Emp ( Emp # # #, # , , F, M, L, Salary, Dep # , # # # ) Emp ( Emp # , F, M, L, Salary, Dep # ) # #, # , , # # # Dep# FK Dept ( Dep # # , DepName) # # Dependent ( DName, Emp # # # # ) EMP# FK Can Dep# be NULL in EMP? What would be sensible FK rules?

Finally, putting it all together Dept ( Dep # # , DepName ) # # FK In Referring NULLS FK Rules Emp ( Emp # #, # # , , , F, M, L, Salary, Dep # # # # ) Relation To Allowed Project ( Proj # # # # , ProjName ) ? Supplier ( S # # , SName, Status, City) # # No- Dep# EMP Dept Updates existance Part ( Part # # , PartName ) # # cascade, constraint deletions Dependent ( DName, Emp # # # ) # rejected Emp-Proj ( Emp # # # # , Proj # # # # ) SPP ( S # # , Proj # # , Part # # , Qty) Dependent No- # # # # # # Emp# Emp Updates existance cascade, Structure ( Part # # # , Subpart # # # # # ) constraint deletions and PK delete Subtypes/Supertypes Translation Base relations for each entity type. By giving the subtype the same primary key as its parent we get the effect of inheritance Suppose base relation for Employee is down the hierarchy. Employee(Emp#, Dept#, Salary, . . . ) Then base relation for Programmer can be Programmer(Emp#, Language, . . . ) where Emp# is a foreign key referencing Employee.