CMIS 450 Database Design Dr Bijoy Bordoloi Entity

CMIS 450 Database Design Dr. Bijoy Bordoloi Entity Relationship (E-R) Model Bordoloi

E-R Modeling and Logical Database Design · · The Entity Relationship Model The Relational Model Transforming E-R Diagrams into Relations Normalization Bordoloi

E-R Model Constructs • • • Entities Relationships Cardinality Attributes Sub-entities (EER) Examples Bordoloi

Definition of Entity • An entity type is a set of entity instances sharing the same common properties (‘attributes’), characterized by: – Name - a noun, e. g. , EMPLOYEE – Description - defines which instances belong to the entity type E – Identifier - a property (or a joint set of properties) which uniquely identify entity instances • An entity instance is a single identifiable realworld thing, e. g. , Jack Spratt Bordoloi

Entity Types DEPARTMENT SUPPLIER EMPLOYEE PROJECT Bordoloi

Attributes • Any property of an owner entity • Attribute type – A set of attribute instances expressing the same property of an entity type, e. g. , Gender • Attribute instance – A particular property of an individual entity instance, e. g. , Male Bordoloi

Attributes EMPLOYEE EMP-ID Bordoloi SS-NUM EMP- NAME PHONE

Identifier • An attribute (or a joint set of attributes) which uniquely identify the entity instances of an entity • An identifier should be: - Unique and known (must not be ‘NULL’) - Short (preferably) - Stable (preferably) Bordoloi

What would you choose as the Identifier of the entity type EMPLOYEE? EMPLOYEE EMP-ID Bordoloi SS-NUM EMP- NAME PHONE

What Should an Entity Be? • SHOULD BE: – An object that will have many instances in the database – An object that will be composed of multiple attributes – An object that we are trying to model – Must have an identifier • SHOULD NOT BE: – A user of the database system – An output of the database system (e. g. a report) Bordoloi

Figure 3 -4 System user Inappropriate entities System output Appropriate entities Bordoloi

Dependent and Independent Entities • Entity B is existence-dependent on entity A when – Some instance of A must exist before B can exist – If A ceases to exist, B must also cease to exist • An independent entity is not existence-dependent on any other entity • Independent Entity – Strong Entity • Dependent Entity – Weak Entity • Diagramming convention Bordoloi

Dependent and Independent Entities • Typically the identifier of a weak entity is a composite identifier which includes the identifier of the entity which the weak entity is existencedependent upon • Diagramming convention Bordoloi

Dependent and Independent Entities • Examples OFFERING TASK Bordoloi DEPENDS ON COURSE PROJECT

Relationships • An association between two or more entities, of significance to the enterprise • Relationships are named with a verb an depicted as lines between entities SUPPLIER EMPLOYEE Bordoloi SHIP WORK FOR PART COMPANY

Degree • The degree of a relationship is the number of associated entities • Relationships are usually of degree 2 (binary) - but sometimes can be unary or of higher degree (N-ary) Bordoloi

Binary Relationships • The relationship involves at the most only two entities SUPPLIER EMPLOYEE Bordoloi SHIP WORK FOR PART COMPANY

Unary Relationships • Relationships may associate an entity with itself, i. e. , when an instance of an entity is related to some other instance(s) of the same entity. Married_to Bordoloi EMPLOYEE

Ternary Relationship • The relationship involves three entities COMPANY SELL PRODUCT Bordoloi AGENT

Relationships • Several relationships may exist between the same entities • The name and description distinguishes different relationships between the same entities MANAGE EMPLOYEE WORK IN DEPARTMENT FORMERLY WORKED IN Bordoloi

Cardinality Constraints • A binary relationship (E 1, E 2) has two directions, left and right • Each direction has cardinality constraints, described as maximum and mimimu. Bordoloi

Cardinality Constraints • Cardinality Constraints - the number of instances of one entity that can or must be associated with each instance of another entity. • Maximum Cardinality (can be) – The maximum number Minimum Cardinality (must be) – If zero, then optional – If one or more, then mandatory Bordoloi

Maximum Cardinality • The maximum cardinality of a direction E 1 to E 2 indicates how many e 2’s can match a given e 1 (at most) • Cardinality is denoted as Max. Left - Max. Right – – 1 -1 1 -N N-1 M-N Bordoloi

Cardinality EMPLOYEE EACH EMPLOYEE MANY EMPLOYEES MANY Bordoloi WORK-IN DEPARTMENT ONE DEPARTMENT EACH DEPARTMENT ONE

Examples of Cardinality EMPLOYEE DEPARTMENT N E 1 E 2 E 3 E 4 Bordoloi 1 WORKS-IN D 1 D 2 D 3 D 4

Examples of Cardinality 1 MANAGE E 1 E 2 E 3 E 4 M D 1 D 2 D 3 D 4 FORMERLY WORKED-IN E 1 E 2 E 3 E 4 Bordoloi 1 D 2 D 3 D 4 N

Minimum Cardinality • Minimum cardinality is specified as zero to one EMPLOYEE EACH EMPLOYEE ZERO EMPLOYEES ZERO Bordoloi WORK IN DEPARTMENT ONE DEPARTMENT EACH DEPARTMENT ONE

Minimum Cardinality • For the purpose of our course, we will use: One to many (maximum cardinality) Mandatory Optional (minimum cardinality) Instructor Bordoloi teaches

Summary of Cardinality • Minimum and maximum cardinality is specified for each direction of a relationship • Maximum cardinality is either one or many for each direction • Minimum cardinality is either zero or one for each direction Bordoloi

Terminology • A single-valued relationship (from entity A to entity B) has maximum cardinality of one (from A to B) • A multi-valued relationship has maximum cardinality of many • When we speak of single-valued and multivalued relationships, a direction is implicit Bordoloi