RE-DESIGN THE DATABASE AND ENSURE THAT ALL THE TABLES ARE IN THIRD NORMAL FORM.

Question

RE-DESIGN THE DATABASE AND ENSURE THAT ALL THE TABLES ARE IN THIRD

NORMAL FORM. Show the table (or schemas).    

FUNTIONAL DEPENDENCIES:

                MEMBER_EMPLOYER --> EMPLOYER_ADDRESS

                MEMBER_EMPLOYER --> EMPLOYER_PHONE

                P_C_ PHYSICIAN_NO --> PHYSICIAN_NAME

                DEPENDENT_ID    --> DEPENDENT_NAME

                DEPENDENT_ID    --> DEPENDENT_DATE_OF_BIRTH

                DEPENDENT_ID    --> DEPENDENT_SEX

THE FIRST TABLE IS MEMBER.

The Schema of the table is as follows:

MEMBER (MEMBERSHIP_NO, MEMBERSHIP_TYPE, MEMBER_NAME, MEMBER_ADDRESS,

MEMBER_DATE_OF_BIRTH, MEMBER_SEX, MEMBER_EMPLOYER, EMPLOYER_ADDRESS

EMPLOYER_PHONE, P_C_PHYSICIAN_NO, PHYSICIAN_NAME)

THE SECOND TABLE IS DEPENDENTS:

The Schema of the table is as follows:

DEPENDENTS(MEMBERSHIP_NO, DEPENDENT_ID, DEPENDENT_NAME, RELATIONSHIP,

DEPENDENT_DATE_OF_BIRTH, DEPENDENT-SEX)

Explanation / Answer

First we need to found all the candidate keys for above relations:-

For MEMBER:-

(MEMBERSHIP_NO, MEMBERSHIP_TYPE, MEMBER_NAME, MEMBER_ADDRESS,

MEMBER_DATE_OF_BIRTH, MEMBER_SEX, MEMBER_EMPLOYER, P_C_PHYSICIAN_NO)+ =

MEMBERSHIP_NO, MEMBERSHIP_TYPE, MEMBER_NAME, MEMBER_ADDRESS,

MEMBER_DATE_OF_BIRTH, MEMBER_SEX, MEMBER_EMPLOYER, EMPLOYER_ADDRESS

EMPLOYER_PHONE, P_C_PHYSICIAN_NO, PHYSICIAN_NAME

so candidate key is {(MEMBERSHIP_NO, MEMBERSHIP_TYPE, MEMBER_NAME, MEMBER_ADDRESS,

MEMBER_DATE_OF_BIRTH, MEMBER_SEX, MEMBER_EMPLOYER, P_C_PHYSICIAN_NO)}

so,above dependencies shown are all partial dependencies because a prime attribute determines non-prime attribute.so,due to these partial dependencies the table MEMBER is not in 2NF.

so,to make table in 2NF,we need to decompose it into several tables.

Let the table MEMBER is decomposed into

MEMBER1(MEMBER_EMPLOYER, EMPLOYER_ADDRESS,EMPLOYER_PHONE)

the dependencies associated with these table is

MEMBER_EMPLOYER --> EMPLOYER_ADDRESS

                MEMBER_EMPLOYER --> EMPLOYER_PHONE

MEMBER2(P_C_PHYSICIAN_NO, PHYSICIAN_NAME)

the dependencies associated with these are:-

   P_C_ PHYSICIAN_NO --> PHYSICIAN_NAME

MEMBER3((MEMBERSHIP_NO, MEMBERSHIP_TYPE, MEMBER_NAME, MEMBER_ADDRESS,

MEMBER_DATE_OF_BIRTH, MEMBER_SEX, MEMBER_EMPLOYER, P_C_PHYSICIAN_NO)

there is no dependencies associated with MEMBER3 table.the attribute MEMBER_EMPLOYER is pointing toward the MEMBER1's MEMBER_EMPLOYER as foreign key and the attribute P_C_PHYSICIAN_NO is pointing toward the MEMBER2's P_C_PHYSICIAN_NO as foreign key

clearly in both tables MEMBER1 and MEMBER2 ,there is no partial dependencies so,it is clearly in 2NF.

also,we can see that the left side of all the dependencies of decomposed tables are all keys.so,clearly all are in 3NF.

For table DEPENDENTS:-

here,candidate keys is as follows:-

(DEPENDENT_ID,MEMBERSHIP_NO,RELATIONSHIP)+ = MEMBERSHIP_NO, DEPENDENT_ID, DEPENDENT_NAME, RELATIONSHIP, DEPENDENT_DATE_OF_BIRTH, DEPENDENT-SEX

so,candidate key = {(DEPENDENT_ID,MEMBERSHIP_NO,RELATIONSHIP)}

clearly,all are partial dependencies so,DEPENDENTS table is not even in 2NF.so,we need decompose table in 2NF form.

these tables can be decomposed like this:-

DEPENDENTS1(DEPENDENT_ID, DEPENDENT_NAME,DEPENDENT_DATE_OF_BIRTH, DEPENDENT-SEX)

the dependencies associated with this subtable is as follows:-

DEPENDENT_ID    --> DEPENDENT_NAME

DEPENDENT_ID    --> DEPENDENT_DATE_OF_BIRTH

DEPENDENT_ID    --> DEPENDENT_SEX

clearly,left side of all these dependencies is primary key.so,it is in 3NF.

DEPENDENTS2(MEMBERSHIP_NO,DEPENDENT_ID, RELATIONSHIP)

there is no dependencies associated with this subtable.

the attribute DEPENDENT_ID of this subtable is pointing towards the DEPENDENTS1's DEPENDENT_ID as a foreign key.

we can see that all subtables are in 3NF.so,overall DEPENDENTS table is in 3NF.

if you notice,dependency is preserved in both tables.

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.