Unit 2:

Domains

 Definition:
A domain is the set of all possible values that an attribute can take. It ensures data
integrity by restricting the type of data allowed in an attribute.

 Properties of a Domain:
1. Each attribute must have a single domain.
2. Values in the domain are atomic (cannot be decomposed further).
3. Domains can be simple (integer, string, date) or complex (set of values).

 Examples:

Attribute Domain

Age Integers (0–120)

Gender {Male, Female, Other}

Salary Decimal values (≥0)

Dept_Name Strings, max length 50

Attributes

 Definition:
An attribute is a named column in a relation, representing a property of the entity.
Each attribute draws its values from a specific domain.

 Characteristics:
1. Attributes have unique names within a relation.
2. Values must belong to the attribute’s domain.
3. Can be simple (atomic) or composite (made of multiple sub-parts, e.g.,
Full_Name = First_Name + Last_Name).

 Example:
In a Student table:

 Student_ID (integer), Name (string), Age (integer), Dept (string)

Tuples

 Definition:
A tuple is a single row in a relation, representing one record or instance of the entity.

 Properties:
1. Each tuple is unique in the relation.
2. Tuples are unordered (their sequence does not matter).
3. Each value in a tuple corresponds to its attribute and must belong to the
attribute's domain.

 Example:

 (101, 'Divya', 28, 'CSE')

 (102, 'Rohan', 25, 'ECE')

Relations

 Definition:
A relation is a table with rows (tuples) and columns (attributes). It represents an entity
or relationship in the database.

 Key Components of a Relation:

1. Relation Schema: Defines the structure (name + attributes + domains).
2. Relation Instance: The set of tuples at a particular time.

 Example of Student Relation:

Student_ID Name Age Dept

101 Divya 28 CSE

102 Rohan 25 ECE

103 Meera 27 ME

Characteristics of Relations
1. Unique Tuples: No duplicate rows exist.
2. Atomic Attribute Values: Each value is indivisible.
3. Values from Domains: Every attribute value must be in its domain.
4. Unordered Rows and Columns: Order does not affect data representation.
5. Relation Name: Each relation must have a unique name.
 6. Fixed Attributes: Number and type of attributes remain fixed; only tuples change.
7. Null Values: Attributes can sometimes have NULL values if data is unknown or
missing.
Keys
A key is an attribute or a set of attributes that helps uniquely identify a tuple in a relation.
Types of Keys:
1. Super Key:
o A set of one or more attributes that can uniquely identify a tuple.
o May contain extra attributes not necessary for uniqueness.
o Example: In a Student(Student_ID, Name, Age, Dept) relation:

 {Student_ID} → Super Key

 {Student_ID, Name} → Also a Super Key (but has extra attribute

Name)
2. Candidate Key:
o Minimal Super Key (cannot remove any attribute without losing uniqueness).
o Example: {Student_ID} is a candidate key if no other single attribute uniquely
identifies students.
3. Primary Key:
o A chosen candidate key to uniquely identify tuples in a relation.
o Cannot contain NULL values.
o Example: Student_ID as primary key in Student relation.
4. Foreign Key:
o An attribute in one relation that refers to the primary key of another relation.
o Ensures referential integrity between tables.
o Example: Dept_ID in Student table referencing Dept_ID in Department table.
5. Composite Key:
o A key made up of two or more attributes to uniquely identify a tuple.
o Example: {Course_ID, Student_ID} in Enrollment relation.

Key Attributes of Relation

  Attributes that form keys are called key attributes.

 Properties:
1. Primary key attribute(s) cannot be NULL.
2. Candidate key attributes uniquely identify tuples.
3. Used to enforce relationships between tables.
Example:
In Student(Student_ID, Name, Age, Dept)

 Key attribute: Student_ID (Primary Key)

 If {Student_ID, Name} is a Super Key, Student_ID is still the key attribute.

Relational Database

 Definition: A relational database is a collection of relations (tables) that store data in

rows and columns.

 Components:
1. Relations (Tables): Store data for entities and relationships.
2. Schema: Defines the structure of each relation.
3. Constraints: Rules for data integrity.

 Example:
o Relations: Student, Course, Enrollment, Department
o Each relation has attributes and tuples, e.g., Student(Student_ID, Name, Age,
Dept)
Schemas

 Relation Schema: Defines the structure of a relation:

 RelationName(Attribute1: Domain1, Attribute2: Domain2, ...)

Example:
Student(Student_ID: Integer, Name: String, Age: Integer, Dept: String)

 Database Schema: Defines the structure of the entire database, i.e., all relation
schemas and their relationships.
o Static: Does not change frequently.
o Instance: Actual tuples (data) at a given time
Integrity Constraints
Constraints are rules that ensure accuracy, consistency, and validity of data in a relational
database.
Types of Integrity Constraints:
1. Domain Constraints:
o Attribute values must be within their domain.
o Example: Age must be between 0–120.
2. Key Constraints:
o Primary key attributes must be unique and not NULL.
o Example: Student_ID must be unique.
3. Entity Integrity Constraint:
o No primary key value can be NULL.
4. Referential Integrity Constraint:
o Foreign key values must match primary key values in referenced table or be
NULL.
o Example: Dept_ID in Student must exist in Department.
5. General Constraints:
o User-defined rules, e.g., Salary > 0, End_Date > Start_Date.
Referential Integrity

 Definition:
Referential integrity ensures that a foreign key in one table must match a primary key
in another table or be NULL. It prevents invalid references and maintains consistency
across tables.

 Purpose:
o Avoids “orphan” records.
o Maintains consistency in relational databases.

 Rules:
1. If a tuple in the referencing table has a foreign key value, that value must exist in the
referenced table’s primary key.
2. Foreign key value can be NULL if optional.

 Example:
Department Table

Dept_ID Dept_Name

1 CSE

2 ECE

Student Table

Student_ID Name Dept_ID

101 Divya 1

102 Rohan 2

103 Meera 3

SQL:
CREATE TABLE Student (
Student_ID INT PRIMARY KEY,
Name VARCHAR(50),
Dept_ID INT,
FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);
Intension and Extension

 Intension (Schema):
o Defines the structure of a relation, its attributes, data types, and constraints.
o Does not contain actual data.
Example:
Student(Student_ID: INT, Name: VARCHAR(50), Age: INT, Dept: VARCHAR(20))

 Extension (Instance):
o The actual tuples (rows) stored in the table at a particular time.
Example:

Student_ID Name Age Dept

101 Divya 28 CSE

102 Rohan 25 ECE

 Key Difference:
o Intension = definition
o Extension = current data
Relational Query Languages (SQL)
SQL is divided into DDL (Data Definition Language), DML (Data Manipulation Language),
and DCL (Data Control Language) for complete database operations.
A. DDL (Data Definition Language)

 Purpose: Define or modify database schema (structure).

 Common Commands:

Command Purpose Example

CREATE CREATE TABLE Student(Student_ID INT PRIMARY

Create new table
TABLE KEY, Name VARCHAR(50));

ALTER Modify existing

ALTER TABLE Student ADD COLUMN Age INT;
TABLE table

DROP
Delete table DROP TABLE Student;
TABLE

CREATE Create index for

CREATE INDEX idx_name ON Student(Name);
INDEX faster queries

 Notes:
o DDL commands affect the schema, not the data itself.
o Can also define integrity constraints using DDL.
B. DML (Data Manipulation Language)

 Purpose: Retrieve, insert, update, or delete data in tables.

Command Purpose Example

SELECT Query data SELECT Name, Age FROM Student WHERE

Dept='CSE';
INSERT Add new tuple INSERT INTO Student VALUES (103, 'Meera', 27, 'ECE');

UPDATE Modify existing tuple UPDATE Student SET Age=28 WHERE Student_ID=102;

DELETE Remove tuple DELETE FROM Student WHERE Student_ID=103;

 Notes:
o DML commands affect data (extension) but not schema (intension).
o Can include conditions, joins, and aggregates in SELECT queries.
Integrity Constraints
Constraints maintain accuracy, consistency, and validity of relational data.

Constraint
Purpose SQL Example
Type

Ensure attribute values fall Age INT CHECK (Age BETWEEN 0 AND
Domain
within valid range/type 120)

Unique identifier, cannot be

Primary Key Student_ID INT PRIMARY KEY
NULL

Enforce referential integrity FOREIGN KEY (Dept_ID) REFERENCES

Foreign Key
between tables Department(Dept_ID)

Ensure attribute cannot be

NOT NULL Name VARCHAR(50) NOT NULL
NULL

Ensure column values are

UNIQUE Email VARCHAR(50) UNIQUE
unique

CHECK Enforce user-defined conditions CHECK (Salary >= 0)

Set default value if none

DEFAULT Dept_ID INT DEFAULT 1
specified

 Notes:
o Entity integrity: No primary key value can be NULL.
o Referential integrity: Foreign keys must match primary key or be NULL.
o Domain integrity: All attribute values must follow their domain constraints.

Relational Algebra is a theoretical language in DBMS (Database Management System)

used to query and manipulate data stored in relational databases.
It provides a set of operations that take relations (tables) as input and produce relations
(tables) as output.
Basic Concepts

 Relation: A table with rows and columns.

 Tuple: A single row in a relation.

 Attribute: A column in a relation.

 Schema: Structure of the relation (attribute names and types).

Types of Operations in Relational Algebra

1. Unary Operations (work on one relation)

Operation Symbol Description Example

Selects rows that satisfy σ(Grade='A')(Enrollment) → Students with
Selection Σ
a condition. grade A
π(Name, Dept_ID)(Student) → Only names
Projection Π Selects specific columns.
and department IDs
Renames a relation or its
Rename Ρ ρ(S)(Student) → Rename Student as S
attributes.
2. Binary Operations (work on two relations)

Operation Symbol Description Example

Combines tuples from two

Union ∪ Student1 ∪ Student2
relations (no duplicates).

Returns tuples in one relation but

Set Difference − Student − Alumni
not the other.

Combines every tuple of one

Cartesian
× relation with every tuple of Student × Course
Product
another.

Common tuples between two

Intersection ∩ Student ∩ Alumni
relations.

Combines related tuples from two Student (Student.Dept_ID =

Join
relations based on a condition. Dept.Dept_ID) Dept

Types of Join Operations

Join Type Symbol/Notation Description

 Combines tuples satisfying condition θ (e.g., R.A
Theta Join R _θ S
= S.B)

R _R.A=S.B
Equi Join Theta join using equality
S

Natural
R S Joins automatically using all common attributes
Join

Includes unmatched tuples from left/right/both

Outer Join , ,
relations

Prefer Practice Question