Introduction to Database

 

Database Design: 

1. Introduction to Databases 

Definition of a database

A database is an organized collection of structured information or data, typically stored electronically in a computer system.

Example: Consider a university's student management system. This database might contain:

• Student personal information
• Course details
• Enrollment records
• Grades

Students Table:
StudentID | Name      | DateOfBirth | Major
1001      | John Doe  | 1999-05-15  | Computer Science
1002      | Jane Smith| 2000-03-22  | Biology

Courses Table:
CourseID | CourseName          | Credits
CS101    | Intro to Programming| 3
BIO201   | Cell Biology        | 4

Enrollments Table:
EnrollmentID | StudentID | CourseID | Semester  | Grade
1            | 1001      | CS101    | Fall 2023 | A
2            | 1002      | BIO201   | Fall 2023 | B+

Importance of databases in modern computing

1. Data Organization: Databases structure information logically.
2. Data Retrieval: They allow quick and efficient access to specific information.
3. Data Integrity: Databases ensure accuracy and consistency of data.
4. Data Sharing: Multiple users can access the same data simultaneously.
5. Data Security: They provide mechanisms to protect sensitive information.

Example: An e-commerce platform like Amazon uses databases to:

• Track inventory across multiple warehouses
• Manage customer accounts and preferences
• Process orders and payments
• Provide personalized product recommendations

Brief history of database systems

1. 1960s: Hierarchical and network database models
   • Example: IBM's Information Management System (IMS)
2. 1970s: Relational database model introduced by Edgar Codd
   • Example: IBM's System R, the first SQL-based DBMS
3. 1980s: Commercial relational databases and SQL standardization
   • Example: Oracle Database, DB2
4. 1990s: Object-oriented databases emerge
   • Example: ObjectStore
5. 2000s: NoSQL and distributed databases gain popularity
   • Example: MongoDB, Apache Cassandra

2. Database Management Systems (DBMS) 

Definition of DBMS

A Database Management System (DBMS) is software that manages the storage, retrieval, and manipulation of data in a database.

Functions of a DBMS

1. Data storage, retrieval, and update
2. User interface creation
3. Security and authorization
4. Data integrity maintenance
5. Concurrent access control
6. Backup and recovery management

Example: Consider a banking system using a DBMS:

• It stores account information and transaction history
• Provides interfaces for tellers and online banking
• Ensures only authorized personnel can access sensitive data
• Maintains accurate balances even with simultaneous transactions
• Allows multiple branches to operate on the same data concurrently
• Regularly backs up data and can recover from system failures

Types of DBMS

Relational DBMS (RDBMS)

Organizes data into tables with rows and columns, using SQL for querying.

Example: Let's model a simple library system:

-- Books Table
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(50),
    ISBN VARCHAR(13)
);

-- Members Table
CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(100)
);

-- Loans Table
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    BookID INT,
    MemberID INT,
    LoanDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);

-- Sample query to find overdue books
SELECT b.Title, m.Name, l.LoanDate
FROM Loans l
JOIN Books b ON l.BookID = b.BookID
JOIN Members m ON l.MemberID = m.MemberID
WHERE l.ReturnDate < CURRENT_DATE;

Popular RDBMS: MySQL, PostgreSQL, Oracle, Microsoft SQL Server

Object-oriented DBMS

Stores data as objects, similar to object-oriented programming.

Example: Modeling a shape in an object-oriented database:

class Shape:
    def __init__(self, color):
        self.color = color

    def area(self):
        pass

class Circle(Shape):
    def __init__(self, color, radius):
        super().__init__(color)
        self.radius = radius

    def area(self):
        return 3.14 * self.radius ** 2

# In the database, you could store and retrieve Circle objects directly

NoSQL DBMS

Designed for flexible schemas and large-scale distributed data.

Example: Storing customer data in MongoDB (a document-based NoSQL database):

{
  "_id": ObjectId("5f8a7b2b9d3b2c1a3c4d5e6f"),
  "name": "John Doe",
  "email": "john@example.com",
  "orders": [
    {
      "orderId": "ORD001",
      "date": "2023-07-15",
      "items": [
        {"product": "Laptop", "price": 999.99},
        {"product": "Mouse", "price": 24.99}
      ]
    },
    {
      "orderId": "ORD002",
      "date": "2023-08-02",
      "items": [
        {"product": "Headphones", "price": 149.99}
      ]
    }
  ]
}

