Review of Week 1 n n n Database

Review of Week 1 n n n * Database DBMS File systems vs. database systems Evolution of data models Basic data modeling components 1

Module 2 Database Design Concepts

Chapter 4 Entity Relationship (ER) Modeling Module 2: Database Design Concepts

Introduction to the E-R Model n n n * Data modeling is about documenting rules and policies that govern data. Entity relationship data model is a detailed logical representation of the entities, associations and data elements for an organization or business arena. Entity-relationship diagram is a graphical representation of an E-R model. 4

Business Rules n n n * Statements that define or constrain some aspect of the business Assert business structure Control/influence business behavior Expressed in terms familiar to end users Automated through DBMS software 5

Entity Relationship (ER) Model n n * ERD represents the conceptual database as viewed by end user ERDs depict the ER model’s three main components: n Entities n Attributes n Relationships 6

Entity (Entity Type) n n * Refers to the entity set and not to a single entity occurrence Is a collection of entities that share common properties or characteristics Corresponds to a table and not to a row in the relational environment Entity name, a noun, is usually written in capital letters 7

What Should an Entity Be? n Should be: n n Should not be: n n * 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 A user of the database system An output of the database system (e. g. A report) 8

Attributes n n Characteristics of entities Classifications of attributes: n n * Simple versus composite attribute Single-valued versus multi-valued attribute Stored versus derived attributes Identifier attributes 9

The Attributes of the STUDENT Entity * 10

Domains n Attributes have a domain: n n * The attribute’s set of possible values Attributes may share a domain 11

Primary Keys (Identifiers) n n n * Identifier (key) - an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type Candidate key – an attribute that could be a key…satisfies the requirements for being a key Underlined in the ER diagram 12

Characteristics of Identifiers n n * Will not change in value Will not be null No intelligent identifiers (e. g. containing locations or people that might change) Substitute new, simple keys for long, composite keys 13

The CLASS Table (Entity) Components and Contents * 14

A Multivalued Attribute in an Entity * 15

Resolving Multivalued Attribute Problems n Although the conceptual model can handle multivalued attributes, you should not implement them in the relational DBMS n n * Within original entity, create several new attributes, one for each of the original multivalued attribute’s components Create a new entity composed of original multivalued attribute’s components 16

Splitting the Multivalued Attribute into New Attributes * 17

A New Entity for a Multivalued Attribute’s Components * 18

Derived Attributes n n n * Attribute whose value may be calculated (derived) from other attributes Need not be physically stored within the database Can be derived by using an algorithm 19

Depiction of a Derived Attribute * 20

Relationships n Association between entities n Participants: n n * Entities that participate in a relationship Relationships between entities always operate in both directions 21

Connectivity or Cardinality n n One – to – One: Each entity instance will have exactly one related entity instance One – to – Many: An entity instance on one side can have many related entity instances, but an entity instance on the other side will have a maximum of one related entity instance Many – to – Many: Entity instances on both sides can have many related entity instances on the other side Established by very concise statements known as business rules * 22

Cardinality in an ERD * 23

Relationship Participation n n * Optional: One entity occurrence does not require a corresponding entity occurrence in a particular relationship Mandatory: One entity occurrence requires a corresponding entity occurrence in a particular relationship 24

Mandatory Participation * 25

Optional Participation * 26

Cardinality in an ERD * 27

Strong vs. Weak Entities n Strong entities n n n Weak entity n n * exist independently of other types of entities has its own unique identifier dependent on a strong entity…cannot exist on its own Has primary key that is partially or totally derived from the parent entity in the relationship 28

A Weak Entity in an ERD * 29

Weak Entity Data Sample * 30

Relationship Degree n n * Indicates number of associated entities or participants Unary relationship – Association is maintained within a single entity Binary relationship - Two entities are associated Ternary relationship - Three entities are associated 31

Three Types of Relationships * 32

The Implementation of a Ternary Relationship * 33

Recursive Relationships * 34

1: 1 Recursive Example * 35

M: N Recursive Example * 36

1: M Recursive Example * 37

Composite Entities n Also known as bridge entities, associative entities n n * Composed of the primary keys of each of the entities to be connected May also contain additional attributes of the relationship 38

M: N Relationship * 39

Converting M: N Relationship with Composite Entity * 40

Composite Entity Data Sample * 41

Developing an ER Diagram n n Database design is an iterative rather than a linear or sequential process Iterative process n n n * Determine all of the entities Determine the attributes for each entity Determine the central or key entity and start the diagram with it Determine the relationship between each of the entities Complete the diagram with cardinality, etc. 42

Completed Tiny College ERD * 43

Activity of ER Modeling n n * View “diagramming a database” Class discussion of ERD 44

Wrap Up n Assignment 2 -1 n n Look ahead of assignment 3 -1 n * Problem 5 on Page 172 Search for a business problem 45