1 / 31

CS122 Using Relational Databases and SQL Introduction to Entity-Relationship Diagram

CS122 Using Relational Databases and SQL Introduction to Entity-Relationship Diagram. Chengyu Sun California State University, Los Angeles. Some Restaurant. Terminal ID: NC2HHRY Merchant ID: 4492414532566624 VISA ***********1234. srv:1. SALE Batch: 000244 Date: JUN 17, 06 Time: 18:44.

colon
Download Presentation

CS122 Using Relational Databases and SQL Introduction to Entity-Relationship Diagram

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS122 Using Relational Databases and SQLIntroduction to Entity-Relationship Diagram Chengyu Sun California State University, Los Angeles

  2. Some Restaurant Terminal ID: NC2HHRY Merchant ID: 4492414532566624 VISA ***********1234 srv:1 SALE Batch: 000244 Date: JUN 17, 06 Time: 18:44 inv:000032 AUTH:00559B Base: $36.70 Tip: Total: Chengyu Sun Database Design Problem in Real World Tables in RDBM ?

  3. Entity-Relationship (ER) Model • Sort of an object-oriented approach • A graphical representation of the design – ER Diagram • Easily converted to relational model Problem ER Model Tables

  4. Sample Problem • Design a database to keep track information about bars, beers, and drinkers • Beers – name, manufacturer • Name, manufacturer • Bars – name, address • Bars sell beers • Drinkers – name, address • Drinkers go to bars and likes beers

  5. ER Diagram name addr name manf Bars Beers Sells Entity Set Attribute Frequents Likes Relationship Drinkers name addr

  6. Entity Set and Attributes • Entity Set is a collection of entities • E.g. Bars, Beers, Drinkers, Products, … • Attributes are the properties of an entity set • For example • Attributes of Bars: name, address • Attributes of Products: id, description, category, price • Must have simple values like numbers or strings

  7. Instances of An Entity Set Entity Set Instances of the Entity Set name manf (Bud, Anheuser-Busch) (Miller, Miller Brewing) (Bud Lite, Anheuser-Busch) Beers name addr (Joe’s Bar, 113 Main St) (Sue’s Bar, 20 East St) Bars

  8. Relationship name addr name manf Bars Beers Sells

  9. Types of Relationships • Many-to-Many • Many-to-One (One-to-Many) • One-to-One

  10. Many-to-Many Relationship • Sells is a many-to-many relationship • One bar can sell many beers • One beer can be sold in many bars Bars Beers Sells

  11. Many-to-One Relationship • Favorite is a many-to-one relationship • One drinker only has one favorite beer • One beer can be the favorites of many drinkers • An arrow is used to indicates the “one” side Drinkers Beers Favorite

  12. One-to-One Relationship • Bestseller is a one-to-one relationship • One manufacturer only has one bestselling beer • One beer can only be the bestseller of one manufacturer • Arrows on both sides Manufacturers Beers Bestseller

  13. Relationship Examples students Take classes teachers Teach classes customers Place orders orders Include products

  14. Attributes of Relationships • Sometimes it’s useful to attach an attribute to a relationship. Sells Bars Beers price

  15. Married Drinkers Roles • An entity set may appear in the same relationship more than once. • Label the edges with names called Roles husband wife

  16. Another Way to Look at Roles Husband (Drinkers) Married Wife (Drinkers)

  17. Keys • A key is an attribute or a set of attributes that uniquely identify an entity in an entity set. • Each entity set must have a key • If there are multiple keys, choose one of them as the primary key

  18. Keys in ER Diagram name cin ssn id category desc Students Products What’s the key for Classes?? code title quarter section# Classes

  19. Design the Store Database • Keep track information about • Products • Customers • Orders

  20. Design #1: Order as an Entity Set Products Customers Include Place Orders

  21. Design #2: Order as a Relationship Order Customers Products

  22. Quick Summary about ER Diagram • Entity Sets • Attributes • Must have simple values • Keys • Relationships • Many-to-many, many-to-one, one-to-one • Attributes

  23. Alternative Notations and Things We Won’t Cover Different ways to represent relationships Weak entity set 1 n Subclass 0 n Multi-way Relationship

  24. Convert ER Diagram to Relations • Entity sets • Relationships

  25. Converting Entity Sets name addr Drinkers Drinkers( name, addr ) name manf Beers Beers( name, manf )

  26. Converting Relationships name addr name manf Sells Bars Beers price Sells( bar_name, beer_name, price )

  27. Converting Relationships – General Rules • The resulting relation includes • All key attributes from the entity sets involved in the relationship • All the attributes of the relationship itself

  28. Converting Relationships – Combining Relations • The relations converted from many-to-one and one-to-one relationships can be absorbed into the relation of the “many” side.

  29. Example of Combining Tables name addr name manf Favorite Drinker Beers Drinkers (name, addr ) Beers (name, manf ) Favorite (drinker_name, beer_name ) Drinkers (name, addr, favorite_beer_name ) Beers (name, manf )

  30. The Store Database • Convert the ER diagram from Design #1 • Convert the ER diagram from Design #2 • Which one is better??

  31. Normal Forms • Formal ways to evaluate the “goodness” of a database design • Covered in CS422

More Related