Terminology in DBMS

Primary Keys

• A Primary key is a unique value in a column/field for a record in a table.
• It serves the data integrity and efficient data retrieval.
• Each value within the primary key column/field must be unique.
• No two/more records can have the same primary key value.
• Primary key columns cannot contain null values.
• The primary key column always contains not null values or must contain a unique value.
• In other words, the Primary keys concept enforces uniqueness by rejecting attempts to insert duplicate key values into the table’s column.
• Primary keys are often automatically indexed by the database system.
• Primary keys provide a reliable way to identify individual records within a table.
• Primary keys are often used to establish relationships between tables in a relational database via the foreign keys concept.
• Types of Primary Keys: Two types- 
  1. Single-Column Primary Key: A primary key is composed of a single column in the table having a unique column to identify a record.
  2. Composite Primary Key: A primary key that consists of two or more columns combined to uniquely identify a record. This is useful when no single column can uniquely identify a record.

Secondary Keys

• Secondary keys in a database system refer to indexes created on columns other than the primary key column(s) to improve query performance and facilitate efficient or speed-up data retrieval for commonly used queries.
• They are also known as non-clustered indexes in some database systems.
• They allow faster access to data by creating a separate index structure/columns that point to the actual data rows in the table.
• Unlike primary keys, secondary keys may contain duplicate values because they are not required to enforce uniqueness.
• Secondary keys are particularly beneficial for improving the performance of search operations, joins, and sorting tasks.
• Secondary keys can be created manually using SQL commands (CREATE INDEX) or through database management tools.
• They need to be maintained and periodically updated to reflect changes in data at regular intervals to ensure optimal performance.

Foreign Keys

• A foreign key is a column or a set of columns within a table that establishes a link or a relationship between two tables using the primary key of one table.
• It creates a logical association between rows in different tables based on the values of those columns.
• Foreign keys ensure referential integrity, i.e., values in the referencing column (or columns) [2nd or child table] must exist in the referenced column (usually the primary key) of the parent/first table, preventing orphaned or inconsistent data.
• Foreign keys can define different types of relationships, including one-to-one, one-to-many, or many-to-many relationships between tables.
• Database systems enforce foreign key constraints to maintain data consistency. They restrict operations that would violate referential integrity, such as inserting a value that doesn’t exist in the referenced table.
• Foreign keys play a vital role in relational databases by maintaining data consistency and integrity through relationships between tables, ensuring accuracy and reliability in complex data structures.

Records

• Records are organized forms of data within tables and are composed of individual data values associated with each field/column/attribute.
• Each row of a table represents a record in the database.
• Each record in a database table represents a specific entity or instance, such as a customer, product, employee, teacher, student, etc.
• Records are stored as rows within a database table, where each column represents a specific attribute of the entity.
• Records are the fundamental building blocks of databases, organizing and storing data in a structured manner.
• They represent real-world entities, and their effective management is critical for accurate data storage, retrieval, and analysis.
• Records in different tables can be related/linked through the foreign keys concept, establishing connections between entities.

Data Dictionary

Database Schema