COP 5725 DATABASE MANAGEMENT ER DIAGRAM AND RELATIONAL

COP 5725 DATABASE MANAGEMENT ER DIAGRAM AND RELATIONAL DATA MODEL Prof: Dr. Shu-Ching Chen TA: Sheng Guan

ENTITY-RELATIONSHIP MODEL

E- R DATA MODELING An entity is an object that exists and is distinguishable from other objects. Example: specific person, company, event, plant Entities have attributes Example: people have names and addresses An entity set is a set of entities of the same type that share the same properties. Example: set of all persons, companies, trees, holidays

ATTRIBUTES An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set. Example: instructor = (ID, name, street, city, salary ) course= (course_id, title, credits) Attribute types: Simple and composite attributes. Single-valued and multivalued attributes Derived attributes

RELATIONSHIP SETS A relationship is an association among several entities Example: 44553 (Peltier) student entity advisor relationship set 22222 (Einstein) instructor entity

ENTITY-RELATIONSHIP DIAGRAMS Representing entities CUSTOMER PART-TIME EMPLOYEE 6 we represent an entity by a named rectangle use a singular noun, or adjective + noun refer to one instance in naming

ENTITY-RELATIONSHIP DIAGRAMS Representing relationship DEPT employs EMPLOYEE

ENTITY-RELATIONSHIP DIAGRAMS One to one One to many

ENTITY-RELATIONSHIP DIAGRAMS Many-to-many relationship (M: N): exists when one instance of the first entity can relate to many instances of the second entity, and one instance of the second entity can relate to many instances of the first entity. (c) Many to many

E-R DIAGRAM FOR A UNIVERSITY

The Relational Data Model Database Model (ODL, E/R) ODL definitions Diagrams (E/R) Relational Schema Tables: row names: attributes rows: tuples Physical storage Complex file organization and index structures.

Terminology Attribute names tuples Name Price Category Manufacturer gizmo $19. 99 gadgets Gizmo. Works Power gizmo $29. 99 gadgets Gizmo. Works Single. Touch $149. 99 photography Canon Multi. Touch $203. 99 household Hitachi

More Terminology Every attribute has an atomic type. Relation Schema: relation name + attribute names + attribute types Relation instance: a set of tuples. Only one copy of any tuple! Database Schema: a set of relation schemas. Database instance: a relation instance for every relation in the schema.

name category name price makes Company Product Stock price buys employs Person address name ssn

Entity Sets to Relations name category price Product: Name Category gizmo gadgets Price $19. 99

Relationships to Relations name Start Year category makes name Company Product Stock price Relation MAKES (watch out for attribute name conflicts) Product-name gizmo Product-Category Company-name gadgets gizmo. Works Starting-year 1963

Handling Weak Entity Sets affiliation Team sport number University name Relation TEAM: Sport mud wrestling Number 15 University-name Montezuma State U. - need all the attributes that contribute to the key of Team - don’t need a separate relation for Affiliation.

Relational Data Model Key point: -Indicate your primary key -Define attribute’s data type -Indicate the relationship

ER Model to Relational Model

Populate your relation Key point: - At least five records are needed for each table. - Also indicate the PK here.

Constraint Key point: - Indicate the constraint at the end