UNIT - IV:

Relational Languages: The Tuple Relational Calculus - The Domain Relational Calculus Database Design and
the E-R Model: Overview of the Design Process - The Entity- Relationship Model - Reduction to Relational
Schemas - Entity Relationship Design Issues - Extended E-R Features - Alternative Notations for Modeling Data
- Other Aspects of Database Design
Tuple Relational Calculus (TRC)
1. Introduction
Tuple Relational Calculus (TRC) is a non-procedural (declarative) query language used in
relational database systems. In TRC, the user specifies what data is required, not how to retrieve it.
It forms the theoretical foundation of SQL.
Unlike Relational Algebra, which is procedural, TRC is based on mathematical predicate logic.

2. Basic Idea of TRC

In Tuple Relational Calculus, queries are written using tuple variables. These variables represent
rows (tuples) of relations.
A TRC expression defines a set of tuples that satisfy a given condition.
General Form:
{ t | P(t) }
Where:
 t → tuple variable
 P(t) → predicate (logical condition)
Meaning: Select all tuples t for which condition P(t) is true.

3. Components of Tuple Relational Calculus

3.1 Tuple Variable
 Represents a tuple of a relation
 Can take values from a specified relation
Example:
Student(t)
Here, t is a tuple variable ranging over the Student relation.

3.2 Predicate
A predicate is a logical expression that evaluates to true or false.
Predicates are formed using:
 Comparison operators: =, ≠, <, >, ≤, ≥
 Logical operators: AND (∧), OR (∨), NOT (¬)
Example:
[Link] > 18

3.3 Logical Connectives

Logical connectives combine multiple conditions.
Operator Meaning
∧ AND
∨ OR
¬ NOT
→ Implication
Example:
[Link] > 18 ∧ [Link] = 'Chennai'

3.4 Quantifiers
Quantifiers are used to express conditions involving all or some tuples.
(a) Existential Quantifier (∃)
 Means "there exists"
Example:
∃s (Student(s) ∧ [Link] = 'CSE')
Meaning: There exists at least one student in the CSE department.
(b) Universal Quantifier (∀)
 Means "for all"
Example:
∀s (Student(s) → [Link] > 17)
Meaning: All students are older than 17.
4. Free and Bound Variables
4.1 Free Variable
 Appears outside the scope of quantifiers
 Determines the result of the query
4.2 Bound Variable
 Appears within quantifiers (∃ or ∀)
 Used only for condition checking
Example:
{ t | Student(t) ∧ ∃s (Course(s) ∧ [Link] = [Link]) }
 t → free variable
 s → bound variable

5. Query Examples in TRC

Example 1: Find all students
{ t | Student(t) }
Example 2: Find names of students older than 20
{ [Link] | Student(t) ∧ [Link] > 20 }
Example 3: Find students from CSE department
{ t | Student(t) ∧ [Link] = 'CSE' }
Example 4: Find students enrolled in at least one course
{ t | Student(t) ∧ ∃c (Enroll(c) ∧ [Link] = [Link]) }

6. Safety of Tuple Relational Calculus

A TRC expression is safe if:
 It returns a finite result
 All values in the result come from the database
Unsafe expressions may produce infinite results.
Safe Expression Example:
{ t | Student(t) ∧ [Link] > 18 }
Unsafe Expression Example:
{ t | ¬Student(t) }

7. Comparison with Relational Algebra

Relational Algebra Tuple Relational Calculus
Procedural Non-procedural
Specifies how to get data Specifies what data is needed
Uses operators Uses logic and predicates
More operational More declarative

8. Advantages of TRC
 Easy to understand due to logical form
 Closer to natural language
 Basis for SQL query language

9. Disadvantages of TRC
 Complex for large queries
 Not directly implemented in DBMS
 Requires careful handling to ensure safety

10. Key Points for Exams

 TRC is a declarative query language
 Uses tuple variables and predicates
 Based on first-order predicate logic
 Uses quantifiers (∃, ∀)
 Safety ensures finite results

