DATABASE
MANAGEMENT SYSTEM
RYAN Z. GONZAGA, MIS
Introduction to DBMS
Learning Objectives:
Define Understand Identify
Define data, Understand the six Identify the
databases, and major components limitations of
DBMS of a DBMS traditional file-
application based systems
compared to
DBMS
Key Concepts:
• Data vs. Database: Data consists of raw facts, while a database is an organized
collection of structured data stored electronically.
• The DBMS: A software layer acting as an intermediary between users and raw data,
handling tasks like querying, security, and performance optimization.
• Six Components: Hardware (servers/disks), Software (MySQL/Oracle), Data (operational
and metadata), Procedures (rules/setup), Database Access Language (SQL), and People
(DBAs, developers, and end-users).
• ACID Properties: Essential principles—Atomicity, Consistency, Isolation, and
Durability—that ensure reliable transactions.
• File System Drawbacks: Traditional systems suffer from data redundancy (duplication),
inconsistency, and poor security.
Examples:
• A university file-based system where student info is duplicated across
academics and hostel files, leading to inconsistency if a student changes their
address.
• Real-world apps: Banking systems for account management or social media for
user profiles.
Questions:
• In a shopping market database, why is it better to have a central DBMS rather
than separate files for sales and inventory?
• Which person in the DBMS "People" component is responsible for managing
security and user access?
Database
Architecture
Learning Objectives:
• Compare 1-Tier, 2-Tier, and 3-Tier architectures.
• Explain the three levels of data abstraction.
Key Concepts:
• 1-Tier Architecture: The user, application, and database all reside on a single
system (e.g., a local file).
• 2-Tier Architecture: A client-server model where the application on the client
end directly communicates with the database on the server using APIs like JDBC.
• 3-Tier Architecture: Adds an intermediate application server layer between the
client and the database, enhancing security and scalability for large web
applications.
Levels of Abstraction:
• Physical Level: How data is actually stored in memory.
• Logical Level: Describes what data is stored and the relationships between
tables.
• View Level: The part of the database visible to individual end-users.
Diagram
Examples:
• 1-Tier: Microsoft Excel used for personal calculations.
• 2-Tier: A Library Management System used in a small school.
• 3-Tier: An E-commerce store where a user searches for products (client), the
system calculates prices/discounts (application server), and retrieves history
(database).
Example
Questions:
• Why is 3-tier architecture preferred for large web applications over 2-tier?
• How does data abstraction help developers when they need to change the
physical storage disks?
Why is 3-tier architecture preferred for large web
applications over 2-tier?
Why 3-tier architecture is preferred over 2-tier for large web applications:
• Separation of concerns: In 3-tier architecture, the presentation layer (client), application layer
(business logic), and database layer (data storage) are separate. This makes it easier to maintain and
update each layer independently.
• Scalability: You can scale the application server or database server independently without affecting
the client layer. In 2-tier, the client is tightly connected to the database, which becomes a bottleneck.
• Security: Business logic and database access are on the server side, so users cannot directly manipulate
the database.
• Flexibility: Changes to the database or application logic don’t require changing the client software.
• Better performance for many users: 3-tier handles large numbers of simultaneous users efficiently
because the application server can manage multiple requests.
How data abstraction helps when changing physical
storage disks:
• Data abstraction hides the physical details of how data is stored from the
developers. They interact with the database through tables, queries, or APIs,
not worrying about the actual disk structure.
• If the company changes to a new storage system (e.g., SSDs, cloud storage),
developers don’t need to rewrite their code. The DBMS handles the mapping
from logical data (tables) to physical storage.
• Benefits:
• Simplifies development.
• Protects applications from hardware changes.
• Ensures continuity of operations during upgrades.
Example:
Your e-commerce database tables still work the same way even if the company
moves from on-premises HDDs to cloud storage. Queries like SELECT * FROM
orders don’t change.
The
Relational Model
Learning Objectives:
• Define relational terminology (tuples, attributes, relations).
• Understand keys and integrity constraints.
Key Concepts:
• Tabular Structure: Data is organized into Relations (tables) consisting of Tuples (rows)
and Attributes (columns).
• Key Terms: Degree is the number of attributes; Cardinality is the number of tuples.
• Types of Keys:
• Primary Key: Uniquely identifies a tuple and cannot be NULL.
• Foreign Key: An attribute that refers to the primary key of another table to create a
link.
• Candidate/Super Keys: Attributes capable of uniquely identifying records.
• Integrity Constraints: Domain constraints ensure atomic values from a specific type
(e.g., integer, date); Referential integrity ensures that a foreign key value must exist in the
referenced table.
Examples:
• A STUDENT table with attributes like Roll_No (Primary Key), Name, and Age.
• Linking a STUDENT table to a BRANCH table using a Branch_Code as a foreign
key.
Questions:
• If we try to delete a department from the "Branch" table that still has students
assigned to it, what kind of anomaly or error occurs?
• Can a primary key contain a NULL value? Why or why not?
Answer
Deleting a branch that still has students:
• The DBMS throws a referential integrity error (sometimes called a foreign key violation) because
deleting the branch would leave students referencing a non-existent branch.
• This is called an update/delete anomaly related to referential integrity.
Can a primary key contain NULL?
• Answer: No, a primary key cannot be NULL.
• Reason:
• The primary key uniquely identifies each record in a table.
• If it were NULL, it would mean “unknown value,” which cannot be used to uniquely identify a
row.
• Every primary key value must be unique and not null.
Entity-Relationship
(ER) Modeling
Learning Objectives:
• Identify entities, attributes, and relationships.
• Translate business requirements into a visual ER diagram.
Key Concepts:
• Entities: Real-world objects (Rectangle). Strong entities have a unique key; Weak
entities depend on a strong entity for identification (Double Rectangle).
• Attributes (Ovals):
• Key: Unique identifier (Underlined).
• Multivalued: Can have multiple values, like Phone_No (Double Oval).
• Derived: Calculated from other data, like Age from Date of Birth (Dashed Oval).
• Relationships (Diamonds): Associations between entities.
• Cardinality: Defines the numerical constraints of the relationship (1:1, 1:M, M:1, M:N).
• Participation Constraints: Total participation (every entity must participate, shown by
double lines) vs. Partial participation.
Examples:
• 1:M Relationship: One Surgeon Department has many Doctors.
• M:N Relationship: An employee works on multiple projects, and each project
has multiple employees.
• Weak Entity: A "Dependent" (spouse/child) who cannot exist in the system
without an "Employee".
Questions:
• What is the difference between a Composite attribute and a Multivalued
attribute? Give an example of each.
• In an ER diagram for a university, if every student must be enrolled in at least one
course, is the participation total or partial?
Answer
Difference between Composite and Multivalued attributes:
Attribute Type Definition Example
An attribute that can be divided
Name → can be divided into
Composite Attribute into smaller sub-parts, each
First_Name and Last_Name.
representing more basic
information.
An attribute that can have Phone_Numbers → a student
Multivalued Attribute multiple values for a single can have more than one phone
entity. number.
Answer
Participation in an ER diagram:
• Total Participation: Every entity in the entity set must be associated with at least one
entity in the related entity set.
• Partial Participation: Some entities may not be associated with any entity in the related
entity set.
Example for your question:
• In a university ER diagram, every student must be enrolled in at least one course.
• This is total participation because no student can exist without being enrolled in a
course.