Aim: Introduction to DBMS and SQL

DBMS: A Database Management System (DBMS) is a software tool designed to create, manage, and
interact with databases. It serves as an interface between the database and the end-users, allowing for
efficient data storage, retrieval, and manipulation.

Key Functions of DBMS

●​ Data Management: DBMS helps in storing, managing, and scaling data, which is essential for
data-driven workflows and analysis.
●​ Data Integrity and Security: It ensures the accuracy and quality of data while providing
mechanisms for data security and concurrent access.
●​ User Interaction: Users can perform various operations on the data, such as querying, updating, and
deleting records, through the DBMS.

Advantages of DBMS

A Database Management System (DBMS) offers numerous advantages that enhance data management and
operational efficiency. Here are some key benefits:

●​ Data Independence: DBMS provides a level of abstraction that separates data from the applications
that use it. This means users can work with data without needing to know its exact location or
structure, which enhances adaptability and reduces the risk of data corruption due to changes in
hardware or software.
●​ Improved Data Integrity and Consistency: DBMS enforces rules and constraints on data, ensuring
that all information is accurate and consistent. For instance, it can restrict certain fields to unique
values, preventing duplicate entries.
●​ Efficient Data Access and Sharing: DBMS facilitates quick and efficient access to data, allowing
multiple users to retrieve and manipulate data simultaneously without conflicts. This is particularly
beneficial in collaborative environments.
●​ Reduced Data Redundancy: By centralizing data storage, DBMS minimizes data duplication,
which helps maintain data consistency and reduces storage costs.

Disadvantages of DBMS

While Database Management Systems (DBMS) offer numerous advantages, they also come with certain
disadvantages that organizations should consider:

●​ Cost of Hardware and Software: Implementing a DBMS often requires significant investment in
both hardware and software. High-speed processors and large memory capacities are essential to
handle the data efficiently, which can increase overall costs significantly.
●​ Performance Issues: As the size and complexity of a database grow, performance can become a
concern. Queries may slow down, especially if the database is not optimized or if it experiences high
traffic from multiple users.
●​ Complexity: DBMS can be complex to set up and manage. This complexity requires specialized
knowledge and training for database administrators and users, which can lead to inefficiencies if not
handled properly.

Bhavya Gupta (02814802723)

Applications of DBMS

●​ Banking: For managing transactions, customer accounts, and financial records, ensuring data
integrity and security.
●​ E-commerce: To handle product inventories, customer data, and order processing, facilitating
smooth online transactions.
●​ Healthcare: In medical records systems to store patient information, treatment histories, and billing
details, ensuring compliance with regulations.
●​ Telecommunications: For managing customer data, call records, and billing information, enabling
efficient service delivery.

SQL: SQL, or Structured Query Language, is a domain-specific language used for managing and
manipulating data in relational database management systems (RDBMS). It is particularly effective for
handling structured data, which is organized into tables consisting of rows and columns.

Key features of SQL:

●​ Data Querying: SQL allows users to retrieve data from databases using queries, making it easy to
access specific information.
●​ Data Manipulation: Users can insert, update, and delete records in a database, enabling dynamic
data management.
●​ Data Definition: SQL provides commands to define and modify database structures, such as creating
or altering tables.
●​ Data Control: It includes features for controlling access to data, ensuring that only authorized users
can perform certain operations.

Types of SQL

SQL commands are categorized into several types, each serving a specific purpose in database management.
The main types of SQL are:

●​ Data Definition Language (DDL): This type includes commands that define and manage all
database objects. Common DDL commands are:
○​ CREATE: To create new tables or databases.

Syntax- CREATE DATABASE databasename;

CREATE TABLE table_name ( column1 datatype, column2 datatype, column3

datatype);

○​ ALTER: To modify existing database structures.

Syntax- ALTER TABLE Customers ADD Email varchar(255);

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

○​ DROP: To delete tables or databases.

Syntax- DROP DATABASE databasename;

Bhavya Gupta (02814802723)
DROP TABLE table_name;

●​ Data Manipulation Language (DML): DML commands are used for managing data within the
database. Key DML commands include:
○​ INSERT: To add new records to a table.

Syntax- INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

○​ UPDATE: To modify existing records.

Syntax- UPDATE table_name​

SET column1 = value1, column2 = value2, ...​
WHERE condition;

○​ DELETE: To remove records from a table.

Syntax- DELETE FROM table_name WHERE condition

●​ Data Control Language (DCL): DCL commands are used to control access to data within the
database. Important DCL commands include:
○​ GRANT: To provide user access privileges.

Syntax- GRANT privileges_names ON object TO user;

○​ REVOKE: To remove user access privileges.

Syntax- REVOKE privileges ON object FROM user;

