Sona College of Technology DBMS Lab Manual

SONA COLLEGE OF TECHNOLOGY

LABORATORY MANUAL

DEPARTMENT OF INFORMATION TECHNOLOGY

DATABASE MANAGEMENT SYSTEMS LAB

Thiagarajar Polytechnic College Road, Salem – 636 005

Ph: (0427) 2443545, 2446898, 2447907 Fax: (0427) 2449174

Email: sonatec@vsnl.com www.sonatech.ac.in

1
Sona College of Technology DBMS Lab Manual

Subject : Database Management Systems Lab

Subject Code : U15IT506R

College/University : Sona College of Technology(Autonomous)

Year of Syllabus : 2020-2021

Revision No : 01

Issue No : 01

PREPARED BY REVIEWED BY APPROVED BY

Name Mr. JL.Aldo Stalin J.Jeba Emilyn Dr.J.Akilandeswari

Ms. I. Janani ASP/ IT
HOD/IT

Signatur
e

Date 08/6/20 15/6/20 19/6/20

2
Sona College of Technology DBMS Lab Manual

GENERAL INSTRUCTIONS

 Leave your footwear outside the lab.

 Clean the terminals

 The Students should get their observations signed from the Lab-in-charge in the
first 15 minutes.

 The Students should proceed with the program execution thereafter.

 The Students should attend the VIVA VOCE simultaneously.

 The Students should COMPLETE the given exercise within the Lab session. The
RECORD WORK of the exercise should be submitted in the next week.

 The Students should maintain STRICT SILENCE inside the Lab.

3
Sona College of Technology DBMS Lab Manual

SYSTEM CONFIGURATION

SERVER : HP  ProLiant  BL280C G6

BLADE SERVER
 Dual Processor Capable Model
 Quad-core Intel Xeon Processor E5504 Processor
 (4M Cache,2.00 GHz, 4.80 GT/s Intel QPI)
 4GB PC3-10600E-9 RAM
 Software SATA RAID
 HP 250 GB SATA 5.4k SFF ETY HDD

CLIENT :
 6th Gen. Intel Core i5(6M, 3.3 Ghz)
 500 GB SATA HDD
 2*4 GB DDR 3 RAM
 18.5" TFT Monitor
 USB Keyboard & Optical Mouse

ORACLE SERVER :
      ORACLE 11G HOME SERVER &11G CLIENT

4
Sona College of Technology DBMS Lab Manual

DATABASE MANAGEMENT SYSTEMS LAB 0 0 4 2

COURSE OUTCOMES
At the end of the course, the student will be able to
1. Build tables, construct relationships among them and retrieve data with simple and
complex queries
2. Build various constraints, triggers and indexes on the tables
3. Design and implement a database and to integrate into a simple application

LIST OF EXPERIMENTS

1. Create a relational database system using DDL commands with constraints

2. Update the database system using DML commands
3. Query the database using simple and complex queries
4. Create and update views
5. High level programming language extensions (Control structures, Procedures and
Functions)
6. Create triggers
7. Create assertions and indexes
8. Use of front end tools to manipulate the database
9. Generate reports using a reporting tool
10. Database Design and implementation of an application system. (Suggested Mini Project)

5
Sona College of Technology DBMS Lab Manual

INTRODUCTION
A Database management is a system
 Involving data,
 The hardware that physically stores that data,
 The software that utilizes the hardware’s file system
In order to
1. Store the data and
2. provide a standardized method for retrieving or changing the data,
 The users who turn the data into information

TOOLS OF ORACLE
The tools required to access the Oracle database are
 SQL * Plus
 PL/SQL
 Forms
 Reports
SQL * Plus
SQL * Plus is a Structured Query Language supported by Oracle. Through SQL *Plus we
can store, retrieve, edit , enter and run SQL commands and PL/SQL blocks. Using SQL * Plus
we can perform calculations, list column definitions for any table and can also format query
results in the form of a report.

6
Sona College of Technology DBMS Lab Manual

DDL Data Definition

SQL

DML Data Manipulation

DCL Data Control

DATA DEFINITION LANGUAGE (DDL)

