JSS SCIENCE AND TECHNOLOGY UNIVERSITY

DEPARTMENT OF COMPUTER APPLICATIONS

Course Code: 24MCA130 Course Instructor: CHAITHRA C S

Course Name: RDBMS
Week 1: Types of DBMS and SQL Overview
Types of DBMS (Database Management Systems)
A Database Management System (DBMS) is software that manages databases and provides an
interface for users to interact with data. The four primary types of DBMS are:
1. Hierarchical DBMS (HDBMS):
o Structure: Data is organized in a tree-like structure where each record has a single
parent and potentially many children (like a hierarchy). It uses a parent-child
relationship.
o Example: IBM's Information Management System (IMS).
o Advantages: Fast access to data and easy to navigate when data is organized
hierarchically.
o Disadvantages: Limited flexibility and difficult to manage when relationships between
data are complex.
2. Network DBMS (NDBMS):
o Structure: Data is represented using a graph structure where nodes (records) are
connected through links (relationships). Each node can have multiple parents, allowing
for more complex relationships than hierarchical systems.
o Example: Integrated Data Store (IDS).
o Advantages: More flexible than hierarchical DBMS with many-to-many relationships.
o Disadvantages: Complex to design and navigate and can be difficult to manage.
3. Relational DBMS (RDBMS):
o Structure: Data is stored in tables (relations), where each table consists of rows and
columns. Tables can be related to one another using primary and foreign keys. It uses
SQL for querying data.
o Example: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
o Advantages: Simple to use, flexible, supports complex queries, and is the most widely
used type today.
o Disadvantages: Can have performance issues with very large databases or extremely
complex relationships.
4. Object-Oriented DBMS (OODBMS):

CCS 1
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

o Structure: Data is stored as objects, similar to how it is done in object-oriented

programming. Objects encapsulate both data and the operations that can be performed
on that data.
o Example: db4o, ObjectDB.
o Advantages: Best suited for applications requiring complex data representations and
relationships, like CAD or multimedia.
o Disadvantages: Not as widely adopted, and the learning curve can be steep for those
familiar with relational models.

DBMS Languages
A DBMS supports various languages to interact with the database. The main types of DBMS languages
are:
1. Data Definition Language (DDL):
o Purpose: Defines the structure of the database, including creating, altering, and deleting
tables, schemas, and other database objects.
o Common Commands:
▪ CREATE: Defines a new table or database object.
▪ ALTER: Modifies an existing database object.
▪ DROP: Deletes a database object.
▪ TRUNCATE: Removes all records from a table, retaining the table structure.
2. Data Manipulation Language (DML):
o Purpose: Manages and manipulates data in the database (insert, update, delete,
retrieve).
o Common Commands:
▪ INSERT: Adds new data into a table.
▪ UPDATE: Modifies existing data in a table.
▪ DELETE: Removes data from a table.
▪ SELECT: Retrieves data from a table.
3. Data Control Language (DCL):
o Purpose: Controls access to the database and manages permissions.
o Common Commands:

CCS 2
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

▪ GRANT: Gives specific privileges to users or roles.

▪ REVOKE: Removes previously granted privileges.
4. Transaction Control Language (TCL):
o Purpose: Manages transactions in a database (group of SQL statements executed
together as a unit).
o Common Commands:
▪ COMMIT: Saves all changes made during the transaction.
▪ ROLLBACK: Undoes changes made during the transaction.
▪ SAVEPOINT: Creates a point within a transaction to which you can roll back.
▪ SET TRANSACTION: Modifies transaction properties.

SQL (Structured Query Language)

SQL (Structured Query Language) is the standard language used to interact with relational databases.
SQL enables users to query, update, and manage data, as well as define database structures.
Key SQL Commands:
1. Data Definition Language (DDL):
o CREATE:
CREATE TABLE Students (
StudentID INT,
Name VARCHAR(50),
Age INT );
o ALTER:
ALTER TABLE Students ADD Address VARCHAR(100);
o DROP:
DROP TABLE Students;
2. Data Manipulation Language (DML):
o SELECT:
SELECT Name, Age FROM Students WHERE Age > 18;
o INSERT:
INSERT INTO Students (StudentID, Name, Age) VALUES (1,
'Alice', 22);
CCS 3
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

