Indira Gandhi Delhi Technical University
for Women
Lab File
Database Management System (BIT 201)
B.Tech, Electronics and Communication Engineering with
Artificial Intelligence (2021-2025)
Submitted to: Ms. Priyanka Behera Submitted by:
Amisha Sharma
ECE-AI 1
05301182021
EXPERIMENT - 1
A. CONCEPT DESIGN WITH E-R MODEL USING BUS RESERVATION SYSTEM
AIM: To relate the entities appropriately. Apply cardinalities for each relationship. Identify
strong and weak entities. Indicate the type of relationship (total/partial). Incorporate
generalization, aggregation, and specialization etc. wherever required.
ENTITIES:
1. Bus
2. Passenger
3. Ticket
4. Reservation
5. Cancellation
ATTRIBUTES:
1. Bus
a. Bus number
b. Coach
c. Number of seats
d. Source
e. Destination
2. Passenger
a. Name
b. Age
c. DOB
d. Passenger code
e. Gender
3. Ticket
a. Ticket number
b. Source
c. Destination
d. Date
e. Departure time
4. Reservation
a. Date
b. Passenger code
c. Number of seats
d. Status
e. Contact number
f. Address
5. Cancellation
a. Passenger code
b. Date
c. Status
d. Number of seats
e. Contact number
f. Address
SCHEMA:
1. BUS: {Bus Number: integer, Coach : string, Number of seats: integer, Source: string, Destination:
string}
2. Passenger : {Name: string, Age: integer, DOB: string, Passenger code: string, Gender: string}
3. Ticket: {Ticket number: integer, Source: string, Destination: string, Date: string, Departure time:
string}
4. Reservation: {Date: integer, Passenger code: string, Number of seats: integer, Status: string,
Contact number: integer, Address: string}
5. Cancellation: {Passenger code: string, Date: string, Status: string, Number of seats: integers,
Contact number: integer, Address: string}
E-R MODEL:
EXPERIMENT – 1 (continued)
B. CONCEPT DESIGN WITH E-R MODEL USING LIBRARY MANAGEMENT SYSTEM
AIM: To relate the entities appropriately. Apply cardinalities for each relationship. Identify
strong and weak entities. Indicate the type of relationship (total/partial). Incorporate
generalization, aggregation, and specialization etc. wherever required.
ENTITIES:
1. Membership
2. Books
3. Staff
4. Borrower
5. Visitor
ATTRIBUTES:
1. Membership
a. Member_ID
b. Name
c. Contact number
d. Address
e. Start_date
f. End_date
g. Staff_discount
h. Student_discount
2. Books
a. Book_ID
b. Name
c. Author
d. Domain
e. Genre
f. Number of copies
3. Staff
a. Employee_ID
b. Name
c. Contact number
d. Address
e. DOB
f. Age
g. Gender
4. Borrower
a. Member_ID
b. Book_ID
c. Borrow_date
d. Return_date
5. Visitor
a. Name
b. Contact number
c. Age
d. Visit_date
e. Visit_duration
SCHEMA:
1. Membership: {Member_ID : string, Name: string, Contact number: integer, Address: string,
Start_date: string, End_date: string, Staff_discount: boolan, Student_discount: boolean}
2. Books: {Book_ID : string, Name: sting, Author: string, Domain: string, Genre: string, Number of
copies: integer}
3. Staff: {Employee_ID: string, Name: string, Contact number: integer, Address: string, DOB: string,
Age: integer, Gender: string}
4. Borrower: {Member_ID: string, Book_ID: string, Borrow_date: string, Return_date:string}
5. Visitor: {Name: string, Contact number: integer, Age: integer, Visit_date: string, Visit_duration:
string}
E-R MODEL:
e
EXPERIMENT-2
AIM: CREATION OF TABLES
A. Create a table called Employee with the following structure.
create table EMPLOYEE(Empno Number(10), Ename Varchar2(10), Job Varchar2(10), Mgr
Number(10), Sal Number(10));
i. Add a column commission with domain to the Employee table.
ALTER table EMPLOYEE ADD Commission Number(10);
ii. Insert any five records into the table.
INSERT into EMPLOYEE(Empno, Ename, Job, Mgr, Sal, Commission) values
(101,'Aman','Manager',2001,60000, 5000);
INSERT into EMPLOYEE(Empno, Ename, Job, Mgr, Sal, Commission) values
(102,'Arun','Analyst',2002,450000, 4000);
INSERT into EMPLOYEE(Empno, Ename, Job, Mgr, Sal, Commission) values
(103,'Neha','Salesman',2003,30000, 3000);
INSERT into EMPLOYEE(Empno, Ename, Job, Mgr, Sal, Commission) values
(104,'Rohit','Salesman',2004,30000, 3000)
INSERT into EMPLOYEE(Empno, Ename, Job, Mgr, Sal, Commission) values
(105,'Neha','Clerk',2005,20000, 1000);
SELECT * From EMPLOYEE;
iii. Update the column details of job
UPDATE EMPLOYEE SET Job='Assistant' WHERE Empno=104;
SELECT * FROM EMPLOYEE;
iv. Rename the column of Employ table using alter command.
ALTER table EMPLOYEE RENAME column Job TO EJob;
v. Delete the employee whose Empno is 105.
DELETE FROM EMPLOYEE WHERE Empno=105;
B. Create a table called Customer table
CREATE TABLE customer(Custname varchar2(20), custstreet varchar2(20), custcity varchar2(20));
i. Insert records into the table.
insert into customer values("TAYLOR","Highstreet","hyd"); insert into
customer values("Selena","avanue","hyd"); insert into customer
values("FujiKaze","ChandiniChawk","hyd"); insert into customer
values("Dhruv","KashmereGate","Banglore");
insert into customer values("Hailey","SiliconCity","Banglore");
ii. Add salary column to the table.
ALTER table customer add salary number;
iii. Alter the table column domain.
Alter table customer RENAME COLUMN custname to cname;
iv. Drop salary column of the customer table.
Alter table customer drop column salary;
v. Delete the rows of customer table whose cust_city is „hyd‟.
DELETE FROM customer WHERE custcity ="hyd";
C. Create a table called sailor table
CREATE TABLE sailors ( sid integer not null,sname varchar(32),rating
integer,CONSTRAINT PK_sailors PRIMARY KEY (sid) );
i. Add column age to the sailor table.
alter table sailors add column age real;
ii. Insert values into the sailor table. INSERT INTO sailors ( sid, sname, rating, age ) VALUES (
1, 'Drake', 7, 45.0 );
INSERT INTO sailors ( sid, sname, rating, age ) VALUES ( 2, 'Beyonce', 9, 60.0 );
INSERT INTO sailors ( sid, sname, rating, age ) VALUES ( 3, 'Luv', 8, 58.0 );
INSERT INTO sailors ( sid, sname, rating, age ) VALUES ( 6, 'Dua', 6, 25.0 );
INSERT INTO sailors ( sid, sname, rating, age ) VALUES ( 7, 'Britanny', 5, 56.0 );
INSERT INTO sailors ( sid, sname, rating, age ) VALUES ( 8, 'Lany', 8, 34.0 );
iii. Delete the row with rating >8.
delete from sailors where rating>8;
iv. Update the column details of sailor.
Alter TABLE sailors RENAME COLUMN sname to sailorname;
v. Insert null values into the table.
INSERT INTO sailors ( sid, sailorname, rating, age ) VALUES ( 9, ' ', 7, 45.0 );
EXPERIMENT-3
A.
1. Create a user and grant all permissions to the user.
2. Insert any three records in the employee table and use rollback. Check the result.
3. Add primary key constraint and not null constraint to the employee table
4. Insert null values to the employee table and verify the result
B.
1. Create a user and grant all permissions to the user.
2. Insert values in the department table and use commit.
3. Add constraints like unique and not null to the department table.
4. Insert repeated values and null values into the table.
C.
1. Create a user and grant all permissions to the user.
2. Insert values into the table and use commit.
3. Delete any three records in the department table and use rollback.
4. Add constraint primary key and foreign key to the table.
SOLUTION:
A
1. Create a user and grant all permissions to the user.
CONNECT <USER-NAME>/<PASSWORD>@<DATABASE NAME>;
--Create user query
CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;
--Provide roles
GRANT CONNECT,RESOURCE,DBA TO <USER NAME>;
--Assigning privileges
GRANT CREATE SESSION GRANT ANY PRIVILEGE TO <USER NAME>;
GRANT UNLIMITED TABLESPACE TO <USER NAME>;
--Provide access to tables.
GRANT SELECT, UPDATE, INSERT, DELETE ON <TABLE NAME> TO <USER NAME>;
2. Insert any three records in the employee table and use rollback. Check the result.
SQL> SELECT * FROM EMPLOYEE;
EMPNO ENAME JOB MANAGER_NO SAL COMMISSION
---------------- ---------- ---------- --------------------- ------ -------------------
1 A manager 1234 1100 70
2 B analyst 2345 9000 65
3 C trainee 3456 9000 65
4 D clerk 4567 7000 55
SQL> insert into employee values(&empno,'&ename','&job',&manager_no,&sal,&commission);
Enter value for empno: 5
Enter value for ename: E
Enter value for job: salesman
Enter value for manager_no: 5678
Enter value for sal: 5000 Enter value for commission: 50 old 1: insert into employee
values(&empno,'&ename','&job',&manager_no,&sal,&commission) new 1: insert into employee
values(5,'E','salesman',5678,5000,50)
1 row created.
SQL> rollback;
Rollback complete.
SQL> SELECT * FROM EMPLOYEE;
EMPNO ENAME JOB MANAGER_NO SAL COMMISSION
---------------- ---------- ---------- --------------------- ------ -------------------
1 A manager 1234 1100 70
2 B analyst 2345 9000 65 3 C
trainee 3456 9000 65
4 D clerk 4567 7000 55
5 E salesman 5678 5000 50
3. Add primary key constraint and not null constraint to the employee table.
SQL> alter table employee modify(empno number primary key, ename varchar2(10) not null);
Table altered.
SQL> desc employee;
Name Null? Type
-------------------------- –-------------- ---------------------------
EMPNO NOT NULL NUMBER
ENAME NOT NULL VARCHAR2(10)
JOB NOT NULL VARCHAR2(10)
MANAGER_NO NUMBER
SAL NOT NULL NUMBER
COMMISSION NUMBER
4. Insert null values to the employee table and verify the result.
SQL> desc employee;
Name Null? Type
-------------------------- –-------------- ---------------------------
EMPNO NOT NULL NUMBER
ENAME NOT NULL VARCHAR2(10)
JOB NOT NULL VARCHAR2(10)
MANAGER_NO NUMBER
SAL NOT NULL NUMBER
COMMISSION NUMBER
SQL> insert into employee values(&empno,'&ename','&job',&manager_no,&sal,&commission);
Enter value for empno: 105
Enter value for ename: mohith
Enter value for job: salesman
Enter value for manager_no: 5678
Enter value for sal: null Enter value for commission: 50 old 1: insert into employee
values(&empno,'&ename','&job',&manager_no,&sal,&commission) new 1: insert into employee
values(105,'mohith','salesman',5678,null,50) insert into employee
values(105,'mohith','salesman',5678,null,50)
B
1. Create a user and grant all permissions to the user.
CONNECT <USER-NAME>/<PASSWORD>@<DATABASE NAME>;
--Create user query
CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;
--Provide roles
GRANT CONNECT,RESOURCE,DBA TO <USER NAME>;
--Assigning privileges
GRANT CREATE SESSION GRANT ANY PRIVILEGE TO <USER NAME>;
GRANT UNLIMITED TABLESPACE TO <USER NAME>;
--Provide access to tables.
GRANT SELECT, UPDATE, INSERT, DELETE ON <TABLE NAME> TO <USER NAME>;
2. Insert values in the department table and use commit.
SQL> insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 6
Enter value for deptname: sales Enter value for location: delhi old 1: insert into
department values(&deptno,'&deptname','&location') new 1: insert into department
values(6,'sales','delhi')
1 row created.
SQL> commit;
Commit complete.
SQL> select * from department;
DEPTNO DEPTNAME LOCATION
---------------- —------------------- —-------------------
1 accounting hyderabad
2 research chennai
3 sales bangalore
4 operations mumbai
5 accounting chennai
6 sales delhi
6 rows selected.
3. Add constraints like unique and not null to the department table. SQL> alter table
department modify(deptno number unique);
Table altered.
SQL> alter table department modify(location varchar2(10) not null);
Table altered.
SQL> DESC DEPARTMENT;
Name Null? Type
-------------------- ---------------- -------------------
DEPTNO NUMBER
DEPTNAME VARCHAR2(10)
LOCATION NOT NULL VARCHAR2(10)
4. Insert repeated values and null values into the table.
SQL> insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 2
Enter value for deptname: research
Enter value for location: old 1: insert into department
values(&deptno,'&deptname','&location') new 1: insert into department
values(2,'research','') insert into department values(2,'research','')
SQL> insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 2
Enter value for deptname: research Enter value for location: hyderabad old 1: insert
into department values(&deptno,'&deptname','&location') new 1: insert into
department values(2,'research','hyderabad') insert into department
values(2,'research','hyderabad')
1. Create a user and grant all permissions to the user.
CONNECT <USER-NAME>/<PASSWORD>@<DATABASE NAME>;
--Create user query
CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;
--Provide roles
GRANT CONNECT,RESOURCE,DBA TO <USER NAME>;
--Assigning privileges
GRANT CREATE SESSION GRANT ANY PRIVILEGE TO <USER NAME>;
GRANT UNLIMITED TABLESPACE TO <USER NAME>;
--Provide access to tables.
GRANT SELECT, UPDATE, INSERT, DELETE ON <TABLE NAME> TO <USER NAME>;
2. Insert values into the table and use commit.
SQL> insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 6
Enter value for deptname: sales Enter value for location: delhi old 1: insert into
department values(&deptno,'&deptname','&location') new 1: insert into department
values(6,'sales','delhi')
1 row created.
SQL> commit;
Commit complete.
SQL> select * from department;
DEPTNO DEPTNAME LOCATION
---------------- —------------------- —-------------------
1 accounting hyderabad
2 research chennai
3 sales bangalore
4 operations mumbai
5 accounting chennai
6 sales delhi
6 rows selected.
3. Delete any three records in the department table and use rollback.
SQL>DELETE FROM Department WHERE DEPTNO BETWEEN 4 AND 6;
SELECT * FROM Department;
GO
(3 rows affected)
DEPTNO DEPTNAME LOCATION
---------------- —------------------- —-------------------
1 accounting hyderabad
2 research chennai
3 sales bangalore
SQL>ROLLBACK;
SELECT * FROM Department;
GO
DEPTNO DEPTNAME LOCATION
---------------- —------------------- —-------------------
1 accounting hyderabad
2 research chennai
3 sales bangalore
4 operations mumbai
5 accounting chennai
6 sales delhi
4. Add constraint primary key and foreign key to the table.
SQL>ALTER TABLE Department
ADD PRIMARY KEY (DEPTNO);
ALTER TABLE Department
ADD FOREIGN KEY (DEPTNO) REFERENCES Employee (job);
(6 rows affected)
EXPERIMENT-4
AIM :- Queries using aggregate functions(COUNT,AVG,MIN,MAX,SUM),Group by, Order by,
Having.
(i) Create Employee table containing all Records.
(ii) Count number of employee names from employee table.
(iii) Find the Maximum age from employee table.
(iv) Find the Minimum age from employee table.
(v) Display the Sum of age employee table.
(vi) Display the Average of age from Employee table.
(vii) Create a View for age in employee table.
(viii) Display views.
(ix) Find grouped salaries of employees.(group by clause)
(x) Find salaries of employee in Ascending Order.(order by clause)
(xi) Find salaries of employee in Descending Order
(xii) Having salary<10000
(i) SQL> create table employee(E_id number, E_name varchar2(10),Age number, Salary
number);
Table created.
SQL> desc employee;
SQL> insert into employee values(&E_id, '&E_name', &Age, &Salary);
Enter value for E_id: 101
Enter value for E_name: Anu
Enter value for Age: 22
Enter value for sal: 9000
old 1: insert into employee values(&E_id, '&E_name', &Age, &Salary,)
new 1: insert into employee values(101,'Anu',22,9000)
1 row created.
SQL> /
Enter value for E_id: 102
Enter value for E_name: Shane
Enter value for Age: 29
Enter value for Salary: 8000
old 1: insert into employee values(&E_id,'&E_name', &Age, &Salary)
new 1: insert into employee values(102,'Shane', 29, 8000)
1 row created.
Similarly in total 7 records were inserted as per the given relation.
(ii) SELECT COUNT(E_name)
FROM employee;
(iii) SELECT MAX(Age)
FROM employee;
(iv) SELECT MIN(Age)
FROM employee;
(v) SELECT SUM(Age)
FROM employee;
(vi) SELECT AVG(Age);
FROM employee;
(vii) CREATE VIEW [Employee Age] AS
SELECT E_Name, Age
FROM employee
WHERE Age > 0; (?)
(viii) SELECT* FROM[Employee Age];
(ix) SELECT COUNT(E_id), Salary
FROM employee
GROUP BY Salary;
(x) SELECT * FROM employee
ORDER BY Salary ASC;
(xi) SELECT * FROM employee
ORDER BY Salary DESC;
(xii) SELECT COUNT(E_id), Salary
FROM employee
GROUP BY Salary;
HAVING COUNT(Salary) <10000;
