Database Management System

Data

One of the most valuable resources

Database

Organizes raw data into meaningful information

Database Management System

Provides tools to store, retrieve, and secure information

Data

Raw facts and figures, No context or meaning until processed, Can be numbers, text, images, or symbols, Example: 85, Maria, CMPE401.

Information

Processed or organized data that has meaning and value, Example: “Maria scored 85 in CMPE401.” Now the data has context (student, subject, grade).

Data

individual puzzle pieces.

Information

completed puzzle showing the picture.

Database

an organized collection of related data stored in a structured way, so it can be easily accessed, managed, and updated.

Database

allow multiple users and applications to work with data efficiently

Database

like a digital filing cabinet, where files are arranged systematically

Database system

an organized collection of data, along with the software (DBMS) that helps manage and retrieve this data efficiently

Database system

Database + Database Management System (DBMS)

Database system

a complete environment where data is stored, managed, and accessed

Database system

ensure accuracy, consistency, and efficiency.

DBMS

a software that helps users interact with the database.
Functions: Create, update, and delete data, Control access and security, Backup and recovery, Ensure data consistency and integrity

RDBMS(Relational Database Management System)

MySQL, Oracle Database, Microsoft SQL Server, PostgreSQL

NoSQL DBMS

MongoDB, Cassandra, CouchDB

Role of DBMS

Data Storage, Data Retrieval, Security, Backup & Recovery, Concurrency Control

Data Storage

organizes data into structured tables

Data Retrieval

provides SQL queries to search information

Security

manages access control (e.g., only admins can delete records)

Backup & Recovery

ensures data safety during failures

Concurrency Control

allows multiple users to access the same database simultaneously

Advantages of DBMS

Reduces redundancy, Improves consistency, Data security, Scalability, Better decision-making

Reduces redundancy

no repeated storage of same data

Improves consistency

single source of truth

Data security

user authentication and access control

Scalability

databases can grow as business grows

Better decision-making

through queries and reports

American National Standards Institute (ANSI)

has established a three-level architecture for a DBMS: internal, conceptual, and external

Internal level

determines where data is stored on the storage devices. This level deals with low-level access methods and how bytes are transferred to and from storage devices. In other words, this level interacts directly with the hardware.

Conceptual level

defines the logical view of the data. The data model is defined on this level, and the main functions of the DBMS, such as queries, are also on this level. The DBMS changes the internal view of data to the external view that users need to see. This level is an intermediary and frees users from dealing with the internal level.

External level

interacts directly with the user (end users or application programs). It changes the data coming from the conceptual level to a format and view that is familiar to the users.

Types of Databases

Hierarchical Database, Network Database, Relational Database(RDBMS), Object-Oriented Database, NoSQL Database

Hierarchical Database

stores data in a tree-like structure.( Old systems, e.g., IBM IMS)

Network Database

allows more complex relationships (many to many)

Relational Database (RDBMS)

stores data in tables (rows and columns). Most common today

Object-Oriented Database

stores data as objects (used in programming applications).

NoSQL Database

designed for unstructured or big data (e.g., MongoDB, Cassandra).

Structured Database

-database where data is organized in a predefined format (rows and columns)
-Uses Relational Database Management Systems (RDBMS) such as MySQL, Oracle, or PostgreSQL.
-Data fits neatly into tables with fixed fields and relationships.
Simple analogy: Like an Excel spreadsheet—everything has rows and columns with clear labels.

Unstructured Database

• A database that stores data without a predefined model or strict structure
. • Often managed using NoSQL databases (MongoDB, Cassandra, CouchDB).

• Handles data types such as text, images, videos, audio, logs, social media posts, emails.
Simple analogy: Like a folder full of mixed files (PDFs, images, videos) without a uniform format.

Database design

the process of organizing data into logical structures (tables, columns, relationships) before actual implementation

file-based system

early method of storing and managing data where each application has its own separate files to store information
• Data is kept in flat files (like text files or spreadsheets).
• Each application program is responsible for creating, managing, and accessing its own files.
• There is no central management of data

Data redundancy

same data repeated in many files.

Data inconsistency

If data is updated in one file but not in others → mismatch

Poor security

Each application controls its own file → hard to enforce consistent security.

Limited data sharing

Files are isolated → difficult to share across different departments.

Structural

refers to the organization or arrangement of data inside the database—meaning how the database is physically or logically structured.

Structural Dependence

• A condition where any change in the database structure (like adding a new column to a table) requires changes in all the application programs that access that data.
• Common in file-based systems.
• Example: If you add a new field “Email” to a Students file, all programs using that file must be modified.

Structural Independence

• A condition where you can change the database structure without affecting the application programs.
• Provided by DBMS.
• Example: Adding “Email” to a Students table in a relational database does not require changes in existing queries if they don’t use that field.

Data Dependence

A condition where the way data is stored directly affects how applications access it.
• If you change the structure of the data (like adding a field or changing data type), then all programs that use that data must also be modified.
• Common in file-based systems.

Data Independence

• A condition where applications are not affected by changes in the way data is stored or structured.
• Achieved using a DBMS because it provides a layer of abstraction between programs and physical storage.
• DBMS handles changes, so applications can continue working without modifications.

Data redundancy

occurs when the same piece of data is stored in multiple places (files or tables)