o UPDATE:
UPDATE Students SET Age = 23 WHERE StudentID = 1;
o DELETE:
DELETE FROM Students WHERE Age < 18;
3. Data Control Language (DCL):
o GRANT:
GRANT SELECT, INSERT ON Students TO UserName;
o REVOKE:
REVOKE SELECT ON Students FROM UserName;
4. Transaction Control Language (TCL):
o COMMIT:
COMMIT;
o ROLLBACK:
ROLLBACK;

CCS 4
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

Banking Database

Week-2

This lab focuses on creating a database schema for a Banking Database and performing operations
using SQL. The database schema includes six tables: BRANCH, ACCOUNT, CUSTOMER,
DEPOSITOR, LOAN, and BORROWER.

Objectives

1. Learn to create relational database tables with primary and foreign keys.

2. Insert data into the tables.

3. Perform SQL queries to retrieve data.

4. Understand relationships between tables (Primary Key and Foreign Key).

➢ The relationships ensure data consistency and integrity.

➢ Primary Keys uniquely identify records in a table.

➢ Foreign Keys link tables, maintaining referential integrity.

1. Creating Tables

a. BRANCH Table

CREATE TABLE BRANCH (

branch_name VARCHAR(50) PRIMARY KEY,

city VARCHAR(50),

asset VARCHAR(50)

);

• Primary Key: branch_name

b. ACCOUNT Table

CREATE TABLE ACCOUNT (

account_number VARCHAR(50) PRIMARY KEY,

branch_name VARCHAR(50),

balance FLOAT,

CCS 5
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

FOREIGN KEY (branch_name) REFERENCES BRANCH(branch_name)

);

• Primary Key: account_number

• Foreign Key: branch_name references BRANCH(branch_name)

c. CUSTOMER Table

CREATE TABLE CUSTOMER (

customer_name VARCHAR(50) PRIMARY KEY,

street VARCHAR(100),

city VARCHAR(50)

);

• Primary Key: customer_name

d. DEPOSITOR Table

CREATE TABLE DEPOSITOR (

customer_name VARCHAR(50),

account_number VARCHAR(50),

PRIMARY KEY (customer_name, account_number),

FOREIGN KEY (customer_name) REFERENCES CUSTOMER(customer_name),

FOREIGN KEY (account_number) REFERENCES ACCOUNT(account_number)

);

• Primary Key: Combination of customer_name and account_number

• Foreign Keys:

o customer_name references CUSTOMER(customer_name)

o account_number references ACCOUNT(account_number)

e. LOAN Table

CREATE TABLE LOAN (

loan_number VARCHAR(50) PRIMARY KEY,

CCS 6
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

branch_name VARCHAR(50),

amount INTEGER,

FOREIGN KEY (branch_name) REFERENCES BRANCH(branch_name)

);

• Primary Key: loan_number

• Foreign Key: branch_name references BRANCH(branch_name)

f. BORROWER Table

CREATE TABLE BORROWER (

customer_name VARCHAR(50),

loan_number VARCHAR(50),

PRIMARY KEY (customer_name, loan_number),

FOREIGN KEY (customer_name) REFERENCES CUSTOMER(customer_name),

FOREIGN KEY (loan_number) REFERENCES LOAN(loan_number)

);

• Primary Key: Combination of customer_name and loan_number

• Foreign Keys:

o customer_name references CUSTOMER(customer_name)

o loan_number references LOAN(loan_number)

2. Inserting Data

a. Insert into BRANCH Table

INSERT INTO BRANCH (branch_name, city, asset)

VALUES ('Downtown', 'New York', '1000000'),

('Uptown', 'Chicago', '800000');

b. Insert into ACCOUNT Table

INSERT INTO ACCOUNT (account_number, branch_name, balance)

CCS 7
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

VALUES ('ACC001', 'Downtown', 5000.00),

('ACC002', 'Uptown', 3000.00);

c. Insert into CUSTOMER Table

INSERT INTO CUSTOMER (customer_name, street, city)

VALUES ('Alice', '123 Main St', 'New York'),

('Bob', '456 Oak St', 'Chicago');

d. Insert into DEPOSITOR Table

INSERT INTO DEPOSITOR (customer_name, account_number)

VALUES ('Alice', 'ACC001'),

('Bob', 'ACC002');

e. Insert into LOAN Table

INSERT INTO LOAN (loan_number, branch_name, amount)

