UNIX Tutorial One



Department

Of

Computer Science & Engineering

Data Base Management Systems Lab Manual

[pic]

BALAJI INSTITUTE OF TECHNOLOGY AND SCIENCE

Department of computer science & engineering

In-charge HOD Principal

| | | | |

|Prepared by: |Approved & Reviewed by: |Issued by: |w.e.f Date: |

| | | | |

[pic]

BALAJI INSTITUTE OF TECHNOLOGY AND SCIENCE

Laknepally(V), Narsampet, Warangal

DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

Lab Manual for the Academic Year 2011-12

(In accordance with JNTU syllabus)

SUBJECT : DBMS LAB

STREAM : CSE

H.O.D

INDEX

|S. No | Contents |Page. no |

|1 |Lab Objective |4 |

|2 |Introduction About Lab |5 |

|3 |Standard Operating Procedure – SOP |7 |

|3 |Guidelines to Students |8 |

|4 |Description about SQL, PL/SQL statements and D2K. |9 |

|5 |List of Lab Exercises |10 |

| |4.1 Syllabus Programs (JNTU) | |

| |4.2 Additional Programs | |

|6 |Background Theory |15 |

|7 |Solutions for Programs |21 |

|8 |PL-Sql Programs |51 |

|9 |Additional Viva Question |89 |

|10 |References |95 |

LAB OBJECTIVE

Upon successful completion of this Lab the student will be able to:

➢ Creating database objects

➢ Modifying database objects

➢ Manipulating the data

➢ Retrieving the data from the database server

➢ Performing database operations in a procedural manner using pl/sql

➢ Performing database operations (create, update, modify, retrieve, etc.,) using front-end tools like D2K.

➢ Design and Develop applications like banking, reservation system, etc.,

INTRODUCTION ABOUT LAB

There are 66 systems ( Compaq Presario ) installed in this Lab. Their configurations are as follows:

Processor : AMD Athelon ™ 1.67 GHz

RAM : 256 MB

Hard Disk : 40 GB

Mouse : Optical Mouse

Network Interface card : Present

Software

➢ All systems are configured in DUAL BOOT mode i.e., Students can boot from Windows XP or Linux as per their lab requirement. This is very useful for students because they are familiar with different Operating Systems so that they can execute their programs in different programming environments.

➢ Each student has a separate login for database access

Oracle 9i client version is installed in all systems. On the server, account for each student has been created.

This is very useful because students can save their work (scenarios’, pl / sql programs, data related projects, etc) in their own accounts. Each student work is safe and secure from other students.

➢ Latest Technologies like DOT NET and J2EE are installed in some systems. Before submitting their final project, they can start doing mini project from 2nd year onwards.

➢ MASM (Macro Assembler) is installed in all the systems

Students can execute their assembly language programs using MASM. MASM is very useful students because when they execute their programs they can see contents of Processor Registers and how each instruction is being executed in the CPU.

➢ Rational Rose Software is installed in some systems

Using this software, students can depict UML diagrams of their projects.

➢ Softwares installed : C, C++, JDK1.5, MASM, OFFICE-XP, J2EE and DOT NET, Rational Rose.

➢ Systems are provided for students in the 1:1 ratio.

➢ Systems are assigned numbers and same system is allotted for students when they do the lab.

STANDARD OPERATING PROCEDURE – SOP

a) Explanation on today’s experiment by the concerned faculty using OHP/PPT covering the following aspects: 25 mins.

1) Name of the experiment/Aim

2) Software/Hardware required

3) Algorithm

4) Test Data

1) Valid data sets

2) Limiting value sets

3) Invalid data sets

b) Writing of source program by the students 25 min.

c) Compiling and execution of the program 100 mins.

Writing of the experiment in the Observation Book:

The students will write the today’s experiment in the Observation book as per the following format:

a) Name of the experiment/Aim

b) Software/Hardware required

c) Algorithm

d) Source Program

e) Test Data

a. Valid data sets

b. Limiting value sets

c. Invalid data sets

f) Results for different data sets

g) Viva-Voc Questions and Answers

h) Errors observed (if any) during compilation/execution

i) Signature of the Faculty

Guidelines to Students

➢ Equipment in the lab for the use of student community. Students need to maintain a proper decorum in the computer lab. Students must use the equipment with care. Any damage is caused is punishable.

➢ Students are required to carry their observation / programs book with completed exercises while entering the lab.

➢ Students are supposed to occupy the machines allotted to them and are not supposed to talk or make noise in the lab. The allocation is put up on the lab notice board.

➢ Lab can be used in free time / lunch hours by the students who need to use the systems should take prior permission from the lab in-charge.

➢ Lab records need to be submitted on or before date of submission.

➢ Students are not supposed to use floppy disks

How to Write and execute sql, pl/sql commands/programs:

1). Open your oracle application by the following navigation

Start->all programs->oracle orahome.->application

development->sql.

2). You will be asked for user name, pass word and host string

You have to enter user name, pass word and host string as given

by the administrator. It will be different from one user to another user.

3). Upon successful login you will get SQL prompt (SQL>).

In two ways you can write your programs:

a). directly at SQL prompt

b). or in sql editor.

If you type your programs at sql prompt then screen will look like follow:

SQL> SELECT ename,empno,

2. sal from

3. emp;

where 2 and 3 are the line numbers and rest is the command /program……

to execute above program/command you have to press ‘/’ then enter.

Here editing the program is somewhat difficult; if you want to edit the previous command then you have to open sql editor (by default it displays the sql buffer contents). By giving ‘ed’ at sql prompt.(this is what I mentioned as a second method to type/enter the program).

in the sql editor you can do all the formatting/editing/file operations directly by selecting menu options provided by it.

To execute the program which saved; do the following

SQL> @ programname.sql

Or

SQL> Run programname.sql

Then press ‘\’ key and enter.

This how we can write, edit and execute the sql command and programs.

Always you have to save your programs in your own logins.

List of Lab Exercises

Syllabus Programs (JNTU)

|S. No |Name of the program |