DDL is used to define the schema of the database (a collection of database objects, e.g.
tables, views, indexes, sequences).
o Create, Drop or Alter a table
o Define Integrity constraints
o Define access privileges to users
o Define access privileges on objects
SYNTAX

1.CREATE

create table tablename (column1 data type , column2 data type, ………);

Example

create table emp ( eno number(3));

2.ALTER

1. alter table tablename modify(column1 data type , column2 data type, ………);

2. alter table tablename add (column1 data type , column2 data type, ………);

Example

7
Sona College of Technology DBMS Lab Manual
1.alter table emp modify(eno number(5));

2.alter table emp add(ename varchar2(10));

3.DROP

drop table tablename;

Example

drop table emp;

4.TRUNCATE

truncate table tablename;

Example

truncate table emp;

DATA TYPES

 A table is made up of one or more columns

 Each column is given a name and a data type that reflects the kind of data it will store.
 Oracle supports four basic data types
 CHAR
 NUMBER
 DATE
 RAW.
 There are also a few additional variations on the RAW and CHAR data types.

VARCHAR2
 Character data type.
 Can contain letters, numbers and punctuation.
 The syntax: VARCHAR2(size) where size is the maximum number of alphanumeric
characters the column can hold.
8
Sona College of Technology DBMS Lab Manual
 In Oracle8, the maximum size of a VARCHAR2 column is 4,000 bytes.

NUMBER
 Numeric data type.
 Can contain integer or floating point numbers only.
 The syntax : NUMBER(precision, scale) where precision is the total size of the number
including decimal point and scale is the number of places to the right of the decimal.
 For example, NUMBER(6,2) can hold a number between -999.99 and 999.99.

DATE
 Date and Time data type.
 Can contain a date and time portion in the format: DD-MON-YY HH:MI:SS.
 No additional information needed when specifying the DATE data type.
 the time of 00:00:00 is used as a default.
 The output format of the date and time can be modified

RAW
 Free form binary data.
 Can contain binary data up to 255 characters.
 Data type LONG RAW can contain up to 2 gigabytes of binary data.
 RAW and LONG RAW data cannot be indexed and cannot be displayed or queried in
SQL*Plus.
 Only one RAW column is allowed per table.

LOB
 Large Object data types.
 These include BLOB (Binary Large Object) and CLOB (Character Large Object).
 More than one LOB column can appear in a table.
 These data types are the preferred method for storing large objects such as text
documents (CLOB), images, or video (BLOB).

DATA MANIPULATION LANGUAGE (DML)

9
Sona College of Technology DBMS Lab Manual
The Data Manipulation Language (DML) is used to retrieve, insert and modify database
information. These commands will be used by all database users during the routine operation of
the database

SYNTAX

1. INSERT
insert into tablename values(list of table values);

Example
insert into emp values (501, ‘sona’);
2.SELECT
1. select * from tablename;
2. select column 1,column 2,….,column n from tablename;
Example
1. select * from emp;
2. select eno, eid from emp;

3.UPDATE
update tablename set column name = value where condition ;
Example
update emp set eno = 2 where eid = 345;
4.DELETE
delete from tablename where conditions;
Example
delete from emp where eno=2;

OBJECTS OF ORACLE

VIEW

A view is an imaginary table and it contains no data and the tables upon which a
view is based are called base tables.

10
Sona College of Technology DBMS Lab Manual
SYNONYM

A synonym is a database object, which is used as an alias(alternative name) for a

table, view or sequence.

SEQUENCE

A sequence is a database object which can generate unique, sequential integer

values. It can be used to automatically generate primary key or unique key values. A sequence
can be either an ascending or a descending sequence

CONSTRAINTS

Objective

To enforce some restrictions on the data in the database

Description

An integrity constraint is a mechanism used by Oracle to prevent invalid data entry into
the table.

Types
Domain integrity constraint
Entity integrity constraint
Referential integrity constraint

Domain integrity constraint – set a range

Types
Not null constraint
Check constraint

Entity integrity constraints types

Unique constraints
Primary key constraints

Implementation

Create table <table name> (columnname <type> constraint n1 not null,………);

Create table <table name> (columnname <type> constraint n2 check(condition),………);

Create table <table name> (columnname <type> constraint n3 unique (columnname),………);

11
Sona College of Technology DBMS Lab Manual
Create table <table name> (columnname <type> constraint n4 primary key,………);

DATA CONTROL LANGUAGE (DCL)

DCL statements are those which are used to control access permissions on the tables,
indexes, views and other elements of the DBMS.

SYNTAX

1.GRANT

grant privileges on <object-name> to <username>

select , insert, delete , all

Example

grant all on customer to accounts;

2.REVOKE

revoke privileges on <object-name> from <username>

select , insert, delete , all

Example

revoke all on customer from accounts;

12
Sona College of Technology DBMS Lab Manual

DATA DEFINITION LANGUAGE(DDL) AND DATA MANIPULATION

LANGUAGE(DML)

Objective
To write simple, compound, nested and correlated queries and manipulate the
base table using objects of oracle.

Description

o Create necessary tables

o Store the collected data
o Write the queries for the given questions to retrieve the necessary information
from the database

Sample Query

Select Sname,Degree from CLASS where percentage > 75

13
Sona College of Technology DBMS Lab Manual
Sample Output

Sname City

Banu B.E

1. DDL Commands

1. Create a table called ‘CLASS’ with the details given in APPENDIX.

2. Create a table called ‘WORKER’ with the details given in APPENDIX
3. Create a table called ‘EMPLOYEE’ with the details given in APPENDIX
4. Create a table called ‘STUDENT’ with the details given in APPENDIX
5. Describe ‘CLASS ’.
6. Create a table level constraint for the above tables.
(Hint: Table Level constraint is Entity integrity constraints.)
7. For the above tables also add column level constraints.
(Hint: Column level constraint is domain constraint)
8. For the above tables add referential integrity constraint.
9. Write the command used for adding a constraint to the existing table.
Using the command add the constraints appropriately. (Any table of your interest)
10. Write the command used for deleting a foreign key constraint. Show this with the help of
an example.
11. Write a command to view all the created database constraints.
12. Write a command to view only the table constraints.
13. Write a command to drop the constraints.
14. Create a table called ‘SOFTWARE’ with the details given in Appendix.
15. Create a table called ‘PROGRAMMER’ with the details given in Appendix.
16. Create a table called ‘STUDIES ’ with the following detail

2. SIMPLE QUERIES

14
Sona College of Technology DBMS Lab Manual
17. Display the worker names whose skill-type is plumbing.
18. Display the details of students whose percentage falls between 65 and 75.
19. Display only those rows from class whose degree begins with ‘B’;
20. Find out the name of your user area.
21. Display only distinct degree from the table ‘CLASS’ IN descending order.
22. Find the names of all students having ‘a’ as the second letter in their names.
23. Count the number of employees whose grade is ‘B’.
24. Calculate the average salary of all employees.
25. Calculate the minimum salary of the employees from the employee table.
26. Display ‘20th September 1996’ in the date format.
27. Display the last 3 characters of the name ‘Bombay’.
28. Display the hourly rate of the each worker without decimal places.
29. Write a SQL command to find all the users in your domain.
30. For the DOB given below find the age as on 1-6-2002.
DOB – 05/04/70
31. Display the date 60 days before the current date.
32. Given the date of joining, find the experience.
33. Write queries using the character, numeric, miscellaneous functions supported by Oracle.
(at least 2 queries for each category).
34. Display the system date in the format ‘14th of November 2000’.
35. Decode the department fields in the ‘student’ table as follows:
CSE – Computer Science
EEE – Electrical
ECE – Electronics
IT – InfoTech
MECH – Mechanical
36. Sort the student’s table in ascending order.
37. Calculate total marks for each student.
38. Display the names of the student whose name sounds as ‘sa’.

3. COMPOUND QUERIES

