5/23/23, 9:23 AM DSE 2252

Exam Date & Time:

31-May--2023 (02:30 PM - 05:30 PM)

MANIPALACADEMY OF HIGHER EDUCATION

FOURTH SEMESTER B.TECHEND SEMESTER EXAMINATIONS, MAY 2023
DATABASE SYSTEMS(DSE 2252]
Marks: 50 Duration: 180 mins.
A
Answer all the qucstions.
Instructions to Candidates:
Answer ALL questions
Missing data may be suitably assumed
) Explain how entity set and relationship set in the ER-diagram can be reduced into relations.
(5)

A)

B) Explain the lock conversion method in 2-phase locking with an example.

(3)

C) The weak entity set is existence dependent on the identifying entity set, Illustrate. (2)

2) Explain the different recovery methods implemented in log based recovery system.
(5)
A)
B) Given some sample data from the Student entity. Write MongoDB statements to do the following:

RegNo Sname DOB City Hobbies

202301|Siona |14/09/2003 Mumbai Reading, Browsing. Gardening

202450| Thara 23/07/2004 Goa Dancing,Singing

(3)
202456 Darshan 04/05/200s Mumbai Cycling, Browsing

202678 Rekha 09/03/2004 Manipal Gardening

i. Insert student data into the collection Student.

ii. Update the city for Thara as Manipal.

iii. Delete information ofDarshan.

Mention the metrics uscd for evaluating diferent indexing techniqucs.

C) (2)

3)
i) Describe the method for testing whether an attribute is extraneous in XY in F.
ii) Considertherelation R4, B, C, D, E, F) with following set F={4-BCD, BC-DE, B-D, (5)
A) D+A}.Compute the canonical cover of F.

B) Consider the tables given in Figure l. and write a PL/SQL block to take member_id, two borrowing
dates as input from keyboard and display Member Namne, Btitle, Bauthor corresponding to borrowing
transactions that took place during those dates. (3)

C) Which type of NOSQL database is closer to RDBMS and why?

(2)

1/3
htps://manipal.exarmcloud. in/reports/exam-qpaper.php
 5/23/23.,9:23 AM DSE 2252

4) below:
Consider the relational schema on bank database application given
A' Customer(Custid, Name. Phone,intr custid,cily)
std is pyimar key and intr custidis foreign key referring cstomer
Iransaction(Aceno,Ir tvpe.Tr ld.Tr date, Amount)
AcCno is foreign ke reterrine Account. Ti ld.Tr date are primary Keys

Account (Accno.Custld,Ace type,Balance)

Accno is primay ker and custid is foreign kev referring Customer
(5)
Write the following qucries in
relational algebra.
1. LIst the Name of the Customers vho arebanving
Ralance more than 300000 in KD aceott
. DISPlay the name of the customer who has
2023" denositedan amount of more than 4SO00 ON bo
ii. List the name of
Customer and Introducer if both reside in the same
iv. Display the count of Ciy.
customers for cach account type
v. Display the total
amount withdrawn by a customer Manju.
B) Consider the following
create the correspondingrelational
tables.
schema for a Librarv Management database application and write the DDL
commands to
MEMBERS (Member id, Name,
Max Books Borro- is9 books andDesignation,
it gets
Date Birth, Max
Books_Borro)
BOOKS (Bid, Btitle, Bauthor, Bpublisher,decremented when a book is Borrowed
Bprice, BCopies, BCategory)
BCategory - Novel, Biography, Detective etc.
BORROW_BOOK (Member id, Bid, Borro Date, Borro Status)
Borro Status - either Pending ('P')or
Note: Underlined attributes are Returned ('R') (3)
Primary Keys and Same column name
referencing same column name Primary Keys. attributes are Foreion ke,.

Figure 1: Relational Schema of Library Management Database

C) Mention the condition based on which any given relation is said to be