VALUES ('LN001', 'Downtown', 10000),

('LN002', 'Uptown', 5000);

f. Insert into BORROWER Table

INSERT INTO BORROWER (customer_name, loan_number)

VALUES ('Alice', 'LN001'),

('Bob', 'LN002');

3. Selecting Data

a. Select All Branches

SELECT * FROM BRANCH;

b. Select All Accounts and Their Balance

SELECT account_number, balance FROM ACCOUNT;

c. Select All Customers Depositing in a Specific Account

SELECT customer_name FROM DEPOSITOR

WHERE account_number = 'ACC001';

CCS 8
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

d. Select All Loans for a Specific Branch

SELECT loan_number, amount FROM LOAN

WHERE branch_name = 'Downtown';

e. Select All Borrowers and Their Loans

SELECT customer_name, loan_number FROM BORROWER;

f. Select All Customers and Their Account Balances

SELECT c.customer_name, a.balance

FROM CUSTOMER c

JOIN DEPOSITOR d ON c.customer_name = d.customer_name

JOIN ACCOUNT a ON d.account_number = a.account_number;

4. Primary Key and Foreign Key Relationships

Primary Keys:

• BRANCH(branch_name)

• ACCOUNT(account_number)

• CUSTOMER(customer_name)

• LOAN(loan_number)

Foreign Keys:

• ACCOUNT(branch_name) → BRANCH(branch_name)

• DEPOSITOR(customer_name) → CUSTOMER(customer_name)

• DEPOSITOR(account_number) → ACCOUNT(account_number)

• LOAN(branch_name) → BRANCH(branch_name)

• BORROWER(customer_name) → CUSTOMER(customer_name)

• BORROWER(loan_number) → LOAN(loan_number)

CCS 9
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

Week-3

Let's continue from the Banking Database and generate the SQL queries for the additional tasks. We
will focus on specific queries involving aggregate functions, comparison operators, and logical
operators. Below are the SQL queries for the required tasks.

1. List the loan number from LOAN having amount 10000 with a specific branch name.

For this query, we will filter the LOAN table for rows where the amount is 10000 and the branch_name
matches a specific branch.

SELECT loan_number

FROM LOAN

WHERE amount = 10000 AND branch_name = 'Downtown';

2. List the loan number with an amount between 1000 and 10000.

This query uses the BETWEEN operator to filter the LOAN table for loans where the amount is
between 1000 and 10000.

SELECT loan_number

FROM LOAN

WHERE amount BETWEEN 1000 AND 10000;

3. List the customer name (cname) with a substring.

If we want to filter customer names containing a specific substring (e.g., names containing "Ali"), we
can use the LIKE operator with wildcard characters (%).

SELECT customer_name

FROM CUSTOMER

WHERE customer_name LIKE '%Ali%';

4. List the number of tuples in the CUSTOMER table.

To get the count of records (tuples) in the CUSTOMER table, we use the COUNT() aggregate function.

SELECT COUNT(*) AS number_of_customers

FROM CUSTOMER;

CCS 10
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

5. List customer name, loan number, and amount with a specific branch name.

This query involves joining the BORROWER, LOAN, and CUSTOMER tables to list the customer
name, loan number, and loan amount for a specific branch.

SELECT c.customer_name, l.loan_number, l.amount

FROM BORROWER b

JOIN CUSTOMER c ON b.customer_name = c.customer_name

JOIN LOAN l ON b.loan_number = l.loan_number

WHERE l.branch_name = 'Downtown';

6. Various Aggregate Functions:

a. Get the total sum of loan amounts:

The SUM() aggregate function is used to calculate the total sum of all loan amounts.

SELECT SUM(amount) AS total_loan_amount

FROM LOAN;

b. Get the average balance of all accounts:

The AVG() function calculates the average balance from the ACCOUNT table.

SELECT AVG(balance) AS average_balance

FROM ACCOUNT;

c. Get the maximum loan amount:

The MAX() function returns the maximum loan amount.

SELECT MAX(amount) AS max_loan_amount

FROM LOAN;

d. Get the minimum balance in accounts:

The MIN() function retrieves the minimum account balance.

SELECT MIN(balance) AS min_balance

FROM ACCOUNT;

e. Get the number of loans (count of loans):

CCS 11
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

The COUNT() function is used to count the total number of loans.

SELECT COUNT(*) AS number_of_loans

