Database Systems (BACSE202)

Module 1
Database Systems Concepts and
Architecture
 IX. Relational Model Concepts
Introduction:

Relational Model: It is one of the most widely used data models for designing and managing
databases.

• The relational model represents a database as a collection of relations (tables), where each
relation stores information about a specific entity or relationship.
Relation:
• A Relation is a table consisting of rows and columns.
✓ Each relation has a unique name.
✓ Each row stores information about one object.
✓ Each column represents a particular property.
 IX. Relational Model Concepts
Example:

Example

Tuple: A tuple is a collection of related data values representing a single entity occurrence.
• A Tuple is a single row of a relation.
Attribute: An attribute is a named column of a relation that describes a property of an entity.
Domain(data type): It is the collection of all permissible values that an attribute can take.
Example: attribute is Age then possible domains are 19, 18,25,28,.etc.
 1. Characteristics of Relations

• The important characteristics of relations are:

1.1. Ordering of Tuples
1.2. Ordering of Attributes and Values
1.3. Atomic Values (First Normal Form)
1.4. NULL Values
1.5. Interpretation (Meaning) of a Relation
 1. Characteristics of Relations

1.1. Ordering of Tuples:

• A relation is mathematically defined as a set of tuples.
• Since a set has no specific order, the tuples in a relation are unordered.
• Changing the order of rows does not change the relation.
Example:
 1. Characteristics of Relations
1.2. Ordering of Attributes and Values:
• In the basic relational model,
✓ Attributes are written in a particular order.
✓ Tuple values follow the same order.
Example: Attribute order - (Student_ID, Name, Department)
Tuple values order - (101, Ravi, CSE)
Self-Describing Data: When every value is stored together with its attribute name, it is
called self-describing data.
Example: Name → Ravi Department → CSE Age → 20
 1. Characteristics of Relations
1.3. Atomic Values (First Normal Form):
• Each attribute should contain only one indivisible value, such values are called atomic
values.
• An atomic value cannot be divided into smaller meaningful parts within the relational
model.
Example:
Student_ID Name Department
101 Ravi CSE
102 David CSE, ECE (Invalid)
• Here "CSE" is atomic because it contains only one value.
 1. Characteristics of Relations
• Composite attributes are also not allowed directly.
Example:
Address
Door No, Street, City, State, PIN Code
• Instead of Store address as:
Door_No Street City State PIN_Code
as separate attributes.
• Multivalued Attributes not allowed.
Example:
Skills
Java,
Python,
SQL
• This is not allowed in a basic relation.
 1. Characteristics of Relations
First Normal Form (1NF):
• The relational model assumes that every relation satisfies the First Normal Form (1NF).
Rules of 1NF:
✓ Every attribute contains only one value.
✓ No repeating groups.
✓ No multivalued attributes.
✓ No composite attributes.
 1. Characteristics of Relations
1.4. NULL Values:
• Sometimes the value of an attribute is unavailable.
• The relational model uses a special value called NULL.
• NULL is not zero, not blank, and not an empty string.
• It simply indicates the absence of a known or applicable value.
Reasons for NULL:
1. Value Unknown: Example - Student's mobile number has not yet been collected.
2. Value Exists but Not Available: The value exists but has not been entered into the
database.
3. Attribute Does Not Apply: Example - An attribute "Visa_Status" applies only to foreign
students.
 1. Characteristics of Relations
Problems with NULL:
• NULL values create ambiguity.
Example:
Customer A
Address = NULL
Customer B
Address = NULL
• This does not mean that both customers have the same address.
 1. Characteristics of Relations
1.5. Interpretation (Meaning) of a Relation:
• A relation represents information about either:
✓ Entities
✓ Relationships
Relation Representing an Entity:
Example:
MAJORS
Student_ID Name Age
101 Ravi 20

• Each tuple represents one student.

 1. Characteristics of Relations
Relation Representing a Relationship:
Example:
MAJORS

Student_ID Department
101 CSE
102 ECE

