For each table below, perform the following 3 steps. (Perform all three steps on

Question

For each table below, perform the following 3 steps. (Perform all three steps on Table A first. Then perform all 3 steps on Table B). Step 1: Determine if the table satisfies 2NF. If the table is already in 2NF, say so. If a table violates 2NF, say so and produce the 2NF tables. It is sufficient to just list the results of 2NF normalization. Step 2: Determine if all resulting tables from Step 1 satisfy BCNF. For each of the resulting tables from Step 1, if it is already in BCNF, say so. If it violates BCNF, say so and produce the BCNF tables. It is sufficient to just list the results of BCNF normalization. There is NO need to describe the normalization process verbally. Step 3: Check and make sure every table you wrote in Steps 1 and 2 has a primary key specified. If not, specify the primary key for each table missing the primary key.

Explanation / Answer

1)

Fundraising(Event#, SubEvent, Date, OrganizerName, OrganizerPhone, Beneficiary)

Given the functional dependencies as,

Event -> Date, OrganizerName

OrganizerName-> OrganizerPhone

Step 1:

As per the given query, in-order to check whether the given table is in 2NF or not, first it has to satisfy the 1NF.

If observed, the table does not contain any repeated groups. So, it satisfies the first normal form.

According to 2NF, the relation must be in 1NF and the non-primary key should not describe or should be depended on primary key or should not be partial dependency. Here, the OrganizerName is functionally dependent on the OrganizerPhone. Therefore, it is not satisfying the 2NF.

So, divide the table into two tables, that is,  

      Event_Handling(Event#, Date, OrganizerName,OrganizerPhone)

      Event(Event#, SubEvent, Beneficiary)

Now, the tables are in 2NF.

Step 2:

According to BCNF, first the tables need to be in 3NF. So, first thing is to check the tables satisfy the 3NF or not.

According to 3NF, the relation must be in 2NF and a non-key attribute must not describe about the non-key attribute. Here, in the first table, Event_Handling OrganizerName is describing the OrganizerPhone number.

So, convert the table into 3NF as by dividing the table into two.

Say,

    Event_Organizing(Event#, Date, OrganizerName)

    Event_MemberDetails(OrganizerName, OrganizerPhone)

    Event(Event#, SubEvent, Beneficiary)

Thus, the above three tables are in 3NF.

Now, to satisfy the BCNF, every determinant should contain a candidate key.

The three tables here are containing the candidate key.

Hence, the tables are in BCNF form.

Step 3:

          Primary key of Event_Organizing is Event#

          Primary key of Event_MemeberDetails is OrganizerName

          Primary key of Event is composition of Event# and SubEvent

2)

FileDownLoad: (SessionId, DownLoadId, FileName, SessionStDateTime, SessionEndDateTime, FileSize, FileType)

Given the functional dependencies as,

SessionId -> SessionStDateTime, SessioEndDateTime

FileName-> FileSize, FileType

Step1:

According to 2NF, the relation must be in 1NF and a non-key attribute must be dependent on primary key.

According to 1NF, the relation or table should contain only atomic values that are they should not contain repeated groups.

Here, there are no repeated groups. Hence, it is in 1NF.

To check the 2NF, here a non-key attribute is dependent on the non-key attribute but not on the primary key. FileName is functionally dependent on the FileSize and File Type which are not depending on the primary key.

So, to make the table into2NF divide the table.

Say,

          Session_Details(SessionId, SessionStDateTime,SessionEndDateTime)

          File_Details(SessionId, DownloadId, FileName,FileSize, FileType)

Now, the tables are in 2NF.

Step2:

According to BCNF, the tables must be in 3NF and each table should contain the candidate key.

According to 3NF, the table must be in 2NF and a non-key attribute must be dependent on the primary key.

According to the above two tables, the Session_Details table is in 2NF and any non-key attribute is not describing about the non-key attribute.

In the second table File_Details, they are in 2NF but non-key attribute FileName is describing about the non-key attributes FileSize and FileType. Thus, violates the 3NF rule.

Now, to make the table into 3NF, divide the table.

Say,

        File_Info(FileName, FileSize, FileType)

       Session_Info(SessionId, DownloadId, FileName)

       Session_Details(SessionId, SessionStDateTime,SessionEndDateTime)

Now, all the tables are in 3NF form and also each table contains the candidate key.

Step 3:

          Primary key of Session_Details is SessionId

          Primary key of Session_Info is composition of SessionId and DownloadId

          Primary key of File_Info is FileName.

Note:

All the primary keys are highlighted with the underline.

         

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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