Shri Vaishnav Vidyapeeth

Vishwavidyalaya
Shri Vaishnav Institute of Information Technology

Department of Computer Science and Engineering

Course Name – Database Management System

Course Code – BTCS401N

II Year IV Sem
Section - C

Submitted To : Submitted By : Kanishq Mukati

Prof Virendra Dani Enrollment No.:21100BTCSE09857

Shri Vaishnav Vidyapeeth Vishwavidyalaya
 CONTENTS
S Date of Name of Experiments Page Date of Sign/Remark
No. Experiment No. Submission
1. Installation of MySQL 8.0.32 and 1
Study of database language.
2. Design a Database and create 14
required Tables for Employee
Database contains
Employee_details and
Employee_personal_info .
3. Write a SQL statement for table 15
and record handling like
implementing INSERT statement,
Using SELECT and INSERT
together, DELETE, UPDATE,
TRUNCATE statements, and
DROP, ALTER statements.
4. Apply the constraints Primary Key, 22
Foreign key, NOT NULL and
Unique key to the tables of
Employee’s database.
5. Write the queries for Retrieving 26
Data from a Database Using the
WHERE clause, Using Logical
Operators in the WHERE clause,
Using IN, BETWEEN, LIKE,
ORDER BY, GROUP BY and
HAVING Clause, and Combining
Tables Using JOINS.
6. Write the query for implementing 34
the following aggregate functions:
MAX (), MIN (), AVG (),
COUNT (), SUM () on bank
database.
7. Write the query to implement the 39
concept of different Integrity
 Constraints.
8. Write the query to create the VIEWS (1 and 43
multiple tables ), replace views and delete
views on collage database.
9. Write the query to create the Stored 45
Procedure in DBMS.
10 Write the query to create the 46
Triggers in DBMS.
DATA BASE MANAGEMENT SYSTEM BTCS401N
Experiment No. 1

[1]
DATA BASE MANAGEMENT SYSTEM BTCS401N

1. Objectives :- Installation of MySQL 8.0.32 and Study

of database language.

2. Outcome :- To achieve secure & Reliable database Management. MySQL prevents your applicaton’s
sensitive data from cyber attacks with data protection feature.

3. Nomenclature, theory with self-assessment questionnaire:-

3.1 Nomenclature:
; or \g Command ends with.

\h For help
To clear the current input statement.
\c

3.2 Solution:

SQL stands for Structural Query Language, and SQL is used for storing, manipulation, and retrieving data from
the database.
The SQL(Structural Query language) was first created in the 1970s by IBM researchers Raymond Boyce and
Donald Chamberlin. The Query language, known then as SEQUEL, was created following the publishing of
Edgar Frank Todd's paper, In 1970, A Relational Model of Data for Large Shared Data Banks.

Step 5 : Click Next.

[2]
 Step 33::Click
Choosing
on the
theMySQL
Setup type
installer
and click
MSI Next.
Go to Download Page >
Downloading MySQL

Step 1: Open Google and type Download MySQL and Click on MySQL Community Downloads
DATA BASE MANAGEMENT SYSTEM BTCS401N

Note: Once the Downloading is completed, then double-click on that and install it on the local system.
Step 4 :Click
Step 2: Clickon
Next
MySQL Community Server.
.
Step 4: Select the OS and click on MSI Installer community

Installation of MySQL

Step 1: Double-Click on Downloaded Application.

Step 6 : Click on Yes.
Step 2 : After clicking on the application we will get a window like below

Step 7 : Click on Execute.

[3]
DATA BASE MANAGEMENT SYSTEM BTCS401N

Step 9 : Click Next.

Step 11 : If you had install then enter current password.
Then Click on Check.
If you first time download it then click on Add User then write User name and set Password.

Step 12 : Click on Next.

[4]
DATA BASE MANAGEMENT SYSTEM BTCS401N
Step 13 : Check the password and Click Next.

5. Advantages

1. Open Source: MySQL is an open-source database, which means it is free to use, distribute, and modify.
This makes it a cost-effective solution for businesses of all sizes.

2. Scalability: MySQL is designed to handle large datasets and can scale to support high traffic applications.
It can handle multiple users accessing the database simultaneously without compromising performance.

3. High Performance: MySQL is optimized for speed and can handle large volumes of data efficiently. It has
a fast response time for read and write operations and supports high-speed transactions.

4. Cross-Platform Compatibility: MySQL is platform-independent and can run on different operating

systems like Windows, Linux, macOS, and Unix. This makes it easier to migrate the database between
different systems.

5. Data Security: MySQL provides advanced security features like encryption, access control, and
authentication to protect data from unauthorized access and attacks.

6. Reliability: MySQL has a high level of reliability and data integrity. It has features like backup and
recovery, replication, and clustering to ensure that data is always available and consistent.

7. Support and Community: MySQL has a large and active community of developers and users who
provide support, share knowledge, and contribute to the development of the database. Additionally,
MySQL is backed by Oracle, a leading technology company, which provides professional support and
services for businesses.

6. Quiz & Viva Questions

6.1 Quiz:
1. What is MySQL ?
a. A programming language
b. A relational database management system
c. A server-side scripting language
d. A markup language
Ans b. A relational database management system

2. What does SQL stand for ?

a. Structured Query Language
b. Structured Question Language
c. Structured Queue Language
d. Structured Programming Language
Ans a. Structured Query Language
[5]
 Step 15 : Click on Finish.

DATA BASE MANAGEMENT SYSTEM BTCS401N

3. Which of the following is not a data type in MySQL ?

a. INT
b. DECIMAL
c. FLOAT
d. STRING
Ans d. STRING

4. What is the default port number for MySQL ?

a. 3306
b. 80
c. 8080
d. 443
Ans a. 3306

Step 16 : After the which

5. In successful
year installation
and by whom of MySQL, twowas
the MySQL windows will ?open.
developed
✓ MySQL Shell a. Sun Microsystem,1995
✓ MySQL WorkBench b. Guido van Rossum,1991
c. Oracle Corporation,1995
d. Tim Berners-Lee,1993
Ans c. Oracle Corporation,1995

***************************************************************************************************

[6]
DATA BASE MANAGEMENT SYSTEM BTCS401N

Experiment No. 2

1. Objective :- Design a Database and create required Tables for Employee Database contains
Employee_details and Employee_personal_info .

 Creating a Database “Employee”.

 Creating Table “Employee_details” in Employee database.

 Creating Table “Employee_personal_info” in Employee database.

[7]
DATA BASE MANAGEMENT SYSTEM BTCS401N

[8]
DATA BASE MANAGEMENT SYSTEM BTCS401N
Experiment No. 3

[9]
DATA BASE MANAGEMENT SYSTEM BTCS401N

1.Objectives :-
Write a SQL statement for table and record handling like implementing INSERT statement, Using SELECT
and INSERT together, DELETE, UPDATE, TRUNCATE statements, and DROP, ALTER statements.
2. Outcome :-
To provide a centralized and organized system for managing and maintaining data related to a employee . It enables
easy access to data for administrative purposes, including employee records.

3. Nomenclature, theory with self-assessment questionnaire:-

3.1 Nomenclature:
* Select all data
used to modify the structure of an
alter existing table

3.2 Solution:
Table and record handling are essential operations in managing data within a database system. Here are some
common SQL statements for handling tables and records:
1. INSERT statement: The INSERT statement is used to add new records to a table. It specifies the table
name and the values for each column in the new record.

2. SELECT and INSERT together: The SELECT and INSERT statements can be used together to insert
data from one table into another. The SELECT statement specifies the data to be inserted, and the INSERT
statement specifies the target table.

3. DELETE statement: The DELETE statement is used to remove one or more records from a table. It
specifies the table name and a condition that determines which records to delete.

4. UPDATE statement: The UPDATE statement is used to modify one or more records in a table. It specifies
the table name, the columns to be updated, and a condition that determines which records to update.

5. TRUNCATE statement: The TRUNCATE statement is used to remove all records from a table. Unlike the
DELETE statement, TRUNCATE does not log each deleted row and is faster and less resource-intensive.

6. DROP statement: The DROP statement is used to remove a table from the database. It permanently
deletes all data and structure associated with the table.

7. ALTER statements: The ALTER statement is used to modify the structure of a table. It can be used to add
or remove columns, change column data types, and modify constraints.

4. INSERTING data in table Employee_details .

[10]
DATA BASE MANAGEMENT SYSTEM BTCS401N

4.1 View the table using SELECT query.

5. INSERTING data in table Employee_personal_info.

[11]
DATA BASE MANAGEMENT SYSTEM BTCS401N

5.1 View the table using SELECT query.

6. UPDATE data in table Employee_personal_info.

[12]
DATA BASE MANAGEMENT SYSTEM BTCS401N

7. ALTER data in table Employee_personal_info.

[13]
DATA BASE MANAGEMENT SYSTEM BTCS401N

8.DELETE data in table Employee_personal_info.

9.TRUNCATE the data of table Employee_personal_info.

[14]
DATA BASE MANAGEMENT SYSTEM BTCS401N

10. DROP table Employee_personal_info.

[15]
DATA BASE MANAGEMENT SYSTEM BTCS401N
11. Quiz & Viva Question
11.1 Quiz:
1. Which SQL command is used to retrieve data from a table? a. INSERT
b. SELECT
c. DELETE
d. DROP
Ans b. SELECT
2. Which SQL command is used to remove data from a table? a. INSERT
b. SELECT
c. DELETE
d. DROP

Ans: c. DELETE

3. Which SQL command is used to remove all data from a table?

a. INSERT
b. SELECT
c. DELETE
d. TRUNCATE

Ans: d. TRUNCATE

4. Which SQL command is used to delete an entire table?

a. INSERT
b. SELECT
c. DELETE
d. DROP
Ans d. DROP

5. Which SQL command is used to modify data in a table?

a. UPDATE
b. SELECT
c. TRUNCATE
d. DROP
Ans a. UPDATE

11.2 Viva

1. Which SQL command is used to remove both the data and structure of a table? --------› DROP
2. Which SQL command is used to modify the structure of an existing table? --------› ALTER
3. Which SQL command is used to permanently delete a table from the database? --------› DROP

***************************************************************************************************

[16]
DATA BASE MANAGEMENT SYSTEM BTCS401N
Experiment No. 4

1.Objectives :-
Apply the constraints Primary Key, Foreign key, NOT NULL and Unique key to the tables of Employee’s
database.

2.Outcome :- By applying these constraints, the database management system can enforce data integrity rules
and prevent inconsistent, incomplete, or invalid data from being inserted into the tables. This, in turn, ensures data
accuracy and reliability, improves data quality, and facilitates effective data management and analysis.

3. Nomenclature, theory with self-assessment questionnaire:-

3.1 Nomenclature:

add To add the constraints

use
Database Changed
<database_name>

[17]
DATA BASE MANAGEMENT SYSTEM BTCS401N

3.2 Solution:
In a database, primary, foreign, unique key constraints, and NOT NULL constraints are used to ensure data
accuracy and reliability.

• Primary key constraint is applied to a column or a set of columns in a table to ensure that each record is
uniquely identified by a specific value. It ensures that no two records in the table have the same value for
the specified column(s), and it is used as a reference for related tables.

• Foreign key constraint is applied to a column or a set of columns in a table to ensure that the values in the
column(s) correspond to the values in another table's column(s). It helps in establishing a relationship
between tables and maintaining referential integrity.

• Unique key constraint is applied to a column or a set of columns in a table to ensure that no two records
in the table have the same value for the specified column(s). It helps in avoiding duplicate data and
maintaining data consistency.

• NOT NULL constraint is applied to a column in a table to ensure that the column cannot contain NULL
values. It helps in preventing data manipulation errors and ensuring data accuracy.

[18]
DATA BASE MANAGEMENT SYSTEM BTCS401N

4.Applying PRIMARY KEY on Employee_details’s Emp_id

5.Applying Foreign key in table Employee_personal_info of Emp_id in Employee_details.

6.Applying NOTNULL constraint in Employee’s Database.

[19]
DATA BASE MANAGEMENT SYSTEM BTCS401N

7.Applying UNIQUE constraint in Employee’s Database.

8. Results
The constraints Primary Key, Foreign Key, NOT NULL, and Unique Key can be applied to the tables of a
database to ensure data integrity and consistency.
• Primary Key uniquely identifies each row, ensuring that values are unique and not null.
• Foreign Key references the primary key of another table, ensuring values match.
• NOT NULL ensures a column cannot contain null values.
• Unique Key ensures values in a column are unique.

8.1 Advantages
1. Data Integrity: These constraints help to ensure data integrity by preventing the entry of invalid or
inconsistent data into the tables. This, in turn, helps to improve the accuracy and reliability of the data stored
in the database.
2. Data Consistency: By enforcing constraints, data consistency is maintained throughout the database. Data
consistency is essential in ensuring that the data is always correct, especially when different people are
accessing the data.
3. Faster Queries: Indexing is automatically applied to columns with Primary Key and Unique Key constraints,
leading to faster queries when retrieving data.
4. Improved Performance: By enforcing constraints, the database's performance is improved by reducing the
number of unnecessary queries or database operations that need to be performed.
5. Better Scalability: As the database grows in size, the constraints ensure that the data remains structured and
organized, making it easier to scale and maintain the database over time.

8. Quiz & Viva Questions

8.5 Quiz:
1. Can a column have both a Primary Key and a Foreign Key constraint?
a. YES
b. NO