|1 |Database Schema for a customer-sale scenario |

| | |

| |Customer(Cust id : integer, cust_name: string) |

| |Item(item_id: integer, item_name: string, price: integer) |

| |Sale(bill_no: integer, bill_data: date, cust_id: integer, item_id: integer, qty_sold: integer) |

| | |

| |For the above schema, perform the following— |

| |Create the tables with the appropriate integrity constraints |

| | |

| |Insert around 10 records in each of the tables |

| | |

| |List all the bills for the current date with the customer names and item numbers |

| | |

| |List the total Bill details with the quantity sold, price of the item and the final amount |

| | |

| |List the details of the customer who have bought a product which has a price>200 |

| | |

| |Give a count of how many products have been bought by each customer |

| | |

| |Give a list of products bought by a customer having cust_id as 5 |

| | |

| |List the item details which are sold as of today |

| | |

| |Create a view which lists out the bill_no, bill_date, cust_id, item_id, price, qty_sold, amount |

| |Create a view which lists the daily sales date wise for the last one week |

|2 |Database Schema for a Student Library scenario |

| | |

| |Student(Stud_no : integer, Stud_name: string) |

| |Membership(Mem_no: integer, Stud_no: integer) |

| |Book(book_no: integer, book_name:string, author: string) |

| |Iss_rec(iss_no:integer, iss_date: date, Mem_no: integer, book_no: integer) |

| | |

| |For the above schema, perform the following— |

| |Create the tables with the appropriate integrity constraints |

| |Insert around 10 records in each of the tables |

| |List all the student names with their membership numbers |

| |List all the issues for the current date with student and Book names |

| |List the details of students who borrowed book whose author is CJDATE |

| |Give a count of how many books have been bought by each student |

| |Give a list of books taken by student with stud_no as 5 |

| |List the book details which are issued as of today |

| |Create a view which lists out the iss_no, iss _date, stud_name, book name |

| |Create a view which lists the daily issues-date wise for the last one week |

|3 |Database Schema for a Employee-pay scenario |

| | |

| |employee(emp_id : integer, emp_name: string) |

| |department(dept_id: integer, dept_name:string) |

| |paydetails(emp_id : integer, dept_id: integer, basic: integer, deductions: integer, additions: integer, DOJ: date) |

| |payroll(emp_id : integer, pay_date: date) |

| | |

| |For the above schema, perform the following— |

| |Create the tables with the appropriate integrity constraints |

| |Insert around 10 records in each of the tables |

| |List the employee details department wise |

| |List all the employee names who joined after particular date |

| |List the details of employees whose basic salary is between 10,000 and 20,000 |

| |Give a count of how many employees are working in each department |

| |Give a names of the employees whose netsalary>10,000 |

| |List the details for an employee_id=5 |

| |Create a view which lists out the emp_name, department, basic, dedeuctions, netsalary |

| |Create a view which lists the emp_name and his netsalary |

|4 |Database Schema for a Video Library scenario |

| | |

| |Customer(cust_no: integer,cust_name: string) |

| |Membership(Mem_no: integer, cust_no: integer) |

| |Cassette(cass_no:integer, cass_name:string, Language: String) |

| |Iss_rec(iss_no: integer, iss_date: date, mem_no: integer, cass_no: integer) |

| |For the above schema, perform the following— |

| |Create the tables with the appropriate integrity constraints |

| |Insert around 10 records in each of the tables |

| |List all the customer names with their membership numbers |

| |List all the issues for the current date with the customer names and cassette names |

| |List the details of the customer who has borrowed the cassette whose title is “ The Legend” |

| |Give a count of how many cassettes have been borrowed by each customer |

| |Give a list of book which has been taken by the student with mem_no as 5 |

| |List the cassettes issues for today |

| |Create a view which lists outs the iss_no, iss_date, cust_name, cass_name |

| |Create a view which lists issues-date wise for the last one week |

|5 |Database Schema for a student-Lab scenario |

| | |

| |Student(stud_no: integer, stud_name: string, class: string) |

| |Class(class: string, descrip: string) |

| |Lab(mach_no: integer, Lab_no: integer, description: String) |

| |Allotment(Stud_no: Integer, mach_no: integer, dayof week: string) |

| |For the above schema, perform the following— |

| |Create the tables with the appropriate integrity constraints |

| |Insert around 10 records in each of the tables |

| |List all the machine allotments with the student names, lab and machine numbers |

| |List the total number of lab allotments day wise |

| |Give a count of how many machines have been allocated to the ‘CSIT’ class |

| |Give a machine allotment etails of the stud_no 5 with his personal and class details |

| |Count for how many machines have been allocatedin Lab_no 1 for the day of the week as “Monday” |

| |How many students class wise have allocated machines in the labs |

| |Create a view which lists out the stud_no, stud_name, mach_no, lab_no, dayofweek |

| |Create a view which lists the machine allotment details for “Thursday”. |

|6 |Write a program to find largest number from the given three numbers. |

|7 |Simple programs using loop, while and for iterative control statement. |

|8 |Write a program to check whether the given number is Armstrong or not |

|9 |Write a program to generate all prime numbers below 100. |

|10 |Write a program to demonstrate the GOTO statement. |

|11 |Write a program to demonstrate %type and %rowtype attributes |

|12 |Write a program to demonstrate predefined exceptions |

|13 |Write a program to demonstrate user defined exceptions |

|14 |Create a cursor, which displays all employee numbers and names from the EMP table. |

|15 |Create a cursor, which update the salaries of all employees as per the given data. |

|16 |Create a cursor, which displays names of employees having salary > 50000. |

|17 |Create a procedure to find reverse of a given number |

|18 |Create a procedure to update the salaries of all employees as per the given data |

|19 |Create a procedure to demonstrate IN, OUT and INOUT parameters |

|20 |Create a function to check whether given string is palindrome or not. |

|21 |Create a function to find sum of salaries of all employees working in depart number 10. |

|22 |Create a trigger before/after update on employee table for each row/statement. |

|23 |Create a trigger before/after delete on employee table for each row/statement. |