(A.B.C.D), F= {AB-> C, B-> D, C-> A}, check whether it satisfies inBCNE
BCNF. For the relation Þ
(2)
5) Consider the tables given in Figure 1. and write the following queries in SQL
i. Retrieve the id and name of the members who are older than 45 years
A) ii. List the title, author, publisher of the books reserved by members with desienation
'Assistant Professor

iii. Findthe name, designation of the members who have borrowed the books that cost
than Rs.S00 and on 15th November 2022.
(5)
iv. Display the name of members who have not yel relurned (Borro Status ='P') nore b.
5 books.

v. FindCatevorv book, number of times it is borTOWed durmg n Ist half year 20)2 ..)
number of tines borrowed.
Sort them indescending order of

a lrigger to update BCopies (deeremet by

B) Consider the tables given in Figure 1. and write
borrow_book recod iS Iserted with Borro Sto
(deerement by )when a
Max Books Borro Borro by I when Borro_Status is updated to p (3)
Max Books
and increment BCopies by I,
function Due Books Count which takes a
1. and write a stored (2)
C)
Consider thetables given in Figure parameter and returns number of books borrowed depending on
borrowo
input
member id and Borro date as due ate of correspondung
exceeded due dates. Assume
how many borrowed books
https://manipal.examcloud.in/reports/exam-qpaper.php 2/3
S23/23, 9:23 AM DSE 2252

10days after Borow Date. Also devclop a PLSQL block which takes member id and Borro date as
input and calls Due Books Count function to display returmed value.

..nd-...
 sfh- Dateboe Sytm

Type: MCQ

Q. Which of the following is not acharacteristic of adatabase system? (0.5)

independently
1. **Each application is free to name data elements
2. Self-describing nature
3. Insulation between programs and data
4. Sharing of data and multiuser transaction processing
communicated to the
Q2. In ABC institution, astudents residential address has been changed and
office. Now all the communication from the institution will reach to the new address because of
feature of database System (0.5)
1. **Data Consistency
2. Data Abstraction
3. Data Isolation

4. Data lndependence
Q3. Several reservation agents try to assign a seat on an airline flight, component of
DBMS ensures that each seat can be accessed by only one agent at a time for assignment to a
passenger. (0.5)
1. Recovery Manager
2. **Concurrency Control Manager
3. Authorization Manager
4. Integrity Manager

(0.5)
Q4. User A who interacts with database system can be identified as sophisticated user because
1. User A use any application or programs to request the database
2. **User A write SQL queries to select/insert/delete/update data
3. User A manages the entire database systemn
4. UserA design and implements tools required for database system

Q5. Consider the following Student database:

Student(ID, Name,City,tutor),JD identifies each student
Course(Code,title, department), code identifies each course
Enrolment(ID,Code,Marks) , which student has taken which course
Which one of the following is correct?(0.5)
1. ID and Code in Enrolment can be super keys but not candidate keys
2. (ID,tutor) in Student is a candidate key
3. ** (ID,Code) in Enrolment is composite key
4. Department in course is a super key in Course
O6. Which relational algebraic operation can reduce number of attributes existing in the operand
relation?

(0.5)
1. Select
 2. Join

3. **Project
4. Cartesian Product
data independence is not true?
Q7. Which one of the following statements about
(0.5)
and eliminating redundancies
1 **Data independence leads to difficulties in integrating data
change in physical storage structure
2. Dataindependence is the immunity of applications to
access strategy
different applications to have different views of
3. Data independence makes it possible for
the same data
database to grow without affecting existing
4. Data independence is necessary toenable the
applications
Q8. Select all the properties which are true with respect to Foreign key ?
Can take NULL values
Can Take Duplicates values
Can Take Unique Values Only
be different
Domain of foreign key and corresponding Parent key domain can
(0.5)
1. () &(iü)
2. ** (i) &(ü)
3. (iü) &(iii)
4. (ü) & (iv)
automate deletion of child records
SOL clause has to be used If we want to
09.
without deleting corresponding parent records (0.5)
1.
** ON DELETE SET NULL

