DOC PREVIEW
UW CSE 444 - Principles of relational Design

This preview shows page 1-2-20-21 out of 21 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Principles of Relational DesignThings can go wrong!Plan of AttackInformal Guidelines by Elmasri and NavatheThe "Universal Relation" ApproachFunctional dependency definedExamplesDependencies between sets of attributesFacts about FDInference Rules for FDsArmstrong's inference rulesSome other rulesClosuresNomalization1st Normal FormKey TerminologyFull and partial dependency2NF3NF3NF (General Definition)Boyce-Codd Normal Form (BCNF)11/06/97 J-1Principles of Relational DesignChapter 1211/06/97 J-2Things can go wrong!•All the relational designs we've seen so far have been pretty good–fairly small, intuitive examples–E/R model not far away•A carelessly designed schema can lead to big problems•How do we evaluate a schema?•How do we design a good one?11/06/97 J-3Plan of Attack•Study some informal principles (12.1)•Functional dependencies: an important type of semantic constraint–define and illustrate (12.2)–use to define "normal forms" 2NF, 3NF, and BCNF (12.3-12.5)•Decomposition algorithms (13.1)•Multivalued dependencies and 4NF (13.2)•Join dependencies and 5NF (13.3)11/06/97 J-4Informal Guidelines by Elmasri and Navathe¶Design a relation so that it is easy to explain its meaning.¶Design so that no insertion, deletion, or modification anomalies can occur.¶Avoid attributes whose values can be null.¶Design so that reasonable joins do not produce spurious tuples.11/06/97 J-5The "Universal Relation" Approach•Assume we have identified all the individual pieces of data (attributes) of the problem.The database design problem: group the attributes into relations.•The informal guidelines are one way of evaluating the result.•The theory of functional dependencies and normal forms gives a more precise way.11/06/97 J-6Functional dependency defined•Let X and Y be attributes•X Y means that Y is a function of X. I.e., if you know the value of X, there's only one possible value of Y. We say that "Y is functionally dependent on X" or "X determines Y."•Note: X Y does not imply Y X !11/06/97 J-7Examples•If you know the SSN, there's only one possible name (is the reverse true?)SSNNAME•If you know the department number, you know the department nameDNO  DNAME11/06/97 J-8Dependencies between sets of attributes•Given today's date and the date of birth, the age and the years until 65 are determined.{TD, DB}  {AGE, YTO65}•If you know the file pathname, you can determine its size, owner, and date of last modification.FP {SZ, O, MDT}•Normally, when writing X  Y, we assume that X and Y are sets of attributes.11/06/97 J-9Facts about FD•FDs are purely semantic in nature•FDs are facts about the abstract relation, not just about a particular relation instance–They must hold for all possible legal instances of a relation•All attributes of a relation are functionally dependent upon its key!–In fact, we can formally define keys in terms of FDs.11/06/97 J-10Inference Rules for FDs•Given a set of FDs, it may be possible to deduce others by purely syntactic means.•Example: Given that {A} {B,C}, it follows that {A}{B} also (and that {A} {C})11/06/97 J-11Armstrong's inference rules•Armstrong's rules (provable directly from the definitions):–IR1. Reflexive rule: if XY, then XY–IR2. Augmentation rule: if XY then XZYZ–IR3. Transitive rule: if XY and YZ then XZ•If you understand the idea of FD, these should make sense, even if you can't prove them formally.11/06/97 J-12Some other rules •Provable from IR1-3:–IR4. Decomposition: if XYZ then XY –IR5. Union (additive) rule: if XY and XZ then XYZ–IR6. Pseudotransitivity: if XY and WYZ then WXZ•These should also make sense. Try them out with actual attributes!11/06/97 J-13Closures•X+ is the "closure" of X: the set of all attributes functionally determined by X (given a set of FDs)11/06/97 J-14Nomalization•Take a relation schema…•Test it against a normalization criterion…•If it passes, fine!–Maybe test again with a higher criterion•If it fails, decompose into smaller relations–Each of them will pass the test–Each can then be tested with a higher criterion11/06/97 J-151st Normal Form•We've already seen!•It's the concept that all attribute values have to be atomic•This is now taken for granted in the relational model–but is being questioned again in the object model•E/R attributes don't have to be atomic11/06/97 J-16Key Terminology•Superkey: attribute set with unique value•Key: minimal superkey (no attribute can be removed)•May be more than one such "candidate key."•One is designated the "primary key"•"Prime" attribute: occurs in some key•"Non-prime": occurs in no key11/06/97 J-17Full and partial dependency•XY is a "full functional dependency" if no attribute can be removed from X and there still be the dependency.•XY is a "partial dependency" if some attribute can be removed from X and the dependency is still there.11/06/97 J-182NF•A relation schema is in Second Normal Form (2NF) if every non-prime attribute in it is fully functionally dependent on the primary key of the relation.–more generally: …FFD on any key...SSN PNUMBER HOURS ENAME PNAME PLOCATION11/06/97 J-193NF•XY is a transitive dependency if there is a set of attributes Z that is not a subset of any key of R, and both XZ and ZY hold.•A relation schema R is in Third Normal Form (3NF) if it is in 2NF and no nonprime attribute of R is transitively dependent on the primary key.11/06/97 J-203NF (General Definition)•A relation is in Third Normal Form (3NF) if, whenever XA holds, either X is a superkey, or A is a prime attribute.Informally: everything depends on the key or is in the key.•Despite the thorny technical definitions that lead up to it, 3NF is intuitive and not hard to achieve. Aim for it in all designs unless you have strong reasons otherwise.11/06/97 J-21Boyce-Codd Normal Form (BCNF)•R is in BFNC if whenever XA holds, then X is a superkey.•Slightly stronger than 3NF.•Example: R(A,B,C) with {A,B}C, CA–3NF but not BCNFAim for BCNF and settle for


View Full Document

UW CSE 444 - Principles of relational Design

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download Principles of relational Design
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Principles of relational Design and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Principles of relational Design 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?