Class Note for CMPSCI 445 at UMass(2)
Class Note for CMPSCI 445 at UMass(2)
Popular in Course
Popular in Department
This 35 page Class Notes was uploaded by an elite notetaker on Friday February 6, 2015. The Class Notes belongs to a course at University of Massachusetts taught by a professor in Fall. Since its upload, it has received 13 views.
Reviews for Class Note for CMPSCI 445 at UMass(2)
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 02/06/15
Data Modeling CMPSCI 445 Fall 2008 Exercise Design a schema to store the data used by iTunes or a similar application Things to keep in mind Can all necessary information be represented Are properties represented in more than one place Are common operations ef cient iTunes information Database includes songs artists albums album artwork playlists Sample elds include song name artist year albumName trackNum genre lastPlayedDate playCount albumArtwork songRating albumRating albumReleaseDate artistDOB playLists an ordered list of songs Relational Schema Design Conceptual product ER Model DeSIgn i Relational Schema Logical plus Integrity design Constraints quot 1 L K Schema Normalized schema Refinement Entity Relationship Diagrams Entity sets Product Attributes Relationships Keys in ER Diagrams Every entity set must have a key q Product Multiplicity 0f ER Relations manyone C manymany ga q Product Company k A product is made by at most one company Person Referential Integrity Constraints Product Each product made by at most one company Company Some products made by no company Product Each product made by exactly one company Company 10 Multiway Relationships Product Purchase Store Person 11 Arrows in Multiway Relationships Q what does the arrow mean Invoice Video Store enta Movie Person A if I know the store person invoice I know the movie too Arrows in Multiway Relationships Q What do these arrow mean Invoice Video Store enta Movie Person A store person invoice determines movie and store invoice movie determines person 13 Roles in Re lationships What if we need an entity set twice in one relationship Product Purchase salesperson Store buyer Person l4 Attributes on Relationships Product Purchase Store Person 15 Subclasses Product Software Product Educational Product Design Principles What s wrong Product Person 17 Design Principles What s Wrong Product Purchase Store Moral pick the right kind of entities 18 Design Principles What s Wrong Product Dates Purchase Moral don t complicate life unnecessarily Store Person l9 From ER Diagrams to a Relational Schema Entity set 9 relation Relationship 9 relation 20 Entity Set to Relation Product Productname category price name category price gizmo gadgets 1999 2 1 A I I Relationships to Relations Makesproductname productcategory companvname year watch out for attribute name con icts 22 A I I Relationships to Relations No need for Makes Modify Product Productname categogy price startYear companyName 23 Multiway Relationships to Product Relations Purchase Store PurchaseprodNamestName Person 24 Product Educational Product Name Price Category Gizmo 99 gadget Camera 49 photo Toy 39 gadget SWProduct Name platforms Gizmo uniX EdProduct Age m Group Gizmo todler Toy 25 retired Normalization 26 Evils of Redundancy When a database schema is poorly designed we get anomalies Redundancy is at the root of several problems associated with relational schemas Redundant storage data iS repeated Update anomalies need to change in several places Insertion anomalies may not be able to add data we want to Deletion anomalies may lose data when we don t want to Anomalies Hourlyemps name lot rating hourywages hoursworked Suppose hourly wages is determined by rating rating gt hourywages Redundant storage association between rating 8 and hourly wages 1O repeated 3 times Update anomalies hourywages updated in first tuple but not second lnsertion anomalies must know hourywage for rating value Deetion anomalies delete all tuples with certain rating value lost assoc Can null values fix problems Not really Insertion anomaly What it we know rating and hourlywages for some rating but there is no employee with that rating No ssn can t be null Deletion anomaly It last employee with some rating and hourlywages value is deleted replace with nulls No ssn can t be null 29 Schema Refinement Integrity constraints in particular functional dependencies can be used to identify schemas with such problems and to suggest refinements Main refinement technique decomposition replacing ABCD with say AB and BCD orACD and ABD Decomposition should be used judiciously Is there reason to decompose a relation What problems if any does the decomposition cause 29 Data Anomalies Persons may have several phones Name SSN PhoneNumber City Fred 123456789 2065551234 Seattle Fred 123456789 2065556543 Seattle Joe 987654321 9085552121 Westfield Anomalies Redundancy repeat data Update anomalies Fred moves to Bellevue Deletion anomalies Joe deletes his phone number what is his city SSN 9 Name City but not SSN 9 PhoneNumber Relation Decomposition Break the relation into two Name SSN PhoneNumber City Fred 123456789 2065551234 Seattle Fred 123456789 2065556543 Seattle Joe 987654321 9085552 12 1 Westfield Name SSN City SSN PhoneNumber Fred 123456789 Seattle 123456789 2065551234 Joe 987654321 Westfield 123456789 2065556543 987654321 9085552121 Anomalies have gone No more repeated data Easy to move Fred to Bellevue how Easy to delete all Joe s phone number how 32 Decompositions in General RA1 An B1 Bm C1 Cp F1A1 An B1 Bm R2A1 An C1 R1 projection of R on A1 An B1 Bm R2 projection of R on A1 An C1 Op 33 Lossless Decomposition Sometimes it is correct Name Price Category Gizmo 1999 Gadget OneCIick 2499 Camera Gizmo 1999 Camera Name Price Name Category Gizmo 1999 Gizmo Gadget OneCIick 2499 OneCIick Camera Gizmo Camera 34 Lossy Decomposition Sometimes it is not Name Price Category Gizmo 1999 Gadget OneCIick 2499 Camera Gizmo 1999 Camera Name CategOI y Price Category Gizmo Gadget 1999 Gadget OneCIick Camera 2499 Camera Gizmo Camera 1999 Camera
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'