Prepare for your exams
Get points
Guidelines and tips
Sell on Docsity
Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips

Sell on Docsity

Docsity AI

Log in Sign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents

20 Points

For each uploaded document

Answer questions

5 Points

For each given answer (max 1 per day)

All the ways to get free points

Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Understanding Entities, Relationships, and ER Model in Database Design, Slides of Database Management Systems (DBMS)

University of Wisconsin (UW) - Madison Database Management Systems (DBMS)

An excerpt from 'database management systems' by r. Ramakrishnan and j. Gehrke. It covers the entity-relationship (er) model, which is used in conceptual database design. Entities, relationships, and their attributes, as well as keys, participation constraints, and weak entities. It also introduces the concept of isa (is-a) hierarchies and aggregation.

Typology: Slides

2011/2012

Uploaded on 02/15/2012

arien 🇺🇸

4.8

(24)

309 documents

1 / 6

This page cannot be seen from the preview

Don't miss anything!

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 1

TheEntity-RelationshipModel

Chapter2

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 2

OverviewofDatabaseDesign



Conceptualdesign:(ERModelisusedatthisstage.)



Whataretheentities andrelationship s inthe

enterprise?



Whatinformationaboutthese entitiesand

relationshipsshouldwestorein thedatabase?



Whataretheintegrityconstraintsorbusinessrulesthat

hold?



Adatabase`schema’intheER Modelcanbe

representedpictorially(ERdiagrams).



CanmapanERdiagramintoa relationalschema.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 3

ERModelBasics



Entity:Real-worldobjectdistinguishable

fromotherobjects.Anentityisdescribed

(inDB)usingasetofattributes.



EntitySet:Acollectionofsimilarentities.

E.g.,allemployees.



Allentitiesinanentitysethavethesamesetof

attributes.(UntilweconsiderISAhierarchies,

anyway!)



Eachentitysethasakey.



Eachattributehasadomain.

Employees

ssn name lot

Discover Slides of Database Management Systems (DBMS) University of Wisconsin (UW) - Madison

Partial preview of the text

Download Understanding Entities, Relationships, and ER Model in Database Design and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1

The Entity-Relationship Model

Chapter 2

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2

Overview of Database Design

Conceptual design : (ER Model is used at this stage.)

What are the entities and relationships in the

enterprise?

What information about these entities and

relationships should we store in the database?

What are the integrity constraints or business rules that

hold?

A database `schema’ in the ER Model can be

represented pictorially ( ER diagrams ).

Can map an ER diagram into a relational schema.

ER Model Basics

Entity: Real-world object distinguishable

from other objects. An entity is described

(in DB) using a set of attributes.

Entity Set : A collection of similar entities.

E.g., all employees.

All entities in an entity set have the same set of

attributes. (Until we consider ISA hierarchies,

anyway!)

Each entity set has a key.

Each attribute has a domain.

Employees

ssn

name

lot

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4

ER Model Basics (Contd.)

Relationship : Association among two or more entities.

E.g., Attishoo works in Pharmacy department.

Relationship Set : Collection of similar relationships.

An n-ary relationship set R relates n entity sets E1 ... En;

each relationship in R involves entities e1 E1, ..., en En

• Same entity set could participate in different

relationship sets, or in different “roles” in same set.

lot

dname did budget

since name

Employees Works_In Departments

ssn

Reports_To

lot

name

Employees

subor- dinate

super- visor

ssn

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5

Key Constraints

Consider Works_In:

An employee can

work in many

departments; a dept

can have many

employees.

In contrast, each

dept has at most

one manager,

according to the

key constraint on

Manages.

1-to-1 1-to Many Many-to-1 Many-to-Many

dname did budget

since

lot

name ssn

Employees Manages Departments

Participation Constraints

Does every department have a manager?

If so, this is a participation constraint : the participation of

Departments in Manages is said to be total (vs. partial ).

Every did value in Departments table must appear in a row of

the Manages table (with a non-null ssn value!)

lot

name (^) dname did budget

since name (^) dname did budget

since

Manages

since

Employees Departments

ssn

Works_In

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 10

Conceptual Design Using the ER Model

Design choices:

Should a concept be modeled as an entity or an

attribute?

Should a concept be modeled as an entity or a

relationship?

Identifying relationships: Binary or ternary?

Aggregation?

Constraints in the ER Model:

A lot of data semantics can (and should) be captured.

But some constraints cannot be captured in ER

diagrams.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 11

Entity vs. Attribute

Should address be an attribute of Employees or an

entity (connected to Employees by a relationship)?

Depends upon the use we want to make of address

information, and the semantics of the data:

• If we have several addresses per employee, address

must be an entity (since attributes cannot be set-

valued).

• If the structure (city, street, etc.) is important, e.g., we

want to retrieve employees in a given city, address

must be modeled as an entity (since attribute values

are atomic).

Entity vs. Attribute (Contd.)

Works_In4 does not

allow an employee to

work in a department

for two or more periods.

wanting to record several

addresses for an employee:

We want to record several

values of the descriptive

attributes for each instance of

this relationship.

Accomplished by

introducing new entity set,

Duration.

name

Employees

ssn lot

Works_In

from to dname did budget

Departments

dname did budget

name

Departments

ssn lot

Employees Works_In

from Duration to

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 13

Entity vs. Relationship

First ER diagram OK if

a manager gets a

separate discretionary

budget for each dept.

What if a manager gets

a discretionary

budget that covers

all managed depts?

Redundancy: dbudget

stored for each dept

managed by manager.

Misleading: Suggests

dbudget associated with

department-mgr

combination.

Manages

name dname did budget

Employees Departments

ssn lot

since dbudget

dname did budget

Manages2 Departments

Employees

name ssn (^) lot since

Managers dbudget

ISA

This fixes the

problem!

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 14

Binary vs. Ternary Relationships

If each policy is

owned by just 1

employee, and

each dependent

is tied to the

covering policy,

first diagram is

inaccurate.

What are the

additional

constraints in the

2nd diagram?

pname age

Covers Dependents

name

Employees

ssn (^) lot

Policies

policyid (^) cost

Beneficiary

pname age

Dependents

policyid cost

Policies

Purchaser

name

Employees

ssn lot

Understanding Entities, Relationships, and ER Model in Database Design, Slides of Database Management Systems (DBMS)

Related documents

Partial preview of the text

Download Understanding Entities, Relationships, and ER Model in Database Design and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

The Entity-Relationship Model

Chapter 2

Overview of Database Design

Conceptual design : (ER Model is used at this stage.)

What are the entities and relationships in the

enterprise?

What information about these entities and

relationships should we store in the database?

What are the integrity constraints or business rules that

hold?

A database `schema’ in the ER Model can be

represented pictorially ( ER diagrams ).

Can map an ER diagram into a relational schema.

ER Model Basics

Entity: Real-world object distinguishable

from other objects. An entity is described

(in DB) using a set of attributes.

Entity Set : A collection of similar entities.

E.g., all employees.

All entities in an entity set have the same set of

attributes. (Until we consider ISA hierarchies,

anyway!)

Each entity set has a key.

Each attribute has a domain.

Employees

ssn

name

lot

ER Model Basics (Contd.)

Relationship : Association among two or more entities.

E.g., Attishoo works in Pharmacy department.

Relationship Set : Collection of similar relationships.

An n-ary relationship set R relates n entity sets E1 ... En;

each relationship in R involves entities e1 E1, ..., en En

• Same entity set could participate in different

relationship sets, or in different “roles” in same set.

Key Constraints

Consider Works_In:

An employee can

work in many

departments; a dept

can have many

employees.

In contrast, each

dept has at most

one manager,

according to the

key constraint on

Manages.

Participation Constraints

Does every department have a manager?

If so, this is a participation constraint : the participation of

Departments in Manages is said to be total (vs. partial ).

the Manages table (with a non-null ssn value!)

Conceptual Design Using the ER Model

Design choices:

Should a concept be modeled as an entity or an

attribute?

Should a concept be modeled as an entity or a

relationship?

Identifying relationships: Binary or ternary?

Aggregation?

Constraints in the ER Model:

A lot of data semantics can (and should) be captured.

But some constraints cannot be captured in ER

diagrams.

Entity vs. Attribute

Should address be an attribute of Employees or an

entity (connected to Employees by a relationship)?

Depends upon the use we want to make of address

information, and the semantics of the data:

• If we have several addresses per employee, address

must be an entity (since attributes cannot be set-

valued).

• If the structure (city, street, etc.) is important, e.g., we

want to retrieve employees in a given city, address