• This relation represents the relationship between students and departments.

 1. Characteristics of Relations
Relation Schema:
• A relation schema defines the properties of an entity.
Predicate Interpretation:
• A relation can also be viewed as a predicate in logic.
• Each tuple satisfies the predicate.
Example:
STUDENT(Name, Age)
(Ravi,20)
• “Ravi is a student aged 20."
 1. Characteristics of Relations
Closed World Assumption (CWA):
• The relational model follows the Closed World Assumption.
Definition:
• Only the facts stored in the database are considered true.
• Any fact that is not stored is assumed to be false or unknown.
Example:
Database
Student_ID Name
101 Ravi
102 Priya

• The database confirms that Ravi and Priya are students.

 X. Relational Model Constraints and Relational Database Schemas
Introduction:
• A relational database generally contains multiple relations (tables) that are
interconnected.
• To ensure that the database stores only valid and meaningful data, various constraints
are imposed.
Constraint:
• A constraint is a rule imposed on the database that restricts the type of data that can be
stored, inserted, updated, or deleted.
• Constraints help maintain the quality and correctness of data.
 X. Relational Model Constraints and Relational Database Schemas
Relational Database Schema:
• A database schema defines the overall logical structure of the database.
Categories of Constraints:
• According to the relational model, constraints are divided into three categories.
Implicit (Inherent)Constraints or(Model-Based):
• These Constraints are inherent in the data model.
• They automatically apply to every relation.
• These constraints cannot be removed because they are fundamental properties of the
relational model.
• Characteristics: No duplicate tuples, Atomic values, Unordered tuples, Relation is a set.
 X. Relational Model Constraints and Relational Database Schemas
Explicit (Schema-Based)Constraints:
• These constraints are specified while designing the database schema.
• They are written using DDL(Data Definition Language)
Example:
CREATE TABLE STUDENT(Student_ID INT PRIMARY KEY, Name VARCHAR(30), Age INT);
Characteristics:
Domain Constraints: Restrict permissible values of an attribute.
Key Constraints: Ensure uniqueness.
NULL Constraints: Specify whether NULL values are permitted.
Entity Integrity Constraint: Primary Key Cannot be NULL and Must be unique.
Referential Integrity Constraint: Maintains consistency between related tables.
X. Relational Model Constraints and Relational Database Schemas
 X. Relational Model Constraints and Relational Database Schemas
Semantic (Application-Based) Constraints:
• These rules are implemented in Application programs, Stored procedures, Triggers,
Business logic.
Example:
• A student's attendance must be at least 75% to write the examination.
• Salary increment should not exceed 20%.
 1. Domain Constraints
• A Domain is the collection of all valid values that an attribute can contain.
Example:
Attribute: Gender
Domain: Male, Female
• A Domain Constraint specifies that the value of every attribute in a tuple must be an
atomic value belonging to the domain of that attribute.
• An atomic value cannot be divided into smaller meaningful parts.
• Mathematically: Attribute Value ∈ Domain(Attribute)
• Domain constraints ensure that Only valid values are stored, Data remains accurate,
Invalid entries are prevented, Data integrity is maintained, Errors during processing are
reduced.
 1. Domain Constraints
Types of Domains:
Numeric Domain: Stores numbers.
Character Domain: Stores single characters.
Boolean Domain: Stores logical values.
String Domain: Stores text.
Fixed-Length String: Length remains constant.
Variable-Length String: Stores strings of different lengths.
Date Domain: Stores calendar dates.
Time Domain: Stores time values.
Timestamp Domain: Stores both date and time.
 2. Key Constraints and Constraints on NULL Values
• A Key Constraint is a rule that ensures no two tuples in a relation have the same value for
a specified attribute or set of attributes.
• It guarantees the uniqueness of each record.
• A Superkey is a set of one or more attributes that uniquely identifies each tuple in a
relation.
Examples:
{License_number} ,{Engine_serial_number}, {License_number, Make}
{License_number, Model}
{License_number, Year}
{Engine_serial_number, Make}
{License_number, Engine_serial_number}
{License_number, Engine_serial_number, Make}
{License_number, Engine_serial_number, Make, Model}
{License_number, Engine_serial_number, Make, Model, Year}
 2. Key Constraints and Constraints on NULL Values