3. Other Systems to Store Data 

File systems

Basic method of storing data in files on a computer.

Example: Storing student records in a CSV file:

student_id,name,major,gpa
1001,John Doe,Computer Science,3.7
1002,Jane Smith,Biology,3.9
1003,Bob Johnson,Mathematics,3.5

Limitations: Difficult to query, no concurrent access control, data redundancy

Spreadsheets

Organize data in rows and columns with basic calculation capabilities.

Example: Managing a small inventory in Excel:

Product ID
Product Name
Quantity
Price
Total Value
P001
Widget A
100
$10.00
=C2*D2
P002
Gadget B
50
$15.00
=C3*D3
P003
Doohickey C
75
$5.00
=C4*D4

Limitations: Not suitable for large datasets or complex relationships

Key-value stores

Simple data storage using unique keys to access values.

Example: Using Redis to store session data:

SET session:user123 "{\"user_id\": 123, \"username\": \"jdoe\", \"last_login\": \"2023-07-30T14:30:00Z\"}"
GET session:user123

Use cases: Caching, session management, real-time analytics

Document stores

Store data in flexible, JSON-like documents.

Example: Storing blog posts in MongoDB:

{
  "_id": ObjectId("5f8a7b2b9d3b2c1a3c4d5e6f"),
  "title": "Introduction to Databases",
  "author": {
    "name": "Jane Doe",
    "email": "jane@example.com"
  },
  "content": "Databases are essential in modern computing...",
  "tags": ["database", "introduction", "technology"],
  "comments": [
    {
      "user": "Bob",
      "text": "Great article!",
      "date": "2023-07-31T10:15:00Z"
    }
  ]
}

Use cases: Content management systems, real-time big data applications

4. How DBMS Differs from Other Systems 

Data independence

DBMS separates the logical and physical aspects of data storage.

Example:

• Logical: Adding a new column to a customer table
• Physical: Changing the storage type or indexing method

This allows applications to remain unchanged when the underlying storage structure is modified.

Efficient data access

DBMS uses indexing and query optimization for fast data retrieval.

Example: Without index:

SELECT * FROM Customers WHERE LastName = 'Smith';
-- Might require scanning all records

With index on LastName:

CREATE INDEX idx_lastname ON Customers(LastName);
-- Now the same query is much faster

Data integrity and security

DBMS enforces constraints and provides access control.

Example:

-- Ensuring age is always positive
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT CHECK (Age > 0)
);

-- Setting up access control
GRANT SELECT ON Employees TO HR_Staff;
GRANT UPDATE (Name, Age) ON Employees TO HR_Managers;

Concurrent access control

DBMS manages multiple users accessing data simultaneously.

Example: Two bank tellers processing withdrawals from the same account:

1. Teller A reads balance: $1000
2. Teller B reads balance: $1000
3. Teller A processes $200 withdrawal, updates balance to $800
4. Teller B processes $300 withdrawal
   • Without DBMS: Might update to $700 (incorrect)
   • With DBMS: Will update to $500 (correct) using transaction management

Data redundancy reduction

DBMS uses normalization to minimize data duplication.

Example: Before normalization:

Orders Table:
OrderID | CustomerName | CustomerEmail | Product  | Price
1       | John Doe     | john@doe.com  | Laptop   | 999.99
2       | John Doe     | john@doe.com  | Mouse    | 24.99

After normalization:

Customers Table:
CustomerID | Name     | Email
1          | John Doe | john@doe.com

Orders Table:
OrderID | CustomerID | Product | Price
1       | 1          | Laptop  | 999.99
2       | 1          | Mouse   | 24.99

Backup and recovery mechanisms

DBMS provides tools for data protection and restoration.

Example:

• Regular backups: BACKUP DATABASE MyDB TO DISK = 'D:\Backups\MyDB.bak'
• Point-in-time recovery: Restore database to its state at 2023-07-30 14:30:00

5. Important Terminologies in Database Design 

