Database Concepts — Study Notes

1. Introduction

A database is a systematically organised collection of data that can be stored, accessed, updated and managed efficiently. Unlike a simple file, a database is designed to handle large volumes of related data while ensuring consistency, integrity and security. Databases are used everywhere: banks maintain account records, hospitals store patient histories, schools keep student databases, and online shops manage product inventories. The software that manages a database is called a DBMS (Database Management System).

The relational model, proposed by Edgar F. Codd in 1970, organises data into tables of rows and columns connected through keys. A relational database is a collection of such related tables, and the language used to query it is SQL (Structured Query Language). Because nearly every modern application rests on a relational database, understanding its concepts is essential for a computer science student.

This chapter introduces the terminology of relational databases: tables, rows, columns, keys, and relationships. It explains the different types of databases and DBMSs, the advantages of using a DBMS over a file system, the constraints that keep data valid, and the concept of relational algebra as a foundation for queries. Later chapters build on these ideas to write actual SQL statements.

2. Database and DBMS

A database is a collection of interrelated data stored together with minimum redundancy. A DBMS is the software that defines, creates, maintains and controls access to the database. Popular DBMS examples include MySQL, Oracle, PostgreSQL, Microsoft SQL Server and SQLite. The DBMS provides an interface for users to query and manipulate data, and it enforces rules for data integrity and security.

The advantages of a DBMS over traditional file systems are numerous. Data redundancy is reduced because shared data is stored once. Data inconsistency is avoided because redundancy is the root cause of conflicting copies. The DBMS enforces integrity constraints, allows concurrent access by multiple users with proper locking, provides recovery from crashes through backup and rollback, and restricts access to authorised users for security. These properties make databases far more robust than ordinary files.

import sqlite3
connection = sqlite3.connect("school.db")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students (roll INTEGER, name TEXT, marks INTEGER)")
connection.commit()
connection.close()

The example uses Python's built-in SQLite library to create a real database with a student table, showing that database concepts map directly to working code.

3. Characteristics of Databases

A well-designed database has several important characteristics. It provides data independence, meaning the storage format can change without affecting application programs. It allows shared and concurrent access, so many users can work with the data simultaneously under control of the DBMS. It ensures data integrity by enforcing constraints, and it supports security and authorisation so that only permitted users can read or modify data. It also maintains data consistency, where each value agrees with the constraints and with related values in other tables.

Redundancy control is central to design. If the same address is stored in two tables, updating it in one place but not the other creates inconsistency. By normalising data into related tables and linking them with keys, the database eliminates such duplication.

4. Key Concepts: Tables, Rows and Columns

The relational model views data as a collection of tables (also called relations). Each table has a name and consists of rows (records or tuples) and columns (fields or attributes). Every row is a unique record of the table, and every column represents a specific attribute of the records.

Consider a STUDENT table with columns ROLL, NAME and MARKS. Each row holds one student's record. The set of valid values for a column is its domain. For example, the domain of MARKS might be integers from 0 to 100. A column's degree refers to the number of attributes (columns) in the table, and cardinality refers to the number of rows (records).

Here the degree is 3 (three columns) and the cardinality is 3 (three rows).

5. Keys in a Relational Database

Keys are the attributes or sets of attributes used to identify and relate records. The most important keys are:

• Primary Key: A column (or combination of columns) whose values uniquely identify each row. It cannot be NULL and cannot have duplicates. In the STUDENT table, ROLL is a natural primary key.
• Candidate Key: An attribute or set of attributes that could serve as a primary key because it has the unique and non-null properties. A table may have several candidate keys; one is chosen as the primary key.
• Alternate Key: The candidate keys that are not selected as the primary key.
• Foreign Key: A column (or set of columns) in one table that refers to the primary key of another table. It creates the relationship between tables and enforces referential integrity.
• Composite Key: A primary key made of two or more columns, used when no single column is unique.

import sqlite3
connection = sqlite3.connect("school.db")
cursor = connection.cursor()
cursor.execute("CREATE TABLE student (roll INTEGER PRIMARY KEY, name TEXT)")
cursor.execute("CREATE TABLE marks (roll INTEGER, subject TEXT, marks INTEGER, FOREIGN KEY (roll) REFERENCES student(roll))")
connection.commit()
connection.close()

In the example, roll is the primary key of the student table and a foreign key in the marks table, linking each mark record to its student.

6. Relationships Between Tables

Relational databases connect tables through relationships defined by foreign keys. Three types of relationships exist:

• One-to-One (1:1): Each record in table A relates to at most one record in table B, and vice versa. For example, one student has one locker.
• One-to-Many (1:M): Each record in table A can relate to many records in table B, but each record in B relates to one record in A. For example, one student (parent) has many mark records.
• Many-to-Many (M:N): Each record in A can relate to many in B, and each in B to many in A. For example, students and courses; this requires a junction table to implement.

The foreign key in the "many" side stores the primary key of the "one" side, forming the link that makes queries across tables possible.

7. Database Constraints

Constraints are rules enforced by the DBMS to keep data valid. NOT NULL ensures a column always has a value. UNIQUE ensures no two rows share the same value in a column. PRIMARY KEY combines NOT NULL and UNIQUE for the identifying column. CHECK restricts values to a valid range or list. DEFAULT supplies a value when none is given. FOREIGN KEY enforces referential integrity, ensuring that a referenced record exists in the parent table.

CREATE TABLE student (
  roll INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  marks INTEGER CHECK (marks BETWEEN 0 AND 100),
  grade TEXT DEFAULT 'F'
);

8. Relational Algebra

Relational algebra is a theoretical language of operations on relations that forms the mathematical foundation of SQL. The core operations are:

• Select (sigma): Picks rows satisfying a condition; a horizontal subset.
• Project (pi): Picks specific columns; a vertical subset.
• Union: Combines rows from two relations, removing duplicates.
• Cartesian Product: Combines every row of one relation with every row of another.
• Join: Combines related rows from two relations based on a matching condition, typically equality of keys.

Every SQL query can be expressed in relational algebra. For example, "list the names of students with marks above 80" is a select followed by a project. Understanding these operations clarifies how the database engine executes queries.

9. Types of DBMS

Besides relational databases, other database models exist. Hierarchical databases organise data as a tree, network databases use graphs, object-oriented databases store objects, and NoSQL databases handle unstructured and massive data with flexible schemas. Relational databases remain the most widely used for business applications because of their strong integrity guarantees and standardised SQL interface.

Quick Revision Tables

Table 1: Database Terminology

Table 2: Types of Keys

Mind Map

Important Diagrams (SVG)

Diagram 1: Primary and Foreign Key Relationship

Diagram 2: DBMS Layers

Common Mistakes

1. Calling every key a primary key: Not all unique columns are primary keys; the primary key is the chosen candidate key.
2. Allowing NULL in a primary key: A primary key can never be NULL and can never contain duplicates.
3. Confusing degree with cardinality: Degree is the number of columns; cardinality is the number of rows.
4. Treating a foreign key as a duplicate of the primary key: The foreign key references the parent's primary key but is not itself the primary key of its table.
5. Saying a database and a DBMS are the same: The database is the data; the DBMS is the software that manages it.
6. Storing redundant data: Repeating a value in many tables invites inconsistency when one copy is updated.
7. Implementing many-to-many without a junction table: Direct M:N storage causes duplicate rows; a linking table is required.
8. Forgetting constraints in schema design: Without PRIMARY KEY, NOT NULL and CHECK, invalid data enters the database silently.

Exam Tips

1. Define every key type in one line with an example; key questions are guaranteed one-mark questions.
2. Memorise the three relationship types and give a concrete example of each (student-locker, student-marks, student-course).
3. Know the difference between degree and cardinality and compute them from a sample table.
4. Relate SQL to relational algebra: select filters rows, project picks columns, join combines tables.
5. Mention the advantages of a DBMS (reduced redundancy, integrity, security, concurrent access) in long answers.
6. Practise writing CREATE TABLE with constraints in SQLite to link theory to code.
7. Remember NULL semantics: NULL means unknown; it is not equal to zero or empty string.

Conclusion

Databases provide the systematic foundation for storing and managing the structured data introduced in the previous chapter. The relational model organises data into tables, rows and columns, connected by keys that guarantee uniqueness and create relationships. A DBMS layers over the raw data to enforce integrity, security and concurrency, and constraints such as primary keys and check rules keep records valid. Relational algebra explains formally how queries select, project and join data, preparing the ground for SQL. With these concepts in place, the next chapter introduces Structured Query Language, the practical tool for creating, querying and modifying relational databases.