Database Slides on Normalization

Chapter 11 Relational Database Design Algorithms and Further Dependencies Chapter Outline ? ? ? ? ? ? ? 0. Designing a Set of Relations 1. Properties of Relational Decompositions 2. Algorithms for Relational Database Schema 3. Multivalued Dependencies and Fourth Normal Form 4. Join Dependencies and Fifth Normal Form 5. Inclusion Dependencies 6. Other Dependencies and Normal Forms DESIGNING A SET OF RELATIONS ? Goals: ? Lossless join property (a must) ? Algorithm 11. 1 tests for general losslessness. Algorithm 11. decomposes a relation into BCNF components by sacrificing the dependency preservation. 4NF (based on multi-valued dependencies) 5NF (based on join dependencies) ? Dependency preservation property ? ? Additional normal forms ? ? 1. Properties of Relational Decompositions ? Relation Decomposition and Insufficiency of Normal Forms: ? Universal Relation Schema: ? A relation schema R = {A1, A2, †¦, An} that includes all the attributes of the database. Every attribute name is unique. ? Universal relation assumption: ? (Cont) ? Decomposition: ? ? Attribute preservation condition: ?The process of decomposing the universal relation schema R into a set of relation schemas D = {R1,R2, †¦, Rm} that will become the relational database schema by using the functional dependencies. Each attribute in R will appear in at least one relation schema Ri in the decomposition so that no attributes are â€Å"lost†. (Cont) ? ? Another goal of decomposition is to have each individual relation Ri in the decomposition D be in BCNF or 3NF. Additional properties of decomposition are needed to prevent from generating spurious tuples (Cont) ? Dependency Preservation Property of a Decomposition: ? Definition: Given a set of dependencies F on R, the projection of F on Ri, denoted by pRi(F) where Ri is a subset of R, is the set of dependencies X > Y in F+ such that the attributes in X U Y are all contained in Ri. Hence, the projection of F on each relation schema Ri in t he decomposition D is the set of functional dependencies in F+, the closure of F, such that all their left- and right-hand-side attributes are in Ri. (Cont. ) ? Dependency Preservation Property of a Decomposition (cont. ): ? Dependency Preservation Property: ? ? A decomposition D = {R1, R2, †¦ Rm} of R is dependency-preserving with respect to F if the union of the projections of F on each Ri in D is equivalent to F; that is ((? R1(F)) U . . . U (? Rm(F)))+ = F+ (See examples in Fig 10. 12a and Fig 10. 11) ? Claim 1: ? It is always possible to find a dependency-preserving decomposition D with respect to F such that each relation Ri in D is in 3NF. Projection of F on Ri Given a set of dependencies F on R, the projection of F on Ri, denoted by ? Ri(F) where Ri is a subset of R, is the set of dependencies X > Y in F+ such that the attributes in X ?Y are all contained in Ri. Dependency Preservation Condition Given R(A, B, C, D) and F = { A > B, B > C, C > D}    Let D1={R1(A,B), R2 (B,C), R3(C,D)} ? R1(F)={A > B} ? R2(F)={B > C} ? R3(F)={C > D} FDs are preserved. (Cont. ) ? Lossless (Non-additive) Join Property of a Decomposition: ? Definition: Lossless join property: a decomposition D = {R1, R2, †¦ , Rm} of R has the lossless (nonadditive) join property with respect to the set of dependencies F on R if, for every relation state r of R that satisfies F, the following holds, where * is the natural join of all the relations in D: (? R1(r), †¦ , ? Rm(r)) = r ? Note: The word loss in lossless refers to loss of information, not to loss of tuples. In fact, for â€Å"loss of information† a better term is â€Å"addition of spurious information† Example S s1 s2 s3 P p1 p2 p1 D d1 d2 d3 = S s1 s2 s3 P p1 p2 p1 * P p1 p2 p1 D d1 d2 d3 Lossless Join Decomposition NO (Cont. ) Lossless (Non-additive) Join Property of a Decomposition (cont. ): Algorithm 11. 1: Testing for Lossless Join Property Input: A universal relation R, a decomposition D = {R1, R2, †¦ , Rm} of R,and a set F of functional dependencies. 1.Create an initial matrix S with one row i for each relation Ri in D, and one column j for each attribute Aj in R. 2. Set S(i,j):=bij for all matrix entries. (/* each bij is a distinct symbol associated with indices (i,j) */). 3. For each row i representing relation schema Ri {for each column j representing attribute Aj {if (relation Ri includes attribute Aj) then set S(i,j):= aj;};}; ? (/* each aj is a distinct symbol associated with index (j) */) ? CONTINUED on NEXT SLIDE (Cont. ) 4. Repeat the following loop until a complete loop execution results in no changes to S {for each functional dependency X >?Y in F {for all rows in S which have the same symbols in the columns corresponding to attributes in X {make the symbols in each column that correspond to an attribute in Y be the same in all these rows as follows: If any of the rows has an â€Å"a† symbol for the column, set the other rows to that same â€Å"aâ €  symbol in the column. If no â€Å"a† symbol exists for the attribute in any of the rows, choose one of the â€Å"b† symbols that appear in one of the rows for the attribute and set the other rows to that same â€Å"b† symbol in the column ;}; }; }; 5.If a row is made up entirely of â€Å"a† symbols, then the decomposition has the lossless join property; otherwise it does not. (Cont. ) Lossless (nonadditive) join test for n-ary decompositions. (a) Case 1: Decomposition of EMP_PROJ into EMP_PROJ1 and EMP_LOCS fails test. (b) A decomposition of EMP_PROJ that has the lossless join property. (Cont. ) Lossless (nonadditive) join test for n-ary decompositions. (c) Case 2: Decomposition of EMP_PROJ into EMP, PROJECT, and WORKS_ON satisfies test. (Cont. ) ? Testing Binary Decompositions for Lossless Join Property ? ?Binary Decomposition: Decomposition of a relation R into two relations. PROPERTY LJ1 (lossless join test for binary decompositions): A decomposi tion D = {R1, R2} of R has the lossless join property with respect to a set of functional dependencies F on R if and only if either ? ? The FD ((R1 ? R2) >? (R1- R2)) is in F+, or The FD ((R1 ? R2) >? (R2 – R1)) is in F+. 2. Algorithms for Relational Database Schema Design Algorithm 11. 3: Relational Decomposition into BCNF with Lossless (non-additive) join property Input: A universal relation R and a set of functional dependencies F on the attributes of R. 1. Set D := {R}; 2.While there is a relation schema Q in D that is not in BCNF do { choose a relation schema Q in D that is not in BCNF; find a functional dependency X > Y in Q that violates BCNF; replace Q in D by two relation schemas (Q – Y) and (X U Y); }; Assumption: No null values are allowed for the join attributes. Algorithms for Relational Database Schema Design Algorithm 11. 4 Relational Synthesis into 3NF with Dependency Preservation and Lossless (Non-Additive) Join Property Input: A universal relation R a nd a set of functional dependencies F on the attributes of R. 1. Find a minimal cover G for F (Use Algorithm 10. ). 2. For each left-hand-side X of a functional dependency that appears in G, create a relation schema in D with attributes {X U {A1} U {A2} †¦ U {Ak}}, where X >? A1, X >? A2, †¦ , X > Ak are the only dependencies in G with X as left-hand-side (X is the key of this relation). 3. If none of the relation schemas in D contains a key of R, then create one more relation schema in D that contains attributes that form a key of R. (Use Algorithm 11. 4a to find the key of R) 4. Eliminate redundant relations from the result. A relation R is considered redundant if R is a projection of another relation SAlgorithms for Relational Database Schema Design Algorithm 11. 4a Finding a Key K for R Given a set F of Functional Dependencies Input: A universal relation R and a set of functional dependencies F on the attributes of R. 1. Set K := R; 2. For each attribute A in K { Compu te (K – A)+ with respect to F; If (K – A)+ contains all the attributes in R, then set K := K – {A}; } (Cont. ) 3. Multivalued Dependencies and Fourth Normal Form (a) The EMP relation with two MVDs: ENAME —>> PNAME and ENAME —>> DNAME. (b) Decomposing the EMP relation into two 4NF relations EMP_PROJECTS and EMP_DEPENDENTS. (Cont. ) c) The relation SUPPLY with no MVDs is in 4NF but not in 5NF if it has the JD(R1, R2, R3). (d) Decomposing the relation SUPPLY into the 5NF relations R1, R2, and R3. (Cont. ) Definition: ? A multivalued dependency (MVD) X —>> Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation state r of R: If two tuples t1 and t2 exist in r such that t1[X] = t2[X], then two tuples t3 and t4 should also exist in r with the following properties, where we use Z to denote (R -(X U Y)): ? t3[X] = t4[X] = t1[X] = t2[X]. t3[Y] = t1[Y] and t4[Y] = t2[Y]. t3[Z] = t2[Z] a nd t4[Z] = t1[Z].An MVD X —>> Y in R is called a trivial MVD if (a) Y is a subset of X, or (b) X U Y = R. ? ? ? Multivalued Dependencies and Fourth Normal Form Definition: ? A relation schema R is in 4NF with respect to a set of dependencies F (that includes functional dependencies and multivalued dependencies) if, for every nontrivial multivalued dependency X —>> Y in F+, X is a superkey for R. ? Informally, whenever 2 tuples that have different Y values but same X values, exists, then if these Y values get repeated in separate tuples with every distinct values of Z {Z = R – (X U Y)} that occurs with the same X value. Cont. ) (Cont. ) Lossless (Non-additive) Join Decomposition into 4NF Relations: ? PROPERTY LJ1’ ? The relation schemas R1 and R2 form a lossless (non-additive) join decomposition of R with respect to a set F of functional and multivalued dependencies if and only if ? (R1 ? R2) —>> (R1 – R2) (R1 ? R2) —>> (R2 – R1 )). ? or ? (Cont. ) Algorithm 11. 5: Relational decomposition into 4NF relations with non-additive join property ? Input: A universal relation R and a set of functional and multivalued dependencies F.Set D := { R }; While there is a relation schema Q in D that is not in 4NF do { choose a relation schema Q in D that is not in 4NF; find a nontrivial MVD X —>> Y in Q that violates 4NF; replace Q in D by two relation schemas (Q – Y) and (X U Y); }; 1. 2. 4. Join Dependencies and Fifth Normal Form Definition: ? A join dependency (JD), denoted by JD(R1, R2, †¦ , Rn), specified on relation schema R, specifies a constraint on the states r of R. ? ? The constraint states that every legal state r of R should have a non-additive join decomposition into R1, R2, †¦ Rn; that is, for every such r we have * (? R1(r), ? R2(r), †¦ , ? Rn(r)) = r (Cont. ) Definition: ? A relation schema R is in fifth normal form (5NF) (or Project-Join Normal Form (PJNF)) with respect to a set F of functional, multivalued, and join dependencies if, ? for every nontrivial join dependency JD(R1, R2, †¦ , Rn) in F+ (that is, implied by F), ? every Ri is a superkey of R. Recap ? ? ? ? ? Designing a Set of Relations Properties of Relational Decompositions Algorithms for Relational Database Schema Multivalued Dependencies and Fourth Normal Form Join Dependencies and Fifth Normal FormTutorial/Quiz 4 Q1) Consider a relation R with 5 attributes ABCDE, You are given the following dependencies: A > B, BC > E, ED > A a) List all the keys, b) Is R in 3 NF c) Is R in BCNF Q2) Consider the following decomposition for the relation schema R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F = { {A, B} > {C}, {A} > {D, E}, {B} > {F}, {F} > {G, H}, {D} -> {I, J} }. Preserves Lossless Join and Dependencies? a) D1 = {R1, R2, R3, R4, R5}, R1={A,B,C} R2={A,D,E}, R3={B,F}, R4 = {F,G,H}, R5 = {D,I,J} b) D2 = {R1, R2, R3} R1 = {A,B,C,D,E} R2 = {B,F,G,H}, R3 = {D,I,J }

