Module 1:

1. Introduction to Databases and transactions

1.​ What is a database system?

Database System

A database system is an organized collection of interrelated data and a set of programs to access
those data. It is used to store, manage, and retrieve information efficiently and securely. Here's a
detailed explanation for a 5-mark answer:

1.​ Definition and Components:

○​ A database system comprises two main components: the database itself and the
database management system (DBMS).
○​ The database is an organized collection of data, typically stored in a structured
format using tables, records, and fields.
○​ The DBMS is the software that interacts with end users, applications, and the
database itself to capture and analyze data. It provides functionalities for defining,
creating, managing, and controlling access to the database.
2.​ Data Organization:
○​ Data in a database system is typically organized into tables. Each table consists of
rows (records) and columns (fields).
○​ Tables are designed to minimize redundancy and optimize data retrieval through
normalization.
○​ Relationships between tables are established using keys, such as primary keys
(unique identifiers for records) and foreign keys (references to primary keys in
other tables).
3.​ Advantages of Using a Database System:
○​ Data Consistency: Ensures that data is accurate and consistent across the
organization.
○​ Data Security: Protects sensitive information through robust security measures.
○​ Data Integrity: Enforces rules to maintain data accuracy and reliability.
 ○​ Efficient Data Management: Facilitates quick and efficient data retrieval and
management through indexing, query optimization, and storage management.
○​ Scalability and Flexibility: Supports the growing needs of businesses by allowing
databases to scale and adapt to changing requirements.
4.​ Examples of DBMS
1.​ MySQL: An open-source relational database management system widely used for
web applications.
2.​ Oracle Database: A powerful commercial RDBMS used in enterprise
environments, offering extensive features and scalability.
3.​ Microsoft SQL Server: A relational database management system developed by
Microsoft, commonly used in business applications.
4.​ MongoDB: A popular NoSQL database that stores data in flexible, JSON-like
documents, suitable for unstructured data.
5.​ Cassandra: A distributed NoSQL database designed for high availability and
scalability, often used for big data applications.
6.​ SQLite: A lightweight, serverless SQL database commonly used in mobile
applications and small projects.

A database system is a crucial component in modern computing, providing a structured and

efficient way to manage large volumes of data, ensuring consistency, security, and accessibility
for various applications.

2. What is purpose of database system

Purpose of a Database System

The primary purpose of a database system is to efficiently manage data and provide a reliable,
secure, and organized way to store, retrieve, and manipulate information. Here are the key
purposes of a database system:

1.​ Data Storage and Management:

 ○​ Organized Data Storage: Databases store data in structured formats, such as
tables, which makes data easy to organize, manage, and retrieve.
○​ Efficient Data Management: They provide efficient methods to handle large
volumes of data, including indexing and query optimization.
2.​ Data Retrieval:
○​ Query Capabilities: Databases allow users to query data using languages such as
SQL, enabling precise and quick retrieval of information.
○​ Complex Queries: They support complex queries, joins, and aggregations,
providing insights and detailed information from large datasets.
3.​ Data Integrity and Accuracy:
○​ Consistency: Databases enforce rules and constraints to ensure data consistency
and accuracy across multiple records and tables.
○​ Validation: They validate data entries to maintain the integrity and reliability of
the stored information.
4.​ Data Security:
○​ Access Control: Databases implement access control mechanisms to ensure that
only authorized users can access or modify data.
○​ Encryption and Authentication: They provide features such as encryption and
user authentication to protect sensitive data from unauthorized access.
5.​ Data Backup and Recovery:
○​ Backup Solutions: Databases offer backup functionalities to create copies of data,
ensuring data can be restored in case of hardware failure or data corruption.
○​ Recovery Mechanisms: They include recovery options to restore data to a
consistent state after unexpected failures or crashes.
6.​ Concurrency Control:
○​ Simultaneous Access: Databases manage concurrent access by multiple users,
ensuring that transactions are executed in a way that maintains data integrity and
isolation.
○​ Transaction Management: They handle transactions, ensuring that all operations
within a transaction are completed successfully before committing the changes.
 7.​ Data Scalability and Flexibility:
○​ Scalability: Databases can scale vertically (adding more resources to a single
server) or horizontally (distributing data across multiple servers) to handle
increasing amounts of data and users.
○​ Flexibility: They can adapt to changing data requirements, supporting various data
types and structures, including structured, semi-structured, and unstructured data.
8.​ Data Analysis and Reporting:
○​ Analytical Tools: Databases provide tools for data analysis, reporting, and
visualization, enabling businesses to derive meaningful insights from their data.
○​ Business Intelligence: They support business intelligence applications, helping
organizations make data-driven decisions.

In summary, the purpose of a database system is to provide a robust and efficient means of
storing, managing, securing, and retrieving data, facilitating effective data handling and
decision-making in various applications and industries.

3. Write short note on View of data (Customization, Abstraction)

Figure : View of Data

View of Data

In the context of a database system, the "view of data" refers to the different perspectives or
levels of abstraction from which data can be seen and interacted with. These views help manage
complexity, provide security, and improve usability. There are three primary views of data in a
database system:

1.​ Physical View:

○​ Definition: The physical view deals with the physical storage of data on hardware.
It describes how data is stored in disks, the file structures used, and the access
methods for data retrieval.
○​ Purpose: This view is concerned with efficiency and performance. Database
administrators (DBAs) use this view to optimize storage and retrieval, manage
hardware resources, and ensure data integrity and security at the physical level.
 2.​ Logical View:
○​ Definition: The logical view, also known as the conceptual view, deals with the
structure of data and the relationships between different data entities without
considering how the data is physically stored.
○​ Purpose: This view is used to design and understand the overall structure of the
database, including tables, relationships, constraints, and data integrity rules. It
abstracts the physical details and focuses on data modeling.
3.​ External View:
○​ Definition: The external view, or user view, is tailored to individual user needs. It
defines how users and applications interact with the database. Each external view
can be different based on the requirements of different users or applications.
○​ Purpose: This view provides customized perspectives for different users,
enhancing security by restricting access to specific data and improving usability by
presenting data in a relevant and understandable format.
○​ Example: A sales department may have an external view that shows customer
details and order histories, while an inventory management system may have a
view that shows product details and stock levels.

4. Write short note on database architecture

Database Architecture

Database architecture refers to the design, implementation, and management of a database system.
It encompasses the structure, components, and layout of the database, and how these elements
interact to provide efficient, secure, and reliable data management. There are several types of
database architectures, including:

1.​ Single-Tier Architecture:

○​ Definition: Also known as a standalone or desktop database, this architecture
involves a single layer where the database is directly accessed by the user.
 ○​ Components: The user interacts with the database directly on their local machine.
○​ Use Cases: Small-scale applications or personal projects where the database does not
need to be accessed by multiple users simultaneously.
2.​ Two-Tier Architecture:
○​ Definition: This architecture separates the database system into two layers: the client
and the server.
○​ Components:
■​ Client: The application or user interface that interacts with the database.
■​ Server: The database management system (DBMS) that handles data storage,
retrieval, and management.
○​ Use Cases: Small to medium-sized applications where clients need to interact with a
centralized database server.
3.​ Three-Tier Architecture:
○​ Definition: This architecture introduces a middle layer between the client and the
server, known as the application server or business logic layer.
○​ Components:
■​ Client: The user interface or front-end application.
■​ Application Server: The middle layer that processes business logic and
communicates between the client and the database server.
■​ Database Server: The back-end system where the DBMS resides, managing
data storage and retrieval.
○​ Use Cases: Enterprise-level applications where complex business logic needs to be
processed, and multiple clients interact with the database server.
5. Write short note on Transaction Management

Transaction management in database management systems (DBMS) is crucial for ensuring data
integrity, consistency, and reliability. Here's a concise overview of its key aspects:

Definition of a Transaction: A transaction is a logical unit of work that comprises one or

more database operations, such as inserts, updates, or deletes. It represents a complete task
that must either be executed in its entirety (commit) or not at all (rollback).
ACID Properties: Transactions in DBMS adhere to the ACID properties:

○​ Atomicity: Ensures that a transaction is treated as a single unit of operation. Either

all operations within the transaction are executed, or none are.
○​ Consistency: Guarantees that a transaction transforms the database from one
consistent state to another consistent state. Constraints, validations, and integrity
rules must be maintained.
○​ Isolation: Ensures that the concurrent execution of transactions does not interfere
with each other. Each transaction appears to execute in isolation from others, even
when they are executed concurrently.
○​ Durability: Once a transaction commits, its changes are permanent and survive
system failures. The changes are stored in non-volatile memory, usually disk.
2. Data Models
The importance of data models, Basic building blocks, Business rules, The evolution of data
models, Degrees of data abstraction

