程序代写代做代考 database ER INFO20003 Database Systems

INFO20003 Database Systems

INFO20003 Database Systems 1

INFO20003 Database Systems

Lecture 03

Introduction to Data Modelling (ER)

Semester 2 2018, Week 2

Dr Renata Borovica-Gajic

INFO20003 Database Systems 2© University of Melbourne 2018

Database Development Lifecycle: Review

Today
Database Planning

Systems Definition

Requirements Definition

and Analysis

Data Conversion and

Loading

Implementation

Application Design

Testing

Operational

Maintenance

Design

Physical Design

Logical Design

Conceptual Design

INFO20003 Database Systems 3© University of Melbourne 2018

The Entity-Relationship Model

• Basic ER modeling concepts

• Constraints

• Conceptual Design

Readings: Chapter 2, Ramakrishnan & Gehrke, Database Systems

INFO20003 Database Systems 4© University of Melbourne 2018

Conceptual Design: Objectives

• 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 that hold?

INFO20003 Database Systems 5© University of Melbourne 2018

ER Model: Entity & its attributes

• Entity: Real-world object distinguishable from other objects.
An entity is described (in DB) using a set of attributes.

• Entity Set: A collection of entities of the same type (e.g. all
employees)

–All entities in an entity set have the same set of attributes

–Each entity has a key (underlined)

Employees

ssn
name

lot

key

entity set

attribute

INFO20003 Database Systems 6© University of Melbourne 2018

ER Model: Relationship

• Relationship: Association among two or more entities.

Relationships can have their own attributes.

–Example: Fred works in Pharmacy department.

• Relationship Set: Collection of relationships of the same type.

–Example: Employees work in departments.

lot

name

Employees

ssn

Works_In

since
dname

budgetdid

Departments

relationship set
(with an attribute)

INFO20003 Database Systems 7© University of Melbourne 2018

ER Model: Relationship roles

Same entity set can participate in:

• different relationship sets, or even

• different “roles” in the same set

subor-

dinate

super-

visor

Reports_To

since

Works_In

dname

budgetdid

Departments

lot

name

Employees

ssn

different roles

different

relationships

INFO20003 Database Systems 8© University of Melbourne 2018

The Entity-Relationship Model

• Basic ER modeling concepts

• Constraints

• Conceptual Design

Readings: Chapter 2, Ramakrishnan & Gehrke, Database Systems

INFO20003 Database Systems 9© University of Melbourne 2018

Key Constraints: Types

1-to-11-to ManyMany-to-Many

Key constraints determine the number of objects taking part in the

relationship set (how many from each side)

Types of key constraints:

INFO20003 Database Systems 10© University of Melbourne 2018

Key Constraints: Many to Many

Example:

An employee can work in many departments; a department can have many

employees.

Many is represented by a “line”.

Employees

dname

budget

did

Departments

since

Works_In

lot

name

ssn

Employees DepartmentsWorks_in

manymany

INFO20003 Database Systems 11© University of Melbourne 2018

Key Constraints: One-to-Many

Example:

Each department has at most one manager.

This is the key constraint on Manages.

since

Manages

dname

budget

did

Departments

lot

name

ssn

Employees

Employees DepartmentsManages

onemany

One-to-many constrains one entity set to have a single entity per a relationship.

An entity of that set can never participate in two relationships of the same

relationship set. This is called key constraint and is represented by an “arrow”.

Key

constraint

INFO20003 Database Systems 12© University of Melbourne 2018

Participation Constraints

Example: Every employee must work in a department. Each department has at

least one employee. Each department has to have a manager (but not everyone

is a manager).

lot

name dname

budget

since

did

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Employees DepartmentsWorks_in

Total

participation
Total

participation

Employees DepartmentsManages

Partial

participation

Total

participation

Participation constraint explores whether all entities of one entity set take part

in a relationship. If yes this is a total participation, otherwise is partial. Total

participation says that each entity takes part in “at least one” relationship, and

is represented by “bold” line.

INFO20003 Database Systems 13© University of Melbourne 2018