Ans a. YES
2. Which database constraint ensures that a column cannot contain null values?
a. Primary Key
b. Foreign Key
c. NOT NULL
d. Unique Key

Ans: c. NOT NULL

[20]
DATA BASE MANAGEMENT SYSTEM BTCS401N

3. What is a unique key in MySQL?

a. A key that is unique across multiple tables
b. A key that identifies a unique row in a table
c. A key that can be null
d. A key that ensures that each value in a column is unique
Ans: d. A key that ensures that each value in a column is unique

4. Can a table have multiple foreign keys or unique key in MySQL? a. YES
b. NO

Ans a. YES

5. What is the purpose of using primary, foreign, and unique keys in MySQL?
a. To improve performance of database queries
b. To ensure data integrity and consistency
c. To enforce security and access control
d. To reduce the amount of storage required for the database
Ans b. To ensure data integrity and consistency.

***************************************************************************************************

Experiment No. 5

1. Objectives :-

Experiment No. 5

2. Objectives :-
Write the queries for Retrieving Data from a Database Using the WHERE clause, Using Logical Operators in
the WHERE clause, Using IN, BETWEEN, LIKE, ORDER BY, GROUP BY and HAVING Clause, and
Combining Tables Using JOINS.

2.Outcome :- By understanding and utilizing these SQL clauses and statements, users can effectively retrieve,
filter, sort, and manipulate data within a database.

3. Nomenclature, theory with self-assessment questionnaire:-

[21]
DATA BASE MANAGEMENT SYSTEM BTCS401N
3.1 Nomenclature:

1. where subset of the data that meets the specified condition(s)

in used to match a value against a set of possible values

between subset of the data that falls within a specific range

like used to retrieve data based on a pattern match

order by used to sort the results in ascending or descending order.

group by and having clause grouped and filtered result set

join used to combine rows from two or more tables based on a related
column

WHERE Clause: The WHERE clause is used to filter the results of a SELECT statement based on one or more
conditions. The outcome of using the WHERE clause is a subset of the data that meets the specified
condition(s).

2. Logical Operators in WHERE Clause: Logical operators, such as AND, OR, and NOT, can be used to
create more complex conditions in the WHERE clause. The result of using logical operators in the
WHERE clause is a subset of the data that meets all the specified conditions.

3. IN Clause: The IN clause is used to match a value against a set of possible values. The outcome of using
the IN clause is a subset of the data where a particular column value matches one of the specified values.

[22]
DATA BASE MANAGEMENT SYSTEM BTCS401N
4. BETWEEN Clause: The BETWEEN clause is used to retrieve data between two values, inclusive of the
values themselves. The outcome of using the BETWEEN clause is a subset of the data that falls within a
specific range.

5. LIKE Clause: The LIKE clause is used to retrieve data based on a pattern match. The outcome of using the
LIKE clause is a subset of the data where a particular column value contains a certain pattern.

6. ORDER BY Clause: The ORDER BY clause is used to sort the results of a SELECT statement in
ascending or descending order. The outcome of using the ORDER BY clause is a sorted result set.

7. GROUP BY and HAVING Clause: The GROUP BY clause is used to group the results of a SELECT
statement by one or more columns, and the HAVING clause is used to filter the results of a GROUP BY
statement based on one or more conditions. The outcome of using GROUP BY and HAVING clauses is a
grouped and filtered result set.

8. JOIN Clause: The JOIN clause is used to combine rows from two or more tables based on a related
column. The outcome of using a JOIN clause is a result set that combines data from multiple tables.

4.Retrieving data using WHERE clause.

[23]
DATA BASE MANAGEMENT SYSTEM BTCS401N

5. Retrieving data using IN clause.

6. Retrieving data using BETWEEN clause.

7. Retrieving data using LIKE clause.

[24]
DATA BASE MANAGEMENT SYSTEM BTCS401N

8. Retrieving data using ORDER BY clause.

9. Retrieving data using GROUP BY AND HAVING clause.

10. Retrieving data from multiple relations using JOIN clause.

[25]
DATA BASE MANAGEMENT SYSTEM BTCS401N

11. Results
The use of these SQL clauses allows for a great deal of flexibility in querying and filtering data from a database,
and can help users to obtain the specific results they are looking for. WHERE Clause: The WHERE clause is a
powerful tool for filtering data in a SELECT statement. It allows users to retrieve only the data that meets
specific criteria, which can save time and improve the efficiency of the query.

1. Logical Operators: Using logical operators such as AND, OR, and NOT in the WHERE clause can
provide even more flexibility for filtering data, allowing users to create more complex conditions and
obtain more precise results.

