UNIT-1

Database Management System (DBMS)

Architecture

1. Introduction to DBMS Architecture

A Database Management System (DBMS) is designed to manage databases

effectively. The architecture of a DBMS defines how data is organized,
stored, and accessed in a system. It plays a crucial role in ensuring data
integrity, security, and efficient data retrieval.

Key Points:

 A DBMS architecture refers to the structure and components of a

database system.
 It defines how data is stored, manipulated, and retrieved.
 A well-defined architecture ensures data independence, efficiency,
and scalability.

2. Components of DBMS Architecture

Before diving into levels of DBMS architecture, let’s look at the major
components of a DBMS:

1. Database Engine:
o Core component responsible for storing and retrieving data.
o Handles data storage, indexing, and query processing.
2. Query Processor:
o Interprets and executes SQL queries.
o Optimizes queries for performance.
3. Transaction Manager:
o Ensures that all transactions are completed correctly.
o Manages ACID properties (Atomicity, Consistency, Isolation,
Durability).
4. Storage Manager:
o Manages how data is stored on physical devices.
o Includes disk management, file management, and indexing.
5. Data Dictionary:
o Contains metadata (data about the data).
o Defines tables, columns, relationships, and constraints.
3. Deep Dive into 3-Tier Architecture
3-Tier Architecture is a widely adopted architectural model in software
development, particularly for database-driven applications. This architecture
separates the application into three distinct layers, each with specific
responsibilities, allowing for better scalability, maintainability, and flexibility.

The 3-Tier Architecture typically includes the following layers:

1. Presentation Layer (Client Tier)

2. Application Layer (Business Logic Tier)
3. Data Layer (Database Tier)

1. Presentation Layer (Client Tier)

Description:

 The Presentation Layer is responsible for interfacing with the end-

user. It's the topmost layer of the application and includes everything
the user interacts with directly.
 This layer’s primary responsibility is to collect data from the user and
display information to the user.

Responsibilities:

 Display the data or information to the user in a user-friendly manner.

 Capture user input (e.g., form submissions, search queries).
 Send requests to the Application Layer for processing.
 Handle user interactions such as clicks, form submissions, or
navigation.

Technologies Involved:

 Web applications: HTML, CSS, JavaScript, React, Angular, or Vue.js.

 Mobile applications: Swift (iOS), Kotlin (Android), Flutter.
 Desktop applications: JavaFX, WPF, Electron, etc.

Example:

 In a web application, the browser acts as the presentation layer. The

user interacts with a web page, and the browser sends requests (like
HTTP requests) to the Application Layer.
2. Application Layer (Business Logic Layer)

Description:

 The Application Layer contains the business logic of the

application. It's the middle tier of the architecture, sitting between the
Presentation Layer and the Data Layer.
 This layer is responsible for processing the data received from the
Presentation Layer, performing any necessary calculations or
operations, and then interacting with the Data Layer to retrieve or
store data.

Responsibilities:

 Processing business logic: This is where the core application logic

resides, such as processing user inputs, making decisions, and
managing workflows.
 Validation: Ensures the data meets the required format or constraints
before interacting with the database.
 Data Manipulation: Makes decisions about which data is needed
from the database and how to manipulate or transform that data.
 Coordination: Coordinates actions between the Presentation and
Data Layers. For example, retrieving data from the database or
sending data for storage.

Technologies Involved:

 Backend programming languages: Java, C#, Python, Ruby, Node.js,

PHP, Go, etc.
 Application frameworks: Spring (Java), Django (Python), Express
