Database Systems

Overview
Database systems transform vast data collections into useful information sources by organizing
and linking data for easy access from multiple perspectives.

1.1 Database Fundamentals

A database is a collection of data organized with internal links, allowing access from various
perspectives, unlike a flat file, which offers a single viewpoint. For example, a flat file lists all
songs by artist, while a database can show all songs by an artist, genre, or related works.
This multidimensional structure enables flexible queries, such as finding all rock songs or artists
influenced by another, making databases powerful tools for extracting relevant information.
Imagine a music streaming app like Spotify. A database lets users search songs by artist, genre,
or playlist, retrieving specific tracks instantly, unlike a flat file that only lists songs
alphabetically.
2.1 The Significance of Database Systems
 Historically, organizations used separate flat files for different tasks, like payroll or
inventory, leading to duplicated data across systems. For instance, customer data might
be stored separately for sales and marketing, causing inefficiencies.
 Database systems integrate these data pools, allowing shared access across
departments. Sales data in a retail database can generate restocking orders, market
trend reports, targeted ads, and sales bonuses, all from one source.
 This integration supports better management decisions by providing access to
comprehensive, organized data. A store manager can use a database to analyze sales
trends and plan inventory, improving efficiency.
 Database technology, combined with data mining, identifies patterns in data, making it a
vital management tool. For example, a supermarket database might reveal which
products sell best together, guiding promotions.
 Databases power major websites like Google, eBay, and Amazon, acting as the core of
their services. When a user searches for a product on Amazon, the server queries a
database, formats results as a webpage, and displays them, making databases the
product itself.
 By combining databases with web interfaces, the Internet has become a global
information hub, enabling instant access to vast data collections for users worldwide.
 Consider an online bookstore. A single database tracks inventory, customer orders, and
preferences, allowing the site to recommend books, process purchases, and manage
stock seamlessly, enhancing user experience and business operations.

A schema is a description of the entire database structure that is used by the database software to
maintain the database.
A subschema is a description of only that portion of the database pertinent to a particular user’s needs.

3.1 The Role of Schemas

 Schemas regulate database access, preventing unauthorized users from viewing sensitive
information while enabling appropriate data sharing. For example, a customer shopping
online should see their order details but not the company’s financial records.
 A schema defines the entire database structure, specifying all data and their relationships,
used by database software to manage the system. In a university database, a schema might
detail student records (e.g., address, grades) linked to faculty records (e.g., employment
history).
 A subschema restricts access to a specific portion of the database tailored to a user’s needs,
enhancing security. For instance, a registrar’s subschema includes student records and
advisor names but excludes faculty employment details, protecting sensitive data.
 Subschemas ensure role-based access, allowing different users to interact with relevant
data only. A payroll department’s subschema might include faculty salaries but not student-
advisor links, preventing access to student information.
Imagine a retail store’s database. A cashier’s subschema allows viewing product prices but not
customer payment details, while a manager’s subschema includes sales data but not employee
records, ensuring data privacy.
Database Management Systems
Database Management Systems (DBMS) organize and manage large data collections, enabling
efficient data access and manipulation in systems like online stores or employee databases.
1. Structure of Database Applications
Database applications consist of two main layers: the application layer and the DBMS layer.
The application layer handles user interactions, such as a website interface where customers
browse products, while the DBMS layer manages the actual data storage and retrieval.
The application layer, which may include web clients and servers, communicates user requests
to the DBMS. For example, when a customer searches for items on an e-commerce site, the
application layer sends the query to the DBMS to fetch results.
2. Role of the DBMS
The DBMS directly manipulates the database, performing tasks like adding, deleting, or
retrieving data based on application requests. For instance, when a user adds a product to their
cart, the DBMS updates the order records.
The application software uses the DBMS as an abstract tool, focusing on what data is needed
rather than how it’s stored. This simplifies interactions, as the DBMS handles complex
operations like searching or updating records.
3. Benefits of Separating Application Software and DBMS
Separating the application layer from the DBMS simplifies software design by isolating data
storage details within the DBMS. For example, the application doesn’t need to know if data is
stored on one server or across a distributed network, as the DBMS manages this complexity.
The DBMS enforces access control by using schemas and subschemas, ensuring users only
access authorized data. In a company database, the DBMS restricts the HR team to employee
records, preventing access to inventory data, as defined by their subschema.
Data independence allows changes to the database structure without altering application
software. For instance, if a personnel department adds a health insurance field to employee
records, only the schema and relevant subschemas are updated, leaving other applications, like
payroll, unchanged.
Database Models
Database models provide a conceptual framework for organizing and accessing data, hiding the complex
internal structure of a database to make it user-friendly.

1. Role of Database Models

Database management systems (DBMS) use abstraction to simplify the complexity of a database’s
internal structure, allowing users to interact with data as if it were arranged in a more intuitive format.
For example, a user sees a company’s employee data as a neat table, even if the actual storage is more
complex.

A database model is the conceptual view of the database that the DBMS presents to users, defining how
data appears to be organized. This model enables users to work with data without needing to
understand the underlying storage system.

The DBMS contains routines that translate user commands, based on the database model, into actions
on the actual data storage system. For instance, a request to list all employees with a specific salary is
processed by the DBMS, regardless of how the data is physically stored.

2. Relational Database Model

In the relational database model, data is conceptualized as a collection of tables, each with rows and
columns, similar to a spreadsheet. For example, a company’s employee database might appear as a
table with a row for each employee and columns for name, address, employee ID, and salary.

The DBMS includes routines that allow application software to perform tasks like selecting specific rows
(e.g., employees in a department) or analyzing column data (e.g., salary ranges), even though the data
isn’t stored as tables internally.

These routines act as abstract tools, enabling users to query or manipulate data based on the table-
based view, simplifying interactions with the database.

3. Object-Oriented Database Model

The object-oriented database model conceptualizes data as objects, which combine data and related
operations, offering an alternative to the table-based relational model. For example, an object might
represent a product in an online store, including its price and methods to update inventory.

This model supports complex data types and relationships, making it suitable for applications like
multimedia or engineering systems, where data doesn’t fit neatly into tables.

4. Interaction with Application Software

Application software, often written in general-purpose programming languages like those discussed in
Chapter 6, lacks built-in database manipulation commands. The DBMS provides prewritten routines,
acting as subroutines, to extend these languages’ capabilities.

These routines allow application software to interact with the database using the conceptual model. For
instance, a program written in Python can use DBMS routines to retrieve employee names from a table,
without needing to handle storage details.

This integration enables developers to write applications that focus on user needs, relying on the DBMS
to manage data access efficiently.

5. Ongoing Development of Database Models

The search for improved database models is continuous, aiming to create models that simplify complex
data systems, allow concise data requests, and ensure efficient DBMS performance.
Better models enhance usability and performance, making it easier to extract meaningful information
from large datasets, such as analyzing customer purchase trends in a retail database.