New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here

Information Systems

by: Roman McCullough

Information Systems CMPSCI 445

Roman McCullough
GPA 3.57

Gerome Miklau

Almost Ready


These notes were just uploaded, and will be ready to view shortly.

Purchase these notes here, or revisit this page.

Either way, we'll remind you when they're ready :)

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

Gerome Miklau
Class Notes
25 ?




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


Buy Material

Are you sure you want to buy this material for

25 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


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'

Why people love StudySoup

Bentley McCaw University of Florida

"I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"

Amaris Trozzo George Washington University

"I made $350 in just two days after posting my first study guide."

Steve Martinelli UC Los Angeles

"There's no way I would have passed my Organic Chemistry class this semester without the notes and study guides I got from StudySoup."

Parker Thompson 500 Startups

"It's a great way for students to improve their educational experience and it seemed like a product that everybody wants, so all the people participating are winning."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.