際際滷

際際滷Share a Scribd company logo

Database Management System

Download as PPT, PDF
Varun Arora
Varun Arora

The document discusses database management system keys and normalization. It defines different types of keys like candidate keys, super keys, primary keys and foreign keys. It also explains database normalization forms like 1st normal form, 2nd normal form, 3rd normal form and Boyce-Codd normal form. The purpose of normalization is to organize data to avoid duplication and dependency issues to improve data integrity. Denormalization re-introduces some duplication to improve query performance.

1 of 34
Downloaded 655 times
Database Management System Keys and Normalization June 2, 2009 Varun Arora, SICSR 2007-'09 By: Varun Arora [email_address] www.varunarora.in
Keys Keys (in ERDs) are certain attributes which can be used to identify instances of that entity. Can be of many types namely Candidate Keys Super Keys or Composite Keys Primary Keys Foreign Keys Are useful in any System. June 2, 2009 Varun Arora, SICSR 2007-'09
Candidate Keys Candidate Keys are those attributes which can be used for the purpose of developing some higher level keys. (Super keys, Primary keys or foreign keys) Example Purchase Order Master Table. June 2, 2009 Varun Arora, SICSR 2007-'09
Super Keys Super keys are those keys which are formed to uniquely identify the instances as well as the attributes of an entity and are formed as a result of the combination of one or more attributes of that entity. Example Purchase Order Details Table June 2, 2009 Varun Arora, SICSR 2007-'09
Primary Key Primary key is that candidate key which is used to uniquely identify each and every attribute and instance of a particular entity. Example Purchase Order Master Table June 2, 2009 Varun Arora, SICSR 2007-'09
Foreign Key A Foreign key is an attribute in some another entity which is already a primary key in some entity. Is used for the purpose of maintaining the integrity of the data. Example The Purchase Order Master and the Purchase Order Detail Relationship. June 2, 2009 Varun Arora, SICSR 2007-'09
Keys - Purpose Purpose of Identifying attributes as some or the other kind of a key is Unique Identification of records Easy Maintenance of the Integrity of Data Help in the searching of the data based on their relationships of Primary Foreign Key and thus facilitate easy querying at the top level. (this issue is excluded at the point of design.) June 2, 2009 Varun Arora, SICSR 2007-'09
Sample ER (Purchase Order) June 2, 2009 Varun Arora, SICSR 2007-'09 Contains Purchase Order Master Purchase Order Detail Po_no (PK) Po_dt Po_amt Supp_cd . Po_no (FK) Item_cd (FK) quantity Unit_price . Po_no Po_dt Supp_cd Po_amt Po_no Item_cd quantity Unit_price 1 M
Extended E-R Features ER Diagrams can be further used to generate views regarding overall hierarchy of the Entity Association (Relationships). This can be done using these concepts Specialization Generalization. June 2, 2009 Varun Arora, SICSR 2007-'09
Specialization It is a concept from which several sub-entities can be derived which have certain attributes different from the common entity (or parent entity) from which they are derived. Example Account ISA Savings or Account ISA Current Account June 2, 2009 Varun Arora, SICSR 2007-'09
Generalization It is a concept wherein the entities having a common set of attributes are grouped together to form a common entity. Example Confirmed Ticket ISA Ticket or Waiting Ticket ISA Ticket. June 2, 2009 Varun Arora, SICSR 2007-'09
Normalization..Topics Functional Dependency 1 st Normal Form 2 nd Normal Form 3 rd Normal Form 4 th or the Boyce Codd Normal Form (BCNF) Denormalization Need for Normalization (and Denormalization) June 2, 2009 Varun Arora, SICSR 2007-'09
Functional Dependency A functional dependency, denoted by X-to-Y (X->Y) between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuples that can form a relation instance r of R. It therefore states that , for any tuples t1 and t2 if t1[X] = t2[X] then t1[Y] = t2[Y]. That is the values of X Component uniquely determine the values of the Y component. June 2, 2009 Varun Arora, SICSR 2007-'09
Functional Dependency..Example June 2, 2009 Varun Arora, SICSR 2007-'09 In this table the values in the attributes Name Marks and %age are functionally dependent on the values in the attribute Roll No because if the value of Roll No changes so does the value of the other attributes changes. Roll No Name Marks %age R001 Raj 300 50% R002 Raj 360 60% R003 Rahul 420 70%
Normalization Normalization of data can be looked on as a process during which unsatisfactory relation schemas are decomposed by breaking up their attributes into smaller relation schemas that possess the desirable properties. The various stages or tests that can be performed to achieve this are called as the Normal Forms and are available as 1NF, 2NF, 3NF, BCNF. June 2, 2009 Varun Arora, SICSR 2007-'09
1 st Normal Form A Relation is said to be in the 1 st Normal Form when the attributes contain values that are atomic in nature. That is, it disallows the relations to contain relation within relations. In general sense, a Relation is said to be in the 1 st Normal Form when the attributes are single valued. June 2, 2009 Varun Arora, SICSR 2007-'09
1 st Normal Form - Example June 2, 2009 Varun Arora, SICSR 2007-'09 Denormalized Data consisting of Attributes with more than one values. Ecode Ename Ecity Ephone E001 Raj Ahmedabad Pune Mumbai 2344567 2344568 E002 Rahul Udaipur 2334445 2334455 E003 Ramesh Jaipur 2345678
1 st Normal Form - Example June 2, 2009 Varun Arora, SICSR 2007-'09 Attributes Containing Atomic Values ECode Ename Ecity Ephone E001 Raj Ahmedabad 2344567 E001 Raj Pune 2344568 E001 Raj Mumbai E002 Rahul Udaipur 2334445 E002 Rahul Udaipur 2334455 E003 Ramesh Jaipur 2345678
2 nd Normal Form A Relation is said to be in the 2 nd Normal Form if all the attributes of the Relation are fully functionally dependent on the whole attribute and not just a part of the attribute. In general sense, it can be stated that a relation is in a 2 nd Normal form if all attributes depend on the whole key and not just a part of the key. June 2, 2009 Varun Arora, SICSR 2007-'09
2 nd Normal Form - Example June 2, 2009 Varun Arora, SICSR 2007-'09 Attributes which decide the uniqueness of the data but the absence of one makes the Dependency partial not absent. Contd.. Ecode Ename Dept PrjCd PrjHrs E001 Raj MKT P001 30 E002 Rahul FIN P002 40 E003 Ramesh FIN P002 40 E004 Mahesh MKT P003 40
2 nd Normal Form - Example June 2, 2009 Varun Arora, SICSR 2007-'09 Full Functional Dependency on the PrjCode Attribute Full Functional Dependency on the ECode Attribute Ecode Ename Dept E001 Raj MKT E002 Rahul FIN E003 Ramesh FIN E004 Mahesh MKT PrjCode Ecode PrjHrs P001 E001 30 P002 E002 40 P002 E003 40 P003 E004 40
Third Normal Form A relation is said to be in the Third Normal Form if there is no transitive functional dependency between non key attributes When one non key attribute can be determined with one or more non key attributes there is said to be a transitive functional dependency. Example The drug administered is dependent on the drug which is in turn dependent on the composite key displayed. June 2, 2009 Varun Arora, SICSR 2007-'09
June 2, 2009 Varun Arora, SICSR 2007-'09 This non key attribute is dependent on the non key attribute Drug Admin so it is not in the Third Normal Form. Source www.sims.berkely.edu
June 2, 2009 Varun Arora, SICSR 2007-'09 Here all the non key attributes are full functionally dependent on a single key attribute. Source www.sims.berkely.edu
June 2, 2009 Varun Arora, SICSR 2007-'09 Here all the non key attributes are full functionally dependent on a single key attribute. Source www.sims.berkely.edu
Boyce Codd Normal Form Most 3NF Relations are also BCNF Relations. A 3NF Relation is not in BCNF if Candidate keys in the relation are composite keys (they are not single attributes) There is more than one Candidate key in the relation, and The keys are not disjoint, that is some attributes in the keys are common. June 2, 2009 Varun Arora, SICSR 2007-'09
June 2, 2009 Varun Arora, SICSR 2007-'09 Source www.sims.berkely.edu In this relation there is a single attribute Patient# which is a Candidate Key and it serves as the primary key also. There is no other attribute Which can serve as the Candidate key. And along with this the attribute is also disjoint
June 2, 2009 Varun Arora, SICSR 2007-'09 Source www.sims.berkely.edu
Denormalization It is the reverse process to Normalization wherein the redundancy in the relations is reduced. Here redundancy is added to speed up the processing of the queries and reduce the load from the server when the queries become more and more complex and involve more than one table. Example Refer to the 2NF-3NF Conversion Example. June 2, 2009 Varun Arora, SICSR 2007-'09
June 2, 2009 Varun Arora, SICSR 2007-'09 The Relations are in the 3NF State but to extract The effect of a certain Drug a join has to be Formed on the two Relations which an be time Consuming. Source www.sims.berkely.edu
Denormalized Relation. June 2, 2009 Varun Arora, SICSR 2007-'09 FD1 FD2 Source www.sims.berkely.edu Relation Transition 3NF->2NF
Importance of Normalization Normalization of a Relation helps In Developing Individual and more simple structures In reducing redundancy In Removing the complexity of remembering the dependencies among attributes In making the entities independent. June 2, 2009 Varun Arora, SICSR 2007-'09
Importance of Denormalization Denormalization is used to Improve the performance of the queries which take more time by adding required redundancy. This helps reducing the amount of joins and makes the queries simple to run cause most of the data can be fetched from a single table. Improve efficiency Making the Structure more simpler for the end user. June 2, 2009 Varun Arora, SICSR 2007-'09
June 2, 2009 Varun Arora, SICSR 2007-'09 Un-Normalized Data Normalized Data Normalization tests And Techniques Lower Level of Normalization Normalized Data + Redundancy A Final Note ..

