Database systems i the entity relationship model
This presentation is the property of its rightful owner.
Sponsored Links
1 / 51

Database Systems I The Entity-Relationship Model PowerPoint PPT Presentation


  • 91 Views
  • Uploaded on
  • Presentation posted in: General

Database Systems I The Entity-Relationship Model. Overview of Database Development. Requirements Analysis What data are to be stored in the enterprise? What are the required applications? What are the most important operations? High-level database design

Download Presentation

Database Systems I The Entity-Relationship Model

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Database systems i the entity relationship model

Database Systems I The Entity-Relationship Model


Overview of database development

Overview of Database Development

  • Requirements Analysis

    • What data are to be stored in the enterprise?

    • What are the required applications?

    • What are the most important operations?

  • High-level database design

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

  • ER model or UML to represent high-level design


Overview of database development1

Overview of Database Development

  • Conceptual database design

    • What data model to implement the DBS?E.g., relational data model

    • Map the high-level design (e.g., ER diagram) to a (conceptual) database schema of the chosen data model.

  • Physical database design

    • What DBMS to use?

    • What are the typical workloads of the DBS?

    • Build indexes to support efficient query processing.

    • What redesign of the conceptual database schema is necessary from the point of view of efficient implementation?


Overview of database development2

Overview of Database Development

Requirements Analysis / Ideas

High-Level Database Design

Conceptual Database Design / Relational Database Schema

Physical Database Design / Relational DBMS

 Similar to software development


Entity relationship model

Entity-Relationship Model

  • Short: ER model.

  • A lot of similarities with other modeling languages such as UML.

  • Concepts

    • Entities / Entity sets,

    • Attributes,

    • Relationships/ Relationship sets, and

    • Constraints.

  • Offers more modeling concepts than the relational data model (which only offers relations).

  • Closer to the way in which people think.


Entity relationship diagrams

Entity-Relationship Diagrams

  • An Entity-Relationship diagram (ER diagram) is a graph with nodes representing entity sets, attributes and relationship sets.

  • Entity sets denoted by rectangles.

  • Attributes denoted by ovals.

  • Relationship sets denoted by diamonds.

  • Edges (lines) connect entity sets to their attributes and relationship sets to their entity sets.


Entities and entity sets

Entities and Entity Sets

  • Entity: Real-world object distinguishable from other objects, e.g. employee Miller.

  • Entity can be physical or abstract object.

  • An entity is associated with attributes describing its properties.

  • Attribute valuesare atomic, e.g. strings, integer or real numbers.

  • Some variations of the ER model support structured attributes.

  • Entity set: A collection of similar entities. E.g., all employees.


Entities and entity sets1

name

ssn

age

Employees

Entities and Entity Sets

  • All entities in an entity set have the same set of attributes. (At least, for the moment!)

  • Each entity set has a key, i.e. a minimal set of attributes to uniquely identify an entity of this set. Key attributes are underlined.

  • Each attribute has a domain, i.e. a set of all possible attribute values.


Entities and entity sets2

Employees

Entities and Entity Sets

  • A key must be unique across all possible (not just the current) entities of its set.

  • A key can consist of more than one attribute.

  • There can be more than one key for a given entity set, but we choose one (primary key) for the ER diagram.

lastname

birthdate

firstname

salary


Relationships and relationship sets

Relationships and Relationship Sets

  • Relationship: Association among two or more entities. E.g., Miller works in Pharmacy department.

  • Relationship set: Collection of similar relationships among two or more entity sets.

name

dname

ssn

budget

age

did

Works_In

Employees

Departments


Relationships and relationship sets1

name

ssn

age

Employees

super-visor

subor-dinate

Reports_To

Relationships and Relationship Sets

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

  • Each relationship in R involves entities e1Î E1, ..., en Î En.

  • Binary relationship sets most common.

  • Same entity set can participate in different relationship sets, or in different “roles” in same set.


Relationships and relationship sets2

Relationships and Relationship Sets

  • Relationship sets can also have attributes.

  • Useful for properties that cannot reasonably be associated with one of the participating entity sets.

since

name

dname

ssn

budget

age

did

Works_In

Employees

Departments


Instances of an er diagram

Instances of an ER Diagram

  • Entity set contains a set of entities. Each entity has one value for each of its attributes.

  • No duplicate instances.

Employees


Instances of an er diagram1

Instances of an ER Diagram

  • Relationship set contains a set (no duplicates!) of relationships, each relating a set of entities, one from each of the participating entity sets.

  • Components are entities, not attribute values.