1. List the workers who receive higher wage than the average wage. (Consider Daily Wage
and 8hrs/day)
2. What is the weekly wage for each plumber? (Assume 8 hrs/day and 6 days/week)
3. What is the average mark scored by CSE students?
4. Find out the selling cost average for the package developed in Pascal.
5. Display the details of the software developed by ‘Reshma’ and ‘Rekha’
6. How many programmers studied at SSI?
7. What is the average age of female programmers
8. List the programmers who celebrate their birthdays during current month.
9. How many people draw salary from 2000 to 5000?
10. Display each institute name with number of students.
11. What are the languages known by the male programmers?
12. Print the names of the packages that are not developed in C or Pascal.
13. Display the name, DOB and software developed by each programmer.
14. Display the details of the SW developed by the programmers who joined before Jan ’90.
15. In which institute did the person who developed the costliest package studied?
15
Sona College of Technology DBMS Lab Manual
16. Display the details of those who do not know COBOL or Pascal.
17. Display the number of packages sold by each programmer.
18. How old is the oldest male programmer?
19. Display the number of packages developed in each language except C and C++.
20. Write a Query that selects the first programmer in an alphabetical order, whose name
begins with ‘r’.
21. Write a Query on the ‘software ’ that will find the highest copies sold in each language.
Put the output in this format. “ For the language (language) the Maximum no. of copies
sold is :(max sold)”
22. Write a Query that selects the highest cost spent by each programmer for the developing
the package.

16
Sona College of Technology DBMS Lab Manual
4. NESTED AND CORRELATED QUERIES

1. Display the details of those who are drawing the same salary.
2. Using correlated query find the highest paid C programmer.
3. Who is the highest paid COBOL female programmer?
4. Which language listed in prof1 and prof2 has not been used to develop any package?
5. List the programmers who joined in the same day.
6. Who is the least experienced programmer?
7. Display the number of packages, number of copies sold and sales value of each
programmer Institute-wise.
8. Display the details of software developed in dbase by male programmers who belong to
the institute in which most number of programmers studied.
9. Display the details of the software that was developed in the language, which is neither
the first, nor the second proficiency of the programmer.
10. Which is the costliest package developed by a person with under 3 experiences?
11. Write a query to find all programmers who have done PGDCA but not used Basic to
develop their packages?
12. Create a union of 2 queries that shows the Pname, Title, DOB, and DOJ of all
programmers.
13. Form union of 3 queries. First: select the pname from PROGRAMMER; Second: the title
of the packages developed by the programmers; Third: the splace where the programmers
studied.

5. OBJECTS OF ORACLE

VIEWS
14. Create a view of programmers who developed the application using C. And also rename
the columns.
15. Create a view that shows all of the customers who have highest ratings.
16. Create a view that gives the total sales value of the software Institute wise.
17. Create a view that gives the names of the programmers who have not developed any
package.
18. Create a view that shows all the female programmers studied in BDPS, who sold more
than 10 copies of their packages.

SYNONYMS
19. Create a synonym for the ‘employee’ table, which should be accessed by all the users in
the database.
20. Create a synonym for the ‘customer’ table.
21. Drop both the synonyms.
22. Create an index on the unindexed attribute splace of the studies relation.
23. Create an index on the unindexed attribute Pname of the software relation.

SEQUENCES
24. Create a sequence ‘seq’ with the following specifications minimum value 1, maximum
value 2,, increment by 2, start with 1, with cycle and cache also drop the created index.
17
Sona College of Technology DBMS Lab Manual

DCL

1. Insert into the table route_detail with the following rows

Route_id Place_id Nonstop
105 01 N
102 02 S
106 01 S
108 05 N

2. Grant the alter, update, insert privileges to your friend on the table route_detail
3. Revoke the above privileges.
4. Create a save point s1.
5. Insert into the above table the following row: 106, 02, N
6. Rollback to save point s1.
7. Commit the changes
8. Change the nonstop to “N” of the route_detail where the route_id is 102.
9. Create a save point s2.
10. Delete the row whose route_id is 102.
11. Rollback to save point s2.
12. Commit the changes.

18
Sona College of Technology DBMS Lab Manual

5. HIGH LEVEL PROGRAMMING LANGUAGE EXTENSIONS

Objective

To learn about the Procedural Language/SQL and to handle the exceptions.

Description

PL/SQL

PL/SQL stands for Procedural Language/SQL. PL/SQL is an extension of SQL, PL/SQL

