1 / 31

CS411 Database Systems

Announcements. Project stage 0 is due todayGrade breakdown of the projectStage 1 (Decide your application): 5%Stage 2 (ER modeling) : 5%Stage 3 (Relational schema design): 10%Stage 4 (Demo for basic functions) : 10%Stage 5 (Final demo): 70%Graduate project stage 0 is posted.Due on Sep. 25DB

lynn
Download Presentation

CS411 Database Systems

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. CS411 Database Systems Kazuhiro Minami

    2. Announcements Project stage 0 is due today Grade breakdown of the project Stage 1 (Decide your application): 5% Stage 2 (ER modeling) : 5% Stage 3 (Relational schema design): 10% Stage 4 (Demo for basic functions) : 10% Stage 5 (Final demo): 70% Graduate project stage 0 is posted. Due on Sep. 25 DBMS accounts for Oracle and MySQL are available

    3. Exercise 4.2.5 (Multiway relationships)

    4. Exercise 4.2.6

    6. Why do we need both the ER and relational models? Relational model has just a single concept: tables well-suited for efficient manipulations on computers ER model is richer: entities, relations, attributes, etc. well-suited for capturing application requirements not so well-suited for computer implementation (no query language)

    8. Each attribute has a type: its domain Integer, string, date, real Traditionally domains were not user-definable, e.g., map Domains must be atomic (why? see later)

    9. The relations schema is its metadata

    10. The relations instance = the data in it

    11. We can write a schema concisely: Product(Name, Price, Category, Manufacturer) DB schema = finite set of relation schemas. Product(Name, Price, Category, Manufacturer), Vendor(Name, Address, Phone), .

    12. Now the fun part: translating from an ER diagram to the relational model

    13. How to convert a E/R diagram to a set of relations?

    14. How to translate an ER diagram to a relational schema Basic cases Entity set E = relation with attributes of E Relationship R = relation with attributes being keys of related entity sets + attributes of R Special cases Combining two relations Translating weak entity sets Translating is-a relationships and subclasses

    15. How to convert an entity set to a relation

    16. How to convert a relationship to a relation

    18. Sometimes it is best to combine two relations - Q: Why not also merge Company to Product? - Q: Why not also merge Company to Product?

    19. It is OK to combine the relation for an entity set E with the relation for a many-one relationship from E to another entity set. Drinkers(name, addr) Favorite(drinker, beer) Drinkers(name, addr, favoriteBeer) What if each drinker could have several favorite beers?

    20. Combining a many-to-many relationship causes trouble Companies(name, addr) MakerOf(companyName, productType)

    21. How to convert a weak entity set to a relation

    22. Exercise 4.5.1

    23. How to translate a subclass

    24. Three ways to translate subclasses Object-oriented: each entity belongs to exactly one class; create a relation for each class, with all its attributes. E/R style: create one relation for each subclass, with only the key attribute(s) and attributes attached to that entity set; Use null: create one relation; entities have nulls in attributes that dont belong to them.

    25. Option #1: the OO Approach

    26. Option #2: the E/R Approach

    27. Option #3: The Null Value Approach

    28. Example

    29. Object Oriented

    30. E/R Style

    31. Using Nulls

    32. Comparisons O-O approach good for queries like find the color of ales made by Petes. Just look in Ales relation. E/R approach good for queries like find all beers (including ales) made by Petes. Just look in Beers relation. Using nulls saves space unless there are lots of attributes that are usually null.

More Related