Works_In


Relationships and relationship sets3

name

ssn

age

Employees

Relationships and Relationship Sets

  • Multiway relationship sets (n > 2) are used whenever binary relationships cannot capture the application semantics.

description

tid

Works_For

Tasks

Projects

pid

pbudget


Relationships and relationship sets4

name

ssn

age

Employees

Relationships and Relationship Sets

description

tid

Works_For

Tasks

Projects

Works_For

pid

pbudget


Multiplicity of relationships

since

name

dname

ssn

age

did

budget

since

name

dname

Employees

Manages

Departments

ssn

budget

age

did

Works_In

Employees

Departments

Multiplicity of Relationships

  • An employee can work in many departments; a dept can have many employees.

  • Each dept has at most one manager, who may manage several (many) departments.


Multiplicity of relationships1

Multiplicity of Relationships

  • The different types of (binary) relationships from a multiplicity point of view:

    • One to one

    • One to many

    • Many to one

    • Many to many

one-to-one

one-to-many

many-to-one

many-to-many


Key constraints

since

name

dname

ssn

age

did

budget

Employees

Manages

Departments

Key Constraints

  • A key constraint on a relationship set specifies that the marked entity set participates in at most one relationship of this relationship set.

  • Entity set is marked with an arrow.

Key constraint


Participation constraints

since

since

name

name

dname

dname

ssn

did

did

budget

budget

age

Departments

Employees

Manages

Works_In

since

Participation Constraints

  • A participation constraint on a relationship set specifies that the marked entity set participates in at least one relationship of this relationship set.

  • Entity set is marked with a bold line.

Participation

constraint


Weak entities

name

cost

name

age

ssn

age

Policy

Dependents

Employees

Weak Entities

  • A weak entity exists only in the context of another (owner) entity.

  • The weak entitycan be identified uniquely only by considering the primary key of the owner and its own partial key.

    • 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 supporting relationship set.


Subclasses

Subclasses

Sometimes, an entity set contains some entities that do share many, but not all properties with the entity set. In this case, we want to define class (entity set) hierarchies.

A ISA B: every A entity is also considered to be a B entity. A specializes B, B generalizes A.

A is called subclass, B is called superclass.

A subclass inherits the attributes of a superclass, and may define additional attributes.


Subclasses1

name

ssn

age

Employees

hours_worked

hourly_wages

ISA

contractid

Contract_Emps

Hourly_Emps

Subclasses

Hourly_Emps and Contract_Emps inherit the ssn (key!), name and age attributes from Employees.

They define additional attributes hourly_wages, hours_worked and contractid, resp.


Subclasses2

Subclasses

  • Overlap constraints: Can Joe be an Hourly_Emps as well as a Contract_Emps entity?

    (Hourly_Emps OVERLAPSContract_Emps)

  • Covering constraints: Does every Employees entity have to be either an Hourly_Emps or a Contract_Emps entity?

    Hourly_Emps ANDContract_Emps COVER Employees


Subclasses3

Subclasses

  • There are several good reasons for using ISA relationships and subclasses:

    • Do not have to redefine all the attributes.

    • Can add descriptive attributesspecific to a subclass.

    • To identify entitity sets that participate in a relationship set as precisely as possible.

  • ISA relationships form a tree structure (taxonomy) with one entity set serving as root.


Design principles

Design Principles

  • Faithfulness

    • Design must be faithful to the specification / reality.

    • Relevant aspects of reality must be represented in the model.

  • Avoiding redundancy

    • Redundant representation blows up ER diagram and makes it harder to understand.

    • Redundant representation wastes storage.

    • Redundancy may lead to inconsistencies in the database.


Design principles1

Design Principles

  • Keep it simple

    • The simpler, the easier to understand for some (external) reader of the ER diagrams.

    • Avoid introducing more elements than necessary.

    • If possible, prefer attributes over entity sets and relationship sets.

  • Formulate constraints as far as possible

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

    • But some constraints cannot be captured in ER diagrams.


High level design with er model

High-Level Design With ER Model

  • Major design choices

    • Should a concept be modeled as an entity or an attribute?

    • Should a concept be modeled as an entity or a relationship?

    • What relationships to use: binary or ternary?


Entity vs attribute

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 attribute1

to

from

name

dname

ssn

lot

did

budget

Departments

Works_In2

Employees

name

dname

ssn