11. Short Answer Points

 TRC specifies what to retrieve, not how
 Tuple variable represents a row of a relation
 Existential quantifier checks for existence
 Universal quantifier checks for all tuples
Domain Relational Calculus (DRC) and Database Design
PART A: DOMAIN RELATIONAL CALCULUS (DRC)
1. Introduction to Domain Relational Calculus
Domain Relational Calculus (DRC) is a non-procedural (declarative) query language used in relational databases. It is
based on first-order predicate logic.
In DRC, queries are written using domain variables instead of tuple variables. Each domain variable represents an
attribute value rather than a whole tuple.
DRC focuses on what data is required, not how to retrieve it.

2. Basic Form of DRC Expression

General Syntax:
{ <x1, x2, x3, … , xn> | P(x1, x2, x3, … , xn) }
Where:
 x1, x2, x3, … , xn → domain variables
 P → predicate (logical condition)
Meaning: Select values of variables for which the predicate is true.

3. Components of Domain Relational Calculus

3.1 Domain Variables
 Represent individual attribute values
 Each variable ranges over a domain
Example:
Student(sid, sname, dept)
Here:
 sid, sname, dept are domain variables

3.2 Predicate
A predicate is a logical expression that evaluates to true or false.
Predicates use:
 Comparison operators: =, ≠, <, >, ≤, ≥
 Logical operators: ∧ (AND), ∨ (OR), ¬ (NOT)
Example:
dept = 'CSE'

3.4 Quantifiers
Existential Quantifier (∃)
 Means "there exists"
Example:
∃cid (Enroll(sid, cid))
Meaning: Student is enrolled in at least one course.
Universal Quantifier (∀)
 Means "for all"
Example:
∀sid (Student(sid, sname, dept) → dept ≠ 'NULL')

4. Free and Bound Variables in DRC

Free Variables
 Variables listed in the result
 Determine the output
Bound Variables
 Variables used inside quantifiers
 Used only for checking conditions

5. Examples of DRC Queries

Example 1: Find all student names
{ <sname> | ∃sid, dept (Student(sid, sname, dept)) }
Example 2: Find students from CSE department
{ <sname> | ∃sid (Student(sid, sname, 'CSE')) }
Example 3: Find students enrolled in a course
{ <sname> | ∃sid, cid (Student(sid, sname, dept) ∧ Enroll(sid, cid)) }

6. Safety of Domain Relational Calculus

A DRC expression is safe if:
 It produces a finite result
 All values come from the database
Unsafe expressions may produce infinite results.
PART B: DATABASE DESIGN
8. Introduction to Database Design
Database design is the process of structuring data efficiently to reduce redundancy and ensure data integrity.
Good database design improves:
 Data consistency
 Storage efficiency
 Query performance

9. Phases of Database Design

9.1 Requirement Analysis
 Identify data to be stored
 Understand user requirements
9.2 Conceptual Design
 Uses Entity-Relationship (ER) model
 Defines entities, attributes, relationships
9.3 Logical Design
 Converts ER model into relational schema
 Defines tables, keys, and constraints
9.4 Physical Design
 Specifies storage structures
 Indexes, file organization

10. Entity-Relationship (ER) Model

10.1 Entity
 A real-world object
 Example: Student, Course
10.2 Attributes
 Properties of entities
 Example: student_id, name
10.3 Relationships
 Associations between entities
 Example: Enrolls

11. Keys in Database Design

11.1 Primary Key
 Uniquely identifies a record
11.2 Foreign Key
 Refers to primary key of another table
11.3 Candidate Key
 Possible primary keys

12. Normalization
Normalization is the process of organizing data to reduce redundancy.
Normal Forms:
 1NF: Atomic values
 2NF: No partial dependency
 3NF: No transitive dependency

13. Advantages of Good Database Design

 Reduces data redundancy
 Ensures data integrity
 Easy maintenance
 Efficient queries

