1 Introduction to Entities Attributes and Relationships Section

1 Introduction to Entities, Attributes, and Relationships Section 02 – Lessons 1, 2, 3 • Use the student note section below for further explanation of the slide content. • Use your mouse to resize the content pane and read all of the student note section. Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Overview • • Why conceptual modeling? Introduction of the Key role players: – Entities

Why Create a Conceptual Model? • It describes exactly the information needs of the business • • It facilitates discussion • It is a very good practice with many practitioners 1 -3 It helps to prevent mistakes, misunderstanding It forms important “ideal system” documentation It forms a sound basis for physical database design Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Between Dream and Reality. . . 1 -4 Copyright Ó Oracle Corporation, 1999. All

Dream and Reality - Activity Directions: • Working in pairs, one student describes his/her "dream house" while the other student attempts to draw it. The pairs can discuss specific details but, the student describing the house is not allowed to see what is being drawn until after time is called. Assessment: • Ask volunteer student pairs to describe the discrepancies between what they thought was being described and what was actually drawn. 1 -5 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Entity Relationship Modeling • Models business, not implementation • Is a well-established technique • • Has a robust syntax Results in easy-toread diagrams…. . . although they may look rather complex at first sight 1 -6 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Goals of Entity Relationship Modeling • • • Capture all required information • Information is in a predictable, logical place 1 -7 Information appears only once Model no information that is derivable from other information already modeled Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Database Types ER Model Network Hierarchical Relational 1 -8 Copyright Ó Oracle Corporation, 1999.

Entity • An Entity is: – “Something” of significance to the business about which data must be known. – A name for the things that you can list. – Usually a noun. • • 1 -9 Examples: objects, events Entities have instances. Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Entities and Instances PERSON PRODUCT TYPE EMPLOYMENT CONTRACT JOB SKILL LEVEL TICKET RESERVATION PURCHASE ELECTION Mahatma Gandhi 2. 5 x 35 mm copper nail my previous contract violinist fluent tonight: Hamlet in the Royal the CD I bought yesterday for parliament next fall PRINTER PREFERENCE DOCUMENT VERSION 1 -10 …. . . Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Entities and Sets • An entity represents a set of instances that are of interest to a particular business. JOB manager cook waitress financial controller dish washer porter waiter piano player 1 -11 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Attribute • Also represents something of significance to the business • • Is a single valued property detail of an entity Is a specific piece of information that: – Describes – Quantifies – Qualifies – Classifies – Specifies an entity. 1 -12 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Attribute Examples Entity EMPLOYEE Attribute Family Name, Age, Shoe Size, Town of Residence, Email, . . . CAR ORDER JOB Model, Weight, Catalog Price, … Order Date, Ship Date, … Title, Description, . . . TRANSACTION Amount, Transaction Date, … EMPLOYMENT CONTRACT Start Date, Salary, . . . 1 -13 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Practice Exercises Complete the following Practice Exercises in class: • • 1 -14 1

Relationships • Also represent something of significance to the business • • Express how entities are mutually related 1 -15 Always exist between two entities (or one entity twice) Always have two perspectives Are named at both ends Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Relationship Examples EMPLOYEES have JOBS are held by EMPLOYEES PRODUCTS are classified by a PRODUCT TYPE is a classification for a PRODUCT PEOPLE make TICKET RESERVATIONS are made by PEOPLE 1 -16 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Employees have Jobs JOB EMPLOYEE Shintaro Jill Ahmed Adam Maria manager cook waitress financial controller dish washer porter waiter piano player Numerical observation: • • 1 -17 All EMPLOYEES have a JOB No EMPLOYEE has more than one JOB Not all JOBS are held by an EMPLOYEE Some JOBS are held by more than one EMPLOYEE Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Entity Representation in Diagram Drawn as a “softbox” Name singular Name inside Neither size, nor position has a special meaning ELECTION • • ORDER EMPLOYEE JOB TICKET RESERVATION JOB ASSIGNMENT During design, entities usually lead to tables. 1 -18 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Attributes in Diagrams EMPLOYEE JOB * Family Name * Address * Title o o o * o Description Birth Date Shoe Size Email Mandatory attribute, that is, known and available for every instance o Optional attribute, that is, unknown or unimportant to know for some instances During design, attributes lead to columns. 1 -19 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Relationship in Diagrams An employee has exactly one job EMPLOYEE JOB has held by Jobs are held by one or more employees During design, relationships lead to foreign keys. 1 -20 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Two Perspectives mandatory: EMPLOYEE optional: has JOB held by 1 -23 Copyright Ó Oracle

One Way mandatory: EMPLOYEE optional: JOB has held by Every EMPLOYEE has one and

The Other Way mandatory: EMPLOYEE optional: JOB has held by A JOB may be

Reading a Relationship End P split into part of “Each P must be may be split into Q One and only one Q one or more Qs “Each P may be split into one or more Qs” 1 -26 Copyright Ó Oracle Corporation, 1999. All rights reserved. ® ”

Reading a Relationship End P split into part of “Each Q must be may be part of Q ” One and only one P one or more Ps “Each Q must be part of exactly one P” 1 -27 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Reading a Relationship End P split into part of Q “Each P may be

Functions Drive Data • Business functions are always present. – Explicit – Assumed • • Business functions need data. An entity, attribute, or relationship may be modeled because: – It is used by a business function. – The business need may arise in the near future. 1 -29 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Weather Forecast January 26 København Bremen Berlin München Amsterdam Bruxelles Paris Bordeaux 1 -30 1/-5 0/-3 4 3/-1 * * ** 3 3 5/-3 3 8/3 4 4/0 2 4/1 3 7/2 3 Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

DK IR København (Copenhagen) UK Bremen NL Amsterdam BE Bruxelles DE Berlin (Brussels) Paris

Weather Forecast, a Solution CITY * Name o Geographical Position located in having COUNTRY * Name o Geographical Position subject of about FORECAST * Date o Minimum Temperature o Maximum Temperature o Wind Force 1 -32 referring to WEATHER TYPE * Icon referred in * Description referring to WIND DIRECTION * Icon referred in * Description Copyright Ó Oracle Corporation, 1999. All rights reserved. ®

Graphical Elements of ER Diagram Entity Attribute * * o Relationship Subtype Unique identifier

Summary • • • ER Modeling models information conceptually • • Diagrams provide easy means of communication • • IN CLASS - Complete the Practice Exercises 1 -34 Based on functional business needs ER Modeling focuses on “what” a business is doing. Not necessarily the “how” Detailed, but not too much IN CLASS and ONLINE Complete the Quiz for this Chapter. Copyright Ó Oracle Corporation, 1999. All rights reserved. ®