1 / 96

Lecture 02: Conceptual Design

Lecture 02: Conceptual Design. Wednesday, October 6, 2010. Nulls. count(category ) != count(*) WHY ? Office hours: Thursdays, 5-6pm. Announcements. Homework 2 is posted: due October 19 th You need to create tables, import data: On SQL Server, in your own database, OR

raiden
Download Presentation

Lecture 02: Conceptual 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. Lecture 02:Conceptual Design Wednesday, October 6, 2010 Dan Suciu -- CSEP544 Fall 2010

  2. Nulls • count(category) != count(*) WHY ? • Office hours: Thursdays, 5-6pm Dan Suciu -- CSEP544 Fall 2010

  3. Announcements • Homework 2 is posted: due October 19th • You need to create tables, import data: • On SQL Server, in your own database, OR • On postgres (we will use it for Project 2) • Follow Web instructions for importing data • Read book about CREATE TABLE, INSERT, DELETE, UPDATE Dan Suciu -- CSEP544 Fall 2010

  4. Discussion SQL Databases v. NoSQL Databases, Mike Stonebraker • What are “No-SQL Databases” ? • What are the two main types of workloads in a database ? (X and Y) • How can one improve performance of X ? • Where does the time of a single server go ? • What are “single-record transactions” ?

  5. Outline • E/R diagrams • From E/R diagrams to relations Dan Suciu -- CSEP544 Fall 2010

  6. 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 • Several formalisms exists • We discuss E/R diagrams Dan Suciu -- CSEP544 Fall 2010

  7. Entity / Relationship Diagrams Objects  entities Classes  entity sets Attributes: Relationships - first class citizens (not associated with classes) - not necessarily binary Product address buys Dan Suciu -- CSEP544 Fall 2010

  8. Company Product Person

  9. prod-ID category name price Company Product stockprice Person name ssn address

  10. prod-ID category name price makes stockprice buys employs name ssn address Company Product Person

  11. Keys in E/R Diagrams • Every entity set must have a key • May be a multi-attribute key: prod-ID category prod-ID cust-ID price date Product Order Dan Suciu -- CSEP544 Fall 2010

  12. 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  B • A={1,2,3}, B={a,b,c,d}, A  B = {(1,a),(1,b), . . ., (3,d)} R = {(1,a), (1,c), (3,b)} - makes is a subset of Product  Company: makes Product Company

  13. 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 Dan Suciu -- CSEP544 Fall 2010

  14. Notation in Class v.s. the Book In class: makes Product Company In the book: makes Product Company

  15. prod-ID category name price makes Company Product stockprice What doesthis say ? buys employs Person name ssn address

  16. Product Purchase Store Person Multi-way Relationships date Dan Suciu -- CSEP544 Fall 2010

  17. Converting Multi-way Relationships to Binary ProductOf date Product Purchase StoreOf Store BuyerOf Person Arrows are missing:which ones ?

  18. 3. Design Principles What’s wrong? Purchase Product Person President Country Person Dan Suciu -- CSEP544 Fall 2010 Moral: be faithful!

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

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

  21. From E/R Diagramsto Relational Schema • Entity set  relation • Relationship  relation Dan Suciu -- CSEP544 Fall 2010

  22. Entity Set to Relation prod-ID category price Product Product(prod-ID, category, price) Dan Suciu -- CSEP544 Fall 2010

  23. Create Table (SQL) CREATE TABLE Product ( prod-ID CHAR(30) PRIMARY KEY, category VARCHAR(20), price double) Dan Suciu -- CSEP544 Fall 2010

  24. Relationships to Relations prod-ID cust-ID date name date Shipment Shipping-Co Orders Shipment(prod-ID,cust-ID, name, date) address Dan Suciu -- CSEP544 Fall 2010

  25. Create Table (SQL) CREATE TABLE Shipment( name CHAR(30) REFERENCES Shipping-Co, prod-ID CHAR(30), cust-ID VARCHAR(20), date DATETIME, PRIMARY KEY (name, prod-ID, cust-ID), FOREIGNKEY (prod-ID, cust-ID) REFERENCES Orders ) Dan Suciu -- CSEP544 Fall 2010

  26. Multi-way Relationships to Relations address name Product Purchase Store price prod-ID How do we representthat in a relation ? Person ssn name Dan Suciu -- CSEP544 Fall 2010

  27. Modeling Subclasses Products Software products Educational products Dan Suciu -- CSEP544 Fall 2010

  28. Subclasses name category price Product isa isa Software Product Educational Product platforms Age Group Dan Suciu -- CSEP544 Fall 2010

  29. field1 field1 field1 field2 field2 field2 Understanding Subclasses • Think in terms of records: • Product • SoftwareProduct • EducationalProduct field3 field4 field5

  30. name category price Product isa isa Software Product Educational Product platforms Age Group Product Subclasses to Relations Sw.Product Ed.Product Dan Suciu -- CSEP544 Fall 2010

  31. FurniturePiece Company Person Modeling UnionTypes With Subclasses Say: each piece of furniture is owned either by a person, or by a company Dan Suciu -- CSEP544 Fall 2010

  32. Company Person FurniturePiece ownedByPerson ownedByPerson Modeling Union Types with Subclasses Say: each piece of furniture is owned either by a person, or by a company Solution 1. Acceptable (What’s wrong ?) Dan Suciu -- CSEP544 Fall 2010

  33. Modeling Union Types with Subclasses Solution 2: More faithful Company Owner isa isa ownedBy Person FurniturePiece Dan Suciu -- CSEP544 Fall 2010

  34. Constraints in E/R Diagrams 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. Dan Suciu -- CSEP544 Fall 2010

  35. Keys in E/R Diagrams name category Underline: price Product v.s. Multi-attribute key Multiple keys Not possible in E/R

  36. Single Value Constraints makes v. s. makes Dan Suciu -- CSEP544 Fall 2010

  37. Referential Integrity Constraints makes Product Company Each product made by at most one company. Some products made by no company makes Product Company Each product made by exactly one company.

  38. Other Constraints makes <100 Product Company What does this mean ? Dan Suciu -- CSEP544 Fall 2010

  39. Weak Entity Sets Entity sets are weak when their key comes from other classes to which they are related. affiliation Team University sport number name Notice: we encountered this when convertingmultiway relationships to binary relationships

  40. Handling Weak Entity Sets affiliation Team University sport number name How do we represent this with relations ? Dan Suciu -- CSEP544 Fall 2010

  41. Weak Entity Sets Weak entity set = entity where part of the key comes from another sport affiliation Team University name number Convert to a relational schema (in class)

  42. What Are the Keys of R ? A B R H S C T W U V V L K F E Q G Z D

  43. Design Theory Dan Suciu -- CSEP544 Fall 2010

  44. Schema Refinements = Normal Forms • 1st Normal Form = all tables are flat • 2nd Normal Form = obsolete • Boyce Codd Normal Form = will study • 3rd Normal Form = see book Dan Suciu -- CSEP544 Fall 2010

  45. First Normal Form (1NF) • A database schema is in First Normal Form if all tables are flat Student Student Takes Course May needto add keys

  46. name buys Person Product price name ssn Relational Schema Design Conceptual Model: Relational Model: plus FD’s Normalization: Eliminates anomalies Dan Suciu -- CSEP544 Fall 2010

  47. Data Anomalies When a database is poorly designed we get anomalies: Redundancy: data is repeated Updated anomalies: need to change in several places Delete anomalies: may lose data when we don’t want Dan Suciu -- CSEP544 Fall 2010

  48. Relational Schema Design Recall set attributes (persons with several phones): One person may have multiple phones, but lives in only one city Anomalies: • Redundancy = repeat data • Update anomalies = Fred moves to “Bellevue” • Deletion anomalies = Joe deletes his phone number: what is his city ?

  49. Relation Decomposition Break the relation into two: Anomalies have gone: • No more repeated data • Easy to move Fred to “Bellevue” (how ?) • Easy to delete all Joe’s phone number (how ?)

  50. Relational Schema Design(or Logical Design) Main idea: • Start with some relational schema • Find out its functional dependencies • Use them to design a better relational schema Dan Suciu -- CSEP544 Fall 2010

More Related