1 / 25

Conceptual Database Design

Conceptual Database Design. Building an Application with a DBMS. Requirements modeling (conceptual, pictures) Decide what entities should be part of the application and how they should be linked. Schema design and implementation Decide on a set of tables, attributes.

nevada-kirby
Download Presentation

Conceptual Database Design

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. Conceptual Database Design

  2. Building an Application with a DBMS • Requirements modeling (conceptual, pictures) • Decide what entities should be part of the application and how they should be linked. • Schema design and implementation • Decide on a set of tables, attributes. • Define the tables in the database system. • Populate database (insert tuples). • Write application programs using the DBMS • way easier now that the data management is taken care of.

  3. Database Design • Why do we need it? • Agree on structure of the database before deciding on a particular implementation. • Consider issues such as: • What entities to model • How entities are related • What constraints exist in the domain • How to achieve good designs

  4. Database Design Formalisms 1. Object Definition Language (ODL): • Closer in spirit to object-oriented models • I don’t teach it anymore. 2. Entity/Relationship model (E/R): • More relational in nature. • Both can be translated (semi-automatically) to relational schemas • ODL to OO-schema: direct transformation (C++ or Smalltalk based system).

  5. 2. Entity / Relationship Diagrams Entities Attributes Relationships between entities Product address buys

  6. Keys in E/R Diagrams • Every entity set must have a key name category price Product

  7. name category name price makes Company Product stockprice buys employs Person name ssn address

  8. makes Company Product 1 a b 2 A= c 3 B= d What is a Relation ? • A mathematical definition: • 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:

  9. 1 2 3 1 2 3 1 2 3 a b c d a b c d a b c d Multiplicity of E/R Relations • one-one: • many-one • many-many

  10. name category name price makes Company Product stockprice What doesthis say ? buys employs Person name ssn address

  11. Product Purchase Store Person Multi-way Relationships How do we model a purchase relationship between buyers, products and stores? Can still model as a mathematical set (how ?)

  12. Invoice VideoStore Rental Movie Person Arrows in Multiway Relationships Q: what does the arrow mean ? A: if I know the store, person, invoice, I know the movie too

  13. Arrows in Multiway Relationships Q: what do these arrow mean ? A: store, person, invoice determines movie and store, invoice, movie determines person Invoice VideoStore Rental Movie Person

  14. Invoice VideoStore Rental Movie Person Arrows in Multiway Relationships Q: how do I say: “invoice determines store” ? A: no good way; best approximation: Q: Why is this incomplete ?

  15. Roles in Relationships What if we need an entity set twice in one relationship? Product Purchase Store buyer salesperson Person

  16. Attributes on Relationships date Product Purchase Store Person

  17. Converting Multi-way Relationships to Binary ProductOf date Product Purchase StoreOf Store BuyerOf Person

  18. From E/R Diagramsto Relational Schema • Entity set  relation • Relationship  relation

  19. Entity Set to Relation name category price Product Product(name, category, price) name category price gizmo gadgets $19.99

  20. Relationships to Relations price name category Start Year name makes Company Product Stock price Makes(product-name, product-category, company-name, year)Product-name Product-Category Company-name Starting-year gizmo gadgets gizmoWorks 1963 (watch out for attribute name conflicts)

  21. Relationships to Relations price name category Start Year name makes Company Product Stock price No need for Makes. Modify Product: name category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorks

  22. Multi-way Relationships to Relations address name Product Purchase Store price name Person Purchase( , , ) ssn name

  23. 3. Design Principles What’s wrong? Purchase Product Person President Country Person

  24. Design Principles:What’s Wrong? date Product Purchase Store Moral: pick the right kind of entities. personAddr personName

  25. Design Principles:What’s Wrong? date Dates Product Purchase Store Moral: don’t complicate life more than it already is. Person

More Related