from er diagrams to the relational model n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
From ER Diagrams to the Relational Model PowerPoint Presentation
Download Presentation
From ER Diagrams to the Relational Model

Loading in 2 Seconds...

play fullscreen
1 / 24

From ER Diagrams to the Relational Model - PowerPoint PPT Presentation


  • 108 Views
  • Updated on

From ER Diagrams to the Relational Model. Rose-Hulman Institute of Technology Curt Clifton. Review – Entity Sets and Attributes. Entity set: collection of “things” in the DB Attribute: property of an entity. calories. name. Soda. Kinds of Attributes. Simple – single atomic value

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

From ER Diagrams to the Relational 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
from er diagrams to the relational model

From ER Diagrams to the Relational Model

Rose-Hulman Institute of Technology

Curt Clifton

review entity sets and attributes
Review – Entity Sets and Attributes
  • Entity set: collection of “things” in the DB
  • Attribute: property of an entity

calories

name

Soda

kinds of attributes
Kinds of Attributes
  • Simple – single atomic value
    • Soda name, calories
  • Composite – several sub-attributes
    • PersonName(First,Middle,Last)
  • Multi-valued – set of values for one attribute
    • Car color, Degrees earned
    • (Somewhat rare, makes some searches harder)
review keys
Review – Keys
  • Let us tell entities apart
  • The key for an entity set is a subset of the attributes for that entity set, such that no two entities agree on all the attributes

calories

name

Soda

review relationships
Review – Relationships
  • Associate 2 or more entity sets
  • Constraints
    • Maximums shown with numbers
      • Read like: a subject-verb-number-object
    • Participation shown with double line
      • Read like: a subject-has to-verb…

Most Likes

N

Person

Soda

1

er design techniques
ER Design Techniques
  • Avoid redundancy and don’t duplicate data
  • Don’t use entity set when attribute will do
  • Limit use of weak entity sets
redundancy
Redundancy
  • Wastes space
  • Leads to inconsistency
  • For example:

manf

addr

name

name

Madeby

N

1

Soda

Manf

failed attempt at fix
Failed Attempt At Fix
  • Delete Manf entity set
  • Add address to Soda

manf

Manf addr

name

Soda

successful fix
Successful Fix
  • Eliminate manf attribute from Soda
  • Use relationship to find manufacturer info.

addr

name

name

Madeby

N

1

Soda

Manf

don t use unnecessary entity sets
Don’t Use Unnecessary Entity Sets
  • Entity set should…
    • Have at least one non-key attribute OR
    • Be the “many” in a many-one or many-many relationship

addr

name

name

Madeby

N

1

Soda

Manf

bad entity set
Bad Entity Set
  • Suppose we didn’t have manufacturer address

name

name

Madeby

N

1

Soda

Manf

avoid weak entity sets
Avoid Weak Entity Sets
  • Don’t try to be too clever
  • Can usually just add a unique ID
  • Government has done this for their databases:
    • Social Security Numbers
    • Vehicle Identification Numbers
  • But…
    • Don’t trust uniqueness of IDs assigned by others
why use weak entity sets at all
Why Use Weak Entity Sets At All?
  • Federated Databases, for example…
    • All students in Indiana receiving state aid
    • All players on FIFA soccer teams
  • One query sent to multiple DB
  • Still want a Conceptual DB Schema
  • But no global authority to assign unique IDs
the relational model
The Relational Model
  • Originated as theoretical idea
    • “A Relational Model of Data for Large Shared Data Banks”, E. F. Codd, Comm. of the ACM, 13(6), June 1970
    • http://www.acm.org/classics/nov95/s1p3.html
  • Revolutionized databases
  • Led to 1981 ACM Turing Award
    • The “Nobel Prize of computing”
relations
Relations
  • (Semi-) Formally
    • Tuple: an ordered list
    • n-tuple: an ordered list of length n
    • Relation: a set of n-tuples
  • Informally:
    • Relation: a table with unique rows
    • Rows = tuples; Columns = attributes;
    • Values in column = domain
  • Database: a collection of relations
some other terms
Some Other Terms
  • Relation schema
    • Describes a relation
    • RelationName (AttrName1, AttrName2,…)
    • Or RelationName (AttrName1:type, …)
  • Database schema
    • Set of all the relation schema for the DB’s relations
why is the relational model dominant
Why is the Relational Model Dominant?
  • Very simple – just one data structure
  • Matches a “list the items” mentality
  • Easy to manipulate tables with UI
  • Forms basic foundation for SQL
    • Relational model based on sets
    • SQL based on bags (a.k.a., multi-sets)
from er diagrams to relations
From ER Diagrams to Relations
  • Entity sets become relations
    • Columns are attributes of entity set
  • Relationships also become relations
    • Columns are keys of participating entity sets
example basic entity sets
Example: Basic Entity Sets

calories

name

addr

Soda

name

Customer

example basic relationship
Example: Basic Relationship

addr

name

calories

name

Likes

Customer

Soda

MostLikes

Namedbuddy

1

BuddyWith

Naming buddy

since

simplifying
Simplifying!
  • Can avoid relations formany-one relationships
  • Just add key of the one to the relation of the many

Customer

name

addr

MostLikes

1

calories

name

Soda

over simplifying
Over Simplifying!
  • What happens if we try to eliminate relation for a many-many relationship?
  • Consider treating Likes as we did Most Likes
    • Redundancy
    • Data loss
weak entity sets
Need enough columns to make rows unique!

So…

All attributes of weak entity set

+ Key from supporting relationship

Weak Entity Sets

name

number

name

city

Plays

on

Player

Team

1

entity sets with subclasses
Entity Sets with Subclasses
  • Use nulls, or
  • Use multiple relations
    • “ER Style”
  • How should we choose which to use?

name

calories

Soda

isa

sweetener

DietSoda