7/3/2023 0 Comments Database normalization![]() ![]() Therefore, the table does not satisfy the definition of BCNF. The functional dependency PROJECT_NO → DURATION is nontrivial. ![]() We cannot pair rows representing two different SSNs working in the same PROJECT_NO and DEPT_NO for example: However, the previous table is not in BCNF, because PROJECT_NO is not a primary key. This means that the attribute address is functionally determined by SSN. If we know the SSN of particular employee, we can find out the address of that employee. It states the interdependency of attributes in a table. Functional Dependencyįunctional dependency is the base of normalization. This is ridiculous because we want to delete an employee information, not the whole department. Suppose we delete an employee from the database, for example, the last employee in the above table, who is the sole representative of DNO=1, then all the information about the department is also lost. Otherwise, the database will be in an inconsistent state. If we change the value of one of the departments by, say, changing its DNAME or MGR_SSN, we also must update the value of all employees who work in that department. For example, department 4, its DNAME must be ‘ Administration‘ and MGR_SSN must be ‘ 234567890‘. Further, if we insert a new employee for a department, say, 4, the other attributes of the department have to be consistent. If we want to insert new employee who has not been assigned any department, the attribute values of the department for that particular employees has to be kept null. This type of redundant data leds to updation, insertion, and deletion anomalies. Observe how DNO and DNAME are repeatedly stored in the table. ![]() Table 1: Employees working in different departments showing redundant data Storing redundant data not only takes more space but also leads to more serious problems. The tables should be designed in such a manner that very minimal data is repeatedly kept in one or more table. One of the primary considerations for table design is to minimize storage space requirements. Before We Begin…īefore we begin with the rules of normalization and applying them, we must understand the following concepts. It basically takes a relational schema through a series of test to minimize redundancy and insertion, deletion, and update anomalies by decomposing into smaller relations. In fact, any schema, if it is working, must follow certain rules of it. Codd in 1972 since then, it has been the cornerstone of every relational database design. Normalization is a very basic and important concept to consider when designing a workable relational schema in the database. ![]()
0 Comments
Leave a Reply. |