Database Management

System
Homework-2

Submitted By:

Name: Suraj Kumar Singh

Section: E - 3004

Roll_No: RE3004B77

Group: 2

Course_code: 1604

Subject_code: CAP301

Date_Of_Submission: 20/09/2010

Submitted To:

Mr. Sartaj Singh

 PART ‘A’

Q1:-> Consider the insurance database where the primary keys

are underlined . Construct the following SQL queries for this
relational database.
(a)Find the total number of people who owned cars that
were involved in accidents in 1989.
(b)Add a new accident to the database; assume any values
for required attributes.
(c)Delete the mazla belonging to ”john Smith”.

Insurance database
Person ( driverid, name, address)
Car (license, model, year)
Accident (reportnumber , date, location)
Owns(driverid, license)
Participated(driverid, car, reportno, damageamount)

Ans:- (
a) select count(*) from owns where
participated.driverid=owns.driverid and
participated.reportno=accident.reportno and accident.date
between 1-jan-1989 and 31-dec-1989;

(b) Insert into accident values(05,’18-mar-1989’,’delhi’);

(c) Delete from car where car.license=owns.license and

owns.driverid=person.driverid and person.name=’john smith’;

Q2:-> Explain the various formats for inserting the records into
the database table?

Ans:

There are three formats for inserting the records into the database.
The first form doesn't specify the column names where the data will
be inserted, only their values:

Syntax:

INSERT INTO table_name

VALUES (value1, value2, value3,...)

Example:

We have the following "Persons" table:

P_Id FirstName LastName Address City

1 Amit Sharmna  NULL ludhiana
2 Raju shriwastav phagwara jalandhar
3 Prashant Singh  NULL jammu

INSERT INTO Persons VALUES (3,’Mohan’, ‘Mishra’, ‘Phagwara’,’Jalandhar’);

P_Id FirstName LastName Address City

1 Amit Sharmna  NULL Ludhiana
2 Raju shriwastav phagwara Jalandhar
3 Prashant Singh  NULL Jammu
4 Mohan Mishra phagwara Jalandhar

The second form specifies both the column names and the values to
be inserted:

Syntax:

INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...)

Example:

INSERT INTO Persons (P_Id, FirstName, LastName, Address, City)

VALUES (5, 'Ramesh', 'Ghosh', ‘Rohini’, ‘Delhi’)
 P_Id FirstName LastName Address City
1 Amit Sharmna  NULL Ludhiana
2 Raju Shriwastav phagwara Jalandhar
3 Prashant Singh  NULL Jammu
4 Mohan Mishra phagwara Jalandhar
5 Ramesh Ghosh Rohini Delhi

The Third form is multiple value/records in one single query insert

into database:

INSERT INTO Persons (P_Id, FirstName, LastName, Address, City)

VALUES

VALUES (1, 'Ramesh', 'Ghosh', ‘Rohini’, ‘Delhi’)

VALUES (2, 'Raju', 'singh', ‘phagwara’, ‘jalandhar’)
VALUES (3, 'mohan', 'mishra', ‘NULL’, ‘ludhiana’)
VALUES (4, 'prahant', 'Ghosh', ‘NULL’, ‘jalandhar’)

Q3:-> Give an SQL schema definition for the employee

database. Choose an appropriate domain for each attribute
and an appropriate primary key for each relation schema.
Employee Database
Employee(employeename, street, city)
Works(employeename, companyname,salary)
Company(companyname, city)
Manages(employeename, managertime)

Ans:- create table Employee

(employeename char(20) not null,
Street varchar2(30),
City char(30),
Primary key (employeename))

Create table works

(employeename char(20) not null,
 Companyname char(15),
Salary number(6),
Primary key (employeename))

Create table company

( companyname char(15) not null,
City char(30),
Primary key (companyname))

Create table Manages

(employeename char(15) not null,
Managetime varchar(15),
Primary key (employeename))