|24 |Create a trigger before/after insert on employee table for each row/statement. |

|25 |Create a Form to display employee details using SQL |

|26 |Create a Report to generate all employee annual salaries…. |

Additional Programs

|S. No |Name of the Program |

|1 |Create a form using Forms 6i to display Employee table data. |

|2 |Create a Master/details relationship form which perform Add New, Search, Delete, Save and Update on the records|

|3 |Generate a report to calculate employee’s salaries department wise from employee table. |

|4 |Create a Report to generate the details of employee table including sum and average salaries department wise. |

Background Theory

Oracle workgroup or server is the largest selling RDBMS product.it is estimated that the combined sales of both these oracle database product account for aroud 80% of the RDBMSsystems sold worldwide.

These products are constantly undergoing change and evolving. The natural language of this RDBMS product is ANSI SQL,PL/SQL a superset of ANSI SQL.oracle 8i and 9i also under stand SQLJ.

Oracle corp has also incorporated a full-fledged java virtual machine into its database engine.since both executable share the same memory space the JVM can communicate With the database engine with ease and has direct access to oracle tables and their data.

SQL is structure query language.SQL contains different data types those are

1. char(size)

2. varchar2(size)

3. date

4. number(p,s)

5. long

6. raw/long raw

Different types of commands in SQL:

A) DDL commands: - To create a database objects

B) DML commands: - To manipulate data of a database objects

C) DQL command: - To retrieve the data from a database.

D) DCL/DTL commands: - To control the data of a database…

DDL commands:

1. The Create Table Command: - it defines each column of the table uniquely. Each column has minimum of three attributes, a name , data type and size.

Syntax:

Create table ( (), ));

Ex:

create table emp(empno number(4) primary key, ename char(10));

2. Modifying the structure of tables.

a)add new columns

Syntax:

Alter table add(,);

f) foreign key constraint at column level.

Syntax:

(size>) references [];

g) foreign key constraint at table level

Syntax:

foreign key([,])references [(,)

h) check constraint

check constraint constraint at column level.

Syntax: (size) check()

i) check constraint constraint at table level.

Syntax: check()

DQL Commands:

12. Viewing data in the tables: - once data has been inserted into a table, the next most logical operation would be to view what has been inserted.

a) all rows and all columns

Syntax:

Select to from tablename;

Select * from tablename;

13. Filtering table data: - while viewing data from a table, it is rare that all the data from table will be required each time. Hence, sql must give us a method of filtering out data that is not required data.

a) Selected columns and all rows:

Syntax:

select , from ;

b) selected rows and all columns:

Syntax:

select * from where ;

c) selected columns and selected rows

Syntax:

select , from where;

14. Sorting data in a table.

Syntax:

Select * from order by , ;

DCL commands:

Oracle provides extensive feature in order to safeguard information stored in its tables from unauthoraised viewing and damage.The rights that allow the user of some or all oracle resources on the server are called privileges.

a) Grant privileges using the GRANT statement

The grant statement provides various types of access to database objects such as tables,views and sequences and so on.

Syntax:

GRANT

ON

TO

[WITH GRANT OPTION];

b) Reoke permissions using the REVOKE statement:

The REVOKE statement is used to deny the Grant given on an object.

Syntax:

REVOKE

ON

FROM;

Solutions for programs (JNTU Syllabus)

1. Database Schema for a customer-sale scenario

Customer(Cust id : integer, cust_name: string)

Item(item_id: integer, item_name: string, price: integer)

Sale(bill_no: integer, bill_data: date, cust_id: integer, item_id: integer, qty_sold: integer)

For the above schema, perform the following—

a) Create the tables with the appropriate integrity constraints

b) Insert around 10 records in each of the tables

c) List all the bills for the current date with the customer names and item numbers

d) List the total Bill details with the quantity sold, price of the item and the final amount

e) List the details of the customer who have bought a product which has a price>200

f) Give a count of how many products have been bought by each customer

g) Give a list of products bought by a customer having cust_id as 5

h) List the item details which are sold as of today

i) Create a view which lists out the bill_no, bill_date, cust_id, item_id, price, qty_sold, amount

j) Create a view which lists the daily sales date wise for the last one week

Aim: Create the tables with the appropriate integrity constraints and Insert around 10 records in each of the tables

HW/SW requirements:

Processor : AMD Athelon ™ 1.67 GHz

RAM : 256 MB

Hard Disk : 40 GB

Software : ORACLE

SQL> create table customer1 (cust_id number(5) primary key, cust_name varchar2(15));

Output: Table created.

SQL> desc customer1;

Output:

Name Null? Type

----------------------------------------- -------- ----------------

CUST_ID NOT NULL NUMBER(5)

CUST_NAME VARCHAR2(15)

Valid Test Data

b) SQL> insert into customer1 values(&custid,'&custname');

SQL> select * from customer1;

Output:

CUST_ID CUST_NAME

---------- ---------------

100 ramu

101 kamal

102 raju

103 raju sundaram

104 lawrence

SQL> create table item(item_id number(4) primary key,

item_name varchar2(15),price number(6,2));

SQL> dsec item

Output:

Name Null? Type

……………………………………………………………………………………………………

Cust_id NOT NULL NUMBER(4)

Item_name VARCHAR2(15)

PRICE NUMBER(6,2)

SQL>insert into item values(&item_id,’&item_name’,&price);

SQL> select * from item;

Output:

ITEM_ID ITEM_NAME PRICE

……………………………………………………………………………………..

2334 geera 6.25

4532 corn soup 34.65

2124 lays chips 20

4531 setwet 99.99

2319 duracell 45.5

SQL>create table sale(bill_no number(5) primary key,bill_date date, cust_id number(5) references customer(cust_id), item_id number(4) references item(item_id),qty_sold number(4));

Out put: Table Created.

SQL>dsec sale

Output:

Name Null? Type

………………………………………………………………………………………..

BILL_NO NOT NULL NUMBER(4)

BILL_DATE DATE

CUST_ID NUMBER(5)

