bcsl 34lab manual solution

7/30/2019 Bcsl 34lab Manual Solution

1/7

SECTION 3Database Management System Lab

Sessio

nEm1p: loyee_id Dept_name Gender Date of birth relationship

Ex 1: In this ses1

on youComputers

e datM

se f10/18/2007

eemanager

ent system of an ABC organization. Thesi need to creat aba or an Employ managemdetails about different tables are given below. According to that you can proceed further and create tables using MS-

Access.

Answer :EMPLOYEEMANAGEMENT SYSTEM

EMPLOYEE TABLECREATE TABLE EMPLOYEE (FIRSTNAME VARCHAR(20) NOT NULL, MIDDLENAME VARCHAR(20),LASTNAME VARCHAR(20) NOT NULL, EMPLOYEE_ID NUMBER PRIMARY KEY, DATEOFBIRTHDATE,ADDRESS VARCHAR(50), GENDER VARCHAR(2), SALARY NUMBER, DATEOFJOINING DATE,DEPT_NUMBER NUMBER);

DEPARTMENT TABLECREATE TABLE DEPARTMENT (DEPT_NAME VARCHAR(20) NOT NULL, DEPT_NUMBER NUMBERPRIMARY KEY, MANAGER_ID NUMBER, MA_DATE_OF_JOIN DATE);

DEPARTMENT LOCATION TABLECREATE TABLE DEPT_LOC (DEPT_NUMBER N UMBER, DEPT_LOC VARCHAR(30), DETP_LOC NUMBERPRIMARY KEY);

PROJECT TABLECREATE TABLE PROJECT (PROJECT_NAME VARCHAR(20), PROJECT_NUMBER NUMBER PRIMARYKEY, PROJECT_LOC VARCHAR(30) NOT NULL, DEPT_NUMBER NUMBER);

WORKS_ON TABLECREATE TABLE WORKS_ON (EMPLOYEE_ID NUMBER NOT NULL, PROJECT_NUMBER NUMBER NOTNULL, HOURS NOT NULL, EMP_PROJ NUMBER PRIMARY KEY);

DEPANDENT TABLECREATE TABLE DEPENDENT (EMPLOYEE_ID NUMBER, DEPENDENT_NAME VARCHAR(20), GENDERVARCHAR(2), DATEOFBIRTH DATE NOT NULL, REPLATIONSHIP VARCHAR(20) NOT NULL);

1) DEPAATMENT WISE DETAILSSELECT FIRSTNAME, MIDDLENAME, LASTNAME, EMPLOYEE_ID, DATEOFBIRTH, GENDER,DEPT_NAME FROM EMPLOYEE, DEPARTMENT WHEREEMPLOYEE.DEPTNUMBER=Department.DEPT_NUMBER ORDER BY DEPARTMENT.DEPT_NUMBER;

2) DEPARTMENTS LOCATED IN MORE THAN ONE LOCATIONSELECT DISTINCT(DEPT_LOC) FROM DEPARTMENT_LOCATION D W HERE EXISTS (SELECT *FROMDEPARTMENT D1 WHERE D.DEPT_NUMBER =D1.DEPT_NUMBER);

3) LIST PROJECTSSELECT PROJECT_NUMBER, PROJECT_NAME FROM PROJECT;

PROJECT_NUMBER PROJECT_NAME

1 HMS

2 PMS

3 EMS4) DEPENDENT OF THE EMPLOYEE WHOSE ID IS 1

SELECT *FROM DEPE4NDENT WHERE EMAPcLcOoYuEnEts_ID=1

Session2:

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 TABLECREATE TABLE BOOK_RECORDS (ACC_NUMBER NUMBER, ISBN_NUMBER NUMBER);INSERT INTO BOOK_RECORDS VALUES(100,10);


2/7

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);

