Lecture 2 (1) (1).pdf

Lecture 2 Attributes in DBMS. Normalization. Types of Normal Forms. Senior Lecturer: Tulebayev Yersultan  Attributes in DBMS. Normalization. Types of Normal Forms: PART I. Attributes in DBMS. Types of the attributes. Super key, Primary key, Foreign key. PART II. F...

Lecture 2 Attributes in DBMS. Normalization. Types of Normal Forms. Senior Lecturer: Tulebayev Yersultan  Attributes in DBMS. Normalization. Types of Normal Forms: PART I. Attributes in DBMS. Types of the attributes. Super key, Primary key, Foreign key. PART II. Functional Dependency. Types of the functional dependencies. PART III. Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database. PART I. Attributes in DBMS. Types of the attributes. Super key, Primary key, Foreign key. Attributes in DBMS Super key Candidate key Primary key Foreign key Non-prime attribute Super key A super key is a set of one or more attributes (columns), which can uniquely identify a row in a table. Example: Table Employee Super keys: The above table has following super keys. All of the following sets of super key are able to uniquely identify a row of the employee table: - {Emp_SSN} - {Emp_Number} - {Emp_SSN, Emp_Number} - {Emp_SSN, Emp_Name} - {Emp_SSN, Emp_Number, Emp_Name} - {Emp_Number, Emp_Name} Candidate key A candidate key is a set of one or more attributes (columns), which can uniquely identify a row in a table with no redundant attribute. Example: Table Employee Candidate Keys: As mentioned in the beginning, a candidate key is a minimal super key with no redundant attributes. The following two set of super keys are chosen from the above sets as there are no redundant attributes in these sets. - {Emp_SSN} - {Emp_Number} Primary key A primary key is a column or a group of columns used to identify a row uniquely in a table. Rules For Defining the Primary Key Minimal Accessible NON NULL Value Time Invariant Unique Example: Table Employee A Primary key is selected from a set of candidate keys. This is done by database admin or database designer. We can say that either {Emp_SSN} or {Emp_Number} can be chosen as a primary key for the table Employee. Foreign key A foreign key is a column or a group of columns in a table that reference the primary key of another table. Example: The table that contains the foreign key is called the referencing table or child table. And the table referenced by the foreign key is called the referenced table or parent table. PART II. Functional Dependency. Types of the functional dependencies. Functional dependency in DBMS Functional Dependency (FD) is a constraint that determines the relation of one attribute to another attribute in a Database Management System (DBMS). A functional dependency is denoted by an arrow “→”. The functional dependency of X on Y is represented by X → Y. Example of the functional dependency In this example, if we know the value of Employee number, we can obtain Employee Name, city, salary, etc. By this, we can say that the city, Employee Name, and salary are functionally depended on Employee number: Employee Number → Employee Name Employee Number → Salary Employee Number → City Types of Functional Dependencies in DBMS Multivalued Dependency Trivial Functional Dependency Non-Trivial Functional Dependency Transitive Dependency Multivalued Dependency Multivalued Dependency (MVD) is a form of data dependency where two or more attributes, other than the key attribute, are functionally dependent on each other, but not on the key itself. Example This dependence can be represented like this: car_model -> maf_year car_model -> colour Trivial Functional Dependency The Trivial dependency is a set of attributes which are called a trivial if the set of attributes are included in that attribute. So, X -> Y is a trivial functional dependency if Y is a subset of X. Example {Emp_id, Emp_name} -> Emp_id is a trivial functional dependency as Emp_id is a subset of {Emp_id,Emp_name}. Non Trivial Functional Dependency In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant. i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional dependency. Example {Company} -> {CEO} (if we know the Company, we knows the CEO name) But CEO is not a subset of Company, and hence it’s non-trivial functional dependency. Transitive Dependency A Transitive dependency in a database is an indirect relationship between values in the same table that causes a functional dependency. Example {Company} -> {CEO} (if we know the compay, we know its CEO’s name) {CEO } -> {Age} If we know the CEO, we know the Age Therefore according to the rule of rule of transitive dependency: { Company} -> {Age} should hold, that makes sense because if we know the company name, we can know his age. Advantages of Functional Dependency Functional Dependency avoids data redundancy. Therefore same data do not repeat at multiple locations in that database It helps you to maintain the quality of data in the database It helps you to defined meanings and constraints of databases It helps you to identify bad designs It helps you to find the facts regarding the database design PART II. Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database. Normalization in DBMS Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomalies. Anomalies in DBMS There are three types of anomalies that occur when the database is not normalized. These are: - insertion, - update - and deletion anomaly. Example This table is not normalized. We will see the problems that we face when a table in database is not normalized. Normalization: Normal forms. First normal form(1NF) Second normal form(2NF) Third normal form(3NF) Boyce & Codd normal form (BCNF) First normal form (1NF) A relation is said to be in 1NF (first normal form), if it doesn’t contain any multi-valued attribute. In other words you can say that a relation is in 1NF if each attribute contains only atomic(single) value only. Example Let’s say a company wants to store the names and contact details of its employees. It creates a table in the database that looks like this: Solution Second normal form (2NF) A table is said to be in 2NF if both the following conditions hold: - Table is in 1NF (First normal form) - No non-prime attribute is dependent on the proper subset of any candidate key of table. Example Let’s say a school wants to store the data of teachers and the subjects they teach. They create a table Teacher that looks like this: Since a teacher can teach more than one subjects, the table can have multiple rows for a same teacher. Solution Teacher_Details table: Teacher_Subject table: Third normal form (3NF) A table design is said to be in 3NF if both the following conditions hold: - Table must be in 2NF - Transitive functional dependency of non-prime attribute on any super key should be removed. Example Let’s say a company wants to store the complete address of each employee, they create a table named Employee_Details that looks like this: Solution Employee Table: Employee_Zip table: Boyce Codd normal form (BCNF) It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table. Example Suppose there is a company wherein employees work in more than one department. They store the data like this: Solution Emp_Nationality Table: Emp_Dept_Mapping table Emp_Dept table: