data modeling with erd
Download
Skip this Video
Download Presentation
Data Modeling with ERD

Loading in 2 Seconds...

play fullscreen
1 / 26

Data Modeling with ERD - PowerPoint PPT Presentation


  • 125 Views
  • Uploaded on

Data Modeling with ERD. BUS 782. Entities. An entity is a person, place, object, event, or concept in the user environment about which the organization wishes to maintain data. Person: Employee, Student, patient Place: Warehouse, Store Object: Product, Machine.

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 'Data Modeling with ERD' - london


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
entities
Entities
  • An entity is a person, place, object, event, or concept in the user environment about which the organization wishes to maintain data.
    • Person: Employee, Student, patient
    • Place: Warehouse, Store
    • Object: Product, Machine.
    • Event: Registration, Sale, Renewal
    • Concept: Account, Course
  • Physical existence:
      • Customer, student, product, etc.
  • Conceptual existence:
      • Bank accounts, sale
entity type
Entity Type
  • A collection of entities that share common properties or characteristics.
  • An entity type represents a collection of entities.
  • A business environment may involve many entity types.
    • University: Faculty, Student, Course
    • Department, Employee, Dependent
    • Sales person, Customer, Order
relationship
Relationship
  • Relationship: Interaction between entity types.
    • Faculty teach Course, Faculty advise Student
    • Customer open Account, Customer purchase Product.
  • Binary relationship: A relationship involves two entity types.
  • Three types of binary relationship:
    • 1:1, 1:M, M:M
entity relationship diagram
Entity-Relationship Diagram
  • ER modeling begins by identifying the entities and relationships between entities that must be represented in the model.
  • In an ERD, an entity type is represented by a rectangle labeled with a singular name.
  • A relationship has a verb phrase name:
    • Faculty teach Course, Faculty advise Student
m m relationship
M:M Relationship

Peter

Paul

John

Woody

Alan

Mary

Linda

Nancy

Mia

Pia

Girl

Boy

A boy may date 0, 1, or many girls.

A girl may date 0, 1, or many boys.

Note: “Many boys date many girls” is not a correct interpretation.

1 1 relationship
1:1 Relationship

Man

Peter

Paul

John

Woody

Alan

Mary

Linda

Nancy

Mia

Pia

Woman

A man may marry 0 or 1 woman.

A woman may marry 0 or 1 man.

1 m relationship
1:M Relationship

Father

Peter

Paul

John

Woody

Alan

Mary

Brian

Linda

Aron

Nancy

Ronald

Mia

Pia

Child

A father has 1 or many children.

A child has 1 father.

other examples
Other Examples
  • 1:1
    • State, State Governor
    • Order, Invoice
  • 1:M
    • Department, Employee
    • Customer, Order
  • M:M
    • Bank customer, Bank account
    • Student, Student organization
erd notations
ERD Notations

1

1

Student

Has

Account

M

M

M

Enroll

Advise

1

M

Faculty

1

Teach

Course

alternative notations
Alternative Notations

Has

Student

Account

Enroll

Advise

Faculty

Teach

Course

attributes
Attributes
  • Properties of an entity or a relationship.
  • Simple attributes vs Composite attributes
    • Address:Street address, City, State, ZipCode
  • Single-valued attributes vs multivalued attributes
  • Derived attributes
  • Primary key
slide13
FullName

DateOfBirth

SID

Lname

Fname

Age

Student

Major

domains of attributes
Domains of Attributes
  • The set of allowable values for one or more attributes.
  • Input validation
  • Examples:
    • Sex: F, M
    • EmpHourlyWage: Between 6 and 300
    • EmpName: 50 charcters
introduction to relational database
Introduction to Relational Database
  • Data is logically structured within relations.
  • Each relation is a table (file) with named columns (attributes, fields) and rows (records).
properties of a relation
Properties of a Relation
  • Simple attribute
    • No composite, no multivalued attribute
  • Each relation must have a primary key:
    • Simple or composite key
    • May have other keys (candidate keys)
    • Key cannot be null
    • Cannot be duplicated
relational database design
Relational Database Design
  • Create a table for each entity that includes all simple attributes
  • Relationship:
    • 1:1, 1:M
      • Relationship table
      • Foreign key
    • M:M: relationship table
database design exampl e
Database Design Example
  • University:
    • 1:1 Relationship:
      • Student and Account
        • What is Peter’s account balance
        • Find students that owe more than 2000 dollars.
    • 1:M Relationship
      • Faculty Advisor and student
        • Who is Peter’s advisor?
        • How many students advised by Chao?
    • M:M Relationship
      • Student and course
        • How many units Peter takes?
        • Find students that are taking 363.
database design example
Database Design Example
  • Bank:
    • Customer, Account, bank employee
database design example1
Database Design Example
  • Order Processing:
    • Customer, Order, Product
slide21
Phone

CID

Phone

Cname

Aname

M

M

Attorney

Case

AID

M

Hours

1

M

1

Judge

Court

JID

Jname

CtID

Room

advanced topics
Advanced Topics
  • Composite key
  • Multivalued attribute
    • Student’s Major attribute
    • Faculty’s DegreeEarned attribute
    • Vehicle’s Color attribute
    • Others: PhoneNumber, EmailAddress
  • Create a table for each multi-valued attribute
      • Key + attribute
  • Attributes on a relation
online shopping cart
Online Shopping Cart

Addr

CartID

Date

CID

Cname

Phone

Has

M

ShoppingCart

Customer

1

M

Has

M

Product

Price

PID

Pname

online shopping cart1
Online Shopping Cart

Addr

CartID

Date

CID

Cname

Phone

Has

M

ShoppingCart

Customer

1

M

Qty

Has

M

Product

Price

PID

Pname

attributes on relationship
Attributes on Relationship
  • Examples:
    • Student/Course: Grade
    • Order/Product: Quantity
ad