Experiment No.

# Installing MySQL server and Workbench.

Step 1: Download the MySQL Installer from dev.mysql.com. The two download options are a web-
Community version and a full version. The web-community version will only download the server,
by

default , but you can select other applications (like Workbench) as desired. The full installer will

download the server and all the recommended additional applications.

You’ll also be asked to create a user account, but you skip this part by scrolling down to the bottom
and clicking “No thanks, just start my download”.
 Step 2: Run the installer that you downloaded from its
location on your system,

generally by double-clicking.

Select Developer Default on the next page.

Install the server instance and whichever additional products you selected and make sure to
download and install MySQL Workbench. Then begin the configuration process by selecting
the availability level (most users will use the default, standalone version).
Complete the configuration process by following the on-screen instructions. You’ll want to
make sure to install MySQL as a Service so that Windows can automatically start the service
after a reboot or can restart the service if it fails.
 Experiment No. 2
Environment: Microsoft Windows

Tools: Draw.io
Objective: Creating Entity-Relationship Diagram using case tools.
Theory & Concept:
ER Diagram (Entity-Relationship Diagram)
The Entity Relationship Diagram explains the relationship among the entities present in the database.
The ER Diagram is the structural format of the database.
Symbols Used in ER Diagram
It is used to model the logical view of the system from a data perspective which consists of these
symbols:

Figures Symbols Represents

Rectangles Rectangles represent Entities in
the ER Model.

Ellipses Ellipses represent Attributes in

the ER Model.

Diamond Diamonds represent

Relationships among Entities.

Lines Lines represent attributes to

entities and entity sets with
other relationship types.

Double Ellipse Double Ellipses represent

Multi-Valued Attributes.

Double Rectangle Double Rectangle represents a

Weak Entity.

Components of ER Diagram
ER Model consists of Entities, Attributes, and Relationships among Entities in a Database System.
1) Entity
i. Strong Entity
ii. Weak Entity
2) Attributes
i. Key
ii. Composite
iii. Multivalued
iv. Derived
3) Relationship
i. One to One
ii. One to Many
iii. Many to One
iv. Many to Many

An ER Diagram for Music Streaming Service:

Entities:

• Song
• Artist
• Playlist
• User
• Subscription
• Genre
Relationships:

• Composed By (Artist to Song)

• Added To (Playlist to Song)
• Followed By (User to Artist)
• Subscribed (User to Subscription)
• Categorized As (Song to Genre)
Attribute:
i. Song
• Song.Title
• Song.ID (Primary Key)
• Artist.ID (Foreign Key)
• Album
• Genre
• Duration
• Release date
ii. Artist
• Artist Name
• Artist.ID(Primary Key)
• Genre.Name(Foreign Key)
• Popularity
iii. Playlist
• Playlist.Title(Primary Key)
• Creator
• Description
• Number of followers
iv. User
• Username(Primary Key)
• Email
• Password
• Plan.Name(Foreign Key)
• Listening History
v. Subscription
 • Plan.Name(Primary Key)
• Price
• Features
vi. Genre
• Genre.Name(Primary Key)
• Descriptions
 Experiment No. 3

Environment: Microsoft Windows

Tools/ Language: MySql/Oracle
Objective: Write the SQL queries for data definition and data manipulation language.
Theory & Concept:
Introduction about SQL
SQL (Structured Query Language) is a nonprocedural language, you specify what you want, not
how to get it. A block structured format of English key words is used in this Query language.
It has the following components:
• DDL (Data Definition Language)
• DML (DATA Manipulation Language)
• View definition
• Transaction Control
• Embedded SQL and Dynamic SQL
• Integrity
• Authorization
Data Definition Language
• The SQL DDL allows specification of not only a set of relations but also information about
each relation, including-
• • Schema for each relation
• • The domain of values associated with each attribute.
• • The integrity constraints.
• • The set of indices to be maintained for each relation.
• • The security and authorization information for each relation.
• • The physical storage structure of each relation on disk. \

Domain types in SQL

The SQL standard supports a variety of built in domain types, including-

• Char (n)- A fixed length character length string with user specified length .
• Varchar (n)- A variable character length string with user specified maximum length n.
• Int- An integer.
• Small integer- A small integer.
• Numeric (p, d)-A Fixed point number with user defined precision.
• Real, double precision- Floating point and double precision floating point numbers with
machine dependent precision.
• Float (n)- A floating point number, with precision of at least n digits.
• Date- A calendar date containing a (four digit) year, month and day of the month. •
Time- The time of day, in hours, minutes and seconds Eg. Time ’09:30:00’. •
Number- Number is used to store numbers (fixed or floating point).
 Practical 1