ITEM_ID NUMBER(4)

QTY_SOLD NUMBER(4)

SQL>insert into Sale values(&bill_no, ’&bill_date’,

&cust_id, &item_id, &qty_sold);

SQL>select * from sale;

Output:

BILL_NO BILL_DATE CUST_ID ITEM_ID QTY_SOLD

………………………………………………………………………………………………………...

1450 04-JAN-06 100 2124 2

1451 04-JAN-06 101 2319 1

1452 04-JAN-06 103 4531 2

1453 04-JAN-06 102 2334 3

1454 04-JAN-06 104 4532 3

c) List all the bills for the current date with the customer names and item numbers

SQL> select c.custname, i.itemid, s.billno from customer c, item I, sale s

where c.custid=s.custid and

s.billdate=to_char(sysdate);

CUSTNAME ITEMID BILLNO

------------- --------- ---------

John 5001 332

d) List the total Bill details with the quantity sold, price of the item and the final amount

SQL> select i.price, s.qty,(i.price*s.qty) total from item I, sale s where i.itemid=s.itemid;

PRICE QTY TOTAL

------- ----- --------

120 2 240

20 3 60

5 2 10

10 1 10

350 4 1400

e) List the details of the customer who have bought a product which has a price>200

SQL> select c.custid, c.custname from customer c, sale s, item i where i.price>200 and

c.custid=s.custid and i.itemid=s.itemid;

CUSTID CUSTNAME

--------- --------------

4. duffy

f) Give a count of how many products have been bought by each customer

SQL> select custid, count(itemid) from sale group by custid;

CUSTID COUNT(ITEMID)

---------- ---------------------

1 2

3 1

4 1

5 1

g) Give a list of products bought by a customer having cust_id as 5

SQL> select i.itemname from item i, sale s where s.custid=5 and i.itemid-s.itemid;

ITEMNAME

--------------

Pens

h) List the item details which are sold as of today

SQL> select i.itemid, i.itemname from item I, sale s where i.itemid=s.itemid

and s.billdate=to_char(sysdate);

ITEMID ITEMNAME

--------- -------------

1234. pencil

i) Create a view which lists out the bill_no, bill_date, cust_id, item_id, price, qty_sold, amount

SQL>create view cust as (select s.billno, s.billdate, c.custid, i. iitemid, i.price, s.qty from customer c,sale s item I where c.custid=s.custid and i.iemid=s.itemid);

view created.

SQL>select * from cust;

BILLNO BILLDATE CUSTID ITEMID PRICE QTY

……………………………………………………………………………………………

3432 12-JAN-06 3 3244 120 2

4424 20-FEB-06 1 3456 20 3

332 13-MAR-06 1 1234 5 2

2343 10-MAR 5 5001 10 1

1331 11-MAR-06 4 76776 350 4

j) Create a view which lists the daily sales date wise for the last one week

Viva-Voce:

Q1. What is SQL?

Ans: Structured Query Language

2. What is database?

A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.

3. What is DBMS?

It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.

4. What is a Database system?

The database and DBMS software together is called as Database system.

5. Advantages of DBMS?

➢ Redundancy is controlled.

➢ Unauthorised access is restricted.

➢ Providing multiple user interfaces.

➢ Enforcing integrity constraints.

➢ Providing backup and recovery.

6. Disadvantage in File Processing System?

➢ Data redundancy & inconsistency.

➢ Difficult in accessing data.

➢ Data isolation.

➢ Data integrity.

➢ Concurrent access is not possible.

➢ Security Problems.

2. Database Schema for a Student Library scenario

Student(Stud_no : integer, Stud_name: string)

Membership(Mem_no: integer, Stud_no: integer)

Book(book_no: integer, book_name:string, author: string)

Iss_rec(iss_no:integer, iss_date: date, Mem_no: integer, book_no: integer)

For the above schema, perform the following—

a) Create the tables with the appropriate integrity constraints

b) Insert around 10 records in each of the tables

c) List all the student names with their membership numbers

d) List all the issues for the current date with student and Book names

e) List the details of students who borrowed book whose author is CJDATE

f) Give a count of how many books have been bought by each student

g) Give a list of books taken by student with stud_no as 5

h) List the book details which are issued as of today

i) Create a view which lists out the iss_no, iss _date, stud_name, book name

j) Create a view which lists the daily issues-date wise for the last one week

AIM: Create the tables with the appropriate integrity constraints

Insert around 10 records in each of the tables

HW/SW requirements:

Processor : AMD Athelon ™ 1.67 GHz

RAM : 256 MB

Hard Disk : 40 GB

Software : Oracle

SQL>create table student(stud_no number(5) primary key,stud_name varchar2(15));

SQL>desc student;

Name Null? Type

………………………………………………………………………………………..

STUD_NO NOT NULL NUMBER(5)

STUD_NAME VARCAHR2(15)

Valid Test Data:

SQL>insert into student values(&stud_no,’&stud_name’);

SQL>select * from student;

STUD_NO STUD_NAME

....................................................................

508 HARISH

513 BALAJI

518 RAKESH

524 PAVAN

534 JOYCE

SQL>create table membership(mem_no number(5) primary key,stud_no number(5) references student(stud)no));

SQL>dsec membership;

Name Null? Type

…………………………………………………………………………………………………….

MEM_NO NOT NULL NUMBER(5)

STUD_NO NUMBER(5)

SQL>insert into membership values(&mem_no,&stud_no);

Enter value for mem_no:5440

Enter value for stud_no:510

old 1:insert into membership values(&mem_no,&stud_no)

new 1:insert into membership values(5440,510)

insert into membership values(5440,510)

*

Errors Observed:

ERROR at line 1:

ORA-02291:integrity constraint(HARISH.SYS_C002724)violated-primary key not found

SQL>select * from membership;

MEM_NO STUD_NO

………………………………………………………………………..

5440. 513

5441. 508

5442. 518

5443. 534

5444. 524

SQL>create table book(book_no number(5) primary key,book_name varchar2(20),author varchar2(2));

SQL>desc book;