14. Key Exam Points

 DRC is declarative and logic-based
 Uses domain variables
 Database design follows structured phases
 Normalization improves database quality

Domain Relational Calculus, Database Design and the E–R Model

Overview of the Database Design Process

PART A: DOMAIN RELATIONAL CALCULUS (DRC)

1. Introduction to Domain Relational Calculus
Domain Relational Calculus (DRC) is a non-procedural (declarative) query language used in relational database systems.
It is based on first-order predicate logic.
In DRC, queries are written using domain variables, where each variable represents an attribute value rather than a
complete tuple. The user specifies what data is required, not how to retrieve it.

2. Basic Syntax of Domain Relational Calculus

General Form:
{ <x1, x2, ..., xn> | P(x1, x2, ..., xn) }
Where:
 x1, x2, ..., xn are domain variables
 P is a predicate (logical condition)
Meaning: Select attribute values that satisfy the predicate condition.

3. Components of DRC
3.1 Domain Variables
 Represent individual attribute values
 Each variable takes values from a domain
Example:
Student(sid, sname, dept)
Here sid, sname, and dept are domain variables.

3.2 Predicates
Predicates are logical expressions that evaluate to true or false.
They use:
 Comparison operators: =, ≠, <, >, ≤, ≥
 Logical operators: ∧ (AND), ∨ (OR), ¬ (NOT)

3.3 Quantifiers
Existential Quantifier (∃)
 Means there exists
Example:
∃cid (Enroll(sid, cid))
Universal Quantifier (∀)
 Means for all

4. Safety of Domain Relational Calculus

A DRC expression is safe if:
 It returns a finite number of results
 All values come from the database
Safety ensures practical implementation.

PART B: DATABASE DESIGN

5. Introduction to Database Design
Database design is the process of structuring data efficiently to minimize redundancy and maintain data integrity. A good
design supports easy data access and modification.

6. Overview of the Database Design Process

The database design process consists of the following stages:
1. Requirement Analysis
2. Conceptual Design
3. Logical Design
4. Physical Design

7. Requirement Analysis
 Identify data to be stored
 Determine user needs and constraints
 Understand system operations
Output: Requirement specification document

8. Conceptual Design
Conceptual design creates a high-level model of the database using the Entity–Relationship (E–R) model.
It focuses on:
 Entities
 Attributes
 Relationships

PART C: ENTITY–RELATIONSHIP (E–R) MODEL

9. Entity–Relationship Model
The E–R model is a conceptual data model used to describe the structure of a database.
10. Components of E–R Model
10.1 Entity
An entity is a real-world object that can be uniquely identified.
Examples:
 Student
 Course

10.2 Attributes
Attributes describe the properties of an entity.
Types of attributes:
 Simple and Composite
 Single-valued and Multi-valued
 Derived attributes

10.3 Relationship
A relationship represents an association between entities.
Example:
 Student enrolls in Course

10.4 Cardinality Constraints

 One-to-One (1:1)
 One-to-Many (1:N)
 Many-to-Many (M:N)

10.5 Participation Constraints

 Total participation
 Partial participation

11. Logical Design

Logical design converts the E–R model into a relational schema.
It involves:
 Creating tables
 Defining primary keys
 Defining foreign keys

12. Physical Design

Physical design specifies:
 Storage structures
 Indexes
 File organization
It focuses on performance and storage efficiency.

13. Normalization
Normalization is the process of organizing data to reduce redundancy.
Normal Forms:
 1NF – Atomic values
 2NF – No partial dependency
 3NF – No transitive dependency

14. Advantages of Proper Database Design

 Reduced redundancy
 Improved data integrity
 Easy maintenance
 Efficient querying

15. Important Exam Points

 DRC uses domain variables
 Database design follows systematic stages
 E–R model is used in conceptual design
 Normalization improves database quality

Entity–Relationship (E–R) Model

