Lab 7: Physical Design and Normalization 1. The rules for making insertions into a B-Tree are as follows. .When we insert into a B-tree, we add new values at the leaves, but the tree grows upwards from the leaves. . Insertion rules I. Add the new value to the appropriate leaf. 2. If the node is full, split the node into two nodes Let x be the middle value in the node Let the two new nodes contain the values less than x and greater than x respectively Move x up to the parent of the original node. If the parent is full, then go to Step 2. If there is no parent, then create a new root containinga. * - Starting from an empty tree, insert A, G, F. B. K, D. H, M. J. ES, I, R, x, C, L, N, T, U, P into a B-tree in that sequence. Assume there are 4 or fewer key values in each node of the B-tree. Therefore, remember that all the nodes (except the root) must have at least 2 or more key values and all leaves of a B-tree are at the same level. Show what the B-tree looks like before and after each split so that the reader can clearly understand your procedure 2. Consider the following table, 1) What are the problems with this table? 2) Transform the unnormalized table to its 1NF, give corresponding schema of the table in 1NF, and draw Functional dependency diagram for it. 3) Transform the table to its 3NF (display tables with data) 4) Create your normalized tables (with data) in MS Access. Unnormalized Table StdID StdName Major CourseCourseDescInstructor OffHrs Loc Phone Grade 8643 Stethi Acct Acc 315 Fin Acct 9-11 442 4545 A ay Schoals 12-501 5555 B Rockl Acc 324 Mgt Acct Paul Mt 401 Fin Hist 201 US Hist Math 212 Calculus 8-10 448 8945 A 4-6 9-11 442 4545 B 4-6 8678 Archer Mgt Mgt 101 Intro Mgt Bueli 46312345 | ? 558 5987 B 9865 Mills Acct Acc 371 Ind. Study Ray Geluski3-4 Bueli 158 7848 B 463 2345 Mgt 101 Intro M Lab 7: Physical Design and Normalization 1. The rules for making insertions into a B-Tree are as follows. .When we insert into a B-tree, we add new values at the leaves, but the tree grows upwards from the leaves. . Insertion rules I. Add the new value to the appropriate leaf. 2. If the node is full, split the node into two nodes Let x be the middle value in the node Let the two new nodes contain the values less than x and greater than x respectively Move x up to the parent of the original node. If the parent is full, then go to Step 2. If there is no parent, then create a new root containinga. * - Starting from an empty tree, insert A, G, F. B. K, D. H, M. J. ES, I, R, x, C, L, N, T, U, P into a B-tree in that sequence. Assume there are 4 or fewer key values in each node of the B-tree. Therefore, remember that all the nodes (except the root) must have at least 2 or more key values and all leaves of a B-tree are at the same level. Show what the B-tree looks like before and after each split so that the reader can clearly understand your procedure 2. Consider the following table, 1) What are the problems with this table? 2) Transform the unnormalized table to its 1NF, give corresponding schema of the table in 1NF, and draw Functional dependency diagram for it. 3) Transform the table to its 3NF (display tables with data) 4) Create your normalized tables (with data) in MS Access. Unnormalized Table StdID StdName Major CourseCourseDescInstructor OffHrs Loc Phone Grade 8643 Stethi Acct Acc 315 Fin Acct 9-11 442 4545 A ay Schoals 12-501 5555 B Rockl Acc 324 Mgt Acct Paul Mt 401 Fin Hist 201 US Hist Math 212 Calculus 8-10 448 8945 A 4-6 9-11 442 4545 B 4-6 8678 Archer Mgt Mgt 101 Intro Mgt Bueli 46312345 | ? 558 5987 B 9865 Mills Acct Acc 371 Ind. Study Ray Geluski3-4 Bueli 158 7848 B 463 2345 Mgt 101 Intro M
