Introduction to Database Systems

Welcome to Unit 1: Introduction to Database Systems. This unit provides the foundational concepts required to understand what databases are, why they are essential in modern computing, and the architecture that makes them function effectively.

1.1 Introduction

In the modern digital age, organizations and individuals generate and consume vast amounts of data daily. Managing this data efficiently, securely, and reliably is crucial. Database Systems were developed to overcome the limitations of traditional file management systems and provide a robust framework for data management.

1.1.1 Data and Information

Data refers to raw, unorganized facts, figures, and details that need to be processed. Data can be something simple and seemingly random and useless until it is organized. Examples of data include numbers, characters, symbols, images, etc., which can be processed by a computer. Example: John, 25, New York, 1001 are discrete pieces of data.

Information, on the other hand, is data that has been processed, organized, structured, or presented in a given context so as to make it useful. When data is processed, organized, formatted, or structured, it gains meaning and becomes information. Example: "John is a 25-year-old resident of New York with the employee ID 1001." This sentence processes the raw data into meaningful information.

1.1.2 Database and Database Management System

A Database is a systematic collection of data. Databases support electronic storage and manipulation of data. Databases make data management easy. They are organized to facilitate the search, retrieval, updating, and deletion of data. Example: A university's database might contain tables for students, faculty, courses, and enrollments.

A Database Management System (DBMS) is a software system that enables users to define, create, maintain, and control access to the database. It is the program that interacts with end-users, applications, and the database itself to capture and analyze the data. The DBMS software additionally encompasses the core facilities provided to administer the database. Examples of DBMS: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, MongoDB.

1.1.3 Purpose of Database System and Application

The primary purpose of a database system is to provide a way to store and retrieve database information that is both convenient and efficient.

Key Purposes include:

1. Data Storage, Retrieval, and Update: A robust and efficient mechanism to handle large volumes of data operations.
2. Data Consistency: Ensures multiple copies of the same data do not mismatch (reducing data redundancy).
3. Data Security: Provides mechanisms to restrict unauthorized access and protect data integrity.
4. Data Integrity: Enforces rules and constraints to ensure the data is accurate and valid (e.g., age cannot be a negative number).
5. Concurrency Control: Allows multiple users to access and modify data simultaneously without conflicts.
6. Backup and Recovery: Provides mechanisms to recover data in case of system failures or crashes.

Applications of Database Systems:

• Banking: For customer information, accounts, loans, and banking transactions.
• Airlines: For reservations and schedule information.
• Universities: For student information, course registrations, and grades.
• Credit Card Transactions: For purchases on credit cards and generation of monthly statements.
• Telecommunications: For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing information about communication networks.
• E-commerce: For storing product catalogs, customer data, and managing orders.

1.1.4 Metadata

Metadata is defined as "data about data." It is descriptive information about a particular data set, object, or resource, including how it is formatted, and when and by whom it was collected. In the context of databases, metadata describes the structure of the database objects like tables, columns, data types, constraints, indexes, and views.

Example: In a table storing employee information, the data might be John Doe, 50000, but the metadata would be EmployeeName (VARCHAR 50), Salary (INTEGER).

1.1.5 Data Items, Fields & Records

To understand the structure of a database, it's important to understand the hierarchy of data organization:

1. Data Item (or Value): The smallest unit of data that has meaning to its users. It is a single piece of information, such as a single first name or a single age.
2. Field: A column in a table that is designed to maintain a specific type of data about a particular entity. A field defines the attributes of a record. For example, in an Employee table, EmployeeID, FirstName, LastName, and Department are all fields.
3. Record (or Row/Tuple): A collection of related data items or fields that are treated as a single unit. A record represents a single instance of an entity. For example, all the data about one specific employee (101, Alice, Smith, HR) constitutes one record.

1.1.6 Data Dictionary

A Data Dictionary (also known as a data registry) is a centralized repository of metadata. It contains information about the definitions, characteristics, rules, structure, and usage of data within a database. The DBMS uses the data dictionary to find information about the database schema, storage structures, and security restrictions.

Contents of a Data Dictionary usually include:

• Names and descriptions of all tables and views.
• Names, data types, and lengths of all fields.
• Integrity constraints (like primary keys, foreign keys).
• User permissions and access controls.

---

1.2 File-Oriented System versus Database System

Before the advent of DBMS, data was typically stored in flat files controlled by the operating system (File-Oriented Systems). The database approach was introduced to overcome the significant drawbacks of the file system.

---

1.3 Database Administrator

A Database Administrator (DBA) is a specialized IT professional responsible for directing and performing all activities related to maintaining a successful database environment. The DBA ensures that the database is secure, performs optimally, and is available to users when needed.