1. Introduction to the E–R Model
The Entity–Relationship (E–R) Model is a conceptual data model used in database design. It provides a high-level view
of data and shows how data is related within a system.
The E–R model is mainly used during the conceptual design phase of the database design process. It helps database
designers understand data requirements clearly before converting them into tables.
2. Purpose of the E–R Model
The main purposes of the E–R model are:
 To represent real-world objects and relationships
 To simplify database design
 To reduce errors during database development
 To act as a bridge between user requirements and database implementation

3. Basic Concepts of the E–R Model

3.1 Entity
An entity is a real-world object that can be uniquely identified and about which data is stored.
Examples:
 Student
 Employee
 Course
Entities are represented using rectangles in E–R diagrams.

3.2 Entity Set

An entity set is a collection of similar entities.
Example:
 All students in a college form a Student entity set

4. Attributes
Attributes describe the properties or characteristics of an entity.
Types of Attributes:
4.1 Simple (Atomic) Attributes
 Cannot be divided further
 Example: Age, Roll_No
4.2 Composite Attributes
 Can be divided into sub-parts
 Example: Name (First_Name, Last_Name)
4.3 Single-Valued Attributes
 Have only one value
 Example: Date_of_Birth
4.4 Multi-Valued Attributes
 Can have multiple values
 Example: Phone_Number
4.5 Derived Attributes
 Derived from other attributes
 Example: Age (derived from Date_of_Birth)
Attributes are represented using ovals in E–R diagrams.

5. Key Attributes
A key attribute uniquely identifies an entity.
Types of Keys:
 Primary Key: Uniquely identifies each entity
 Candidate Key: Possible keys from which primary key is chosen
 Composite Key: Combination of two or more attributes
Key attributes are underlined in E–R diagrams.

6. Relationships
A relationship represents an association between two or more entities.
Example:
 Student enrolls in Course
Relationships are represented using diamonds in E–R diagrams.

7. Degree of a Relationship
The degree indicates the number of entity sets involved in a relationship.
 Unary (Recursive): One entity set
 Binary: Two entity sets
 Ternary: Three entity sets

8. Cardinality Constraints
Cardinality specifies the number of entities that can participate in a relationship.
Types:
 One-to-One (1:1)
 One-to-Many (1:N)
 Many-to-One (N:1)
 Many-to-Many (M:N)
9. Participation Constraints
Participation specifies whether an entity must participate in a relationship.
Types:
 Total Participation: Every entity participates
 Partial Participation: Some entities may not participate

10. Weak Entity

A weak entity cannot be uniquely identified by its own attributes.
Characteristics:
 Depends on a strong entity
 Has a partial key
Example:
 Dependent (depends on Employee)

11. Generalization and Specialization

Generalization
 Bottom-up approach
 Combines similar entities into a higher-level entity
Specialization
 Top-down approach
 Divides an entity into sub-entities

12. Aggregation
Aggregation is used when a relationship itself needs to be treated as an entity.
It is useful for representing complex relationships.

13. Advantages of the E–R Model

 Easy to understand
 Visual representation of data
 Reduces design complexity
 Improves communication with users

14. Limitations of the E–R Model

 Cannot represent complex constraints easily
 No direct support for behavioral aspects

15. Important Exam Points

 E–R model is used in conceptual design
 Entities are represented by rectangles
 Attributes are represented by ovals
 Relationships are represented by diamonds
 Cardinality and participation are important constraints

Reduction to Relational Schemas – Entity

1. Introduction
Reduction to Relational Schemas is the process of converting an Entity–Relationship (E–R) model into a set of
relational tables (schemas). This step is part of the logical database design phase.
The main goal is to represent entities, attributes, and relationships of the E–R model in the form of relations that can be
implemented in a relational DBMS.

2. Mapping E–R Model to Relational Model

When reducing an E–R model to relational schemas, we apply a set of mapping rules. These rules ensure that:
 All data is preserved
 Constraints are maintained
 The design is free from redundancy

3. Reduction of Strong Entity Sets

