Database terms

Relation:

• A relational database consists of a collection of tables, each of which is assigned a unique name.
• In the relational model, the term relation is used to refer to a table.

Entity:

• Entity means any real-world object.
• It may be a person, things, concept or theory for which we are storing the data.
• For example Employee record.

Attribute:

• Each entity has specific characteristics that are defined by an attribute.
• Similarly, the term attribute refers to a column of a table.

Attribute Value:

• For each attribute of a relationship, there is a set of permitted values call the domain of the attribute.

Tuple: 

• A tuple is simply a sequence (or list) of values. The term tuple is used to refer to a row in the relational model.

Null:

• The null value is a special value that signifies that the value is unknown or does not exist.

Key: 

• A key is an attribute or set of an attribute which helps you to identify a row (tuple) in a relation (table). 
• They allow you to find the relation between two tables.
• Keys help you uniquely identify a row in a table by a combination of one or more columns in that table.
• Example: 
• Employee ID is a primary key because it uniquely identifies an employee record.
• In this table, no other employee can have the same employee ID.
• Why we need a Key? 
• Keys help you to identify any row of data in a table. In a real-world application, a table could contain thousands of records. 
• Moreover, the records could be duplicated. 
• Keys ensure that you can uniquely identify a table record despite these challenges. 
• Allows you to establish a relationship between and identify the relation between tables 
• Help you to enforce identity and integrity in the relationship. 

Various Keys in Database Management System

Primary key: 

• A primary key is made of one or more columns in a table.
• It must not be null and unique across the column.
• It is a column or group of columns in a table that uniquely identify every row in that table. 
• The Primary Key can't be a duplicate meaning the same value can't appear more than once in the table. 
• A table cannot have more than one primary key. 
• Rules for defining Primary key: 
• Two rows can't have the same primary key value. 
• It must for every row to have a primary key value. 
• The primary key field cannot be null. 
• The value in a primary key column can never be modified or updated if any foreign key refers to that primary key. 

Super Key: 

• A superkey is a set of one or more attributes that, taken collectively, allow us to identify uniquely a tuple in the relation.
• A superkey is a group of single or multiple keys that identify rows in a table. 
• A Super key may have additional attributes that are not needed for unique identification. 
• Example:
• EmpSSN and EmpNum name are superkeys.

Candidate key: 

• Minimal superkey are called candidate keys.
• It is a set of attributes that uniquely identify tuples in a table. 
• Candidate Key is a super key with no repeated attributes. 
• The Primary key should be selected from the candidate keys. 
• Every table must have at least a single candidate key. 
• Example:
• Stud ID, Roll No, and email are candidate keys which help us to uniquely identify the student record in the table.
• Properties of Candidate key: 
• It must contain unique values 
• Candidate key may have multiple attributes 
• Must not contain null values 
• It should contain minimum fields to ensure uniqueness 
• Uniquely identify each record in a table 

Note: A table can have multiple candidate keys but only a single primary key. 

Alternate key: 

• An alternate key is a key associated with one or more columns whose values uniquely identify every row in the table, but which is not the primary key.
• It is a column or group of columns in a table that uniquely identify every row in that table. 
• A table can have multiple choices for a primary key but only one can be set as the primary key. 
• All the keys which are not primary key are called an Alternate Key. 
• Example:
• StudID, Roll No, Email are qualified to become a primary key. 
• But since StudID is the primary key, Roll No, Email becomes the alternative key.

Foreign Key:

• It is a column that creates a relationship between two tables.
• The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity.
  
• Example:
• The foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables.
• It acts as a cross-reference between two tables as it references the primary key of another table.
• This concept is also known as Referential Integrity.

Compound key:

• It has two or more attributes that allow you to uniquely recognize a specific record.
• It is possible that each column may not be unique by itself within the database.
  
• Example:
• OrderNo and ProductID can't be a primary key as it does not uniquely identify a record. 
• However, a compound key of Order ID and Product ID could be used as it uniquely identified each record.
• However, when combined with the other column or columns the combination of composite keys become unique.
• The purpose of compound key is to uniquely identify each record in the table.

Composite key

• It is a combination of two or more columns that uniquely identify rows in a table.
• The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed.
• Hence, they are combined to uniquely identify records in a table.
• The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key.

Surrogate Key

• An artificial key which aims to uniquely identify each record is called a surrogate key.
• These kind of key are unique because they are created when you don't have any natural primary key.
• They do not lend any meaning to the data in the table. 
• A surrogate key is usually an integer.
• Helps you to uniquely identify a record in the table.

Primary Key vs. Foreign Key

• Primary Key never accept null values.
• A foreign key may accept multiple null values.

• Primary key is a clustered index and data in the DBMS table are physically organized in the sequence of the clustered index.
• A foreign key cannot automatically create an index, clustered or non-clustered. However, you can manually create an index on the foreign key.

• You can have the single Primary key in a table.
• You can have multiple foreign keys in a table.