(Node.js), Laravel (PHP), .NET (C#), etc.

Example:

 In a web application, when the user submits a form, the data goes to
the Application Layer, where business logic is executed. For example,
checking if a username already exists before saving the data into the
database.

3. Data Layer (Database Tier)

Description:
  The Data Layer is the lowest tier of the architecture. This layer
handles the storage and retrieval of data, typically by interacting
with a DBMS (Database Management System).
 The Data Layer is responsible for storing, managing, and retrieving
data, often in the form of databases or other data storage systems
(like NoSQL databases or file systems).

Responsibilities:

 Data Management: Provides a centralized place to store data and

allows other layers to retrieve or update it as needed.
 Data Security: Ensures that sensitive data is stored securely and that
access is restricted to authorized users.
 Database Queries: Sends SQL queries or other types of requests to
the database to fetch or update data based on the Application Layer's
request.

Technologies Involved:

 DBMS: MySQL, PostgreSQL, Oracle, SQL Server (relational); MongoDB,

Cassandra, Redis (NoSQL).
 SQL Queries: Structured Query Language (SQL) is typically used to
retrieve or update data.
 ORM: Object-Relational Mapping frameworks like Hibernate (Java),
Sequelize (Node.js), Django ORM (Python), etc.

Example:

 When the Application Layer sends a request for user data (e.g., a query
to find user details), the Data Layer interacts with the database to
fetch this information and returns it to the Application Layer.

How 3-Tier Architecture Works (Flow)

Let’s break down the flow between the three tiers with an example scenario:

1. User Request (Presentation Layer):

o The user interacts with the presentation layer (a web page or
mobile app).
o Example: The user fills out a form to create a new account.
2. Application Layer Processing:
o The form data is sent to the application layer (server-side).
o The application processes the data (e.g., checks if the username
is unique, validates the password format).
o If the data is valid, it proceeds to the next step; if not, it returns
an error to the presentation layer.
 3. Data Layer Interaction:
o The Application Layer sends a query to the Data Layer
(Database).
o Example: An SQL query is sent to insert the user details into the
database.
o The Data Layer stores the data and sends a confirmation
message back to the Application Layer.
4. Return Response to User (Presentation Layer):
o The Application Layer sends a success message (or error) back
to the Presentation Layer.
o The Presentation Layer then updates the user interface (e.g.,
shows a success message or redirects the user to the login
page).

Example: 3-Tier Architecture in a Web Application

Here’s a practical example of a 3-Tier Architecture for an Online Shopping

Website:

 Presentation Layer:
o The user interacts with a web page where they can browse
products, add them to the shopping cart, and checkout. The
front-end might use HTML, CSS, and JavaScript to render the UI.
 Application Layer:
o The server-side application (running on a server, possibly using
Node.js, Java, or Python) receives requests from the user
interface. It handles user authentication, product management,
order processing, etc.
 Data Layer:
o The database (e.g., MySQL or MongoDB) stores product details,
user profiles, order history, etc. When the Application Layer
requests data (like retrieving available products), it interacts with
the Data Layer and fetches the necessary data.

Advantages of 3-Tier Architecture

1. Separation of Concerns:
o Each layer has a clear and distinct responsibility. This separation
allows for better modularity and maintainability.
2. Scalability:
o Each layer can be scaled independently. For instance, if the
application logic layer is overloaded, you can scale it without
affecting the database or presentation layers.
3. Maintainability:
 oIf there’s a bug or a need for an update, you can update one
layer without having to modify the other layers.
o Changes in the database or business logic do not affect the user
interface and vice versa.
4. Reusability:
o The Application Layer and Data Layer can be reused across
different front-end platforms (e.g., web, mobile, or desktop
applications).
5. Flexibility:
o New presentation layers (e.g., a mobile app) can be added
without changing the core application or database logic.
o Data can be stored in various formats (SQL, NoSQL) and
accessed in a unified way through the business logic.

Introduction to Database Approach vs. Traditional

File Accessing Approach
Before diving into the differences, it's important to define both approaches:

 Traditional File Accessing Approach: In the traditional system,

data is stored in flat files (such as text files, CSV files, or other simple
formats) and the application programs directly access and manipulate
these files. There’s no structured management of data, and each
program manages its own files.
 Database Approach: The database approach involves using a DBMS
(Database Management System) to store, manage, and query data
in a structured and efficient way. A DBMS provides a central repository
for data, allowing multiple users and applications to access and
manipulate data in a more organized manner.
1. Structure of Data

Traditional File Accessing Approach:

 Flat Files: Data is typically stored in a sequence of records within a

file. These records can be of varying formats, and there is often no
defined schema or relationship between them.
 Data Organization: There is no standardized structure, and each
application is responsible for defining how to store and retrieve data.
Files are typically organized in a linear or sequential manner, with little
flexibility in querying or processing data.

Example: A file where each record represents customer information, like

name, address, and phone number, but each application accessing this file
might store or process it differently.

Database Approach:

 Tables: In a DBMS, data is stored in structured tables that are

organized into rows and columns. Each table typically has a defined
schema (structure), which enforces consistency and defines the types
of data stored (e.g., integer, string, date).
 Data Relationships: The database allows for relationships between
tables using primary keys and foreign keys. For instance, a
customer table can be related to an order table by the customer ID.

Example: A relational database where data is stored in tables like Customers,

Orders, and Products. Relationships are established between these tables using
keys, and you can easily query data from multiple tables with JOIN
operations.

2. Data Redundancy

Traditional File Accessing Approach:

 Redundancy: In the traditional approach, data redundancy is

common. Multiple applications or systems might store the same data
(such as customer details) in different files. This leads to duplication,
wasted space, and potential inconsistencies when updating data across
multiple locations.
  Lack of Centralization: Each file is independent, meaning there's no
centralized system to manage the data across the organization. This
can lead to data inconsistency and integrity issues.

Example: The customer information might be stored in multiple files for

different departments (sales, marketing, customer support), and if one
department updates customer info, it may not be reflected in others.

Database Approach:

 Data Integrity and Centralization: The DBMS centralizes data

storage, reducing redundancy. Through normalization (organizing data
into separate tables to minimize redundancy), a DBMS minimizes data
duplication.
 Consistency: When data is updated in one place, it is automatically
reflected throughout the database due to the centralization. This
ensures consistency and integrity of data.

Example: The customer information is stored in a single Customers table, and

any update (like a change of address) will be automatically consistent across
all parts of the database.

3. Data Integrity and Security

Traditional File Accessing Approach:

 Data Integrity: Ensuring data integrity is difficult in a traditional file-

based system. Since the application itself manages the file, there’s a
higher risk of human error, data corruption, and inconsistency.
 Lack of Security: Files are often not protected with robust access
control mechanisms. Different users or applications may have
unrestricted access to all data, increasing the risk of unauthorized
access.

Example: A text file containing customer data could be edited or overwritten

by anyone with access to the file, leading to data corruption or loss.

Database Approach:

 Data Integrity: DBMS systems use constraints (e.g., primary keys,

foreign keys, and check constraints) to enforce data integrity. This
prevents the entry of invalid or inconsistent data and ensures that the
relationships between tables are correctly maintained.
  Security: DBMS systems have user roles and access control
mechanisms (e.g., read-only, read-write permissions), ensuring that
only authorized users can access or modify specific parts of the
database.

Example: A DBMS can ensure that a customer’s email is unique (via a

unique constraint) and prevent inserting duplicate data. Users can have
different levels of access, ensuring that sensitive customer information is
only available to authorized personnel.

4. Querying and Data Retrieval

Traditional File Accessing Approach:

 Manual Processing: Data retrieval is typically done manually using

custom-written application code or scripts. There is no structured
querying mechanism, and retrieving specific data can be slow and
inefficient.
 Limited Flexibility: Searching and extracting information from files
may require the entire file to be scanned (sequential access), which is
inefficient for large datasets.

Example: To retrieve a customer’s order history from a text file, you might
need to manually search through the file or use a program to filter data,
which is slow for large files.

Database Approach:

 SQL Querying: DBMS systems provide powerful query languages like

SQL (Structured Query Language) to retrieve data. SQL allows for
complex queries, filtering, aggregation, sorting, and joining of multiple
tables with ease.
 Efficient Retrieval: DBMS systems use indexing and optimized
query execution plans to make data retrieval much faster, even with
large datasets.

Example: A SQL query like SELECT * FROM Orders WHERE CustomerID = 12345 can
quickly retrieve the order history for a specific customer without scanning
the entire dataset.

5. Data Isolation
Traditional File Accessing Approach:

 Isolated Data: In traditional systems, each application or department

may maintain its own file. These files are not usually connected or
share data in a structured way. This isolation makes it difficult to share
data across departments or applications.
 Complex Integration: When integration is needed, it often requires
manual effort and custom solutions to synchronize data between
different files or applications.

Example: The sales department may have a separate file for tracking
orders, while the accounting department uses a different file for billing. If
they need to share information, it can be cumbersome and error-prone.

Database Approach:

 Data Sharing and Integration: A DBMS enables centralized data

storage, making it easy for multiple applications to access and share
data. The use of relational models, normalization, and APIs makes
integration simpler and more reliable.
 Cross-Department Access: Different departments or applications
can use the same database and access the necessary data in a
controlled and structured way.

Example: The sales department can access customer orders in real time,
while the accounting department can retrieve payment information from the
same database without duplication or synchronization issues.

6. Performance

Traditional File Accessing Approach:

 Slower Performance: As the size of the file grows, data retrieval and
processing become slower. Files are often accessed sequentially, which
leads to inefficiencies, especially for large datasets.
 No Indexing: In traditional systems, there’s no automatic indexing
mechanism. Searching through large files without indexes can be slow.

Database Approach:

 Faster Performance: DBMS systems are optimized for speed with

indexing, caching, and query optimization techniques. Data can be
accessed using indexes, which makes retrieval faster, even with large
amounts of data.
  Optimized Query Execution: DBMS engines automatically generate
optimized execution plans for queries, making operations like filtering,
sorting, and joining much more efficient.

Summary: Key Differences

Traditional File
Aspect Database Approach
Accessing Approach
Data Structured tables with defined
Flat, unstructured files
Structure schema
Data High, data is often Low, data is centralized and
Redundancy duplicated across files normalized
Data Integrity Limited or no data integrity Strong integrity constraints,
& Security constraints, weak security robust security
Slow, manual or custom Fast, SQL queries with indexing
Data Retrieval
methods and optimization
Centralized, integrated data
Data is isolated within
Data Isolation accessible by multiple
individual files
applications
Slower, no optimization or Faster, with indexing and query
Performance
indexing optimization

Data Models
Definition:

A data model defines the logical structure of the data and the relationships
between the data elements in the database. It determines how data is
stored, accessed, and manipulated. It provides a framework to represent and
organize data in a way that aligns with the application’s requirements.

Types of Data Models:

There are several types of data models, each with different methods of
structuring and organizing data:
Network Data Model in Detail

The Network Data Model is a type of data model that organizes data in a
graph or network format, where data entities (records) are represented as
nodes (or vertices), and relationships between these entities are
represented as edges (or links). This model is an extension of the
Hierarchical Data Model, and it allows for more complex relationships
where an entity can have multiple parent and child records, unlike the
hierarchical model where each child has only one parent.

The Network Data Model was widely used in early database systems and
was formally represented by the CODASYL (Conference on Data Systems
Languages) standards in the 1960s.

1. Key Characteristics of the Network Data Model

a. Data Representation:

 Data is represented in the form of records (nodes) and sets (edges or

links).
 Records contain data fields (attributes) that describe an entity, while
sets represent the relationships between different records.
 Unlike the hierarchical model, where data is stored in a tree structure,
the network model supports many-to-many relationships, meaning
that a record can have multiple parent records and multiple child
records.

b. Data Structure:

 The Network Data Model is represented using a graph-like

structure.
 In this structure:
o Entities are represented as records (or nodes).
o Relationships between entities are represented as sets (or
edges), where a set connects one or more records to another
record.

c. Relationships:

 In the network model, relationships between records are

implemented through the set construct.
o A set is a pointer-based connection between records, where
one record (the owner) is related to other records (the
members).
 o Each set can have multiple owners and members, making it
more flexible than the hierarchical model.

d. Navigation:

 In the network model, data retrieval involves navigating the links or

pointers between records to access the data. This is similar to following
edges in a graph.
 Navigation is achieved through pointers that directly link one record
to another.

2. Components of the Network Data Model

a. Records:

 Records in the network model represent the entities in the database.

Each record contains a collection of fields (attributes) that store data
related to that entity.
 Example: A record for an Employee might contain fields such as
Emp_ID, Name, Salary, Dept_ID, etc.

b. Sets:

 A set represents a relationship between two or more records. It links

one record (the owner) to other records (the members).
 A set can connect any number of records, creating a flexible network of
relationships.
 Owner Record: The record that holds the set (often representing the
"one" side in a relationship).
 Member Records: The records connected to the owner through the
set (often representing the "many" side in a relationship).

c. Pointers:

 The pointer is the mechanism that links records together within a set.
A pointer points to the memory location of the related record.
 This creates a direct connection between records, allowing efficient
data retrieval.

d. Schema:

 The schema in the network model defines the types of records and the
relationships between them. It includes definitions for records,
attributes, sets, and pointers.
  The schema also defines the types of relationships (one-to-one, one-to-
many, many-to-many) between entities.

3. Example of the Network Data Model

Let's consider an example of a University Database with the following

entities:

 Student
 Course
 Professor

The relationships between these entities are:

 A Student can enroll in many Courses (many-to-many relationship).

 A Professor can teach many Courses (one-to-many relationship).

We can represent these relationships in the Network Data Model as

follows:

Entities (Records):

 Student record: Contains attributes like Student_ID, Student_Name, Major,

etc.
 Course record: Contains attributes like Course_ID, Course_Name, Professor_ID,
etc.
 Professor record: Contains attributes like Professor_ID, Professor_Name,
Department, etc.

Relationships (Sets):

 Student-Course Set: This set connects students to the courses they

are enrolled in. Each student can be a member of multiple courses.
 Professor-Course Set: This set connects professors to the courses
they teach. Each professor can be associated with multiple courses.

Visual Representation:

+------------+ +------------+ +------------+

| Student | | Course | | Professor |

+------------+ +------------+ +------------+

 | Student_ID |---------| Course_ID | | Professor_ID|

| Name | | Name | | Name |

+------------+ +------------+ +------------+

| | |

+--------------------+--------------------+

Enrolls Teaches

Navigating the Network:

 To retrieve all courses a student is enrolled in, you would follow the
Student-Course set using pointers.
 To find all students in a particular course, you would follow the
Course-Student set and navigate through the relationships.

When to Use the Network Data Model

The Network Data Model is useful in scenarios where:

 You need to represent complex relationships with multiple parent-

child entities.
 You are working with a legacy system that still uses network-based
structures.
 You require efficient direct navigation between records (via
pointers).
 The data relationships are highly interconnected and must be
accessed through specialized data retrieval paths.

Relational Data Model:

The Relational Data Model is one of the most widely used data models in
modern database systems. It was introduced by Edgar F. Codd in 1970 and
forms the foundation for relational database management systems
(RDBMS) such as MySQL, PostgreSQL, Oracle, and SQL Server.
In the relational model, data is organized into tables (also called
relations), which are collections of rows and columns. Each table represents
an entity type (such as Employee, Department, or Product), and each row (or
tuple) represents a single instance or record of that entity.

Let’s break down the components and features of the Relational Data
Model in detail.

1. Key Concepts in the Relational Model

a. Tables (Relations):

 A table is the primary structure used to store data in a relational

database. Each table consists of rows and columns.
o Rows (or tuples) represent individual records or instances of
the entity.
o Columns (or attributes) represent the properties or
characteristics of the entity.

Example: Consider an Employee table:

Emp_I Nam Ag Departm

D e e ent
1 John 29 HR
2 Alice 34 IT
3 Bob 40 Finance

 Each row represents a single employee, and each column represents a

specific attribute (e.g., Emp_ID, Name, Age, Department).

b. Fields (Attributes):

 A field or attribute is a column in the table that defines a particular

characteristic or property of the entity. Each field has a specific data
type (e.g., INTEGER, VARCHAR, DATE).

For example:

o Emp_ID: Integer (unique identifier for each employee).

o Name: String (employee’s name).
o Age: Integer (employee’s age).
o Department: String (employee’s department).
c. Tuples (Rows):

 A tuple is a single row in the table. Each tuple contains a specific set
of values corresponding to the attributes of the table.

In the Employee table example above, the row (1, John, 29, HR) represents
a single employee's record with specific values for Emp_ID, Name, Age,
and Department.

d. Primary Key:

 A primary key is a column (or a combination of columns) that

uniquely identifies each tuple (row) in a table. It ensures that no two
rows in the table have the same values for the primary key attributes.
 Example: In the Employee table, the Emp_ID column could be the
primary key because it uniquely identifies each employee.

e. Foreign Key:

 A foreign key is an attribute in one table that links to the primary

key of another table, establishing a relationship between the two
tables.
 It helps enforce referential integrity, ensuring that a value in the
foreign key column corresponds to a valid value in the referenced
table's primary key.

Example: In a Department table, there might be a Dept_ID column that

serves as the primary key, and the Employee table could include a
Dept_ID column as a foreign key, which links employees to their
respective departments.

2. Relational Model Properties

a. Data Independence:

 Data independence refers to the ability to change the schema of a

database (like adding new tables or columns) without affecting the
application programs that use the database.
o Logical data independence: The ability to change the logical
schema (structure of data) without affecting the external schema
(user view of the data).
o Physical data independence: The ability to change the
physical storage of data without affecting the logical schema.
b. Integrity Constraints:

 Integrity constraints are rules that ensure the accuracy and

reliability of the data. They are applied to data at the table level to
enforce correctness. Some of the most common integrity constraints
are:
o Domain constraints: Ensures that the values in a column are of
the correct data type.
o Entity integrity: Ensures that each tuple (row) has a unique
primary key value.
o Referential integrity: Ensures that foreign keys correctly
reference primary keys in related tables.
o Null constraints: Defines whether a column can accept NULL
values (i.e., undefined or missing values).

c. Normalization:

 Normalization is the process of organizing the attributes and tables

of a relational database to reduce redundancy and avoid undesirable
characteristics like insertion, update, and deletion anomalies.
o It involves dividing large tables into smaller, manageable ones
while maintaining relationships between them through primary
and foreign keys.
o Normal Forms (1NF, 2NF, 3NF, BCNF, etc.) are the various
levels of normalization that progressively eliminate redundancy
and improve data integrity.

5. Advantages of the Relational Model

1. Simplicity: The tabular structure of the relational model is intuitive

and easy to understand.
2. Data Integrity: The use of keys (primary, foreign) and constraints
helps ensure data consistency and accuracy.
3. Flexibility: The model allows for complex queries, supports a variety
of operations (e.g., joins, aggregations), and can handle a wide range
of data types.
4. Scalability: Relational databases are highly scalable and are designed
to handle large volumes of data efficiently.
5. Query Power: SQL provides a powerful and flexible query language
for data manipulation and retrieval.
6. Disadvantages of the Relational Model

1. Performance: For highly complex, large-scale data and applications

with frequent updates, relational databases might face performance
bottlenecks due to the rigid structure and extensive use of joins.
2. Complexity with Relationships: While relational databases are
flexible, managing highly interconnected data can become complex.
3. Not Always Suitable for Non-Relational Data: The relational model
is not always ideal for managing unstructured or semi-structured data
like documents, images, and other non-tabular data.

Object-Oriented Data Model

The Object-Oriented Data Model (OODM) is a data model that integrates

object-oriented programming (OOP) concepts into databases. It allows
the representation of real-world entities as objects (just like in object-
oriented programming), where each object can contain both data
(attributes) and methods (functions or operations).

In this model, data is represented as objects that encapsulate both the

state (data) and behavior (methods) of real-world entities. This model is
particularly useful for applications that require complex data
representations, such as multimedia databases, CAD systems, and
simulation-based systems.

The object-oriented data model combines concepts from relational

databases and object-oriented programming, which enables more
complex relationships and better management of data compared to
traditional relational models.

1. Key Concepts of the Object-Oriented Data Model

a. Object:

 An object represents a real-world entity and consists of two main

components:
o Attributes (or fields): The properties or data associated with the
object.
o Methods (or functions): The operations or behaviors that can be
performed on the object. Methods define what actions an object
can take.
 Example: In an object-oriented system for a university, a Student object
might have the following:

o Attributes: Student_ID, Name, Age, Address

o Methods: Enroll(), DropCourse(), ViewTranscript()

b. Class:

 A class is a blueprint or template for creating objects. It defines the

attributes and methods that an object of this class will have.
 In an object-oriented database, a class corresponds to an entity or
type in a traditional relational database.

Example: The Student class might define the structure and behavior
that all Student objects will follow.

c. Inheritance:

 Inheritance allows one class (the subclass) to inherit properties and

methods from another class (the superclass). This promotes
reusability and establishes a hierarchical relationship between classes.
 For example, a GraduateStudent class might inherit from a Student class
but add additional attributes such as ThesisTitle and Advisor.

d. Encapsulation:

 Encapsulation refers to bundling the data (attributes) and methods

(functions) that operate on the data into a single unit (object).
Additionally, it ensures that the internal workings of an object are
hidden from the outside world (accessed only through well-defined
methods).
 This ensures data integrity by preventing unauthorized access and
modification.

e. Polymorphism:

 Polymorphism allows an object to take on many forms, enabling the

use of a single interface for different data types. This means that
methods can behave differently based on the object calling them,
leading to flexibility and code reusability.

Example: A Display() method could be defined differently for a Student

object and a Course object, even though they share the same method
name.

f. Abstraction:
  Abstraction refers to the concept of hiding the complex
implementation details of an object while exposing only the essential
features. The user interacts with the object through its defined
interfaces (methods) without knowing how the internal workings are
carried out.

2. Object Identity

 In the object-oriented data model, each object has a unique object

identifier (OID). This identifier allows an object to be distinguished
from other objects, even if they have identical data values.
 The OID is used to uniquely identify each instance of an object within a
database, ensuring object identity remains consistent across the
database.

3. Relationships in Object-Oriented Data Model

a. Association:

 Association refers to the relationship between two or more objects. In

an object-oriented data model, associations can be represented by
attributes that refer to other objects.

Example: A Student object may be associated with a Course object via

an attribute EnrolledCourses, which might be a list of course objects the
student is enrolled in.

b. Aggregation:

 Aggregation is a special form of association where one object is part

of another object, but it still exists independently. It is a whole-part
relationship but without the ownership implied by composition.

Example: A Library object might contain multiple Book objects, but the
Books could exist independently of the Library.

c. Composition:

 Composition is a stronger form of aggregation where the lifetime of

the contained object depends on the container object. If the container
object is deleted, the contained objects are also deleted.
 Example: A Car object might have a Engine object, and if the Car object
is destroyed, the Engine object is also destroyed along with it.

4. Object-Oriented Database Management System (OODBMS)

An Object-Oriented Database Management System (OODBMS) is a

database system that supports the Object-Oriented Data Model. In an
OODBMS:

 Data is stored as objects.

 Relationships between objects can be represented using object
references.
 The system supports complex data types, inheritance, and methods for
objects.
 Queries can be made using object-oriented programming languages
such as C++ or Java.

5. Example: Object-Oriented Data Model for a University

System

Consider a system that manages data for a university. The following are the
classes, objects, and relationships in the object-oriented model.

Classes:

 Student: Represents a student.

o Attributes: Student_ID, Name, Age, Major
o Methods: EnrollInCourse(), DropCourse(), ViewGrades()
 Course: Represents a course.
o Attributes: Course_ID, Course_Name, Credits, Instructor
o Methods: RegisterStudent(), AssignGrades()
 Instructor: Represents an instructor.
o Attributes: Instructor_ID, Name, Department
o Methods: AssignCourse(), GradeStudent()

Relationships:

 A Student enrolls in many Courses. This could be modeled by an

association relationship between the Student and Course classes.
  A Course is taught by one Instructor. This is a composition relationship
because if the Course is removed, the Instructor related to it is removed
as well.

Objects:

 Student objects (e.g., student1, student2) will be created based on the

Student class, and each student object will have attributes like Student_ID
= 101, Name = "Alice", etc.
 Similarly, Course and Instructor objects will be created.

6. Advantages of the Object-Oriented Data Model

1. Complex Data Representation:

o The object-oriented model allows more complex data types to be
represented more naturally. For instance, objects can represent
multimedia data (images, audio, etc.), which would be difficult to
model in a relational database.
2. Data Encapsulation:
o By encapsulating data and behavior together, the model ensures
that the internal details of the object are hidden, providing better
data security and integrity.
3. Reusability:
o Inheritance allows for code reusability, making it easier to extend
existing systems without having to rewrite code. This is useful in
systems where there are many similar objects, like employees or
products.

ER Diagrams in DBMS: Entity Relationship Diagram Model

What is an ER Diagram?
An Entity Relationship Diagram (ER Diagram) pictorially explains the
relationship between entities to be stored in a database. Fundamentally, the
ER Diagram is a structural design of the database. It acts as a framework
created with specialized symbols for the purpose of defining the relationship
between the database entities. ER diagram is created based on three
principal components: entities, attributes, and relationships.
The following diagram showcases two entities - Student and Course, and
their relationship. The relationship described between student and course is
many-to-many, as a course can be opted by several students, and a student
can opt for more than one course. Student entity possesses attributes -
Stu_Id, Stu_Name & Stu_Age. The course entity has attributes such as Cou_ID
& Cou_Name.

Why Use ER Diagrams in DBMS?

 ER Diagram helps you conceptualize the database and lets you know
which fields need to be embedded for a particular entity

 ER Diagram gives a better understanding of the information to be

stored in a database

 It reduces complexity and allows database designers to build

databases quickly
  It helps to describe elements using Entity-Relationship models

 It allows users to get a preview of the logical structure of the

database

Symbols Used in ER Diagrams

 Rectangles: This Entity Relationship Diagram symbol represents

entity types

 Ellipses: This symbol represents attributes

 Diamonds: This symbol represents relationship types

 Lines: It links attributes to entity types and entity types with other
relationship types

 Primary key: Here, it underlines the attributes

 Double Ellipses: Represents multi-valued attributes

Entities

An entity can be either a living or non-living component.

It showcases an entity as a rectangle in an ER diagram.

For example, in a student study course, both the student and the course are
entities.
Weak Entity

An entity that makes reliance over another entity is called a weak entity

You showcase the weak entity as a double rectangle in ER Diagram.

In the example below, school is a strong entity because it has a primary key
attribute - school number. Unlike school, the classroom is a weak entity
because it does not have any primary key and the room number here acts
only as a discriminator.
Attribute

An attribute exhibits the properties of an entity.

You can illustrate an attribute with an oval shape in an ER diagram.

Key Attribute

Key attribute uniquely identifies an entity from an entity set.

It underlines the text of a key attribute.

For example: For a student entity, the roll number can uniquely identify a
student from a set of students.
Composite Attribute

An attribute that is composed of several other attributes is known as a

composite attribute.

An oval showcases the composite attribute, and the composite attribute oval
is further connected with other ovals.

Multivalued Attribute
Some attributes can possess over one value, those attributes are called
multivalued attributes.

The double oval shape is used to represent a multivalued attribute.

Derived Attribute

An attribute that can be derived from other attributes of the entity is known
as a derived attribute.

In the ER diagram, the dashed oval represents the derived attribute.

Relationship

The diamond shape showcases a relationship in the ER diagram.

It depicts the relationship between two entities.

In the example below, both the student and the course are entities, and
study is the relationship between them.

One-to-One Relationship
When a single element of an entity is associated with a single element of
another entity, it is called a one-to-one relationship.

For example, a student has only one identification card and an identification
card is given to one person.

One-to-Many Relationship

When a single element of an entity is associated with more than one element
of another entity, it is called a one-to-many relationship

For example, a customer can place many orders, but an order cannot be
placed by many customers.

Many-to-One Relationship

When more than one element of an entity is related to a single element of

another entity, then it is called a many-to-one relationship.
For example, students have to opt for a single course, but a course can have
many students.

Many-to-Many Relationship

When more than one element of an entity is associated with more than one
element of another entity, this is called a many-to-many relationship.

For example, you can assign an employee to many projects and a project
can have many employees.

How to Draw an ER Diagram?

Below are some important points to draw ER diagram:

 First, identify all the Entities. Embed all the entities in a rectangle
and label them properly.
  Identify relationships between entities and connect them using a
diamond in the middle, illustrating the relationship. Do not connect
relationships with each other.

 Connect attributes for entities and label them properly.

 Eradicate any redundant entities or relationships.

 Make sure your ER Diagram supports all the data provided to design
the database.

 Effectively use colors to highlight key areas in your diagrams.

Converting ER Diagrams to Tables in DBMS

Rule-01: Convert strong entity into a table
 A strong entity set with only simple attributes will directly convert into one
table.
 All Attributes of the entity set will be the attributes of the table.
 The primary key of the entity set will be the key attribute of the table.
Rule-02: Convert Composite Attributes of a strong
entity
All composite attributes convert as simple attributes in the table.

Schema: Student ( Roll_no, First_name, Last_name , House_no, Street , City )

Rule-03: Convert multivalued attributes of strong

entity into table.
 · Split the relations of multivalued strong entity set into two tables.
  · One table define all the simple attributes with the primary key and
other table define multi valued attributes with primary key.

Example

Rule-04: Convert Relationship Set into a Table

 A relationship set will convert into one table.
 Relationship table have primary keys of connected entities and own
attributes.
 Set of non-descriptive attributes will be the primary key.

Example
Schema: Works in ( Emp_no , Dept_id , since )
Rule-05: Convert Binary Relationships (Cardinality
Ratios) into the table.
The following four cases are possible

Case-01: Binary relationship with cardinality ratio m:n

Case-02: Binary relationship with cardinality ratio 1:n

Case-03: Binary relationship with cardinality ratio m:1

Case-04: Binary relationship with cardinality ratio 1:1

Case-01: For Binary Relationship With Cardinality Ratio m:n

Here, three tables will be required[1]1. A ( a1 , a2 ) 2. R ( a1 , b1) 3. B ( b1 , b2 )

Case-02: For Binary Relationship With Cardinality Ratio 1:n
Here, two tables will be required A ( a1, a2 ) 2. BR ( a1 , b1 , b2 )

NOTE- The combined table will be drawn for entity set B and relationship set R.

Case-03: For Binary Relationship With Cardinality Ratio m:1

Here, two tables will be required AR ( a1 , a2 , b1 ) 2. B ( b1 , b2 )

NOTE- Combined table will be drawn for the entity set A and relationship set R.

Case-04: For Binary Relationship With Cardinality Ratio 1:1

Here, two tables will be required. Either combine ‘R’ with ‘A’ or ‘B’

Way-01: AR ( a1 , a2 , b1 ) 2. B ( b1, b2 )

Way-02: A ( a1 , a2 ) 2. BR ( a1 , b1 , b2 )
Rule-06: Convert Binary Relationship With Both
Cardinality Constraints and Participation
Constraints into the table.
The total participation constraint acquires NOT NULL constraint foreign key.

Case-01: In Binary Relationship (Cardinality Constraint and Total

Participation Constraint) From One Side (cardinality ratio = 1 : n)

We will combine entity set B and relationship set R.

Then, two tables will be required 1. A ( a1 , a2 ). 2. BR ( a1, b1 , b2 )

Case-02: In Binary Relationship (Cardinality Constraint and Total

Participation Constraint) From Both Sides

If a key constraint from both the sides of an entity is set with total participation,
then the binary relationship is represented only single table.

Here, Only one table is required ARB ( a1, a2, b1 , b2 )

Rule-07: Convert Binary Relationship with weak
Entity Set into the table
Weak entity sets are always used in association with identifying relationship and
total participation constraints.
Here, two tables will be required 1. A ( a1 , a2 ) 2. BR ( a1 , b1 , b2 ).

Specialization

Specialization is a top-down approach in which a higher-level entity

is divided into multiple specialized lower-level entities. In addition to
sharing the attributes of the higher-level entity, these lower-level
entities have specific attributes of their own. Specialization is
usually used to find subsets of an entity that has a few different or
additional attributes.

The following enhanced entity relationship diagram expresses the

entities in a hierarchical database to demonstrate specialization:
Generalization

Generalization is a bottom-up approach in which multiple lower-level

entities are combined to form a single higher-level entity.
Generalization is usually used to find common attributes among
entities to form a generalized entity. It can also be thought of as the
opposite of specialization.

The following enhanced entity relationship diagram expresses

entities in a hierarchical database to demonstrate generalization: