Week 6 Exercise
Computer Sciences and Information Technology
Review question 14.17 and 14.18 on page 429 – 430 of the Connolly textbook as background for the below question.
Then, using the information in the figure below, answer the following questions. (The sample data may not represent all future field values; use common knowledge and consider the domain for each field)
We record only the last sale for each car. For each sale, a car can be sold to only one customer; however, each customer can buy (own) more than one car.
Include the below numbers to organize the submission. DO NOT include the Questions or other content from the instructions.
1) 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 forum. While you may incorporate the formal definition, you must explain the concept in your own words, using fields names and values from this exercise. 15%
Using the functional dependency notation shown on pages 412, 413 Examples 14.4, 14.5, 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. 15%
2) Identify a primary key for the table in the above figure. 10%
Indicate whether there are any alternate keys (for this table) 15%
explain each of the above choices
3) Is the table in 3NF? 10%
If not, explain why – (provide specific rationale, use field names and values in the table to demonstrate your understanding). 15%
Explain what normal form the table provided is in. 10%
APA guidelines, spelling, grammar, file name 10%
Submit a Word compatible document. Name your document Last Name_Assignment6 (i.e. Smith_Assignment6)
1)
Connolly defines functional dependency as a constraint that occurs between a set of attributes in a relation. In the table given both the fields that include the year and the manufacturer of the car are functionally dependent. This is seen on the CarVin field. At the same time, this field can only possess a single value of the Manufacturer, Year and CusID. The field consisting of the CusID is functionally dependent as it determines the fields including the FirstName, LastName. This is because for the CusID field there can only be one value that points to the FirstName and LastName.
CarVin# => Manufacturer, Year, CusID, Manufacturer, Year, SalePrice
CusID=> LastName, FirstName, StAddr, City, Phone#, LastName, FirstName , StAddr, City, Phone#
2)
The primary key is The CarVin. CusID is the alternate key. This is known as a composite key. Both of these fields uniquely identify various fields in the key. Using both as the primary key enhances the exclusivity of the database because both fields will be used when Question Assignmenting particular data. For instance, through the value of the CarVin# one would have the ability to determine who the customer is and get the Manufacturer of the car, the year when it was manufactured as well as details from the CusID field. This field has the ability to determine values from LastName, FirstName which are able to point to the address field, StAddr, City and Phone#. The CusID can hence be used as an alternate key because it has the ability to uniquely identify the rows. There are no repeating tuples in the table. This situation ensures that there is no redundancy in the table and only a particular set of keys is unique and used to identify information pertaining the sale and the information about the customer.
3)
The table cannot belong to 3NF. If it were to belong to this category then we would have to remove the transitive dependency that exists in the table. The table consists of various partial dependencies. Eliminating these dependencies is the first step in moving it to the second normal form. The fields; StAdddr and Phone# depend on the Name fields that is the FirstName and LastName. There comes a transitive dependency on the grounds that the FirstName and LastName are used to uniquely identify these fields yet there are not the primary keys in the table. In order to categorize it as 3NF we would move the fields into a new table. When field are moved to a new table partial dependencies will be eliminated and it will be moved to the third normal form.
The table is categorized as 1NF. Carlson and Arora define the first normal form as a relation where the domain of an attribute cannot be further separated or is indivisible (1982). This is because there are still partial dependencies among the fields in the table. Fields such as FirstName and LastName still depend on the CustID rather than the primary key. To transform to 2NF it requires that we move the fields FirstName, City StAddr, City, Phone#, LastName to a new table and create a relation. The second normal form is where a relation belongs to the first normal form and at the same time does not consist of non-prime attributes that are functionally dependent (Fagin 1977). The determining key would qualify as the primary key. We would pick CusID as the primary key.
References
Connolly, T. M., & Begg, C. E. (2005). Database systems: a practical approach to design, implementation, and management. Pearson Education.
Carlson, C. R., & Arora, A. K. (1982). The application of functional dependency theory to relational databases. The Computer Journal, 25(1), 68-73.