block can contain any number of SQL statements integrated with flow of control statements.
Thus PL/SQL combines the data manipulating power of SQL with data processing power of
procedural languages.

Syntax

Declare

Declarations

Begin

Executable statements

Exception

Handlers

End;

Exceptions

Exceptions are designed for run time error handling, rather than compile time error handling.
When an error occurs, an exception is raised.
Types are
 User-defined exceptions
 Pre-defined exceptions
19
Sona College of Technology DBMS Lab Manual

User defined exceptions is an error that is defined by the program. The error that it
signifies need not necessarily be an Oracle error. Pre-defined exceptions correspond to common
SQL errors.

PL/SQL SIMPLE PROGRAMS WITH EXCEPTION HANDLING

1. Write a PL/SQL program to print the factorial of a given number.

2. Write a PL/SQL program to handle the zero divide error.

3. Write a PL/SQL Block for handling the exceptions on ‘no data found’ as and
appropriately display the message.(Assume your own set of data).

4. Write a PL/SQL program to calculate the Bonus for the employees whose salary is less
than 10000. (EMPLOYEE table.)

5. Insert the values using PL/SQL block into a table.

6. Write a PL/SQL block to count the number of rows whose salary is greater than 5000.
(PROGRAMMER table).

7. Write a PL/SQL block to calculate the average marks of the student in marks1.

8. Write a PL/SQL block to compare 2 strings one declared as char and other declared as
varchar2.

20
Sona College of Technology DBMS Lab Manual

5. PROCEDURES AND FUNCTIONS

Objective

To write stored procedures in order to avoid repetition and to reduce the cost of software
maintenance by calling the same procedure from different sources.

Description

A stored procedure or function is a PL/SQL program stored in an Oracle Database and

invoked by a user, either directly or indirectly. A package is a collection of related PL/SQL
procedures and functions that is stored on an Oracle database
.
Exercise:

1. Write a function, to find the programmer who has sold the highest number of copies from
the Programmer table.

2. Write a procedure to find all the employees with grade A.

3. Write a procedure to get a pattern of string as input and find whether the pattern exists in
the main string.

4. Write function which will accept x,y and calculate xy

5. Write a function to find the topper from the STUDENT table.

6. Write a procedure to display the names of the students whose marks in all the 3 subjects
are greater than 70 and also print the number of rows. (STUDENT)

21
Sona College of Technology DBMS Lab Manual

6. CURSORS & TRIGGERS

Objective

To retrieve multiple rows from the database using PL/SQL block. To enforce a
sophisticated security policy and to perform complex Validation of column values.

Description

A cursor is a mechanism for programmatically retrieving an arbitrary number of rows

with a select statement.

A database trigger is a group of PL/SQL statements that are executed when the
contents of the table are modified in some way either through an insert, update or delete
statement.

Exercise:

1. Display the name and salary of the first 3 employees, whose salary is less than 4000,
using cursors.
2. Write a Database trigger BEFORE DELETE FOR EACH ROW not allowing deletions
and give the appropriate message on the class table.
3. Write the PL/SQL block that will select all rows of the STUDENT table and the block
should return the number of rows.
4. Write a PL/SQL block using cursors to read the percentage from the CLASS table and
add the appropriate data to the remark field. The conditions are:
i. percentage >= 75, remark = ‘distinction’
ii. percentage >= 60 and <75, remark = ‘first class’
iii. percentage >= 45 and <60, remark = ‘second class’
iv. percentage < 45, remark = ‘fail’
5. Using Cursors update the EMPLOYEE table based on their grades.
i. if grade is ‘A’ then bonus = 15% of the salary
ii. if grade is ‘B’ then bonus = 20% of the salary
iii. if grade is ‘C’ then bonus = 25% of the salary
6. Write a Database Trigger BEFORE DELETE FOR EACH ROW not allowing deletions
on Wednesdays and give the appropriate message STUDENT table.
7. Write a PL/SQL block that will select those values from the STUDENT table where
dept= ‘IT’ and calculate the total marks and print the same. If a value is not found then a
message should be displayed.
8. Write a PL/SQL block using cursors to calculate the incentive (for a week) for the
workers based on their skill types.
(Electrical, Plumbing – 50% of the average hourly rate, others 25%)

