Sql server® 2012 t-sql fundamentals

orderid

PK

orderdate

customerid

companyname

OrderDetails

orderid

productid

PK,FK1

PK

qty

FIGuRE 1-2

  Data model after applying 2NF and before 3NF. 

3NF

  The third normal form also has two rules. The data must meet the second normal form. Also, all 

non-key attributes must be dependent on candidate keys non-transitively. Informally this rule means 

www.it-ebooks.info

---

 

CHAPTER 1

 

Background to T-SQL Querying and Programming

 

 9

that all non-key attributes must be mutually independent. In other words, one non-key attribute can-

not be dependent on another non-key attribute.

The Orders and OrderDetails relations described previously now conform to the second normal 

form. Remember that the Orders relation at this point contains the attributes orderid, orderdate, 

 customerid, and companyname, with the primary key defined on orderid. Both customerid and 

 companyname depend on the whole primary key—orderid. For example, you need the entire pri-

mary key to find the customerid representing the customer who placed the order. Similarly, you need 

the whole primary key to find the company name of the customer who placed the order. However, 

customerid and companyname are also dependent on each other. To meet the third normal form, you 

need to add a Customers relation (shown in Figure 1-3) with the attributes customerid (as the primary 

key) and companyname. Then you can remove the companyname attribute from the Orders relation.

OrderDetails