2. IN, BETWEEN, and LIKE Clauses: These clauses provide additional methods for filtering data,
allowing users to match values, specify ranges of values, and perform pattern matching to obtain specific
results.

3. ORDER BY Clause: The ORDER BY clause allows users to sort data in ascending or descending order,
which can make it easier to analyze and work with the data.

4. GROUP BY and HAVING Clauses: The GROUP BY and HAVING clauses allow users to group data
based on one or more columns and perform aggregate functions on the grouped data, which can provide
insights into patterns and trends within the data.

5. JOINS: The use of JOIN clauses can combine data from multiple tables based on related columns,
allowing users to retrieve data that is spread across multiple tables in a single query. This can save time
and improve the efficiency of the query.

12.Quiz & Viva Questions

12.1 Quiz:
1.What is the purpose of the WHERE clause in MySQL?
a. to specify the tables being queried
b. to define the columns being returned
c. to filter the rows returned by a query
d. to order the results of a query
Ans c. To filter the rows returned by a query

2. Which operator is used to search for a pattern within a string in a WHERE clause? a. IN
b. BETWEEN
c. LIKE
d. ORDER BY

Ans: c. LIKE

[26]
DATA BASE MANAGEMENT SYSTEM BTCS401N

3. Which clause is used to sort the results of a query in a specific order?

a. WHERE
b. IN
c. ORDER BY
d. GROUP BY
Ans: c. ORDER BY

4. What is the purpose of the GROUP BY clause in a MySQL SELECT statement?

a. To specify which columns to select
b. To specify the values to insert into a table
c. To group rows based on a specific column or set of columns
d. To specify the order in which to return rows
Ans c. To group rows based on a specific column or set of columns.

5. Which operator is used to check if a value falls within a range in a WHERE clause? a. IN
b. BETWEEN
c. LIKE
d. ORDER BY
Ans b. BETWEEN

6. What is the purpose of the JOIN clause in MySQL?

a. To define the columns being returned
b. To filter the rows returned by a query
c. To combine rows from two or more tables into a single result set
d. To order the results of a query
Ans: d. To combine rows from two or more tables into a single result set

7. Which type of JOIN returns only the matching rows from both tables?
a INNER JOIN b.
LEFT JOIN
c. RIGHT JOIN
d. FULL OUTER JOIN
Ans: c. INNER JOIN

8. Which type of JOIN returns all rows from the left table and matching rows from the right table?
a. INNER JOIN
b. LEFT JOIN
c. RIGHT JOIN
d. FULL OUTER JOIN
Ans: b. LEFT JOIN

[27]
DATA BASE MANAGEMENT SYSTEM BTCS401N
9. Which type of JOIN returns all rows from the right table and matching rows from the left table?
a. INNER JOIN
b. LEFT JOIN
c. RIGHT JOIN
d. FULL OUTER JOIN
Ans: c. RIGHT JOIN

10. Which type of JOIN returns all rows from both tables, with NULL values in the columns
where there is no match?
a. INNER JOIN
b. LEFT JOIN
c. RIGHT JOIN
d. FULL OUTER JOIN
Ans: d. FULL OUTER JOIN

***************************************************************************************************

[28]
DATA BASE MANAGEMENT SYSTEM BTCS401N

Experiment No. 6

1. Objectives :-
Write the query for implementing the following aggregate functions: MAX (), MIN (), AVG (), COUNT (),
SUM () on bank database.

2. Outcome :- The outcome of implementing the aggregate functions MAX(), MIN(), AVG(), COUNT(),
and SUM() on a database can provide valuable insights and information about the data stored in the database.

3. Nomenclature, theory with self-assessment questionnaire:-

3.1 Nomenclature:
MAX returns the maximum value in a column

MIN returns the minimum value in a column

AVG returns the average value of a column

COUNT returns the number of rows in a table that meet certain conditions

SUM returns the sum of values in a column

3.2 Solution:
[29]
DATA BASE MANAGEMENT SYSTEM BTCS401N
1. MAX: This function returns the maximum value of a selected column. For example, if you want to find the
highest salary in an employee table, you can use the MAX function.

2. MIN: This function returns the minimum value of a selected column. For example, if you want to find the
lowest temperature recorded in a weather table, you can use the MIN function.

3. AVG: This function returns the average value of a selected column. For example, if you want to find the
average age of all customers in a customer table, you can use the AVG function.

4. COUNT: This function returns the number of rows that meet a specific condition. For example, if you want
to count the number of employees in a department, you can use the COUNT function.

5. SUM: This function returns the total sum of a selected column. For example, if you want to find the total
sales for a particular product in a sales table, you can use the SUM function.

