Databases: Normalization: Part b refers to the results of part a, part c refers

Question

Databases:

Normalization:

Part b refers to the results of part a, part c refers to the results of part b. You may add additional fields, like making up a key when there is no good existing field.

2. Property(Property_id, county, lot_num, lot_area, price, tax_rate, (date_paid, amount) )

Functional Dependencies Property_id count,lot_num, area, price, tax_rate, date_paid,amount area price county tax_rate date_paid amount a) is this relation in 1NF? If not, why not? And if not, put it in 1NF b) is this relation in 2NF? If not, why not? And if not, put it in 2NF. c) is this relation in 3NF? If not, why not? And if not, put it in 3NF.

1. invoice (invoice_no, invoice_date, cust_name, cust_addr, (pet_name, procedure, amount ))

Functional Dependencies invoice_no invoice_date, cust_name, cust_addr, pet_name invoice_no, pet_name procedure cust_name cust_addr procedure amount a) is this relation in 1NF? If not, why not? And if not, put it in 1NF b) is this relation in 2NF? If not, why not? And if not, put it in 2NF. c) is this relation in 3NF? If not, why not? And if not, put it in 3NF.  

Explanation / Answer

(Property_id, county, lot_num, lot_area, price, tax_rate, (date_paid, amount) )

a) is this relation in 1NF? If not, why not? And if not, put it in 1NF

                                  The propery schema is not in 1nf, because the "(date_paid, amount)" value in propery schema is atomic, it can be divided further. Hence to make it as 1nf the schema is further divided as

property(Property_id, county, lot_num, lot_area, price, tax_rate)

pay(date_paid, amount)

Now the schema is atomic hence it was in first normal form.

b) is this relation in 2NF? If not, why not? And if not, put it in 2NF

Functional Dependencies Property_id count,lot_num, area, price, tax_rate, date_paid,amount

Since all the element in the schema are functionally dependant with propety_id, which is a primary key, the table is in 2nf(second normal form).

c) is this relation in 3NF? If not, why not? And if not, put it in 3NF.

   since there exists transitive dependancies among the vaules the schema not in 3NF.

   area price county tax_rate date_paid amount

the above 3 elments are transitiviely dependant with other non key attributes.

To put the schema in 3NF it is further sub divided into

   property(Property_id, county, lot_num, lot_area, price, tax_rate)

   pay(date_paid, amount)

area_price( area , price)

county_tax(county , tax_rate)

date_of_pay(date_paid ,amount )

Now the schema is completely in thirl normal form (3NF)

-----------------------------------------------------------------------------------------------------------------------------------------

invoice (invoice_no, invoice_date, cust_name, cust_addr, (pet_name, procedure, amount ))

a) is this relation in 1NF? If not, why not? And if not, put it in 1NF

The propery schema is not in 1nf, because the "((pet_name, procedure, amount ))" value in propery schema is atomic, it can be divided further. Hence to make it as 1nf the schema is further divided as

invoice (invoice_no, invoice_date, cust_name, cust_addr)

pet_details(pet_name, procedure, amount )

The above schema elements are atomic,hence now it is in 1NF.

b) is this relation in 2NF? If not, why not? And if not, put it in 2NF.

Functional Dependencies invoice_no invoice_date, cust_name, cust_addr, pet_name invoice_no, pet_name procedure cust_name cust_addr procedure amount

   No the schema is not in 2NF. here not all the non key attributes are functionally dependant with primary key element.

invoice(invoice_no , invoice_date, cust_name, cust_addr, pet_name) here all the non key elements are functionally dependant with the key element "invoice_no".

pet_details(pet_name , procedure)

cust_details(cust_name, addr)

proc(procedure , amount)

Now the above schema satisfies 1NF and 2NF.

c) is this relation in 3NF? If not, why not? And if not, put it in 3NF.  

    yes, the schema is now in 3NF, becuause the elements satisfies 2NF as well as there is no transitive dependancies exists among the elements in the table. All the elements in the table are functionally dependant with the key elements in the table.

Note :

         Rule 1 : No atomic values in the table.

           Rule 2 : Have to satisfy Rule 1 and all the non key attibutes needs to be functinally dependant with key attibutes.

           Rule 3 : Have to satisfy Rule 1 , Rule 2 and there should be no transitive functional dependancies among the values in the table.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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