DBMS
UNIT-II
Relational
Model
Content:
Introduction to relational model, concepts
of domain, attribute, tuple, relation,
Importance of null values,
constraints (Domain, Key constraints,
integrity
constraints) and their importance,
Relational Algebra, Calculus.
BASIC SQL:
Simple Database schema, data types,
table definitions (create, alter),
different DML operations (insert, delete,
update).
Introduction to relational model
The relational model in DBMS organizes
data into two-dimensional tables called
relations, where each table represents an
entity, columns are its attributes, and
rows are tuples (records).
Introduced by E.F. Codd(Edgar F. Codd)
British “computer scientist” it separates
logical data structure from physical
storage, allowing for flexible data access
via SQL, making it the foundation for
modern databases like MySQL and
Oracle.
Introduction to relational model
Relation (Table): A collection of related data,
structured as rows and columns.
Attribute (Column/Field): A property or
characteristic of the entity (e.g., Student
Name, ID).
Tuple (Row/Record): A single instance of the
entity, containing a set of attribute values
(e.g., data for one specific student).
Domain: The set of permissible values for an
attribute (e.g., ages must be positive
integers).
Keys: Special attributes (like Primary Keys)
used to uniquely identify tuples and
establish relationships between tables.
Example: Consider a relation STUDENT with attributes
ROLL_NO, NAME, ADDRESS, PHONE and AGE shown in the
table.
constraints (Domain, Key constraints, integrity
constraints) and their importance
Constraints in DBMS are rules (Domain,
Key, Integrity) that maintain data
accuracy, consistency, and validity by
restricting the type of data entered and
ensuring valid relationships.
They prevent erroneous data insertion,
guarantee unique record identification,
and maintain structural integrity across
tables, which is critical for high-quality
database management.
Types of Constraints
Domain Constraints: Define the permissible
values, data types (string, integer, date), and
formats for a column, ensuring that only
valid data is entered.
Key Constraints: Ensure that special keys,
such as Primary Keys, are unique and not
null, thus allowing for the distinct
identification of each tuple (row) in a table.
Integrity Constraints:
Entity Integrity: Guarantees that the Primary Key is
not null, which is crucial for distinguishing
records.
Referential Integrity: Ensures that a foreign key in
one table matches a primary key in another table,
or is null, maintaining consistency across linked
tables.
Importance of Constraints
Data Accuracy: By restricting inputs to specified
domains, it ensures data is accurate and fits
within expected parameters.
Data Consistency: Prevents invalid or
contradictory data from being stored, reducing
anomalies.
Database Reliability: Ensures that relationships
between tables are consistent, preventing
broken links or orphans.
Preventing Duplication: Key constraints prevent
the same record from being inserted more than
once, maintaining unique identification.
Relational Algebra, Calculus
Relational Algebra (procedural) and
Relational Calculus (declarative) are the
theoretical foundations of DBMS query
languages like SQL.
Algebra defines how to retrieve data using
operators (select, project, join) to produce
new relations.
Calculus focuses on what data is required
using logic (tuple or domain-based),
without defining procedural steps.
Relational Algebra, Calculus
Simple Database schema, data types, table
definitions (create, alter)
A database schema acts as the blueprint
for organizing data, defining the tables,
columns, data types, and the
relationships between them.
Data Definition Language (DDL)
statements like CREATE TABLE and
ALTER TABLE are used to define and
modify this structure.
Simple Database Schema Example
A very common simple schema involves a
few related tables, such as one for users
and another for their orders.
Below is an example with three tables:
Users, Products, and Orders.
●Users: Stores user information.
●Products: Stores product details.
●Orders: Links users to products via specific
orders.
Common Data Types
● The specific data types can vary slightly between different
database systems (like MySQL, PostgreSQL, or SQL Server),
but common types include:
INT: Stores integer (whole) numbers (e.g., a User ID).
VARCHAR(size): Stores variable-length character strings (e.g., a
name or address).
TEXT: Stores larger amounts of character data.
DATE or TIMESTAMP: Stores date and time information (e.g.,
order date).
DECIMAL(P, S) or NUMERIC: Stores exact numeric values, often
used for currency (e.g., product price).
Table Definitions (SQL Examples)
●The following SQL (Structured Query
Language) commands demonstrate how to
define and modify these tables.
1. CREATE TABLE
The CREATE TABLE statement is used to
create a new table, specifying column
names and their data types.
DML operations (insert, delete, update).
Data Manipulation Language (DML)
commands (INSERT, UPDATE, DELETE)
are SQL statements used to manage,
modify, and manipulate data within
database tables without altering the table
structure.
They allow users to add new rows,
change existing data, and remove
records, typically requiring transaction
control to make changes permanent.
THE END