Table (Relation)

A structured set of data elements in rows and columns.

Example:

Products Table:
ProductID | ProductName | Category | Price
1         | Laptop      | Electronics | 999.99
2         | T-shirt     | Clothing | 19.99
3         | Book        | Literature | 14.99

Record (Tuple)

A single row in a table representing a unique entity.

Example: In the Products table above, this is a record:

2 | T-shirt | Clothing | 19.99

Field (Attribute)

A column in a table representing a specific characteristic.

Example: In the Products table, 'ProductName' is a field.

Primary Key

A unique identifier for each record in a table.

Example: 'ProductID' in the Products table is the primary key.

Foreign Key

A field that refers to the primary key in another table.

Example:

Orders Table:
OrderID | CustomerID | OrderDate
1       | 101        | 2023-07-30
2       | 102        | 2023-07-31

Customers Table:
CustomerID | Name      | Email
101        | Jane Doe  | jane@example.com
102        | John Smith| john@example.com

Here, 'CustomerID' in the Orders table is a foreign key referencing the Customers table.

Schema

The overall structure of a database.

Example: An e-commerce database schema might include:

• Products table
• Customers table
• Orders table
• OrderItems table
• Categories table

Normalization

The process of organizing data to reduce redundancy and improve data integrity.

Example: First Normal Form (1NF): Eliminate repeating groups

Before 1NF:
CustomerID | Name | Phone1     | Phone2
1          | John | 1234567890 | 9876543210

After 1NF:
CustomerID | Name | Phone
1          | John | 1234567890
1          | John | 9876543210

Query

A request for data or information from a database.

Example:

SELECT ProductName, Price
FROM Products
WHERE Category = 'Electronics' AND Price < 500;

Transaction

A sequence of database operations that are treated as a single unit of work.

Example: Transferring money between bank accounts

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;

Index

A data structure that improves the speed of data retrieval operations.

Example:

CREATE INDEX idx_lastname ON Customers(LastName);

This would speed up queries that search or sort by LastName.

Recap:

1. Introduction to Databases

• A database is an organized collection of structured data.
• Databases are crucial for data organization, retrieval, integrity, sharing, and security.
• Historical evolution: hierarchical → relational → object-oriented → NoSQL databases.

2. Database Management Systems (DBMS)

• DBMS is software that manages storage, retrieval, and manipulation of data.
• Key functions: data storage/retrieval, user interface, security, integrity, concurrent access, backup/recovery.
• Types of DBMS:
  • Relational (RDBMS): Uses tables and SQL (e.g., MySQL, PostgreSQL)
  • Object-oriented: Stores data as objects (e.g., ObjectDB)
  • NoSQL: Flexible schemas for unstructured data (e.g., MongoDB, Cassandra)

3. Other Systems to Store Data

• File systems: Basic storage in files (e.g., CSV files)
• Spreadsheets: Organize data in rows and columns (e.g., Excel)
• Key-value stores: Simple key-based data access (e.g., Redis)
• Document stores: Store flexible, JSON-like documents (e.g., MongoDB)

4. How DBMS Differs from Other Systems

• Data independence: Separates logical and physical aspects of data storage
• Efficient data access: Uses indexing and query optimization
• Data integrity and security: Enforces constraints and access control
• Concurrent access control: Manages simultaneous data access
• Data redundancy reduction: Uses normalization techniques
• Backup and recovery: Provides data protection and restoration tools

5. Important Terminologies in Database Design

• Table (Relation): Structured set of data elements
• Record (Tuple): Single row in a table
• Field (Attribute): Column in a table
• Primary Key: Unique identifier for each record
• Foreign Key: Field referencing a primary key in another table
• Schema: Overall structure of a database
• Normalization: Process of organizing data to reduce redundancy
• Query: Request for data from a database
• Transaction: Sequence of operations treated as a single unit
• Index: Data structure to improve retrieval speed

Key Takeaways

1. Databases provide structured, efficient, and secure data management.
2. DBMS offers advanced features over simple file storage systems.
3. Different types of databases suit different data structures and use cases.
4. Understanding database terminologies is crucial for effective design.
5. Proper database design enhances data integrity, reduces redundancy, and improves performance.