Chapter 2Entity-Relationship ModelChapter 12 & 13 in Textbook

*Database DesignSteps in building a database for an application:

1. Understand the real-world domain being captured.2. Specify it using a database conceptual model (E/R,OO).3. Translate specification to model of DBMS (relational).4. Create schema using DBMS commands (DDL).5. Load data (DML).

Real-world domainConceptualmodelDBMS data modelCreate Schema(DDL)Load data(DML)ER Model

*Entity-Relationship Model (E/R)A picture is worth a thousand words

The Entity-Relationship model (ER) is a high-level description of the structure of the DB.

The Entity-Relationship Diagram (ERD) is a graphical model for representing the conceptual model for the data.

A E/R models the DB using three element types:- Entities- Attributes- RelationshipsER Model

ERD Exampleenroll-inSCHOOLDEPARTMENTCOURSECLASSSTUDENTPROFESSORadviseofferoperatechairsteachbelong-tohasdean of1111M1MM11M1MMN1M(0,*)(0,*)(1,1)(1,*)(1,1)(0,1)(0,4)(1,1)(0,1)(1,1)(0,*)(1,1)(0,*)(0,*)(1,1)namenumberDOBhrscode(1,*)(1,1)1(1,*)(1,1)

*Entities & Entity TypeEntity is an object that exists and is distinguishable from other objects.e.g. person, company, course, university

Entity Type is a set of entities of the same type that share the same properties.e.g. set of all persons, companies, trees, coursesCOURSESTUDENTER Model

*Relationships & RelationshipTypesA relationship associates 2 or more entities.

A relationship type is a set of associations between entity types.COURSEstudySTUDENTER Model

*Degree of Relationship TypeDegree of relationship refers to number of participating entity types in a relationship.

A relationship of degree two (2 entity types) are binary.

A relationship of degree three (3 entity types) are ternary.ER Model

*Degree of Relationship Type A relationship of degree two (2 entity types) are binary.

COURSEstudySTUDENTER Model

*Degree of Relationship TypeA relationship of degree three (3 entity types) are ternary.

e.g. registration of a student in a course by a staff.

COURSEregisterSTUDENTSTAFFER Model

*Recursive RelationshipRecursive relationship is a relationship type where the same entity type participates more than once in a different role. It is a unary relationship.

COURSErequireER Model

*RolesRole indicates the purpose that each participating entity type plays in a relationship. (e.g. prerequisite, requester) COURSErequirerequesterprerequisiteER Model

*RolesRole can be used when two entities are associated through more than one relationship to classify the purpose of each relationship. STAFFallocatedManagerStaff memberBRANCHBranch officeBranch officemanagesER Model

*Attributes Attributes are descriptive properties for an entity type or a relationship type.

All entities in one entity type have the same attributes.

STUDENTSt_nonameDOBTel_noER Model

*Attributes of EntitiesCOURSEstudySTUDENTSt_nonameDOBTel_nonumbernamehoursER Model

*Attributes of RelationshipsAll relationships in one relationship type have the same attributes.

Relationships can be distinguished not only by their attributes but also by their participating entities.contractStartEndObjectER ModelCompanyEmployee

*Simple & Composite AttributesSimple attribute is an attribute that have a single value with an independent existence.

e.g. salary, age, gender,...

ER Model

*Simple & Composite AttributesComposite attribute is an attribute composed of multiple distinct components, each with an independent existence.

e.g. address (street, area, city, post code) name (First name, initial, Last name) phone no. (area code, number, exchange no)STUDENTSt_nonameDOBTel_noLNameinitialFNameArea_cdnoEXER Model

*Single-valued & Multi-valued AttributesSingle-valued attribute is an attribute that holds a single value for a single entity. It is not necessarily a simple attribute.

e.g. student_no, age, gender,...

ER Model

*Single-valued & Multi-valued AttributesMulti-valued attribute is an attribute that may hold multiple values, of the same type, for a single entity.e.g. tel_no, degrees,STUDENTSt_nonameDOBTel_noLNameinitialFNameArea_cdnoEXER Model

