Week 6 Database Exercise Essay

Week 6 Database Exercise Essay

Week 6 Database Exercise

1. Functional Dependency

Functional dependency occurs when one property controls the other uniquely (Sug, 2020). This table’s year and manufacturer columns are functionally reliant on the CarVin# attribute. CarVin# field has a unique six-digit identifier for every vehicle. Every CarVin# can only have one of the following values: CustID, Manufacturer, and Year. CarVin# includes the automobile’s manufacturer, year of make, and a CustID given to each car. Because each CustID can have only one entry for the FName and LName sections, the CustID functionally binds them. The color, model and make of a car are functionally dependent on the car identifier, CarVin#.

ORDER A PLAGIARISM-FREE PAPER HERE

In a database, functional dependency imposes a limit among two sets of characteristics. Establishing functional dependency is a significant aspect of relational database management systems and helps to normalize elements. Third normal form and Boyce-Codd normal form are defined by functional dependency (Sug, 2020). This keeps attribute dependency while avoiding data redundancy. A candidate key is associated with functional dependency since it distinctively defines a tuple and identifies the value of all other properties in the relation.

2. Primary Key

According to Jiang & Naumann (2020), a primary key is the field or fields in a table that holds values that uniquely identify every row.  CarVin# forms the primary key as it distinguishes the vehicles sold. CustID could be an alternate key. It is a unique identification number for every client to whom a car is sold and can also be used to identify rows in the table individually. Keys are used in databases to analyze, filter, and update records, as well as to develop connections between them (Jiang & Naumann, 2020). Identifying the primary key in a database is a critical element in the database development. It can be a standard variable that is assured to be distinct, such as a National Insurance number on a table with only one entry per individual, or it can be created by the database system, such as a GUID, in MS SQL Databases (Jiang & Naumann, 2020). Primary keys might be made up of a single characteristic or a combination of several properties.

3. Table Normalization Form

Normalization is the act of designing and managing data relationships in order to reduce duplication in relational tables and eliminate unwanted database abnormalities such as delete, update, and insertion (Demba, 2013). It is beneficial to break huge tables in the database into smaller tables and bind them together. It can reduce superfluous data and make it easier to add, modify, or eliminate table fields. A normalization specifies guidelines for determining if a relational table meets the normal form. A normal form is a technique that assesses each relationship against predefined criteria and eliminates multivalued, combines, functional, and insignificant dependency from a relationship.

The table is not in 3NF since all variables in a table are non-transitive based on the primary key. Demba (2013) stated that a table cannot be in 3NF if it includes transitive dependencies. For example, city, staddr, fname, and lname depend on CarVin#, the primary key, and CustID, a non-primary property.

The table has been normalized, or in 1NF – every field has indivisible entries. When we look at the City property, we see that it only has one value, which cannot be separated into several values. The same is true for all other qualities. The table is in 2NF since there is no non-prime trait dependence on any subset of the candidate key.

References

Demba, M. (2013). Algorithm for relational database normalization up to 3nf. International Journal of Database Management Systems, 5(3), 39–51. https://doi.org/10.5121/ijdms.2013.5303

Jiang, L., & Naumann, F. (2020). Holistic primary key and foreign key detection. Journal of Intelligent Information Systems, 54(3), 439–461. https://doi.org/10.1007/s10844-019-00562-z

Sug, H. (2020). Efficient checking of functional dependencies for relations. Journal of Physics: Conference Series, 1564(1), 012011. https://doi.org/10.1088/1742-6596/1564/1/012011

Week6 Database Exercise

Instructions

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 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. 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).