4. Accessing data using MAX() Function.

5. Accessing data using MIN() Function.

6. Accessing data using AVG() Function.

[30]
DATA BASE MANAGEMENT SYSTEM BTCS401N

7. Accessing data using COUNT() Function.

8. Accessing data using SUM() Function.

9.Results
These aggregate functions are essential in data analysis and can provide valuable insights into a database. By using
these functions, you can quickly retrieve useful information from large datasets and make informed decisions based
on the results.

10.Advantages

1. Summarize data: Aggregate functions are used to summarize data in a table by performing calculations
such as sum, average, count, min, and max. This makes it easier to understand and analyze large data sets.

[31]
DATA BASE MANAGEMENT SYSTEM BTCS401N

2. Easy to use: These functions are simple to use and require only a basic understanding of SQL. They allow
you to retrieve summary information from your data without having to write complex queries.

3. Efficient: Since aggregate functions operate on entire sets of data, they are often more efficient than using
a series of individual queries to retrieve the same information.

4. Consistent results: Aggregate functions always return consistent results, regardless of the order of the
rows in a table or the order in which the functions are applied.

5. Grouping: Aggregate functions can be used in conjunction with the GROUP BY clause to group data by
one or more columns. This allows you to perform calculations on subsets of data, such as calculating the
average salary by department.

11. Quiz & Viva Question

11.1 Quiz:

1. What is an aggregate function in MySQL?

a. A function that performs calculations on individual rows of data
b. A function that performs calculations on groups of rows in a table
c. A function that sorts data in a table
d. A function that returns a single row of data
Ans: b. A function that performs calculations on groups of rows in a table

2. Which of the following is not an aggregate function in MySQL?

a. SUM()
b. COUNT()
c. ROUND()
d. MAX()
Ans: c. ROUND()

3. Which aggregate function would you use to find the average salary of all employees in a company?
a. MIN()
b. MAX()
c. SUM()
d. AVG()
Ans: d. AVG()

4. Which of the following is an example of using the COUNT() function?

a. COUNT(*) FROM employees
b. COUNT(salary) FROM employees WHERE salary > 50000
c. COUNT(DISTINCT department) FROM employees
[32]
DATA BASE MANAGEMENT SYSTEM BTCS401N
d. All of the above
Ans: d. All of the above

5. Which aggregate function would you use to find the highest salary in a table? a. MAX()
b. MIN()
c. AVG()
d. COUNT()
Ans: a. MAX()

***************************************************************************************************

[33]
DATA BASE MANAGEMENT SYSTEM BTCS401N

Experiment No. 7

1.Objective-:
Write the query to implement the concept of different Integrity Constraints.

2.Integrity Constraints
Integrity constraints are a set of rules. It is used to maintain the quality of information.
Integrity constraints ensure that the data insertion, updating, and other processes have to be
performed in such a way that data integrity is not affected.
Thus, integrity constraint is used to guard against accidental damage to the database.
2.1 Types of Integrity Constraint

1. Domain constraints
Domain constraints can be defined as the definition of a valid set of values for an attribute.
The data type of domain includes string, character, integer, time, date, currency, etc. The value of
the attribute must be available in the corresponding domain.

[34]
DATA BASE MANAGEMENT SYSTEM BTCS401N
Example:

QUERY: Shows error because of domain constraint

2. Entity integrity constraints

The entity integrity constraint states that primary key value can't be null.
This is because the primary key value is used to identify individual rows in relation and if the
primary key has a null value, then we can't identify those rows.
A table can contain a null value other than the primary key field.
Example:

QUERY: Shows error because of Entity constraint.

[35]
DATA BASE MANAGEMENT SYSTEM BTCS401N

3. Referential Integrity Constraints

A referential integrity constraint is specified between two tables.
In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of
Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.
Example:

QUERY: Shows error because of Referential constraint.

[36]
DATA BASE MANAGEMENT SYSTEM BTCS401N

4. Key constraints
Keys are the entity set that is used to identify an entity within its entity set uniquely.
An entity set can have multiple keys, but out of which one key will be the primary key. A primary
key can contain a unique and null value in the relational table.
Example:

QUERY: Shows error because of Key constraint.

[37]
DATA BASE MANAGEMENT SYSTEM BTCS401N

[38]
DATA BASE MANAGEMENT SYSTEM BTCS401N

Experiment No. 8

1.Objective-:
Write the query to create the VIEWS (1 and multiple tables ), replace views and delete views on
collage database.

