Dbms Lab Manual 2015-16
Dbms Lab Manual 2015-16
Dbms Lab Manual 2015-16
5th CSE
CONTENTS
1.
2.
3.
4.
5.
Syllabus
Introduction
Structured Query Language
Lab set Exercises
Viva- voce questions
5th CSE
I.A. Marks: 25
Exam Hours: 03
Exam Marks: 50
5th CSE
5th CSE
i. Create the above tables by properly specifying the primary keys and the foreign keys.
ii. Enter at least five tuples for each relation.
iii. Give the details of the authors who have 2 or more books in the catalog and the price of the
books is greater than the average price of the books in the catalog and the year of
Publication is after 2000.
iv. Find the author of the book which has maximum sales.
v. Demonstrate how you increase the price of books published by a specific publisher by 10%.
vi. Generate suitable reports.
vii. Create suitable front end for querying and displaying the results.
5. Consider the following database for a banking enterprise
BRANCH(branch-name:string, branch-city:string, assets:real)
ACCOUNT(accno:int, branch-name:string, balance:real)
DEPOSITOR(customer-name:string, accno:int)
CUSTOMER(customer-name:string, customer-street:string, customer-city:string)
LOAN(loan-number:int, branch-name:string, amount:real)
BORROWER(customer-name:string, loan-number:int)
i. Create the above tables by properly specifying the primary keys and the foreign keys
ii. Enter at least five tuples for each relation
iii. Find all the customers who have at least two accounts at the Main branch.
iv. Find all the customers who have an account at all the branches located in a specific city.
v. Demonstrate how you delete all account tuples at every branch located in a specific city.
vi. Generate suitable reports.
vii. Create suitable front end for querying and displaying the results.
Instructions:
1. The exercises are to be solved in an RDBMS environment like Oracle or DB2.
2. Suitable tuples have to be entered so that queries are executed correctly.
3. Front end may be created using either VB or VAJ or any other similar tool.
4. The student need not create the front end in the examination. The results of the queries may be
displayed directly.
5. Relevant queries other than the ones listed along with the exercises may also be asked in the
examination.
6. Questions must be asked based on lots.
5th CSE
Database: - A group of tables with related data in them are called database.
Database Management System: - DBMS consists of a collection of interrelated data and a set of
programs to manage these data.
Data Model:- Structure of database is defined by data model. Different data model are as follows:
E R model
Object Oriented model
Relational model
Network model
Hierarchical model
Relational model:Relational model uses a collection of tables to represent both data and relationship among those
tables. Most database management systems are based on the relational model. RDBMS follows
codds rules. There are 12 rules specified by E.F. Codd that must be satisfied by adatabase package
for being an RDBMS. The DBMS packages that fall in this category are as follows:
Oracle ( follows 7 rules )
DB2 ( follows 9 rules )
Ingress ( follows 10 rules )
Sybase ( follows 9 rules )
Unity
Etc
SQL BASICS
Structured Query Language (SQL), which is an ANSI standard language for interacting with
relational databases, is the main tool for extracting the information. A database is a representation
of a real-world thing called an Entity. Examples of entities are vehicles, employees, customers, fish,
buildings, and even things such as baseball teams. The database stores facts about the entity in an
organized framework, model, or schema. These facts are called attributes. An Instance is one
occurrence of an entity. Each entity must have an identifier, which is one or more attributes that
make each entity instance unique from any other instance. The identifier should contain a value
that does not change. Examples of identifiers are student IDs, payroll numbers, or social security
numbers. If the entity does not have an attribute that can be used as an identifier, an artificial
identifier can be created. The identifier on an entity is often called a primary key. A foreign key is a
set of attributes of the considered table that exists as a primary key attributes in another table.
Database records are matched (joined) through the use of primary and foreign keys.
Normalization is a process consisting of series of steps, which is used to group the database
attributes. The purpose of this design is to ensure that the tables within the database are space
efficient and performance efficient.
Zero Normal Formeach of the relations (tables) has a unique identifier (primary key).
First Normal FormSeparate the repeating groups of attributes or multivalued attributes into a
relation of their own. Be sure to form composite keys.
Second Normal FormEstablish full functional dependency by separating out attributes that are
not fully dependent on the full primary keys.
Third Normal FormRemove transitive dependencies by separating attributes that are dependent
on a non key attribute.
HOW SQL WORKS
5th CSE
The purpose of SQL is to interface to a relational database such as Oracle, and all SQL statements
are instructions to the databases SQL provides commands for a variety of tasks including:
Querying data
Inserting, updating, and deleting rows in a table
Creating, replacing, altering, and dropping objects
Controlling access to the database and its objects
Guaranteeing database consistency and integrity
DATATYPES
Each literal or column value manipulated by Oracle has a datatype. A values datatype associates a
fixed set of properties with the value. These properties cause Oracle to treat values of one
datatypes differently values of another.
Character Datatypes:- Character datatypes are used to manipulate words and freeform text. These
datatypes are used to store character. These datatypes are used for character data:
CHAR:- The CHAR datatype specifies a fixed length character is 1 character and
maximum allowed is 2000 character.
NCHAR:- The NCHAR datatypes specifies a fixed-length national character set
character string. The maximum column size allowed is 2000 bytes.
NVARCHAR2:- The NVARCHAR2 datatype specifies variable-length national
character string. The maximum column size allowed is 4000 bytes.
VARCHAR2:- The VARCHAR2 datatype specifies a variable length character string.
The maximum length of VARCHAR2 data is 4000 bytes.
NUMBER DATATYPES:- The NUMBER datatype is used to store zero, positive and negative fixed
and floating point numbers with magnitudes
Floating Point Numbers:- A floating point value either can have a decimal point
anywhere from the first to the last digit or can omit the decimal point altogether.
LONG Datatype:- LONG columns store variable length variable length character
strings containing up to 2 gigabytes, or 231 -1 bytes. LONG data type is subject to
some restrictions which are:
A table cannot contain more than one LONG column. LONG columns cannot appear in integrity
constraints. LONG columns cannot be indexed. Also, LONG columns cannot appear in certain
parts of SQL statements:
WHERE, GROUP BY, or CONNECT BY clause or with the DISTINCT operator in
SELECT statements
UNIQUE clause of a SELECT statement
Select list of queries containing GROUP BY clauses
Select list of subqueries or queries combined by set operators
DATE Data types:- The DATE data types is used to store date and time information.
Operators:- All the normal Arithmetic, Relational, Logical operators are used in SQL.
SQL Commands:- in order to define schemas, store data, retrieve data and to make amendments in
schema and data stored in the database different types of commands are used which are:
Data Definition Language Commands.
Data Manipulation Language Commands
Transaction Control Commands
Session Control Commands
System Control Commands
Data Definition Language (DDL) commands allow you to perform these tasks:
Dept of CSE, AIT
5th CSE
5th CSE
GROUP BY type;
This query returns the sum of year-to-date sales for each type of book:
SELECT type, SUM( ytd_sales )
FROM titles
GROUP BY type;
COUNT
The COUNT function has three variations. COUNT(*) counts all the rows in the target table
whether they include nulls or not. COUNT(expression) computes the number of rows with nonNULL values in a specific column or expression. COUNT(DISTINCT expression) computes the
number of distinct non-NULL values in a column or expression.
Examples
This query counts all rows in a table:
SELECT COUNT(*) FROM publishers;
The following query finds the number of different countries where publishers are located:
SELECT COUNT(DISTINCT country) "Count of Countries" FROM publishers
MIN and MAX
MIN(expression) and MAX(expression) find the minimum and maximum value (string, datetime,
or numeric) in a set of rows. DISTINCT or ALL may be used with these functions, but they do not
affect the result. MIN and MAX are supported by Microsoft SQL Server, MySQL, Oracle, and
PostgreSQL. MySQL also supports the functions LEAST( ) and GREATEST( ), providing the same
capabilities.
Examples
The following query finds the best and worst sales for any title on record:
SELECT 'MIN' = MIN(ytd_sales), 'MAX' = MAX(ytd_sales)
FROM titles;
Aggregate functions are used often in the HAVING clause of queries with GROUP BY. The
following query selects all categories (types) of books that have an average price for all books in
the category higher than $15.00:
SELECT type 'Category', AVG( price ) 'Average Price'
FROM titles
GROUP BY type
HAVING AVG(price) > 15
CONCATENATE
SQL99 defines a concatenation operator ( || ), which joins two distinct strings into one string value.
The CONCATENATE function appends two or more strings together, producing a single output
string. PostgreSQL and Oracle support the double-pipe concatenation operator. Microsoft SQL
Server uses the plus sign (+) concatenation operator.
MySQL supports a similar function, CONCAT( ). Refer to the "Concatenation Operators" section
Chapter 3, SQL Statements Command Reference, for more information on concatenation within
Oracle, PostgreSQL, and Microsoft SQL Server.
SQL99 Syntax
CONCATENATE('string1' || 'string2')
Transaction Control Commands manages change made by Data Manipulation Language
commands. Following are the commands:
Commit
Rollback
Save point
Set Transaction
SQL * Plus
5th CSE
SQL *Plus enables you to manipulate SQL commands and PL/SQL blocks, and to perform many
additional tasks as well. Through SQL *Plus, you can
Enter, edit, store, retrieve, and SQL commands and PL/SQL blocks
Format, perform calculations on, store, and print query results in the form of reports
List column definitions for any table
Access and copy data between SQL databases
Send messages to and accept responses from an end user
Oracle Overview
Oracle is one the most popular Relational Database Management System (RDBMS). Some other
famous RDBMS includes Microsoft SQL Server, Sybase, MySQL, PostgreSQL, etc. Essentially, all
the aforementioned RDBMS employs Structural Query Language (SQL) as their query interface.
Users usually issue their queries by SQL through a "client". Different RDBMS offer different
forms of clients. For example, MS SQL Server offers a GUI interface for user to type in their SQL
language, and their queries would be executed after pressing the "Execute" button on the client.
Oracle provides both GUI client and command-line client. In this lesson, we will study the
command-line client, SQL*Plus. In addition, Oracle extends the standard SQL (e.g. select * from
table) with its application-specific commands (e.g. checking how many table you have been created
in your Oracle account) into a Oracle specific language called PL/SQL. In this tutorial, you will
interact with Oracle database, thru SQL*Plus, by issuing a number of PL/SQL queries.
Logging In to Oracle
Microsoft Windows
Under Windows environment, the Oracle client is called SQL*Plus.
Steps:
1. Click Start, and then click Run.
2. Type sqlplus, and fill in the username, password, and database name
3. After you log in to SQL*Plus, you see the following message:
Connected to:
Oracle9i Enterprise Edition Release 9.1.7.0.0 - Production
JServer Release 9.1.7.0.0 - Production
and you should receive the prompt:
Creating user:
Create user <yourName> identified by <Password>;
where <yourName> is again your login name, and <Password> is the password you would like to
use in the future. This command, like all other SQL commands, should be terminated with a
semicolon.
Changing Your Password
In response to the SQL> prompt, type
alter user <yourName> identified by <newPassword>;
where <yourName> is again your login name, and <newPassword> is the password you would like
to use in the future. This command, like all other SQL commands, should be terminated with a
semicolon.
Creating Tables
In SQL*Plus we can execute any SQL command. One simple type of command creates a table
(relation). The form is
CREATE TABLE <tableName> (
<list of attributes and their types>
);
5th CSE
You may enter text on one line or on several lines. If your command runs over several lines, you
will be prompted with line numbers until you type the semicolon that ends any command.
(Warning: An empty line terminates the command but does not execute it; see editing commands
in the buffer.) An example table-creation command is:
CREATE TABLE test (
i int, s char(10)
);
Note that SQL is case insensitive, so CREATE TABLE TEST and create table test are the same.
This command creates a table named test with two attributes. The first attribute, named i, is an
integer, and the second, named s, is a character string of length (up to) 10.
Exercise 1: Create a relation Student that suitable for the following instance:
SID
NAME
JOB
Salary
Ben Kao
Associate Professor
7000
01-Sep-1995
Eric Lo
Teaching Assistant
1000
01-Oct-2003
Hammer
Lecturer
7000
11-Feb-2000
6000
12-Dec-1999
STREAM START_AT
5 Steven Chu
Project Assistant
7000
I
13-Dec-2002
Inserting Tuples
Having created a table, we can insert tuples into it. The simplest way to insert is with the INSERT
command:
INSERT INTO <tableName> VALUES( <list of values for attributes, in order> );
For instance, we can insert the tuple (10, 'hi world') into relation test by
INSERT INTO test VALUES(10, 'hi world');
Exercise 2: Insert the records as stated into Exercise 1 into the student table. Trick: Try to insert a record
into test with the following SQL:
INSERT INTO test VALUES(11, 'ha 'world');
Updating Tuples
Tuples can be updated by the UPDATE command:
UPDATE <tableName> SET <Attribute>=<Expression/Value> WHERE <Predicate>;
For example, we can update the tuple (10, 'hi world') in relation test by
UPDATE test SET s='bye world' WHERE i=10;
Exercise 3: Update the record of 'Eric Lo' in relation Student such that his salary change to 1234
Deleting Tuples
Having insert / update a tuple, we can delete it as well. The simplest way to delete is with the
DELETE command:
DELETE FROM <tableName> [WHERE <condition>];
<condition> is an optional statement and is used to identify a single record when necessary.
For example, you can delete the record with i=10 in table test with the the following SQL:
DELETE FROM test WHERE i=10;
Exercise 4: Delete the record of 'Eric Lo' in relation Student.
Trick: Does that record really deleted successfully? Let's check it out by using SELECT command
(we will cover it in next section).
Retrieving Tuples
We can see the tuples in a relation with the command:
SELECT <attributes-separated-by-comma>/<wildcard> FROM <tableName>;
For instance, after the above CREATE, INSERT, DELETE and UPDATE statements, the
command
Dept of CSE, AIT
10
5th CSE
Description
VARCHAR2
(size)
CHAR
[(size)]
Fixed-length character data of length size bytes (default and minimum size
is 1; maximum size is 2000)
NUMBER
[(p,s)]
Number having precision p and scale s (The precision is the total number of
decimal digits, and the scale is the number of digits to the right of the
decimal point; the precision can range from 1 to 38 and the scale can range
from -84 to 127)
11
5th CSE
Date and time values to the nearest second between January 1, 4712 B.C.,
and December 31, 9999 A.D.
Creating Tables with Keys
To create a table that declares attribute a to be a primary key:
CREATE TABLE <tableName> (..., a <type> PRIMARY KEY, b, ...);
To create a table that declares the set of attributes (a,b,c) to be a primary key:
CREATE TABLE <tableName> (<attrs and their types>, PRIMARY KEY (a,b,c));
DATE
12
5th CSE
Exercise-1:
1. Consider the following relations containing student class information:
Student (snum: integer, sname: string, major: string, level: string, age: integer)
Class (cname: string, meets at: time, room: string, fid: integer)
Enrolled (snum: integer, cname: string)
Faculty (fid: integer, fname: string, deptid: integer)
Step-1: ER Diagram Design
SNAME
SNUM
MEETS AT
SNUM
MAJOR
STUDENT
ROOM
NAME
CLASS
ENROLL
Dept
LEVEL
AGE
CNAME
TEACHES
FACULTY
FID
FNAM
EE
DEPTI
D
13
5th CSE
STUDENT TABLE:
===================
create table student
(snum number primary key,
sname varchar2(20),
major varchar2(10),
level1 varchar2(10),
age number);
FACULTY TABLE:
==================
create table faculty
(fid number primary key,
fname varchar2(10),
deptid number);
CLASS TABLE:
================
create table class
(cname varchar2(10) primary key,
meetsat varchar2(10),
room varchar2(10),
fid number,
foreign key(fid) references faculty(fid));
ENROLLED TABLE:
===================
create table enrolled
(snum number,
cname varchar2(10),
foreign key(snum) references student(snum),
foreign key(cname) references class(cname));
===============================================================
14
5th CSE
15
5th CSE
2. Find the names of all classes that either meet in room R128 or have five or more students enrolled.
SQL>SELECT C.cname
FROM Class C
WHERE C.room = 'R128'
OR C.cname IN (SELECT E.cname
FROM Enrolled E
GROUP BY E.cname HAVING COUNT (*) >= 5)
3. Find the names of all students who are enrolled in two classes that meet at the same time.
SQL> SELECT DISTINCT S.*
FROM Student S
WHERE S.snum IN (SELECT E1.snum
FROM Enrolled E1, Enrolled E2, Class C1, Class C2
WHERE E1.snum = E2.snum AND E1.cname <> E2.cname
Dept of CSE, AIT
16
5th CSE
4. Find the names of faculty members who teach in every room in which some class
is taught.
SQL> select * from faculty
where fid
in(select fid from
(select count(room) cnt,fid from class group by fid)
where cnt=(select count(distinct(room)) from class));
5. Find the names of faculty members for whom the combined enrollment of the courses that they teach
is less than five.
SQL> select fid,fname
From faculty
where fid in(
select fid from
(select count(1) cnt,fid
From (SELECT E.snum,c.fid
FROM Class C, Enrolled E
WHERE C.cname = E.cname)
group by fid) where cnt<5)
17
5th CSE
Exercise-2:
Consider the following relations containing airline flight information:
Flights (flno: integer, from: string, to: string, distance: integer, departs: time, arrives: time, price: real)
Aircraft (aid: integer, aname: string, cruisingrange: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as well; every pilot is
certified for some aircraft (otherwise, he or she would not qualify as a pilot), and only pilots are certified to
fly.
Step-1: ER Diagram Design:
From
To
Ename
Fno
Flno
FLIGHTS
Departs
Arriv
es
Salary
Eid
Distanc
e
EMPLOYEE
FLYS
Price
Eid
CERTIFIES
AIRCRAFT
Cruisingrange
Aid
Aname
18
5th CSE
19
5th CSE
where salary>20000);
ANAME
---------Boeing
B)For each pilot who is certified for more than three
aircrafts, find the eidand the maximum cruisingrangeof
the aircraft for which she or he is certified.
SQL>select * from certified;
EID
AID
------ ---------88
205
82
202
88
203
81
205
81
203
87
201
SQL> select * from aircraft;
AIDD ANAME
CRANGE FNO
------ ---------------------------201
boeing
1001
112
202
boeing2
1010
111
203
boeing3
1200
114
205
boeing4
1400
114
SQL> select c.eid, max(a.crange) from aircraft a,
employee e, certified c where a.aidd=c.aid and
c.eid=e.eid group by c.eid having count(c.eid)>3;
EID
MAX(A.CRANGE)
------ ------------81
1400
88
1400
C)Find the names of pilots whose salary is less than the
price of the cheapest route from Bengaluru to Frankfurt.
Select unique e.name from employee e where salary<
(select min(price) from flight where ffrom=bangalore and
fto=frankfurt);
Dept of CSE, AIT
20
5th CSE
21
5th CSE
AIDD
ANAME
CRANGE FNO
---------- ---------------------------201
boeing
1001
112
202
boeing2
1010
111
203
boeing3
1200
114
205
boeing4
1400
114
SQL> select * from employee;
EID
ENAME SALARY
-----------------------81
san
20000
82
jack
45000
88
lapoid
45000
87
killer
50000
SQL> select * from certified;
EID
AID
-------- ---------82
202
88
203
81
205
81
203
87
201
SQL> insert into certified values(88,205);
1 row created.
SQL> select * from certified;
EID
AID
-------- ---------82
202
88
203
81
205
81
203
87
201
88
205
SQL> select a.aname,avg(e.salary) from aircraft a, employee
e, certified c where a.aidd=c.aid and c.eid=e.eid and
crange>1200
group by a.aname;
22
5th CSE
ANAME
AVG(E.SALARY)
---------- ------------boeing4
32500
Find the names of pilots certified for some Boeing aircraft.
select ename from employee e, certified c, aircraft a where
e.eid=c.eid and c.aid=a.aidd and aname='boeing3';
Output:
ENAME
---------lapoid
san
E. Find the aids of all aircraft that can be used on routes from
Bengaluru to New Delhi.
Select a.aid from aircraft a, flight f where f.from=bangalore
and f.to=newdehli;
FNO FFROM
FTO
DISTANCE
PRICE
DEPARTS
ARRIVES
----- ---------- ------------------------ ---------- --------------------------111
bang
frankfurt
150
5000
9 a.m
12 p.m
112
bang
delhi
150
3000
9.15 a
m
12.15 p.m
113
delhi
san fra
250
25000
9.15
am
12.15 pm
114
chennaisingapore
500
20000
12noon
10.30 pm
115
chennaimalashriya
600
20000
1noon
11.30pm
AIDD ANAME
-------------201 boeing
202 boeing2
203 boeing3
205 boeing4
CRANGE
---------1001
1010
1200
1400
FNO
---------112
111
114
114
output:
AIDD
FNO
------ ---------203
114
Dept of CSE, AIT
23
5th CSE
205
114
Create the above tales by properly specifying the primary keys and
the foreign keys.
(ii) Enter atleast five tuples for each relation.
(iii) Give the details of the authors who have 2 or more books in the
catalog and the price of the books is greater than the average price of
the books in the catalog and the year of publication is after 2000.
(iv) Find the author of the book which has maximum sales.
(v) Demonstrate how you increase the price of books published by a
specific publisher by 10%.
(vi) Generation of suitable reports.
Dept of CSE, AIT
24
5th CSE
(vii) Create suitable front ends for querying and displaying the results.
Name
City
Countr
y
Author_Id
AUTHOR
QUANTI
TY
AIN
N
Publisher_I
d
Name
1
PUBLISHER
Country
CategoryI
D
CATALOG
PIN
City
Yea
r
Pric
e
Title
Book_ID
Descriptio
n
1
CCategory
category
Category
REGISTE
R_NO
25
CIN
5th CSE
ORDER_DETAILS
Order_no
Quantit
y
26
5th CSE
);
CREATE TABLE CATALOG
(
BOOKID NUMBER(5),
TITLE VARCHAR2(15),
AUTHORID NUMBER(5),
PUBLISHERID NUMBER(5),
CATEGORYID NUMBER(5),
YEAR NUMBER(5),
PRICE NUMBER(10,3),
PRIMARY KEY(BOOKID),
FOREIGN KEY(AUTHORID) REFERENCES AUTHOR(AUTHORID),
FOREIGN KEY(PUBLISHERID) REFERENCES
PUBLISHER(PUBLISHERID),
FOREIGN KEY(CATEGORYID) REFERENCES
CATEGORY(CATEGORYID)
);
CREATE TABLE ORDER_DETAILS
(
ORDERNO NUMBER(5),
BOOKID NUMBER(5),
QTY NUMBER(5),
PRIMARY KEY(ORDERNO),
FOREIGN KEY(BOOKID) REFERENCES CATALOG(BOOKID)
);
2) INSERT THE RECORDS INTO THE RELATIONS
INSERT INTO AUTHOR
VALUES(&AUTHORID,'&ANAME','&ACITY','&ACOUNTRY');
INSERT INTO PUBLISHER
VALUES(&PUBLISHERID,'&PNAME','&PCITY','&PCOUNTRY');
INSERT INTO CATEGORY VALUES(&CATEGORYID,'&DESCRIPTION');
INSERT INTO CATALOG VALUES
(&BOOKID,'&TITLE',&AUTHORID,&PUBLISHERID,&CATEGORYID,&YEAR,
&PRICE);
INSERT INTO ORDER_DETAILS VALUES(&ORDERNO,&BOOKID,&QTY);
VIEW THE RELATIONS
AUTHOR
Dept of CSE, AIT
27
5th CSE
AUTHORID ANAME
ACITY
ACOUNTRY
---------- --------------- --------------- ---------------------------------------1000
MANJUNAMTH
BANGALORE
INDIA
2000
TONY
HAYHOOD
USA
3000
TONIER
NEWYORK
USA
4000
TENNENBAUM
LONDON
UK
5000
BALGURU
CHENNAI
INDIA
PUBLISHER
PUBLISHERID PNAME
PCITY
PCOUNTRY
----------- --------------- --------------- ----------------------------11
WEILY
NEWDELHI
INDIA
22
PHI
CALIFORNIA USA
33
SAPNA
BANGALORE INDIA
44
TMH
NEWYORK
USA
55
WROX
TEXAS
USA
CATEGORY
CATEGORYID DESCRIPTIONM
---------- -----------------------------------1
OS
2
LANGUAGES
3
HARDWARE
4
ALGORITHMS
5
INTERNET
CATALOG
BOOKID TITLE AUTHORID PUBLISHERID CATEGORYID YEAR PRICE
--------- --------------- ---------- ----------- ---------- --------------------- --------- -------------1
DSC
1000
33
2
2000
190
2
N/W 4000
44
4
2002
365
3
VB-6 2000
11
2
2000
300
4
SE
4000
44
5
2002
500
5
ADA 1000
33
4
2000
200
ORDER_DETAILS
ORDERNO BOOKID QTY
--------- ---------- ---------------------------100
1
100
Dept of CSE, AIT
28
200
300
400
500
1
2
3
5
5th CSE
20
20
50
8
29
5th CSE
30
5th CSE
Create the above tables by properly specifying the primary keys and
the foreign keys
Enter atleast five tuples for each relation
Find all the customers who have atleast two accounts at the Main
branch.
Find all the customers who have an account tuples at every branch
located in a specific city.
Demonstrate how you delete all account tuples at every branch
located in a specific city.
Generation of suitable reports.
Create suitable front ends for querying and displaying the results.
31
5th CSE
CITY
CUSTOMER
QUANTI
TY
BALANC
E
ACC_NO
ACCOUNT
DEPOSI
TOR
BORROWE
R
ACC_IN
1
REGISTE
R_NO
N
LOAN
LOAN_NO
BRANCH
LOAN_I
N
AMOUNT
BRANCH_NAM
E
CITY
ASSET
S
32
5th CSE
33
5th CSE
34
5th CSE
BRANCH
BRANCH_NAME BRANCH_CITY
ASSETS
--------------- --------------- ------------------------------------RAJAJINAGAR
BANGALORE
200000
JAYANAGAR
BANGALORE
100000
VIJAYANAGAR
BANGALORE
300000
KUVEMPUNAGAR MYSORE
100000
GOKULUM
MYSORE
100000
CUSTOMER
CUSTOMER_NAME CUSTOMER_ADDRES CUSTOMER_CITY
--------------- --------------- -------------------------------------------------------RAVI
VIJAYANAGAR
BANGALORE
HARISH
BULLTEMPLEROAD
BANGALORE
BHAVYA
GANDHIBAZAR
BANGALORE
SRIRAM
RAJAJINAGAR
BANGALORE
RANI
MGROAD
BANGALORE
ACCOUNT
ACCOUNT_NUMBER
BRANCH_NAME
BALANCE
--------------- -----------------------------------------------------------------101
RAJAJINAGAR
2000
102
RAJAJINAGAR
3000
103
VIJAYANAGAR
4000
104
KUVEMPUNAGAR
5000
105
GOKULUM
5000
106
JAYANAGAR
6000
107
RAJAJINAGAR
5000
DEPOSITOR
CUSTOMER_NAME ACCOUNT_NUMBER
--------------- ----------------------------------------RAVI
101
HARISH
102
RAVI
103
SRIRAM
104
RAVI
107
Dept of CSE, AIT
35
5th CSE
RAVI
106
LOAN
LOAN_NUMBER BRANCH_NAME
AMOUNT
----------- --------------- --------------------------------------201
RAJAJINAGAR
10000
202
JAYANAGAR
20000
203
VIJAYANAGAR
30000
204
KUVEMPUNAGAR
40000
205
GOKULUM
50000
BORROWER
CUSTOMER_NAME LOAN_NUMBER
--------------- -------------------------------------RAVI
201
HARISH
202
BHAVYA
203
SRIRAM
204
RANI
205
36
5th CSE
BRANCH_NAME
-----------------------RAJAJINAGAR
IN ( SELECT B.BRANCH_NAME
FROM BRANCH B
WHERE B.BRANCH_CITY='BANGALORE'
);
EXTRA QUERIES
1) SUPPOSE THAT ANNUAL INTEREST PAYMENTS ARE BEING MADE AND
ALL BRANCHES ARE TO BE INCREASED BY 3%.
2) IF INTEREST IS TO BE PAID ONLY TO ACCOUNTS WITH A BALANCE OF
Rs.1000 OR MORE.
3) FIND ALL LOAN NUMBERS FOR LOANS MADE AT RAJAJINAGAR
BRANCH WITH LOAN AMOUNT ABOVE Rs.5000.
Dept of CSE, AIT
37
5th CSE
38
5th CSE
Creation of suitable Front End using Visual Basic for querying and
Displaying the results
STEP 1:
Create the necessary FORM using label, textbox & button controls.
STEP 2:
CommandType
Source
of the ADO
Control
as
click on adcBranch d
select ADODC Properties
39
5th CSE
6.To set the connection [property click on BUILD button & You get a
Data Link Dialog box as follows
40
5th CSE
9. Click on RecordSource TAB & select 2 - adCmdTable. Wait till the tables
are listed in the Table combo Box
41
5th CSE
42
5th CSE
43
5th CSE
4. Double click on the Delete button & add the following code
Private Sub Delete_Click()
adcBranch.Recordset.Delete
adcBranch.Recordset.Save
MsgBox "Record Deleted..."
adcBranch.Recordset.MoveNext
End Sub
44
5th CSE
What is database?
What is DBMS?
What is a Database system?
What are the disadvantages in File Processing System?
Answer: Data redundancy & inconsistency.
Difficult in accessing data.
Data isolation.
Data integrity.
Concurrent access is not possible.
Security Problems.
45
5th CSE
46
5th CSE
47
5th CSE
Answer: A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the
following is true.
1. X is a Super-key of R.
2. A is a prime attribute of R.
In other words, if every non prime attribute is non-transitively dependent on primary key.
51. What is BCNF (Boyce-Codd Normal Form)?
Answer: A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that
for every FD X A, X must be a candidate key.
52. What is 4NF?
Answer: A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that
holds over R, one of following is true.
1.) X is subset or equal to (or) XY = R.
2.) X is a super key.
53. What is 5NF?
Answer: A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn}
that holds R, one the following is true
1.)Ri = R for some i.
2.) The join dependency is implied by the set of FD, over R in which the left side is key of R.
54. What is DKNF (Domain Key Normal Form)?
Answer: A relation is said to be in DKNF if all constraints and dependencies that should hold on
the the constraint can be enforced by simply enforcing the domain constraint and key constraint
on the relation.
55. What are the Inference Rules for Functional and Multivalued Dependency?
56. What is a query?
Answer: A query with respect to DBMS relates to user commands that are used to interact with a
data base. The query language can be classified into data definition language and data
manipulation language.
57. What is the use of CASCADE CONSTRAINTS?
Answer: When this clause is used with the DROP command, a parent table can be dropped even
when a child table exists.
58. Difference between VARCHAR and VARCHAR2?
Answer: Varchar means fixed length character data (size) i.e., min size-1 and max-2000
Varchar2 means variable length character data i.e., min-1 to max-4000
Dept of CSE, AIT
48
5th CSE
59. Which system table contains information on constraints on all the tables created?
Answer: USER_CONSTRAINTS
60. What is the main difference between the IN and EXISTS clause in sub queries?
Answer: The main difference between the IN and EXISTS predicate in sub query is the way in
which the query gets executed.
IN -- The inner query is executed first and the list of values obtained as its result is used by the
outer query. The inner query is executed for only once.
EXISTS -- The first row from the outer query is selected, then the inner query is executed and,
the outer query output uses this result for checking. This process of inner query execution repeats
as many no .of times as there are outer query rows. That is, if there are ten rows that can result
from outer query, the inner query is executed that many no. of times.
61. What is transaction in DBMS?
Answer: A transaction is a logical unit of database processing that includes one or more
database access operations these can include insertion, deletion, modification or
retrieval operations.
62. What are the different types of failures?
Disk failure
Atomicity
Consistency preservation
Isolation
Durability or permanency
49
5th CSE
Answer: When transactions are executing concurrently in an interleaved fashion, then the order
of execution of operations from the various transaction is known as schedule or history.
65. What is timestamp?
Answer: Timestamp is a unique identifier created by the DBMS to identify a transaction.
66. What is the use of Spool command?
Answer: Spool will record all your statements in a text file which will be created in the path
specified by you in path directory. Until and unless you switch off the spool it won't record your
statements but a file will be created in that path.
spool C:\temp.txt (temp file will be created in C drive)
select * from tablename1 (Output will be generated)
select * from tablename2 (Output will be generated)
etc......
spool off (All records after your file was
errors/messages/outputs/results etc.)
created
will
be
recorded
with
50