1 / 37

CS411 Database Systems

Patman
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

    3. Steps in building a DB application

    4. The ER model is very simple 4 3 main types of concepts: entity sets, relations, attributes no operations3 main types of concepts: entity sets, relations, attributes no operations

    5. Entity real-world object distinguishable from other objects described by its attributes Attribute Has an atomic domain: string, integers, date, Entity set: all have the same set of attributes

    6. Relationships If A, B are sets, then a relation R is a subset of A x B. A = {1, 2, 3} B = {a, b, c, d} R = {(1,a), (1,c), (3,b)} makes is a subset of Product x Company:

    7. plus a bunch of other specifications that cannot be pictorially represented must be specified in Englishplus a bunch of other specifications that cannot be pictorially represented must be specified in English

    8. Exercises 4.1.1 and 4.1.2

    9. Constraint = assertion about the DB that must always be true

    10. Constraints are very important Help us to come up with efficient storage, query processing, etc. Help us keep garbage out of the DB Garbage in, garbage out! GarbageGarbage

    11. Referential integrity: if you refer to something, it actually exists More or less built into the ER model But NOT automatic in other models Examples: Erbana, IL 61801 Brittany Speers The DB equivalent of a dangling pointer Garbage in, garbage out!

    12. Underline the key for each entity set weak entity setsweak entity sets

    13. Sometimes your entity might not seem to have a key

    14. We can show the cardinality of a relationship one-one many-one many-many

    15. How do we model an n-way relationship?

    16. What do arrows mean in n-way relationships? If I know the store, person, and invoice, then there is only one possible movie. VideoStore, Invoice, and Person determines Movie

    17. What if there are several arrows? store, person, invoice determines movie; store, invoice, movie determines person

    18. How do I say invoice determines store? No good way; best approximation: Why is this incomplete?

    19. What if we need an entity set twice in one relationship?

    20. What if we need an entity set twice in one relationship?

    21. Some versions of the ER model allow attributes on relationships not necessary, but usefulnot necessary, but useful

    22. You can upgrade a relationship to be an entity set note the many-one nature of the converted relationshipsnote the many-one nature of the converted relationships

    23. isa is a 1-1 relation the point of the triangle points to the superclass isa structure must be trees (no multiple inheritance)isa is a 1-1 relation the point of the triangle points to the superclass isa structure must be trees (no multiple inheritance)

    24. Subclasses Subclass = special case = fewer entities = more properties. Example: Ales are a kind of beer. Not every beer is an ale, but some are. Let us suppose that in addition to all the properties (attributes and relationships) of beers, ales also have the attribute color.

    25. Example

    26. ER subclasses are different from object oriented subclasses In the object-oriented world, objects are in one class only. Subclasses inherit properties from superclasses. In contrast, E/R entities have components in all subclasses to which they belong. Matters when we convert to relations.

    27. Example

    28. ER Design Principle #1: Model your domain faithfully for instructor teaching course: is it many-one or one-one or many-many? depending on which assumptions we have about the domainfor instructor teaching course: is it many-one or one-one or many-many? depending on which assumptions we have about the domain

    29. Principle #2: Avoid redundancy Dont say the same thing in two different ways. Redundancy wastes space and (more importantly) encourages inconsistency The two instances of the same fact may become inconsistent if we change one and forget to change the other, related version. garbagegarbage

    30. Good

    31. Bad

    32. Bad

    33. Good

    34. Principle #3: Dont overuse entity sets An entity set should satisfy at least one of the following conditions: It is more than the name of something; it has at least one non-key attribute. or It is the many in a many-one or many-many relationship.

    35. Good

    36. Bad

    37. Principle #4: Dont Overuse Weak Entity Sets Beginning database designers often make most entity sets weak, supported by all other entity sets to which they are linked. Instead, we create unique IDs for entity sets. Social-security numbers, drivers license numbers, automobile VINs, Only use weak entity sets when necessary. Example: unique player numbers across all football teams in the world.

More Related