cs411-05-reldesign

cs411-05-reldesign - CS411 Database Systems 05: Relational...

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

View Full Document Right Arrow Icon
1 CS411 Database Systems 05: Relational Schema Design
Background image of page 1

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

View Full DocumentRight Arrow Icon
2 Why Do We Learn This?
Background image of page 2
3 Motivation • We have designed ER diagram, and translated it into a relational db schema R = set of R1, R2, . .. • Now what? • We can do the following – specify all relevant constraints over R – implement R in SQL – start using it, making sure the constraints always remain valid • However, R may not be well-designed, thus causing us a lot of problems
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 Q: This a good design? Address SSN Phone Number 10 Green 123-321-99 (201) 555-1234 10 Green 123-321-99 (206) 572-4312 431 Purple 909-438-44 (908) 464-0028 431 Purple 909-438-44 (212) 555-4000 Persons with several phones:
Background image of page 4
5 Potential Problems • Redundancy • Update anomalies • Deletion anomalies
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 How do We Obtain a Good Design? • Start with the original db schema R • Transform it until we get a good design R* • Desirable properties for R* – must preserve the information of R – must have minimal amount of redundancy – must be dependency-preserving • if R is associated with a set of constraints C, then it should be easy to also check C over R* – (must also give good query performance)
Background image of page 6
7 OK, But . .. • How do we recognize a good design R*? • How do we transform R into R*? • What we need is the “theory” of …
Background image of page 7

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

View Full DocumentRight Arrow Icon
8 Normal Forms • DB gurus have developed many normal forms • Most important ones – Boyce-Codd, 3rd, and 4th normal forms • If R* is in one of these forms, then R* is guaranteed to achieve certain good properties – e.g., if R* is in Boyce-Codd NF, it is guaranteed to not have certain types of redundancy • DB gurus have also developed algorithms to transform R into R* that is in some of these normal forms
Background image of page 8
9 Normal Forms (cont.) • DB gurus have also discussed trade-offs among normal forms • Thus, all we have to do is – learn these forms – transform R into R* in one of these forms – carefully evaluate the trade-offs • Many of these normal forms are defined based on various constraints – functional dependencies and keys
Background image of page 9

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

View Full DocumentRight Arrow Icon
10 Behind the Scene: Know whom we should blame?
Background image of page 10
11 Our Attack Plan • Motivation • Functional dependencies & keys • Reasoning with FDs and keys • Desirable properties of schema refinement • Various normal forms and the trade-offs – BCNF, 3rd normal form, 4th normal form, etc. • Putting all together: how to design DB schema
Background image of page 11

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

View Full DocumentRight Arrow Icon
12 Functional Dependencies and Keys
Background image of page 12
13 Better Designs Exist SSN Address 123-321-99 10 Green 909-438-44 431 Purple SSN Phone Number 123-321-99 (201) 555-1234 123-321-99 (206) 572-4312 909-438-44 (908) 464-0028 909-438-44 (212) 555-4000 Break the relation into two:
Background image of page 13

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

View Full DocumentRight Arrow Icon
14 Functional Dependencies • A form of constraint (hence, part of the schema) • Finding them is part of the database design • Used heavily in schema refinement Definition: If two tuples agree on the attributes A , A , … A 12 n then they must also agree on the attributes B , B , … B m Formally: A , A , … A n B , B , … B m
Background image of page 14
15 Examples • EmpID Name, Phone, Position • Position Phone • but Phone Position EmpID Name Phone Position E0045 Smith 1234 Clerk E1847 John 9876 Salesrep E1111 Smith 9876 Salesrep E9999 Mary 1234 Lawyer
Background image of page 15

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

View Full DocumentRight Arrow Icon
16 In General • To check if A B violation:
Background image of page 16
Image of page 17
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 63

cs411-05-reldesign - CS411 Database Systems 05: Relational...

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

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