Presentation on theme: "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."— Presentation transcript: 1 Data Modeling with ERD BUS 782 Show
2 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 3 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 4 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 5 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 6 M:M Relationship Peter
Paul John Woody Alan Mary Linda Nancy Mia Pia 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. Boy Girl 7 1:1 Relationship Peter Paul John Woody Alan Mary Linda Nancy Mia Pia A man may
marry 0 or 1 woman. A woman may marry 0 or 1 man. Man Woman 8 1:M Relationship Peter Paul John Woody Alan Mary Brian Linda Aron Nancy Ronald Mia Pia A father has 1 or many children. A child has 1 father. Father Child
9 Other Examples 1:1 –State, State Governor –Order, Invoice 1:M –Department, Employee –Customer, Order M:M –Bank customer, Bank account –Student, Student organization
10 ERD Notations Student Account Faculty Course Has 11 Enroll MM Advise M 1 Teach M 1 11 Alternative
Notations Student Account Faculty Course Has Teach Enroll Advise 12 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
13 Student SID Fname Lname DateOfBirth Age FullName Major 14 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 15 Introduction to Relational Database Data is logically structured within relations. Each relation is a table (file) with named columns (attributes, fields) and rows (records).
16 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 17 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 18 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. 19 Database Design Example Bank: –Customer, Account, bank employee
20 Database Design Example Order Processing: –Customer, Order, Product 21 AttorneyCase MM CourtJudge
AID Aname Phone CID Cname JID Jname M 1 1M CtID Room Hours Phone 22 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 23 Online Shopping Cart Customer
ShoppingCart Product Has 1 M M M CID Cname Addr CartIDDate PID Pname Price Phone 24 Order Form 25 Online Shopping Cart
Customer ShoppingCart Product Has 1 M M M CID Cname Addr CartIDDate Qty PID Pname Price Phone 26 Attributes on Relationship Examples: –Student/Course: Grade –Order/Product: Quantity
27 Suggested Readings http://en.wikipedia.org/wiki/Entity- relationship_model http://faculty.babson.edu/osborn/mis7510/r eadings/erd.htmhttp://faculty.babson.edu/osborn/mis7510/r eadings/erd.htm
http://dhdurso.org/articles/database- design.htmlhttp://dhdurso.org/articles/database- design.html http://knol.google.com/k/conceptual- database-design# Is a person place object event or concept in the user environment about which company wishes to maintain the data?An entity is a person, place, object, event, or concept in the user environment about which the organization wishes to maintain data.
Is a person place object event or concept in the user environment?An attribute is a person, place, object, event in a user environment.
Is a named property or characteristic of an entity that is of interest to the organization?Attribute: A named property or characteristic of an entity type that is of interest to the organization. Identifier/Primary Key: An attribute (or set or attributes) that uniquely identifies individual instances of an entity type.
Which of the following is a term for an entity that depends on another entity for its meaning?A weak entity is an entity that depends on the existence of another entity. In more technical terms it can be defined as an entity that cannot be identified by its own attributes.
|