Database Management System: Submitted To
Database Management System: Submitted To
Database Management System: Submitted To
CO-202
LAB FILE
SUBMITTED TO:
Dr. Indu Singh
SUBMITTED BY:
Kanupriya Jain
2K20/CO/221
1
INDEX
2
EXPERIMENT 1
Aim:
Introduction to SQL, database and database management system.
Theory:
SQL
• SQL stands for structured query language.
• It is a database query language that allows us to work with data in tables
and tells us how to query the tables and other objects.
• SQL (Structured Query Language) is a computer language that can be
used to create a database, store data or add/update/delete the data in a
database. All Relational Database Softwares like MySQL use SQL as the
standard database language.
• This language is used by most of the databases to manage the data like
SQL server, Oracle, PostgreSQL and mySQL.
Uses of SQL:
SQL Commands
3
Used to change/modify existing data into the table
Database:
A logically ordered collection of structured data which is kept electronically in a
computer system is known as a database. In charge of the database and
database management system.
Also, a database management system allows the users to connect with the
database securely and simultaneously while protecting the Data integrity.
4
• It follows the ACID concept (Atomicity, Consistency, Isolation, and
Durability).
• DBMS supports a multi-user environment that allows users to access and
manipulate data in parallel.
Learning:
We studied structured query language, its uses and some basic commands.
Also, we learnt about databases, database management system, its
characteristics and applications.
Conclusion:
SQL is the most widely used database language and will be used to manage databases
in all our further lab practicals.
5
EXPERIMENT 2
Aim:
Introduction to various software of database management system and database
language.
Theory:
DBMS software
A Database Management Software is used to store, manipulate, and manage
data in a database environment. A database management system (DBMS) acts
as a link between a user and a database, allowing them to create, read, update,
and remove data in the database. Some examples of DBMS software are as
follows:
• Oracle
• MySQL
• SQLite
• Microsoft Access
• Amazon RDS
• ADABAS
• Teradata
• IBM DB2
Oracle:
• Oracle is a relational database management system.
• It is also called OracleDB, or simply Oracle.
• It is produced and marketed by Oracle Corporation. It was created in
1977 by Lawrence Ellison.
• It is one of the most widely used relational database engines for storing,
organizing, and retrieving data in the IT sector.
• It uses SQL queries as a language for interacting with the database
MySQl:
• MySQL is a relational database management system that is free and
open-source (RDBMS).
• "My" is the name of co-founder Michael Widenius's daughter, and "SQL"
is the abbreviation for Structured Query Language.
• A relational database organizes data into one or more data tables where
data types can be associated to one another, allowing the data to be
structured.
6
• SQL is a programming language that allows programmers to create,
change, and extract data from relational databases, as well as control
user access.
Microsoft Access:
• Microsoft Access is a database management system (DBMS) by
Microsoft that combines a graphical user interface with the relational
Microsoft Jet Database Engine and software development tools.
• It uses the Access Jet Database Engine to store data in its own format.
• Most major databases that accept the Open Database Connectivity
(ODBC) standard, such as SQL Server, Oracle, and DB2, are compatible
with Access.
Amazon RDS:
• Amazon RDS is a cloud-based relational database service.
• Amazon RDS automates relational database management, making it
easier to manage.
• For high availability and failovers, Amazon RDS creates additional
instances.
• It offers easy-to-use relational databases in the cloud at an inexpensive
price.
• PostgreSQL, MySQL, Maria DB, Oracle, SQL Server, and Amazon
Aurora are all supported by Amazon RDS.
Database Languages:
When data needs to be manipulated after it has been stored or filled, including
insertion, deletion, updating, and alteration. The database management system
provides a collection of languages for these tasks (DBMS). To read, update,
and store data in a database, database languages are used.
7
• Create: This command is used to create database objects.
• Alter: It is used to alter the database's structure.
• Drop: It is a method for deleting objects from a database.
• Truncate: It is used to delete all records from a table.
• Rename: It is a command that is used to rename an object.
3. Data Control Language (DCL): It is used to retrieve data that has been
saved or stored. The DCL is executed in a transactional manner. There
are also rollback options.
8
5. Session Control statements (SCL): Session control statements
dynamically manage the properties of a user session. These statements
do not implicitly commit the current transaction. PL/SQL does not support
session control statements.
Learning:
We studied various types of database storage and manipulation software
available and the five different sorts of database languages, each with its own
set of functions and syntax.
Conclusion:
Oracle is the most popular and widely used software which supports all types
of languages and hence we will use this software for our lab practical.
9
EXPERIMENT 3
Aim:
Introduction to ER diagram and symbol table.
Theory:
ER Model: Entity-relationship model is a high-level data model used to design
and represent relationships between data. The main data objects are Entities
along with their details defined as attributes, some of these attributes are
important and are used to identity the entity, and different entities are related
using relationships.
Attributes
Attributes are characteristic property of an entity. If Student is an Entity, then
student's roll no., student's name, student's age, student's gender etc will be
its attributes.
The different types of attributes defined in ER database model are as follows:
1. Simple attribute: The attributes with values that are atomic and cannot be
broken down further are simple attributes. For example, age.
2. Composite attribute: A composite attribute is made up of more than one
simple attribute. For example, student name consists of first name,
middle name and last name.
3. Derived attribute: These are the attributes which are not present in the
whole database management system, but are derived using other
attributes. For example, average age of students in a class.
4. Single-valued attribute: They have a single value.
5. Multi-valued attribute: They can have multiple values like phone number.
10
Relationships
When an Entity is related to another Entity, they are said to have a relationship.
Depending upon the number of entities involved, a degree is assigned to
relationships. For example, if 2 entities are involved, it is said to be Binary
relationship, if 3 entities are involved, it is said to be Ternary relationship, and
so on.
ER Diagram of a bank
Advantages of ER Diagram
The advantages of choosing an entity relation (ER) model over physical
design are as follows:
11
(in a physical data model.) In software engineering, an ER diagram is
often an initial step in determining requirements for an information
systems project. It’s also later used to model a particular database or
databases. A relational database has an equivalent relational table and
can potentially be expressed that way as needed.
ER Diagram Symbols
12
Cardinality Symbols:
13
Learning/ Conclusion
We understood the ER diagrams, their need, components like entities, attributes
and relationships, its advantages, uses and the symbols to represents different
components.
14
EXPERIMENT 4
Aim:
Introduction to different types of constraints in SQL.
Theory:
SQL Constraints are rules that limit the types of data that can be entered into a
table in order to maintain the data's accuracy and integrity. Constraints can be
classified into two categories:
NOT NULL
By default, a column can hold NULL values. But if you do not want a column
to have a NULL value, then we use the NOT NULL constraint.
Example:
CREATE TABLE Student
( s_id int NOT NULL,
name varchar(60),
age int
);
15
UNIQUE
It ensures that a column will only have unique values. A UNIQUE constraint
field cannot have any duplicate data.
Example
CREATE TABLE Student
( s_id int NOT NULL,
name varchar(60),
age int NOT NULL UNIQUE
);
PRIMARY KEY
A primary key constraint uniquely identifies each row/record in a database
table. Primary keys must contain unique values. A primary key column cannot
have NULL values.
A table can have only one primary key, which may consist of single or multiple
fields. When multiple fields are used as a primary key, they are called a
composite key. The field with a PRIMARY KEY constraint can be used to
uniquely identify each record of a table in various database operations.
OR
16
ALTER TABLE Employees
ADD [CONSTRAINT PK_Emp_Id] PRIMARY KEY (Emp_Id);
FOREIGN KEY
Uniquely identifies a row/record in any of the given database tables. Foreign
Key Constraint is used to link two tables. It is used to establish a relationship
between the data in two tables.
Example
CREATE TABLE Employees
(
Emp_Id INT(3) PRIMARY KEY,
Emp_Name VARCHAR(30),
Dept_Id INT(5) REFERENCES Department(Dept_Id)
);
17
CHECK
CHECK constraint is used to restrict the value of a column between a range. It
performs check on the values, before storing them into the database. It is like
condition checking before saving data into a column.
Example
CREATE table Student (
s_id int NOT NULL CHECK (s_id > 0),
Name varchar(60) NOT NULL,
Age int
);
DEFAULT
DEFAULT constraint is used to provide a default value for the fields of a table.
The DEFAULT Constraint is used to fill a column with a default and fixed
value. The value will be added to all new records when no other value is
provided
Example
CREATE TABLE Persons
(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Delhi'
);
Learnings/ Conclusion
After this experiment, we learnt the different constraints in SQL.
18
EXPERIMENT 5
Aim:
Case Study - University Management System
Theory:
The database for the university management system will have the
following tables:
• COLLEGE Table
It will maintain the data about the college like college_id,
college_name, address, contact_number.
• DEPARTMENT Table
It will maintain the data about the department of a college like
department_id, department_name, HOD, location, contact_number.
• FACULTY Table
It will maintain the data about the faculty working under different
departments with attributes like faculty_id, faculty_name, designation,
qualification, address and contact_number
• COURSE Table
It will maintain the data about the different courses available like
course_id, course_name and year
• STUDENT Table
It will maintain the information related to the student like student_id,
student_name, contact_number, address and email
Assumptions:
1. One College can have many departments but a department can have
only one College. (1:N relationship)
19
2. One Department can have more than one faculty member but a faculty
member can only work under one department only. (1:N relationship)
3. A Faculty can teach many courses and a single course can be taught by
only one faculty member. (1:N relationship)
4. A Department can consist of more than one course but a course can
only be taught by one department only. (1:N relationship)
6. A student owns more than one progress report for different courses
he/she is studying. (1:N relationship)
7. A department has many courses but a course has only one department.
(1:N relationship)
Learning/ Conclusion:
From this experiment, we learnt about the different entities and their
relationships in a University Management System.
20
EXPERIMENT 6
Aim:
To construct the entity relationship diagram for university management
system, create entities and relationship tables.
Theory:
COLLEGE TABLE
College_id, cname, address, contact_no
DEPARTMENT TABLE
dept_id, dname, hod, location, contact_no, c_id
21
FACULTY TABLE
faculty_id, fname, designation, qualification, location, contact_no,
dept_no
22
COURSE TABLE
course_id, name, year, fac_id, dept_id
23
STUDENT TABLE
student_id, sname, contact_no, address, email, clg_id
24
STUDIES TABLE
Stu_id, course_id, credits
Relationship Table
25
College: Department (1: M)
Department: Faculty (1: M)
Faculty: Course (1: N)
Department: Course (1: M)
College: Student (1: M)
Student: Progress Report (1: M)
Student: Course (M: N)
Learning/ Conclusion
Through this experiment, we learnt to create the ER diagram for University
management system and the Entity and Relationship tables in Oracle.
26
EXPERIMENT 7
Aim:
To implement DDL (Data Definition Language) and DML (Data Manipulation
Language) commands.
Theory:
DDL Statements
DDL or Data Definition Language actually consists of the SQL commands that
can be used to define the database schema. It simply deals with descriptions of
the database schema and is used to create and modify the structure of
database objects in the database.
CREATE
Used to create a Table in the database.
DROP Table
It is used to remove an existing table from the database.
27
ALTER Table
It is used to for altering the table structure, such as, to add a column to
existing table, to rename any existing column, to change datatype of any
column or to modify its size and to drop a column from the table.
28
RENAME Table
Using ALTER command, we can rename an existing column of a table.
TRUNCATE
It is used to remove all the records from a table.
29
DML Statements
Data Manipulation Language (DML) commands are used for managing data in
database. DML commands are not auto-committed which means the changes
made by DML command are not permanent to database and can be rolled
back.
SELECT
It is used to retrieve data from the database.
INSERT
It is used to insert data into the table.
30
UPDATE - It is used to update the existing data from the database.
31
DELETE
It is used to delete any existing record from the table.
Learning/ Conclusion
In this experiment, we learnt about Data Definition Language (DDL) and Data
Manipulation Language (DML) commands. We implemented the commands-
created tables, added columns, inserted data, updated data, deleted rows,
dropped tables etc.
32
EXPERIMENT 8
Aim:
To implement simple queries in SQL.
Theory:
Simple Queries in SQL involves:
1. SQL Queries using logical operators and operations.
2. SQL Query using group by functions.
3. SQL Query for group by functions.
33
BETWEEN: Select the student ID of the students with rank between 1 to 20.
IN: Display the details of all courses where department ID is 10, 11 or 15.
34
LIKE: Display the details of all courses starting with D
GROUP BY:
To group the result set by one or more columns, the GROUP BY statement is
frequently used with aggregate functions (COUNT, MAX, MIN, SUM, AVG).
35
Learning
Learned about several sorts of simple queries, such as employing logical
operators, group by, and aggregate functions, and how to construct these
queries using Oracle tables and commands, using tables from the previously
analysed university management system as examples.
Conclusion
Before moving on to complex queries and joins, it is critical to fully learn and
grasp basic principles by implementing simple queries with the help of
operators, group by, and aggregate functions.
36
EXPERIMENT 9
Aim:
Implementation of JOINS
THEORY:
A JOIN clause is used to combine rows from two or more tables, based on a
related column between them. The SQL Joins clause is used to combine
records from two or more tables in a database. A JOIN is a means for combining
fields from two tables by using values common to each.
INNER JOIN
INNER JOIN returns records that have matching values in both tables.
37
LEFT JOIN
LEFT (OUTER) JOIN returns all records from the left table, and the matched
records from the right table.
38
RIGHT JOIN
RIGHT JOIN returns all records from the right table, and the matched records
from the left table.
39
FULL OUTER JOIN
FULL JOIN returns all records when there is a match in either left or right table.
A FULL JOIN returns all the rows from the joined tables, whether they are
matched or not i.e. you can say a full join combines the functions of a LEFT
JOIN and a RIGHT JOIN.
40
Conclusion
From this experiment we get to learn about different joins in SQL i.e., inner join,
left join, right join, full join and also implemented in our database using oracle
10g express. Hence, all the joins were understood and it was implemented in
our database for effective database maintenance
41
EXPERIMENT 10
Aim:
THEORY:
Aggregate Queries:
String Queries:
A string function accepts a string value as an input and returns a string value
regardless of the data type. There are numerous built-in string functions in SQL Server
that developers can use. They're used to do something with an input string and then
return an output string.
Date Queries:
For newcomers to SQL, dates are difficult since, while working with databases, the
format of the date in the table must match the input date in order to insert. Datetime
(time is also linked with date) is used in many contexts instead of date. These functions
return Dates in the format specified by the user.
Relation Used
42
COUNT
Returns the NUMBER of values in a column. Leaves the column having value NULL.
AVG
It returns the average value of the expression.
43
MAX
It returns the maximum value of the expression.
44
MIN
It returns the minimum value of the expression.
SUM
Returns the sum of the values of the expression.
45
VARIANCE ()
Returns the VARIANCE of values in a column. Leaves the column having
value NULL.
SUBSTR
Returns a substring from given string. Format of this function is first entering the String
(column name), then starting address and then length which need to be print.
46
LOWER
Returns the SUBSTRING in Lower case. Change the Word to Upper to Lower case.
47
UPPER
Returns the SUBSTRING in UPPER case. Change the Word to LOWER to UPPER case.
LENGTH
48
IMPLEMENTATION OF DATE QUERIES
SYSDATE FROM DUAL
Return the Date from system. It returns in format (date- month- year).
49
To_char(sysdate)
It is used to print system date and time in a specified manner as required by the user.
CONCLUSION
We learned about different Queries included Aggregate queries, String queries and Date
queries and implemented them using Oracle Software. Implemented then queries in my
Hospital Management System Billing Table.
50
EXPERIMENT 11
Aim:
Implementation of views, triggers, grant and revoke commands
THEORY:
GRANT
SQL Grant is used to provide permissions like Select, All, Execute to user on
the database objects like Tables, Views, Databases and other objects in a
SQL Server.
Syntax
Grant privilageName
on objectName
To {userName/Public/roleName}
[with Grant Option]
REVOKE
SQL Revoke is used to remove the permissions or privileges of a user on
database objects set by the Grant command.
Syntax
Revoke privilageName
on objectName
from{userName/public/roleName}
Creating a user
51
Grant Insert, Select, Update, Delete on STUDENT to harry_potter.
52
Grant [With Grant Option]
53
VIEWS
A view is like a “virtual” table
- Defined by a query, which describes how to compute the view contents
on the fly
- DBMS stores the view definition query instead of view contents
- Can be used in queries just like a regular table
VIEWS QUERIES
54
TRIGGERS
Syntax
CREATE TRIGGER schema.trigger_name
ON table_name
AFTER {INSERT, UPDATE, DELETE}
[NOT FOR REPLICATION]
AS
{SQL_Statements}
IMPLEMENTATION OF TRIGGERS
55
Insert statement on PROGRESS_REPORT
56
Impact of trigger on Progress_Report after Insertion
CONCLUSION
University Management System is successfully built and triggers, views and
grant/revoke privileges are executed on it.
57