The Entity Relationship Model Part II CS 3431

The Entity. Relationship Model Part II. CS 3431 1

Database Design Stages Application Requirements Conceptual Design Conceptual Schema Logical Design Logical Schema Physical Design Physical Schema CS 3431 2

Relationship types in ER Model 3 -ary “supplies” relationship: Supplier supplies Products to Consumers CS 3431 3

Recursive Relationship Types and Roles Recursive relationship type : Part-Subpart Roles: There are Parts that play the role of super. Part There are Parts that play the role of sub. Part CS 3431 4

ER Model so far l Structures l l Entity Types Relationship Types l l l Attributes l l l Binary, ternary, n-ary Recursive For entity types and relationship types Simple, composite, multivalued Roles CS 3431 5

ER Model: Key Constraints How : Underline the key attribute/attributes Key for Student is s. Number Key for Movie is <title, year> Note: We can represent key for entity type consisting of more than 1 attribute (e. g. : Movie) We cannot represent multiple keys for an entity type (e. g: key for Student can be either s. Number or s. Name) CS 3431 6

ER Model: Cardinality Constraints How : Expressed using (min, max) Student can take >= 2 and <= 3 Courses Course can have >= 0 and <= * (infinity) Students min and max are non-negative integers max > min CS 3431 7

Cardinality Constraints 1: 1 relationship type: A Dept has exactly one Manager, A Person can manage at most one Dept p. Number d. Number Person Manages p. Name Dept d. Name CS 3431 8

Cardinality Constraints 1: 1 relationship type: A Dept has exactly one Manager, A Person can manage at most one Dept CS 3431 9

Cardinality Constraints 1: many relationship type: A Person works for exactly one Dept, A Dept can have any number of Persons p. Number d. Number Person Works For p. Name Dept d. Name CS 3431 11

Cardinality Constraints 1: many relationship type: A Person works for exactly one Dept, A Dept can have any number of Persons CS 3431 12

Cardinality Constraints 1: 1 relationship type: A Dept has exactly one Manager, A Person can manage at most one Dept p. Number d. Number Person (0, 1) Manages (1, 1) Dept p. Name d. Name 1: many (1: n) relationship type: A Person works for exactly one Dept, A Dept can have any number of Persons p. Number d. Number (1, 1) Person Works For (0, *) p. Name Dept d. Name CS 3431 13

Cardinality Constraints p. Number d. Number Person Works For p. Name Dept d. Name many: many (m: n) relationship type: A Person works for one or more Depts, A Dept can have any number of Persons CS 3431 15

Cardinality Constraints many: many (m: n) relationship type: A Person works for one or more Depts, A Dept can have any number of Persons CS 3431 16

Cardinality Constraints for n-ary relationships p. Number p. Name Product s. Name c. Name Supply Supplier Consumer s. Loc c. Loc price A Supplier supplies at least one qty Product to some Consumer CS 3431 17

Cardinality Constraints for n-ary relationships A Supplier supplies at least one Product to some Consumer CS 3431 18

Cardinality Constraints for n-ary relationships What about the following constraints : (1) A Consumer gets a Product from only one Supplier (2) Each Supplier supplies exactly 2 Products CS 3431 19

Cardinality Constraints for Recursive Relationships p. Number p. Name Part super. Part sub. Part Contains quantity A Part can be subpart of one super. Part A Part can have many sub. Parts CS 3431 20

Cardinality Constraints for Recursive Relationships A Part can be subpart of one super. Part A Part can have many sub. Parts CS 3431 21

Cardinality Constraints for Recursive Relationships A Part can be subpart of one super. Part A Part can have many sub. Parts A Part can be subpart of many super. Parts A Part can have many sub. Parts CS 3431 22

ER Model Constraints Summary l Key Constraints l Cardinality Constraints l l Expressed using (min, max) Binary relationship types are called 1: 1, 1: many, many: many CS 3431 24

Summary of ER l Structures l l l l Entity Types Relationship types – binary, ternary, n-ary. recursive Attributes l For entity types or relationship types l Simple, composite or multi-valued Constraints – key, cardinality Roles of entity types in a relationship type ISA relationship types Weak Entity Types – identifying relationship type, identifying entity type CS 3431 25