Create table employee which consist of following attributes

EMPLOYEE TABLE

COLUMN NAMES TYPE

EMP_NO NUMBER(5) NOT NULL

EMP_NAME VARCHAR(30)

DESIGNATION CHAR(10)

DOJ DATE

SALARY NUMBER(9,2)

ADDR VARCHAR(30)

DEPT_NAME CHAR(30)

Insert the following data in above table

EMP EMP DESIGN DOJ SALARY ADD DEPT_N
ATION AME
NO NAME

1001 Amit Officer 21-Dec- 1000 Mathur Marketing

1995 a

1002 Sumit Clerk 14-May- 500 Delhi Accounts

1982

1003 Raj Manager 23-Dec- 3500 Bomba Sales

1984 y

1004 James Analyst 22-Jul- 5000 Mathur Software

1990 a

1005 Amit Analyst 22-Jul- 4900 Delhi Production

1990

1006 Jones Clerk 16-Apr- 950 Delhi

1986

Perform the following SQL queries:

1. List the Emp name, doj from employee table

2. List the name of employee who is getting 1000 Rs.

3. List the name of emp who belong to Mathura and depart name is MARKETING
4. List the name of emp who are getting their salary in between 800 and 2500

5. List the age of all employee in the organization.

6. List the employees who are earning more than 1200 but less than 4000.
7. List the employees who have joined after 1st Jan 84 in the order of the joining date.

8. List the employees are located at Gnoida

9. List the employees who are in sales dept.

10. List the departments that are located in Delhi.

11. List the employees who are not work in Delhi.

12. List names of all employees whose designation is 'Analyst’
 Experiment No: - 4
1. Program Name: Creating procedure and functions.

Theory Concept:
Normalization is a database design process used to organize data in a relational database
efficiently and reduce data redundancy. It is a multi-step process that sets the data into tabular form
and removes the duplicated data from the relational tables. Normalization typically involves
dividing a database into two or more tables and defining relationships between them. Let's go
through an example of normalizing a database with sample data and MySQL queries. We'll start
with an unnormalized table and normalize it step by step.

Step 1: Create an Unnormalized Table

Suppose we have a table called "CustomerOrders" that stores information about customers and
their orders. This table is not normalized because it contains repeating groups and data
redundancy:

CREATE TABLE CustomerOrders (

customer_id INT PRIMARY KEY,
customer_name VARCHAR(255),
order_id INT,
order_date DATE,
total_amount DECIMAL(10, 2)
);

INSERT INTO CustomerOrders (customer_id, customer_name, order_id, order_date, total_amount)

VALUES
(1, 'Alice', 101, '2023-01-15', 100.00),
(1, 'Alice', 102, '2023-02-20', 150.00),
(2, 'Bob', 201, '2023-03-10', 75.50),
(3, 'Charlie', 301, '2023-04-05', 200.00);

Step 2: Normalize the Data

We'll normalize the data by creating two separate tables: "Customers" and "Orders." The
"Customers" table will store customer information, and the "Orders" table will store order
information.

-- Create the Customers table

CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(255)
);

-- Create the Orders table

CREATE TABLE Orders (
order_id INT PRIMARY KEY,
 customer_id INT,
order_date DATE,
total_amount DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

-- Populate the Customers table with unique customer information

INSERT INTO Customers (customer_id, customer_name)
SELECT DISTINCT customer_id, customer_name FROM CustomerOrders;

-- Populate the Orders table with order information

INSERT INTO Orders (order_id, customer_id, order_date, total_amount)
SELECT order_id, customer_id, order_date, total_amount FROM CustomerOrders;

Step 3: Query the Normalized Tables

Now that we have normalized our data, we can query the "Customers" and "Orders"
tables to retrieve information:

-- Query to retrieve customer information

SELECT * FROM Customers;

-- Query to retrieve order information

SELECT * FROM Orders;

-- Query to retrieve customer names and their total order amounts

SELECT c.customer_name, SUM(o.total_amount) AS total_order_amount
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

Output:

These queries demonstrate the result of normalizing the data. The "Customers" table
contains unique customer information, and the "Orders" table stores order details with a reference
to the customer. The last query retrieves the total order amount for each customer, demonstrating
the power of relational databases and normalization.
 Experiment No-5
Program Name: Design and implementation of Student Information System.

Theory Concept:

Designing and implementing a Student Information System (SIS) experiment in a

Database Management System (DBMS) is a practical way to learn about database design and
development. Below, I’ll outline a simplified experiment scenario for creating a basic SIS using a
relational DBMS (e.g., MySQL, PostgreSQL). This experiment assumes you have basic knowledge of
SQL and database concepts.

Experiment Scenario:

You are tasked with creating a Student Information System (SIS) for a university. The
system should store information about students, courses, and grades. Students can enroll in courses,
and teachers can enter grades for students in those courses.

Experiment Steps:

1. Database Design:
Define the database schema with tables for students, courses, and grades.
Here's a simplified schema:

-- Students table
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
birthdate DATE,
email VARCHAR(100)
);

