Saturday, March 31, 2012

Database Management System Important Questions 3


DESCRIPTIVES

Q.35
What are the  various symbols used  to  draw an  E-R  diagr am? Explain with  the help of  an
example how weak entity sets are represented in an E-R  diagram.
(6)
Ans: Various symbols used to draw an E-R diagram
Symbol
Meaning
ENTITY TYPE
WEAK ENTITY TYPE
RELATIONSHIP TYPE
ATTRIBUTE
KEY ATTRIBUTE
MULTIVALUED
ATTRIBUTE
COMPOSITE ATTRIBUTE
DERIVED ATTRIBUTE
1
N
E
E
R
CARDINALITY RATIO 1:N
1
2
Weak Entity Sets -
An entity set that  does  not have a key attribute is called weak entity
set.  A  weak  entity must participate  in an identifying  relationship  type  with  an  owner or
identifying entity type Entities are identified b y the combination of:
A partial key of th e weak entity type
The primary key o f the identifying entity type
Example:
Suppose  that  a  DEPENDENT  entity  is  identified  by  the  dependent’s  first  name  and
birhtdate,
and
the specific EMPLOYEE that the  dependent is related to.   DEPENDENT is
a  weak  entity  type  with  EMPLOYEE  as  its  identifying  entity  type  via  the  identifying
relationship type DEPENDENT_OF
FNAME BDATE ADDRESS
EMPLOYEE
DEPENDENTS_OF
DEPENDENT
Name Birth Date Relatio nship
Q.36
Define the following terms:
(8)
a)
Primary key.
b)
DML
c)
Multivalued attribute
d)
Relationship instance
Ans:  Primary  Key  –
Primary  key  is  one  of  the  candidate  keys.  It  should  be  chosen
such that its attribute values are never, or very rarely, changed.
b)   Data  Manipulation  Language  (DML)
–  A  data  manipulation  language  is  a
language  that  enables  users  to  access  or  manipulate  data  as  organized  b y  the
appropriate data model.
c)   Multivalued  Attribute
–  Multivalued  attribute  may  have  more  than  one  value  for
an entity. For example, PreviousDegrees of a STUDENT.
d)   Relationship  Instance  –
A  relationship  is  an  association  among  two  or  more
entities. An instance of relationship set is a set of relationships.
Q.37
Define  a  table  in  SQL  called  Client,  which  is  used  to  store  information  about  the
clients.  Define  CLIENT_NO  as the  primary  key  whose  first letter  must  start with ‘C’.
Also ensure that the column ‘NAME’ should not allow NULL values.
Column name  Data type  Size
CLIENT_NO  Varchar2  6
NAME  Varchar2  20
ADDRESS1  Varchar2  30
ADDRESS2  Varchar2  30
CITY  Varchar2  15
STATE  Varchar2  15
PINCODE  Number  6
BAL_DUE  Number  10, 2
(7)
Ans:
CREATE TABLE CLIENT
( CLIENT_NO      VARCHAR2(6)  PRIMARY  KEY  CHECK  (CLIENT_NO  LIKE
‘C%’),
NAME              VARCHAR2(20) NOT NULL,
ADDRESS1  VARCHAR2(30),
ADDRESS2  VARCHAR2(30),
CITY    VARCHAR2(15),
STATE    VARCHAR2(15),
PINCODE  NUMBER(6),
BAL_DUE  NUMBER(10,2))
Q.38
An orchestra database consists of the following relations:
(3.5 x 2=7)
CONDUCTS (conductor, composition)
REQUIRES (composition, Instrument)
PLAYS (Player, Instrument)
Give the relational calculus queries for the following:
(i)  List the compositions and the players.
(ii)  List the compositions which require th e ‘violin’ and the ‘congo’
Ans: (i)
Tuple Calculus:
{r[Composition]  || p[Player] | r
REQUIRES
p
PLAYS
r[Instrument] = p[ Instrument]}
Domain Calculus:
{c || p |
i
, i
(
> 
REQUIRES
> 
PLAYS
i
= i
)}
1
2
1
2
1
2
(ii)  Tuple Calculus:
{r[Composition]  | r
REQUIRES
u (u
REQUIRES
r[Composition] = u[Composition]
r[Instrument] = ‘violin’
u[Instrument] = ‘congo’)}
Domain Calculus:
{c |
i
, c
, i
(
> 
REQUIRES
, i
> 
REQUIRES
1
2
2
1
2
2
c
=   c
i
=   ‘ vi o li n ’
i
=   ‘ co n g o ’ ) }
1
1
2
Q. 3 9
Perform the  following with syntax and a suitable example
(i)
Create a table from ex isting table.
(ii)
Insert data in your table from another table.
Ans:  (i)
Create table as
e.g,  To  create  a  new  table  ‘N_emp’  with  employee  names  and  their  identification
numbers only from employee table, statement is   to  create  table  N_emp   as  select
empname, empid from  employee
(ii)
insert into
e.g,  To  insert  tuples  from  employee  into  N_emp  created  abov e,  use  following
statement
Insert into N_emp select empname, empid from employee
Q.40
What  is  an  INDEX  as  defined  in  ORACLE?  Write  the  syntax  of  creating  an  INDEX.
Create an ind ex for the table Client, field CLIENT_NO of Q.
(2+2+3)
Ans: Indexes  in  Oracle  –
Index  is  typically  a  listing  of  keywords  accompanied  by  the
location  of  information  on  a  subject.  In  oth er  words,  An  index  can  be  viewed  as  an
auxiliary  table  which  contains  two  fields:  the  key  and  the  location  of  the  record  of  that
key. Indexes  are  used to improve  the  performan ce  of  the  search op eration. Index es  are not
strictly necessar y to running Oracle, they do sp eed the process.
Syntax of Creating an Index:
CREATE [BITMAP]  [UNIQUE]  INDEX ON
( [, ] . . .);
Command:
CREATE INDEX client_client_no ON client(client_no);
Q.41
Consider the following relational database:
STUDENT (name, student#, class, major)
COURSE (course name, course#, credit hours, department)
SECTION (section identifier, course#, semester, year, instructor)
GRADE_REPORT (student#, section identifier,  grade)
PREREQUISITE ( course#, presequisite#)
Specify th e following queries in SQL on the above database schema.
(3.5 x 4=14)
(i)
Retrieve the names o f all students majoring in ‘C S’ (Computer Science).
(ii)
Retrieve the names o f all courses taught b y Professor King in 1998
(iii)
Delete  the  record  for  the  student  whose  name  is  ‘Smith’  and  whose  student
number is 17.
(iv)
Insert a new course <’Knowledge Engineering’, ‘CS4390’, 3, ‘CS’>
Ans: (i)
SELECT NAME FROM STUDENT WHERE MAJOR = ‘CS’
(ii)
SELECT COURSE_NAME FROM COURSE C, SECTION S
WHERE C.COURSE# = S.COURSE#
AND INSTRUCTOR = ‘KING’ AND YEAR = 1998
OR
SELECT COURSE_NAME FROM COURSE
WHERE COURSE# IN (SELECT COURSE# FROM SECTION
WHERE INSTRUCTOR = ‘KING’ AND YEAR  = 1998)
(iii)
DELETE FROM STUDENT WHERE NAME = ‘Smith’ AND STUDENT# = 17
(iv)
INSERT INTO COUR SE
VALUES(‘Knowledge Engineering’, ‘CS4390’, 3, ‘CS’)
Q.42
Explain the concept of a data model. What data models are used in
database man agement systems?
(7)
Ans:
Data  Model  –
Model  is  an  abstraction  process  that  hides  irrelevant  details  while
highlighting  details  relevant  to  the  applications  at  hand.  Similarly,  a  d ata  model  is  a
collection of concepts that can be used to describe structure of a database and provides the
necessary  means  to  achieve  this  abstraction.  Structure  of  database  means  the  data  types,
relationships,  and  constraints  that  should  hold  for  the  data.  In  general  a  data  model
consists of two elements:
A mathematical notation for ex pressing d ata and relationships.
Operations  on  the  data  that  serve  to  ex press  queries  and  other  manipulations  of  the
data.
Data Models used in DBMSs:
Hierarchical  Model  -
It  was  developed  to  model  many  types  of  hierarchical
organizations  that  exist  in  the  real  world.  It  uses  tree  structures  to  represent
relationship  among  r ecords.  In  hierarchical  model,  no  dependent  record   can  occur
without  its  parent  record  occurrence  and  no  dependent  record  occurrence  may  be
connected to more than one parent record occurrence.
Network  Model  -
It was formalised in  the late  1960s by  the  Database Task Group of
the  Conference  on  Data  System Language (DBTG/CODASYL).  It  uses two  different
data structures to represent the database entities and relationships between the entities,
namely
record type
and
set type
. In the network model,  the relationships as well  as the
navigation through the database are predefined at database creation time.
Relational  Model  -
The  relational  model  was  first  introduced  by  E.F.  Codd  of  the
IBM  Research in  1970. The model  uses  the  concept of  a  mathematical  relation  (like a
table  of  values)  as  its  basic  building  block,  and  has  its  theoretical  basis  in  set  theor y
and  first-order  predicate  logic.  The  relational  model  represents  the  database  as  a
collection of
relations
.
Object  Oriented  Model  –
This  model  is  based  on  the  object-oriented  programming
language paradigm. It includes the features of OOP like inheritance, object-identity,
encapsulation,  etc.  It  also  supports  a  rich  type  system,  including  structured  and
collection types.
Object  Relational  Model  –
This  model  combines  the  features  of  both  relational
model  and  object  oriented model.  It  extends  the  traditional  relational  model  with  a
variety of features such as structured and collectio n types.
Q.43
Briefly  explain the differences between a stand alone query language,
embedded query language and a data manipulation language.
(7)
Ans: Stand  alone  Query  Language  –
The  query  language  which  can  be  used
interactively  is  called  stand  alone  query  language.  It  does  not  need  the  su pport  of  a  host
language.
Embedded Query Language –
A query language (e.g., SQL) can be implemented in two
ways.  It  can  be  used  interactively  or  embedded  in  a  host  language.  The  use  of  query
language  commands  within  a  host  language  (e.g.,  C,  Java,  etc.)  program  is  called
embedded  query  language.  Although  similar  capabilities  are  supported  for  a  variety  of
host languages, the syntax sometimes varies.
Data  Manipulation  Language  (DML)  –
A  data  manipulation  language  is  a  language
that  enables  users  to  access  or  manipulate  data  as  organized  by  the  appropriate  data
model.
Q.44
Consider  the  following  relations  for  a  database  that  keeps  track  of  business  trips  of
salespersons in a sales office:
SALESPERSON (SSN, Name, start_year, Dept_no)
TRIP (SSN, From_ city,  To_city, Departur e_Date, Return_Date, Trip_ ID)
EXPENSE(TripID, Account#, Amount)
Specify th e following queries in relational algebra:
(4x3 =12)
(i)
Give the details (all attributes of TRIP)  for trips that exceeded $2000 in expenses.
(ii)
Print the SSN of salesman who took trips to ‘Honolulu’
(iii)
Print  the  trip  expenses  incurred  by  the  salesman  with  SSN=  ‘234-56-7890’.Note
that  the  salesman  may  have  gone  on  more  than  one  trip.  List  them  individually
Ans:  (i)
p
(
s
(TRIP    EXPENSE))
TRIP.*
amount > 2000
(ii)
p
(
s
(TRIP))
SSN
to_city = ‘Honolulu’
(iii)
p
(
s
(TRIP    EXPENSE))
EXPENSE.tripid, amount
SSN = ‘234-56-7890’
Q.45
What is the difference between a key and a superkey?
(2)
Ans: Key  –
A key  a  single attribute  or  a combin ation of  two  or  more  attributes  of  an
entity set that  is  used to identify one or  more  instances  (rows)  of the  set  (table).  It is  a
minimal combination of attributes.
Super Key –
A super key is a set of one  or more attributes  that, taken collectively, allows
us to identify uniquely a tuple in the relation.
Q.46
Why are  cursors necessary in embedded SQL?
(2)
Ans:
A  cursor  is  an  object  used  to  store  the  output  of  a  query  for  row-by-row
processing  by  the  application  programs.  SQL  statements  operate  on  a  set  of  data  and
return  a set  of data. On o ther  hand, host  language programs  operate on  a ro w  at  a time.
The  cursors are  used to  navigate  through  a  set  of  rows  returned  by  an  embedded  SQL
SELECT statement. A cursor can be compared to a pointer.
Q.47
Write  a  program  in  embedded  SQL  to  retrieve  the  total  trip  expenses  of  the  salesman
named ‘John’ for the relations of Q. 44
(6)
Ans:
EXEC SQL  BEGIN DECLARE SECTION;
long total_ex penses;
EXEC SQL END DECLARE SECTION;
EXEC SQL
SELECT  SUM(AMOUNT)  INTO  :total_expenses  FROM  EXPENSE  WHERE  TRIPID
IN (SELECT TRIP_ ID FROM TRIP
WHERE SSN = (SELECT SSN FROM SALEPERSON WHER E NAME = ‘John’));
printf(“\nThe total trip expenses of the salesman John is: %ld”, total_expenses);
Q.48
What are views? Explain how views are different from tables.
(6)
Ans:
A view in  SQL terminology is  a single table  that is derived from  other  tables. These  other
tables could  be base tables or previously defined  views. A  view does not necessarily  exist
in  physical  form;  it  is  co nsidered  a  virtual  table,  in contrast  to  base  tables,  whose  tuples
are  actually  stored  in  the  database.  This  limits  the  possible update  operations  that  can  be
applied  to  views,  but  it  does  not  provide  any  limitations  on  querying  a  view.  A  view
represents a different perspective of a base relation(s). The  definition of  a view  in a  create
view statement is stored in the system catalog. Any attribute in the view can be updated as
long as the  attribute is simple and not derived from a computation involving two or more
base relation attribute. View  that involve a  join  may  or may not b e updatable. Such views
are not updatable if they do not include the primar y keys o f the base relations.
Q.49
What  do  you  mean  b y  integrity  constraints?  Explain  the  two  constraints,  check  and
foreign key in SQL with an example for each. Give the syntax.
(8)
Ans:Integrity Constraints –
An
integrity constraint
is a condition specified on a database
schema  and  restricts  the  data  that  can  be  stored  in  an  instance  o f  th e  database.  If  a
database instance  satisfies all  the  integrity constraints  specified on the database  schema,  it
is  a  legal  instance.  A  DBMS  enforces  integrity  constraints,  in  that  it  permits  only  legal
instances to be stored in the database.
CHECK  constraint  –
CHECK  constraint  specifies  an  expression  that  must  always  be
true for ev ery row in the  table. It can’t ref er to values in other rows.
Syntax:
ALTER TABLE
ADD CONSTRAINT CHEC K();
FOREIGN  KEY  constraint  –  A  foreign  key  is  a  combination  of  columns  with  values
based on  the  primary  key values from  another table. A foreign key  constraint, also known
as  refer ential  integrity  constraint,  specifies  that  the  values  of  the  foreign  key  correspond
to  actual  values  of the primary or unique key in  other table.  One  can  refer  to  a primary or
unique key in the same table also.
Syntax:
ALTER TABLE
ADD CONSTRAINT FOREIGN KEY()
REFERENCES () ON {DELETE | UPDATE}
CASCADE;
Q.50
Define the following  constraints for the table client of Q.37
(6)
(i)  BAL_DUE must be at least 1000.
(ii)   NAME is a unique key.
Ans: (i)
ALTER  TABLE CLIENT
ADD CONSTRAINT C LIENT_BAL_DUE_C1 CHECK(BAL_DUE < 1000);
(ii)
ALTER TABLE CLIENT
ADD CONSTRAINT C LIENT_NAME_U UNIQUE(NAME);
Q.51
What are  the  different types  of database  end  users?   Discuss  the  main  activities  of  each.
(7)
Ans:
End-Users  –
End-users  are  the  people  whose  jobs  require  access  to  the  database  for
querying, updatin g,  and  generatin g  reports; the database primarily exists for  their use.  The
different types of end-users are:
Casual  end-users  –
occasionally  access  the  database,  need  different  information each
time
Naive  or  Parametric  end-users  –
includes  tellers,  clerks,  etc.,   make  u p  a  sizable
portion  of  database  end-users,  main  job  function  revolves  around  constantly  querying
and updating the database
Sophisticated end-users –
includes  engineers, scientists, business analyst,  etc., use for
their complex requirements
Stand-alone  users  –
maintain  personal  databases  by  using  ready-made  program
packages, provide easy-to-use menu-based or  gr aphics-based inter faces
Q.52
Discuss the typical user friendly interfaces and the types of users who use each.
(7)
Ans:
User-f riendly interfaces provided by a DBMS may include the following:
Menu-Based  Interfaces  f or  Web  Clients  or  Browsing –
These  interfaces  present  the
user with lists of  options, called  menus,  that lead  the  user through the  formulation of  a
request.   Pull-down  menus are a very popular  techniques  in  Web-based  user  interfaces.
They  are  also  used  in  browsing  interfaces,  which  allow  a  user  to  look  through  the
contents of a database in  an exploratory and unstr uctured manner.
Forms-Based  Interfaces  –
A  forms-based  interface  displays  a  form  to  each  user.
Forms  are  usually  designed  and  programmed  for  naive  users  and  interfaces  to  canned
transactions. Many DBMSs have forms specification languages.
Graphical User  Interfaces  (GUIs) –
A GUI typically  displays  a  schema to the  user  in
diagrammatic  form.  The user  can  then specify  a  query  by manipulating the diagram. In
many  cases,  GUIs  utilizes  both  menus  and  forms.  Most  GUIs  use a  pointing  device  to
pick certain parts of the displayed schema diagram.
Natural Language  Interfaces  –
These  interfaces  accept  requests  written  in  English  or
some  other  langu age  and  attempt  to  “understand”  them.  A  natural  language  interface
usually  has  its  own  “schema,”  which  is  similar  to  the  database  conceptual  schema,  as
well as a dictionary of important words.
Interfaces for Parametric  Users  –
Parametric  users, such  as bank  tellers,  often  have a
small set  of operations that  they must perform rep eatedly. The interfaces for  these  users
usually  have  a  small  set  of  abbreviated  commands  with  the  goal  of  minimizing  the
number of keystrok es required for each request.
Interfaces for the DBA  –
Most database systems contain privileged commands that can
be used only b y the DBA’s staff.  These include commands for creating accounts, setting
system  parameters,  granting  account  authorization,  changing  a  schema,  and  a
reorganizing th e storage  structures of a database.
Q.53
With the help of an example show how records can be deleted and updated in QBE.
(5)
(i)
In crease Pay_Rate o f employees with the skill of ‘cook’ by 10%.
(ii)
Delete employee record for EMP# 123459
Ans: (i)
EMPLOYEE  Emp#  Name  Skill  Pay_Rate
EX
Cook  PX
U.
EX
PX * 1.1
(ii)
EMPLOYEE  Emp#  Name  Skill  Pay_Rate
D.  123459