Data Models
6. Importance of Data Models
●​ Data models provide a structured framework for managing and organizing data in a
database.
●​ They are essential for designing and defining data elements and their relationships,
ensuring data consistency and integrity.
●​ By using data models, organizations can efficiently manage and retrieve data, support
decision-making processes, and maintain a clear representation of data requirements.
●​ They also facilitate communication between stakeholders and developers by providing a
visual representation of data structures and relationships.

7. Basic Building Blocks

Data models are constructed using several fundamental building blocks that collectively define the
structure and relationships within a database. Understanding these building blocks is crucial for
designing and managing effective databases. Here’s a detailed explanation of each building block:

1. Entities:

Entities represent objects or things within the domain of the database that have a distinct existence.
Each entity corresponds to a real-world object or concept that the database aims to store
information about. For example:

●​ Definition: An entity is a distinguishable item or object that is relevant to the database. It

can be a person, place, thing, or event that the database needs to track.
●​ Examples:
 ○​ Customers: Represents individuals or organizations that purchase products or
services.
○​ Orders: Represents transactions or requests made by customers.
○​ Products: Represents items available for sale or inventory.

Entities are fundamental because they form the primary units of information within the database.

2. Attributes:

Attributes are the properties or characteristics that define and describe entities. They provide
additional details about an entity and are used to capture specific pieces of information. For
instance:

●​ Definition: Attributes are the descriptive features of an entity, capturing the various aspects
or details related to that entity.
●​ Examples:
○​ Customer Name: An attribute of the customer entity that specifies the name of the
customer.
○​ Order Date: An attribute of the order entity that records the date on which the order
was placed.
○​ Product Price: An attribute of the product entity that indicates the cost of the
product.

Attributes help in distinguishing one entity instance from another and provide valuable details
necessary for data management.

3. Relationships:

Relationships define how entities are associated with each other. They describe the interactions or
links between different entities within the database. Understanding relationships is essential for
establishing how data is connected and how it can be retrieved. For example:
 ●​ Definition: A relationship is an association between two or more entities that describes how
they interact with each other.
●​ Examples:
○​ Customer Places Order: A relationship between the customer and order entities
indicating that a customer can place one or more orders.
○​ Order Contains Product: A relationship between the order and product entities
showing that an order can include multiple products.

Relationships help in modeling real-world interactions and ensure that data is connected logically.

4. Entity Sets:

Entity sets refer to collections of similar types of entities. An entity set is a group of entities that
share the same attributes and are of the same type. It represents a category or class of entities
within the database. For example:

●​ Definition: An entity set is a collection of entities that have the same attributes and belong
to the same entity type.
●​ Examples:
○​ Customer Set: A set of all customer entities, where each entity in the set represents
an individual customer.
○​ Order Set: A set of all order entities, where each entity represents a specific order.

Entity sets help in organizing entities into groups that can be managed and queried collectively,
facilitating efficient data handling.

●​
8. Business Rules
Business rules in a Database Management System (DBMS) are constraints and policies that
define how data should be created, stored, and manipulated to align with organizational goals
and processes. They ensure data integrity and support consistent business logic within the
database.

For e.g.

Consider the product table :

Business Rules:

1.​ Product Name Uniqueness: Each product must have a unique name.
2.​ Minimum Stock Quantity: The stock quantity of a product must be at least 1.

Table Definition

Products Table

CREATE TABLE Products (

ProductID INT PRIMARY KEY,
ProductName NVARCHAR(100) NOT NULL UNIQUE, -- Product names
must be unique
StockQuantity INT NOT NULL CHECK (StockQuantity >= 1) --
Stock quantity must be at least 1
);

Explanation:
 1.​ Product Name Uniqueness: The UNIQUE constraint on ProductName ensures that no
two products can have the same name, enforcing uniqueness across the table.
2.​ Minimum Stock Quantity: The CHECK constraint on StockQuantity ensures that the
value is always at least 1, preventing entries with zero or negative stock quantities.

Valid Insertion:

INSERT INTO Products (ProductID, ProductName, StockQuantity)

