Problem 1. Understanding Keys and Normalization Read Access Comprehensive Ch 11

Question

Problem 1. Understanding Keys and Normalization Read Access Comprehensive Ch 11 Database Design, Help with the Assessment Problem 1. And answer the following questions: 1. Figure below contains sample data for parts and for suppliers who supply those parts. In discussing the data with users, you find that part numbers but not descriptions -uniquely identify parts, and that supplier names uniquely identify suppliers. Multiple suppliers can supply the same part. For example, air deflectors can be purchased from either Asteman or MZ Technologies. Convert this relation into an equivalent set of relations in third normal form (3NF). In word document write out all transitions from the first normal form to the third normal form following format in the chapter 11, pp.AC696-AC705 Item Item Number Description Supplier Name Address Unit Cost 1234 Air Deflector 5.00 Asterman Georgetown JMZ Technologies Buda 4.50 5678 Energy Saving Kit Scryps Distributors San Rita 3.00 Georgetown 2.00 Aster man JMZ Technologies Buda

Explanation / Answer

The distinction between 3NF and BCNF is refined.

3NF

Definition

A relation is in 3NF if it's in 2NF and no non-prime attribute transitively depends on the first key. In different words, a relation R is in 3NF if for every practical dependency X A in R a minimum of one in every of the subsequent conditions area unit met:

X may be a key or superkey in R
A may be a prime attribute in R
Example

Given the subsequent relation:

EMP_DEPT(firstName, employeeNumber, dateOfBirth, address, departmentNumber, departmentName)

An worker will solely add one department and every department has several staff.

The candidate secret's employeeNumber.

Consider the subsequent practical dependencies:

employeeNumber firstName, dateOfBirth, address, departmentNumber
departmentNumber departmentName
Given the definition on top of it's attainable to conclude that the relation EMP_DEPT isn't in 3NF as a result of the second practical dependency doesn't meet any of the two conditions of the 3NF:

departmentNumber isn't a key or superkey in EMP_DEPT
departmentName isn't a main attribute in EMP_DEPT
BCNF

Definition

A relation R is in BCNF if it's in 3NF and for every practical dependency X A in R, X may be a key or superkey in R. In different words, the sole distinction between 3NF and BCNF is that in BCNF it's not gift the second condition of the 3NF. This makes BCNF stricter than 3NF as any relation that's in BCNF are going to be in 3NF however not essentially each relation that's in 3NF are going to be in BCNF.

Example

Given the subsequent relation:

STUDENT_COURSE(studentNumber, socialSecurityNumber, courseNumber)

A student will assist {to several|to several} courses and in a very course there will be many students.

The candidate keys are:

socialSecurityNumber, courseNumber
studentNumber, courseNumber
Consider the subsequent practical dependencies:

studentNumber socialSecurityNumber
socialSecurityNumber studentNumber
Given the definitioin on top of it's attainable to conclude that STUDENT_COURSE isn't in BCNF as a minimum of studentNumber isn't a key or superkey in STUDENT_COURSE.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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