lot

did

budget

Works_In3

Departments

Employees

Duration

to

from

Entity vs. Attribute

  • Works_In2 does not allow an employee to work in the same department for two or more periods (why?).

  • We want to record several values of the descriptive attributes for each instance of this relationship.


Entity vs relationship

since

dbudget

name

dname

ssn

lot

did

budget

Departments

Employees

Manages2

Entity vs. Relationship

  • This ER diagram o.k. if a manager gets a separate discretionary budget for each dept.

  • But what if a manager gets a discretionary budget that covers all managed depts?

    • Redundancy of dbudget, which is stored for each dept managed by the manager.

    • Misleading: suggests dbudget tied to managed dept.


Entity vs relationship1

since

dbudget

name

dname

ssn

lot

did

budget

Manages2

Departments

Employees

name

dname

ssn

lot

did

budget

Departments

Manages3

Employees

since

Mgr_Appts

apptnum

dbudget

Entity vs. Relationship

  • What about this diagram?

  • The following ER diagram is more appropriate and avoids the above problems!


Binary vs ternary relationships

name

ssn

lot

Employees

Policies

policyid

cost

Binary vs. Ternary Relationships

  • If each policy is owned by just one employee:

    • Key constraint on Policies would mean policy can only cover 1 dependent!

    • Bad design!

pname

age

Dependents

Covers


Binary vs ternary relationships1

name

pname

age

ssn

lot

Dependents

Employees

Purchaser

Beneficiary

Policies

policyid

cost

Binary vs. Ternary Relationships

  • This diagram is a better design.

  • What are the additional constraints in this diagram?


Binary vs ternary relationships2

Binary vs. Ternary Relationships

  • Previous example illustrated a case when two binary relationships were better than one ternary relationship.

  • An example in the other direction: a ternary relation Contracts relates entity sets Parts, Departments and Suppliers, and has descriptive attribute qty. No combination of binary relationships is an adequate substitute:

    • S “can-supply” P, D “needs” P, and D “deals-with” S does not imply that D has agreed to buy P from S.

    • How do we record qty?


Conceptual design er to relational

How to represent

Entity sets,

Relationship sets,

Attributes,

Key and participation constraints,

Subclasses,

Weak entity sets

. . . ?

Conceptual Design: ER to Relational


Entity sets

Entity sets are translated to tables.

name

ssn

lot

Employees

Entity Sets

CREATE TABLE Employees

(ssn CHAR(11),

name CHAR(20),

lot INTEGER,

PRIMARY KEY (ssn));


Relationship sets

Relationship sets are also translated to tables.

Keys for each participating entity set (as foreign keys).

The combination of these keys forms a superkey for the table.

All descriptive attributesof the relationship set.

Relationship Sets

CREATE TABLE Works_In(

ssn CHAR(11),

did INTEGER,

since DATE,

PRIMARY KEY (ssn, did),

FOREIGN KEY (ssn)

REFERENCES Employees,

FOREIGN KEY (did)

REFERENCES Departments);


Key constraints1

Each dept has at most one manager, according to the key constraint on Manages.

since

name

dname

ssn

lot

Employees

Manages

Key Constraints

budget

did

Departments

Translation to

relational model?

one-to-one

one-to-many

many-to-one

many-to-many


Key constraints2

Map relationship set to a table:

Separate tables for Employees and Departments.

Note that did is the key now!

Since each department has a unique manager, we could instead combine Manages and Departments.

Key Constraints

CREATE TABLE Manages(

ssn CHAR(11),

did INTEGER,

since DATE,

PRIMARY KEY (did),

FOREIGN KEY (ssn) REFERENCES Employees,

FOREIGN KEY (did) REFERENCES Departments)

CREATE TABLE Dept_Mgr(

did INTEGER,

dname CHAR(20),

budget REAL,

manager CHAR(11),

since DATE,

PRIMARY KEY (did),

FOREIGN KEY (manager) REFERENCES Employees)


Participation constraints1

Participation Constraints

  • We can capture participation constraints involving one entity set in a binary relationship, using NOT NULL.

  • In other cases, we need CHECK constraints.

CREATE TABLE Dept_Mgr(

did INTEGER,

dname CHAR(20),

budget REAL,

manager CHAR(11) NOT NULL,

since DATE,

PRIMARY KEY (did),

FOREIGN KEY (manager) REFERENCES Employees,

ON DELETE NO ACTION)


Weak entity sets

