DAYANANDA SAGAR ACADEMY OF TECHNOLOGY AND MANAGEMENT

UDAYAPURA, KANAKAPURA ROAD, BANGALORE-82

DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

Subject : DATABASE MANAGEMENT SYSTEMS Sub Code : 18CS53

Faculty: Manjunath D R Sem & Sec : V Sem “B”

Course outcome
CO1: Identify, analyze and define database objects, enforce integrity constraints on a
database using RDBMS.
CO2: Construct an Entity-Relationship (E-R) model from specifications
CO3: Understanding of Relational algebra and is operations.
CO3: Use Structured Query Language (SQL) to develop queries for relational database.
CO4: Design and build simple database systems
CO5: Develop application to interact with databases.

ASSIGNMENT

1. A) Compare DBMS and early file systems , bringing out the major advantages of the database
approach. (06 Marks, Jan 2020)
B) Define the following with examples:
a) Entity and Attribute b. Entity type and entity set c. String and weak entity d. recursive relationship and
identifying relationship. (04 Marks, June 2016)
2. Explain three-schema architecture with neat diagram. Why do we need mappings among schema levels?
How do different database schema definitions language support this architecture? (08 Marks jan 2018,july
2018,jan 2019)
3. Discuss the various component modules of a DBMS with a neat diagram.(08 M, Dec 2012,july 2018,jan
2020)
4. A) Define the following terms: (6 Marks, Dec 2014, Jan 2020)
i) Database ii) Canned Transactions iii) Data model iv) Meta data v) Database designer.
B) Define the following terms : i) Key ii)Super key iii) Candidate key iv) Primary key v) Foreign key.
(05 Marks Jan 2020)
5. Explain with block diagram the different phases of database design. .(08 M, Jan 2019)
6. A) Explain the different types of attributes that occur in an ER model, with an example.(6M july 2018)
B) Define the following with an example: (10 Marks, June 2010)
i) Weak entity type
ii)Participation constraints
iii)Cardinality ratio
iV)Ternary relationship
7. Draw an ER diagram to represent the Election Information System based on the following . description: (12
Marks,jan 2020)
In the Indian national election, a state is divided into a number of constituencies depending upon the
population of the state. Several candidates contest elections in each constituency. Candidates may be from
some party or independent. The election information system must record the number of votes obtained by
each candidate. The system also maintains the voter list and a voter normally belongs to a particular
constituency. Note that the party details must also be taken care in the design.
8. Briefly discuss the different types update operations on relational database. Show an example of a
violation of the referential integrity on each of the update operations. (08 Marks, Dec 2012, 06 Marks
2016,Jan 2018,jun 2018)
9. A)Discuss the characterization of a relation, with example. (06 Marks, Dec 2014,jan 2018,jan 2019)
B) Define the following: (4M Jun 2018)
i) Relation state
ii) Relation schema
iii) Arity
iv) Domain
10. Describe the steps of an algorithm ER-to-relational mapping .(08 marks jan 2018,jan 2019,2020)
11. A) Explain Different data types available for attribute specification in SQL.(4M june 2018)
B) In SQL which command is used for table creation? Explain how constraints are specified in SQL during
table creation with example. (08 marks Jan 2018,2020 jun 2018)
12. A) Explain the Single - tier and Client - server architecture, with neat diagram.( 06 Marks jan 2018)
B) Give an example of mapping of generalization or specialization into relation schemas. (06 Marks jun
2018)
13. Considering the schema (07 marks Jan 2020)
Sailors (sid , sname , rating , age)
Boats (bid , bname , color)
Reserves (sid , bid , day) m =
Write relational algebraic queries for the following :
i)Find names of sailors who have reserved boat # 103.
ii)Find names of sailors who have reserved a red boat.
iii) Find names of sailors who have reserved a red or green boat.
iv) Find names of sailors who have reserved all boats.