2. ON DELETE SET 0

3. ON DELETE CASCADE
4. ON DELETE SET CASCADE
Q10. Relational Algebra is a language.(0.5)
1 ** Procedural Language
2. Declarative Language
3. Object-Oriented Language
4. Block Declarative Language

Type; DES
o11. Why does Database Management System require a query processor module? (2)
o12, Asume that the following tables with primary key exists.
 Vendor (V code,V name, V order),
Product(P code,Quantity On Hand,Costprice, Discount, V_code),
Write the SQL commands to do following operations.
table referencing Vendor table.
a)Add a foreign key constraint on V code in productthan 10% of CostPrice (2)
D) Put a condition on Discount that must not be more

Q13. Consider the following relations

Student(lD, Name,City,tutor), ID is primary key
Subject(Code,Sub name,credit,type), Code is primary key this is foreign key
Enrolment(|D,Code,Marks), ID and Code is composite and each of
queries.
Write the Relational algebraic expressions for the following
than 70.
Display the name of the subject where the average mark scored is greater students.
a. subject that has more than 5
b. Display number of students and the name of the
their average marks.
C. Display the student name and
(3)
Q14. Consider the following relational schema:
Student (Regno, SName, Gender, Dob, City)
Unique
Subject (SubCode, Title, Credits); Title is
Test2 Mark, EndSemMark, Total)
Marks (RegNo, SubCode, Testl Mark, attributes with same name as having primary
key and
primary keys and
Underlined attributes are queries
statements for the following
foreign key relationship. Write SQL
students have scored marks
the student and corresponding subject name in which
a) Find name of
end sem exams.
more than 80 in testl, test2 and 5 students have scored
Total
number of students and subject names in which more than
b) Find the
more than 250. student who born on the same
of all female students if there is at least one male
c) Find the name
corresponding female student born.
month as that of

