Data modeling with erd
This presentation is the property of its rightful owner.
Sponsored Links
1 / 26

Data Modeling with ERD PowerPoint PPT Presentation


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

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.

Download Presentation

Data Modeling with ERD

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


Data modeling with erd

Data Modeling with ERD

BUS 782


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


    Data modeling with erd

    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


    Data modeling with erd

    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


    Order form

    Order Form


    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


  • Login