Name Null? Type

………………………………………………………………………………………..

BOOK_NO NOT NULL NUMBER(5)

BOOK_NAME VARCHAR2(20)

AUTHOR VARCHAR2(20)

SQL>insert into book values(&book_no,’&book_name’,’&author’);

SQL>select * from book;

BOOK_NO BOOK_NAME AUTHOR

………………………………………………………………………………………………..

9123 DBMS Rama Krishna

2342 JAVA Robett wilkins

4523 Fearless tales Alfred

8723 my ambition Harish

7821 Harry Potter JK Rowling

SQL>create table lss_rec(iss_no number primary key,iss_date date,mem_no number(5) references membership(mem_no),book_no number(5) references book(book_no));

SQL>desc iss_rec;

Name Null? Type

………………………………………………………………………………………………………

ISS_NO NOT NULL NUMBER

ISS_DATE DATE

MEM_NO NUMBER(5)

BOOK_NO NUMBER(5)

SQL>select * from iss_rec;

ISS_NO ISS_DATE MEM_NO BOOK_NO

…………………………………………………………………………………………………

43 05-JAN-06 5443 4523

81 28-DEC-05 5441 8723

22 08-DEC-05 5440 7821

53 07-JAN-06 5442 9123

35 06-JAN-06 5444 2342

c) List all the student names with their membership numbers

SQL> select s.studname, m.memno from student s, membership m where m.studno=s.studno;

STUDNAME MEMNO

------------- --------

abhijeet 1001

arun 1002

arvind 1003

ashish 1004

ashwin 1005

d) List all the issues for the current date with student and Book names

SQL> select i.issno, s.studname, b.bookname from iss_rec I, membership m, student s, book b

2 where i.memno=m.memno and m.studno=s.studno and

i.issdate=to_char(sysdate);

ISSNO STUDNAME BOOKNAME

------- ------------ ---------------

13 arvind P&S

e) List the details of students who borrowed book whose author is CJDATE

SQL> select * from student where studno in(select studno from membership where memno in

2 (select memno from iss_rec where bookno in(select bookno from book where author=’CJDATE’)));

STUDNO STUDNAME

---------- -------------

505. ashwin

f) Give a count of how many books have been bought by each student

SQL> select s.studno, count(i.bookno) from student s.membership m, book b, 2 iss_rec I where s.studno=m.studno and b.bookno=i.bookno group by s.studno;

STUDNO COUNT(I.BOOKNO)

---------- -----------------------

501 5

502 5

503 5

504 5

505 5

g) Give a list of books taken by student with stud_no as 5

SQL> select bookname from book where bookno in (select bookno from iss_rec where

2 memno in(select memno from membership where

3 studno in(select studno from student where studno=5)));

BOOKNAME

-------------

NT

h) List the book details which are issued as of today

SQL> delete from book where bookno in(select bookno from iss_rec where issdate=to_char(sysdate));

delete from book where bookno in (select bookno from iss_rec where issdate=to_char(sysdate))

Errors Observed:

ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.SYS_C00840) violated – child record found

i) Create a view which lists out the iss_no, iss _date, stud_name, book name

j) Create a view which lists the daily issues-date wise for the last one week

Viva-Vice:

1. Describe the three levels of data abstraction?

The are three levels of abstraction:

➢ Physical level: The lowest level of abstraction describes how data are stored.

➢ Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.

➢ View level: The highest level of abstraction describes only part of entire database.

2. Define the "integrity rules"

There are two Integrity rules.

➢ Entity Integrity: States that “Primary key cannot have NULL value”

➢ Referential Integrity: States that “Foreign Key can be either a NULL value or should be Primary Key value of other relation.

3. What is extension and intension?

Extension -

It is the number of tuples present in a table at any instance. This is time dependent.

Intension -

It is a constant value that gives the name, structure of table and the constraints laid on it.

4. What is System R? What are its two major subsystems?

System R was designed and developed over a period of 1974-79 at IBM San Jose Research Center. It is a prototype and its purpose was to demonstrate that it is possible to build a Relational System that can be used in a real life environment to solve real life problems, with performance at least comparable to that of existing system.

Its two subsystems are

➢ Research Storage

➢ System Relational Data System.

5. How is the data structure of System R different from the relational structure?

Unlike Relational systems in System R

➢ Domains are not supported

➢ Enforcement of candidate key uniqueness is optional

➢ Enforcement of entity integrity is optional

➢ Referential integrity is not enforced

6. What is Data Independence?

Data independence means that “the application is independent of the storage structure and access strategy of data”. In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.

Two types of Data Independence:

➢ Physical Data Independence: Modification in physical level should not affect the logical level.

➢ Logical Data Independence: Modification in logical level should affect the view level.

NOTE: Logical Data Independence is more difficult to achieve

7. What is a view? How it is related to data independence?

A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.

Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.

3. Database Schema for a Employee-pay scenario

employee(emp_id : integer, emp_name: string)

department(dept_id: integer, dept_name:string)

paydetails(emp_id : integer, dept_id: integer, basic: integer, deductions: integer, additions: integer, DOJ: date)

payroll(emp_id : integer, pay_date: date)

For the above schema, perform the following—

a) Create the tables with the appropriate integrity constraints

b) Insert around 10 records in each of the tables

c) List the employee details department wise

d) List all the employee names who joined after particular date

e) List the details of employees whose basic salary is between 10,000 and 20,000

f) Give a count of how many employees are working in each department

g) Give a names of the employees whose netsalary>10,000

h) List the details for an employee_id=5

i) Create a view which lists out the emp_name, department, basic, dedeuctions, netsalary

j) Create a view which lists the emp_name and his netsalary

AIM: Create the tables with the appropriate integrity constraints

Insert around 10 records in each of the tables

HW/SW requirements:

Processor : AMD Athelon ™ 1.67 GHz

RAM : 256 MB

Hard Disk : 40 GB

Software : Oracle

Create table employee(emp_id number(5) primary key,emp_name varchar2(25));

SQL>desc employee;

Name Null? Type

