Question

7. A relation schema is given as SalesRecord(sID, sName, cID, CID, PNo, pName, quantity). The functional dependencies are fd1. SID, CID - pNo, pName, quantity fd2. SID - SName fd3. CID cName fd4. pName Normalize this to 3NF. Answers should be text-form schema. pNo

Answer 1

SalesRecord(sID,sName,cID,pNo,pName,quantity)

sID,cID--->pNo,pName,quantity

sID--->sName

cID--->cName

pNo--->pName   candidate key=sID,cID

for 1NF:   sID,cID--->pNo

sID,cID--->pName

sID,cID--->quantity

sID--->sName

cID--->cName

pNo--->pName Therefore 1NF satisfied.

for 2NF: candidate key=sID,cID prime attributes: {sID,cID}

non-prime attributes: {sName,cID,pNo,pName,quantuty,cName}

sID,cID--->pNo : total dependency

sID,cID--->pName : total dependency

sID,cID--->quantity : total dependency

sID--->sName : partial dependency

cID--->cName : partial dependency

pNo--->pName : total dependency

R:[sID,cID,,pNo,pName,quantity] R1:[sID,sName,cID,cName]

sID,cID--->pNo sID--->sName   

sID,cID--->pName   cID--->cName

sID,cID--->quantity

pNo--->pName Therefore, 2NF satisfied.

for 3NF: sID,cID--->pNo :it is in 3NF

sID,cID--->pName :it is in 3NF

sID,cID--->quantity :it is in 3NF

pNo--->pName :it is not in 3NF, because there is transitive dependency.

R:[sID,cID,,pNo,pName,quantity] R1:[sID,sName,cID,cName] R2:[pNo,pName]

sID,cID--->pNo sID--->sName pNo--->pName

sID,cID--->pName   cID--->cName

sID,cID--->quantity

Now, 3NF is satisfied.