tailieunhanh - Lecture Database Systems - Lecture 27

After completing this chapter, students will be able to: Remaining activities/steps in building conceptual data model, logical database design, purpose of normalization, data redundancy and update anomalies, functional dependencies. | CSC271 Database Systems Lecture # 27 Summary: Previous Lecture Remaining activities/steps in building conceptual data model Logical database design Build and validate logical database design Derive relations for logical data model Validate relations using normalization Normalization Chapter 13 Purpose of Normalization Normalization is a technique for producing a set of suitable relations that support the data requirements of an enterprise Characteristic of Suitable Relations The characteristics of a suitable set of relations include the following: The minimal number of attributes necessary to support the data requirements of the enterprise Attributes with a close logical relationship (described as functional dependency) are found in the same relation Minimal redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys, which are essential for the joining of related relations Benefits of DB with Suitable Relations The benefits of using a database that has a suitable set of relations are: Easier for the user to access and maintain the data Take up minimal storage space on computer Normalization Supporting DB Design Data Redundancy & Update Anomalies Major aim of relational database design is to group attributes into relations to minimize data redundancy If this aim is achieved, the potential benefits for the implemented database include the following: Updates to the data stored in the database are achieved with a minimal number of operations thus reducing the opportunities for data inconsistencies occurring in the database Reduction in the file storage space required by the base relations thus minimizing costs Data Redundancy & Update Anomalies Problems associated with data redundancy are illustrated by comparing the Staff and Branch relations with the StaffBranch relation The StaffBranch relation is an alternative format of the Staff and Branch relations Staff (staffNo, sName, position, salary, . | CSC271 Database Systems Lecture # 27 Summary: Previous Lecture Remaining activities/steps in building conceptual data model Logical database design Build and validate logical database design Derive relations for logical data model Validate relations using normalization Normalization Chapter 13 Purpose of Normalization Normalization is a technique for producing a set of suitable relations that support the data requirements of an enterprise Characteristic of Suitable Relations The characteristics of a suitable set of relations include the following: The minimal number of attributes necessary to support the data requirements of the enterprise Attributes with a close logical relationship (described as functional dependency) are found in the same relation Minimal redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys, which are essential for the joining of related relations Benefits of DB with Suitable Relations