1 / 72

ARCH-3: Database Design, a Practical Guide

ARCH-3: Database Design, a Practical Guide. Click to add subtitle. Gus Bj örklund. Wizard, Progress Software Corporation. Ask questions as we go if I am not being clear. Warning: there is a mistake in these slides. Rules are made to be broken. To every rule, there is an exception!.

august-curry
Download Presentation

ARCH-3: Database Design, a Practical Guide

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. ARCH-3: Database Design, a Practical Guide Click to add subtitle Gus Björklund Wizard, Progress Software Corporation

  2. Ask questions as we goif I am not being clear.Warning: there is a mistake in these slides. ARCH-3: Database Design A Practical Guide

  3. Rules are made to be broken To every rule, there is an exception! ARCH-3: Database Design A Practical Guide

  4. If you thought this talk was going to be about indexing … It isn’t. Nor is it about performance. ARCH-3: Database Design A Practical Guide

  5. Topics • Theory: • What is Database Design • Basic Elements • Representing the Model as Tables • Practice • An Example • Some Other Topics ARCH-3: Database Design A Practical Guide

  6. First, a little theory ARCH-3: Database Design A Practical Guide

  7. What do we mean by database design? • A process for defining a model of a subset of the “real”1 world, then representing it as data in tables in a relational databaseAt least, that’s the definition we will use for the purposes of this talk. 1 Well, for small values of real, anyway. ARCH-3: Database Design A Practical Guide

  8. Basic Elements What do we put in our model? • Just 3 Things: • Entities • Attributes • Relationships The “entity-relationship model” was described by Peter Chen in 1976. See http://bit.csc.lsu.edu/~chen/chen.html ARCH-3: Database Design A Practical Guide

  9. Basic Elements: Entities • Can be thought of as nouns • People • author, composer, performer, seller, buyer • Places • home, IP address, URL, destination, factory, store • Things • song, recording, instrument, car, invoice Is “telephone number” a place or a thing? ARCH-3: Database Design A Practical Guide

  10. Basic Elements: Attributes Entities have attributes • Can be thought of as adjectives (but only loosely): • Length • Color • Horsepower • Part number • Song Title • Publication Date • Size • Fabric • Owner Is “telephone number” a attribute or an entity? ARCH-3: Database Design A Practical Guide

  11. Basic Elements: Relationships Entities are connected by relationships • Can be thought of as verbs: • has a • owns • contains • supervises • performs • called • sold • purchased • proved Is “telephone number” a relationship? ARCH-3: Database Design A Practical Guide

  12. Relationships have attributes too In May, 1995, Andrew Wiles published a proof of Fermat’s Last Theorem ARCH-3: Database Design A Practical Guide

  13. Relationships have attributes too In May, 1995, Andrew Wiles published a proof of Fermat’s Last Theorem attribute entity relationship entity ARCH-3: Database Design A Practical Guide

  14. What goes in an entity • Identifying attributes • Must be able to uniquely identify the entity • Can have more than one way to id • Id can be composite • Descriptive attributes • the values you need to keep track of • generally should be simple, not complex ARCH-3: Database Design A Practical Guide

  15. What to include in your model • The things your application has to keep track of • Telephones, wires, switches • The actions your application or its users perform • Make calls, send telephone bills, collect payments • Some attributes of the things and actions • Originating number, date and time of call, duration, called number • Keep it simple • Be accurate • Keep it up to date ARCH-3: Database Design A Practical Guide

  16. What to include in your model • Consider the goals of the system • Everything you include should be there for a reason you can state • in no more than two sentences • Everything should have a clear name • if you can’t name it, it doesn’t belong • Talk to the stakeholders !!! ARCH-3: Database Design A Practical Guide

  17. What to leave out of your model • The real world has properties that don’t matter (to your application) • The real world has relationships that don’t matter • Things happen in the real world that don’t matter • Keep it simple • If you can’t say why you need it, leave it out ARCH-3: Database Design A Practical Guide

  18. Logical vs Physical Data Models • Logical entities often require multiple tables to represent them • Tables can be thought of as logical or physical • It depends on your point of view • There is also the physical storage database layout • storage areas • data extents • disks • etc. • We aren’t going to talk about the physical database layout • We will talk about tables ARCH-3: Database Design A Practical Guide

  19. Mapping Your Model to a Database Simply put, • Entities become tables • Identifiers become indexes • Attributes become columns • Data types: pick appropriate • Relationships become tables or foreign keys ARCH-3: Database Design A Practical Guide

  20. “In theory, there is no difference betweentheory and practice, but in practice there is.”Jan van de Snepscheut ARCH-3: Database Design A Practical Guide

  21. Now for some practice. ARCH-3: Database Design A Practical Guide

  22. An example • Music store • Buys compact disc recordings from distributors • Has inventory • Allows customers to search for what they want • Maybe in an in-store kiosk or on the web • Sells compact discs to customers ARCH-3: Database Design A Practical Guide

  23. What should we do first? ARCH-3: Database Design A Practical Guide

  24. Activities • We buy discs from a distributor • Orders are sent to a distributor • Orders are delivered to the store • Orders may be cancelled • We sell discs to customers in sales transactions • Customers buy discs in sales transactions • Customers search for what they want to buy Which of these must be remembered by the system? ARCH-3: Database Design A Practical Guide

  25. What do we need to keep track of • Discs we have • Discs we sold • Discs we know about and can get • Discs we have ordered • Information needed to do our income tax • what we paid for stock • when we bought it • what we sold it for • when we sold it ARCH-3: Database Design A Practical Guide

  26. Disc entities • UPC Code: 8697-07416-2 • Manufacturer: Sony BMG • Cost to us: $ 2.00 • Price charged: $ 17.95 • Tax charged: $ 0.80 • Date purchased: March 19, 2007 • Date sold: June 9, 2007 ARCH-3: Database Design A Practical Guide

  27. Disc table might look like this ARCH-3: Database Design A Practical Guide

  28. What’s wrong? • Is upc a unique identifier? • Might have bought from a distributor • Have no information about what is on the disc • How do customers search? • Don’t know when disc was made • Could be more than one tax jurisdiction • provincial tax, city tax • Don’t know if disc is on order • Don’t know who bought it • Duplicated data • Etc., etc. ARCH-3: Database Design A Practical Guide

  29. Disc entities take 2 • UPC Code: 8697-07416-2 • Manufacturer: Sony BMG • Distributor: Bob’s Wholesale CD’s • Cost to us: $ 2.00 • Price charged: $ 17.95 • Tax charged: $ 0.80 • Date ordered: March 19, 2007 • Date received: March 20, 2007 • Date sold: June 9, 2007 • Disc Title: “The Essential Joshua Bell” • Artist: Joshua Bell • Track 1: “Danse Russe” • Track 2: “Violin Concerto in E Minor” • Track 3: “Nocturne in C-sharp Minor” • etc. ARCH-3: Database Design A Practical Guide

  30. Example: Now What’s wrong? • This is getting messy • Activities combined with disc’s attributes • Have duplicated information • How many tracks can there be? • What if there is more than one artist? • Don’t have all the information a customer might want to use to search ARCH-3: Database Design A Practical Guide

  31. Discs revisited • Discs have titles • Discs have pictures on the cover • Discs contain tracks • Discs are made by manufacturers • Discs are purchased from distributors • Discs are ordered from distributors • Discs are delivered to the store • Discs are sold to customers ARCH-3: Database Design A Practical Guide

  32. “Discs contain tracks …” • Tracks contain songs • Tracks occur in order • Tracks have a duration • Songs are performed in performances • Songs have performers (usually) • Songs have composers • Songs have names (titles) • Songs have a key (but not always) • Performances are done by performers • Performers can be groups (bands, orchestras, etc.) • Performances are performed in a location or venue ARCH-3: Database Design A Practical Guide

  33. Discs Manufacturers Distributors Orders Customers Inventory Tracks Songs Performers Groups ? We seem to need these entities ARCH-3: Database Design A Practical Guide

  34. Songs have names (titles).Are names properties of songs?Or are they entities related to songs?Or are they something else? ARCH-3: Database Design A Practical Guide

  35. Song data (track 1) ARCH-3: Database Design A Practical Guide

  36. Song data (track 2) ARCH-3: Database Design A Practical Guide

  37. Performance data ARCH-3: Database Design A Practical Guide

  38. Performance data take 2 ARCH-3: Database Design A Practical Guide

  39. Performer data ARCH-3: Database Design A Practical Guide

  40. Performance to Performer Relationship ARCH-3: Database Design A Practical Guide

  41. Performance data take 3 ARCH-3: Database Design A Practical Guide

  42. Track to Performance Relationship ARCH-3: Database Design A Practical Guide

  43. Relationships (so far): track performance one to one performer performance performance disc performer track performance track one to many many to many track ARCH-3: Database Design A Practical Guide

  44. What happened to Songs? ARCH-3: Database Design A Practical Guide

  45. Relationships (take 2): song performance track song one to many performance one to one performance disc performer track performance track performance one to many performer track performance many to many ARCH-3: Database Design A Practical Guide

  46. disc track performance performer track track performance performer song song song performance Relationships (take 3): ARCH-3: Database Design A Practical Guide

  47. What about“business entities”?Where are they? ARCH-3: Database Design A Practical Guide

  48. disc track performance performer track track performance performer song song song performance Business entities ARCH-3: Database Design A Practical Guide

  49. disc track performance performer track track performance performer song song song performance Business entities ARCH-3: Database Design A Practical Guide

  50. disc track performance performer track track performance performer song song song performance Business entities ARCH-3: Database Design A Practical Guide

More Related