Q4:-> Describe the following terms by giving its importance

with example.
(a) null value (b)group by clause (c)joins
(d) views (e) complex queries (f)where
and order by clause

Ans:

(a) NULL VALUE

If a column in a table is optional, we can insert a new record or update

an existing record without adding a value to this column. This means
that the field will be saved with a NULL value.

NULL values are treated differently from other values.

NULL is used as a placeholder for unknown or inapplicable values.

Note: It is not possible to compare NULL and 0; they are not

equivalent.
 P_Id FirstName LastName Address City
1 Amit Sharmna  NULL Ludhiana
2 Raju shriwastav Phagwara Jalandhar
3 Prashant Singh  NULL Jammu

Suppose that the "Address" column in the "Persons" table is optional.

This means that if we insert a record with no value for the "Address"
column, the "Address" column will be saved with a NULL value.

It is not possible to test for NULL values with comparison operators,

such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators to check
the NULL values.

Example:

SELECT FirstName, LastName, Address FROM Persons

WHERE Address IS NULL

SELECT FirstName, LastName, Address FROM Persons

WHERE Address IS NOT NULL

(b) GROUP BY

GROUP BY clause: -This clause is an optional clause of select

command. It is used to group similar rows together to produce one
summary row of query output for each group. These also arrange the
rows in order in the query result.
The syntax is: -
Select <column list> from <table name> GROUP BY
<COLUMN_NAME>;8
Writing a query for grouping the amount And showing total quantity in
the MARKETTING table shown as below:

Cus_name billno Amount

RANI 12002 2000
VINA 3300 5000
 NITU 1022 2500
KINNI 1022 1011
LILA 3300 2000

Select amount sum(amount) “Total amount” from MARKETTING

GROUP BY billno.; 8

billno Total amount

12002 2000
3300 7000
1022 3511

(c) JOIN

The SQL JOIN clause is used whenever we have to select data from

2 or more tables.

To be able to use SQL JOIN clause to extract data from 2 or more

tables, we need a relationship between certain columns in these
tables.

We have two tables customers and sales.

CustomerID FirstName LastName Email DOB Phone

1 Ajay Gupta [email protected] 2/4/1968 626 222-2222

2 Mohan Sharma [email protected] 4/4/1974 323 455-4545

3 Raju Paul [email protected] 5/24/1978 416 323-3232

4 vikash Agarwal [email protected] 20/10/1980 416 323-8888

Customers:

Sales:

CustomerID Date SaleAmount

2 5/6/2004 100.22
1 5/7/2004 99.95
 3 5/7/2004 122.95
3 5/13/2004 100.00
4 5/22/2004 555.55

In these two tables column “CustomerId” is common that we can

extract information from both tables by matching their CustomerID
columns.

Example:

SELECT Customers.FirstName, Customers.LastName,

SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName 

FirstName LastName SalesPerCustomers

Ajay Gupta 99.95
Mohan Sharma 100.22
Raju Paul 122.95
Vikash Agarwal 555.55

Types of JOIN

 Inner join
 Outer join
 Cross join

1. Inner join: 
Inner join is the default type of join, it will produces the result
set, which contains matched rows only. 

Syntax: select * from table1<innerjoin>table2 

Simply we can specify Join also. In place of inner join

Syntax: select * from table1<join>table2 

2. Outer join: 
Outer join produces the results, which contains matched rows
 and unmatched rows. 

Outer join is further classified as three types. They are 

1.left outer join 
2.right outer join 
3.full outer join.

a. Left outer join: 

Left outer join produces the results, which contains all the rows
from left table and matched rows from right table. 

Syntax: select * from table1<leftouterjoin>table2 

b. Right outer join: 

Right outer join produces the result set, which contains all the
rows from right table and matched rows from left table. 

Syntax: select * from table1<right outer join>table2 

c. Full outer join: 

Full outer join produces the result set, which contains all the
rows from left table and all the rows from right table. 

Syntax: select * from table1<fullouterjoin>table2 

3. Cross join: 
A join without having any condition is known as cross join, in cross join
every row in first table is joins with every row in second table. Cross
join is nothing but Cartesian product.

Syntax: select * from table1<cross join>table2 

(d) VIEW

A relation that is not of the conceptual model but is made visible to

a user as a “virtual relation” is called a view.

A view is defined using the create view statement which has the
form

create view v as < query expression >

 where <query expression> is any legal SQL expression. The
view name is represented by v.

Once a view is defined, the view name can be used to refer to the
virtual relation that the view generates.

CREATE VIEW V_Customer

AS SELECT First_Name, Last_Name, Country
FROM Customer

FirstName LastName Country

Ajay Gupta
India
Mohan Sharma
U.S.A
Raju Paul
London
Vikash Agarwal
Australia

(e) Complex query:-complex queries are often hard or impossible to

write as a single sql block or a union/intersection/difference of sQL
blocks. (An sQL block consisL of a single select from- where statement,
possibly with group by and having clauses.). The two ways of
composing multiple sql- blocks to express a=complex query:
derivedrelations and the with clause

syntax:-
Select……
From (select…..
From…..
Where……
)
Where……
 (f) Where Clause

WHERE clause : The where clause of select command is used to

specify a search condition. This clause tells SQL to rewrite only
certain row of data that fulfills the specified condition.
The syntax is :

Select <column list> from <table name> where <search

condition>;8
Select amount from MARKETTING table whose amount<=2500.

Cus_name billno amount

RANI 12002 2000
VINA 3300 5000
NITU 1022 2500
KINNI 1022 1011
LILA 3300 2000
:
Select *from MARKETTING where amount<=2500; 8

Cus_name billno amount

RANI 12002 2000
NITU 1022 2500
KINNI 1022 1011
LILA 3300 2000

(f) ORDER BY

The ORDER BY keyword is used to sort the results by a specified

column.

The ORDER BY keyword sorts the records in ascending order by

default.

If we want to sort the records in a descending order, we can use the

DESC keyword.
Syntax:

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

Example:

Ascending order

Select *from Marketing order by amount;

Cus_name billno amount

KINNI 1022 1011
RANI 12002 2000
LILA 3300 2000
NITU 1022 2500
VINA 3300 5000

Descending order

Select *from Marketing order by amount DESC;

Cus_name billno amount

VINA 3300 5000
NITU 1022 2500
LILA 3300 2000
RANI 12002 2000
KINNI 1022 1011
 PART “B”

Q1:-> Explain when we use before triggers and after triggers

statements in the database?

Ans:
A trigger is a statement that is executed automatically by the
system as a side effect of a modification to the database.
To design a trigger mechanism, we must:
 Specify the conditions under which the trigger is to be
executed.
 Specify the actions to be taken when the trigger
executes.

Before Triggers

The BEFORE triggers execute the trigger action before the triggering
statement (INSERT/DELTE/UPDATE) is executed. The BEFORE triggers
are commonly used in the following situations :
For example,
a) BEFORE UPDATE trigger on Items table can be allowed to be
completed if only there exists a corresponding record for the Delivery
table for a particular item. That is, if only an item has been delivered
then its stock quality can be updated otherwise this update operation
would not be allowed to complete at all.
b) When specific column values need to be delivered before
completing a triggering INSERT or UPDATE statement. For example, a
BEFORE INSERT trigger on the student table may be used to assign a
value for the primary key column BEFORE a new record is inserted into
this table. This trigger is activated upon the INSERT operation into the
student table and the trigger actions are executed BEFORE the INSERT
itself is performed.

After Triggers

An AFTER trigger is activated after the event for which it is defined, is

