Содержание
- 2. There are two approaches to the logical design of the database: -The top-down approach -Bottom-up approach
- 3. Through normalization we want to design for our relational database a set of files that (1)
- 4. Data redundancy Data redundancy means their repeatability. Redundancy increases the time it takes to update, add,
- 5. Example of data redundancy Consider the structure of the table student of STUDENT (_Code, _Name, _DateOfBirth,
- 6. Normalization can be viewed as a series of steps (i.e., levels) designed, one after another, to
- 7. Functional Dependency and Determinants The essence of this idea is that if the existence of something,
- 8. In general, a functional dependency is a relationship among attributes. In relational databases, we can have
- 9. A simple example of the functional dependence Table “Employee”: All attributes in the table must be
- 10. Key concept is in terms of functional dependencies Functional dependence can be given the following definition:
- 11. The 1st Normal Form (1NF) Definition: A table (relation) is in 1NF if 1. There are
- 12. Example1 Consider a table “Projects”
- 13. Applying the requirements of 1NF, we obtain the following table:
- 14. Table1 satisfies the definition of 1NF: viz., it has no duplicated rows; each cell is single-valued
- 15. A key attribute will, by the definition of key, uniquely determine the values of the other
- 16. In Table2 the Level attribute can be said to be functionally dependent on the Major attribute.
- 17. The 2nd Normal Form (2NF) Definition: A table is in 2NF if it is in 1NF
- 18. The table is in 2NF if it is in 1NF and every attribute in a row
- 19. Table2 has another interesting aspect. Its key is a composite key, consisting of the paired attributes,
- 20. Thus this table fails to qualify as a 2nd Normal Form table, since the definition of
- 21. Example1 With the SSN defined as the key, Table 3 is in 2NF, as you can
- 22. Anomalies and Normalization At this point it is appropriate to note that the main thrust behind
- 23. Turning a Table with Anomalies (Table 3) into Single-Theme Tables
- 24. Example2 Consider a table “Project”
- 25. Instructions for applying the changes to the table Project in 2NF, we obtain the following table:
- 26. The 3rd Normal Form (3NF) Definition: A table is in 3NF if it is in 2NF
- 27. Example1. Consider the table Employees
- 28. Applying the guidelines to the transformation of the employee table in 3NF, we obtain the following
- 29. Example2. The following table, Table 4, provides an example of how transitive dependencies can occur in
- 30. By examining Table 4 we can infer that books dealing with history, cognitive psychology, and folksong
- 31. Thus we see that there is a transitive dependency in Table4: any book that deals with
- 32. What is wrong with having a transitive dependency or dependencies in a table? For one thing,
- 33. Table 5
- 35. You can verify for yourself that none of these tables contains a transitive dependency; hence, all
- 36. That would be more economical of storage space than Table 5. Furthermore, the structure of these
- 37. The Boyce-Codd Normal Form (BCNF) Definition: A table is in BCNF if it is in 3NF
- 38. Example1. We begin by showing that Table_6 lacks the required property, viz., that every determinant be
- 39. Still another determinant is the attribute, Adviser, for each different value of Adviser determines a unique
- 40. It is easy to check on the anomalies in Table6. For example, if student 987-65-4321 were
- 42. The basic definition of NF 3 is inadequate and inappropriate for the tables: -Having multiple candidate
- 43. Example2. Consider a table “Projects”
- 44. After applying the changes to the table "Projects" in BCNF, we obtain the following table:
- 45. Denormalization Input in the table intentional redundancy to improve query performance is called denormalization. Denormalization is
- 46. After applying denormalization table “Orders”, get the following table:
- 47. Conclusion In this lesson, you learned that: There are two approaches to the logical design of
- 48. The most important and commonly used normal forms are: -First Normal Form (1 NF) -Second Normal
- 50. Скачать презентацию