1 / 42

Exercise for NF

Exercise for NF. List the Functional dependency Objective :combination of attributes whose closure includes all the attributes 1. Find the closures of all single attributes to see ( if there is another FD exist, then next)

vito
Télécharger la présentation

Exercise for NF

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exercise for NF

  2. List the Functional dependency Objective :combination of attributes whose closure includes all the attributes 1. Find the closures of all single attributes to see (if there is another FD exist, then next) 2. Calculate the closures of pairs of attributes that are possible keys: (only the left sideattributes) ( then next) 3. Then calculate the closures of triples of attributes that are possible keys. then next 4. if any is a key or there is other key, then continue next step to find the more combination of attributes whose closure includes all the attributes until all attributes are included. Step1- Functional dependency

  3. Step2- Find candidate keys Objective : Find the candidate keys CK1,CK2,...... 1.all of these CKi is prime (key) attributes 2.The others is nonprime (nonkey) attributes 3.Base on the step 1 FD results, if there are more then one minimal sets of attributes whose closure includes all the attributes Note: if the relations is derived from ER-model, most of these relations will be only one (or two) candidate key. It is easy to find the candidate key in each relation.

  4. Step 3 and 4 Determine and Decomposition • 1 NF • 2NF • 3NF • BCNF

  5. EXERCISE 1 R(A, B, C, D, E, F)是一個關連(relation)，其 中有以下的functional dependencies: (AB)， (EF)，(DEABCF)，請將relation R正規化 (normalize)成以下的格式。 (一)2NF relations (二)3NF relations (三)BCNF relations 複合主鍵為(D,E) A B C D E F

  6. F E E A B C D (一)移除部分功能相依成為2NF ∵E→F，而E是複合主鍵(D,E)的一部分 ∴存在部分功能相依 所以，2NF之後relation如下： 主鍵：E 複合主鍵：(D,E)，而E是Foreign key

  7. B F A E E C D A (二)移除遞移相依成為3NF，首先考慮(一)2.relation如下 ∵(D,E)→A，A→B　∴存在遞移相依 所以，3NF之後 主鍵：A 複合主鍵：(D,E)，而E是FK，A是FK考慮(一)1.relation如下： ∵不存在遞移相依　∴滿足3NF 主鍵：E

  8. B F A E E C D A 移除決定因素（Determinants）不是候選鍵的現象，成為BCNF 因為以下三個relation皆滿足決定因素都是候選鍵，故三個 relation皆是BCNF。 主鍵：A 複合主鍵：(D,E)，而E是FK，A是FK 主鍵：E

  9. Stu_no City ZIP Subject_no, Score 75312 台中市 400 (S5302, 89), (S5345, 90), (S8005, 78), (S3581, 80), (M1201, 65), (M5251, 95) 75524 高雄市 800 (S5302, 88) 75302 高雄縣 830 (S5302, 98), (S5345, 90), (S3581, 84), (M5251, 85) EXERCISE 2 • 假設我們將要設計一個成績單郵寄列印系統，需要學號、地址、郵遞區號、學科代碼與各科成績等資料，而初步搜集到的原始資料如下表所示： • 我們將依序探討 1NF, 2NF 與 3NF 的過程

  10. Stu_no City ZIP Subject_no Score 75312 台中市 400 S5302 89 75312 台中市 400 S5345 90 75312 台中市 400 S8005 78 75312 台中市 400 S3581 80 75312 台中市 400 M1201 65 75312 台中市 400 M5251 95 75524 高雄市 800 S5302 88 75302 高雄縣 830 S5302 98 75302 高雄縣 830 S5345 90 75302 高雄縣 830 S3581 84 75302 高雄縣 830 M5251 85 • 第一正規化(1NF) 的表格最重要的是能滿足「每個欄位只能含有一個值」這個條件。

  11. 在同一學生只能選修同科目一次的條件下，「Stu_no」加上「Subject_no」可以做為 A 的主鍵（Primary key）。我們以下圖來說明主鍵與其他欄位之間在功能上的相依關係（Functional Dependency）： • 在 A 之中係以（Stu_no, Subject_no）為 Primary key，但從上圖看來，有三項「功能相依」關係是錯誤的（如紅線所示），City 與 ZIP 的值與 Subject_no 絲毫無關。

  12. 在這樣的架構下，將產生下列問題： 　 • 無法單獨新增一筆學生資料。因為 Subject_no 是 Primary key 之一，不能為空值（Null）；因此，一個未修習任何課程學生的資料，將無法寫入 A。 　 • 無法單獨刪除一筆成績資料。如果我們打算刪除（75524, S5302）這筆資料的話，該生的地址資料也將一併消失。 　 • 需要同步異動的資料太多。假如 75312 這個學生搬家了，那麼我們得異動其中的 6 筆紀錄。 • 2NF: 一個表格必須滿意第一正規化的條件，並且非主鍵的欄位都要對主鍵有「完全地功能性相依（Fully Functional Dependency）」關係，才能算是達到第二正規化。

  13. Stu_no Stu_no City Subject_no ZIP Score 75312 75312 台中市 S5302 400 89 75524 75312 S5345 高雄市 800 90 75312 75302 S8005 高雄縣 830 78 75312 S3581 80 75312 M1201 65 75312 M5251 95 75524 S5302 88 75302 S5302 98 75302 S5345 90 75302 S3581 84 75302 M5251 85 在正規化之後，我們將表格 A 一分為二，並分別命名為 B1 與 B2

  14. B1與 B2各欄位和主鍵之間在功能上的相依關係（Functional Dependency）： 在一個表格中，如果某一欄位值可決定其他欄位值；而這些欄位中又存在某一欄位可以決定剩餘欄位的值，稱為「遞移相依性（Transitive Dependency）」。若有此一情況發生，在異動資料時，可能會造成其他資料不一致的現象。

  15. 在 B1 之中便有「遞移相依性」關係存在：B1.Stu_no -> B1.City 且 B1.City -> B1.ZIP 。在這樣的架構下，將產生下列問題： 　 (1) 無法單獨新增一筆縣市資料。因為 Stu_no 是 Primary key，不能為空值（Null）；因此，若無任何學生居住的某個縣市，其郵遞區號資料將無法被事先建立。 　 (2) 無法單獨刪除一筆學生資料。如果我們打算刪除 75524 這筆資料的話，該生所在的高雄市郵遞區號資料也將一併消失。 　 (3) 仍有需要同步異動的資料。假如台中市的郵遞區號修改了，且住在該地區的學生又不只一位時，那麼我們又得異動多筆紀錄了。

  16. Stu_no Stu_no City ZIP City City ZIP 75312 75312 台中市 400 台中市 台中市 400 高雄市 75524 75524 800 高雄市 高雄市 800 高雄縣 75302 75302 830 高雄縣 高雄縣 830 一個表格必須滿意第二正規化的條件，並且消除「遞移相依」現象，意即非主鍵的欄位之間沒有「完全地功能性相依」關係，才能算是達到第三正規化。 將表格 B1再度一分為二，並分別命名為 C1與 C2：

  17. EXERCISE 3 • Suppose you have the following relation R that contains information about course offering at a university: R( CourseNo, OfferingDeptNo, OfferingDeptName, Semester, Year, RoomNo, Address, BuildingName, RoomSize, InstructorId) Besides, you have the following functional dependencies: {CourseNo} -> { OfferingDeptNo} {OfferingDeptNo} -> {OfferingDeptName} {CourseNo, Semester, Year}-> {RoomNo, Address, InstructorId} {RoomNo, Address} -> {BuildingName, RoomSize} {BuildingName} -> {Address} 1. What is the key of R? 2. Decompose it into 2NF, then to 3NF, then to BCNF (if any)

  18. EXERCISE 4 考慮以下的RELATION SCHEMA和FUNCTIONAL DEPENDENCY： R(DNUM, AREA, DLOCATION, LAND_LORD, TAX, START_DATE) {AREA} -> {TAX} {DNUM, DLOCATION} -> {AREA, LAND_LORD, START_DATE} {DLOCATION} -> {AREA} {LAND_LORD} -> {DLOCATION} 1. 請問R的key是什麼? 2. 請將R轉成 A. 2NF, B. 3NF, 然後 C. BCNF (if any)

  19. EXERCISE 5 Consider the following relation: • Given the above extension, which of the following dependencies may • hold in the above relation? If a dependency cannot hold, explain why.A->B, B->C, C->B, B->A, C->A • B. What is the key of the above relation?

  20. (A). A->B cannot hold B->C can be hold C->B cannot hold B->A cannot hold C->A cannot hold (B) (A,B) or (A,C) or (A,B,C)

  21. EXERCISE 6 假設妳有一汽車販賣的relation如下： CAR_SALE(Car#, Date_sold, Salesman#, Commission%, Discount%). 其中一輛車可由多位銷售員（Salesman）賣出，此外，抽成 (Commission%)和折價(Discount%)可由其他屬性決定如下： Date_sold->Discount%, Salesman#->Commission% A. key 是什麼？ B. 依序轉成1NF, 2NF, 和3NF Sol: (A) CAR#+SALEMAN#

  22. 1NF CAR_SALE(CAR#, SALESMAN#, DATE_SOLD, COMMISSION%, DISCOUNT%) 2NF CAR_SALE(CAR#, SALESMAN#, DATE_SOLD, DISCOUNT%) SALESMAN(SALESMAN#, COMMISSION%)

  23. 3NF CAR_SALE1(CAR#, SALESMAN#, DATE_SOLD) CAR_SALE2(DATE_SOLD, DISCOUNT%) SALESMAN(SALESMAN#, COMMISSION%)

  24. EXERCISE 7 • Suppose we have the following requirements for a university database that is used to keep track of students transcripts: • The university keeps track of each student's name (SNAME), student number (SNUM), social security number (SSSN), current address (SCADDR) and phone (SCPHONE), permanent address (SPADDR) and phone (SPPHONE), birthdate (BDATE), sex (SEX), class (CLASS) (freshman, sophomore, ..., graduate), major department (MAJORDEPTCODE), minor department (MINORDEPTCODE) (if any), and degree program (PROG) (B.A., B.S., ..., Ph.D.). Both sssn and student number have unique values for each student. • Each department is described by a name (DEPTNAME), department code (DEPTCODE), office number (DEPTOFFICE), office phone (DEPTPHONE), and college (DEPTCOLLEGE). Both name and code have unique values for each department. • Each course has a course name (CNAME), description (CDESC), code number (CNUM), number of semester hours (CREDIT), level (LEVEL), and offering department (CDEPT). The value of code number is unique for each course.

  25. Each section has an instructor (INSTUCTORNAME), semester (SEMESTER), year (YEAR), course (SECCOURSE), and section number (SECNUM). Section numbers distinguish different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, ...; up to the number of sections taught during each semester. • A transcript refers to a student (SSSN), refers to a particular section, and grade (GRADE). • Design an relational database schema for this database application. First show all the functional dependencies that should hold among the attributes. Then, design relation schemas for the database that are each in 3NF or BCNF. Specify the key attributes of each relation. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.

  26. FD1: {SSSN} -> {SNAME, SNUM, SCADDR, SCPHONE, SPADDR, SPPHONE, BDATE, SEX, CLASS, MAJOR, MINOR, PROG} FD2: {SNUM} -> {SNAME, SSSN, SCADDR, SCPHONE, SPADDR, SPPHONE, BDATE, SEX, CLASS, MAJOR, MINOR, PROG} FD3: {DEPTNAME} -> {DEPTCODE, DEPTOFFICE, DEPTPHONE, DEPTCOLLEGE} FD4: {DEPTCODE} -> {DEPTNAME, DEPTOFFICE, DEPTPHONE, DEPTCOLLEGE} FD5: {CNUM} -> {CNAME, CDESC, CREDIT, LEVEL, CDEPT} FD6: {SECCOURSE, SEMESTER, YEAR, SECNUM} -> {INSTRUCTORNAME} FD7: {SECCOURSE, SEMESTER, YEAR, SECNUM, SSSN} -> {GRADE} FD1 and FD2 refer to student attributes; we can define a relation STUDENT and choose either SSSN or SNUM as its primary key. Similarly, FD3 and FD4 refer to department attributes, with either DEPTNAME or DEPTCODE as primary key. FD5 defines COURSE attributes, and FD6 SECTION attributes. Finally, FD7 defines GRADES attributes.

  27. The foreign keys will be as follows: STUDENT.MAJOR -> DEPARTMENT.DEPTCODE STUDENT.MINOR -> DEPARTMENT.DEPTCODE COURSE.CDEPT -> DEPARTMENT.DEPTCODE SECTION.SECCOURSE -> COURSE.CNUM GRADES.(SECCOURSE, SEMESTER, YEAR, SECNUM) -> SECTION.(SECCOURSE, SEMESTER, YEAR, SECNUM) GRADES.SNUM -> STUDENT.SNUM Note: We arbitrarily chose SNUM over SSSN for primary key of STUDENT, and DEPTCODE over DEPTNAME for primary key of DEPARTMENT.

  28. EXERCISE 8 Consider the relation schema EMP_DEPT in Figure 10.3(a) and the following set G of functional dependencies on EMP_DEPT: G = {SSN ->{ENAME, BDATE, ADDRESS, DNUMBER} , DNUMBER ->{DNAME, DMGRSSN} }. Calculate the closures {SSN} + and {DNUMBER} + with respect to G. Answer: {SSN} + ={SSN, ENAME, BDATE, ADDRESS, DNUMBER, DNAME, DMGRSSN} {DNUMBER} + ={DNUMBER, DNAME, DMGRSSN}

  29. EXERCISE 9 Consider the universal relation R = {A, B, C, D, E, F, G, H, I} and the set of functional dependencies F = { {A, B} -> {C}, {A} -> {D, E}, {B} -> {F}, {F} ->{G, H},{D} -> {I, J} }. What is the key for R? Decompose R into 2NF, then 3NF relations.

  30. First, identify partial dependencies that violate 2NF. These are attributes that are functionally dependent on either parts of the key, {A} or {B}, alone. We can calculate the closures {A}+ and {B}+ to determine partially dependent attributes: {A}+ = {A, D, E, I, J}. Hence {A} -> {D, E, I, J} ({A} -> {A} is a trivial dependency) {B}+ = {B, F, G, H}, hence {B} -> {F, G, H} ({B} -> {B} is a trivial dependency) To normalize into 2NF, we remove the attributes that are functionally dependent on part of the key (A or B) from R and place them in separate relations R1 and R2, along with the part of the key they depend on (A or B), which are copied into each of these relations but also remains in the original relation, which we call R3 below: R1 = {A, D, E, I, J}, R2 = {B, F, G, H}, R3 = {A, B, C} The new keys for R1, R2, R3 are underlined. Next, we look for transitive dependencies in R1, R2, R3. The relation R1 has the transitive dependency {A} -> {D} -> {I, J}, so we remove the transitively dependent attributes {I, J} from R1 into a relation R11 and copy the attribute D they are dependent on into R11. The remaining attributes are kept in a relation R12. Hence, R1 is decomposed into R11 and R12 as follows: R11 = {D, I, J}, R12 = {A, D, E} The relation R2 is similarly decomposed into R21 and R22 based on the transitive dependency {B} -> {F} -> {G, H}: R21 = {F, G, H}, R22 = {B, F} The final set of relations in 3NF are {R11, R12, R21, R22, R3}

  31. EXERCISE 10 Repeat exercise 14.26 for the following different set of functional dependencies G = { {A, B} -> {C}, {B, D} -> {E, F}, {A, D} -> {G, H}, {A} -> {I}, {H} -> {J} } SOL: {A}+ -> {A, I}, {B}+ -> {B}, {C}+ -> {C}, {D}+ -> {D}, {E}+ -> {E}, {F}+ -> {F}, {G}+ -> {G}, {H}+ -> {H, J}, {I}+ -> {I}, {J}+ -> {J} {A, B}+ -> {A, B, C, I}, {B, D}+ -> {B, D, E, F}, {A, D}+ -> {A, D, G, H, I, J} {A, B, D}+ -> {A, B, C, D, E, F, G, H, I}

  32. R = {A, B, D, C, E, F, G, H, I} The first-level partial dependencies on the key (which violate 2NF) are: {A, B} -> {C, I}, {B, D} -> {E, F}, {A, D}+ -> {G, H, I, J} Hence, R is decomposed into R1, R2, R3, R4 (keys are underlined): R1 = {A, B, C, I}, R2 = {B, D, E, F}, R3 = {A, D, G, H, I, J}, R4 = {A, B, D} Additional partial dependencies exist in R1 and R3 because {A} -> {I}. Hence, we remove {I} into R5, so the following relations are the result of 2NF decomposition: R1 = {A, B, C}, R2 = {B, D, E, F}, R3 = {A, D, G, H, J}, R4 = {A, B, D}, R5 = {A, I} Next, we check for transitive dependencies in each of the relations (which violate 3NF). Only R3 has a transitive dependency {A, D} -> {H} -> {J}, so it is decomposed into R31and R32 as follows: R31 = {H, J}, R32 = {A, D, G, H} The final set of 3NF relations is {R1, R2, R31, R32, R4, R5}

  33. EXERCISE 11 Consider the relation R, which has attributes that hold schedules of courses and sections at a university; R = {CourseNo, SecNo, OfferingDept, CreditHours, CourseLevel, InstructorSSN, Semester, Year, Days_Hours, RoomNo, NoOfStudents}. Suppose that the following functional dependencies hold on R: {CourseNo} -> {OfferingDept, CreditHours, CourseLevel} {CourseNo, SecNo, Semester, Year} ->{Days_Hours, RoomNo, NoOfStudents, InstructorSSN} {RoomNo, Days_Hours, Semester, Year} -> {InstructorSSN, CourseNo, SecNo} Try to determine which sets of attributes form keys of R. How would you normalize this relation?

  34. Let us use the following shorthand notation: C = CourseNo, SN = SecNo, OD = OfferingDept, CH = CreditHours, CL = CourseLevel, I = InstructorSSN, S = Semester, Y = Year, D = Days_Hours, RM = RoomNo, NS = NoOfStudents Hence, R = {C, SN, OD, CH, CL, I, S, Y, D, RM, NS}, and the following functional dependencies hold: {C} -> {OD, CH, CL} {C, SN, S, Y} -> {D, RM, NS, I} {RM, D, S, Y} -> {I, C, SN}

  35. First, we can calculate the closures for each left hand side of a functional dependency,since these sets of attributes are the candidates to be keys: • (1) {C}+ = {C, OD, CH, CL} • (2) Since {C, SN, S, Y} -> {D, RM, NS, I}, and {C}+ = {C, OD, CH, CL}, we get: • {C, SN, S, Y}+ = {C, SN, S, Y, D, RM, NS, I, OD, CH, CL} = R • (3) Since {RM, D, S, Y} -> {I, C, SN}, we know that {RM, D, S, Y}+ contains {RM, D, S, Y, I, C, SN}. But {C}+ contains {OD, CH, CL} so these are also contained in {RM, D, S, Y}+ since C is already there. • Finally, since {C, SN, S, Y} are now all in {RM, D, S, Y}+ and {C, SN, S, Y}+ contains {NS} (from (2) above), we get: • {RM, D, S, Y}+ = {RM, D, S, Y, I, C, SN, OD, CH, CL, NS} = R • Hence, both K1 = {C, SN, S, Y} and K2 = {RM, D, S, Y} are (candidate) keys of R.

  36. By applying the general definition of 2NF, we find that the functional dependency {C} ->{OD, CH, CL} is a partial dependency for K1 (since C is included in K1). Hence, R is normalized into R1 and R2 as follows: • R1 = {C, OD, CH, CL} • R2 = {RM, D, S, Y, I, C, SN, NS} with candidate keys K1 and K2 • Since neither R1 nor R2 have transitive dependencies on either of the candidate keys, R1 and R2 are in 3NF also. • They also both satisfy the definition of BCNF.

More Related