executed. That is, AFTER triggers execute the trigger action after the
triggering statement is executed. The AFTER triggers are used when
you want the triggering statement to complete before executing the
trigger action.
For example, an AFTER DELETE trigger on Student table deletes
corresponding records of deleted Student’s rollno from Marks table.
This AFTER DELTE trigger on student table can be allowed to execute
only after a student record(s) has been successfully deleted. There is
no point deleting corresponding marks records of a student unless its
record in the student table has been successfully deleted.

Q2:-> Discuss insertion, deletion and modification

anomalies .why are they considered bad? Illustrate with the
help of example?

Ans:
Insert Anomalies

An insertion anomaly occurs when we are unable to insert a tuple into

a table. Such a situation can arise when the value of primary key is
not known. As per the entity integrity rule, the primary key cannot
have null value. Therefore, the value/s corresponding to primary key
attribute/s of the tuple must be assigned before inserting the tuple. If
these values are unknown, the tuple cannot be inserted into the table.

Stu_No Stu_Name Address Course_ID Course_Name Instructor

1001 Amit Jalandhar Cap302 Operating_System Mrs. Jasleen
1002 Vikash Chandighar Cap301 Data_base Mr.Sartaj
Singh
1003 Sumit ludhiana Cap303 Financial_Management Mrs.
Manpreet
Kaur
1004 Rahul Jammu Cap301 Data_base Mr.Sartaj
Singh
1005 Vijay Chandighar Cap303 Financial_Management Mrs.
Manpreet
Kaur

Let the primary key of the above database be (Stu_No, Course_ID),

then if we want to add new course the database
Then we have to insert course_ID, Course_Name and instructor and
leave the column stu_N0, Name, address as NULL. That is not possible
Because Stu_no is primary key which have some constraint.
Delete Anomalies

In case of a deletion anomaly, the deletion of a tuple causes problems

in the database.This can happen when we delete a tuple, which
contains an important piece of information,and the tuple being the last
one in the table containing the information. With the deletion of the
tuple the important piece of information also gets removed from the
database.

Stu_No Stu_Name Address Course_ID Course_Name Instructor

1001 Amit Jalandhar Cap302 Operating_System Mrs. Jasleen
1002 Vikash Chandighar Cap301 Data_base Mr.Sartaj
Singh
1003 Sumit ludhiana Cap303 Financial_Management Mrs.
Manpreet
Kaur
1004 Rahul Jammu Cap301 Data_base Mr.Sartaj
Singh
1005 Vijay Chandighar Cap303 Financial_Management Mrs.
Manpreet
Kaur

If we delete the tuple corresponding to stu_no 1001 doing Cap302, we

will loose relevant information about course Cap302 (course_name,
instructor) if the student 1001 was the only student enrolled in that
course.

Modification Anomalies

An modification anomaly occurs when we have a lot of redundancy in

our data. Due to redundancy, data updating becomes cumbersome. If
we have to update one attribute value, which is occurring a number of
times, we have to search for every occurrence of that value and then
change it. The anomalies will be further elaborated when we discuss
the normal forms Before we proceed with discussion on normalization
it would be useful to understand the concept of dependencies.

Stu_No Stu_Name Address Course_ID Course_Name Instructor

1001 Amit Jalandhar Cap302 Operating_System Mrs. Jasleen
1002 Vikash Chandighar Cap301 Data_base Mr.Sartaj Singh
1003 Sumit ludhiana Cap303 Financial_Management Mrs. Manpreet
Kaur

1004 Rahul Jammu Cap301 Data_base Mr.Sartaj Singh

1005 Vijay Chandighar Cap303 Financial_Management Mrs. Manpreet

Kaur

In STU_DETAIL, if we want to change the name of Instructor of

Cource_ID cap301 then it will update all the all the tuples in the
table,but some reason all the tuples are not updated, we might have a
database that gives two names of instructor for subject cap301.

Q3:->What is functional dependency? What are the possible

sources of information that defines the functional dependencies
that hold among the attributes of relation schema?

