Download IGNOU Second Semester Solved Lab Manual

SECTION – 3
Database Management System Lab
Session 1:
Ex 1: In this session you need to create database for an Employee management system of an ABC organization. The details about different tables are given below. According to that you can proceed further and create tables using MS-Access.
Answer :
EMPLOYEE MANAGEMENT SYSTEM
EMPLOYEE TABLE
CREATE TABLE EMPLOYEE (FIRSTNAME VARCHAR(20) NOT NULL, MIDDLENAME VARCHAR(20), LASTNAME VARCHAR(20) NOT NULL, EMPLOYEE_ID NUMBER PRIMARY KEY, DATEOFBIRTH DATE,ADDRESS VARCHAR(50), GENDER VARCHAR(2), SALARY NUMBER, DATEOFJOINING DATE, DEPT_NUMBER NUMBER);
DEPARTMENT TABLE
CREATE TABLE DEPARTMENT (DEPT_NAME VARCHAR(20) NOT NULL, DEPT_NUMBER NUMBER PRIMARY KEY, MANAGER_ID NUMBER, MA_DATE_OF_JOIN DATE);
DEPARTMENT LOCATION TABLE
CREATE TABLE DEPT_LOC (DEPT_NUMBER N UMBER, DEPT_LOC VARCHAR(30), DETP_LOC NUMBER PRIMARY KEY);
PROJECT TABLE
CREATE TABLE PROJECT (PROJECT_NAME VARCHAR(20), PROJECT_NUMBER NUMBER PRIMARY KEY, PROJECT_LOC VARCHAR(30) NOT NULL, DEPT_NUMBER NUMBER);
WORKS_ON TABLE
CREATE TABLE WORKS_ON (EMPLOYEE_ID NUMBER NOT NULL, PROJECT_NUMBER NUMBER NOT NULL, HOURS NOT NULL, EMP_PROJ NUMBER PRIMARY KEY);
DEPANDENT TABLE
CREATE TABLE DEPENDENT (EMPLOYEE_ID NUMBER, DEPENDENT_NAME VARCHAR(20), GENDER VARCHAR(2), DATEOFBIRTH DATE NOT NULL, REPLATIONSHIP VARCHAR(20) NOT NULL);
1) DEPAATMENT WISE DETAILS
SELECT FIRSTNAME, MIDDLENAME, LASTNAME, EMPLOYEE_ID, DATEOFBIRTH, GENDER, DEPT_NAME FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.DEPTNUMBER=Department.DEPT_NUMBER ORDER BY DEPARTMENT.DEPT_NUMBER;
2) DEPARTMENTS LOCATED IN MORE THAN ONE LOCATION
SELECT DISTINCT(DEPT_LOC) FROM DEPARTMENT_LOCATION D WHERE EXISTS (SELECT *FROM DEPARTMENT D1 WHERE D.DEPT_NUMBER =D1.DEPT_NUMBER);
3) LIST PROJECTS
SELECT PROJECT_NUMBER, PROJECT_NAME FROM PROJECT;
Employee_id
Dept_name
Gender
Date of birth
relationship
Computers
M
10/18/2007
manager
ISBN_nubmer
author
publisher
price
XYZABC
XXXXXXX
XYZABC
XXXXXXX
XYZABC
XXXXXXX
10000
XYZABC
YYYYYY
Member_id
Member_name
Max_no_books
Max_no_days
XZXZXZ
XYXYXY
YXYXYXY
YZYZYZ
4) DEPENDENT OF THE EMPLOYEE WHOSE ID IS ‘1’
SELECT *FROM DEPENDENT WHERE EMPLOYEE_ID=1
Session 2:
Ex 2: This session assume that you are developing a prototype database of the IGNOU library management system, for that you need to create the following tables:
(a) Book Records
(b) Book details
(c) Member details and
(d) Book issue details
Answer : LIBRARY MANAGEMENT SYSTEM
BOOK_RECORDS TABLE
CREATE TABLE BOOK_RECORDS (ACC_NUMBER NUMBER, ISBN_NUMBER NUMBER);
INSERT INTO BOOK_RECORDS VALUES(100,10);
INSERT INTO BOOK_RECORDS VALUES(101,11);
INSERT INTO BOOK_RECORDS VALUES(102,12);
INSERT INTO BOOK_RECORDS VALUES(103,13);
INSERT INTO BOOK_RECORDS VALUES(104,14);
INSERT INTO BOOK_RECORDS VALUES(105,15);
BOOKS
CREATE TABLE BOOKS (ISBN_NUMBER NUMBER PRIMARY KEY, AUTHOR VARCHAR(30), PUBLISHER VARCHAR(30), PRICE NUMBER);
INSERT INTO BOOKS VALUES(10,'XYZABC','XXXXXXX',100);
INSERT INTO BOOKS VALUES(11,'XYZABC','XXXXXXX',110);
INSERT INTO BOOKS VALUES(12,'XYZABC','XXXXXXX',210);
MEMBERS TABLE
CREATE TABLE MEMBERS (MEMBER_ID NUBMER PRIMARY KEY,
MEMBER_NAME VARCHAR(30), MAX_BOOKS NUMBER, MAX_DAYS NUMBER);
INSERT INTO MEMBERS VALUES(10,'XZXZXZ',2,20);
INSERT INTO MEMBERS VALUES(11,'XYXYXY',2,20);
INSERT INTO MEMBERS VALUES(12,'YXYXYXY',2,20);
INSERT INTO MEMBERS VALUES(13,'YZYZYZ',2,20);
BOOKS_ISSUE
CREATE TABLE BOOKS_ISSUE (MEMBER_ID NUMBER, ACC_NUMBER NUMBER, ISSUE_ATE DATE, RETURN_DATE DATE);
INSERT INTO BOOK_ISSUE VALUES(10,10,'12-03-2007','13-03-2007');
INSERT INTO BOOK_ISSUE VALUES(10,11,'12-03-2007','13-03-2007');
INSERT INTO BOOK_ISSUE VALUES(11,10,'12-03-2007','13-03-2007');
INSERT INTO BOOK_ISSUE VALUES(11,11,'12-03-2007','13-03-2007');
INSERT INTO BOOK_ISSUE VALUES(12,10,'12-03-2007','13-03-2007');
a) Get the list of all books
SELECT *FROM BOOKS;
b) Get list of all members
SELECT *FROM MEMBERS;
Member_id
Acc_number
Issue_date
Return_date
12/3/2007
3/13/2007
ISBN_nubmer
author
publisher
price
XYZABC
XXXXXXX
10000
XYZABC
YYYYYY
Member_id
Acc_number
Issue_date
Return_date
12/3/2007
3/13/2007
12/3/2007
3/13/2007
12/3/2007
3/13/2007
1/1/2005
3/13/2007
1/1/2005
3/13/2007
Customer_id
Name
Area
Phone
1
XYXYXY
040
5252525
2
XYXYXY
040
2525250
3
YZYZYZY
040
222222
4
XZXZXZXZ
080
232323
5
XYZXYZ
080
242424
c) Get the accession number of the books which are available in the library
SELECT DISTINCT(ACC_NUMBER) FROM BOOK_RECORDS; acc_number
100
101
102
103
104
105
d) List of books issued on 01-jan-2005
SELECT *FROM BOOK_ISSUE WHERE ISSUE_DATE=01/01/2005
e) Get list of all books having price greater than Rs. 500/-
SELECT *FROM BOOKS WHERE PRICE > 500
f) Get the members who have not returned the book
SELECT DISTINCT(MEMBERS.MEMBER_NAME) FROM BOOK_ISSUE, MEMBERS
WHERE (MEMBERS.MEMBER_ID) NOT IN (BOOK_ISSUE.MEMBER_ID) MEMBER_NAME
XYXYXY
XZXZXZ
YXYXYXY
YZYZYZ
g) Book issue details
SELECT *FROM BOOK_ISSUE;
h) Find the no of copies available of a book of given number
SELECT COUNT (*) FROM BOOK RECORDS WHERE ISBN_NUMBER=10; Expr1000
2
Session 4:
Ex 4: Create the following table and perform the necessary tasks defined below one by one. You must use the query tools/ SQL/ Reports/ Forms/ Graphs/Views/ using client/server wherever needed.
Answer : CUSTOMER TABLE
a) Print entire customer table
SELECT *FROM CUSTOMER;
Customer_id
Name
1
XEXYXY
2
XEXYXY
Customer_id
Name
Area
Phone
1
XEXYXY
ABC
5252525
5
XYZXYZ
ABC
242424
Expr1000
AREA
ABC
ACD
DEF
XYZ
Customer_id
Name
Area
Phone
1
XEXYXY
ABC
5252525
5
ABCDEFG
ABC
242424
MEMBER_ID
MEMBER_NAME
YXYXYXY
YZYZYZ
MEMBER_ID
MEMBER_NAME
XZXZXZ
XYXYXY
b) List the names of those customers who have ‘e’ as second letter in their names
SELECT NAME FROM CUSTOMER WHERE NAME LIKE '_i%'
c) Find out the customer belonging to area ‘abc’
SELECT *FROM CUSTOMER WHERE AREA LIKE 'ABC'
e) Delete record where area is NULL
DELETE FROM CUSTOMER WHERE AREA IS NULL
g) Create a table temp from customer having customer_id, name, and area
CREATE TEMPORARY TABLE TEMPTABLE (CUSTOMER_ID NUMBER,NAME VARCHAR(20),AREA VARCHAR(20));
h) Display area and number of records from customer table within each area
SELECT COUNT (*), AREA FROM CUSTOMER GROUP BY AREA
i) Display all those records from customer table where name starts with ‘a’ or area is ‘abc’
SELECT *FROM CUSTOMER WHERE NAME LIKE 'A%' OR AREA LIKE '%ABC%'
Ex 5: Answer the following queries using Library system as created earlier. You must create a view to know member name and name of the book issued to them, use any inbuilt function and operators like IN, ANY, ALL, EXISTS
Answer : LIBRARY MANAGEMENT SYSTEM
a) List the recors of members who have not been issues any book using EXISTS operator
SELECT MEMBER_ID, MEMBER_NAME FROM MEMBERS M WHERE NOT EXISTS
(SELECT *FROM BOOK_ISSUE B WHERE M.MEMBER_ID = B.MEMBER_ID);
b) List the members who have got issued at least one book.
SELECT MEMBER_ID, MEMBER_NAME FROM MEMBERS M WHERE
M.MEMBER_ID IN (SELECT MEMBER_ID FROM BOOK_ISSUE);
EMP_NO
NAME
SALARY
XYXYXY
50000
ZXZXZX
50000
EMP_NO
NAME
DEPT
SALARY
XYXYXY
50000
ZXZXZX
50000
YZYZYZ
50000
ZXYZXZ
50000
ABCDEF
50000
Ex 6: Create a table of Employee (emp-number, name, dept, salary) and Department (dept number, dept name). Insert some records in the tables through appropriate forms having integrity checks. Add some records in employee table where department value is not present in department table.
Answer : EMPOYEE MANAGEMENT SYSTEM
EMPLOYEE TABLE
CREATE TABLE EMPLOYEE(EMP_NO NUMBER PRIMARY KEY, NAME VARCHAR(20) NOT NULL, DEPT NUMBER, SALARY NUMBER NOT NULL);
INSERT INTO EMPLOYEE VALUES(100, 'XYZABC',1,50000);
INSERT INTO EMPLOYEE(EMP_NO, NAME, SALARY) VALUES(101, 'XYXYXY',50000);
INSERT INTO EMPLOYEE(EMP_NO, NAME, SALARY) VALUES(102, 'ZXZXZX',50000);
INSERT INTO EMPLOYEE VALUES(103, 'YZYZYZ',1,50000);
INSERT INTO EMPLOYEE VALUES(104, 'ZXYZXZ',1,50000);
INSERT INTO EMPLOYEE VALUES(105, 'ABCDEF',1,50000);
DEPARTMENT TABLE
CREATE TABLE DEPARTMENT(DEPT_NO NUMBER PRIMARY KEY, DEPT_NAME VARCHAR(30) NOT NULL);
INSERT INTO DEPARTMENT VALUES(1,’COMPUTERS’);
INSERT INTO DEPARTMENT VALUES(2,’ACCOUNTS’);
INSERT INTO DEPARTMENT VALUES(3,’SALES’);
a) display employee where department is NULL
SELECT EMP_NO, NAME, SALARY FROM EMPLOYEE WHERE DEPT IS NULL
b) Employee table report
c) Employee records whose salary less than the salary of employee code is ‘A100’
SELECT *FROM EMPLOYEE WHERE SALARY <(SELECT SALARY FROM
EMPLOYEE WHERE EMP_NO = 100);
d) Creating sales_data table
CREATE TABLE SALES_DATA(REGION_CODE NUMBER PRIMARY KEY, CITY VARCHAR(30), SALESPERSON_CODE NUMBER, SALE_QTY NUMBER)
SELECT *FROM SALES_DATA
REGION_CODE
CITY
SALESPERSON_CODE
SALE_QTY
HYDERABAD
DELHI
VIZAG
REGION_CODE
CITY
SALESPERSON_CODE
SALE_QTY
DELHI
VIZAG
e) Sales person sales details
SELECT *FROM SALES_DATA WHERE SALE_QTY >= 5;
Session 6 :
Ex 8: Create the following tables:
Order party : (Order number, Order date, customer code)
Order : Order number, Item code, Quantity
The key to the second table is order-number + item-code
Create a form for data entry to both the tables.
Answer : ORDER MANAGEMENT
ORDER PARTY TABLE
CREATE TABLE ORDER_PARTY(ORDER_NO NUMBER PRIMARY KEY, ORDER_DATE DATE, CURSTOMER_CODE NUMBER)
ORDER TABLE
CREATE TABLE ORDER(ORDER_NO NUMBER PRIMARY KEY, ITEM_CODE NUMBER, QUANTITY NUMBER);
1)ORDER AND ORDER PARTY DATA ENTRY FORM
Ex 9: Create a form for storing Bio-data of students. Create the supporting tables to store the data.
Answer : BIO DATA FORM
BIO DATA TABLE
CREATE TABLE BIO_DATE(NUM NUMBER PRIMARY KEY, NAME VARCHAR(20), DOB DATE, PLACE VARCHAR(20), QUALIFICATION VARCHAR(10), SKILLS VARCHAR(10), EXP NUMBER, ADDRESS VARCHAR(100));
BIO DATA ENTRY FORM
Ex 10: Design a suitable form for storing basic information and salary details of employees of an organization. Design and implement the necessary tables.
Answer : EMPLOYEE SALARY DETAILS
EMP TABLE
CREATE TABLE EMP(ENO NUMBER, ENAME VARCHAR(20), DEPT VARCHAR(20), SALARY NUMBER);
EMPLOYEE SALARY ENTRY FORM
Session 8 :
Ex 11: Design a form that shows the status of books pending on a member on entering the member-id.
Answer : Member book issue details using member id and member name
MEMBERS REPORT
Ex 12: Design a form that modifies the records of an Item Table having the fields: Item Code, Item Name, Quantity, Price, Re-order Level.
Answer : ITEM TABLE DETAILS
ITEM TABLE
CREATE TABLE ITEM(ITEM_CODE NUMBER PRIMARY KEY, ITEM_NAME VARCHAR(50), QUANTITY NUMBER, PRICE NUMBER, RE_ORDER_LEVEL NUMBER);
ITEM DETAILS ENTRY FORM
Ex 13: Design the form to display the leave information of each employee following. The validations must be made for the fields.
Answer : Employee leave table
EMP_LEAVE TABLE
CREATE TABLE EMP_LEAVE (EMP_ID NUMBER, REASON VARCHAR(50), L_DATE DATE, MONTH VARCHAR(5));
a) Leaves information group by Month
b) Leaves information group by Employee id