How to Differentiate a good database design from a bad database design easily

A good relational database system should be capable of maintaining a good relationship among the data’s and generate new relations among the existing data’s in the database system. A database is said to be a relational system, when the database system satisfies Codd twelve rules and relational theory in algebra. In a relational database system there exist some relationship among the stored data’s, it is also possible to form relations from the stored data with the help of relational algebra. An important concept in relational schema design is that of a functional dependency. Normalization theory is based on the functional notion of functional dependency and moreover it helps in simplifying the structure of tables.

A functional dependency is a property of the semantics or meaning of the attributes. The database designers will use their understanding of the semantics of the attributes, how they relate to each one another. Certain FD’s can be specified without referring specific relation, but as the property of those attributes. Functional dependency plays a key role in establishing and maintaining a relationship among the data’s that are functionally related to one another and they are separated from other non-related data’s thus providing clear relationship among the set of data’s present.

Functional dependencies of the data’s are verified and implied in the second normal form. Normalization is basically used to eliminate data redundancy and provide data integrity. Every data’s in the database will be stored in row and column format that is table format. Axioms or rules of inference provide a simpler technique for reasoning about functional dependencies. We can also use other rules to find the logically implied functional dependencies. By applying these rules we can easily identify the columns that are functionally dependent or not. They are referred as inference rules as a whole.

1. Reflexivity rule.
2. Augmentation rule.
3. Transitivity rule.
4. Decomposition or projective rule.
5. Union or additive rule.
6. Pseudotransitive rule.

The reflexivity rule states that the set of attributes always determines it self or any of its subsets. Augmentation rule states that adding the same set of attributes to both the left and right hand sides of a dependency result in valid dependency. All functional dependencies are transitive in nature. The decomposition rule states that we can remove attributes from the right hand side of the dependency applying this rule repeatedly we can decompose into many relations. This collection of rules is called as Armstrong’s axioms. These axioms are strong because they do not generate any incorrect dependencies, they are complete.

Functional dependencies of data’s are nothing but every non-key attribute is functionally or fully dependent on the primary key. No non-key attribute exists in the relation that is all the columns are components of the primary key. What is a primary key? Primary key is basically used to avoid duplicate values or data’s in our parent table, moreover a parent table can have only one primary key but we can assign a single primary key to combination of columns, which is called a composite primary key.

For example a table called employee contains four columns namely empnumber which is set as a primary key for this employee table, First name, Last name, and company details. In this table the columns first name, last name are related to the empnumber (employee details) that is each one has relationship with others, with help of empnumber we can uniquely identify the employee and view his details. But the fourth column gives us information about the company it has no relationship with the primary key (empnumber) of the employee table.

Hence this column should be segregated and it should be stored separately in another table. Thus functional dependency helps in maintaining a relationship among the data’s present in the table. Any columns that are not dependent on the primary of the parent table should be segregated. Every column should be dependent on the primary key of the parent table. Hence FD’s are useful in maintaining relations and deriving new relations among the data’s and it also provides with data integrity.

Tweet