Information Systems CMPSCI 445
Popular in Course
Popular in ComputerScienence
This 35 page Class Notes was uploaded by Roman McCullough on Friday October 30, 2015. The Class Notes belongs to CMPSCI 445 at University of Massachusetts taught by Gerome Miklau in Fall. Since its upload, it has received 29 views. For similar materials see /class/232262/cmpsci-445-university-of-massachusetts in ComputerScienence at University of Massachusetts.
Reviews for Information Systems
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: 10/30/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 ER Model Des1gn Relational Schema Logical plus Integrity design Constraints 1 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 39 011601162 manyone Multiplicity 0f ER Relations 4ltgt O O D V manymany Q V Product makes 72gt A product is made by at most one company Person Company 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 Arrows in Multiway Relationships Q what does the arrow mean Person A if I know the store person invoice I know the movie too Arrows in Multiway Relationships Q What do these arrow mean Person A store person invoice determines movie and store invoice movie determines person 13 Roles in Relationships What if we need an entity set twice in one relationship Product Purchase Store salesperson buyer Person 14 Attributes on Relationships Product Purchase Store Person Subclasses Product Educational Product Design Principles What s wrong Product Person Design Principles What s Wrong Product Purchase Store Moral pick the right kind of entities 18 Design Principles What s Wrong Dates Product Purchase Store Moral don t complicate life unnecessarily Person 19 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 21 Product Makesmroductname productcategory companvname year watch out for attribute name con icts 22 Product No need for Makes Modify Product Productname categogy price startYear companyName 2 3 Multiway Relationships to Product Relations 39 Purchase Store I PurehaseprodNamestName I Person 24 rd Product Educational Product A W Price Category Gizmo 99 gadget Camera 49 photo Toy 39 gadget SWPr0duct m platforms Gizmo uniX r EdProduct m Age Group Gizmo todler Toy 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 to 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 987 654321 9085552121 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 R1A1 An B1 Bm R2A1 An C1 Cp 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 Sometimes it is not Lossy Decomposition 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'