1.3.1 Roles and Responsibilities of DBA

The responsibilities of a DBA span a wide range of tasks:

1. Schema Definition: The DBA creates the original database schema by writing a set of data definition language (DDL) statements.
2. Storage Structure and Access Method Definition: Deciding how the data will be represented on the physical storage devices and what access methods (like indexes) are needed to optimize performance.
3. Schema and Physical Organization Modification: Modifying the database structure to reflect changing requirements or to improve performance.
4. Granting of Authorization for Data Access: Determining who is permitted to access different parts of the database and what actions they can perform (read, write, delete).
5. Routine Maintenance:
   • Periodically backing up the database (to tape or remote servers) to prevent data loss.
   • Ensuring enough free disk space is available for normal operations and upgrading disk space as required.
   • Monitoring jobs running on the database and ensuring performance remains acceptable.
6. Performance Tuning: Identifying bottlenecks in database queries or server hardware and tuning the system (e.g., adding indexes, rewriting queries) for optimal speed.
7. Disaster Recovery: Formulating and executing a recovery plan in case of catastrophic system failures.

---

1.4 Schema, Sub-Schema, Instances

Schema: The overall design or logical structure of the database is called the Database Schema. It is a blueprint that describes the data types, relationships, constraints, and tables. The schema is defined during the database design phase and is expected to change infrequently. Example: The structure defining an Employee table (Emp_ID integer, Emp_Name varchar, Salary decimal) is its schema.

Sub-Schema: A Sub-Schema (often related to the External Schema or View) is a subset of the schema having the same properties that describe different views of the database for different user groups. It hides the rest of the database from that user group. Example: A payroll clerk might only have a sub-schema that shows Emp_ID and Salary, hiding personal details like Home_Address.

Instance: The collection of information stored in the database at a particular moment in time is called an Instance of the database. While the schema is relatively static, the instance changes constantly as data is inserted, deleted, or updated. Example: The actual data inside the Employee table at 10:00 AM on Monday is a specific instance. If a new employee is hired at 10:05 AM, the database instance has changed.

---

1.5 Data Abstraction

A major purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data is stored and maintained, making it easier for users to interact with the system without needing to understand complex underlying hardware or software structures.

The ANSI/SPARC architecture defines three levels of data abstraction:

1.5.1 Internal Level (Physical Level)

The lowest level of abstraction describes how the data is actually stored on the physical storage media (like hard drives or SSDs). At this level, complex low-level data structures, file organizations, and access methods (such as B-trees or hashing) are described in detail. This level is usually managed by the DBMS and the DBA.

1.5.2 Conceptual Level (Logical Level)

The next higher level of abstraction describes what data is stored in the database and what the relationships are among those data. The entire database is described in terms of a small number of relatively simple structures. Database administrators work at this level of abstraction to decide what information to keep in the database. It hides the details of physical storage structures.

1.5.3 External Level (View Level)

The highest level of abstraction describes only a part of the entire database. Many users of the database system do not need all the information; instead, they need to access only a part of the database relevant to them. The system may provide many views for the same database. This level simplifies interaction for the end-user and provides security by hiding the rest of the database.

---

1.6 Data Independence

Data Independence is the ability to modify the schema definition in one level of the database architecture without affecting the schema definition in the next higher level. It is a key advantage of the three-level architecture.

There are two types of data independence:

1. Logical Data Independence: The capacity to change the Conceptual Schema without having to change the External Schemas (Views) or application programs. Example: If a new field (like DateOfBirth) is added to an Employee table at the conceptual level, existing applications that query only Emp_Name and Salary (external view) will not need to be rewritten or recompiled. Logical data independence is difficult to achieve as application programs are heavily dependent on the logical structure of the data they access.

2. Physical Data Independence: The capacity to change the Internal Schema (physical storage structure) without having to change the Conceptual Schema or the External Schemas. Example: If the DBA decides to move the database files from one hard drive to another, or if they change the file organization from sequential to indexed for performance reasons, the conceptual schema remains exactly the same. The users and application programs are completely unaware of this physical change. Physical data independence is relatively easy to achieve because applications interact with the conceptual schema, not the physical one.

---

Summary

In this unit, we explored the foundational aspects of database systems. We distinguished between data and information, understood the role of a DBMS, and learned why databases are vastly superior to traditional file systems. We also discussed the vital role of the Database Administrator, clarified the concepts of schemas and instances, and explored how the three-level architecture provides data abstraction and data independence, ensuring systems are flexible, secure, and user-friendly.