CS 3630 Database Design and Implementation
Design Methodology Three main phases 1. Conceptual database design Understanding client data E-R (EER) Model Contract between clients and designers E-R Model could be used for any database system 2. Logical database design Step 1: Mapping E-R Model to (relational) database schema Step 2: Normalization 3. Physical database design 2
Normalization Relational Database A set of normalized relations. Normalization To get desired performance Data consistency 1 NF, 2 NF, 3 NF, BCNF, 4 NF and higher normal forms We stop at BCNF 3
Example: Dream. Home Table schema for Staff (DBDL): Staff (Sno, Name, Address, Phone, Bno, BAddress, BPhone) PK: Sno AK: NONE FK: Bno references Branch 4
Problems Staff (Sno, Name, Address, Phone, Bno, BAddress, BPhone) Redundancy and Inconsistency Waste space : for each staff, store BAddress and BPhone Extra work on insertion: insert staff requires insertion of branch Extra work on updating: change BPhone, all staff in the branch Inconsistent data : when inserting and updating Sno Name Address Phone Bno Baddress BPhone S 001 . . . B 20 20 main st, Platteville, WI 53818 348 -8815 S 012 . . . B 20 20 main st, Platteville, WI 53818 348 -8815 S 034 . . . B 20 20 main st, Platteville, WI 53818 348 -8825 S 033 . . . B 20 25 main st, Platteville, WI 53818 348 -8815 5
Example: Dream. Home Table schema Staff_and_Branch (Sno, Name, Address, Phone, Bno, BAddress, BPhone) Major Issue Data Redundancy Data Inconsistency Should be Staff (Sno, Name, Address, Phone, Bno) Branch (Bno, Address, Phone) Normalization! Functional Dependency! 6
Functions A function is a mapping y = f(x) y 1 = f(x 1) y 2 = f(x 2) If x 1 = x 2 Then y 1 = y 2 If x 1 != x 2 Then y 1 != y 2 YES! NO! 7
Examples Example 1 y = f(x) = x 2 f(2) = f(-2) Is it possible that f(v 1) != f(v 2), but v 1 = v 2? NO! Example 2 y = g(x) = x 2 - 5 x + 6 y 1 = g(2) = 0 y 2 = g(3) = 0 Is it possible that g(u 1) != g(u 2), but u 1 = u 2? NO! 8
Functions y = f(x) y 1 = f(x 1) y 2 = f(x 2) x 1 = x 2 y 1 = y 2 True Same x value, then same y value. False x 1 != x 2 y 1 != y 2 True False Different x values, then same or different y values. Is it possible that val 1 = val 2 but f(val 1) != f(val 2) NO! Not a function! 9
Functional Dependency Table schema (DBDL) from E-R Model R (A, B, C, D, E, F) PK: A AK: B, C FK: C references R 1 Functional Dependency: B D For any two records of R Same B value Same D value Different B values Same or different D values (We don’t care!) D = f(B) No such a function to calculate the value of D from that of B! 10
Example Staff (Sno, Name, Address, Bno, BAddress) PK: Sno AK: None FD: ? ? ? 11
Example Staff (Sno, Name, Address, Bno, BAddress) PK: Sno AK: None FD: Sno All Sno Name Sno Address Sno Bno PK is Unique! 12
Example Staff (Sno, Name, Address, Bno, BAddress) FD: Sno All Address Name Address Bno BAddress Bno Sno Name Address Bno BAddress S 001 J. Clifton 102 main B 01 1 westhill S 002 M. Smith 102 main B 02 3 easttown S 013 M. Smith 20 main B 01 1 westhill 13
Functional Dependency R (A, B, C, D, E, F) PK: A AK: B, C FK: C references R 1 Functional Dependencies: B D For any two records of R Same B value Same D value Different B values Same or different D values (We don’t care!) B D not true Find two records of R Same B value Different D values (val 1 = val 2 but f(val 1) != f(val 2)) 14
Example Property (Pno, Address, Type, Ono, Oname) PK: Pno AK: Address FK: Ono references Owner FD: ? ? ? 15
Example Property (Pno, Address, Type, Ono, Oname) Pno All Address All Ono OName Ono Pno Address Type YES NO Ono OName P 001 1 Moonnite House O 01 J. Clifton P 002 3 Sunny House O 02 M. Smith P 013 9 S. Drive, U 22 Aparts O 03 J. Clifton P 014 9 S. Drive, U 15 Aparts O 02 M. Smith 16
Example A 10 20 10 30 B X X Y Y Y X C 200 300 200 200 100 D CS CS SE SE CS CS A C? Yes B D? NO Same value implies same value Different values? We do not care! A C? B D? 17
Example A 10 20 10 30 10 10 30 20 B X X Y Y Y V Z W C 200 300 200 250 ? ? D CS SE CS CS CS XX EE CJ A C? Possible B D? NO Business rules! Must be true for all table instances! Must be part of table schema and apply to all table instances! Not just from some table instances! Checking data entry if A C is true! 18
Functional Dependency Definition Assume A and B are attributes of a table R. A B (B is functionally dependent on A) If each value of A in R is associated with exactly one value of B in R. A is called Determinant. Any two records of R If they agree on A (have the same value on A), Then they will agree on B (have the same value on B). If they have different values on A, Then they may have different values or the same value on B. B = f(A) 19
Assignment 5 -1 Due Friday, February 23, by 5 PM Friday will cover all materials for A 5 -1 20
Assignment 4 Due February 21 By 5 pm 21
Скачать презентацию CS 3630 Database Design and Implementation Design
4d89dcbee51cc527e713caaa6dd4a771.ppt