What is a Database?

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. Databases allow for data to be easily accessed, managed, modified, updated, and deleted. They serve as the backbone of various applications—everything from social media platforms to e-commerce websites and financial systems rely on databases to store user profiles, products, transactions, and more.

Key Characteristics of Databases

1. Persistent Storage: Data is stored over the long term, surviving application restarts and system reboots.
2. Structured and Organized: Data is systematically arranged to avoid duplication and inconsistency.
3. Easily Retrievable: Efficient methods exist for querying, filtering, and retrieving stored data quickly.
4. Concurrent Access: Multiple users and applications can use the database simultaneously without corrupting data.
5. Security and Integrity: Access can be controlled and data can be protected against unauthorized use or corruption.

Why Use a Database?

• To maintain a permanent record of information.
• To ensure data integrity and reduce redundancy.
• To efficiently handle large volumes of data and ensure fast retrieval.
• To allow multiple users and applications to access and work with the data safely and concurrently.
• To back up and recover data in case of hardware failures or data corruption.

Database Management Systems (DBMS)

It is software that manages databases, handling data storage, retrieval, updates, and security. It acts as an interface between databases and users/applications

Examples of DBMS:

• Relational DBMS (RDBMS): MySQL, PostgreSQL, Oracle, SQL Server
• NoSQL DBMS: MongoDB, Cassandra, DynamoDB
• In-memory DBMS: Redis, Memcached

In this course we will focus on MySQL which is a popular open-source RDBMS

Introduction to the Relational Data Model

The relational data model organizes data into one or more tables (also known as relations) with rows and columns. The idea, introduced by E.F. Codd in 1970, revolutionized how databases are structured and queried.

Key Concepts in the Relational Model

• Tables (Relations): A table represents an entity or a concept. For example, Employees, Customers, Products. Each table consists of rows and columns.
• Columns (Attributes): Columns define the type of data stored. For example, in an Employees table, you might have columns like employee_id, first_name, last_name, hire_date. All rows in the same column share the same type and meaning of data.
• Rows (Records): Each row in a table represents a single instance or record. For example, one row in the Employees table would represent one specific employee.
• Keys: Primary Key & Foreign Key
• Relationships Between Tables: 1:1, 1:M & M:N

Primary Key

A column or set of columns that uniquely identify each row in a table. For instance, employee_id can be a primary key if it uniquely identifies every employee.

Foreign Key

A column in one table that refers to the primary key in another table. For example, a department_id in the Employees table that references the department_id in a Departments table.

One-to-One (1:1)

Each row in Table A is related to exactly one row in Table B. For example, one employee might have one unique company car assigned.

One-to-Many (1:N)

One row in Table A can be associated with multiple rows in Table B. For example, one department can have many employees.

Many-to-Many (M:N)

Multiple rows in Table A can be associated with multiple rows in Table B. For example, employees can work on multiple projects, and projects can have multiple employees.

Why the Relational Model?

• Data Integrity: By using primary and foreign keys, the relational model enforces referential integrity. This means no orphaned records should exist (e.g., an employee record that refers to a department that doesn’t exist).
• Reduced Redundancy: Through a process called normalization, relational databases are designed to minimize duplication and maintain consistent data.
• Flexibility in Querying: Structured Query Language (SQL) provides a powerful, declarative way to retrieve and manipulate data in complex ways without changing the database design.

Numeric Data Type

In case of DECIMAL, range of p is [1, 65] & that of s is [0, 30].