Slide 4

Question 1

Entity Relationship Diagram (ERD)

Answer 1

• A commonly-used conceptual model of data requirements – Others exist…
• The purpose – identifying and understanding the data requirement from a business perspective
• A key advantage - can be easily converted to a relational (tabular) logical model – The relational database model is by far the most commonly used storage method for structured data

Question 2

Entity Relationship Diagram (ERD) consist of

Answer 2

• 1) Entities,
• 2) Relationships
• 3) Attributes
• 4) Cardinalities.

Question 3

The Three Phases of Database Design

Answer 3

Conceptual design
logical design
physical design

Question 4

Conceptual design

Answer 4

modeling business entities and their attributes
– E.g., ERD – Entity-Relationships Diagram
– Similar to an architectural “blue-print” for constructing a building.

Question 5

Logical design

Answer 5

converting the conceptual model to a specific storage method – E.g., The Relational model – storing the data in twodimensional tables (columns and rows)

Question 6

Physical design

Answer 6

converting the logical model to a specific DBMS technology – E.g., Oracle, Sybase, MS-SQL, MS-Access

Question 7

Entity

Answer 7

•An abstract concept that we use to represent key business aspects that we need to capture data about

Question 8

The entity (or entity-class) represents a type of objects with some common characteristics:

Answer 8

– Persons: agency, contractor, customer, department, division, employee, instructor, student, supplier – Places: sales region, building, room, branch office, campus – Objects: book, machine, part, product, raw material, software license, software package, tool, vehicle model, vehicle – Events: application, award, cancellation, class, flight, invoice, order, registration, renewal, requisition, reservation, sale, trip – Concepts: account, block of time, bond, course, fund, qualification, stock

Question 9

Entity instance – a specific case of an Entity, eg

Answer 9

– Universities: Boston University, Harvard, MIT, Boston College …
– Actors: Brad Pitt, Julia Roberts, Denzel Washington, Tom Cruise, Hale Berry,…
– Fast-food chains: McDonald’s, Subway, Starbucks, Burger King

Question 10

In the ERD we represent each entity by a rectangle with the unique name.

Answer 10

– Choose a simple name that makes business sense – Singular – E.g., “Student” not “Students”

Question 11

Example – Entities in a Casino environment

Which entities are important?

Answer 11

• Resort
• Customer
• Employee
• Product

Question 12

Relationships

Answer 12

The relationship  (or relationship-class) represents how instances that belong to two or more entities are associated with each other in the “real-world”
  If entities are the “nouns”, relationships are the “verbs”…

Question 13

A relationship instance is a specific case of a relationship

Answer 13

E.g., Prof. Emily Bailey (entity instance) teaches IS323 (entity instance) to John Robertson (entity instance)

Question 14

Attributes is:

Answer 14

• Characteristics of an entity or a relationship

Question 15

Identifier attributes (also known as primary key):

Answer 15

– a set of one or more attributes that uniquely identifies each instance of an entity or relationship
– E.g. Student-ID – unique for each student
– E.g. Course-Number, Semester, Year, Section – together, the combination is unique for each course offering

• Mandatory for each Entity • In the ERD diagram - identifier attributes are underlined

Question 16

Cardinality

Answer 16

• a property of a relationship, the extent of participation
• Useful for understanding and defining business rules • Defining constraints on entity-participation in the relationship • Can capture both the minimum and maximum degree of participation – E.g., should a student participate at least in one course? • In the ERD – cardinality is represented by using the “crowsfoot” notation

Question 17

ERD – The Importance of Proper Description

Answer 17

• The ERD provides a convenient visual representation of the conceptual model – A great communication tool between business managers and IT-personnel
• Together with the diagram, it is important to provide some documentation that describes: – The entity, the relationships, and their attributes – Justify the cardinalities • Especially, if any of the above is not obvious and “straight forward