Weak Entity Sets

  • A weak entity set can be identified uniquely only by considering the primary key of another (owner) entity set.

    • 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 identifying relationship set.

name

cost

pname

age

ssn

lot

Policy

Dependents

Employees


Weak entity sets1

Weak Entity Sets

  • Weak entity set and identifying relationship set are translated into a single table.

    • When the owner entity is deleted, all owned weak entities must also be deleted.

CREATE TABLE Dep_Policy (

pname CHAR(20),

age INTEGER,

cost REAL,

ssn CHAR(11) NOT NULL,

PRIMARY KEY (pname, ssn),

FOREIGN KEY (ssn) REFERENCES Employees,

ON DELETE CASCADE)


Subclasses4

If we declare A ISA B, every A entity is also considered to be a B entity.

Attributes of B are inherited to A.

Overlap constraints: Can Joe be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/disallowed)

Covering constraints: Does every Employees entity either have to be an Hourly_Emps or a Contract_Emps entity? (Yes/no)

Subclasses

name

ssn

lot

Employees

hours_worked

hourly_wages

ISA

contractid

Contract_Emps

Hourly_Emps


Subclasses5

Subclasses

  • ER style translation

    • One table for each of the entity sets (superclass and subclasses).

    • ISA relationship does not require additional table.

    • All tables have the same key, i.e. the key of the superclass.

    • E.g.: One table each for Employees, Hourly_Emps and Contract_Emps.

      • General employee attributes are recorded in Employees. For hourly emps and contract emps, extra info recorded in the respective relations.


Subclasses6

Subclasses

  • Queries involving all employees easy, those involving just Hourly_Emps require a join to get their special attributes.

CREATE TABLE Employees(

ssn CHAR(11),

name CHAR(20),

lot INTEGER,

PRIMARY KEY (ssn))

CREATE TABLE Hourly_Emps(

ssn CHAR(11),

hourly_wages REAL,

hours_worked INTEGER,

PRIMARY KEY (ssn),

FOREIGN KEY (ssn) REFERENCES Employees,

ON DELETE CASCADE)


Subclasses7

Subclasses

  • Alternative translation

    • Create tables for the subclasses only. These tables have all attributes of the superclass(es) and the subclass.

    • This approach is applicable only if the subclasses cover the superclass.

    • E.g.:

      Hourly_Emps: ssn, name, lot, hourly_wages,hours_worked.

      Contract_Emps: ssn, name, lot, contractid.

  • Queries involving all employees difficult, those on Hourly_Emps and Contract_Emps alone are easy.

  • Only applicable, if Hourly_Emps ANDContract_Emps COVER Employees


Binary vs ternary relationships3

name

ssn

lot

Employees

Policies

policyid

cost

name

ssn

lot

Employees

Beneficiary

Policies

policyid

cost

Binary vs. Ternary Relationships

pname

age

  • If each policy is owned by just one employee:

    • Key constraint on Policies would mean policy can only cover one dependent!

Dependents

Covers

Bad design

pname

age

Dependents

Purchaser

Better design


Binary vs ternary relationships4

Binary vs. Ternary Relationships

CREATE TABLE Policies (

policyid INTEGER,

cost REAL,

ssn CHAR(11) NOT NULL,

PRIMARY KEY (policyid).

FOREIGN KEY (ssn) REFERENCES Employees,

ON DELETE CASCADE)

  • The key constraints allow us to combine Purchaser with Policies and Beneficiary with Dependents.

  • Participation constraints lead to NOT NULL constraints.

CREATE TABLE Dependents(

pname CHAR(20),

age INTEGER,

policyid INTEGER NOT NULL,

PRIMARY KEY (pname, policyid).

FOREIGN KEY (policyid) REFERENCES Policies,

ON DELETE CASCADE)


Summary

Summary

  • High-level design follows requirements analysis and yields a high-level description of data to be stored.

  • ER model popular for high-level design.

    • Constructs are expressive, close to the way people think about their applications.

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

  • Some additional constructs: weak entities, subclasses, and constraints.

  • ER design is subjective. There are often many ways to model a given scenario! Analyzing alternatives can be tricky, especially for a large enterprise.


Summary1

Summary

  • There are guidelines to translate ER diagrams to a relational database schema.

  • However, there are often alternatives that need to be carefully considered.

  • Entity sets and relationship sets are all represented by relations.

  • Some constructs of the ER model cannot be easily translated, e.g. multiple participation constraints.


  • Login