BOOKSCREATE TABLE BOOKS (ISBN_NUMBER NUMBER PRIMARY KEY, AUTHOR VARCHAR(30), PUBLISHERVARCHAR(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 TABLECREATE TABLE MEMBERS (MEMBER_ID NUBMER PRIMARY KEY,MEMBER_NAME VARCHAR(30), MAX_BOOKS NUMBER, MAX_DAYS NUMBER);

MIeNmSEbReTr_IiNdTOAMcEcM_nBuEmRSbeVrALIUsEsSu(e1_0d,'XaZteXZXRZe',2tu,2r0n)_; dateINSERT INTO MEMBERS VALUES(11,'XYXYXY',2,20);INS1E0RT INTO MEM1B1ERSVALU1E2S/3(/1220,'0Y7XYXYX3Y/1',32/,20)0;7

INSERT INTO MEMBERS VALUES(13,'YZYZYZ',2,20);

BOOKS_ISSUECREATE TABLE BOOKS_ISSUE (MEMBER_ID NUMBER, ACC_NUMBER NUMBER, ISSUE_ATE DATE,RETISUBRN__DnAuTbEmDeArTE);author publisher price

INSERT IN1T2O BOOK_

XISYSZUA

EB

VCAL

XUXEXS

X(1

X0,X1X0,'12

1-00030-20007','13-03-2007');

INSERT INTO BOOK_ISSUE VALUES(10,11,'12-03-2007','13-03-2007');INSERT IN1T3O BOOK_XISYSZUAEBVCALUYEYSY(1Y1Y,1Y0,'121-030-02007','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 booksSELECT *FROM BOOKS;

Member_id Acc_number Issue_date Return_dateb) Get list of all members

S1E0LECT *FROM1M0EMBERS1; 2/3/2007 3/13/2007

10 11 12/3/2007 3/13/2007

11 10 12/3/2007 3/13/2007

11 11 1/1/2005 3/13/2007

12 10 1/1/2005 3/13/2007

c) Get the accession number of the books which are available in the librarySELECT DISTINCT(ACC_NUMBER) FROM BOOK_RECORDS;

acc_number

100

ISB1N01_nubmer author publisher price

10210310 XYZABC XXXXXXX 100

10411 XYZABC XXXXXXX 110105

d) List of books iss1u2edon 01X-YjaZnA-2B0C05XXXXXXX 10000

SELECT *FR1O3M BOOK_XISYSZUAEBCWHEYRYEYIYSYSUYE_D1A0T0E0=01/01/2005

Member_id Member_name Max_no_books Max_no_dayse) Get list of all books having price greater than Rs. 500/-

S1E0LECT*FROXMZBXOZOXKZS WHERE PRIC2E > 500 20

11 XYXYXY 2 20

12 YXYXYXY 2 20

13 YZYZYZ 2 20

f) Get the members who have not returned the bookSELECT DISTINCT(MEMBERS.MEMBER_NAME) FROM BOOK_ISSUE, MEMBERSWHERE (MEMBERS.MEMBER_ID) NOT IN (BOOK_ISSUE.MEMBER_ID)

MEMBER_NAME

XYXYXYXZXZXZ

YXYXYXYYZYZYZ

g) Book issue details

SELECT *FROM BOOK_ISSUE;


3/7

omer_id Name Area Ph

1 XYXYXY 040 525

2 XYXYXY 040 252

Expr1000 AREA

2 ABC

ose 1custo AerCsDwFROM

1CUS ODMERF

1 XYZ

sCtoumsteormbeeerl_oindging tNoaamreea aAbrcea Phone

1X

EXYXY ABC 5252525

5 ABCDEFG ABC 242424

h) Find the no of copies available of a book of given numberSELECT COUNCTus(*t)oFmReOrM_iBdOOKNRaEmCOe RDS WHERE ISBN_NUMBER=10;

Expr1000

2

1 XEXYXY

2 XEXYXY

Session4:

Ex 4: Create the following table and perform the necessary tasks defined below one by one. You must use the querytools/ SQL/ Reports/FCoursmtos/mGerra_pidhs/ViNewams/eusinAgreclaientP/sheorvneer wherever needed.

Answer : CU1

OMER TABXEXYXY ABC 5252525