2. Views in SQL
1.Views in SQL are considered as a virtual table. A view also contains rows and columns.
2.To create the view, we can select the fields from one or more tables present in the database.
3.A view can either have specific rows based on certain condition or all the rows of a table.

2.1 Advantages of View:

Complexity: Views help to reduce the complexity. Different views can be created on the same
base table for different users.
Security: It increases the security by excluding the sensitive information from the view.
Query Simplicity: It helps to simplify commands from the user. A view can draw data from
several different tables and present it as a single table.

2.2 Disadvantages of View:

The DML statements which can be performed on a view created using single base table have
certain restrictions are:
1.You cannot INSERT if the base table has any not null column that do not appear in view.
2.You cannot INSERT or UPDATE if any of the column referenced in the INSERT or UPDATE
contains group functions or columns defined by expression.
3.You can't execute INSERT, UPDATE, DELETE statements on a view if with read only option is
enabled.

[39]
DATA BASE MANAGEMENT SYSTEM BTCS401N

3. Creating Views from a single table.

4. Creating Views from Multiple Table.

[40]
DATA BASE MANAGEMENT SYSTEM BTCS401N

Experiment No. 9

1.Objective-:
Write the query to create the Stored Procedure in DBMS.

2.Stored Procedure
A stored procedure in Structured Query Language is a group of logical statements stored in the
database for performing a particular task.
It is a subprogram consisting of a name, a list of parameters, and Transact-SQL statements.
Any user can store the stored procedure as a named object in the SQL database and can call it by
using triggers, other procedures, and other programming applications such as Java, PHP, R, C#,
Python, etc.
SQL database creates an execution plan and stores it in the cache memory when the stored
procedure is called for the first time. The plan is reused by SQL Server, which executes the stored
procedure quickly with reliable performance

3. Creating a Stored Procedure.

[41]
DATA BASE MANAGEMENT SYSTEM BTCS401N
Experiment No. 10

1.Objective-:
Write the query to create the Triggers in DBMS.

2. Trigger: A trigger is a stored procedure in database which automatically invokes whenever a

special event in the database occurs. For example, a trigger can be invoked when a row is inserted
into a specified table or when certain table columns are being updated.

3. Creating a Trigger in dbms.

[42]
DATA BASE MANAGEMENT SYSTEM BTCS401N

Experiment No. -11

1. TITLE: - Write a query to implement various transaction commands on employee data content department
name department number and location.
2. Objective: - To understand the use of the creating the users and their role. Using GRANT and
REVOKE operations.
3. Outcomes: - Must be able to Create and use the users and role using the GRANT and REVOKE operation.
4. Nomenclature, theory with self-assessment questionnaire:

1.Nomenclature: N/A

2.Solution:

 Transactions: In MySQL, you can use transaction commands to manage the execution of

multiple database statements as a single unit of work. Here are the transaction commands commonly

used in MySQL:

 COMMIT: Commits the current transaction, making all the changes made within the transaction

permanent. The changes are visible to other database sessions.

 ROLLBACK: Rolls back the current transaction, undoing all the changes made within the

transaction. It restores the database to the state before the transaction began.

 SAVEPOINT: Sets a named marker within a transaction. It allows you to create points to which you

can roll back later if needed, without rolling back the entire transaction.

These commands enable you to control the atomicity, consistency, isolation, and durability (ACID) properties

of transactions in MySQL. They help ensure data integrity and provide a way to handle errors and recover

from unexpected situations.

5. Assumptions: N/A
6. Dependencies: N/A
[43]
DATA BASE MANAGEMENT SYSTEM BTCS401N

7. Code/ Pseudo Code:

[44]
DATA BASE MANAGEMENT SYSTEM BTCS401N

1.2 Assumptions:
Not Applicable
1.3 Dependencies
Not Applicable

1.4 Results
1.4.1 Test Case :

[45]
DATA BASE MANAGEMENT SYSTEM BTCS401N

1.4.2 Result Analysis

1.4.2.1 Advantages
1.4.2.2 Issues
1.5 References:

Lab Assignment: In this regression task you have to predict the percentage of marks that a student is
expected to score based upon the number of hours they studied. This is a simple linear regression task as it
involves just two variables.

2. Quiz & Viva Questions

Quiz:
1.Procedure cannot be created in Resource database.
a) System
b) User defined
c) Extended
d) All of the mentioned

2. Point out the correct statement.

a) Stored procedures assist in achieving consistent implementation of logic across
applications.

b)A stored procedure is a group of Transact-SQL statements compiled into a single

execution plan
c) Stored procedures can also improve performance
d) All of the mentioned

3. Which of the following is a system procedure?

a) sp_changeowner
b) sp_owner
c) sp_change
d) all of the mentioned