• A Key is a minimal superkey, It uniquely identifies every tuple and contains no redundant
attributes.
Example:{License_number}, {Engine_serial_number}
• A Candidate Key is a minimal superkey, If a relation has multiple keys, each is called a
Candidate Key.
Example: License_number, Engine_serial_number
• A Primary Key is the candidate key chosen to uniquely identify tuples in a relation.
Example: License_number
• Candidate keys that are not selected as the primary key are called Unique Keys.
Example:IfPrimary Key = License_number
Then
Unique Key = Engine_serial_number
 2. Key Constraints and Constraints on NULL Values
• A key consisting of more than one attribute is called a Composite Key.
Example: {License_number, Model}
Constraints on NULL Values: Specifies whether an attribute can contain NULL.
• NULL means Unknown value, Missing value, Not applicable, It is not Zero, Blank, Empty
string.
Example:
License_number, Engine_serial_number, Make, Model, Year
✓ These attributes should not contain NULL values
 3. Entity Integrity, Referential Integrity and Foreign Keys
• To maintain data accuracy, consistency and integrity, the relational model defines two
important integrity constraints: Entity Integrity and Referential Integrity
Entity Integrity Constraint:
• Entity Integrity states that no Primary Key attribute can contain a NULL value.
Relation Primary Key
Example: Primary Keys
EMPLOYEE Ssn
Primary Keys (Ssn, Dnumber, Pnumber) DEPARTMENT Dnumber
PROJECT Pnumber
cannot be NULL.
WORKS_ON (Essn, Pno)
Rules of Entity Integrity: DEPT_LOCATIONS (Dnumber, Dlocation)
DEPENDENT (Essn, Dependent_name)
✓ Primary Key must be unique.
✓ Primary Key cannot be NULL.
✓ Every tuple must have a valid primary key.
Example:
 3. Entity Integrity, Referential Integrity and Foreign Keys
Referential Integrity:
• Referential Integrity ensures that a Foreign Key value in one relation must either:
Match an existing Primary Key value in another relation, or Be NULL (if allowed).
• Referential Integrity Maintains consistency between related tables, Prevents invalid
references and Ensures relationships remain valid.
Example:
• Every Foreign Key (Dno, Mgr_ssn, Super_ssn, Essn, Pno, Dnum) must reference an existing Primary
Key.
 3. Entity Integrity, Referential Integrity and Foreign Keys
Foreign Key:
• A Foreign Key is an attribute or set of attributes in one relation that refers to the Primary
Key of another relation.
• The Foreign Key and the referenced Primary Key must have the same domain.
Characteristics:
✓ Creates relationships between tables
✓ References the Primary Key of another table
✓ Values may repeat
✓ May contain NULL values (if permitted)
✓ Maintains Referential Integrity
 3. Entity Integrity, Referential Integrity and Foreign Keys
Referencing Relation and Referenced Relation:
• Two tables participate in a Foreign Key relationship.
Referencing Relation: The table containing the Foreign Key.
Referenced Relation: The table containing the Primary Key.
Self-Referencing Foreign Key:
• A Foreign Key may reference the same table, this is called a Self-Referencing Foreign Key.
Example:
EMPLOYEE.Super_ssn → [Link]
 3. Entity Integrity, Referential Integrity and Foreign Keys
Example: Foreign Keys in Figure 5.7

Referencing Referenced
Foreign Key Primary Key
Relation Relation
EMPLOYEE Dno DEPARTMENT Dnumber
EMPLOYEE Super_ssn EMPLOYEE Ssn
DEPARTMENT Mgr_ssn EMPLOYEE Ssn
DEPT_LOCATIONS Dnumber DEPARTMENT Dnumber
PROJECT Dnum DEPARTMENT Dnumber
WORKS_ON Essn EMPLOYEE Ssn
WORKS_ON Pno PROJECT Pnumber
DEPENDENT Essn EMPLOYEE Ssn
 XI. Dealing with Constraint Violations