22
Sona College of Technology DBMS Lab Manual

7. INDEXING LARGE FILES

1. Populate large data into the employee table

2. Run the following queries

1. select * from employee where eno = 99999;

2. select * from employee where dept = ‘it’ and salary >50000;

3. Find the query plan for each of the above queries by prefixing the query with the explain
keyword
4. Create an index on the unindexed attribute salary of the employee relation
5. Create an index on dept attribute of the employee relation.
6. Run the following queries

select * from employee where eno = 99999;

select * from employee where dept = ‘it’ and salary >50000;

Find the query plan for each of the above queries by prefixing the query with the explain
keyword. Analyze the time taken and the execution plan

23
Sona College of Technology DBMS Lab Manual

8. FRONT END TOOLS, FORMS, MENU DESIGN AND REPORTS

FORMS

This tool is used for generating and executing Forms based applications. A Form
basically comprises blocks and fields, multiple tables can be accessed over a single Form, based
on the application with the help of transaction commands. Oracle Forms designer is the design
component of Oracle Forms. We can build, generate and run an Oracle Forms application from
the designer.

REPORTS

It is an application development tool of Oracle for developing , executing , displaying and

printing reports. We can create a wide variety of reports. Oracle reports are powerful, yet easy to
use.

PAYROLL PROCESSING SYSTEM

Objective:

To design and implement the payroll processing system by using VB and forms.

Description:

Create a database for minimum of 15-20 employees. The system should contain the following
information

1. Login information

2. Identify the employees based on experience, designation.

3. Calculate the salary based basic, DA, HRA and other allowances

4. Calculate the P.F for eligible employee (permanent employees only) etc.

24
Sona College of Technology DBMS Lab Manual
BANKING SYSTEM
Objective:

To design and implement the banking system by using VB and forms.

Description:

Create a database for minimum of 15-20 users and to perform the ATM operations. The system
should contain the following information

1. Login information

2. Banking Service:

i. Debit
ii. Credit
iii. Balance inquiry
iv. Amount transaction
v. Mini statement…...etc

3. Other services

i. Cheque book request

ii. Password change request….etc

LIBRARY INFORMATION SYSTEM

Objective:

To design and implement the Library Management System by using VB and forms.

Description:

Create a database for 15-20 customers to perform the book search and other related
services. To design and implement the Library Information System by using your own templates.

25
Sona College of Technology DBMS Lab Manual

What Is Oracle10g Forms/Reports?

Oracle10g Forms/Reports is an Oracle tool that helps you to create web-enabled forms and
reports based on the tables that you have created using Oracle10g Designer or SQL Plus
commands. Starting from Oracle10g, Oracle10g Forms/Reports has become one of Oracle10g
Developer Suite components. You can use Oracle10g Forms/Reports to:

 Design and customize your forms and reports.

 Add various functionality, like radio buttons, combo boxes, and list of values to make
your forms and reports more user friendly.
 Write triggers on your objects to add functionality to them and capture errors.
 Web enable your Forms or Reports on a development server.

Tools Provided By Oracle10g Forms/Reports

Oracle Oracle10g Forms/Reports provides four tools:

 Object Navigator: In this tool you can view all your objects, add new objects and
name/rename your objects.
 Layout Editor: This tool helps you design your forms and reports and add various
objects to them like push buttons and list boxes.
 PL/SQL Editor: This is the tool that is used to write all the codes for the triggers,
procedures or functions.
 Menu Editor: This tool will help you create a customized menu that can be attached
to your form or report.

Logging On to Oracle10g Forms/Reports

1. To log on to Oracle10g Forms/Reports, go to Start Programs Oracle10g

Developer Suite Forms Developer, click Start OC4J Instance (Very important: You
must keep OC4J running while using Oracle10g Forms/Reports !!!), after OC4J
initialized, then click Forms Builder.
2. Immediately, you will see the window for Forms Builder:

26
Sona College of Technology DBMS Lab Manual