Storage Waste

Same data stored many times.

Update Problems

If Maria changes her address, you need to update all files

Inconsistency Risk

One file may have the new address, while another still has the old one

Data anomalies

are inconsistencies or errors that occur in a database due to poor design or redundancy. They usually occur in un-normalized databases (when tables are not organized properly).

Update Anomaly

When data is duplicated, updating one record but not the others lead to inconsistency.

Insert Anomaly

Cannot add new data because some other data is missing

Delete Anomaly

Removing a record deletes unintended information

one to one, one to many, many to many

Three types of data relationshhip

Physical, logical, view levels

Three degrees of data abstraction

data model

a conceptual framework that describes how data is represented, organized, and manipulated within a database system. Think of it as an architect's blueprint for a database.

data model

tells how the logical structure of a database is modeled

Data model

define how data is connected to each other and how it will be processed and stored inside the system

Provide Clarity

They offer a clear and unambiguous structure for how data is stored, related, and accessed

Improve Communication

They create a common language for both technical teams and business stakeholders, ensuring everyone is on the same page

Enhance Data Integrity

By defining rules and relationships, they help reduce redundancy and prevent inconsistencies in the data

Guide Implementation

They serve as a roadmap for building the actual database and a reference for future maintenance and updates

Entities, Attributes, Relationships

Key Building Blocks of a Data Model

Entity

any real-world object, person, place, or concept about which data is stored. For example, in a university database, Student, Course, and Professor are entities.

attribute

a property or characteristic of an entity. For Student, attributes might include StudentID, FirstName, and Major.

relationship

defines how two or more entities are associated with each other. For example, a Student enrolls in a Course.

Entity-Relationship Diagrams

(ER) Diagrams

Cardinality

in a database (or in an Entity-Relationship Diagram, ERD) describes the number of instances of one entity that can be associated with instances of another entity

junction table or associative entity

In a relational database, M:N relationships cannot be implemented directly. We must create a third table, called a

Hierarchical

1960s, Tree-like (one parent per child), Very fast for navigating defined paths., Rigid and inflexible; cannot model many-to many relationships easily.

Network

1970s, Graph-like (multiple parents per child), More flexible than hierarchical; directly models M:N relationships., Overly complex and difficult to query and maintain.

Relational

1970s Present, Tables (relations) with rows and columns, Simple, flexible, and powerful. Still the most, Can be less efficient for navigating deeply connected data.

Object Oriented

1980s 1990s, Objects with attributes and methods, Integrates well with object oriented programming; good for complex data types., Niche use cases; less support and standardization than relational models.

NoSQL

2000s Present, Varies (Document, Key-Value, Graph, etc.), Highly scalable and flexible; handles unstructured and "big data" perfectly., Weaker consistency guarantees (eventual consistency) compared to relational models.

hierarchical model

data is organized as an inverted tree. Each entity has only one parent but can have several children. At the top of the hierarchy, there is one entity, which is called the root.

network model

the entities are organized in a graph, in which some entities can be accessed through several paths

relational model

data is organized in two-dimensional tables called relations. The tables or relations are, however, related to each other, as we will see shortly.

RDBMS

the data is represented as a set of relations

relation

appears as a two-dimensional table

Name

Each relation in a relational database should have a name that is unique among other relations

Attributes

Each column in a relation is called an ___. These are the column headings in the table above

Tuples

Each row in a relation is called a __. It defines a collection of attribute values

Physical Level (Lowest) – INTERNAL VIEW

o This is the lowest level and describes how the data is actually stored on a physical device. It deals with files, memory blocks, indexes, and disk organization.
o This level is the most complex and is managed by the Database Management System (DBMS) and database administrators. It is hidden from most users.
o Example: A student record is stored in a specific data block on a hard drive, with an index on StudentID for fast lookups.

Logical Level (Middle) – EXTERNAL VIEW

o This level describes what data is stored and what relationships exist among that data. It defines all the tables, columns (with their data types), and relationships in the entire database.
o This is the level where database designers and developers work. It provides a complete view of the database structure without worrying about the physical implementation.
o Example: The database contains a Student table with columns like StudentID (Integer), Name (Text), and a Course table related through a foreign key.

View Level (Highest)

o This is the user's perspective. It shows only a small part of the database relevant to a specific user or application, hiding the rest. A single database can have many different views.
o This level provides security by restricting access and simplifies interaction for end-users.
o Example: A professor has a view that only shows the StudentID and Name of students enrolled in their specific courses, hiding student financial data and courses taught by other professors.

relational database model

E.F. Codd

who developed the relational database model

Predicate logic and Set Theory

The relational model, introduced by E. F. Codd in 1970, is based on ___ and ___

Predicate logic

used extensively in mathematics to provide a framework in which an assertion (statement of fact) can be verified as either true or false. Example: A student with a student ID of 324452 is named Mark Reyes.

Set theory

is a part of mathematical science that deals with sets, or groups of things, and is used as the basis for data manipulation in the relational model. Example: Set A contains 15, 23, and 52 while Set B contains 41, 52, 70, 12. It can be concluded that the A and B share a common value, 52.

name/table/entity

is as a two-dimensional structure composed of rows and columns.

Tuple

Each table row ___ represents data about an entity

attribute

Each table column represents an ___