●​ Transaction Control Language (TCL): TCL commands manage transactions in the database,
ensuring data integrity. Key TCL commands are:
○​ COMMIT: To save all changes made during the current transaction.

Syntax- COMMIT;

○​ ROLLBACK: To undo changes made during the current transaction.

Syntax- ROLLBACK;

Database Architecture: Database architecture refers to the design, structure, and organization of a database
system. It defines how data is stored, managed, and accessed within the system. There are different types of
database architectures, including one-tier, two-tier, and three-tier, each with its own configuration and use
cases

One-Tier Architecture: All components (UI, logic, database) on one system. Simple, but not scalable.

Two-Tier Architecture: Client (UI) directly communicates with server (database). Better performance, but
server can be a bottleneck.

Three-Tier Architecture: Client, middle tier (handles logic), and database server. Offers scalability,
flexibility, and maintainability, but more complex to develop.
Bhavya Gupta (02814802723)
Program- 1

Aim: The Institute offers multiple B. Tech Courses. Students can take admission in any Course offered by
the Institute. Multiple subjects are taught in each branch. The institute has a Director and Hod of each B.
Tech Course. The HoD of another branch may be given additional responsibility to take charge of one more
department for a limited period in the absence of Hod of that branch. Faculties recruited in each B. Tech
Course can teach only a single same subject to students of different B.Tech Courses. For each subject, there
is a faculty coordinator responsible for coordinating all the faculties teaching the same subject. A batch of 20
students is assigned a faculty mentor. The mentor takes counsellor meetings twice a month.
Data to be recorded in the database include:
Student – enrollment no, name, date of birth, address, contact no, email – id, qualifying exam rank, Course
Faculty – Faculty name, contact no, email id, course
Parents data – Parents name, contact no, email id, Profession
Draw the ER diagram (Conceptual design) of the given problem statement.

Theory: The Entity Relationship Model is a model for identifying entities (like student, car or company) to
be represented in the database and representation of how those entities are related. The ER data model
specifies an enterprise schema that represents the overall logical structure of a database graphically.

Why Use ER Diagrams In DBMS?

1.​ ER diagrams represent the E-R model in a database, making them easy to convert into relations
(tables).
2.​ ER diagrams provide the purpose of real-world modeling of objects which makes them intently
useful.
3.​ ER diagrams require no technical knowledge of the underlying DBMS used.
4.​ It gives a standard solution for visualizing the data logically.

Symbols Used in ER Model

ER Model is used to model the logical view of the system from a data perspective which consists of these
symbols:
1.​ Rectangles: Rectangles represent Entities in the ER Model.
2.​ Ellipses: Ellipses represent Attributes in the ER Model.
3.​ Diamond: Diamonds represent Relationships among Entities.
4.​ Lines: Lines represent attributes to entities and entity sets with other relationship types.
5.​ Double Ellipse: Double Ellipses represent Multi-Valued Attributes.
6.​ Double Rectangle: Double Rectangle represents a Weak Entity.

Components of an E-R Diagram

1.​ Entity- An Entity may be an object with a physical existence – a particular person, car, house, or
employee – or it may be an object with a conceptual existence – a company, a job, or a university
course.
Types of Entity-
a.​ Strong Entity: Independent, has its own unique identifier (primary key).
b.​ Weak Entity: Dependent on a strong entity for its existence and identification, uses a partial
key in conjunction with the strong entity's primary key.

Bhavya Gupta (02814802723)

2.​ Attributes- Attributes are like the specific characteristics or properties that describe an entity.
Types of Attributes-
a.​ Key Attribute: This is like the most important label. It's the one that makes each "thing"
(entity) in your box unique. Think of it like a student's ID number - no two students can have
the same ID. It's used to identify and find specific information quickly.
b.​ Composite Attribute: This is when a label needs to be broken down further. Imagine the
label "Full Name." It's made up of two parts: "First Name" and "Last Name." So, "Full
Name" is a composite attribute.
c.​ Multi-valued Attribute: Some labels can have more than one value. For example, a student
might have multiple "Interests" like "Sports," "Music," and "Coding." "Interests" is a
multi-valued attribute because it can hold several values for one student.
d.​ Derived Attribute: It's a label that you don't actually fill in directly. Instead, you figure it out
based on other labels. For instance, you could have a "Date of Birth" label and a "Current
Date" label. From those, you can derive the student's "Age." So, "Age" is a derived attribute.​

3.​ Relationship- A Relationship Type represents the association between entity types. For example,
‘Enrolled in’ is a relationship type that exists between entity type Student and Course. In the ER
diagram, the relationship type is represented by a diamond and connects the entities with lines.