Ans:
Functional Dependency (F.D) – Functional dependency represents
semantic association between attributes. If a value of an attribute A
determines the value of another attribute B, we say B is functionally
dependent on A. This is denoted by
A => B and read as “A determines B” and A is called the determinant

• For any two tuples t1 and t2 in any relation instance r(R):

If t1[X]= t2[X], then t1[Y]= t2[Y]
X is a candidate key of R
⇒ X→ Y for any subset Y of R
• X→ Y holds if whenever two tuples have the same value for X,
they must have the same value for Y
• FDs are derived from the real-world constrains on the attributes
 • Social security number determines employee name SSN →
ENAME
• Project number determines project name and location
PNUMBER →{PNAME, PLOCATION}

Lat a set F of functional dependencies, there are certain other

functional dependencies that are logically implied by F.
For example: If A ® B and B ® C, then we can infer that A
®C
The set of all functional dependencies logically implied by F is the
closure of F.
We denote the closure of F by F+.
F+ is a superset of F.

Q4:-> What is multivalued dependency? What type of

constraint does it specify? When does it arise?

Ans:
Multi-valued Dependency—Multi-valued dependency refers to m:n
(many-to-many) relationships. We say multi-valued dependency exists
between two data items when one value of the first data item gives a
collection of values of the second data item i.e., it multi-determines
the second data items.

Dependency between attributes (for example, A, B, and C) in a

relation, such that for each value of A there is a set of values for B and
a set of values for C. However, set of values for B and C are
independent of each other.

MVD between attributes A, B, and C in a relation using the following

notation:
A B
A C

Eid Language Skill

100 English Teaching
100 Kurdish Politic
100 English Politic
100 Kurdish Teaching
200 Arabic Singing

In the above table ,B and C are dependent to A but B and C are

independent to each other.

specifies the following constraint on any relation r of R: if two

tuples t1 and t2 exist in r such that t1[X] = t2[X] then t3 and t4
should also exist in r with
the following properties
t3[x] = t4[X] = t1[X] = t2[X]
t3[Y] = t1[Y] and t4[Y] = t2[Y]
t3[Z] = t2[Z] and t4[Z] = t1[Z]
where [Z = (R-(X U Y)) ]

Q5:->Define join dependencies and 5th normal form? Why is

5NF also known as project –join normal form(PJNF)?

Ans:
Join Dependency—If we decompose a relation into smaller relations
and the join of the smaller relations does not give us tuples as in the
parent relation, we say the relation has join dependency.

Fifth normal form (5NF and also PJ/NF) requires that there
are no non-trivial join dependencies that do not follow from
the key constraints. A table is said to be in the 5NF if and only
if it is in 4NF and every join dependency in it is implied by the
candidate keys.

Q6:->Define boyce– Codd normal form. How does it differ from

3NF ? why is it considered as stronger form of 3NF ?

Ans:
Boyce-Codd normal form (or BCNF) is a normal form used in
database normalization. It
is a slightly stronger version of the third normal form (3NF). A table is
in Boyce-Codd
normal form if and only if, for every one of its non-trivial functional
dependencies X →
Y, X is a superkey—that is, X is either a candidate key or a superset
thereof.
Only in rare cases does a 3NF table not meet the requirements of
BCNF. A 3NF table
which does not have multiple overlapping candidate keys is
guaranteed to be in BCNF.
Boyce-codd Normal Form (BCNF)A Table is in BCNF if and only if every
determinant
is a candidate key. BCNF is a stronger form of 3NF.

• Difference between 3NF and BCNF is that for a functional

dependency A ® B, 3NF allows this dependency in a relation if B
is a primary-key attribute and A is not a candidate key.

• Whereas, BCNF insists that for this dependency to remain in a

relation, A must be a candidate key.

• Every relation in BCNF is also in 3NF. However, relation in 3NF

may not be in BCNF.