Rule 1: Mapping a Strong Entity Set
For each strong entity set, create a separate relation.
 All simple attributes of the entity become attributes of the relation
 The primary key of the entity becomes the primary key of the relation
Example:
Entity: Student
 Attributes: Roll_No, Name, Dept, Age
Relational Schema:
STUDENT(Roll_No, Name, Dept, Age)
Primary Key: Roll_No
4. Mapping of Composite Attributes
Rule 2: Composite Attributes
 Composite attributes are broken down into simple attributes
 Only simple attributes are included in the relation
Example:
Entity: Employee
 Attribute: Name (First_Name, Last_Name)
Relational Schema:
EMPLOYEE(Emp_ID, First_Name, Last_Name)

5. Mapping of Multi-Valued Attributes

Rule 3: Multi-Valued Attributes
For each multi-valued attribute, create a new relation.
 Include the primary key of the original entity
 Include the multi-valued attribute
Example:
Entity: Student
 Multi-valued attribute: Phone_No
Relational Schemas:
STUDENT(Roll_No, Name, Dept)
STUDENT_PHONE(Roll_No, Phone_No)
Primary Key of STUDENT_PHONE: (Roll_No, Phone_No)

6. Mapping of Derived Attributes

Rule 4: Derived Attributes
 Derived attributes are usually not stored in relations
 They can be computed when required
Example:
Age derived from Date_of_Birth is not stored explicitly.

7. Mapping of Weak Entity Sets

Rule 5: Weak Entity Set
For each weak entity, create a separate relation.
 Include all attributes of the weak entity
 Include the primary key of the strong (owner) entity as a foreign key
 The primary key is a combination of owner key and partial key
Example:
Strong Entity: Employee (Emp_ID)
Weak Entity: Dependent (Dep_Name, Age)
Relational Schema:
EMPLOYEE(Emp_ID, Name)
DEPENDENT(Emp_ID, Dep_Name, Age)
Primary Key of DEPENDENT: (Emp_ID, Dep_Name)

8. Mapping of Entity Sets with Key Constraints

If an entity has:
 Single primary key → mapped directly
 Composite primary key → all key attributes are included
Example:
COURSE(Course_ID, Semester, Title)
Primary Key: (Course_ID, Semester)

9. Advantages of Proper Entity Reduction

 Clear table structure
 Preserves entity constraints
 Reduces redundancy
 Easy implementation in DBMS

10. Important Exam Points

 Each strong entity → one relation
 Composite attributes are decomposed
 Multi-valued attributes require separate relations
 Weak entity uses owner key + partial key
 Derived attributes are not stored

11. Short Notes (for 2–5 Marks)

 Reduction to relational schema is part of logical design
  Primary keys uniquely identify tuples
 Foreign keys maintain relationships

Relationship Design Issues

1. Introduction
Relationship Design Issues arise while converting an Entity–Relationship (E–R) model into a relational database
schema. Proper handling of relationships is essential to avoid data redundancy, inconsistency, and update anomalies.
These issues are mainly considered during the logical database design phase.

2. Relationship Sets in E–R Model

A relationship set represents an association among entities.
Example:
 Student — enrolls — Course
While designing relationships, several important decisions must be taken to represent them correctly in relational form.

3. Key Relationship Design Issues

3.1 Choosing Primary Keys for Relationship Relations
 For binary relationships, the primary key depends on cardinality.
 In many-to-many (M:N) relationships, a composite primary key is used.
Example:
ENROLL(Student_ID, Course_ID, Grade)
Primary Key: (Student_ID, Course_ID)

3.2 Redundancy in Relationship Design

Redundancy occurs when the same data is stored in multiple places.
Causes:
 Incorrect relationship mapping
 Poor choice of attributes in relationships
Effects:
 Wastage of storage
 Update anomalies

3.3 Handling Relationship Attributes

Some relationships have their own attributes.
Example:
 Relationship: Enroll
 Attribute: Grade
Such attributes should be stored with the relationship relation, not in entity tables.

4. Cardinality Constraints and Design Issues