-- Courses table
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
teacher VARCHAR(100)
);

-- Grades table
CREATE TABLE grades (
grade_id INT PRIMARY KEY,
student_id INT,
course_id INT,
grade VARCHAR(2),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
 2. Data Population:

Insert sample data into the tables for testing purposes.

-- Insert sample students

INSERT INTO students (student_id, first_name, last_name, birthdate, email)
VALUES
(1, 'John', 'Doe', '1995-01-15', 'john@example.com'),
(2, 'Jane', 'Smith', '1996-03-22', 'jane@example.com');

-- Insert sample courses

INSERT INTO courses (course_id, course_name, teacher)
VALUES
(101, 'Mathematics 101', 'Dr. Smith'),
(102, 'Computer Science 101', 'Prof. Johnson');

-- Enroll students in courses

INSERT INTO grades (student_id, course_id, grade)
VALUES
(1, 101, 'A'),
(1, 102, 'B'),
(2, 101, 'B');

3. Querying the Database:

Practice querying the database to retrieve information. For example,

you can retrieve a student's
grades or find courses taught by a specific teacher.

-- Get a student's grades

SELECT s.first_name, s.last_name, c.course_name, g.grade
FROM students s
JOIN grades g ON s.student_id = g.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE s.student_id = 1;

-- Find courses taught by a specific teacher

SELECT course_name
FROM courses
WHERE teacher = 'Dr. Smith';

4. CRUD Operations:

Practice performing CRUD (Create, Read, Update, Delete) operations on the database.
For example,
you can add a new student, update a student's information, or delete a course.

-- Create: Add a new student

INSERT INTO students (student_id, first_name, last_name, birthdate, email)
VALUES (3, 'Alice', 'Johnson', '1997-05-10', 'alice@example.com');

-- Update: Change a student's email

UPDATE students
SET email = 'new_email@example.com'
WHERE student_id = 3;

-- Delete: Remove a course

DELETE FROM courses
WHERE course_id=102;
 Experiment No: 6

Program Name: Write a CURSOR to display list of clients in the client Master Table.

TheoryConcept:The following example would illustrate the concept of CURSORS. We will be using
the CLIENT_MASTER table and display records.

Implementation:

DECLARE
CURSOR client_cur
isSELECT
id,name,address
FROM client_master;
client_rec
client_cur%rowtype;B
EGIN
OPENclient_cur;
LOOP
FETCH client_cur into
client_rec;EXITWHENclient_
cur%notfound;
DBMS_OUTPUT.put_line(client_rec.id||''||client_rec.nam
e);END LOOP;
END;
/
Output:Whenthe above codeis executed atSQLprompt,itproduces thefollowing result:
1 Ramesh
2 Khilan
3 kaushik
4 Chaitali
5 Hardik
6 Komal

PL/SQLprocedure successfully completed.

 Experiment No-7

ProgramName: Execute queries related to Exists, Not Exists, Union, Intersection, Difference, Join
ontablesCLIENT_MASTER,PRODUCT_MASTER,SALESMAN_MASTER,SALES_ORDER,SALES_O
RDER_DETAILS

TheoryConcept:
Theprogramretrievesdatafromrecordsbydefiningrelationbetweentwotablessoastoretrievefilteredrecords.

Implementation:
CorrelatedquerieswithEXISTS/NOTEXISTSclause

1) Select all products and order_no where order_status is ‘in Process’

Ans: Select order_no.,product_no. from sales_order_details where exists(select * from sales_order
,order_no = sales_order_details,order_no and order_status=’in process’);
Output:
Order_no Product_
no
0003 3

2) Selectorder_no and order_date for all orders which include product_no ‘P001’ and
quantity_ordered>10Ans:Select order_no,order_data from sales_order where exists(select * from
sales_order_details wheresales_order_details,order_no = sales_order.Order_no and product-
no=’p001’ and quantity-ordered>10;Output:

Order_no Product_no
0002 05/feb/13

3) Find all order_no for salesman rashmi.

