Unit 1: Basics of Database

CAP570 — Advanced Database Techniques 9 min read

I. Orientation

A database is an organized collection of related data designed for efficient storage, retrieval, management, and sharing. Database systems emerged from file-processing limitations and became formalized through relational theory, especially E. F. Codd’s relational model (1970). A Database Management System (DBMS) provides the software environment that allows users and applications to define, manipulate, secure, and maintain data.

Data: Raw facts such as StudentID = 101, Name = "Asha", or Marks = 85.
Information: Processed data that has meaning, such as “Asha scored 85 marks.”
Database: A structured, persistent, and logically related collection of data.
DBMS: Software that manages databases and provides controlled access to them.
Core principle: Data should be stored once where possible, remain consistent, and be accessible to authorized users.
Common assumption: Multiple users and applications may access the same data concurrently.
Main objectives: Data independence, integrity, security, availability, reduced redundancy, and efficient processing.

II. Overview of Databases

A. Overview of Databases

An overview of databases explains how data is organized and managed as a shared resource rather than as isolated application files.

Structured storage: A relational database stores data in tables composed of rows and columns. In a Student table, one row may represent one student and StudentID may identify that row.
Data operations: The SQL commands SELECT, INSERT, UPDATE, and DELETE support retrieval and modification.
Metadata: A database stores descriptions of its own structure, including table names, column types, constraints, and indexes. This descriptive data is called the data dictionary or system catalog.
Controlled sharing: A university database can allow admissions, finance, and examination departments to use the same student record with different permissions.
Persistence: Data remains available after an application terminates because it is stored on secondary storage such as SSDs or hard disks.
Database transaction: A transaction is a logical unit of work, such as transferring money from Account A to Account B, that should be completed consistently.

B. Importance and Applications

Databases are important because organizations depend on accurate, available, and shared data for operations and decision-making.

Business: Retail systems store products, customers, orders, and payments. An order record may connect CustomerID, ProductID, quantity, and price.
Banking: Accounts, transactions, loans, and customer identity data require strong security and transaction consistency.
Healthcare: Patient histories, diagnoses, prescriptions, and laboratory results must be available while access is restricted to authorized personnel.
Education: Student enrollment, attendance, marks, and course information can be connected through keys such as StudentID and CourseID.
E-commerce: Shopping platforms use databases for inventory, carts, payment status, delivery tracking, and recommendations.
Decision support: Analytical databases aggregate historical sales, for example calculating monthly revenue with SUM(Amount) and grouping by month.
Importance of integrity: A foreign key can prevent an order from referring to a customer who does not exist.

III. Types of Databases

A. Types of Databases

Database types differ according to their data model, distribution, workload, and storage technology.

Relational databases: Store structured data in related tables. MySQL, PostgreSQL, Oracle, and Microsoft SQL Server are examples.
Hierarchical databases: Organize records in a tree with parent-child relationships. A department may contain employees, but each child generally has one parent.
Network databases: Represent many-to-many relationships through linked records, allowing a record to have multiple parent records.
Object-oriented databases: Store objects containing both state and behavior, making them suitable for applications using complex objects such as engineering designs.
Document databases: Store semi-structured documents, commonly JSON. A customer document may contain an embedded address and order list.
Key-value databases: Store values using unique keys, such as session_7821 -> user data; they are common for caching.
Column-family databases: Store data by column groups and support large-scale distributed workloads.
Graph databases: Represent entities as nodes and relationships as edges. A social network can model Person -follows-> Person.
Centralized databases: Data is maintained at one primary location.
Distributed databases: Data is stored across multiple networked sites, improving locality or availability but increasing coordination complexity.
Operational versus analytical: Online Transaction Processing handles frequent small updates, while Online Analytical Processing handles large queries over historical data.

IV. Database Architecture

A. Database Architecture

Database architecture describes how users, applications, DBMS services, and stored data are arranged.

One-tier architecture: The user interface, application logic, and database operate in one environment, as in a local desktop database.
Two-tier architecture: A client application communicates directly with a database server. The client may send SQL queries, and the server returns result sets.
Three-tier architecture: A presentation layer communicates with an application server, which communicates with the database server. Web systems commonly use browser, application server, and database server layers.
Presentation layer: Displays forms, reports, and web pages to the user.
Application layer: Applies business rules, such as rejecting an order when stock quantity is less than requested quantity.
Database layer: Handles storage, query execution, transactions, authorization, and recovery.
Advantages of three tiers: It centralizes business logic, improves security, supports scalability, and prevents direct database exposure to every client.
Client-server principle: Clients request services, while the database server manages shared data and processing.