*Derived AttributesDerived attribute is an attribute that represents a value that is derived from the value of a related attribute,not necessarily in the same entity type.

e.g. age is derived from date_of_birthtotal_cost is derived from quantity*unit_priceSTUDENTSt_nonameDOBTel_noageER Model

*KeysCandidate key (CK) is the minimal set of attributes that uniquely identifies an entity. It cannot contain null.e.g. student_no, social_security_no, branch_no

Primary Key (PK) is a candidate key that is selected to uniquely identify each entity.

Alternate Key (AK) is a candidate key that is NOT selected to be the primary key.

ER Model

*Keys ExampleELEMENT(symbol, name, atomic_no)

ER Model

*Keys ExampleELEMENT(symbol, name, atomic_no)

Primary KeyAlternate KeysCandidate KeyER Model

*Choice of PKChoice of Primary Key (PK) is based on:

Attribute length Number of attributes required Certainty of uniquenessER Model

*Primary Key in ERDageSTUDENTSt_nonameDOBTel_noLNameinitialFNameArea_cdnoEXER Model

*KeysA key can be:

- simple key is a candidate key of one attribute.

e.g. student_no, branch_no

- composite key is a candidate key that consists of two or more attributes. e.g. STUDENT (Lname, Fname, Init) CLASS (crs_code, section_no) ADVERT (property_no, newspaperName, dateAdvert)

ER Model

*Composite Key in ERDCLASScrs_codeSection_nonamehoursER Model

*Strong & Weak Entity TypesA strong entity type is NOT existence-dependent on some other entity type. It has a PK.

A weak entity type is an entity type that is existence-dependent on some other entity type. It does not have a PK.

ER Model

*Weak Entity TypeThe existence of a weak entity type depends on the existence of a strong entity set; it must relate to the strong entity type via a relationship type called identifying relationship.

The PK of a weak entity set is formed by the PK of its strong entity type, plus a weak entity type discriminator attribute.

EMPLOYEEemp_noLNameFNameDOBdep_noFNameDEPENDENThasER Model

*CardinalitiesCardinality ratio expresses the number of relationships an entity can participate in.

Most useful in describing binary relationship types.

For a binary relationship type the mapping cardinality must be one of the following types:

One to one (1:1) One to many(1:M) Many to one (M:1) Many to many (M:N)

ER Model

*One-To-One RelationshipA professor chairs at most one department; and a department is chaired by only one professor.DEPARTMENTchairsPROFESSOR11PROFESSOR chair DEPARTMENT P1 r1 D002 P2 P3 r2 D001

ER Model

*One-To-Many RelationshipA course is taught by at most one professor; a professor teaches many courses.COURSEteachPROFESSOR1MPROFESSOR teach COURSE P1r1 C01 r2C02 P2r3C03 P3 C04

ER Model

*Many-To-One RelationshipA class requires one room; while a room can be scheduled for many classes.ROOMrequireCLASSM1CLASS require ROOM C1 r1 R001C2 r2 R002 C3 r3 R003 R004

ER Model

*Many-To-Many RelationshipA class enrolls many students; and each student is enrolled in many classes.STUDENTenrollCLASSMNCLASS enroll STUDENT C1 r1 S1 C2 r2 S3 C3 r3 S4 r5 S5

ER Model

*MultiplicityMultiplicity is the number (range) of possible entities that may relate to a single association through a particular relationship.

It is best determined using sample data.

