Database Past Paper Help I need help with a database past paper, our professor d

Question

Database Past Paper Help

I need help with a database past paper, our professor did not provide us with solutions, so I don't know whether my answers are correct or not.

I would appreciate if an expert could provide me with some example answers.

Q1. (a) Explain what Third Normal Form is and why we normalise relations into 14] Third Normal Form. (b) An estate agent currently stores property viewing bookings in a table with the following schema ViewingBooking (ClientNumber, ClientName, PropertyNumber, PropertyAddress, ViewingDateBooked, ViewingTimeBooked, AccompanyingAgent ID, AccompanyingAgent Name, Comment sonProperty) The attributes are self-explanatory and the following constraints apply: at any given date and time, a property may be booked for viewing by one client only. Comments on the property after viewing are to be accumulated That is, if a property had two viewings and the estate has received two separate comments from the viewings, then the two comments will be stored in the single attribute CommentsOnProperty For this schema, (i) identify the functional dependencies that you would expect to hold among the attributes and express them in a functional dependency diagram (state any assumptions you make); 17] (ii) normalise the schema into a set of Third Normal Form tables (primary keys should be underlined) [51 (c) Explain in what way your normalised design is better than the current de- sign. Consider, for example, what happens when a client cancels a viewing booking.

Explanation / Answer

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Normalization: Normalization means decomposing of larger and complex data into simple and smaller tables. Before Normalization data can be in the form of a table or it can be a row data that is data in the text file.

Following are the Advantages of Normalization:

Advantages of Normalization:

Normalization Forms :

First Normal Form (1NF):

In First Normal Form (1NF) all the columns in the table should be atomic in nature that means each column should contain one value & each row should contain the same number of columns. Repeating groups or duplicate columns are not allowed in first normal form.

Second Normal Form (2NF):

Second Normal Form (2NF) should full fill requirements of first normal form (1NF) and all the columns in the table should functionally depend upon primary key column in the table. Partially dependencies are not allowed in second normal form

Third Normal Form (3NF):

In Third Normal Form (3NF), It should full fill all the requirements of second normal form and all the columns in the table should be depend upon primary key column. Transitive dependency are not allowed in the 3NF. Transitive dependency means non key column depends upon non key column in the table.

Following section gives the details about the schema given in the question.

Given schema is not normalized hence need to normalize it into third normal form (3NF).

Table Name : Client

Description : This table stores the details of Client which includes ClientNumber which is primay key and Client Name.

Schema : Client (ClientNumber – Primary Key , ClientName)

Table Name : Property

Description : This table stores the details about the property , which includes PropertyNumber which is primary key and PropertyAddress.

Schema : Property (PropertyNumber –Primary Key , PropertyAddress)

Table Name : AccompanyingAgentDetails

Description : This table stores agent details which includes AgentID which is primary kay and AgentName.

Schema : AccompanyingAgentDetails (AccompanyingAgentID-Primary Key ,

AccompanyingAgentName)

Table Name : ViewingBooking

Description : This table stores the details about the property booking viewing, which includes ViewingBookingID and combination of booking date and time as primary key with name ViewingDateBookedTimeBooked. Also PropertyViewingStatus as one attribute which contains the viewing status like seen , cancel , yetTosee like this.

ViewingBooking(ViewingBookingID ,ViewingDateBookedTimeBooked-Primary Key,

ClientNumber –Foreign Key ,

PropertyNumber-Foreign Key,

AccompanyingAgentID-Foreign Key,

CommentsOnProperty,

PropertyViewingStatus)

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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