4.1 One-to-One (1:1) Relationships
Design choices:
 Merge both entities into one table
 Add foreign key to either entity
Decision depends on:
 Total participation
 Frequency of access

4.2 One-to-Many (1:N) Relationships

 Add the primary key of the one-side as a foreign key in the many-side entity
Example:
DEPARTMENT(Dept_ID, Dept_Name)
EMPLOYEE(Emp_ID, Name, Dept_ID)

4.3 Many-to-Many (M:N) Relationships

 Always create a separate relation
 Include primary keys of both entities
Example:
ENROLL(Student_ID, Course_ID)

5. Participation Constraints
Total Participation
 Every entity must participate in the relationship
 Foreign key should be NOT NULL
Partial Participation
 Some entities may not participate
 Foreign key can be NULL
6. Relationship vs Entity Design Issue
Sometimes, it is unclear whether to model something as:
 An entity OR
 A relationship
Guideline:
 If it has its own attributes and identity → Entity
 If it only links entities → Relationship

7. Ternary Relationship Design Issues

 Ternary relationships involve three entities
 Cannot always be replaced by binary relationships
Incorrect decomposition may cause loss of meaning.

8. Weak Entity and Relationship Issues

 Weak entity depends on a strong entity
 Identifying relationship must be preserved
 Owner key becomes part of weak entity primary key

9. Integrity Constraints in Relationship Design

Important constraints:
 Entity integrity
 Referential integrity
 Cardinality constraints
Proper enforcement avoids invalid data.

10. Common Design Mistakes

 Ignoring cardinality constraints
 Improper primary key selection
 Storing relationship attributes in entity tables
 Redundant relations

11. Advantages of Proper Relationship Design

 Reduces redundancy
 Ensures data consistency
 Improves query performance
 Easy maintenance

12. Important Exam Points

 M:N relationships require separate tables
 Relationship attributes belong to relationship tables
 Foreign keys enforce relationships
 Cardinality affects schema design

Extended Entity–Relationship (E–R) Model – Extended E–R Features

1. Introduction to Extended E–R Model
The Extended Entity–Relationship (EER) Model is an enhancement of the basic E–R model. It is used to represent more
complex data relationships and constraints found in real-world applications.
EER model provides additional features such as specialization, generalization, inheritance, aggregation, and categories,
which help in better conceptual database design.

2. Need for Extended E–R Features

The basic E–R model is not sufficient to represent:
 Hierarchical relationships
 Inheritance of attributes
 Complex constraints
Extended E–R features overcome these limitations and improve expressiveness and clarity.

3. Specialization
Definition:
Specialization is a top-down approach in which an entity set is divided into smaller sub-entity sets based on distinguishing
characteristics.
Characteristics:
 Subclasses inherit attributes of the superclass
 Each subclass has its own specific attributes
Example:
Entity: Employee
Sub-entities: Teaching_Staff, Non_Teaching_Staff
4. Generalization
Definition:
Generalization is a bottom-up approach where multiple entity sets with common features are combined into a higher-level
entity set.
Example:
Entities: Car, Truck
Generalized entity: Vehicle

5. Inheritance
Definition:
Inheritance allows subclasses to acquire attributes and relationships of the superclass.
Advantages:
 Reduces redundancy
 Improves data consistency

6. Constraints on Specialization and Generalization

6.1 Disjoint Constraint
 An entity can belong to only one subclass
6.2 Overlapping Constraint
 An entity can belong to multiple subclasses

6.3 Completeness Constraint

Total Specialization
 Every entity must belong to at least one subclass
Partial Specialization
 Some entities may not belong to any subclass

7. Aggregation
Definition:
Aggregation is used when a relationship itself needs to be treated as an entity.
Purpose:
 Represents complex relationships
 Avoids ambiguity
Example:
Employee works on Project supervised by Manager

8. Categories (Union Types)

Definition:
A category represents a subclass whose entities are drawn from multiple entity sets.
Example:
Owner = Student ∪ Employee

