CC106 - Fundamentals of Database Systems

Introduction to Databases
Mary Jane L. Dumalagan
data vs database

A database is a collection of related data.

A database is designed, built, and populated with data for a
specific purpose. It has an intended group of users and some
preconceived applications in which these users are interested.
By data, we mean known facts that can be recorded and that
have implicit meaning.
Database Management Systems (DBMS)
A database management system (DBMS) is a computerized system that enables
users to create and maintain a database. The DBMS is a general-purpose software
system that facilitates the processes of defining, constructing, manipulating, and
sharing databases among various users and applications.

Defining a database involves specifying the data types, structures, and constraints
of the data to be stored in the database. The database definition or descriptive
information is also stored by the DBMS in the form of a database catalog or
dictionary; it is called meta-data.
Database Management Systems (DBMS)

Constructing the database is the process of storing the data on some storage
medium that is controlled by the DBMS.

Manipulating a database includes functions such as querying the database to

retrieve specific data, updating the database to reflect changes in the miniworld,
and generating reports from the data.

Sharing a database allows multiple users and programs to access the database
simultaneously.
Application Program

- accesses the database by sending queries or requests for data to the DBMS.

Query , Transaction
An application program accesses the database by sending queries or requests for data to
the DBMS. A query typically causes some data to be retrieved; a transaction may cause
some data to be read and some data to be written into the database.
 Protection Maintain

Protection includes system protection against hardware or software

malfunction (or crashes) and security protection against unauthorized or
malicious access. A typical large database may have a life cycle of many
years, so the DBMS must be able to maintain the database system by
allowing the system to evolve as requirements change over time.
DATABASE DBMS Software

Database
System
DATABASE DBMS Software

Database
System
A simplified database system environment.
 Users/Programmers: These are the individuals who interact with the database
system, either by writing application programs or by running queries.

Application Programs/Queries: This box represents the programs or queries

written by users to interact with the database. These can be anything from
simple data retrieval queries to complex applications that manipulate data.

DBMS Software: The Database Management System (DBMS) software is the

core component that manages the database. It handles the execution of
queries and the management of data.

Software to Process Queries/Programs: This part of the DBMS software is

responsible for processing the queries and programs submitted by users. It
interprets the commands and executes them.

Software to Access Stored Data: This component of the DBMS software

handles the actual retrieval and storage of data in the database.

Stored Database Definition (Meta-Data): This is a special part of the database

that stores metadata, which is data about the data. It includes information like
the structure of the database, the types of data stored, and the relationships
between different data elements.

Stored Database: This is the actual database where the data is stored. It
contains all the data that users interact with through their queries and
application programs.
A simplified database system environment.
 Users/Programmers:

Example: A data analyst who writes SQL queries to retrieve

sales data from a company’s database.

Application Programs/Queries:

Example: A web application that allows users to search for

products in an online store. The application sends SQL queries
to the database to fetch product information based on user
input.

DBMS Software:

Example: MySQL, PostgreSQL, or Oracle Database. These are

software systems that manage databases and provide tools for
data manipulation and retrieval.

A simplified database system environment.

 Software to Process Queries/Programs:

Example: The query processor in MySQL that interprets and executes

SQL commands. When a user submits a query like SELECT * FROM
products WHERE price < 100, the query processor parses, optimizes,
and executes this query.

Software to Access Stored Data:

Example: The storage engine in MySQL, such as InnoDB, which

handles the actual reading and writing of data to the disk. When the
query processor needs to fetch data, it communicates with the storage
engine to retrieve the necessary records.

Stored Database Definition (Meta-Data):

Example: The information schema in MySQL, which contains metadata

about the database, such as table definitions, column types, and
indexes. For instance, it stores details about the products table, like
the names and types of its columns (id, name, price, etc.).

Stored Database:

Example: The actual data stored in the products table.

A simplified database system environment.
Types of Databases
Relational Databases (SQL)

Relational databases store data in tables, which are composed of rows and
columns. Each row represents a record, and each column represents an attribute.
The tables are linked together by relationships, which are established using keys.

● Table: A set of data organized into rows and columns, similar to a spreadsheet.
For example, a table named 'Students' might have columns for StudentID,
FirstName, LastName, and Major.
● Row (Record/Tuple): A single entry in a table. In the 'Students' table, one row
would contain all the data for a single student.
Column (Field/Attribute): A vertical category of data within a table. FirstName
is a column.

Primary Key: A column (or set of columns) that uniquely identifies each row in
a table. It cannot contain duplicate values or be null (empty). For example,
StudentID would be a good primary key.