14.
15. Explain with examples , the basic constraints that can be specified when a database table is created in
SQL. (12 Marks Jan 2020)
16. Explain with examples in SQL: (8M jan 2019)
i)Alter Command
ii) Drop command
ii) Delete command
iii) Update command.
17. Discuss how each of the following constructs is used in SQL and discuss the various options for each
construct: (10Marks Jan 2019)
i)Nested Queries ii) Aggregate Functions iii) Schema Change Statements iV) Group BY and having clause
V)Inner Joins VI) outer Joins
18. Consider the following Schema:
Emp(name, id, age, salary)
Works_for(pid,eid, #hrs)
Proj(pid, name)
Write the relational algebra for the following?
i. Retrieve employee name and employee id who works for all the project.
ii. Retrieve employee name and age whose salary > 1000
iii. For each employee, get the number of projects and number of hours worked on projects
iv. Retrieve the employee name who is working for “CSE” project
(08 Marks, June 2016)
19. A) What is Dynamic SQL and how it is different from Embeded SQL.(4Marks jun 2018)
B) What is SQLJ and how it is different from JDBC. (4M Jun 2018)
20. A)With program segment, explain retrieving of tuples with embedded SQL in C.(6 Marks jan 2019)
b )What are views in SQL? Show how views are specified in SQL. List the advantages of views. (06
Marks, June 2016,jan 2020)
21. A ) What are assertions and triggers in SQL? Write a SQL program to create an assertion to specify the
constraint that the salary of an employee must not be greater than the salary of the department. The employee
works for in the COMPANY database. (05 Marks , jan 2020)
B) Write a trigger in SQL to call a stored procedure INFORM_SUPERVISOR( ) whenever a new record is
inserted or updated, check whether an employee's salary is greater than the salary of his or her direct supervisor
in the COMPANY database. (05 Marks , jan 2020)

22. Explain the informal design guidelines for relation schemes. (08 M, jan 2018 ,June 2015, 06 M, June
2016)
23. With real world examples, explain the following : i) JDBC ii)Correlated queries
iii)Stored Procedure iv) Schema change statements in SQL. (12 Marks , jan 2020)
24. A) Discuss insertion, deletion and modification anamolies.Why are they considered bad? Illustrate with
Examples (4 Marks jun 2018,jan 2020)
B) What do you mean by closure of attribute? Write an algorithm to find closure of attribute.
(06 Marks Jan 2019)
25. What is the need for normalization? Explain second normal form. Consider the relation EMP-PROJ={ SSn,
Pnumber,Hours,Ename,Pname,Plocation}. Assume {SSn,Pnumber} as primary key. The dependencies are
SSn Pnumber → {Hours}
SSn → {Ename}
Pnumber → {Pname,Plocation}
Normalize the above relation into 2NF. (10 Marks, June 2010)
26. Given below are two sets of FDs for a relation R (A, B, C, D, E). Are they equivalent?
i) A B , AB C , D AC , D  E
ii) A  BC , D AE (06 Marks)
27. Normalize the below relation upto 3NF : (10 M, jan 2020)
Module Dept Lecturer Text

M1 D1 L1 T1
M1 D1 L1 T2
M2 D1 L1 T1
M2 D1 L1 T3
M3 D1 L1 T4
M4 D1 L2 T1
M4 D2 L3 T5
M5 D2 L4 T6
28. Define Multi valued Dependency and Join Dependency. Explain 4NF and 5NF with
examples. (10 M, jan 2020)
29. A) Discuss the ACID properties of a transaction. (04 Marks Jan 2019,jun 2019)
B) What are the anomalies occur due to interleave execution? Explain them with example.(6 Marks jan
2019)
30. Consider the three transactions T1, T2 and T3 and schedules S1 and S2 given below.
Determine whether each schedule is serializable or not? If a schedule is serializable write
down the equivalent serial schedule (S). (6 Marks jan 2019)
T1 : R1(x); R1(z); W1(x);
T2 : R2(x); R2(y); W2(z); W2(y);
T3 : R3(x); R3(y); W3(y);
S1 : R1(x); R2(z); R1(z); R3(x); R3(y); W1(x); W3(y); R2(y); W2(z); W2(y);
S2 : R1(x); R2(z); R3(x); R1(z); R2(y); R3(y); W1(x); W2(z); W3(y); W2(y);
31. A) Describe the problems that occur when concurrent execution uncontrolled. Give examples.(06 Marks
Jan 2019)
B) What is two phase locking? Describe with the help of an example. (04 Marks jan 20192, Dec 2008,jun
2018)
32. What is Deadlock? Consider the following sequences of actions listed in the order they are
submitted to the DBMS.
Sequence S1: R1(A); W2(B); R1(B); R3(C); W2(C); W4(B); W3(A)
Draw waits-for graph in case of Deadlock situation. (06 Marks, Jan 2019)
33. What is serializability? How can serializability be ensured? Do you need to restrict concurrent execution of
transaction to ensure serializability? Justify your answer,(6Marks jun 2018)
34. Write short notes on:(16 Marks, Dec 2008,jan 2018,jun 2018,jan 2019,2020)
I) Transaction support in SQL II) Write ahead log protocol iii)Time stamp ordering algorithm
35. Consider the two tables, show the results of the following: (8M jan 2019)
36. Consider the following tables(6 Marks jun 2018)