Degree of a Relationship
The degree of a relationship refers to the number of entity types that participate in that relationship.
It's like counting how many different "things" are involved in the connection.
a.​ Unary Relationship: Only one entity type participates. Imagine a "Person" entity where a
person can be married to another person (within the same "Person" entity). This is a degree 1
relationship.
b.​ Binary Relationship: Two entity types participate. This is the most common type. For
example, a "Student" entity can be related to a "Course" entity (students enroll in courses).
This is a degree 2 relationship.
c.​ Ternary Relationship: Three entity types participate. For example, a "Student," "Course,"
and "Professor" entity might be related in a "Student takes a course taught by a professor"
scenario. This is a degree 3 relationship.
d.​ N-ary Relationship: More than three entity types participate. These are less common and
can be more complex to represent.

Cardinality of a Relationship
Cardinality describes the number of instances of one entity that can be associated with instances of
another entity. It's like specifying the limits on how many connections can exist.
a.​ One-to-One (1:1): One instance of entity A is related to at most one instance of entity B, and
vice-versa. Think of a "Person" and their "Passport" - one person usually has one passport,
and one passport belongs to one person.
b.​ One-to-Many (1:M): One instance of entity A can be related to many instances of entity B,
but one instance of entity B is related to only one instance of entity A. For example, a
"Teacher" can teach many "Students," but a "Student" has one "Teacher" (in a specific class).
c.​ Many-to-One (M:1): Many instances of entity A can be related to one instance of entity B,
but one instance of entity B is related to many instances of entity A. This is the reverse of
one-to-many. For example, many "Students" can belong to one "Class," but one "Class" has
many "Students."

Bhavya Gupta (02814802723)

d.​ Many-to-Many (M:N): Many instances of entity A can be related to many instances of
entity B, and vice-versa. For example, many "Students" can enroll in many "Courses," and
one "Course" can have many "Students”.

E-R Diagram-
Entities-
Student, Faculty, Parents, Course, Subject, Director, Hod, Faculty_coordinator and Mentor.
Attributes-
1.​ Student– enrollment no, name, date of birth, address, contact no, email – id, qualifying exam rank,
Course, mentor_id
2.​ Parents– Parents name, contact no, email id, Profession
3.​ Faculty– Faculty name, contact no, email id
4.​ Course– course name, course_id, hod_id
5.​ Subject– subject name, subject_id, faculty_coordinator_id
6.​ Director– director name, director_id
7.​ Hod- hod name, hod_id, course_id
8.​ Faculty_coordinator– faculty_coordinator_id, faculty_coordinator_name, subject_id
9.​ Mentor— mentor_id, mentor_name

Relationships-
1.​ Student — has — Parents
2.​ Student — enrolled in — Course
3.​ Subject — belongs to — Course
4.​ Faculty_coordinator — coordinates — Subject
5.​ Hod — manages — course
6.​ Director — manages — hod
7.​ Mentor — mentors — Student
8.​ Faculty — teaches — Subject

Bhavya Gupta (02814802723)

Railway Management System

The Railway Management System is designed to efficiently manage train schedules, passenger bookings,
ticket payments, and cancellations. Below is a detailed description of the database, including its entities,
attributes, and relationships.

Entities- Stations, Trains, Passengers and Tickets.

Attributes-
1.​ Stations- stations_id (primary key, auto increment), name, location, state and zipcode.
2.​ Trains- train_id (primary key, auto increment), train_name, train_type (ex- Express, Passenger,
Freight), source_station_id (foreign key => stations.station_id), destination_station_id (foreign key
=> stations.station_id) and total_seats.
3.​ Passengers- passenger_id, name, age, gender, email, phone.
4.​ Tickets- ticket_id (primary key, auto increment), passenger_id (foreign key =>
passengers.passenger_id), train_id (foreign key => trains.train_id), source_station_id (foreign key =>
stations.station_id), destination_station_id (foreign key => stations.station_id), seat_number,
booking_date, travel_date, status, price

Relationships-
1.​ passengers (passenger_id) ---< tickets (passenger_id)
2.​ trains (train_id) ---< tickets (train_id)
3.​ stations (station_id) ---< trains (source_station_id, destination_station_id)
4.​ stations (station_id) ---< tickets (source_station_id, destination_station_id)

Bhavya Gupta (02814802723)

Program-2
Aim: Write queries for implementing DDL commands – CREATE, ALTER, DROP and TRUNCATE.
Software used: Maria DB
Theory:
DDL (Data definition language) commands-
1.​ CREATE: To create new tables or databases.

Syntax- CREATE DATABASE databasename;

CREATE TABLE table_name ( column1 datatype, column2 datatype, column3

datatype);

2.​ ALTER: To modify existing database structures.

Syntax- ALTER TABLE Customers ADD Email varchar(255);

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

3.​ DROP: To delete tables or databases.

Syntax- DROP DATABASE databasename;

DROP TABLE table_name;

4.​ TRUNCATE: To delete data inside the tables.

​ Syntax- TRUNCATE TABLE tablename;

Queries & Output:

Bhavya Gupta (02814802723)

Bhavya Gupta (02814802723)
Bhavya Gupta (02814802723)
Program-3
Aim: Write queries to apply the integrity constraints like Primary Key, Foreign key, Check, NOT NULL,
etc. to the tables.
Software used: Maria DB
Theory:
Constraints-
1.​ Primary Key- Ensures that a column (or set of columns) has unique and non-null values.
Syntax- ALTER TABLE tablename ADD CONSTRAINT pk_name PRIMARY KEY
(col_name);
2.​ Foreign Key- Enforces referential integrity between tables.
​ Syntax- ALTER TABLE tablename ADD CONSTRAIN fk_name FOREIGN KEY
(col_name) REFERENCES tablename(colname);
3.​ Check- Ensures that column values satisfy a specified condition.
​ Syntax- ALTER TABLE tablename ADD CONSTRAINT chk_name CHECK (condition);
4.​ Not Null- Ensures that a column cannot have NULL values.
​ Syntax- ALTER TABLE tablename MODIFY COLUMN colname datatype NOT NULL;
Queries & Output:

Bhavya Gupta (02814802723)

Bhavya Gupta (02814802723)
Program-4
Aim: Write queries for implementing DML commands – SELECT, INSERT, UPDATE and DELETE.
Software used: Maria DB
Theory:
DML (Data Manipulation Language) commands-
1.​ SELECT- The SELECT statement is used to retrieve data from a database.
​ Syntax- SELECT column1, column2, ... FROM table_name WHERE condition;
2.​ INSERT- It is used to add new records into a table.
​ ​ Syntax- INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
3.​ UPDATE- It modifies existing records in a table.
​ Syntax- UPDATE table_name

SET column1 = value1, column2 = value2

WHERE condition;

4.​ DELETE- It removes records from a table.

​ Syntax- DELETE FROM table_name WHERE condition;

Queries & Output:

Bhavya Gupta (02814802723)

Bhavya Gupta (02814802723)
Program-5
Aim: Write the queries for implementing Built-in functions, GROUP BY, HAVING and ORDER BY.
Software used: Maria DB
Theory:
Built-in Functions-
1.​ Aggregate Functions–
​ Syntax- SELECT SUM(column_name) AS total_sum FROM table_name;
2.​ String Functions-
​ Syntax- SELECT CONCAT(column1, ' ', column2) AS combined_string FROM table_name;
3.​ Date Functions-
​ ​ Syntax- SELECT CURDATE() AS current_date, NOW() AS current_datetime;
GROUP BY Clause- The GROUP BY clause groups rows that have the same values into summary rows.

​ ​ Syntax- SELECT column_name, COUNT(*) AS count FROM table_name

GROUP BY column_name;

HAVING Clause- It filters groups after applying GROUP BY clause.

​ ​ Syntax- SELECT column_name, COUNT(*) AS count FROM table_name

GROUP BY column_name HAVING COUNT(*) > some_value;

ORDER BY Clause- It sorts the result set by one or more columns.

​ ​
​ ​ Syntax- SELECT column_name1, column_name2 FROM table_name
ORDER BY column_name1 ASC/DESC;

Queries & Output:

Bhavya Gupta (02814802723)

Bhavya Gupta (02814802723)
Program-6
Aim: Write the queries to implement the joins.
Software used: Maria DB
Theory:
1.​ Cross Join- A cross join returns the Cartesian product of two tables, meaning it combines all rows
from one table with all rows from another table. This can result in a large number of rows if both
tables have many rows.
​ Syntax- SELECT * FROM TableA CROSS JOIN TableB;
2.​ Natural Join- A natural join automatically joins tables based on columns with the same name and
compatible data types. It eliminates the need for explicitly specifying the join condition.
​ Syntax- SELECT * FROM TableA NATURAL JOIN TableB;
3.​ Outer Join-
a.​ Left Outer Join- A left outer join returns all rows from the left table and the matched rows
from the right table. If there are no matches, the result is NULL on the side of the right table.
​ Syntax- SELECT * FROM TableA LEFT OUTER JOIN TableB
ON TableA.common_column = TableB.common_column;
b.​ Right Outer Join- A right outer join returns all rows from the right table and the matched
rows from the left table. If there are no matches, the result is NULL on the side of the left
table.
​ Syntax- SELECT * FROM TableA RIGHT OUTER JOIN TableB
ON TableA.common_column = TableB.common_column;
c.​ Full Outer Join- A full outer join returns all rows when there is a match in either left or right
table. If there is no match, the result is NULL on the side where there is no match.
​ Syntax- SELECT * FROM TableA FULL OUTER JOIN TableB
​ ON TableA.common_column = TableB.common_column;
Queries & Output:

Bhavya Gupta (02814802723)

Bhavya Gupta (02814802723)