VALUES (1, 'Laptop', 10);

Invalid Insertion (Product Name Uniqueness Violation):

-- This will fail if 'Laptop' already exists

INSERT INTO Products (ProductID, ProductName, StockQuantity)
VALUES (2, 'Laptop', 5);

-- This will fail because StockQuantity is less than 1

INSERT INTO Products (ProductID, ProductName, StockQuantity)
VALUES (3, 'Mouse', 0);

9. The Evolution of Data Models

1. Hierarchical Model
 ●​ Data is organized in a tree-like structure with a single root node.
●​ Each parent node can have multiple child nodes, but each child node has only one
parent.
●​ Commonly used in early database systems and file systems.

2. Network Model

Ok
 ●​ Data is organized in a graph-like structure.
●​ Entities (nodes) can have multiple parent and child relationships.
●​ Allows more complex relationships compared to hierarchical models.

3. Relational Model

●​ Data is organized into tables (relations).

●​ Each table has rows (records) and columns (attributes).
●​ Relationships between tables are established using foreign keys.
●​ SQL is used for querying and manipulating data.
4. Object-Oriented Model
 ●​ Integrates object-oriented programming with databases.
●​ Data is represented as objects with attributes (properties) and methods.

5. NoSQL Model

●​ Key-Value Store: Stores data as key-value pairs.

10. Degrees of Data Abstraction

●​ Physical Level: Describes how data is physically stored in the database. It involves
details of storage structures and access methods.
●​ Logical Level: Focuses on the structure of the data itself and how it is organized and
related. It defines tables, relationships, and constraints without considering how they are
implemented physically.
 ●​ View Level: Represents how data is presented to end-users. It includes user interfaces and
views that provide a simplified or customized representation of the data, often abstracting
away the complexities of the underlying logical and physical levels.

Entity-Relationship Model (ER Model)

The ER Model is a conceptual representation of the data. It provides a blueprint of the database by
defining entities, attributes, relationships, and constraints.

1.​ Entities:
○​ Definition: Entities represent real-world objects or concepts, such as people,
products, or events, that the database must store information about.
○​ Examples: Customer, Product, Employee.
2.​ Attributes:
○​ Definition: Attributes are the properties or characteristics of entities.
○​ Types of Attributes:
■​ Simple Attributes: Attributes that cannot be broken down further (e.g.,
FirstName, Age).
■​ Composite Attributes: Attributes that can be subdivided into more attributes
(e.g., Address can be divided into Street, City, State).
■​ Derived Attributes: Attributes that can be calculated from other attributes
(e.g., Age can be derived from DateOfBirth).
3.​ Relationships:
○​ Definition: Relationships define the associations between entities.
○​ Types of Relationships:
■​ One-to-One (1:1): One entity instance is associated with exactly one instance
of another entity.
■​ One-to-Many (1​
): One entity instance is associated with multiple instances of another entity.
 ■​ Many-to-Many (M​
): Multiple instances of one entity are associated with multiple instances of
another entity.

ER Diagrams (ERD)

ER Diagrams (Entity-Relationship Diagrams) are graphical representations of the ER Model.

ERDs use specific symbols to represent entities, attributes, and relationships.

ERD Issues

When designing ERDs, there are several issues that may arise:

1.​ Redundancy:
○​ If entities and relationships are not modeled correctly, redundant data may be stored,
leading to inefficiency and data anomalies.

Problem: Redundant Data Storage

If the Order Table stores customer name and contact details (e.g., address, phone number) for
every order, it leads to data redundancy. This is because the same customer details are repeated for
every order they make.

Example of Redundant Data:

Order ID Order Product Customer Customer Address Customer

Date Name Phone

101 2024-09-01 Laptop John Doe 123 Main St, Cityville 123-456-7890
 102 2024-09-05 Headphones John Doe 123 Main St, Cityville 123-456-7890

103 2024-09-07 Keyboard Jane Smith 456 Elm St, 987-654-3210

Townsville

104 2024-09-10 Mouse John Doe 123 Main St, Cityville 123-456-7890

Consequences of Redundancy:

1.​ Inefficiency:
○​ The database consumes more storage space because the same customer information
is stored multiple times.
2.​ Data Anomalies:
○​ Update Anomaly: If John Doe changes his phone number, it must be updated in
every order entry. If the update is missed in some records, the database will have
inconsistent data.
○​ Deletion Anomaly: If an order is deleted, and it happens to be the only record for
that customer, their information might be accidentally lost.
○​ Insertion Anomaly: Adding a new order for a customer requires duplicating all
customer details again, increasing the risk of data entry errors.

Correct Data Modeling to Avoid Redundancy:

To avoid redundancy, we should normalize the database by separating customer details from order
details:

1.​ Customer Table:

Customer ID Customer Name Customer Address Customer Phone

1 John Doe 123 Main St, Cityville 123-456-7890

2 Jane Smith 456 Elm St, Townsville 987-654-3210

2.​ Order Table:

Order ID Order Date Product Customer ID

101 2024-09-01 Laptop 1

102 2024-09-05 Headphones 1

103 2024-09-07 Keyboard 2

104 2024-09-10 Mouse 1

2.​ Weak Entities:

 ○​ Entities that do not have a primary key on their own and rely on another entity’s key
are called weak entities. Special handling is required for weak entities to ensure data
integrity.

Weak Entity Definition:

●​ The Payment entity is considered a weak entity because it cannot be uniquely identified by
its attributes alone. It relies on the Order entity's primary key (OrderID) to provide a
unique identification for each payment.

Example of Weak Entity with Special Handling:

Order Table (Strong Entity):

OrderID OrderDate CustomerID

1001 2024-09-01 C001

1002 2024-09-03 C002

Payment Table (Weak Entity):

OrderID PaymentID PaymentDate Amount

1001 1 2024-09-02 100.00

1001 2 2024-09-04 150.00

1002 1 2024-09-04 200.00

3.​ Multi-valued Attributes:

○​ Handling attributes that can have multiple values (e.g., phone numbers) can
complicate database design.
4.​ Complexity:
○​ Large ERDs with many entities, relationships, and attributes can become complex
and hard to interpret, making the design phase more difficult and prone to errors.

ER Model design

Let's create an example of an ER model for a Library Management System. This system
manages information about Books, Authors, Members, and Loans (where members borrow
books from the library). The following steps will guide us through creating the ER model.

Entities

1.​ Book
○​ Attributes: BookID (Primary Key), Title, Genre, ISBN, PublicationYear
2.​ Author
○​ Attributes: AuthorID (Primary Key), Name, Nationality
3.​ Member
○​ Attributes: MemberID (Primary Key), Name, Email, Phone
4.​ Loan
 ○​ Attributes: LoanID (Primary Key), LoanDate, ReturnDate

Relationships

1.​ Book-Author (Many-to-Many Relationship)

○​ A book can have multiple authors, and an author can write multiple books.
2.​ Member-Loan (One-to-Many Relationship)
○​ A member can have multiple loans, but each loan is associated with one member.
3.​ Loan-Book (Many-to-One Relationship)
○​ A loan can involve multiple books, but each loan is linked to a specific member.

Steps to Create the ER Model

1.​ Identify the Entities:

○​ Book, Author, Member, and Loan are the main entities.
2.​ Define Attributes:
○​ For each entity, specify the attributes such as BookID, Title, AuthorID, Name,
etc.
3.​ Establish Relationships:
○​ Connect the entities with relationships such as WrittenBy (between Book and
Author), BorrowedBy (between Member and Loan), and Contains (between
Loan and Book).
4.​ Define Cardinality:
○​ Define the cardinality of relationships such as Many-to-Many between Book and
Author, One-to-Many between Member and Loan, etc.

ER Model Diagram

Below is a simplified version of the ER model for the Library Management System.
Explanation of the ER Model:

1.​ Entities: Each rectangle represents an entity with its primary key (PK) and attributes listed
inside.
○​ Book: Stores information about books in the library.
○​ Author: Stores information about authors.
○​ Member: Stores information about library members.
○​ Loan: Stores information about the loan transaction.
2.​ Relationships:
○​ WrittenBy (Book-Author): Many-to-Many relationship because a book can have
multiple authors, and an author can write multiple books.
○​ BorrowedBy (Member-Loan): One-to-Many relationship because a member can
borrow multiple books, but each loan is associated with one member.
○​ Contains (Loan-Book): Many-to-One relationship because a loan can contain
multiple books, but each loan belongs to one member.