• A relational database allows three types of update operations:
1. INSERT 2. DELETE 3. UPDATE (MODIFY)
• Whenever these operations are performed, the DBMS checks whether any database
constraints are violated.
Types of Constraints Checked:
• The DBMS checks the following constraints:
✓ Domain Constraint
✓ Key Constraint
✓ Entity Integrity Constraint
✓ Referential Integrity Constraint
 XI. Dealing with Constraint Violations
• Constraint Violations in Different Operations:
Operation Possible Constraint Violations
INSERT Domain, Key, Entity Integrity, Referential Integrity
DELETE Referential Integrity
UPDATE Domain, Key, Entity Integrity, Referential Integrity
 1. Constraint Violations during INSERT
INSERT Operation:
• The INSERT operation adds a new tuple to a relation.
Possible Violations:
1.1. Domain Constraint Violation:
• Occurs when an attribute value does not belong to its domain.
Example:
INSERT INTO EMPLOYEE VALUES('John', 'ABC’);
• Suppose Salary should be numeric, but a character value is inserted.
 1. Constraint Violations during INSERT
1.2. Key Constraint Violation:
• Occurs when a duplicate primary key value is inserted.
Example:
INSERT INTO EMPLOYEE VALUES('999887777','David’);
• Another employee already has the same SSN.
1.3. Entity Integrity Violation:
• Occurs when the Primary Key is NULL.
Example:
INSERT INTO EMPLOYEE VALUES(NULL,'Cecilia’);
• Primary Key (SSN) cannot be NULL.
 1. Constraint Violations during INSERT
1.4. Referential Integrity Violation:
• Occurs when a Foreign Key refers to a non-existing tuple.
Example:
INSERT INTO EMPLOYEE
(Name,Dno)
VALUES('Cecilia',7);
• Department 7 does not exist.
 2. Constraint Violations during DELETE
DELETE Operation:
• DELETE removes tuples from a relation.
• DELETE usually violates Referential Integrity.
Example:
EMPLOYEE
SSN
333445555
• Referenced by DEPARTMENT (Manager), WORKS_ON, DEPENDENT, EMPLOYEE
(Supervisor)
• If employee 333445555 is deleted, many tables lose valid references.

DELETE FROM EMPLOYEE WHERE SSN='333445555';

 2. Constraint Violations during DELETE
DBMS has 4 possible Actions for DELETE Violations:
i. Restrict (Reject): The DBMS rejects the deletion.
ii. Cascade: The DBMS automatically deletes all dependent records.
iii. Set NULL: Instead of deleting child records, Foreign Key values become NULL.
iv. Set Default: Instead of NULL, Foreign Key is changed to a predefined default value.
 3. Constraint Violations during UPDATE
UPDATE Operation:
• UPDATE changes existing values.
• Constraint violations depend on the updated attribute.
3.1. Updating Normal Attribute:
Example:
Salary
UPDATE EMPLOYEE
SET Salary=50000
WHERE SSN='999887777';
• Only domain is checked, Accepted.
 3. Constraint Violations during UPDATE
3.2. Updating Foreign Key: 3.3. Updating Primary Key:
Example: Example:
UPDATE EMPLOYEE UPDATE EMPLOYEE
SET Dno=7 SET SSN='987654321'
WHERE SSN='999887777'; WHERE SSN='999887777';
• Department 7 does not exist. • Suppose SSN 987654321 already exists.
• Violation: Referential Integrity so • Violations: Key Constraint and Referential Integrity
Rejected. so Rejected.
 3. Constraint Violations during UPDATE
DBMS Actions for UPDATE Violations:
• Same options as DELETE.
i. Restrict
ii. Cascade
iii. Set NULL
iv. Set Default