Culture, Ethnicity, and Health Care Practices Essay

1. Identify two religious or spiritual practices in which members of the Evans Family may engage for treating hypertension, diabetes, and mental illness. In the text, the Evan’s family is introduced as an African-American family, and members of the Baptist church. Members of this family have been diagnosed with hypertension, diabetes, and mental illness, diseases that the family may attempt to treat through religious of spiritual practices. Daily prayer may be a spiritual/religious practice used by the Evans family to treat the mentioned health conditions. â€Å"African-Americans consider themselves spiritual beings, and sickness is viewed as a separation between God and man† (Purnell, 2003, p. 49). In an attempt to get closer to God, daily prayer would be viewed as a positive aspect to accomplish this, lessen the separation between themselves and God, to improve or abolish the sicknesses. God is viewed as the â€Å"supreme healer†, and the Evans family may also be asking God through prayer for healing to improve the illnesses, and restore their health. Another spiritual practice which may be employed by the Evans family to treat the hypertension, diabetes, and mental illness which they are experiencing involves the laying on of hands. African Americans may hold the belief that diseases can be cured when a person of faith gifted with special healing powers touches the afflicted person. This touch is usually accompanied by prayer. Sometimes, it is believed that â€Å"†¦the laying on of hands is thought to free the person from all suffering and pain, and people who still experience pain are considered to have little faith† (Purnell, 2003, p. 50). It is important that the health care provider be aware of these spiritual practices, their potential effect on the health care plan, to provide culturally competent care. 2. Name two dietary health risks for African Americans. Typical diets for African Americans can reflect the regional cuisine, but there are some foods and types of foods that are considered central to the diet. Salt pork, or â€Å"fat back† is a staple ingredient in many African American’s diet, and reflect the tendency of the diet to be very high in fat, cholesterol, and sodium. This type of diet is associated with high blood pressure, heart disease, stroke, and diabetes; these conditions do occur at a higher prevalence in the African American population. Another diet related health risk involves the tendency to view people of healthy body weight as being underweight and needing more â€Å"meat on their bones† (Purnell, 2003, p. 47). Even in infancy, it is a common practice to avoid underfeeding by feeding newborn infants liquefied cereal mixed with formula and given through a bottle. â€Å"African Americans believe that giving only formula is starving the baby and that the infant needs ‘real food’ to sleep through the night† (Purnell, 2003, p. 47). These views may lead to a tendency to overeat, and be overweight, which is associated with the increased risks for the health conditions listed above. Health care providers can be in a position to give nutritional information, and recommend resources to assist patients to reduce dietary health risks, while maintaining the spirit of traditional cuisine. A magazine published by a health network directed at African Americans encourages healthier, culturally sensitive changes: Dr. Jones stresses that eating healthy doesn’t mean having to give up favorite foods. It just means preparing these dishes in a healthier fashion. That includes traditional favorites, like soul food—one of the oldest and richest cuisines in America. While it’s always been good for the soul, soul food is usually not so good for the heart†¦But soul food doesn’t have to be bad for you. You can continue to enjoy your ages-old family recipes in a new, health-conscious style by changing your cooking methods (Advocate Health Systems, 2007, p. 2). 3. What historical precedence is there for distrust of â€Å"outsiders† in Appalachia? People in Appalachia are characterized as having a distrust of â€Å"outsiders†, and are often unwilling to share their true thoughts and feelings with them. Part of the historical precedence that contributes to this distrust centers on their prior dealings with authority in the form of the large mining and timber companies. The Appalachian people were exploited by having their land taken by these outside business interests, who then subsequently attempted to control the behaviors of the workers for their own profits. There is a strong reliance on family and community; individualism and self-reliant behavior, and reverences for family lineage are highly valued within this culture. Appalachia has been characterized by poverty and isolation, which can result in cultural lag. There has historically been a limited access to education, as well as less of a valuing of higher education, with an emphasis placed upon learning skills necessary to enter the traditional employment fields and earn a living. These factors encourage the continuing distrust of outsiders. Communication styles reflect a general distrust and unease with â€Å"outsiders†. Materials or oral communication presented at a level that is beyond the understanding of the client may lead one to believe that the provider is â€Å"stuck up† or â€Å"putting on airs† and may add to a general feeling of distrust. Traditional Appalachians may value a large amount of personal distance, direct eye contact, and may value silence. They are often unwilling to communicate true needs in an attempt to be polite. Because of historical inequities with interactions with authority figures, including outsider health care providers, traditional Appalachians may not respond well to teach or instruction that is perceived to be an order; they may be much more receptive to hints and suggestions which solicit the client’s opinions (Purnell, 2003, pp. 73, 74). Because of improved technology, transportation, educational and employment opportunities, and more exposure to outside culture, traditional Appalachia may be changing, and the barriers and distrust of â€Å"outsiders† will be lessened. However, an Appalachian educator notes: The mountains have figured into all of it. The mountains kept us so isolated from the rest of the country and from other people’s involvement in our lives that we developed these characteristics: We are very independent, we are very contented with the places that we live, we are very close to nature, we have a deep sense of a belief in God, and we have a deep sense of being friendly and kind to one another and helping one another, and taking care of everyone else in their needs. And we also have a strong sense of what’s right and what ought to be and a deep mistrust of anyone who’s new, anyone who’s a stranger. We don’t trust change very well and we don’t like change very well (Morgan, 2005, p. 1). 4. What evidence do you see of the â€Å"ethic of neutrality† in this case study? The case study presents a view of the client by using her preferred terms, language, and presents her views in an objective manner. The case study seems to reflect the point of view of Miss Leona, and it respects her unique perspective. The author uses the words of the speaker to explain her reasons for doing things in a way that reflects aspects of the culture. For example, rather than state that Miss Leona may have difficulty reading medication instructions, the author includes the information that she was only educated through the third grade, and states â€Å"She admits not using the salve given to her by Dr. Adi because he told her to apply the salve according to the instructions on the jar† (Purnell, 2003, p. 89). The author does not make any judgments relating to the health related behaviors of the people in the case study. Several elements that illustrate cultural domain concepts are objectively presented. The use of folk medicine is demonstrated, with no evaluation of the home remedies, including the application of a bacon grease poultice to the leg wound. Similarly, the author presents Miss Leona’s own explanations of her illnesses, including her statement that she has â€Å"low blood†, in a subjective manner. The â€Å"ethic of neutrality† is maintained through an objective account of her situation, as experience by the patient alone, within her unique perspective. 5. How might you help Miss Leona eat a more nutritious diet? In the case study, the elderly Miss Leona reveals that she has been diagnosed with a heart disease, which her physician attributes to a high fat diet. She believes that her heart problem is instead related to the recent death of her husband, based in part on the timing of the diagnosis. She has expressed her dissatisfaction with the physician who has diagnosed this condition, because he did not communicate adequately during the visit. Miss Leona reveals that her diet is mainly high fat meat, dairy, and simple starches; she doesn’t mention fruits or vegetables at all. These foods are easy for her to prepare, and readily available. She demonstrates the difficulty in bringing food to her home, because she has to walk up a steep grade and a footbridge to get to her home. She does rely on others to bring her groceries at times, but makes it clear that she values self reliance. In accordance with traditional Appalachian cultural values, Miss Leona would most likely not respond well to being told directly which foods that she needs to eat to stay healthy. Rather, a practitioner would have to engage her in more of an indirect chat that would show knowledge of Miss Leona’s unique situation, and respect her likes and dislikes, and her need for ease of food preparation. The practitioner would also have to address the issue of being able to accept assistance needed to even bring healthy foods into her home, since there are physical barriers present that make carrying heavy groceries up a steep incline, especially with an infected leg wound, and a heart condition, impossible. The practitioner would need to provide the appropriate support needed to help make food choices that Miss Leona would be able to prepare, and enjoy. 6. Describe two taboo views that African Americans may have about pregnancy. Taboos are practices that if done, are likely to result in harm coming to the mother, unborn child, or both in the case of pregnancy-related taboos. There are a few pregnancy taboos regarding photographs that African Americans might have, according to the text. There is the belief that pregnant women should not take photographs, or they may cause the baby to be stillborn. Another cultural belief that may be shared is that a pregnant woman should not have their picture taken, because it captures their soul. There is also a taboo that involves the dangers of purchasing clothing or other items for an unborn baby. This is said to be â€Å"bad luck† for the baby and mother. Baby items are purchased after the child is born to avoid causing harm to or misfortune to the mother or her child. Pregnancy taboos views may be held by African Americans, in addition to other prescriptive and restrictive practices regarding childbearing. The realities of pregnancy are related to many factors. â€Å"African American women usually respond to pregnancy in the same manner as other ethnic groups, based on their satisfaction with self, economic status, and career goals† (Purnell, 2003, p. 48). 7. Identify and discuss views that Dorothy and her parents may have about mental illness and medication. In the case study, Dorothy is a 42 year old African American woman living with her parents who takes medication for a diagnosed of bipolar disorder. African Americans have variations in drug metabolism, particularly of psychotropic medications. They show higher blood levels, experience a faster therapeutic response, and are at more risk for drug side effects. â€Å"Health care professional’ personal beliefs may about a specific ethnic group may account of how the drug is prescribed, and African Americans are at a higher risk of misdiagnosis for psychiatric disorders and ,therefore, may be treated inappropriately with drugs† (Purnell, 2003, p. 48). The Evans family may have seen these medications misused in family members or friends, and may have some negative view regarding the treatment of mental illness. In African American culture, there is a tendency to use medication on an â€Å"as needed† basis; when an illness improves, and the person feels better, she may stop taking the medication. This practice could be very detrimental to medication therapy for bipolar disorder. If Dorothy and her family engage in this practice, she would most likely not have had good responses to medication. Dorothy and her family may therefore have a negative view of the medication’s effectiveness. African Americans often attach a stigma to mental illness, especially since there tends to be a high frequency of misdiagnosis of psychiatric disorders, leading to a distrust of mental health professionals (Purnell, 2003, p. 50). The Evans are a religious family, and they may have prayed and attempted to achieve spiritual healing for Dorothy. When the illness persisted, they may have judged Dorothy as not being faithful enough to receiving healing. With these cultural factors coming in to play, there is a high probability that Dorothy’s illness presents a great challenge within the Evans family. References Advocate Health Systems (2007). How to make your family recipes heart healthy. Advocate Magazine 3(24). Retrieved 6/12/07, from http://www. advocatehealth. com/trin/info/library/ham/win00/trin1. html? fitness Morgan, K. (2005). West Virginia’s Appalachian culture. West Virginia’s Appalachian Music and Literature Resources. Retrieved 6/12/07, from http://www. ferrum. edu/applit/studyg/West/htm/wvcultur. htm . Purnell, L. (2003). Transcultural healthcare: a culturally competent approach. Philadelphia: F. A. Davis Company.