Takes the form (min#, max#)ER Model

*MultiplicityBRANCHmanageSTAFF11(0,1)(1,1)STAFF manageBRANCH SG1 r1 B002 SG2 SG3 r2 B001

ER Model

*MultiplicityPROPERTYoverseeSTAFF1M(0,10) (0,*)(0,1)STAFF overseePROPERTY SG1 r1 P1 r2 P2 SG2 r3 P14 SG3 P6

ER Model

*MultiplicityPROPERTYadvertiseNEWSPAPERMN(0,*)(0,*)Newspaper advertisePROPERTYAl-Riyadh r1 P1 r2 P13 Al-Bilad r3Al-Madinah r4 P6Al-Sharq P4

ER Model

*Participation ConstraintsParticipation constraints determine whether all or only some entities participate in a relationship.

Two types of participation:

- Mandatory (total)- Optional (partial)ER Model

*Participation ConstraintsMandatory (total) (1:*): if an entitys existence requires the existence of an associated entity in a particular relationship (existence-dependent).

e.g. CLASS taught-by PROFESSOR i.e. CLASS is a total participator in the relation.

A weak entity always has a mandatory participation constraints but the opposite not always true.

ER Model

*Participation ConstraintsOptional (partial) (0:*): if an entitys existence does not require a corresponding entity in a particular relationship. (Not existence-dependent). e.g. PROFESSOR teach CLASS i.e. PROFESSOR is a partial participator in the relation.ER Model

ER Case StudyEach bank has a unique name.

Each branch has a number, name, address (number, street, city), and set of phones.

Customer includes their name, set of address (P.O. Box, city, zip code, country), set of phones, and social security number.

Accounts have numbers, types (e.g. saving, checking) and balance. Other branches might use the same designation for accounts. So to name an account uniquely, we need to give both the branch number to which this account belongs to and the account number.

Not all bank customers must own accounts and a customer may have at most 5 accounts in the bank.

An account must have only one customer.A customer may have many accounts in different branches.Class exercise: Banks Database

ER Case StudyHomework: Television Series DatabaseA Television network wishes to create a database to keep track of its TV series. A television series has one or more episode. Television series identified by name and season number, and includes their production company name and Num_of_Episodes ( i.e. total number of episodes in a specific season of a series ).

Episode of a specific season of a series is identified by episode number and has a title and a length. No episode can exist without a corresponding television series. Also each episode has only one writer. A writer is identified by name, and also has birth date and a literary agency that represents him or her.

An actor appears as a performer in a television series or a guest star on an episode. An actor is identified by name and also has a nationality and birth date. An actor plays a particular character in a television series or episode.

*Problems with ER ModelConnection TrapsConnection traps are problems that occur due to misinterpretation of the meaning of certain relationships.

Types of connection traps:Fan TrapChasm TrapER Model

*Fan TraphasoperateDIVISIONSTAFFBRANCH1M1MSTAFF has DIVISION operate BRANCH SG1 r1 D1 r4 B002SG3 r2 r5 B003 SG2 r3 D2 r6 B004

ER Model

*Fan TrapoperatehasBRANCHDIVISIONSTAFFMM11DIVISION operate BRANCH has STAFF D1 r1 B002 r4 SG1 r2 B003 r5 SG3 D2 r3 B004 r6 SG2

ER Model

*Fan TrapFan trap where a model represents a relationship between entity types, but the pathway between certain entity occurrence is ambiguous.

Exists where 2 or more 1:M relationships fan out from the same entity.ER Model

*Chasm TraphasoverseeSTAFFBRANCHPROPERTYMM11BRANCH has STAFF oversee PROPERTY B001 r1 SG1 r4 P1B003 r2 SG2 P14 B002 r3 SG3 r6 P5

(1,*)(1,1)(0,*)(0,1)ER Model

*Chasm TraphasoverseeSTAFFBRANCHPROPERTYMM11BRANCH has STAFF oversee PROPERTY B001 r1 SG1 r4 P1B003 r2 SG2 P14 B002 r3 SG3 r5 P5

offer r6 r7 r8

offer1M(0,*)(0,1)(1,1)(1,*)ER Model

*Chasm TrapChasm trap where a model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entities.

Exists when partial participating entity is part of the pathway between related entities.ER Model

*What makes a good DB design?Faithfulness. Entity types, attributes & relationship types should reflect reality.

BRANCHHourly-rateEMPLOYEEmanagers-inBRANCH11(a)(b)ER Model

*What makes a good DB design?Avoid Redundancy. Say everything only once.

Biz-namePERSONownBUSINESSMNnameER Model

*What makes a good DB design?Simplicity. Avoid introducing more elements into your design than is absolutely necessary.

PERSONrepresentOWNER11ownBUSINESS1MExample 1ER Model

*What makes a good DB design?PERSONownrunBUSINESSExample 2ER Model

*What makes a good DB design?EMPLOYEEworkCOMPANYM1ownPERSONM1Example 3contract11ER Model

*What makes a good DB design?Pick the right kind of element.

STUDENTallocateDEPARTMENTM1STUDENTdegreesdpt_namedegreesdpt_nameExample 1ER Model

*What makes a good DB design?BUSINESScontractSUPPLIER1MLAWYER1Example 2CONTRACTSup-makeBiz-makeLaw-makeBUSINESSLAWYERSUPPLIER1MM1M1(business, lawyer, supplier)Biz-make (business, contract)Sup-make (supplier, contract)Law-make (lawyer, contract)(a)(b)

*Enhanced Entity-Relationship Model (EER)EER is an ER model supported with additional semantic concepts.

Semantic concepts supported:- Specialization- Generalization- AggregationER Model

*SpecializationTop-down design process; we designate sub-groupings within an entity type that are distinctive from other entities in the set.

These sub-groupings (subclasses) become lower-level entity types that have attributes or participate in relationships that do not apply to the higher-level entity set (superclass).ER Model

*Specialization/GeneralizationSTAFFMANAGERSECRETARYSALESPERSONNELSubclassSuperclassIS_A relationship 1:1ER Model

*GeneralizationA bottom-up design process combine a number of entity types that share the same features into a higher-level (superclass) entity type.

Specialization and generalization are simple inversions of each other; they are represented in an EER diagram in the same way.ER Model

*InheritanceA subclass entity type inherits all the attributes and relationship participation of the superclass entity type to which it is linked.ER Model

*InheritanceSTAFFSALESPERSONNELUnsharedattributesSuperclassaddressDOBnameSalesareaCarallowancesharedattributesSubclassrequireCARUnsharedrelationshipcontractCOMPANYSharedrelationshipER Model

*Constraints on Specialization/GeneralizationParticipation constraint determines whether every member in the superclass must participate as a member of a subclass.

Two types of participation constraints:- Mandatory (total)- Optional (partial)

ER Model

*Participation ConstraintsMandatory (total) participation where every member in the superclass must also be a member of a subclass.

STAFFFULL-TIMESTAFFPART-TIMESTAFFsalaryHourly-rateER Model

*Participation ConstraintsOptional (partial) participation where a member in the superclass need not belong to any of its subclasses.

STAFFMANAGERSECRETARYSALESPERSONNELER Model

*Constraints on Specialization/GeneralizationDisjoint constraint describes the relationship between members of the subclasses & indicates whether it is possible for a member of a subclass to be a member of one or more subclasses.

Two types of disjoint constraints:- Disjoint- Non-DisjointER Model

*Disjoint ConstraintsDisjoint constraint when an entity can be a member of only one of the subclasses of the specialization.STAFFFULL-TIMESTAFFPART-TIMESTAFFsalaryHourly-ratedER Model

*Disjoint ConstraintsNon-disjoint constraints: an entity is a member of more than one subclass of specialization. Entity types may overlap.

STAFFMANAGERSECRETARYSALESPERSONNELoER Model

*Aggregation Represents a part-of relationship between entity types, where one represents the whole and the other the part.

No inherited attributes; each entity has its own unique set of attributes.ER Model

*Aggregation

TEAMMEMBERER Model

*Summary of ERD notations (1)ENTITYWEAK ENTITYRELATIONSHIPIDENTIFYING RELATIONSHIPATTRIBUTEKEY ATTRIBUTEMULTI-VALUEDCOMPOSITEDERIVEDER Model

*Summary of ERD notations (2)CARDINALITYRATIONPARTICIPATIONCONSTRAINTS1M(min,max)ER Model

*Summary of EERD notations (3)Disjoint constraintdoNon-Disjoint constraintTotal ParticipationOptional ParticipationER Model

***