More Related Content

Database Management System

  • 1. Database Management System Keys and Normalization June 2, 2009 Varun Arora, SICSR 2007-'09 By: Varun Arora [email_address] www.varunarora.in
  • 2. Keys Keys (in ERDs) are certain attributes which can be used to identify instances of that entity. Can be of many types namely Candidate Keys Super Keys or Composite Keys Primary Keys Foreign Keys Are useful in any System. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 3. Candidate Keys Candidate Keys are those attributes which can be used for the purpose of developing some higher level keys. (Super keys, Primary keys or foreign keys) Example Purchase Order Master Table. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 4. Super Keys Super keys are those keys which are formed to uniquely identify the instances as well as the attributes of an entity and are formed as a result of the combination of one or more attributes of that entity. Example Purchase Order Details Table June 2, 2009 Varun Arora, SICSR 2007-'09
  • 5. Primary Key Primary key is that candidate key which is used to uniquely identify each and every attribute and instance of a particular entity. Example Purchase Order Master Table June 2, 2009 Varun Arora, SICSR 2007-'09
  • 6. Foreign Key A Foreign key is an attribute in some another entity which is already a primary key in some entity. Is used for the purpose of maintaining the integrity of the data. Example The Purchase Order Master and the Purchase Order Detail Relationship. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 7. Keys - Purpose Purpose of Identifying attributes as some or the other kind of a key is Unique Identification of records Easy Maintenance of the Integrity of Data Help in the searching of the data based on their relationships of Primary Foreign Key and thus facilitate easy querying at the top level. (this issue is excluded at the point of design.) June 2, 2009 Varun Arora, SICSR 2007-'09
  • 8. Sample ER (Purchase Order) June 2, 2009 Varun Arora, SICSR 2007-'09 Contains Purchase Order Master Purchase Order Detail Po_no (PK) Po_dt Po_amt Supp_cd . Po_no (FK) Item_cd (FK) quantity Unit_price . Po_no Po_dt Supp_cd Po_amt Po_no Item_cd quantity Unit_price 1 M
  • 9. Extended E-R Features ER Diagrams can be further used to generate views regarding overall hierarchy of the Entity Association (Relationships). This can be done using these concepts Specialization Generalization. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 10. Specialization It is a concept from which several sub-entities can be derived which have certain attributes different from the common entity (or parent entity) from which they are derived. Example Account ISA Savings or Account ISA Current Account June 2, 2009 Varun Arora, SICSR 2007-'09
  • 11. Generalization It is a concept wherein the entities having a common set of attributes are grouped together to form a common entity. Example Confirmed Ticket ISA Ticket or Waiting Ticket ISA Ticket. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 12. Normalization..Topics Functional Dependency 1 st Normal Form 2 nd Normal Form 3 rd Normal Form 4 th or the Boyce Codd Normal Form (BCNF) Denormalization Need for Normalization (and Denormalization) June 2, 2009 Varun Arora, SICSR 2007-'09
  • 13. Functional Dependency A functional dependency, denoted by X-to-Y (X->Y) between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuples that can form a relation instance r of R. It therefore states that , for any tuples t1 and t2 if t1[X] = t2[X] then t1[Y] = t2[Y]. That is the values of X Component uniquely determine the values of the Y component. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 14. Functional Dependency..Example June 2, 2009 Varun Arora, SICSR 2007-'09 In this table the values in the attributes Name Marks and %age are functionally dependent on the values in the attribute Roll No because if the value of Roll No changes so does the value of the other attributes changes. Roll No Name Marks %age R001 Raj 300 50% R002 Raj 360 60% R003 Rahul 420 70%
  • 15. Normalization Normalization of data can be looked on as a process during which unsatisfactory relation schemas are decomposed by breaking up their attributes into smaller relation schemas that possess the desirable properties. The various stages or tests that can be performed to achieve this are called as the Normal Forms and are available as 1NF, 2NF, 3NF, BCNF. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 16. 1 st Normal Form A Relation is said to be in the 1 st Normal Form when the attributes contain values that are atomic in nature. That is, it disallows the relations to contain relation within relations. In general sense, a Relation is said to be in the 1 st Normal Form when the attributes are single valued. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 17. 1 st Normal Form - Example June 2, 2009 Varun Arora, SICSR 2007-'09 Denormalized Data consisting of Attributes with more than one values. Ecode Ename Ecity Ephone E001 Raj Ahmedabad Pune Mumbai 2344567 2344568 E002 Rahul Udaipur 2334445 2334455 E003 Ramesh Jaipur 2345678
  • 18. 1 st Normal Form - Example June 2, 2009 Varun Arora, SICSR 2007-'09 Attributes Containing Atomic Values ECode Ename Ecity Ephone E001 Raj Ahmedabad 2344567 E001 Raj Pune 2344568 E001 Raj Mumbai E002 Rahul Udaipur 2334445 E002 Rahul Udaipur 2334455 E003 Ramesh Jaipur 2345678
  • 19. 2 nd Normal Form A Relation is said to be in the 2 nd Normal Form if all the attributes of the Relation are fully functionally dependent on the whole attribute and not just a part of the attribute. In general sense, it can be stated that a relation is in a 2 nd Normal form if all attributes depend on the whole key and not just a part of the key. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 20. 2 nd Normal Form - Example June 2, 2009 Varun Arora, SICSR 2007-'09 Attributes which decide the uniqueness of the data but the absence of one makes the Dependency partial not absent. Contd.. Ecode Ename Dept PrjCd PrjHrs E001 Raj MKT P001 30 E002 Rahul FIN P002 40 E003 Ramesh FIN P002 40 E004 Mahesh MKT P003 40
  • 21. 2 nd Normal Form - Example June 2, 2009 Varun Arora, SICSR 2007-'09 Full Functional Dependency on the PrjCode Attribute Full Functional Dependency on the ECode Attribute Ecode Ename Dept E001 Raj MKT E002 Rahul FIN E003 Ramesh FIN E004 Mahesh MKT PrjCode Ecode PrjHrs P001 E001 30 P002 E002 40 P002 E003 40 P003 E004 40
  • 22. Third Normal Form A relation is said to be in the Third Normal Form if there is no transitive functional dependency between non key attributes When one non key attribute can be determined with one or more non key attributes there is said to be a transitive functional dependency. Example The drug administered is dependent on the drug which is in turn dependent on the composite key displayed. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 23. June 2, 2009 Varun Arora, SICSR 2007-'09 This non key attribute is dependent on the non key attribute Drug Admin so it is not in the Third Normal Form. Source www.sims.berkely.edu
  • 24. June 2, 2009 Varun Arora, SICSR 2007-'09 Here all the non key attributes are full functionally dependent on a single key attribute. Source www.sims.berkely.edu
  • 25. June 2, 2009 Varun Arora, SICSR 2007-'09 Here all the non key attributes are full functionally dependent on a single key attribute. Source www.sims.berkely.edu
  • 26. Boyce Codd Normal Form Most 3NF Relations are also BCNF Relations. A 3NF Relation is not in BCNF if Candidate keys in the relation are composite keys (they are not single attributes) There is more than one Candidate key in the relation, and The keys are not disjoint, that is some attributes in the keys are common. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 27. June 2, 2009 Varun Arora, SICSR 2007-'09 Source www.sims.berkely.edu In this relation there is a single attribute Patient# which is a Candidate Key and it serves as the primary key also. There is no other attribute Which can serve as the Candidate key. And along with this the attribute is also disjoint
  • 28. June 2, 2009 Varun Arora, SICSR 2007-'09 Source www.sims.berkely.edu
  • 29. Denormalization It is the reverse process to Normalization wherein the redundancy in the relations is reduced. Here redundancy is added to speed up the processing of the queries and reduce the load from the server when the queries become more and more complex and involve more than one table. Example Refer to the 2NF-3NF Conversion Example. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 30. June 2, 2009 Varun Arora, SICSR 2007-'09 The Relations are in the 3NF State but to extract The effect of a certain Drug a join has to be Formed on the two Relations which an be time Consuming. Source www.sims.berkely.edu
  • 31. Denormalized Relation. June 2, 2009 Varun Arora, SICSR 2007-'09 FD1 FD2 Source www.sims.berkely.edu Relation Transition 3NF->2NF
  • 32. Importance of Normalization Normalization of a Relation helps In Developing Individual and more simple structures In reducing redundancy In Removing the complexity of remembering the dependencies among attributes In making the entities independent. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 33. Importance of Denormalization Denormalization is used to Improve the performance of the queries which take more time by adding required redundancy. This helps reducing the amount of joins and makes the queries simple to run cause most of the data can be fetched from a single table. Improve efficiency Making the Structure more simpler for the end user. June 2, 2009 Varun Arora, SICSR 2007-'09
  • 34. June 2, 2009 Varun Arora, SICSR 2007-'09 Un-Normalized Data Normalized Data Normalization tests And Techniques Lower Level of Normalization Normalized Data + Redundancy A Final Note ..