………………………………………………………………………………………..

EMP_ID NOT NULL NUMBER(5)

EMP_NAME VARCHAR2(25)

Valid Test Data:

SQL>insert into employee values(&emp_id,’&emp_name’);

SQL>select * from employee;

EMP_ID EMP_NAME

………………………………………………………….

10 Robert

21 Coulthard

30 Fernando Alonso

39 Kartikeyan

87 Kimmi

SQL>create table department(dept_id number(5) primary key,dept_name varchar2(20));

SQL>desc department;

Name Null? Type

………………………………………………………………………………………..

DEPT_ID NOT NULL NUMBER(5)

DEPT_NAME VARCHAR2(20)

SQL>insert into department values(&dept_id,’&dept_name’);

SQL>select * from department;

DEPT_ID DEPT_NAME

……………………………………………………………………………..

100. sales

101. accounts

102. administration

103. production

104. supervisor

SQL>create table paydetails(emp_id number(5) references employee(emp_id),dept_id number(5) reerences department(dept_id),basic number(7,2),deductions number(5,2),additions number(5,2),doj date);

SQL>desc paydetails;

Name Null? Type

………………………………………………………………………………………..

EMP_ID NUMBER(5)

DEPT_ID NUMBER(5)

BASIC NUMBER(7,2)

DEDUCTIONS NUMBER(5,2)

ADDITIONS NUMBER(5,2)

DOJ DATE

Different Data Sets:

SQL>insert into paydeatils values(&emp_id,&dept_id,

&basic,&deductions,&additions,&doj);

SQL>select * from paydeatils;

EMP_ID DEPT_ID BASIC DEDUCTIONS ADDITIONS DOJ

…………………………………………………………………………………………………………………..

10 101 25023.12 43.09 71.23 08-JAN-93

21 100 10500.29 23.98 40.9 01-JAN-06

30 102 6500.5 30.54 15 06-JUL-97

39 103 9700.45 32.78 65.09 08-AUG-03

87 104 15000 97.66 154.8 24-SEP-04

SQL>create table payroll(emp_id number(5)references employee(emp_id),pay_date date);

SQL>desc payroll;

Name Null? Type

………………………………………………………………………………………..

EMP_ID NUMBER(5)

PAY_DATE DATE

SQL>insert into payroll values(&emp_id,’&date’);

SQL>select * from payroll;

EMP_ID PAY_DATE

………………………………………………………….

10 31-JAN-06

21 03-FEB-06

30 15-JAN-06

39 27-JAN-06

87 04-FEB-06

c) List the employee details department wise

SQL>select empid,deptid from paydet;

EMPID DEPTID

…………………………

401. 500

402. 200

403. 600

404. 400

405. 1200

d) List all the employee names who joined after particular date

SQL>select e,empname from employee e,paydet p where e.empid=p.empid and p.doj>=’05-mar-06’;

EMPNAME

…………………

AVINASH

NITIN

PHALGUN

e) List the details of employees whose basic salary is between 10,000 and 20,000

sqL> Select empid,empname from employee where salary between 10000 and 20000;

EMPID EMPNAME

…………………………….

402. AKHILA

403. aaaaaaaa

EMPID EMPNAME

…………………………….

AKHILA

f) Give a count of how many employees are working in each department

SQL>select count(empid),deptid from paydet group by deptid;

COUNT (EMPID) DEPTID

………………………………………………………

1 200

1 400

1 500

1 600

1 1200

g) Give a names of the employees whose netsalary>10,000

SQL> select empname from employee where empid in(select empid from paydet where basic-deduction>10000);

EMPNAME

………………

AVINASH

AKHILA

HARISH

NITIN

PHALGUN

h) List the details for an employee_id=5

sql> select * from employee where empid=5;

empid empname

------------------------------------------

5 Coulthard

i) Create a view which lists out the emp_name, department, basic, dedeuctions, netsalary

j) Create a view which lists the emp_name and his netsalary

Viva-Vice:

13. What is Data Model?

A collection of conceptual tools for describing data, data relationships data semantics and constraints.

14. What is E-R model?

This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

15. What is Object Oriented model?

This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

16. What is an Entity?

It is a 'thing' in the real world with an independent existence.

17. What is an Entity type?

It is a collection (set) of entities that have same attributes.

18. What is an Entity set?

It is a collection of all entities of particular entity type in the database.

4. Database Schema for a Video Library scenario

Customer(cust_no: integer,cust_name: string)

Membership(Mem_no: integer, cust_no: integer)

Cassette(cass_no:integer, cass_name:string, Language: String)

Iss_rec(iss_no: integer, iss_date: date, mem_no: integer, cass_no: integer)

For the above schema, perform the following—

a) Create the tables with the appropriate integrity constraints

b) Insert around 10 records in each of the tables

c) List all the customer names with their membership numbers

d) List all the issues for the current date with the customer names and cassette names

e) List the details of the customer who has borrowed the cassette whose title is “ The Legend”

f) Give a count of how many cassettes have been borrowed by each customer

g) Give a list of book which has been taken by the student with mem_no as 5

h) List the cassettes issues for today

i) Create a view which lists outs the iss_no, iss_date, cust_name, cass_name

j) Create a view which lists issues-date wise for the last one week

AIM: Create the tables with the appropriate integrity constraints

Insert around 10 records in each of the tables

HW/SW requirements:

Processor : AMD Athelon ™ 1.67 GHz

RAM : 256 MB

Hard Disk : 40 GB

Software : Oracle

SQL>create table customer(cust_no number(5) primary key,cust_name varchar2(20));

SQL>desc customer;

Name Null? Type

……………………………………………………………………………………………………………..

CUST_NO NOT NULL NUMBER(5)

CUST_NAME VARCHAR2(20)

Valid Test Data:

SQL>insert into customer values(&cust_no,’&cust_name’);

SQL>select * from customer;

CUST_NO CUST_NAME

……………………………………………………………….

50. scott

51. pandey

52. varshney

53. naidu

54. bhimbra

