UCI 204: RELATIONAL DATABASE
MANAGEMENT SYSTEM
INTRODUCTION TO RELATIONAL MODEL &
RELATIONAL DATABASE MANAGEMENT
SYSTEM (RDBMS)
WHAT IS RELATIONAL MODEL
The relational model is a method of organizing
data in a structured, tabular form where data
is stored in relations (tables).
WHAT IS RELATIONAL DATABASE
MANAGEMENT SYSTEM (RDBMS)?
A Relational Database Management
System (RDBMS) is a type of DBMS that stores
data in the form of tables (relations) and
manages relationships between those tables using
keys.
BASIC CONCEPT OF
RDBMS/RELATIONAL MODEL
In an RDBMS:
• Data is stored in tables - A table representing a collection of related
data.
• Tables consist of:
• Rows (Tuples) → A single record in a table.
• Columns (Attributes) → A field representing a data item.
• Domain: The set of allowable values for an attribute.
• Tables are linked using Primary Keys and Foreign Keys
• Data is manipulated using Structured Query Language (SQL)
STRUCTURE OF A RELATIONAL
DATABASE
Example: STUDENT Table
StudentI
Name Course Year
D
101 John IT 2
102 Mary CS 1
•StudentID = Primary Key
•Each row = Record
•Each column = Attribute
STRUCTURE OF A RELATIONAL
DATABASE
• Keys in RDBMS
• Primary Key
• Uniquely identifies each record.
• Cannot be NULL.
• No duplicate values
• Foreign Key
• Links one table to another.
• Maintains referential integrity.
STRUCTURE OF A RELATIONAL
DATABASE
Keys in RDBMS
Example:
• STUDENT table
• COURSE table
• CourseID in STUDENT references COURSE
table.
CHARACTERISTIC OF RDBMS
• Data stored in tabular format
• Relationships enforced using keys
• Supports SQL
• Ensures data integrity
• Supports multi-user access
• Provides ACID properties:
Atomicity, Consistency, Isolation, Durability
KEY PRINCIPLES OF THE RELATIONAL
MODEL
•Data is stored in tables: Each table represents an entity or relationship.
•Every row is unique: Identified by a primary key.
•Relationships are maintained using keys:
•Primary Key: Unique identifier for a table.
•Foreign Key: Links one table to another.
•Data integrity is enforced using constraints:
•Entity integrity: Primary key cannot be null.
•Referential integrity: Foreign key must match a primary key in another
table.
ADVANTAGES OF RELATIONAL MODEL
•Simplicity: Easy to understand and use.
•Flexibility: Can query data using SQL.
•Data Integrity: Constraints enforce accuracy and
consistency.
•Data Independence: Logical structure can change without
affecting applications.
•Multi-user Access: Supports concurrent access by multiple
users.
RELATIONAL OPERATIONS
Relational algebra allows operations on tables such as:
• SELECT (σ): Retrieve rows that satisfy a condition
• PROJECT (π): Retrieve specific columns
• JOIN: Combine related tables
• UNION, INTERSECTION, DIFFERENCE: Set operations
on tables
Example:
Retrieve names of all students enrolled in the “IT” course:
SELECT Name FROM STUDENT WHERE Course = 'IT';
RDBMS VS NON-RELATIONAL
DATABASES
RDBMS Non-Relational (NoSQL)
Table-based Document/Key-Value/Graph
Fixed schema Flexible schema
SQL language NoSQL queries
Strong consistency Often eventual consistency
RDBMS KEY TERMS
1. Relation (Table)
A relation is a table that stores data in rows and
columns.
Example:
STUDENT table
StudentID Name Course
•Each table represents one entity.
•A relation has a unique name.
RDBMS KEY TERMS
2. Tuple (Row)
A tuple is a single row in a table.
Example:
(101, John, IT)
Represents one student record.
RDBMS KEY TERMS
3. Attribute (Column)
An attribute is a column in a table.
Example:
• StudentID
• Name
• Course
RDBMS KEY TERMS
3. Attribute (Column)
Each attribute has:
• A name
• A data type
• A domain (allowed values)
RDBMS KEY TERMS
4. Domain
A domain is the set of valid values an attribute can take.
Example:
• Age → 0–120
• Gender → {Male, Female}
• Year → {1, 2, 3, 4}
RDBMS KEY TERMS
5. Degree
The degree of a relation is the number of
attributes (columns).
Example:
If a table has 4 columns, its degree is 4.
RDBMS KEY TERMS
6. Cardinality
The cardinality of a relation is the number of
tuples (rows).
Example:
If a table has 200 records, its cardinality is 200.
RDBMS KEY TERMS
RDBMS KEY TERMS
7. Keys
(a) Primary Key
• Uniquely identifies each record.
• Cannot be NULL.
• No duplicate values.
Example:
StudentID
RDBMS KEY TERMS
7. Keys
(b) Foreign Key
• An attribute in one table that references a primary
key in another table.
• Establishes relationships between tables.
(c) Candidate Key
• A possible primary key.
• A table may have multiple candidate keys.
RDBMS KEY TERMS
7. Keys
(d) Composite Key
• A key made of two or more attributes.
(e) Super Key
• Any set of attributes that uniquely identifies a
record.
RDBMS KEY TERMS
RDBMS KEY TERMS
8. Schema
A schema is the structure or blueprint of a
database.
It defines:
• Tables
• Attributes
• Relationships
Example:
• STUDENT(StudentID, Name, Course)
RDBMS KEY TERMS
9. Instance
A database instance is the actual data stored in
the database at a specific time.
Schema = Structure
Instance = Data at a given moment
RDBMS KEY TERMS
10. Integrity Constraints
Rules that maintain data accuracy:
• Entity Integrity (Primary key cannot be NULL)
• Referential Integrity (Foreign key must match
existing primary key)
• Domain Integrity (Values must follow defined
domain)
RDBMS KEY TERMS
11. SQL (Structured Query Language)
SQL is the language used to interact with RDBMS.
Common RDBMS examples include:
• MySQL
• Oracle Database
• PostgreSQL
RDBMS KEY TERMS
12. View
A view is a virtual table created from one or more
tables.
• Does not store data physically
• Used for security and simplicity
RDBMS KEY TERMS
13. Index
An index improves the speed of data retrieval.
• Works like an index in a book
• Speeds up search operations