Chapter 9: Database Concepts & Relational Data Model

In the modern world, immense amounts of data are generated every second. Managing this data efficiently is crucial for any organization. This is where Databases come in.

9.1 Introduction to Database Concepts

A Database is an organized collection of structured data, typically stored electronically in a computer system. The software used to manage this database is called a Database Management System (DBMS) (e.g., MySQL, Oracle, PostgreSQL).

Why do we need Databases?

Before databases, data was often stored in traditional file systems (like text files or Excel sheets). This led to numerous problems:

1. Data Redundancy: Duplication of the same data across multiple files wastes storage space.
2. Data Inconsistency: If duplicate data exists, updating it in one place but forgetting another leads to mismatched, inconsistent data.
3. Data Isolation: It’s difficult to retrieve and combine data spread across multiple disjointed files.
4. Security Problems: File systems offer very limited user access controls.

A DBMS solves these problems by providing centralized control, reducing redundancy, ensuring consistency, and offering robust security mechanisms.

9.2 The Relational Data Model

There are different ways to structure a database. The most popular and widely used model is the Relational Data Model, proposed by E.F. Codd in 1970.

In the relational model, data is organized into two-dimensional tables called Relations.

Terminology Mapping

• Relation: A Table. It stores data about a specific entity (e.g., a “Student” table, an “Employee” table).
• Tuple: A Row (or Record) in a table. It represents a single, complete set of related data about one specific instance of the entity.
• Attribute: A Column (or Field) in a table. It represents a specific property or characteristic of the entity.
• Domain: The set of all possible permissible values for a specific attribute (e.g., the domain for “Gender” might be just ‘M’ or ‘F’).
• Degree: The total number of attributes (columns) in a relation.
• Cardinality: The total number of tuples (rows) in a relation.

Example Table: STUDENT

• Degree of STUDENT: 4 (AdmNo, Name, Age, Stream)
• Cardinality of STUDENT: 3 (Number of rows of data)

9.3 Keys in a Relational Database

Keys are attributes (or sets of attributes) that uniquely identify a row within a table or establish relationships between tables.

1. Candidate Key: An attribute, or combination of attributes, that can uniquely identify a tuple in a relation. A table can have multiple Candidate Keys. (e.g., AdmNo, RollNo, AadharCardNumber).
2. Primary Key: The specific Candidate Key chosen by the database designer to uniquely identify tuples in the relation. There can be only one Primary Key per table. Crucially, a Primary Key cannot contain NULL values. (e.g., we choose AdmNo as the Primary Key).
3. Alternate Key(s): The Candidate Keys that were not chosen as the Primary Key. (If AdmNo is the Primary Key, then RollNo and AadharCardNumber become Alternate Keys).
4. Foreign Key: A non-key attribute in one table whose values are drawn from the Primary Key of another table. It is used to establish and enforce a link (relationship) between the two tables.

Understanding these concepts is vital before we start writing queries to manipulate the database using SQL.