The fundamental architecture of modern SQL databases. Discover how data is structured in relations (tables), what defines a valid row and column, and how different keys ensure data integrity across your entire database.
The Relational Model (RM) organises data in the form of relations (which we commonly refer to as tables).
Common RDBMS Systems: Oracle, IBM DB2, MySQL, PostgreSQL, Microsoft SQL Server, MS Access.
In the relational model, tables must adhere to a strict set of rules to be considered valid relations:
Keys are arguably the most important concept in relational databases. They are mechanisms used to uniquely identify rows and to establish relationships between tables.
Any Permutation & Combination (P&C) of attributes present in a table which can uniquely identify each tuple. It can have redundant/unnecessary attributes.
Example: {Employee_ID}, or {Employee_ID, Name}, or {Employee_ID, Name, Dept}
The minimum subset of super keys which can uniquely identify each tuple. It contains no redundant attributes. A Candidate Key value shouldn't be NULL.
Example: {Employee_ID} and {Email} can both be CKs.
However, {Employee_ID, Name} is NOT a CK because Name is redundant.
The one specific Candidate Key chosen by the database designer to act as the principal means of identifying rows. It is selected out of the CK set, usually preferring the one with the least number of attributes.
Example: Out of CKs {Employee_ID} and {Email}, we choose {Employee_ID} as the Primary Key.
All the Candidate Keys that were NOT chosen to be the Primary Key. In mathematical terms: AK = All CKs except PK.
Example: If {Employee_ID} is the PK, then {Email} becomes the Alternate Key.
A Foreign Key is an attribute in one relation (table) that refers to the Primary Key of another relation. It is what creates the relationships between two tables, enabling the cross-referencing of data.
r1 may include among its attributes the PK of an another relation r2. This attribute is called the FK in r1 referencing r2.r1 (which holds the FK) is the Referencing (Child) relation.r2 (which holds the original PK) is the Referenced (Parent) relation.The Dept_ID inside the Employee table acts as a pointer to the Dept_ID primary key in the Department table.
A Primary Key that is formed using at least 2 attributes. Used when a single column is not enough to guarantee uniqueness.
Ex: {Class_Grade, Roll_Number} together make a composite PK for a student table.
A type of Composite Key, but specifically, a Primary Key which is formed using two or more Foreign Keys.
Ex: A Many-To-Many relationship table `Enrollments` where PK is {Student_ID (FK), Course_ID (FK)}.
A completely synthetic / artificial Primary Key. It has no meaning or relation to the actual row data. It is usually an integer value generated automatically by the Database (like an auto-incrementing ID).
Ex: An auto-generated sequential `id` column that increments 1, 2, 3... just to provide an easy unique identifier.
CRUD (Create, Read, Update, Delete) operations must be done with an integrity policy so that the database is always consistent. Constraints are introduced so we do not accidentally corrupt the DB with bad data.
Restricts the value in the attribute, specifying its valid Domain (Data Type and rules).
Every relation should have a Primary Key, and the Primary Key CANNOT be NULL.
If a PK was null, we couldn't reliably identify that row. Therefore, PK != NULL is the cardinal rule of entity constraints.
Specified between two relations. It helps maintain consistency among tuples of two relations (Parent-Child relationship).
Restricts the user from inserting a NULL value. Ensures that every element in the database for that column has a value.
Ensures that all values present in a specific column are completely different from each other. No duplicates allowed.
Used to provide a default value for a column. This value is added automatically if no value is explicitly provided during row insertion.
Keeps the check that data integrity is maintained before and after CRUD completion. (Testing a condition, e.g., Price > 0).
An attribute (or set of attributes) that uniquely idenfity each entity. By definition, a PK must contain UNIQUE values and must NOT NULL.
Creates a relationship between two entities. Represents a common attribute. This key prevents actions which could result in a loss of connection (orphaned records) between tables.
Because both the ER-Model and the Relational Model are abstract logical representations of real-world enterprises built on similar design principles, we can convert ER designs directly into Relational (table) designs. This is how we arrive at a valid database schema from an ER diagram.
// ER Notation: Student(RollNo, Name)
Table: Student(RollNo, Name)
// Strong: Loan, Weak: Payment
Table: Payment(Loan_No (FK), Instalment_No, Date)
Represented as columns directly in the tables/relations.
Handled by creating a separate column for each composite sub-attribute. The original parent attribute is completely ignored.
// Address has {street-name, house-no}
Customer(Cust_ID, address-street-name, address-house-no)
New tables are created for each multivalued attribute (named as the original attribute). The PK of the entity is used as a column (FK) in the new table. The PK of this new table is composite: {FK + multivalued name}.
// Employee has multivalued dependent-name
New Table: Dependent-Name(emp-id (FK), dname)
Not considered in the relational tables at all (computed dynamically at query time).
Create table for higher-level entity, and tables for lower-level entities containing the PK of higher-level + their specific attributes.
Account(acc_no, balance)
Savings(acc_no, interest_rate)
Current(acc_no, overdraft)
Create tables ONLY for lower-level entities containing all attributes of higher-level entities.
Savings(acc_no, balance, int_rate)
Current(acc_no, balance, overdraft)
Drawback: Data duplication for overlapping entities (balance is stored twice), and cannot store generic accounts that are neither Savings nor Current.
When a relationship set is treated as an abstract entity to participate in another relationship.
// Example where Evaluation relates to the (Student-Project) aggregation
Table: Evaluation(Student_ID (FK), Project_ID (FK), Evaluator_ID (FK), Grade)