Weak Entities

A weak entity can be identified uniquely only by considering (the primary

key of) another (owner) entity. They are represented as a “bold” rectangle.

• Owner entity set and weak entity set must participate in a one-to-many

relationship set (one owner, many weak entities)

• Weak entity set must have total participation in this relationship set. Such

relationship is called identifying and is represented as “bold”.

name

agepname

DependentsEmployees

ssn

Policy

cost

Weak entities have only a “partial key” (dashed underline)
and they are identified uniquely only when considering
the primary key of the owner entity

INFO20003 Database Systems 14© University of Melbourne 2018

Ternary Relationships

In general, we can have n-ary relationships, and

relationships can have attributes

Suppliers

qty

DepartmentsContractParts

This is a ternary relationship

with one relationship attribute

INFO20003 Database Systems 15© University of Melbourne 2018

Ternary vs. Binary Relationships

Second model:
• S “can-supply” P, D “needs” P, and D “deals-with” S does not imply

that D has agreed to buy P from S. Not the same!

• How do we record qty?

Suppliers

qty

DepartmentsContractParts

Suppliers

Departments

deals-with

Parts

can-supply

VS.

Are these two models the same?

INFO20003 Database Systems 16© University of Melbourne 2018

Now you try

University database schema:

• Entities: Courses, Professors

• Each course has id, title, time

• Make up suitable attributes for professors

INFO20003 Database Systems 17© University of Melbourne 2018

Now you try

1. Every professor must teach some course.

2. Every professor teaches exactly one course (no more, no less).

3. Every professor teaches exactly one course (no more, no less),

and every course must be taught by some professor.

INFO20003 Database Systems 19© University of Melbourne 2018

The Entity-Relationship Model

• Basic ER modeling concepts

• Constraints

• Conceptual Design

Readings: Chapter 2, Ramakrishnan & Gehrke, Database Systems

INFO20003 Database Systems 20© University of Melbourne 2018

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?

– Should we model relationships as binary, ternary, n-ry?

• Constraints in the ER Model:

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

INFO20003 Database Systems 21© University of Melbourne 2018

Entity vs. Attribute

Example:

Should “address” be an attribute of Employees or an entity
(related to Employees)?

Answer:

• Depends upon how we want to use address information, and
the semantics of the data:

– If we have several addresses per employee,
address must be an entity

– If the structure (city, street, etc.) is important, address
should be modeled as an entity

INFO20003 Database Systems 24© University of Melbourne 2018

Notes on the ER design

• ER design is subjective. There are often many ways to model

a given scenario!

• Analyzing alternatives can be tricky, especially for a large

enterprise. Common choices include:

–Entity vs. attribute, entity vs. relationship, binary or n-ary

relationship.

• There is no standard/notation (we will cover two notations,

today we learned Chen’s notation)

INFO20003 Database Systems 25© University of Melbourne 2018

Summary of Conceptual Design

• Conceptual design follows requirements analysis

–Yields a high-level description of data to be stored

• ER model popular for conceptual design

–Constructs are expressive, close to the way people think

about their applications

–Originally proposed by Peter Chen, 1976

Note: there are many variations on ER model

• Basic constructs: entities, relationships, and attributes (of

entities and relationships)

• Some additional constructs: weak entities

INFO20003 Database Systems 26© University of Melbourne 2018

Database Development Lifecycle: Review

Today
Database Planning

Systems Definition

Requirements Definition

and Analysis

Data Conversion and

Loading

Implementation

Application Design

Testing

Operational

Maintenance

Design

Physical Design

Logical Design

Conceptual Design

Next time

INFO20003 Database Systems 27

What’s Examinable

© University of Melbourne 2018

• Need to be able to draw conceptual diagrams on your own

‒ Given a problem, determine entities, attributes, relationships

‒ What is key constraint and participation constraint, weak entity?

‒ Determine constraints for the given entities & their relationships

INFO20003 Database Systems 28

Next Lecture

© University of Melbourne 2018

• Continue exploring modelling

– From conceptual through to physical

– Introducing relational model