3. Double click Data Blocks, Select 'Use the Datablock Wizard' and click OK--This is the
easiest method to design a new form.
4. You will now see the Welcome to the Datablock Wizard Window. Click Next to proceed.

5. You will now see the window for the Datablock Wizard. Select Table or View as in the
figure and click Next.

27
Sona College of Technology DBMS Lab Manual

6. You will now see the window that prompts you to select a table or a view--your form will
be created based on this selection. Since no table or view is being shown, click on browse
to look at the list of tables and views in your database.

7. Once you click browse, the connect window will appear. Type in your username,
password and database to connect to the database.

8. You will now see the tables window. Select current users and tables and click OK.
28
Sona College of Technology DBMS Lab Manual

9. You will now see the list of tables created in your database. Select Students and click
OK.

10. You will now see your selected table and its available columns on your screen. Click on
the single right arrow to select the first column to be shown in your form; in this case the
STUDID column. You will now see this column under the database items selected sub-
window.

29
Sona College of Technology DBMS Lab Manual

11. To move the rest of the columns, simply click on the double right arrow and this will
select all your columns in to the database items.

12. You will now see the Congratulations window. Make sure that "Create the data block,
then call the Layout Wizard" is selected and click on Finish.

30
Sona College of Technology DBMS Lab Manual

13. You will now see the Layout Wizard Welcome window, click next. You will see the
following screen, click next.

14. select the items that you would like to show in the form. Make sure that the data block
selected is Students and then click the double right arrow to move all the columns of the
Student block from the available items to the displayed items. Click on Next to continue.

31
Sona College of Technology DBMS Lab Manual

15. The window with the prompt for the height and width of the items will appear. Click
Next to accept the default values.

16. The Layout Wizard will now prompt you to select the layout or view style of your block.
Select Form and click Next.

32
Sona College of Technology DBMS Lab Manual

17. The Layout Wizard will now prompt you to select a title for the form that you are
creating. Type in Student Records. Click Next to continue.

18. Congratulations! You have now successfully created your first form. Click Finish to view
your form.

33
Sona College of Technology DBMS Lab Manual

19. You will now see the canvas view of the form that you have created. You can now add
various objects like push buttons, combo boxes and radio buttons to your form to make it
more graphical and user friendly. We will do this in the next lesson.

20. You can now format the form manually. Click on the frame to select it. Then drag the
frame to make it bigger.

34
Sona College of Technology DBMS Lab Manual

21. You can now space out the data fields to make your form more visually appealing. You
can do this by simply selecting the data field and dragging it to your desired area.

22. After you have formatted all the data fields, your form should look like the following:

35
Sona College of Technology DBMS Lab Manual

Appendix
CLASS

Sno Sname Degree Percentage

101 Anil B.Tech 68.15
102 Banu B.E 78.28
103 Chandni M.Tech 71.34
104 Deepika B.Sc 65
105 Fatima M.E. 74
106 Sruthi B.Sc 64

EMPLOYEE

Eno Ename Dept Salary Grade

E101 Akash EDP 9900 B
E102 Bharat Accts 6000 B
E103 Moni Prod 7000 C
E104 Naren Accts 12000 A
E105 Uthra EDP 15000 A
E106 Senthil IT 24000 A
E107 Raman CSE 24000 A

DEPARTMENT
Dept Project_name Budget

EDP DOCS 100000

Prod WHEEL 75000
Accts BALDE 200000
IT Data Warehouse 125000
CSE Data Mining 250000

WORKER

WorkerId Workername Hrlyrate Sup_Id Skill_type

1235 Franklin 12.5 1311 Electrical

1412 Nayan 13.75 1520 Plumbing
2920 Girish 10 2920 Roofing
1520 Rakesh 11.75 1520 Plumbing
1311 Chandar 15.5 1311 Electrical

36
Sona College of Technology DBMS Lab Manual
STUDENT

Sno Sname City Dept M1 M2 M3

98CSE01 Sangeetha Salem CSE 45 42 35

