lec09.366

lec09.366 - . . Winter 2008 CPE/CSC 366: Database Modeling,...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: . . Winter 2008 CPE/CSC 366: Database Modeling, Design and Implementation Alexander Dekhtyar . . Theory of Normal Forms Examples Database Anomalies Consider the following relation Purchases : Purchases(ReceiptNo,Ordinal, PDate, Customer, Item, Price) Each tuple of the relation stores information about a single item purchased by a customer. The information stored is the receipt number, the position of the item on the receipt (item scanned first has Ordinal = 1 , next item Ordinal = 2 , etc.), the date of the purchase, the name of the customer, the name of the purchased item and its price. Consider the following fragment of the Purchases table. ReceiptNo Ordinal PDate Customer Item Price 1234 1 01/21/2008 "Fausto Klosner" "Chocolate Chip Cookie" 0.95 1234 2 01/21/2008 "Fausto Klosner" "Rye Bread" 2.50 3042 1 01/23/2008 "Susie Gladney" "Rye Bread" 2.50 3042 2 01/23/2008 "Susie Gladney" "Opera Cake" 15.00 3403 1 01/26/2008 "Susie Gladney" "Ganache Cookie" 1.30 5612 1 01/26/2008 "Dean Dews" "Chocolate Chip Cookie" 0.95 1564 1 01/21/2008 "Fausto Klosner" "Ganache Cookie" 1.30 1564 2 01/21/2008 "Fausto Klosner" "Coffee" 1.50 Example 1: Redundancy. Some information stored in the table Purchases is redundant. For example, the receipt number ( ReceiptNo ) uniquely identifies the date of purchase. Yet, the date of purchase is repeated in multiple tuples for the same receipt number. Similarly, the price is uniquely determined by the item purchased. This information also appears in many tuples. Example 2: Update Anomaly. A database application program can exe- cute an update: UPDATE Purchases SET Price = Price+ .2 WHERE ReceiptNo = 1234 AND Ordinal = 2; 1 This update will make the price of "Rye Bread" purchased by "Fausto Klosner" on 01/21/2008 to 2 . 70. However, the price of the same item pur- chased by "Susie Gladney" on 01/23/2008 will remain 2 . 50....
View Full Document

Page1 / 5

lec09.366 - . . Winter 2008 CPE/CSC 366: Database Modeling,...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online