Foreign Key: A column in one table that links to the primary key of another
table. It's how we establish relationships between different tables. For
example, a CourseID in a 'Registrations' table could be a foreign key
referencing the CourseID primary key in a 'Courses' table.
Types of Databases
Non-Relational Databases (NoSQL)

NoSQL databases are designed to handle large volumes of unstructured or

semi-structured data. They don't use the traditional table-based relational model.
They are often more flexible and scalable for certain applications, like big data or
real-time web apps. Types of NoSQL databases include:

● Document: Stores data in flexible, semi-structured documents (e.g., JSON).

● Key-Value: Stores data as a simple collection of key-value pairs.
● Graph: Uses nodes and edges to represent and store data.
● Column-Family: Stores data in column families rather than rows.
Non-Relational Databases (NoSQL)

Document Databases
These databases store data in flexible, semi-structured "documents," typically in
formats like JSON (JavaScript Object Notation). A document can contain nested
fields and arrays, and different documents in the same database can have different
structures. This flexibility makes them great for applications with evolving data
requirements.
Example: MongoDB * Use Case: A social media app where each user's
profile is a document. This document could contain their name, email, friends
list, and a list of their recent posts. The structure can be easily updated for
each user without affecting others.
Non-Relational Databases (NoSQL)

Document Databases
These databases store data in flexible, semi-structured
"documents," typically in formats like JSON (JavaScript
Object Notation). A document can contain nested fields
and arrays, and different documents in the same
database can have different structures. This flexibility
makes them great for applications with evolving data
requirements.
Example: MongoDB: A social media app where each
user's profile is a document. This document could contain
their name, email, friends list, and a list of their recent
posts. The structure can be easily updated for each user
without affecting others.
Non-Relational Databases (NoSQL)

Key-Value Stores

This is the simplest type of NoSQL database. It stores data as a collection of key-value pairs, where each unique
key points to a specific value. You can only retrieve a value by knowing its exact key. This model is ideal for
high-speed read/write operations and caching.

● Example: Redis
○ A real-time online game that needs to store player session data and leaderboards. A unique session
ID is the key, and the value is a large, complex object containing all the player's current data.
○ Key-Value Example:
■ Key: user_session:12345
■ Value: { "player_name": "gamer_1", "score": 9870, "last_login":
"2025-09-01T10:00:00Z" }
Non-Relational Databases (NoSQL)

Wide-Column Stores
These databases are similar to relational tables but are organized by columns rather than rows.
They are optimized for handling massive amounts of data with very high write throughput. Data is
stored in column families, allowing for flexible schema that can vary from row to row.

● Example: Apache Cassandra

○ An Internet of Things (IoT) application that collects a massive number of time-series
data points from sensors. The data is partitioned by sensor ID, and each sensor can
have different types of data (e.g., temperature, humidity, light) stored in separate
columns, allowing for fast writes and analytical queries across specific columns.
Non-Relational Databases (NoSQL)

Graph Databases
Graph databases use a model based on nodes (entities) and edges (relationships) to represent
and store data. This model is exceptionally good at handling and querying highly connected data,
where relationships are as important as the data itself.

● Example: Neo4j
○ A social networking service that needs to find all friends of a friend, or recommend new
connections. The users are nodes, and the friendships are the edges. A query can
traverse these relationships to find patterns efficiently.
EXAMPLE
Example

A UNIVERSITY database for maintaining information concerning

students, courses, and grades in a university environment.
Figure 1. A database that stores student and course information.
Define
- To define this database, we must specify the structure of the records of
each file by specifying the different types of data elements to be
stored in each record.
- data elements
- data type
Construct
- To construct the UNIVERSITY database, we store data to represent
each student, course, section, grade report, and prerequisite as a
record in the appropriate file.

- Notice that records in the various files may be related. For example, the
record for Smith in the STUDENT file is related to two records in the
GRADE_REPORT file that specify Smith’s grades in two sections.
Database manipulation
Examples of queries are as follows:

■ Retrieve the transcript—a list of all courses and grades—of ‘Smith’

■ List the names of students who took the section of the ‘Database’ course offered in
fall 2008 and their grades in that section
■ List the prerequisites of the ‘Database’ course

Examples of updates include the following:

■ Change the class of ‘Smith’ to sophomore
■ Create a new section for the ‘Database’ course for this semester
■ Enter a grade of ‘A’ for ‘Smith’ in the ‘Database’ section of last semester