Download to read offline
More Related Content
Similar to Database Design in web and web database design
Database Design in web and web database design
- 1. Lecture 9: Conceptual DatabaseDesign January 27th , 2003
- 2. Building an Applicationwith 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 • Whydo 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/RDiagrams • Every entity set must have a key Product name category price
- 7. address name ssn Person buys makes employs Company Product namecategory stockprice name price
- 8. What is aRelation ? • 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: 1 2 3 a b c d A= B= makes Company Product
- 9. Multiplicity of E/RRelations • one-one: • many-one • many-many 1 2 3 a b c d 1 2 3 a b c d 1 2 3 a b c d
- 10. address name ssn Person buys makes employs Company Product namecategory stockprice name price What does this say ?
- 11. Multi-way Relationships How dowe model a purchase relationship between buyers, products and stores? Purchase Product Person Store Can still model as a mathematical set (how ?)
- 12. Q: what doesthe arrow mean ? A: if I know the store, person, invoice, I know the movie too Rental VideoStore Person Movie Invoice Arrows in Multiway Relationships
- 13. Q: what dothese arrow mean ? A: store, person, invoice determines movie and store, invoice, movie determines person Rental VideoStore Person Movie Invoice Arrows in Multiway Relationships
- 14. Q: how doI say: “invoice determines store” ? A: no good way; best approximation: Q: Why is this incomplete ? Rental VideoStore Person Movie Invoice Arrows in Multiway Relationships
- 15. Roles in Relationships Purchase Whatif we need an entity set twice in one relationship? Product Person Store salesperson buyer
- 16.
- 17. Converting Multi-way Relationships toBinary Purchase Person Store Product StoreOf ProductOf BuyerOf date
- 18. From E/R Diagrams toRelational Schema • Entity set relation • Relationship relation
- 19. Entity Set toRelation Product name category price Product(name, category, price) name category price gizmo gadgets $19.99
- 20. Relationships to Relations makesCompany Product name category Stock price name Makes(product-name, product-category, company-name, year) Product-name Product-Category Company-name Starting-year gizmo gadgets gizmoWorks 1963 Start Year price (watch out for attribute name conflicts)
- 21. Relationships to Relations makesCompany Product name category Stock price name No need for Makes. Modify Product: name category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorks Start Year price
- 22. Multi-way Relationships to Relations Purchase Product Person Store nameprice ssn name name address Purchase( , , )
- 23. 3. Design Principles Purchase ProductPerson What’s wrong? President Person Country Moral: be faithful!
- 24. Design Principles: What’s Wrong? Purchase Product Store date personName personAddr Moral:pick the right kind of entities.
- 25. Design Principles: What’s Wrong? Purchase Product Person Store date Dates Moral:don’t complicate life more than it already is.
- 26. Modeling Subclasses The worldis inherently hierarchical. Some entities are special cases of others • We need a notion of subclass. • This is supported naturally in object-oriented formalisms. Products Software products Educational products
- 27. Product name category price isa isa EducationalProduct Software Product Age Group platforms Subclasses in E/R Diagrams
- 28. Understanding Subclasses • Thinkin terms of records: – Product – SoftwareProduct – EducationalProduct field1 field2 field1 field2 field1 field2 field3 field4 field5
- 29. Subclasses to Relations Product namecategory price isa isa Educational Product Software Product Age Group platforms Name Price Category Gizmo 99 gadget Camera 49 photo Toy 39 gadget Name platforms Gizmo unix Name Age Group Gizmo todler Toy retired Product Sw.Product Ed.Product
- 30. Modeling UnionTypes With Subclasses FurniturePiece Person Company Say:each piece of furniture is owned either by a person, or by a company
- 31. Modeling Union Typeswith Subclasses Say: each piece of furniture is owned either by a person, or by a company Solution 1. Acceptable, imperfect (What’s wrong ?) FurniturePiece Person Company ownedByPerson ownedByPerson
- 32. Modeling Union Typeswith Subclasses Solution 2: better, more laborious isa FurniturePiece Person Company ownedBy Owner isa
- 33. Constraints in E/RDiagrams Finding constraints is part of the modeling process. Commonly used constraints: Keys: social security number uniquely identifies a person. Single-value constraints: a person can have only one father. Referential integrity constraints: if you work for a company, it must exist in the database. Other constraints: peoples’ ages are between 0 and 150.
- 34. Keys in E/RDiagrams address name ssn Person Product name category price No formal way to specify multiple keys in E/R diagrams Underline:
- 35.
- 36. Referential Integrity Constraints Company Productmakes Company Product makes
- 37. Other Constraints Company Product makes <100 Whatdoes this mean ?
- 38. Weak Entity Sets Entitysets are weak when their key comes from other classes to which they are related. University Team affiliation number sport name
- 39. Handling Weak EntitySets University Team affiliation number sport name Convert to a relational schema (in class)