For Entity Integrity Rule, each table has a Primary Key.Primary Key cannot have NULL value.Student_IDStudent_AwardsStudent_AwardsAbove, you can see our primary key is Student_ID. We cannot consider Student_Awards as the primary key since not every student would have received the award.Let us see another example −Employee_IDEmployee_NameEmployee_AgeEmployee_LocationIn the above table, the Primary Key is Employee_IDLet us now summarize the Entity Integrity Rule −Make sure that each tuple in a table is unique.Every table mush has a primary key, for example, Student_ID for a Student table.Every entity is unique.The relations Primary Key must have unique values for each row.Primary Key cannot have NULL ... Read More

What is a Secondary KeySecondary Key is the key that has not been selected to be the primary key. However, it is considered a candidate key for the primary key.Therefore, a candidate key not selected as a primary key is called secondary key. Candidate key is an attribute or set of attributes that you can consider as a Primary key.Note: Secondary Key is not a Foreign Key.ExampleLet us see an example −Student_IDStudent_EnrollStudent_NameStudent_AgeStudent_Email0969122717Manish25aaa@gmail.com0559122655Manan23abc@gmail.com0679122699Shreyas28pqr@gmail.comAbove, Student_ID, Student_Enroll and Student_Email are the candidate keys. They are considered candidate keys since they can uniquely identify the student record. Select any one of the candidate key as ... Read More

A relation is in DKNF when insertion or delete anomalies are not present in the database. Domain-Key Normal Form is the highest form of Normalization. The reason is that the insertion and updation anomalies are removed. The constraints are verified by the domain and key constraints.A table is in Domain-Key normal form only if it is in 4NF, 3NF and other normal forms. It is based on constraints −Domain ConstraintValues of an attribute had some set of values, for example, EmployeeID should be four digits long −EmpIDEmpNameEmpAge0921Tom330922Jack31Key ConstraintAn attribute or its combination is a candidate keyGeneral ConstraintPredicate on the set ... Read More

What is Transitive DependencyWhen an indirect relationship causes functional dependency it is called Transitive Dependency.If  P -> Q and Q -> R is true, then P-> R is a transitive dependency.To achieve 3NF, eliminate the Transitive Dependency.ExampleMovie_IDListing_IDListing_TypeDVD_Price ($)M08L09Crime180M03L05Drama250M05L09Crime180The above table is not in 3NF because it has a transitive functional dependency −Movie_ID -> Listing_IDListing_ID -> Listing_TypeTherefore, the following has transitive functional dependency.Movie_ID -> Listing_TypeThe above states the relation violates the 3rd Normal Form (3NF).To remove the violation, you need to split the tables and remove the transitive functional dependency.Movie_IDListing_IDDVD_Price ($)M08L09180M03L05250M05L09180Listing_IDListing_TypeL09CrimeL05DramaL09CrimeNow the above relation is in Third Normal Form (3NF) ... Read More

What is Functional DependencyFunctional dependency in DBMS, as the name suggests is a relationship between attributes of a table dependent on each other. Introduced by E. F. Codd, it helps in preventing data redundancy and gets to know about bad designs.To understand the concept thoroughly, let us consider P is a relation with attributes A and B. Functional Dependency is represented by -> (arrow sign)Then the following will represent the functional dependency between attributes with an arrow sign −A -> BAbove suggests the following:ExampleThe following is an example that would make it easier to understand functional dependency −We have a ... Read More

The 5NF (Fifth Normal Form) is also known as project-join normal form. A relation is in Fifth Normal Form (5NF), if it is in 4NF, and won’t have lossless decomposition into smaller tables.You can also consider that a relation is in 5NF, if the candidate key implies every join dependency in it.ExampleThe below relation violates the Fifth Normal Form (5NF) of Normalization −EmpNameEmpSkillsEmpJob (Assigned Work)DavidJavaE145JohnJavaScriptE146JamiejQueryE146EmmaJavaE147The above relation can be decomposed into the following three tables; therefore, it is not in 5NF −EmpNameEmpSkillsDavidJavaJohnJavaScriptJamiejQueryEmmaJavaThe following is the relation that displays the jobs assigned to each employee −EmpNameEmpJobDavidE145JohnE146JamieE146EmmaE147Here is the skills that are ... Read More

A primary key having two or more attributes is called composite key. It is a combination of two or more columns.An example can be −Here our composite key is OrderID and ProductID −{OrderID, ProductID}Let us see another example −StudentIDStudentEnrollNoStudentMarksStudentPercentageS001072172257090S002072179049080S003072176644086Above, our composite keys are StudentID and StudentEnrollNo. The table has two attributes as primary key.Therefore, the Primary Key consisting of two or more attribute is called Composite Key.

Normalization removes data redundancy and update, insert and delete anomalies and gives you a normalized perfect database design that a database administrator love.To normalize a database table, follow the below given steps that highlights the role of normalization forms and its uses −First Normal Form (1NF)1 INF is useful in removing the data redundancy issue and anomalies of a database. All attributes in 1NF should have atomic domains.Second Normal Form (2NF)The Second Normal Form eliminates partial dependencies on primary keys.Third Normal Form (3NF)The Third Normal Form eliminates Transitive Functional Dependency.Fourth Normal Form (4NF)To be in 4NF, a relation should may ... Read More

What is Partial Dependency?Partial Dependency occurs when a non-prime attribute is functionally dependent on part of a candidate key.The 2nd Normal Form (2NF) eliminates the Partial Dependency.Let us see an example −ExampleStudentIDProjectNoStudentNameProjectNameS01199KatieGeo LocationS02120OllieCluster ExplorationIn the above table, we have partial dependency; let us see how −The prime key attributes are StudentID and ProjectNo, andStudentID =  Unique ID of the studentStudentName = Name of the studentProjectNo = Unique ID of the projectProjectName = Name of the projectAs stated, the non-prime attributes i.e. StudentName and ProjectName should be functionally dependent on part of a candidate key, to be Partial Dependent.The StudentName can be determined by StudentID, which makes the relation ... Read More

Referential Integrity Rule in DBMS is based on Primary key and Foreign Key. The Rule defines that a foreign key have a matching primary key. Reference from a table to another table should be valid.Referential Integrity Rule example −EMP_IDEMP_NAMEDEPT_IDDEPT_IDDEPT_NAMEDEPT_ZONEThe rule states that the DEPT_ID in the Employee table has a matching valid DEPT_ID in the Department table.To allow join, the referential integrity rule states that the Primary Key and Foreign Key have same data types.