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