Fundamentals of Database Systems Chapter 3 Data Modeling

Fundamentals of Database Systems Chapter 3 Data Modeling Using Entity-Relationship Model Database 1

Database Design Process q Two main activities: · Database design · Applications design q Focus in this chapter on database design · To design the conceptual schema for a database application q Applications design focuses on the programs and interfaces that access the database · Generally considered part of software engineering Database 2

Database Design Process (cont. ) q Requirements collection and analysis · The database designers interview prospective database users · The result is data requirement q Functional analysis · Specify the known functional requirements of the application · Consist of user-defined operations, such as retrievals and updates Database 3

Database Design Process (cont. ) q Conceptual design · Specify concisely the data requirement of the users, such as entity types, relationships, and constraints · Use high-level conceptual data model, e. g. , Entity. Relationship model · Used as a reference to communicate with nontechnical users · Modifications to the conceptual schema can be introduced when some functional requirements cannot be specified Database 4

Database Design Process (cont. ) q Logical design (data model mapping) · Refer to the actual implementation of the database using a commercial DBMS · Need to transform the conceptual data model into the implementation data model, e. g. , relational model q Physical design · Specify the internal storage structures, access paths, and file organizations Database 5

Database 6

Example Database q An COMPANY database · The company is organized into departments · Each department controls several projects · Need to keep track all employee and their working hours · Need to keep track of the dependents of each employee Database 7

Example Database (cont. ) Database 8

ER Model Concepts q Entities and Attributes · Entities are specific objects or things in the mini-world, e. g. , EMPLOYEE John Smith · Attributes are properties used to describe an entity, e. g. , Name, SSN, Address, Sex, Birth. Date for EMPLOYEE · A specific entity will have a value for each of its attributes, e. g, Name = ‘John Smith’ Database 9

ER Model Concepts (cont. ) · Two example entities Database 10

ER Model Concepts (cont. ) q Types of attributes · Simple vs Composite » Each entity has a single atomic value for the attribute, e. g. , SSN » Composite attributes may be composed of several components, e. g. , address, and may form a hierarchy · Single-valued vs Multi-valued » Most attributes are single-valued, but some attributes may have multiple values, e. g. , Expert Database 11

ER Model Concepts (cont. ) · An example hierarchy of composite attributes Database 12

ER Model Concepts (cont. ) · Stored vs Derived » In some case, two (or more) attributes are related » Age (derived attribute) can be derived from the Birth. Date attribute (stored attribute) · Complex » In general, composite and multi-valued attributes may be nested arbitrarily » e. g. , Address. Phone for a person Database 13

ER Model Concepts (cont. ) q Entity Types and Key Attributes · Entities with the same basic attributes are grouped or typed into an entity type, e. g, EMPLOYEE type · Key attribute » An attribute for which each entity must have a unique value, e. g, SSN for EMPLOYEE » A key attribute may be composite » An entity type may have more than one key attributes, e. g, Vehicle. Id, Registration Database 14

ER Model Concepts (cont. ) Database 15

ER Model Concepts (cont. ) Database 16

Displaying an Entity type q In ER diagrams, an entity type is displayed in a rectangular box q Attributes are displayed in ovals · Each attribute is connected to its entity type · Components of a composite attribute are connected to the oval representing the composite attribute · Each key attribute is underlined · Multivalued attributes displayed in double ovals Database 17

Displaying an Entity type (cont. ) q Example diagram for CAR entity type Database 18

Entity Set q Each entity type will have a collection of entities stored in the database · Called the entity set · e. g, entity set for CAR q Same name (CAR) used to refer to both the entity type and the entity set q Entity set is the current state of the entities of that type that are stored in the database Database 19

ER Model Concepts – Relationships q Relationships and Relationship Types · A relationship relates two or more distinct entities with a specific meaning » e. g, EMPLOYEE John Smith works on Product. X PROJECT · Relationships of the same type are grouped or typed into a relationship type Database 20

ER Model Concepts – Relationships (cont. ) Database 21

ER Model Concepts – Relationships (cont. ) · Degree of a relationship type: the number of participating entity types » e. g. , WORKS_FOR is a binary relationship, SUPPLY is a ternary relationship · A relationship type can be represented as attributes » e. g. , WORKS_FOR relationship => Department of EMPLOYEE or Employees of DEPARTMENT Database 22

ER Model Concepts – Relationships (cont. ) Database 23

ER Model Concepts – Relationships (cont. ) · Recursive relationships: A relationship relate two entities of the same entity type (participates in different roles) » e. g. , SUPERVISION: relates one EMPLOYEE (in the role of supervisee) to another EMPLOYEE (in the role of supervisor) · A relationship type can have attributes » e. g. , Hours. Per. Week of WORKS_ON Database 24

