Alternate Key or Secondary Key is the key that has not been selected to be the primary key, but are candidate keys. However, it is considered a candidate key for the primary key.A candidate key not selected as a primary key is called alternate or secondary key. Candidate key is an attribute or set of attributes that you can consider as a Primary key.Let us see an example −Student_IDStudent_EnrollStudent_NameStudent_Email0962717Manishaaa@gmail.com0552655Mananabc@gmail.com0672699Shreyaspqr@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 the primary. Rest ... Read More

Each table has only a single primary key. Each relation may have one or more candidate key. One of these candidate key is called Primary Key. Each candidate key qualifies for Primary Key. Therefore candidates for Primary Key is called Candidate Key.Candidate key can be a single column or combination of more than one column. A minimal super key is called a candidate key.ExampleEmployeeID and EmployeeEmail, both can be a Primary key; therefore both are candidate keys. Select any of the as Primary Key for your table, since a table can have only a single Primary Key.Let us see another example ... Read More

A Surrogate Key’s only purpose is to be a unique identifier in a database, for example, incremental key, GUID, etc. It hasSurrogate Key has no actual meaning and is used to represent existence. It has an existence only for data analysis.ExampleKeyProductIDPrice505_921987200698_561256170304_571898250458_661666110Above, the surrogate key is Key in the table.Other ExamplesSome other examples of a Surrogate Key −Counter can also be shown as Surrogate Key.System date/time stampRandom alphanumeric string.

Structural IndependenceStructural independence exists when changes in the database structure do not affect DBMS ability to access data.Structural dependence exists when changes in the database structure do not affect DBMS ability to access data.Data IndependenceThe changes done in the lower level will not affect the upper layers. The two types are −Physical Data IndependenceLogical Data IndependenceLet us begin with Physical Data Independence −Physical Data IndependenceModify physical schema without affecting the schema or logical data. It is easier to achieve.It is achieved by the internal level of the database and mapping from the logical level to the internal level. The Conceptual Schema ... Read More

Super Key is an attribute (or a set of attributes) that uniquely identify a tuple i.e. an entity in entity set.It is a superset of Candidate Key, since Candidate Keys are selected from super key.ExampleLet us see an example −Student_IDStudent_EnrollStudent_NameStudent_EmailS024545Daveddd@gmail.comS344541Jackjjj@gmail.comS224555Markmmm@gmail.comThe following are the super keys for the above table −{Student_ID}{Student_Enroll}{Student_Email}{Student_ID, Student_Enroll}{Studet_ID, Student_Name}{Student_ID, Student_Email}{Student_Name, Student_Enroll}{Student_ID, Student_Enroll, Student_Name}{Student_ID, Student_Enroll, Student_Email}{Student_ID, Student_Enroll, Student_Name, Student_Email}The following would be the candidate key from the above −{Student_ID}{Student_Enroll}{Student_Email}Read More

What is 4NF?The 4NF comes after 1NF, 2NF, 3NF, and Boyce-Codd Normal Form. It was introduced by Ronald Fagin in 1977.To be in 4NF, a relation should be in Bouce-Codd Normal Form and may not contain more than one multi-valued attribute.ExampleLet us see an example −Movie_NameShooting_LocationListingMovieOneUKComedyMovieOneUKThrillerMovieTwoAustraliaActionMovieTwoAustraliaCrimeMovieThreeIndiaDramaThe above is not in 4NF, sinceMore than one movie can have the same listingMany shooting locations can have the same movieLet us convert the above table in 4NF −Movie_NameShooting_LocationMovieOneUKMovieOneUKMovieTwoAustraliaMovieTwoAustraliaMovieThreeIndiaMovie_NameListingMovieOneComedyMovieOneThrillerMovieTwoActionMovieTwoCrimeMovieThreeDramaNow the violation is removed and the tables are in 4NF.

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