V. Database Components

A. Database Components

A database environment contains interacting hardware, software, data, procedures, and people.

Hardware: Servers, storage devices, network equipment, and client computers provide the physical execution environment.
Software: The DBMS, operating system, network software, utilities, and application programs work together.
Data: User data includes business records; metadata describes structures, constraints, indexes, and relationships.
Database engine: The engine parses queries, optimizes execution plans, reads pages from storage, and returns results.
Query processor: It converts SQL into an execution plan. For example, it may choose an index rather than scanning every row.
Storage manager: It controls files, indexes, buffers, and disk pages.
Transaction manager: It coordinates concurrent operations and supports atomicity, consistency, isolation, and durability.
Recovery manager: It uses logs and backups to restore a valid state after failure.
Security manager: It authenticates users and enforces privileges such as SELECT or UPDATE.
People: Database administrators, database designers, developers, analysts, and end users have different responsibilities.
Procedures: Backup schedules, naming standards, access policies, and recovery procedures guide correct operation.

VI. Introduction to Database Schemas

A. Introduction to Database Schemas

A database schema is the formal blueprint that defines the structure, relationships, constraints, and permitted data types of a database.

Schema definition: A schema may specify Student(StudentID INT PRIMARY KEY, Name VARCHAR(50)).
Instance: The schema is relatively stable, while an instance is the actual data stored at a particular time.
Relation structure: In a relational schema, a table name is followed by attributes and their domains, such as Course(CourseID, Title, Credits).
Primary key: A primary key uniquely identifies each record; StudentID cannot be duplicated or null.
Foreign key: A foreign key references a key in another table, such as Enrollment.StudentID referencing Student.StudentID.
Integrity constraints: Domain constraints restrict values, entity integrity protects primary keys, and referential integrity protects relationships.
External schema: Defines the portion of data visible to a particular user or application.
Conceptual schema: Describes the complete logical structure of the organization.
Internal schema: Describes physical storage, including files, indexes, and access paths.
Schema versus instance: Adding a new student changes the instance; adding a DateOfBirth column changes the schema.

VII. Types of Data Models

A. Types of Data Models

A data model provides concepts for describing data, relationships, constraints, and operations.

Hierarchical model: Uses a tree structure. It is efficient for one-to-many paths but less flexible for many-to-many relationships.
Network model: Uses record types and set types to represent complex links, including one record participating in several relationships.
Relational model: Represents data as relations, with rows called tuples and columns called attributes. SQL is its dominant query language.
Entity-Relationship model: Uses entities, attributes, and relationships during conceptual design. Student and Course may be connected by Enrolls.
Object-oriented model: Represents objects, classes, inheritance, and encapsulation, such as a Vehicle superclass with Car and Truck subclasses.
Document model: Represents flexible records, useful when individual documents have different attributes.
Graph model: Focuses on connected data. A route-planning graph can represent cities as nodes and roads as weighted edges.
Model selection criterion: Choose according to structure, query patterns, transaction requirements, scalability, and consistency needs.

VIII. Conceptual, Logical, and Physical Database Design

A. Conceptual, Logical, and Physical Database Design

Database design transforms organizational requirements into an implementable database through three increasingly detailed levels.

Conceptual design: Identifies major entities, attributes, and relationships without choosing a DBMS. A university model may identify Student, Course, and Enrollment.
Logical design: Converts the conceptual model into a specific logical model, usually relational tables, keys, and constraints.
Physical design: Determines how the DBMS stores and accesses data using indexes, partitions, file organization, and storage parameters.
Design sequence: Requirements analysis leads to an ER model, which becomes relational schemas and then physical database structures.
Separation of concerns: Conceptual design focuses on meaning, logical design on structure, and physical design on performance and storage.
Normalization: Logical design reduces redundancy and update anomalies. A student-course table may be decomposed into Student, Course, and Enrollment.
Denormalization: Carefully controlled duplication may improve read performance, but it increases maintenance and consistency requirements.
Index design: An index on StudentID can accelerate searches but adds storage cost and slows inserts or updates.
Physical trade-off: Partitioning a large Sales table by year can reduce the data scanned by a query for SalesYear = 2025.
Design quality: A sound design preserves data integrity, supports required queries, minimizes unnecessary redundancy, and remains adaptable to changing requirements.
Data independence: Logical data independence allows changes to the logical schema without rewriting user views; physical data independence allows storage changes without changing logical tables.
Worked example: A many-to-many relationship between students and courses requires an associative table:

SQL

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    EnrollDate DATE,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Here, StudentID and CourseID identify the participating records, while their combined primary key prevents duplicate enrollment in the same course.