Describe functional dependency only; NOT full functional dependency, or partial dependency, or transitive dependency. Hint – review the Functional Dependencies Topic in the Terms and Concepts discussion. While you may incorporate the formal definition, you must explain the concept in your own words, using fields names and values from this exercise.
Identify the functional dependencies that exist in the above figure. All attributes should be included at least once. There may be more than one row of functional notation needed.
2) Identify a primary key for the table in the above figure.
Indicate whether there are any alternate keys (for this table)
explain each of the above choices
3) Is the table in 3NF?
If not, explain why – (provide specific rationale, use field names and values in the table to demonstrate your understanding).
Explain what normal form the table provided is in.
Full Answer Section
- OrderID → ProductOrdered: If you know the
OrderID, you can find the ProductOrdered.
- OrderID → OrderDate: If you know the
OrderID, you can find the OrderDate.
- OrderID → ShippingAddress: If you know the
OrderID, you can find the ShippingAddress.
Functional Dependencies in the Figure:
To accurately answer this, I need to know what the figure contains. Since I don't have the figure, I will create a hypothetical table, and then apply the logic to it.
Hypothetical Table:
Functional Dependencies:
- OrderID → CustomerID, CustomerName, ProductID, ProductName, OrderDate, ShippingAddress
- CustomerID → CustomerName, ShippingAddress
- ProductID → ProductName
2. Identifying Primary and Alternate Keys:
- Primary Key:
OrderID is the primary key.- Explanation: Each
OrderID is unique, and it can be used to identify a specific order and all associated information.
- Alternate Keys:
- There are no alternate keys in this example.
- Explanation: An alternate key would be another column or set of columns that uniquely identifies each row. In this example,
CustomerID does not uniquely identify each row, as one customer may have multiple orders.
3. Normalization:
- Is the table in 3NF?
- No, the table is not in 3NF.
- Why?
- The table is in 1NF. To be in 2NF, it must be in 1NF and have no partial dependencies. To be in 3NF, it must be in 2NF and have no transitive dependencies.
CustomerID -> CustomerName and ShippingAddress. This is a transitive dependancy. OrderID -> CustomerID -> CustomerName and ShippingAddress.ProductID -> ProductName. This is another transitive dependancy. OrderID -> ProductID -> ProductName.
- What Normal Form is the table in?
- The table is in 1NF (First Normal Form). This is because all attributes are atomic (indivisible), and there are no repeating groups.
Sample Answer
. Functional Dependency (General Definition):
In its most basic form, functional dependency means that the value of one attribute (or set of attributes) determines the value of another attribute. If you know the value of the first attribute, you can definitively find the value of the second.
Applying it to the table:
Let's say we have a table with the following fields: OrderID, CustomerID, CustomerName, ProductOrdered, OrderDate, and ShippingAddress.
- OrderID → CustomerID: If you know the
OrderID, you can find the CustomerID associated with that order.
- CustomerID → CustomerName: If you know the
CustomerID, you can find the CustomerName of that customer.