SQL>create table membership(mem_no number(5) primary key,cust_no number(5) references customer(cust_no));

SQL>dsec membership;

Name Null? Type

………………………………………………………………………………………………………...

MEM_NO NOT NULL NUMBER(5)

CUST_NO NUMBER(5)

SQL>insert into memship values(&mem_no,&cust_no);

SQL>select * from memship;

MEM_NO CUST_NO

…………………………………………………

920 50

981 51

897 52

820 53

928 54

SQL>create table cassette(cass_no number(5) primary key,

Cass_name varchar2(15),language varchar2(15));

SQL>desc cassette;

Name Null? Type

………………………………………………………………………………………..

CASS_NO NOT NULL NUMBER(5)

CASS_NAME VARCHAR2(15)

LANGUAGE VARCHAR2(15)

SQL>insert into cassette values(&cass_no,’&cass_name’,’&language’);

SQL>select * from cassette;

CASS_NO CASS_NAME LANGUAGE

………………………………………………………………………………………

1 tagore telugu

2 the lion king English

3 anniyan tamil

4 indra telugu

5 lord of rings English

SQL>create table issu_rec(iss_no number(5) primary key,iss_date date,mem_no number(5)references memship(mem_no),cass_no number(5) references cassette(cass_no));

SQL>desc issu_rec;

Name Null? Type

………………………………………………………………………………………………………...

ISS_NO NOT NULL NUMBER(5)

ISS_DATE DATE

MEM_NO NUMBER(5)

CASS_NO NUMBER(5)

SQL>select * from issu_rec;

ISS_NO ISS_DATE MEM_NO CASS_NO

……………………………………………………………………………………

22 07-JAN-06 920 1

23 10-JAN-00 981 2

26 10-JAN-06 897 5

3 01-JAN-06 820 4

34 31-DEC-05 928 3

c) List all the customer names with their membership numbers

SQL>select c.custname,m.memno from customer1 c,membership1 m where c.custno=m.custno;

CUSTNAME MEMNO

……………….. ………………..

NIKHIL 51

VIVEK 52

SHRAVAN 58

VAMSI 57

SHIVA 56

d) List all the issues for the current date with the customer names and cassette names

SQL>select i.issno,c.custname,cc.cassettename from customer1 c,membership1 m,cassette cc,issrec1 I where i.issdate=to_char(sysdate) and c.custno=m.custno and i.cassno=cc.cassno and i.memno=m.memno;

OutPut:

no rows selected.

e) List the details of the customer who has borrowed the cassette whose title is “ The Legend”

f) Give a count of how many cassettes have been borrowed by each customer

g) Give a list of book which has been taken by the student with mem_no as 5

h) List the cassettes issues for today

i) Create a view which lists outs the iss_no, iss_date, cust_name, cass_name

j) Create a view which lists issues-date wise for the last one week

Viva-Vice:

19. What is an Extension of entity type?

The collections of entities of a particular entity type are grouped together into an entity set.

20. What is Weak Entity set?

An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.

21. What is an attribute?

It is a particular property, which describes the entity.

22. What is a Relation Schema and a Relation?

A relation Schema denoted by R(A1, A2, …, An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).

23. What is degree of a Relation?

It is the number of attribute of its relation schema.

24. What is Relationship?

It is an association among two or more entities.

25. What is Relationship set?

The collection (or set) of similar relationships.

5.Database Schema for a student-Lab scenario

Student(stud_no: integer, stud_name: string, class: string)

Class(class: string, descrip: string)

Lab(mach_no: integer, Lab_no: integer, description: String)

Allotment(Stud_no: Integer, mach_no: integer, dayof week: string)

For the above schema, perform the following—

a) Create the tables with the appropriate integrity constraints

b) Insert around 10 records in each of the tables

c) List all the machine allotments with the student names, lab and machine numbers

d) List the total number of lab allotments day wise

e) Give a count of how many machines have been allocated to the ‘CSIT’ class

f) Give a machine allotment etails of the stud_no 5 with his personal and class details

g) Count for how many machines have been allocatedin Lab_no 1 for the day of the week as “Monday”

h) How many students class wise have allocated machines in the labs

i) Create a view which lists out the stud_no, stud_name, mach_no, lab_no, dayofweek

j) Create a view which lists the machine allotment details for “Thursday”

AIM: Create the tables with the appropriate integrity constraints

Insert around 10 records in each of the tables

HW/SW requirements:

Processor : AMD Athelon ™ 1.67 GHz

RAM : 256 MB

Hard Disk : 40 GB

Software : Oracle

SQL>create table stu(stud_no number(5) primary key,stud_nam varchar2(20),class varchar2(20));

SQL> desc stu;

Name null? Type

STUD_NO NOT NULL NUMBER(5)

STUD_NAM VARCHAR2(20)

CLASS VARCHAR2(20)

Valid Data Sets:

SQL> insert into stu values(&stud_no,’&stud_nam’,’&class’);

SQL> select * from stu;

STUD_NO STUD_NAM CLASS

39 LEON CSE

34 VIKAS CSIT

18 MATHEW ECE

8 HANSEN MECH

24 ALEXIS EEE

SQL> Create table class (class varchar2(20), descript varchar2(10));

SQL> Describe class;

Name null type

CLASS VARCHAR2(10)

DESCRIPT VARCHAR2(20)

SQL> create table lab(match_no number(5), lab_no number(5), description varchar2(20));

SQL> desc lab;

Name null type

MACH_NO NOT NULL NUMBER(5)

LAB_NO NUMBER(5)

DESCRIPTION VARCHAR2(20)

SQL> insert into lab values(&mach_no,&lab_no,’&description’);

SQL> select * from lab;

MATCH_NO LAB_NO DESCRIPTION

--------------- --------- --------------------

23 7 physics

78 2 chemistry

87 1 edc

12 10 cds

8 3 java lab

SQL> create table allotment(stud_no number(5) references stu(stud_no), match_no number(5) references lab(mach_no),

Doweek varchar2(20));

SQL> desc allotment;

Name Null? Type

-------------- ------- ---------