ER Model Concepts – Relationships (cont. ) Database 25

ER Model Concepts – Relationships (cont. ) q Structural constraints on relationships · Cardinality ratio (of a binary relationship) » Specify the number of relationship instances that an entity can participate in » 1: 1, 1: N, N: 1, or M: N (e. g. , WORKS_ON) · Participation constraint (on each participating entity type) » Specifies whether the existence of an entity depends on another entity » Two different types – Total (called existence dependency), e. g. , EMPLOYEE in WORKS_FOR – Partial, e. g. , EMPLOYEE in MANAGES Database 26

ER Model Concepts – Relationships (cont. ) Database 27

ER Model Concepts – Relationships (cont. ) Database 28

ER Model Concepts (cont. ) q Weak entity types · Entity types that does not have a key attribute · Entity types that have a key attribute are called regular (or strong) entity types · A weak entity type must participate in an identifying relationship type with an owner (identifying entity type) · A weak entity type always has a total participation constraint Database 29

ER Model Concepts (cont. ) · Example » Suppose that a DEPENDENT entity is identified by – the dependent's first name and birthdate – the specific EMPLOYEE the dependent is related to » DEPENDENT is a weak entity type – EMPLOYEE as its identifying entity type – via the identifying relationship type DEPENDENT_OF Database 30

Notation Summary for ER Diagrams Database 31

Notation Summary for ER Diagrams (cont. ) Database 32

Notation Summary for ER Diagrams (cont. ) Database 33

Alternative Notation for ER Diagrams q Alternative notation for relationship structural constraints · Associate (min, max) with each participation of an entity type E in a relationship type R » Each entity e in E participates in at least (min) and at most (max) relationship instances in R » Default (no constraint): min = 0, max = n » Must have 0 < min < max, max > 1 » min = 0 implies partial, min > 0 implies total Database 34

Alternative Notation for ER Diagrams (cont. ) Read the min, max numbers next to the entity type and looking away from the entity type Database 35

Notation for ER Diagrams (cont. ) Database 36

ER Conceptual Design q Design Paradigm · Initial design » Identify all entity types and their attributes · Iterative refinement » Refine the attribute that are reference to another entity into a relationship » An attribute in several entity types may be refined into its own entity type – e. g. , Department in STUDENT, INSTRUCTOR, and COURSE » An inverse refinement to the previous case may be applied Database 37

ER Conceptual Design (cont. ) q Example: An company database · Initial design Database 38

Database 39

ER Conceptual Design (cont. ) · Refinement We specify the following relationship types » MANAGES, 1: 1 (EMPLOYEE: DEPARTMENT) » WORKS_FOR, 1: N (DEPARTMENT: EMPLOYEE) » CONTROLS, 1: N (DEPARTMENT: PROJECT) » SUPERVISION, 1: N (EMPLOYEE: EMPLOYEE) » WORKS_ON, M: N (EMPLOYEE: PROJECT) » DEPENDENTS_OF, 1: N (EMPLOYEE: DEPENDENT) Database 40

ER Conceptual Design (cont. ) q In the refined design, some attributes from the initial entity types are refined into relationships: · Manager of DEPARTMENT -> MANAGES · Works_on of EMPLOYEE -> WORKS_ON · Department of EMPLOYEE -> WORKS_FOR q In general, more than one relationship type can exist between the same participating entity types · relationship types MANAGES and WORKS_FOR between EMPLOYEE and DEPARTMENT Database 41

Higher Degree Relationship q Binary relationship vs ternary (or n-ary) relationship · A ternary relationship type three binary relationship types · cp. Fig. 4. 13, (s, p), (j, p), (s, j) > (s, j, p) · In general, a ternary relationship type represents more information than do three binary relationship types » e. g. , quantity, supply date Database 42

Higher Degree Relationship (cont. ) Database 43

Higher Degree Relationship (cont. ) · In case that only binary relationships are permitted, a ternary relationship must be » represented as a weak entity type with no partial key, and » with three identifying relationships · It is often tricky to decide when to use binary or n-degree relationship representation Database 44

Higher Degree Relationship (cont. ) Database 45

Higher Degree Relationship (cont. ) » (i, c, s) => (i, c), (i, s), (c, s) » (i, c), (i, s), (c, s) > (i, c, s) Database 46

Higher Degree Relationship (cont. ) q Constraints on ternary (or n-ary) relationships · Cardinality ratio · Participation constraint » Partial » Total · (min, max) notation Database 47

Chapter Summary q ER Model Concepts: Entities, attributes, relationships q Constraints in the ER model q Using ER in step-by-step conceptual schema design for the COMPANY database q ER Diagrams - Notation q Alternative Notations Database 48