Viva Questions:
1. What is a stored procedure or procedure in SQL Server?
Ans. In a database management system (DBMS), a stored procedure is a precompiled
set of Structured
Query Language (SQL) statements with an assigned name and directly saved in the
database.
[46]
DATA BASE MANAGEMENT SYSTEM BTCS401N

2. What is the syntax of stored procedure in SQL?

Ans. The syntax for creating a stored procedure is:

DELIMITER //

CREATE PROCEDURE procedure_name ([parameter1 datatype [, parameter2 datatype

[, ...]]])
[characteristic ...]
BEGIN
-- SQL statements
END //

3. What are the advantages of using a stored procedure?

Ans.
 Security is improved and the user access to the stored procedure is managed by
the administrator.
 Development cost reduces and reliability increases.
 It improves the performance of the database.

 These are used to encapsulate the logic and hence the code can be changed
without affecting the clients.
 The access to other database objects becomes more secure.
 SQL injection attacks can be avoided using this.

4. How many types of stored procedure are there in SQL?

Ans. SQL Server categorizes the stored procedures mainly in two types:
 User-defined Stored Procedures.
 System Stored Procedures.

[47]
DATA BASE MANAGEMENT SYSTEM BTCS401N

Experiment 12

3. Title:- Write a PL/SQL code for stored Procedure on any above database.
4. Outcome:- Must be able to write a PL/SQL code for stored Procedure on any database.
5. Objectives
Understand the basic commands and working of MYSQL.

6. Nomenclature, theory with self-assessment questionnaire:-

4.1 Nomenclature:

N/A N/A

N/A N/A

N/A N/A

N/A N/A

6.2 Solution:
STORED PROCEDURE
A stored procedure is a set of SQL statements that are precompiled and stored in the database. It allows
you to group and execute multiple SQL statements as a single unit. Stored procedures can accept input
parameters, perform calculations, execute conditional logic, and return results.

Here's an example of creating a stored procedure in MySQL:

DELIMITER //
CREATE PROCEDURE procedure_name ([parameter1 datatype [, parameter2 datatype [, ...]]])
[characteristic ...]
BEGIN
-- SQL statements
END ;
//

Let's break down the syntax:

DELIMITER //: This command changes the delimiter temporarily to // instead of the default ;. It is
necessary because stored procedures can contain multiple SQL statements, and the default delimiter ;
would prematurely end the procedure definition.

[48]
DATA BASE MANAGEMENT SYSTEM BTCS401N
CREATE PROCEDURE procedure_name: This is where you specify the name of the stored
procedure you want to create.

[parameter1 datatype [, parameter2 datatype [, ...]]]: You can specify input parameters for the stored
procedure. Each parameter consists of a name and a data type. Parameters are optional, so you can omit
them if your procedure doesn't require any inputs.

[characteristic ...]: This section allows you to define various characteristics of the stored procedure,
such as DETERMINISTIC, CONTAINS SQL, SQL DATA ACCESS, and more. These characteristics
control the behavior and optimization of the stored procedure.

BEGIN and END: These keywords enclose the body of the stored procedure, where you write the SQL
statements that make up the logic of the procedure.

-- SQL statements: This is where you write the actual SQL statements that define the functionality of
the stored procedure. You can include any valid SQL statements within the procedure, such as SELECT,
INSERT, UPDATE, and DELETE.

To execute the stored procedure, you can use the CALL statement:

Call procedure_name();

[49]
DATA BASE MANAGEMENT SYSTEM BTCS401N

6.3 Assumptions:
Not Applicable
6.4 Dependencies
Not Applicable

6.5 Results
6.5.1 Test Case
Input: 4 Output: 6
Input: 7 Output: 8
6.5.2 Result Analysis
6.5.2.1 Advantages
6.5.2.2 Issues
6.6 References:

7. Lab Assignment: In this regression task you have to predict the percentage of marks that a student is expected
to score based upon the number of hours they studied. This is a simple linear regression task as it involves just
two variables.

8. Quiz & Viva Questions

6.1 Quiz:

 Which of the following methods do we use to find the best fit line for data in Linear Regression
(a) Least Square Error
(b) Maximum Likelihood
(c) Logrithmic Loss
(d) Both A and B
 Which o
 f the following evaluation metrics can be used to evaluate a model while modeling a continuous output
variable?
(a) AUC-ROC
(b) Accuracy
(c) Logloss
(d) Mean Squared Error

6.2 Viva
 What is regression?
 What is the difference between observation and response variable?
 What is the expectation of response variable?
 What do you mean by residuals?

[50]