9. Attribute and Relationship Inheritance

 Subclasses inherit all attributes of superclasses
 Additional attributes can be defined in subclasses

10. Advantages of Extended E–R Model

 Models real-world scenarios accurately
 Supports inheritance
 Reduces redundancy
 Improves database clarity

11. Limitations of Extended E–R Model

 More complex than basic E–R model
 Difficult to implement directly

12. Important Exam Points

 Specialization is top-down
 Generalization is bottom-up
 Aggregation treats relationship as entity
 Categories combine multiple entity sets
 Inheritance reduces redundancy

13. Short Notes (for 2–5 Marks)

 EER extends basic E–R model
 Supports complex constraints
 Used in advanced conceptual design
Alternative Notations for Modeling Data
1. Introduction
In database design, data can be modeled using different notations and models apart from the traditional Entity–
Relationship (E–R) model. These alternative notations help represent data structures, constraints, and relationships in various
ways depending on system requirements.
They are mainly used during the conceptual and logical design phases of database development.

2. Need for Alternative Notations

Alternative notations are required because:
 Different systems need different levels of abstraction
 Some notations represent constraints more clearly
 They improve communication between designers and users
 Complex real-world data can be modeled better

3. Unified Modeling Language (UML)

3.1 UML Class Diagram
UML (Unified Modeling Language) is a popular notation used for object-oriented data modeling.
In database design, UML Class Diagrams are used as an alternative to E–R diagrams.
Components of UML Class Diagram:
 Class → Similar to entity
 Attributes → Properties of the class
 Operations → Methods (optional in DB design)
Example:
 Class: Student
 Attributes: student_id, name, dept

3.2 Relationships in UML

UML Relationship Meaning

Association Relationship between classes
Aggregation Whole–part relationship
Composition Strong aggregation
Inheritance IS-A relationship

4. Object-Oriented Data Model

The Object-Oriented Data Model represents data as objects, similar to programming languages like Java and C++.
Features:
 Objects contain data and methods
 Supports inheritance
 Supports encapsulation
This model is useful for complex applications such as multimedia and CAD systems.

5. Semantic Data Model

Semantic data models focus on meaning (semantics) of data rather than structure.
Characteristics:
 Captures real-world meaning
 Supports specialization and generalization
 Improves clarity of design
Example: Enhanced E–R (EER) model

6. Functional Data Model

The Functional Data Model represents data using functions.
Characteristics:
 Data is viewed as functions mapping values
 Supports complex relationships
Used mainly in theoretical database research.

7. Network Data Model

The Network Data Model represents data as records connected by links.
Characteristics:
 Supports many-to-many relationships
 Uses pointers
 Complex structure
Example systems: IDS, IDMS
8. Hierarchical Data Model
The Hierarchical Data Model organizes data in a tree structure.
Characteristics:
 One-to-many relationships
 Parent–child structure
 Simple but rigid
Example: File systems, XML

9. Comparison with E–R Model

Model Key Feature

E–R Model Simple and visual
UML Object-oriented
Hierarchical Tree structure
Network Graph structure
Object-Oriented Data + behavior

10. Advantages of Alternative Notations

 Flexible data representation
 Better modeling of complex systems
 Improves communication

11. Limitations
 Some notations are complex
 Difficult to convert directly into relational schema
 Requires skilled designers

12. Important Exam Points

 UML is an alternative to E–R model
 Object-oriented model supports inheritance
 Hierarchical model uses tree structure
 Network model supports many-to-many relationships

Other Aspects of Database Design

1. Introduction
Apart from conceptual modeling (E–R model) and logical design (relational schemas), database design also involves other
important aspects that ensure the database is efficient, reliable, secure, and easy to maintain. These aspects play a crucial
role in real-world database applications.

2. Data Integrity Constraints

Data integrity ensures that the data stored in the database is accurate, consistent, and reliable.
Types of Integrity Constraints:
2.1 Domain Integrity
 Attribute values must be from a valid domain
 Example: Age must be a positive integer