ST LE5 XYZXYZ ABC 242424

a) Print entire customer tableSELECT *FROM CUSTOMER;

b) List the names of tSELECT NAME

ho have e as second letter in their namesWHERE NAME LIKE '_i%'

c) Find out the cSELECT *

e) Delete record where area is NULLDELETE FROM CUSTOMER WHERE AREA IS NULL

g) Create a table temp from customer having customer_id, name, and areaCREATE TEMPORARY TABLE TEMPTABLE (CUSTOMER_ID NUMBER,NAME VARCHAR(20),AREA

VARCHAR(20));

h) Display area and number of records from customer table within each areaSELECT COUNT (*), AREA FROM CUSTOMER GROUP BY AREA

MEMBER_ID MEMBER_NAME

12 YXYXYXY

13 YZYZYZ

i) Display all those records from customer table where name starts with a or area is abcT *FROM CUSTOMER WHERE NAME LIKE 'A%' OR AREA LIKE '%ABC%'SCEuLsEtCMEMBER_ID MEMBER_NAME

10 XZXZXZ

11 XYXYXY

one

2525

5250

3 YZYZYZY 040 222222

4 XZXZXZXZ 080 232323

Ex 5: Answer the fo5llowing quXeYriZeXs YuZsing L0i8b0rary sy2st4e2m42a4s created earlier. You must create a view to knowmember 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 operatorSELECT 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 WHEREM.MEMBER_ID IN (SELECT MEMBER_ID FROM BOOK_ISSUE);


4/7

EMP_NO NAME DEPT SALARY

REGION_CODE CITY SALESPERSON_CODE SALE_QTY

10 VIJAYAWADA 100 5

Ex 6: Create1a2

table of EmplDoEyLeeHI

(emp-number, na1m0e2, dept, salary) and

1D0

epartment (dept number, dept name).

Insert some records in the tables through appropriate forms having integrity checks. Add some records in employee

table where dep1a3rtment value iVs InZoAtGpresent in departm1e0n3t table. 5

Answer : EMPOYEE MANAGEMENT SYSTEM

EMPLOYEETABLECREATE TABLE EMPLOYEE(EMP_NO NUMBER PRIMARY KEY, NAME VARCHAR(20) NOT NULL, DEPTNUMBER, 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 TABLECREATE TABLE DEPARTMENT(DEPT_NO NUMBER PRIMARY KEY, DEPT_NAME VARCHAR(30) NOT

NULL);INSERT INTO DEPARTMENT VALUES(1,COMPUTERS);INSERT IN

ETMO

PD_

ENPOART

NM

AEN

MTE

VALSUAELS

A(2

R,AY

CCOUNTS);INSERT INTO10D1EPARTXMYEXNYTXVYALU5E0S0(030,SALES);

a) display employee10w2hereZdXeZpXarZtXment5i0s0N00ULLSELECT EMP_NO, NAME, SALARY FROM EMPLOYEE WHERE DEPT IS NULL

b) Employee table report

c) Employee records1w01hose XsaYlXarYyXlYess than th5e0s0a0l0ary of employee code is A100SELECT *FROM10E2MPLOZYXEZEXWZHXERESALAR5Y0= 5;

Session 6 :


5/7

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 TABLECREATE 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, QUANTITYNUMBER);

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 TABLECREATE TABLE BIO_DATE(NUM NUMBER PRIMARY KEY, NAME VARCHAR(20), DOB DATE, PLACEVARCHAR(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 TABLECREATE 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


6/7

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 TABLECREATE TABLE ITEM(ITEM_CODE NUMBER PRIMARY KEY, ITEM_NAME VARCHAR(50), QUANTITYNUMBER, 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 madefor the fields.

Answer : Employee leave table

EMP_LEAVE TABLECREATE TABLE EMP_LEAVE (EMP_ID NUMBER, REASON VARCHAR(50), L_DATE DATE, MONTHVARCHAR(5));

a) Leaves information group by Month

b) Leaves information group by Employee id


7/7

bcsl 34lab manual solution

Documents