FROM LOAN;

7. Various Comparison Operators:

a. Find customers with a balance greater than 5000:

Here we use the > operator to find accounts with a balance greater than 5000.

SELECT c.customer_name, a.balance

FROM CUSTOMER c

JOIN DEPOSITOR d ON c.customer_name = d.customer_name

JOIN ACCOUNT a ON d.account_number = a.account_number

WHERE a.balance > 5000;

b. Find customers who borrowed loans greater than 5000:

We use the > comparison operator to find loan amounts greater than 5000.

SELECT b.customer_name, l.loan_number, l.amount

FROM BORROWER b

JOIN LOAN l ON b.loan_number = l.loan_number

WHERE l.amount > 5000;

c. Find accounts with a balance less than or equal to 1000:

Using the <= operator, this query retrieves accounts with a balance less than or equal to 1000.

SELECT a.account_number, a.balance

FROM ACCOUNT a

WHERE a.balance <= 1000;

d. Find customers with loans less than 5000 and belonging to a specific branch:

SELECT c.customer_name, l.loan_number, l.amount

FROM BORROWER b

CCS 12
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

JOIN CUSTOMER c ON b.customer_name = c.customer_name

JOIN LOAN l ON b.loan_number = l.loan_number

WHERE l.amount < 5000 AND l.branch_name = 'Uptown';

8. Various Logical Operators:

a. Find customers who have a balance greater than 1000 and have a loan of more than 5000:

This query uses AND to combine the two conditions: balance greater than 1000 and loan amount
greater than 5000.

SELECT c.customer_name, a.balance, l.loan_number, l.amount

FROM CUSTOMER c

JOIN DEPOSITOR d ON c.customer_name = d.customer_name

JOIN ACCOUNT a ON d.account_number = a.account_number

JOIN BORROWER b ON c.customer_name = b.customer_name

JOIN LOAN l ON b.loan_number = l.loan_number

WHERE a.balance > 1000 AND l.amount > 5000;

b. Find customers who either have a loan or an account with a balance greater than 5000:

This query uses the OR operator to find customers who meet at least one of the conditions.

SELECT c.customer_name

FROM CUSTOMER c

JOIN DEPOSITOR d ON c.customer_name = d.customer_name

JOIN ACCOUNT a ON d.account_number = a.account_number

LEFT JOIN BORROWER b ON c.customer_name = b.customer_name

LEFT JOIN LOAN l ON b.loan_number = l.loan_number

WHERE a.balance > 5000 OR l.amount > 5000;

c. Find customers with a loan amount between 1000 and 5000, and from a specific branch:

Here, we combine AND and BETWEEN to find customers who have a loan between 1000 and 5000
and are from a specific branch.

CCS 13
JSS SCIENCE AND TECHNOLOGY UNIVERSITY
DEPARTMENT OF COMPUTER APPLICATIONS

SELECT c.customer_name, l.loan_number, l.amount

FROM BORROWER b

JOIN CUSTOMER c ON b.customer_name = c.customer_name

JOIN LOAN l ON b.loan_number = l.loan_number

WHERE l.amount BETWEEN 1000 AND 5000 AND l.branch_name = 'Uptown'; -

- Replace 'Uptown' with the desired branch

d. Find customers with names starting with "A" and having an account balance greater than
2000:

This query uses both LIKE and the AND logical operator.

SELECT c.customer_name, a.balance

FROM CUSTOMER c

JOIN DEPOSITOR d ON c.customer_name = d.customer_name

JOIN ACCOUNT a ON d.account_number = a.account_number

WHERE c.customer_name LIKE 'A%' AND a.balance > 2000;

Summary of SQL Queries:

• List loan numbers based on conditions: Using WHERE, BETWEEN, and comparison
operators.
• Substring search: Using the LIKE operator with %.
• Count tuples: Using COUNT() aggregate function.
• Join tables for specific information: Using JOIN to combine data from multiple tables.
• Aggregate functions: Using SUM(), AVG(), MAX(), MIN(), and COUNT() for calculations.
• Comparison operators: Using =, <, >, <=, >=, and BETWEEN to filter data.
• Logical operators: Using AND, OR, and NOT to combine multiple conditions.
These queries address the tasks for retrieving data from the Banking Database, including using
aggregate functions, comparison operators, and logical operators.

CCS 14