L07-FD - L7: Dependency Theory x Functional dependencies x...

Info iconThis preview shows pages 1–6. 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

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

View Full DocumentRight 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: L7: Dependency Theory x Functional dependencies x Closure of attribute sets x Closure of a set of dependencies x Minimal cover The Evils of Redundancy h R e d u n d a n c y is at the root of several problems associated with relational schemas: b r e d u n d a n t s t o r a g e , i n s e r t / d e l e t e / u p d a t e a n o m a l i e s r e d u n d a n t s t o r a g e , i n s e r t / d e l e t e / u p d a t e a n o m a l i e s r e d u n d a n t s t o r a g e , i n s e r t / d e l e t e / u p d a t e a n o m a l i e s r e d u n d a n t s t o r a g e , i n s e r t / d e l e t e / u p d a t e a n o m a l i e s h Consider relation obtained from Hourly_Emps: ourly_Emps ( DBMS-L7: FD -- 2 Dependency Theory b Hourly_Emps ( e i d , n a m e , o f f i c e , r a t i n g , h r l y _ w a g e s , h r s _ w o r k e d ) h N o t a t i o n : We will denote this relation schema by listing the attributes: ENORWH b This is really the s e t of attributes {E,N,O,R,W,H}. b Sometimes, we will refer to all attributes of a relation by using the relation name. (e.g., Hourly_Emps for ENORWH) Anomalies x IC: the h o u r l y _ w a g e is determined by r a t i n g . r U p d a t e a n o m a l y : Can we change W in just the 1st tuple of ENORWH? r I n s e r t i o n a n o m a l y : What if we want to insert an employee and don’t know the hourly wage for his rating? / What if we want to record the hourly wage for a rating that no employee has currently? DBMS-L7: FD -- 3 Dependency Theory E N O R W H 123-22-3666 A ttishoo 48 8 10 40 231-31-5368 Smiley 22 8 10 30 131-24-3650 Smethurst 35 5 7 30 434-26-3751 G uldu 35 5 7 32 612-67-4134 M adayan 35 8 10 40 r D e l e t i o n a n o m a l y : If we delete all employees with rating 5, we lose the information about the wage for rating 5! Refining Relational Schema h Relational database design requires that we find a “ good ” collection of relation schemas. A bad design may lead to b Repetition of information. ability to represent certain information. DBMS-L7: FD -- 4 Dependency Theory b Inability to represent certain information. h Design Goals: b Avoid redundant data b Ensure that relationships among attributes are represented b Facilitate the checking of updates for violation of database integrity constraints Decomposition h Main refinement technique: d e c o m p o s i t i o n (replacing ENORWH with ENORH and RW) h Decomposition should be used judiciously: there a reason to E N O R H 123-22-3666 Attishoo 48 8 40 231-31-5368 Smiley 22 8 30 131-24-3650 Smethurst 35 5 30 Hourly_Emps 2 DBMS-L7: FD -- 5 Dependency Theory b Is there a reason to decompose a relation? b What problems (if any) does the decomposition cause? h Integrity constraints, in particular f u n c t i o n a l d e p e n d e n c i e s , can be used to identify schemas with such problems and to suggest refinements....
View Full Document

This note was uploaded on 05/03/2010 for the course CS 9311 taught by Professor Wyewang during the Three '10 term at University of New South Wales.

Page1 / 27

L07-FD - L7: Dependency Theory x Functional dependencies x...

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

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