(3
 Tune: 20-Apr-2023(08:00 AM- 09:00
AM)
Date&
Exam

MANIPAL ACADEMY OF HIGHER EDUCATION

B.Tech IVth Semester Sessional-2 Examination April 2023
DATABASE SYSTEMS (DSE 2252]
Duration: 60 mins.
Marks: 1S
MCQ

possible from the given ER diagram? (0.5)

01. How many relational schemas are
1. 5
2. 3
3. 4
4. 2
holds?(0.5)
instance given and determine whether B->C,C’A
Q2. Consider the relational
B

X2 Y1 Z2

X2 Y4 Z3

X1 Y1 Z4

X2 Y4 Z2

Y2 71
X3

1. NO,YES
2. YES,YES
3. YES,NO
NO,NO
R=(P,R,O,S)? (0.5)
is a trivial functional dependency for
Q3. Which of the following
1. PQ-R
2. POS’0S
3. P’S
4. S’R
attribute? (0.5)
following is unlikely to be an
Q4. Which of the
1, Address
2. Date of Birth
3. Supplies
4. Item name

following are correct? (0.5)

Q5. Which of the key
must have a primary
1. Each entity have attributes
must
2 Each relationship relationship plays a role
participating in a
3. Each entity real-world associations among at most two
entities.
4. Relationships represent
Q6. In general, what will be the identifier for identifying many-many cardinality binary
relationship
uniquely? (0.5)
1
Primary key of both entities &descriptive attribute if exist.
2. Primary key of any one entity.
3. Partial key and descriptive attribute of the relationship.
4 All attributes of both entities.
Q7. The valid relational schema for the
student entity set is (0.5)
Roll No Phone Number
First Name

Middle Name
narme
Student
Last Name

DOB

1.
2. Student(RollNo, FirstName,MiddleName,LastName,DOB)
3 Student(RollNo, FirstName,MiddleName,LastName)
Student(RollNo,FirstName, MiddleName,LastName,PhoneNumber)
4. Student(RollNo, Name,FirstName,MiddleName,LastName,PhoneNumber)
Q8. Given R= (A, B, C,
of the D,E,F) is a relational schema and F= {A -> BC,B->E,
following functional dependency
1
2
AD->F does not hold? (0.5) CD->EF}.Which
BC->F
3 AD->B
4. BD->E

Q9. Given R= (A, B,

C,D,E,F) is a
Armstrong rules can be used to relational schema and F= {A -> BC,B->E,
1, Union and Transitivity
add A->E in F? (0.5) CD->EF}.Which
2. Decomposition and Transitivity
3. Union and Pseudotransitivity
4. Augmentation and Transitivity
Q10. Which of the following are
1. Implicit cursor are incorrect statement regarding the implicit cursor?(0.5)
created when executing SQL query
rows
statements returning multiple
2. Implicit cursor are created when
3. executing SQL query
4.
Implicit cursor are defined
automatically by oracle for statements
Implicit cursor are not defined all SQL
returning single rowS
DML statements.
automnatically by oracle for any SQL DDL statements.
Type:DES

Convert the
following specialization into
Q11. relational schema using method which
tribute redundancy. (2) minimizes

name street city

person

salary credit-rating

employee customer

officer teller secretary

officer-number hours-worked

station-number hours-worked

Q12.Consider the following schema and write a procedure Test2Q12(). (2)

Customer (Custld, CName, Email);
Policy(Policy ng, Premium, Policy_Type, Policy_Inception_date, CustiD, Policy_Status,
Maturity_Date, Maturity_amount);
Note: Attributes with underline are Primary key; Policy.Custld is Foreign Key; Policy _Status may be
Active/InActive/Matured; Policy_Type may be -Lifelnsurance/MoneyBack/ULIP
Write the procedure to take Policy_type as parameter and find how many numbers of policies this
type is active and how many are inactive.Return these counts to the procedure calling PL/SQL
block and display.
Q13. I) Write the Procedure for Computing F*.
|) Given R={A,B,C,D,E,F,G) and F={AB->CD,AF->D, DE->F,C->G,F->E,G->A).Check whether CF
andABF are super keys with proper steps? If yes,prove whether they are candidate keys.
(3)
Q14. Consider the following schema and write a PL/SQL block. (3)
Customer (Custld,CName, Email);
Policy(Policy no, Premium, Policy_Type, Policy_Inception_date, CustiD, Policy_Status,
Maturity_Date, Maturity_amount);
Note: Attributes with underline are Primary key; Policy.Custld is Foreign Key; Policy_Status may be
Active/nActive/Matured; Policy_Type may be - Lifelnsurance/MoneyBack/ULIP
Write a PL/SQL block to display information about policies (such as Policy_No, Premium,Policy_type,
Policy Inception date) that are taken after 01-01-2022 by Customer with name Rajesh. Also displav
number of matured policies as on date 18-04-2025 owned by Rajesh.
 Type: DES
Ql. Explain the various functional components of database system with a neat diagram. (5)
Q2. What are the various unary operations in relational algebra? (3)
Q3. What are the features of a good relational database design? (2)

Q4.
Consider the following relations:
Members (Member id, Name, Designation, Age)
Books (Bid, Btitle, Bauthor, Bpublisher, Bprice)
Reserves (Member id, Bid, Date)
Write the query in relational algebra for the following:
i Retrieve the id and name of the members who are older than 45 years.
List the title, author, publisher of the books reserved by assistant professors
iii. Find the name, designation, age of the members who have reserved the books that
cost more than Rs.500 and on 15th November 2018.
iv. Find the member id and the number of books reserved by cach member.
V.
Find the id, name of the member along with the book title, author of the books
published by Bright Publications. (5)

Q5. Explain the state transition diagram of a transaction. (3)

Q6. How are variable length attributes represented in a record? (2)

Q7. Describe Boyce-Codd and third normal form with an example. (5)
Q8. Describe the two methods of representing specialization in ER Model into schema with
an example. (3)
09. What is a database trigger and what is the difference between row trigger and statement
trigger? (2)

Q10. Write the SQL commands to create the relational tables for the following schema
diagram (assume appropriate data type and size) as shown in figure 1

DEPENDENTS EMP SERVICES DEPT

Eid dEid Eid Deptno

Dependent name Ename Deptno Dname

|Age Salary Date Join Dept head

Relationship |Age Date Releive Budget
Deptno
Mgrid

Figure 1 Schema Diagram

(5)
an example. (3)
Q11. Illustrate the usage of user defined exceptions in PL/SQL block with
Q12. Write the block nested loop join algorithm. (2)
the
Q13. Write the SQL queries for the following data retrieval requirements by referring
relational schema diagram given in Figure 1.
i. Display employee name and their manager names if employee age is more
than coresponding manager age.
ii. Retrieve Department name and name of employee heading the department.
iii. Retrieve the name of employee having more than two dependents.
iv. Find employee id and number of departments in which he has tendered his
service.

V. Display employee names who are working in department with name

'FINANCE'. (5)
Q14. Write a stored procedure -SERVICE INFO that take employee id as input and display
each
the name of departments in which serviced and number of months serviced in
department.
3)
Q15. Briefly describe NoSQL database categories with an example for each. (2)
 Type: DES

Q1.Consider the relation R(A, B, Cc, R, D, T) and F= {A’BC, A’R, C’DT, B->CD}
Check AB is Candidate keyor not, if not check A & B also.
ii Find the canonical cover of F
iii. Decompose R into 3NF relations

(5)
Q2. Consider the table Book (Book No, Book_title, author, type, pages, price, publisher_id)
Describe the query processing steps in executing the query to display the title and price of
the book written by the author Korth and price greater than 450.
(3)

Q3. Consider the index file and data file given below and answer the following.
What kind of index file structure is defined on the Branch data file.
ii. What changes are expected in the data file and index file when the records
<'Smalltown',105, John',650> and <'Redwood,333,'Lisa',789) are inserted into the
Branch file?
Index File
Data File
Branch(Branch_name,account_no,customer_name,amount)
Bighton
217 Gccn 750
Dowotown Bighton
Dowotown Joboson SOO
Mianus
Dowatown LLO Petcs on 600
Peccicidgc
215 Sroith 700
Redwood Mia ms
Round Hil Pecticidg L02 Hayes 400

Pecitidge 201 Williarns 900

218 Lyle 700
Pecicidg
Redwood 222 Lindsay 700
Rouod HilL 305 Tucocc 350

(2)
Q4. Consider the following relations for a database that keeps track of automobile sales in a
car dealership. (OPTION refers to some optional equipment installed inside the
automobile):
CAR(Serial no,Model, Manufacturer, Cost_Price)
OPTION(Serial no, Option name, Price)
SALE(Salesperson id,Serial ng,Date, Sale_price)
SALESPERSON(Salesperson id,Name,Phone)
Write the following queries in relational algebra:
i. Display the number of cars under each manufacturer.
i. Display the details of the car sold on 02-06-2022.
iii. Display the details of salesperson who sold the cars with selling price greater
than 2 lakhs.
iv Display the details of options that comes along with the car 'SWIFT' and "I20.
 Displaythe details of salesperson selling cars manufactured by FORD (6

O5, DiscuSS the role of storage manager in the Database Management System environnens
(3)
cot
Q6. Convert the following schedule into serial schedule and check whether it is
serializable?

|T1 T2
Read(A)
Read(B)
A-A+B
Read(B)
Read(C)
B=B+C
Write(A)
Write(B)
Re ad(A)
Read(A)
A=A+1000
Write(A)
Read( C)
C=C-1000
Write(C)
(2)
constraints used in an
Q7. Describe the mapping cardinality constraints and participation
Entity Relationship model. (5)

Q8. Describe Brewer's CAP Theorem. (3)

properties: r1 has 10,000
Q9. Consider the relations rl(A, B, C) and r2(c, D, E) with following
tuples of r2 fit in
tuples and r2 has 9,000 tuples. 25 tuples of r1 can fit in one block, and 30
one block. Assume that nested-loop join strategy is applied for r1 ba r2. Estimate the
number of block accesses required in Best and worst cases. (2)
Q10. Write the SQL commands to create the relational tables for the following schema
diagram (assume appropriate data type and size) -
CakeFlavours
CakeHouse CakeHouse Flavours
houseno itemcode
houseno
Pincode itemcode ItemName
Qty available Price
City
Ratings

Customer CakeOrder
CustNo Orderld
|CName OrderDate
Phone CustNo
Pincode Itemcode
lcity lotvordred
(5)
Q11. Write a stored procedure (Refer the following schema diagram.) to display quantity
wise three highest selling item names in the month JUNE.
Cakeflavours
CakeHouse CakeHouse Flavours
itemcode
houseno houseno
+itemName
Pincode itemcode
Price
City Qty_available
Ratings

Customer CakeOrder

CustNo
Orderld
CName OrderDate
Phone CustNo
itemcode
Pincode
City QtyOrdred
Bonus Points Earned BillAmount

(3)
Q12. Describe cursor attributes with an example. (2)
retrieval requirements by referring the
Q13. Write the SQL queries for the following data
relational schema diagram given below

CakeHouse Flavours CakeFlavours

CakeHouse
Hhouseno
itemcode
houseno
itemcode +ItemName
Pincode
Qty_available Price
City
Ratings

Customer
CakeOrder

CustNo Orderld
CName OrderDate
Phone CustNo
|Itemcode
Pincode
City QtyOrdred
Bonus Points Earned BillAmount

500/- are
Find the Houseno in which cake items with price more than
available.
 Give 10 additional bonus points to customers who billed order for more thax
1000/- during CakeHouse anniversary week 06/06/2022 to 13/06/2022.
i Retrieve allthe Cake HouseNo and available ItemNames in the
location(pincode) where Customer with Name 'Ajay' lives.
Display the item name which is sold in quantity less than 30 during the 1 half
year of 2022.
Retrieve number of CakeHouse which are having rating 4 to 5 and existing in
the city in which Customer -Ravi lives. (5)

Q14. Write astored function (Refer Relational schema diagram given below) to accept
Custld, BillAmount, Bonus Points to be surrendered as parameters and calculates eligibie
discount amount based on following criteria and return to calling PL/SQL block

CakeHouse CakeHouse Flavours CakeFlavours

houseno housen0 itemcode
Pincode itemcode +ItemName
aty_available Price
City
Ratings

Customer CakeOrder
CustNo Orderld
CName OrderDate
Phone lCustNo

Pincode |Itemcode
City |Qtyordred
Bonus Points Earned BillAmount

Bonus <50 0%- discount of BillAmount.

BillAmount.
Bonus 50-100 10% Discount of
Bonus >100 15% Discount of BillAmount.
Earned then raise the
If Bonus Points to be surrendered is more than Bonus Points
application error. (3)
O15. Describe briefly the ACID properties of the database system. (2)
 Type: MCa

holds?
Q1. Consider the relation instance given and determine whether AB->C,C’B
B
-|:
| a1 b1 | c1
| a1 bl | c2
a2 b1 | c1
a2 | b1 | c3

(0.5)
1. ** NO,YES
2. YES,YES
3. YES, NO
4. NO,NO

Q2. How relation schema is created for a multivalued attribute 'A' in Entity E1 having 1-M
relationship with Entity E2? (0.5)
1. Primary Key(E1)U Primary Key(E2)
** Primary Key(E1) UA
3. Primary Key(E1) - Foreign Key(E1) nA
4. Primary Key(E1)U Foreign Key(E1) - A
Q3. Consider the relation Telephone (Custld, STD_Code, City, STD_Region) and Set of
functional dependencies
F= (Custld ’ STD_Code, STD_Code -City, STD_Code STD_Region).
What is the closure of STD Code? (0.5)
1. (Custld, STD_Code, City, STD_Region)
2. {STD_Code, City)
3. (Custld, STD_Code,City)
4. **STD_Code, City, STD_Region)

Q4. Consider the relation R(A, B, C, D, E),functional dependence F={ABC-ABCDE,

AC-ABCDE, AB-ABCDE, A-ABD, B-ABCDE C-BCD) and following statements
(Ö). ABC is candidate key
(iü). AC is minimal super key
(i). AB is Super key only
(iv). AB is minimal Super key
Select the correct option given below. (0.5)
1. ** (iü) and (ii) TRUE
2. (Ö) and (i) TRUE
3. () and (ii) TRUE
4 (ü) and (iv) TRUE
 PL/SQL stored procedures?
OS Which of the following statements are true about
IN
(0). The default mode of parameter passing is
calling statement s.
(in), Values passed as parameters to aprocedure as arguments in a
termed formal parameters.
called actual parameters
(ii). The parameters in aprocedure declaration are not copied harl..
values are
(iv). If a procedure raises an exception, the formal parameter
their corresponding actual parameters
(0.5)
1. (ü) and (i)
2. (i) and (ii)
3. ** () and (iv)
4. (ii) and (iv)
for R={A,B,C)?
Q6. Which of the folowing is a trivial functional dependency
(0.5)
1 ** ABC-’AB

2. A’B

3. BC BA

4. B->C
EMP(EmpNo,Ename,Salary, DeptNo), (Empno, Name)’Deptno, but DeptNOD
Q7. In the relation dependency is known as
dependency.
only dependent on EmpNo.Such functional

(0.5)
1. ** Partial
2. Fully Functional

3. Transitive

4. Formal

the below ER Diagram:

08. How many relation schemas can be formed from

DOCTORS PAIENIS

Repol
Q aliuo

ESIS

(0.5)
1, **7
 2. 6
3. 5

4. 8
information about the most recently run
attributes that return
cursor has cursor. (0.5)
09. An implicit associated with anamed
statement that is not
1. ** SELECT

2. CREATE

3. DROP

4. ALTER