2.2 Entity Integrity
 Primary key values cannot be NULL
 Ensures each record is uniquely identifiable
2.3 Referential Integrity
 Foreign key values must match primary key values in another table
 Prevents orphan records

3. Normalization and Denormalization

Normalization
Normalization is the process of organizing data to:
 Reduce redundancy
 Avoid update anomalies
Normal Forms:
 1NF: Atomic values
 2NF: No partial dependency
 3NF: No transitive dependency
Denormalization
 Controlled introduction of redundancy
  Used to improve query performance

4. Performance Considerations
Database performance depends on:
 Table structure
 Indexing
 Query optimization
Indexing
 Improves data retrieval speed
 Uses additional storage
Query Optimization
 Efficient SQL queries
 Proper join conditions

5. Security and Authorization

Database security ensures that data is protected from unauthorized access.
Security Measures:
 User authentication
 Authorization (GRANT, REVOKE)
 Encryption

6. Backup and Recovery

Backup and recovery protect data from:
 Hardware failure
 Software failure
 Human errors
Types of Backup:
 Full backup
 Incremental backup
 Differential backup

7. Concurrency Control
Concurrency control ensures that multiple users can access the database simultaneously without conflicts.
Problems handled:
 Lost update
 Dirty read
 Uncommitted data
Techniques:
 Locking
 Timestamp ordering

8. Transaction Management
A transaction is a sequence of operations treated as a single unit.
ACID Properties:
 Atomicity
 Consistency
 Isolation
 Durability

9. Scalability and Maintenance

Scalability
 Ability of the database to handle growing data and users
Maintenance
 Regular updates
 Performance tuning
 Data cleanup

10. Documentation and Standards

 Proper documentation helps future maintenance
 Naming conventions improve readability
 Standards ensure consistency

11. Advantages of Considering Other Design Aspects

 Improves reliability
 Enhances performance
 Ensures data security
 Supports long-term usage
12. Important Exam Points
 Database design is not limited to schema creation
 Integrity constraints ensure correctness
 Indexing improves performance
 Security and recovery are essential

What is Normalization? (with example)

Normalization is a database design process used to organize data in tables so that data redundancy is
reduced and data consistency is maintained.
In simple words, it means breaking a big table into smaller, well-structured tables and linking them
properly.

Why Normalization is Needed

Normalization helps to:
 Avoid duplicate data
 Prevent update, insert, and delete anomalies
 Improve data integrity
 Make the database easy to maintain
Example (Before Normalization)
STUDENT table (Unnormalized)
RollNo Name Dept Subjects
101 Arun CSE DBMS, OS
102 Bala IT DBMS, CN
Problems:
 Multiple values in one column (Subjects)
 Difficult to update or search
 Data redundancy

After Normalization
First Normal Form (1NF)
Rule: Each field should have atomic (single) values
RollNo Name Dept Subject
101 Arun CSE DBMS
101 Arun CSE OS
102 Bala IT DBMS
102 Bala IT CN
Atomic values achieved
Second Normal Form (2NF)
Rule:
 Table must be in 1NF
 No partial dependency (non-key attributes should depend on full key)
Split the table:
STUDENT
RollNo Name Dept
101 Arun CSE
102 Bala IT

ENROLLMENT

RollNo Subject
101 DBMS
101 OS
102 DBMS
102 CN
Partial dependency removed
Third Normal Form (3NF)
Rule:
 Table must be in 2NF
 No transitive dependency
If DeptName depends on DeptID, separate it:
DEPARTMENT
DeptID DeptName
CSE Computer Science
IT Information Tech
Fully normalized design

Definition for Exams (Short)

Normalization is the process of organizing data in a database to reduce redundancy and
eliminate anomalies by dividing tables into smaller, well-structured tables.

Key Points (Exam Ready)

 Normalization improves data consistency
 1NF → Atomic values
 2NF → No partial dependency
 3NF → No transitive dependency