98EEE07 Santhosh Chennai EEE 74 63 85
96ECE03 Tanuja Coimbatore ECE 84 82 68
97IT04 Utsav Bangalore IT 98 96 94
97ME05 Vivek Salem MECH 51 40 28
97CVE06 Suresh Trichy CIVIL 81 82 76
97CSE07 Suma Madurai CSE 95 90 98
98CSE45 Somesh Chennai CSE 89 67 78

SOFTWARE
Pname Title Dev_In Swcost Dcost No_Sold
Anand Parachutes Basic 399.95 6000 43
Anand Videopack Pascal 7500 16000 9
Juliana Invent.ctrl COBOL 3000 3500 0

Mary Finance Oracle 18000 8500 4

Mary Codegen C 4500 20000 23
Rekha PCutil C 725 5000 51
Rekha TSRhelp Assembly 2500 6000 6
Reshma Hotelmgt Pascal 1100 75000 2
Reshma Quiz Basic 3200 2100 15
Anand Syss/w C 400 24000 10
Note:
Swcost - Software Cost
Selling price - Dcost + Swcost

PROGRAMMER
Pname DOB DOJ Sex Prof1 Prof2 Sal
Anand 21-04-66 21-04-92 M Pascal Basic 4200
Juliana 31-01-68 21-04-90 F COBOL Dbase 3000
Mary 24-06-70 01-02-91 F C++ Oracle 5500
Nelson 11-09-65 11-10-89 M COBOL Dbase 6000
Rekha 19-04-70 20-04-93 F C Assembly 3600
Reshma 02-12-69 02-01-92 F Pascal Basic 3700
Vinaya 14-12-65 02-05-92 F Foxpro C 3500

37
Sona College of Technology DBMS Lab Manual
STUDIES
Pname Splace Course Cost

Anand SSI PGDCA 4500

Juliana BITS MCA 22000
Mary SSI PGDCA 4500
Rekha BDPS DCS 6000
Reshma SSI DAP 5000
Vinaya BDPS DCA 46000

VIVA QUESTIONS

1. What is data?
2. What is datum?
3. What is information?
4. What is processed data?
5. What is database And its type ?
6. What is database Management Systems And their types ?
7. What is RDBMS?
8. What is OORDBMS?
9. What is the difference between DBMS and RDBMS?
10. What is the difference between RDBMS and OORDBMS?
11. What is Single User Environment?
12. What is Multi User Environment?
13. What is an application of architecture?
14. What is two- tire architecture?
15. What is three-tire architecture?
16. What is Multi–tire architecture?
17. Mention the front tools of Oracle?
18. What is the Oracle’s Database Language?
19. Who developed SQL?
20. Give the classification of database models?
21. What is the difference between Object based logical models and Record based logical
models?
22. What do you mean by an entity, entity type, and entity instances?
23. What do you mean by an attribute, attribute type, attribute instances?
24. Difference between SQL * Plus and PL/SQL?
25. What is DDL?
26. What is DML?
27. What is DCL?
28. What is the purpose of using privilege commands? What are they? And give syntax for
them.
29. What do you meant by integrity constraints?
30. How can you overcome Redundancy?
31. What is Demoralization?
32. What is Primary key?

38
Sona College of Technology DBMS Lab Manual
33. What is foreign key?
34. What is unique key?
35. What is the difference between primary key and unique key?
36. Whether one null value is equal to another null value?
37. What is Referenced key?
38. A composite key can contain a maximum of how many columns?
39. What is the difference between varchar2 and char?
40. What is the difference between truncate and delete command?
41. What are constraints and their types?
42. Is it possible to alter a constraint after creating it?
43. What is query?
44. How can you combine two queries?
45. How can you combine tables?
46. Is it possible for a sub query to return a value?
47. What are correlated sub query and multiple sub queries?
48. What is the difference between Rollback and Commit?
49. Where will be the most recently used SQL * PLUS command stored?
50. What is transaction?
51. What is Concurrency?
52. What is Deadlock?
53. What is the use of Log File?
54. What is Cursor and give its types?
55. What is Triggers?
56. What are the parts of Triggers?
57. What is sub program?
58. What are the two types of sub programs?
59. What is package?
60. What is Forms and Mention the components of Forms?
61. What is Report?
62. What is Embedded SQL and its use?

39