DBMS Concepts

DBMS Tutorial Components of DBMS. Applications of DBMS The difference between file system and DBMS. Types of DBMS DBMS Architecture DBMS Schema Three Schema Architecture. DBMS Languages.

DBMS ER Model

ER model: Entity Relationship Diagram (ERD) Components of ER Model. DBMS Generalization, Specialization and Aggregation.

DBMS Relational Model

Codd’s rule of DBMS Relational DBMS concepts Relational Integrity Constraints DBMS keys Convert ER model into Relational model Difference between DBMS and RDBMS Relational Algebra DBMS Joins

DBMS Normalization

Functional Dependency Inference Rules Multivalued Dependency Normalization in DBMS: 1NF, 2NF, 3NF, BCNF and 4NF

DBMS Transaction

What is Transaction? States of transaction ACID Properties in DBMS Concurrent execution and its problems DBMS schedule DBMS Serializability Conflict Serializability View Serializability Deadlock in DBMS Concurrency control Protocols

Difference

Difference between DFD and ERD

Misc

Advantages of DBMS Disadvantages of DBMS Data Models in DBMS Relational Algebra in DBMS Cardinality in DBMS Entity in DBMS Attributes in DBMS Data Independence in DBMS Primary Key in DBMS Foreign Key in DBMS Candidate Key in DBMS Super Key in DBMS Aggregation in DBMS Hashing in DBMS Generalization in DBMS Specialization in DBMS View in DBMS File Organization in DBMS What Is A Cloud Database What Is A Database Levels Of Locking In DBMS What is RDBMS Fragmentation in Distributed DBMS What is Advanced Database Management System Data Abstraction in DBMS Checkpoint In DBMS B Tree in DBMS BCNF in DBMS Advantages of Threaded Binary Tree in DBMS Advantages of Database Management System in DBMS Enforcing Integrity Constraints in DBMS B-Tree Insertion in DBMS B+ Tree in DBMS Advantages of B-Tree in DBMS Types of Data Abstraction in DBMS Levels of Abstraction in DBMS 3- Tier Architecture in DBMS Anomalies in Database Management System Atomicity in Database Management System Characteristics of DBMS DBMS Examples Difference between Relational and Non-Relational Databases Domain Constraints in DBMS Entity and Entity set in DBMS ER Diagram for Banking System in DBMS ER Diagram for Company Database in DBMS ER Diagram for School Management System in DBMS ER Diagram for Student Management System in DBMS ER Diagram for University Database in DBMS ER Diagram of Company Database in DBMS Er Diagram Symbols and Notations in DBMS How to draw ER-Diagram in DBMS Integrity Constraints in DBMS Red-Black Tree Deletion in DBMS Red-Black Tree Properties in DBMS Red-Black Tree Visualization in DBMS Redundancy in Database Management System Secondary Key in DBMS Structure of DBMS 2-Tier Architecture in DBMS Advantages and Disadvantages of Binary Search Tree Closure of Functional Dependency in DBMS Consistency in Database Management System Durability in Database Management System ER Diagram for Bank Management System in DBMS ER Diagram for College Management System in DBMS ER Diagram for Hotel Management System in DBMS ER Diagram for Online Shopping ER Diagram for Railway Reservation System ER Diagram for Student Management System in DBMS Isolation in DBMS Lossless Join and Dependency Preserving Decomposition in DBMS Non-Key Attributes in DBMS Data Security Requirements in DBMS DBMS functions and Components What is Homogeneous Database? DBMS Functions and Components Advantages and Disadvantages of Distributed Database

DBMS Keys: Primary, Super, Candidate, Foreign

In database management system, keys play an important role which is used for identifying unique records by the combination of one or more fields in the database table. Keys also allows you to establish the relationship between the database tables.

Different types of keys

  1. Primary key
  2. Super key
  3. Candidate key
  4. Foreign key
  5. Alternate key or Secondary key
  6. Composite key

Primary key

A primary key is a field or a set of fields in a database table that uniquely identifies each record in that table. In a table, there can be more than one candidate key from which one of the key is selected as a primary key.

  • The value of the primary key field cannot be NULL.
  • A table is allowed to have only one primary key.
  • The value of a primary key field should always be unique.

Example:

In the Student table, Student_id can be a primary key since it is unique for each student. In the Student table, we can even select Aadhar_Number as a primary key as it is also unique.

Table: Student

Student_id Name Department_id Aadhar_Number

Primary key

Super Key

A super key is a set of single and multiple key attributes which is used to identify records in a table. The super key is a superset of the candidate key.
The set of all attributes or fields to identify the tuples in a relation is called the trivial super key.

Example:

Table Student:

Student_id Name Department_id


Following are the examples of a super key for the table Student:

1. Student_id
2. (Student_id, Name)
3. (Name, Department_id)

Candidate key

A minimal (minimum) set of attributes that can uniquely identify each record in a relation is called a candidate key. It is a subset of a super key.

  • The value of the candidate key field must be unique and always be not NULL for every tuple.
  • There can be more than one candidate key in a table or a relation.
  • Removing any field from the candidate key fails in identifying each record uniquely.

Example:

Table student:

Student_id Name Department_id

Following are the examples of candidate key for above table:

1. Student_id
2. (Student_id, Name)

Foreign key

A foreign key is anattribute in one table that acts as a primary key in another table. The foreign key is useful for establishing the relationship between two tables in a database.

Example:

In a college, every student study in a specific department, and department and student are two different entities. So you cannot store the information of the student in the department table. That's why we relate these two tables using the primary key of one table.

Example:
We create the primary key on the field department_idof the DEPARTMENT table.
Now in the student table, Department_id is the foreign key, and both the tables are linked.

Alternate key

Those keys which are not selected as the primary key from the candidate keys are called as the alternate keys. These keys are also known as the secondary keys.

Example:
Table student:

Student_id Name Department_id

The example of candidate key for above table:(Student_id, Name)

Composite key

A composite key is a key which is a combination of two or more fields (attributes) that uniquely identify each record in the table.

Example:

Table student:

Student_id Name Department_id


The examples of the composite key for above table: (Name, Department_id)