LAST DAY('12/O5/2022") FROM DUAL 0s; (0.5)

Q10. The output of SELECT
1. ** Error

2. 31-MAY-22

3. 31-05-22

4. 30-MAY-22

Type: DES

Q11.
.(2)

Q12.
.(2)

Q13.

.(3)

Q14.

.(3)
 Database System

Type: MCQ

Q1. Assume that we want to develop a multiplex show seat reservation system. In order to provide
minimum response time which kind of processing system is suitable? (0.5)
1. **Database System
2. File-oriented

3. CApplication with seat data files

4. Seat information stored in Excel file

Q2. A customers residential address has been changed and communicated to the ABC Bank.Now all
the communication from the bank will reach to the new address because of feature of
database System (0.5)
1 **Data Consistency
2. Data Abstraction

3. Data lsolation

4. Data Independence
Q3. In an airline DBMS we could have a situation where two travel agents sell the same airline seat
at the same time and the total number of bookings made is larger than the capacity of the
aircraft.The feature of the database system provides solution for the above problem. (0.5)

1 Data Security
2. **Data Integrity
3. Data Durability
4. Data Consistency

Q4. Which one of the following is correct? (0.5)

1. Each candidate key is a primary key
2. Each primary key is aforeign key
3 ** Each foreign key is a primary key in some relation
4. Each foreign key a candidate key

O5. Consider the following Student database:

Student(|D, Name,City,tutor)
Course(Code,title, department)
Enrolment(|D, Code, Marks)
Which one of the following is correct?(0.5)
1. ID and Code in Enrolment are both candidate keys
2. (ID,tutor) in Student is a candidate key
 composite key
3. ** (ID, Code) in Enrolment is
key in Course
4. (Code,title) is a super
Q6. Which one of the following does not always have the same list of attributes that the operands

have? (0.5)
1. **Project
2. Union

3. Select

4. Difference

Q7. Consider the tables

Vendor (V code,V_name,V_areacode,V_phone,V_ state,V_order)

Product(P code,P_description, P_indate,P_Q0H,P_Unit,P_price,P_discount,V_code)
In the relation product, V_code is the foreign key which refers to V_code of Vendor
Choose the correct query in SQL to display the information of the vendor supplying the product with
highest discount. (0.5)
1. select v_name from vendor where v code =(select v code from product where
P_discount=(select max(p_discount) from product));
2. ** select v_ name from vendor where v code in(select v code from product
where
P_discount=(select max(p_discount) from product));
3. select v_name from vendor where v code in(select v code
from product where
P_discount in (select max(p_discount) from product)):
4. select v_name from vendor where
v_code (select v code from
p_discount in(select max(p_discount) from product);None of the above product wnere
Q8. Which of the following is true about SQL? (0.5)
1. SQL automatically eliminates
duplicate rows from results
2. SQL automatically sorts the rows
from results
3. SQL does not allow any join
other than a natural join
4. ** SQL does not provide
commands to compute all relational aleebra operators direciy

09. Which of this is

the correct
command for inserting
book(bookno,booktitle, price, pages), if the defaut value 100 is to be values in the ta
pages?(0.5) assigned to the colu
1. insert into book
2. ** insert into values(101,'MYSQL'A50.00);
3. insert into book(bookno,booktitle,price) values(101,'MYSQL',450.00);
4.
book(bookno,price, ,booktitle)values(101,' MYSQL',450.00);
insert into book
values(101,'MYSQL' 450.00,100);
 the custno
customer(custno,custname,place) has been created with custno int(5).If
010. The table appropriate? (0 5)
be changed to store alphanumeric values of length 15, which command is
has to
modify custno char(15):
1. ** ALTER TABLE customer
custno char(15):
2. ALTERTABLE customer change
customer add custno char(15):
3. ALTER TABLE
drop custno char(15);
4. ALTERTABLE customer

Type: DES
.(2)
functionalities of DBA.
Q11. Write any four

constraint and suitable constraint

the SQL statements to create the following tables with
Q12. Write
name

Flights (flno, from , to,distance)

Aircraft (aid, aname, cruising_range )
Certified (eid, aid)
eid is unique
Employees (eid, ename, salary )
Attributes underlined are primary keys constraints.
linked by Primary/Foreign key
Attributes with same column names are .(2)
to 100000 km
Crusing range must be 5000 km

supply some parts and cost of the

relations containing suppliers who
Q13. Consider the following
parts supplied.
some parts
Suppliers(sid, sname, address) - Suppliers information who supply
- Part details
Parts(pid, pname, color)
part & cost of the part
Sales (sid, pid, cost) - Which supplier supplied which
expressions for the following queries.
Write the Relational algebraic
supply some red part.
a. Find the names of suppliers who
fora part than the
supplier with the first sid charges more
b. Find pairs of sids such that the
supplier with the second sid.
Tiger Circle. .(3)
some red part or are his office is at
C. Find the sids of suppliers who supply

containing airline flight information

Q14. Consider the following relations
Flights (flightno, from, to, distance)
 Aircraft (aircraftid, aname, cruising_range )
Certified (eid, aircraftid)
Employees (eid,ename, salary )
Attributes underlined are primary keys
Attributes with same column names are linked by Primary/Foreign key constraints.
a. Find the aids of all aircraft that can be used on
distance) flights from Bangalore to Mumbai. non-stop(Cruse range must be more than ui
b. Find the names of pilotswho can
miles but are not certified on any operate
Boeing
planes with a range greater than 5,000
aircraft.
C. Find the eids of
employees who make the salary same
allthe employees flying Jet01 as that of maximum salary drawn by
flights .(3)