Ans:Select order_no from sales_order where exists(select * from salesman_master
wheresalesman_master.saleman-no=sales_order-salesman_noandname=’rashmi’);
Output:

Order_no
0003

4) Select all clients who have not placed any orders.

Ans:Select * from client_master where not exists(select *
fromsales_order.client_no=client_master.client_no);

Output:

Client_no Name City Pincode State

6 Divya Hapur 35498 U.P.
7 Dorothy Noida 32547 U.P.
 5)Select all orders with order_date for ‘acrylic colors’
Ans:Select order_no,order_date from sales_order where exists(select *
fromsales_order_details.oder_no=sales_order.order_noANDexists(select*fromproduct1wheresales_order_de
tails.product_no=product_noAND description=’acrylic colors’);
Output:

Order_no Order_date
0001 23/jan/13

Union,Intersect and minus clause:

1) List all the clients and salesman and their names

Ans:Select client_no, name from client_master UNION select salesman_no,name from salesman_master;
Output:

Client_no Name
3 Akshita
4 Dhawal

2) List all the clients and their names who are also salesman.
Ans:Selectnamefromclient_masterINTERSECT,selectnamefromsalesman_master;
Output:
No rows selected

3) List all the clients who are not salesman.

Ans:Select name from client_master MINUS select name from salesman_master;
Output:

Name
Akshita
Dhawal
Akansha
Divya
Dorothy

4) List all the clients who have placed orders

Ans:Select client_no from client_masterINTERSECTselectclient_no from sales_order;

Output:

Client_no
6
 7

5) Listalltheclientswhohavenotplacedanyorder.

Ans:Select client_no from client_masterMINUS select client_no from sales_order;

Output:

Client_no
3
4
5

6) List all the clients in UPwho have placed orders

Ans:Selectclient_nofromclient_masterwherestate=’UP’INTERSECTselectclient_nofromsales_ord
er;
Output:

Client_no
3
4
5

7) FindalltheclientsandtheirnamesfromcityGhaziabadwhohavedeliverydateoftheirordersastoday.Ans:
Select client_no from client_master where city=’Ghaziazbad’ INTERSECT select client_no
fromsales_order where delivery_date=’09-MAR-13’
Output:
Client_no
5

Queries on Joins
1) List the product_no and description of products sold.
Ans:Select product_no, description from (product1 natural join sales_order_details)
Output:

Product_no Description
1 Chair
1 Chair
2 Table
3 Sofa

2) Find the products which have been sold to ‘akansha’

Ans:Select product_no, description from (product1 natural join sales_order details natural
joinsales_order natural join client_master) where name=’akansha’;
Output:
Product_no Description
3 Sofa
3) Find the products and their quantities that will have to be delivered in the current month.

Ans:Select sales_order_detailsproduct_no, product1 ,description,

sum(sales_order_details,quantity_ordered) from sales_order_details, sales_order, product1 where
product1,product_no=sales_order_details,product_noandsales_order,order_no=sales_order_details,order_
noandto_char (delivery_date,’mon-yy’) = to_char(sysdate,’mon-yy’)group by sales_order_details,
product_no,product1, description ;
Output: no rows selected

4) Find thenamesofclientwhohavepurchased ‘chair’

Ans:Select name from(client_master natural join sales_order natural join sales_order_details natural
joinproduct1) where description= ‘chair’;

Output:

Name
Akshita
Akansha

5)
6) List theorders forlessthan 5unitsof saleof‘chair’
Ans:Select product_no, order_no from (sales_order_details natural join product1)
where(description=’chair’and qty_ordered<5);
Output:

Product_no Order_no
1 0001
1 0001

7) Find the products and their quantities placed by ‘akansha’or ‘akshita’.

Ans:Selectproduct_no,description,qty_orderedfrom(product1naturaljoinsales_order_detailsnaturaljoin
sales_order_natural join client_master) where (name=’akansha’or name=’akshita’);
Output :

Product_no Description Qty_ordered

1 Chair 4
1 Chair 3
2 Sofa 2

8) Find the products and their quantities for the orders placed by the client_no ‘3’and ‘5’
Ans:Selectproduct_no,description,qty_orderedfrom(product1naturaljoinsales_order_detailsnaturaljoin
sales_order natural join client_master) where (client_no=3 OR client_no=5);
Output:
PRODUCT_NO DESCRIPTION QTY_ORDERED
1 Chair 4
1 Chair 3