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


  • 107 Views
  • Uploaded 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

PowerPoint Slideshow about 'From ER Diagrams to the Relational Model' - ronna


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