STUD_NO NUMBER(5)

MACH_NO NUMBER(5)

DOWEEK VARCHAR2(20)

SQL>select * from allotment;

STUD_NO MACH_NO DOWEEK

------------- -- ------------ ------------

39 23 sat

34 87 mon

18 78 tue

8 12 wed

24 12 thu

c) List all the machine allotments with the student names, lab and machine numbers

SQL>select s.studname,l.machno from student1 s,lab l,allotment a where a.machno=l.machno and a.studno=s.studno;

STUDNAME MACHNO

………………………………………..

ABHIJEET 1

KALYAN 22

ASHWIN 3

ARKA 4

ARVIND 5

d) List the total number of lab allotments day wise

SQL>select l.machno,l.descrip,a.day from lab l,allotment a where a.machno=l.machno;

MACHNO DESCRIP DAY

……………………………………………………………………

1 UNIX MONDAY

22 UNIX TUESDAY

3 XP WEDNESDAY

4 WINDOWS THRUSDAY

5 ME FRIDAY

e) Give a count of how many machines have been allocated to the ‘CSIT’ class

SQL>select count(machno)from allotment where studno in(select studno from student1 where class=’CSIT’);

COUNT (MACHNO)

……………………..

1

f) Give a machine allotment etails of the stud_no 5 with his personal and class details

SQL>select a.studno,a.machno,s.studname,s.class from allotment a,student1 s where a.studno=s.studno and a.studno=503;

STUDNO MACHNO STUDNAME CLASS

………………………………………………………………………………………………………

503 5 ARVIND CSE

g) Count for how many machines have been allocatedin Lab_no 1 for the day of the week as “Monday”

h) How many students class wise have allocated machines in the labs

SQL>select count(studno) “allocated students in the labs”,class from student1 where studno in(select studno from allotment) group by class;

allocated students in the lab CLASS

……………………………………………………………………………

2 CSE

1 ECE

1 EEE

1 IT

i) Create a view which lists out the stud_no, stud_name, mach_no, lab_no, dayofweek

j) Create a view which lists the machine allotment details for “Thursday”

Viva-Vice:

26. What is Relationship type?

Relationship type defines a set of associations or a relationship set among a given set of entity types.

27. What is degree of Relationship type?

It is the number of entity type participating.

25. What is DDL (Data Definition Language)?

A data base schema is specifies by a set of definitions expressed by a special language called DDL.

26. What is VDL (View Definition Language)?

It specifies user views and their mappings to the conceptual schema.

27. What is SDL (Storage Definition Language)?

This language is to specify the internal schema. This language may specify the mapping between two schemas.

28. What is Data Storage - Definition Language?

The storage structures and access methods used by database system are specified by a set of definition in a special type of DDL called data storage-definition language.

29. What is DML (Data Manipulation Language)?

This language that enable user to access or manipulate data as organised by appropriate data model.

➢ Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.

➢ Non-Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data.

31. What is DML Compiler?

It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.

6). Write a program to find largest number from the given three numbers.

Aim: To find largest number from the given three numbers.

HW/SW requirements:

Processor : AMD Athelon ™ 1.67 GHz

RAM : 256 MB

Hard Disk : 40 GB

Software : Oracle, PlSQL

Algorithm:

Step 1: Declare the variable A, B, and C.

Step 2: Store the valid data.

Step 3: Compare variable A with B and A with C

Step 4: If the value stored in variable A is big, it displays “A is Big”. (IF conditional statement should be used)

Step 5: Compare variable B with C

Step 6: If the value stored in variable B is big, it displays “B is Big”.

Step 7: other wise it displays “C is Big”

Declare

A number;

B number;

C number;

Begin

A:=&a;

B:=&b;

C:=&c;

If a > b && a> c then

Dbms_output.put_line(‘ A is big ‘);

Else

If( b>c && b> a ) then

Dbms_output.put_line(‘ B is big ‘);

Else

Dbms_output.put_line(‘ C is big ‘);

End if;

End if;

End;

Valid Data Sets:

Enter the value of a:

1

Enter the value of b:

2

Enter the value of c:

3

OUTPUT:

C is big

Invalid Data sets :

Enter the value of a:

y

Enter the value of b:

x

Enter the value of c:

a

Output:

Invalid data types.

Viva-Vice:

31. What is Pl-SQL ?

Procedural Language Structured Query Language

32. What is Query evaluation engine?

It executes low-level instruction generated by compiler.

33. What is DDL Interpreter?

It interprets DDL statements and record them in tables containing metadata.

34. What is Record-at-a-time?

The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.

35. What is Set-at-a-time or Set-oriented?

The High level or Non-procedural DML can specify and retrieve many records in a single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.

36. What is Relational Algebra?

It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.

37. What is Relational Calculus?

It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.

7). Simple programs using loop, while and for iterative control statement.

a) To generate first 10 natural numbers using loop, while and for.

AIM: To generate first 10 natural numbers using loop, while and for.

HW/SW requirements:

Processor : AMD Athelon ™ 1.67 GHz

RAM : 256 MB

Hard Disk : 40 GB

Software : Oracle, PlSQL

Algorithm:

Step 1: Declare the variable I.

Step 2: Store the valid data 1 in I.

Step 3: Use LOOP statement

Step 4: Display the first value.

Step 5: Increment the value of I by 1 value.

Step 6: check the value up to 10 no. and repeat the loop

Step 7: If condition exceeds the given value 10, the loop will be

terminated.

/* using loop statement */

Declare

I number;

Begin

I:=1;

Loop

Dbms_output.put_line(I);

I:=I+1;

Exit when I>10;

End loop;

End;

Algorithm: for WHILE loop

Step 1: Declare the variable I.

Step 2: Store the valid data 1 in I.

Step 3: Use WHILE statement

Step 4: Check the value of I with value 10.

Step 5: if the value of I reached to 10 the loop will be terminated

Step 6: otherwise display value of I

Step 7: increment the next value of I using I=I+1.

/* using while */

Declare

I number;

Begin

I:=1;

While (I ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download