Model Question DBMS

MODEL/PRACTICE QUESTIONS
Discuss the main characteristics of the database approach and specify how it differs from traditional
file system.
Explain about different types of integrity constraints?
Draw and Explain in detail about the three-tier schema architecture of DBMS.
Explain entity, attributes and relationship, its types and symbols with example.
Who gave the 12 rules for relational database? Explain all rule with example.
What is Relational algebra in DBMS. Explain with example and its application also.
a) Draw transaction state diagram and describe each state that a transaction goes through during its
execution.
Explain ACID properties and illustrate them through examples?
Normalization provides normal forms? Explain about various normal forms with examples. Describe
lossless join decomposition.
Normalize following relation up to 3NF:
Bank(acno, cust_name, ac_type, bal, int_rate, cust_city, branchId, branch_nm, br_city)
Define Primary key, Foreign key and Composite Key. Give one example.
List and brief four aggregate functions.
Define normalisation. Explain its uses.
Define transaction. Draw the transaction state diagram. Explain briefly four codd’s rule in DBMS.
Describe ACID property of transactions.
Discuss the role of DBA.
Draw a neat diagram for elucidating the process of Query Processing and Optimization.
Explain three schema architecture in DBMS with the help of suitable diagram.
What are different database schema languages and interfaces. Name two command used in each
database language.
Define concurrency control technique. Discuss the main problems with concurrency control
mechanism. Briefly explain the concurrency control techniques.
Define 2NF. Given a relation R (P, Q, R, S, T) and Functional Dependency set FD = {PQ →
R, S → T }, determine whether the given R is in 2NF? If not convert it into 2 NF. Identify
candidate key by finding closure of PQS+.
Consider the following relational database schema consisting of the four relation schemas:
passenger ( pid, pname, pgender, pcity)
agency ( aid, aname, acity)
flight (fid, fdate, time, src, dest)
booking (pid, aid, fid, fdate)
Answer the following questions using relational algebra queries;
a) Get the complete details of all flights to New Delhi.
b) Get the details about all flights from Chennai to New Delhi.
c) Find the passenger names for passengers who have bookings on at least one flight.
Draw ER diagram for Hospital Management System
(Entity: DOCTOR, PATIENT, HOSPITAL and MEDICAL_RECORD). Consider the attributes for each entity
appropriately. Identify Primary Key and Foreign Key.
Differentiate among Relational model and Hierarchical model.

What is Data model? Draw three categories of data model.
State four advantages of DBMS over file processing system.
Define Normalization and state three advantages of normalization. Differentiate between 3NF and
BCNF.
What is 2-phase locking protocol? How does it guarantee serializability?
Write syntax for Create, Alter, Drop and Rename table commands.
“Entity relationship is composed of entity, attributes and relationship”. Explain entity, attributes and
relationship, its types and symbols with example.
“Boycee-codd normal form is found to be the stricter than 3NF”. Justify the statement with example.
What is transaction? Explain ACID properties of a transaction.
Explain in detail about timestamp-based concurrency control techniques with example.
Define Multi-valued dependencies. Explain the 4 NF with example.
Codd’s rule
What is Data independence and how does DBMS support it?
Can a foreign key value in a database be NULL or duplicate? Justify your answer.
Explain any four significant advantages of using DBMS over storing conventional file system for
maintaining data in an organization.
List and explain various data models used for database design.
What is Entity set? Also define Relationship set. List and explain the symbols used to draw ER
Diagram.
What are the problems related to decompositions? Explain.
Explain Deadlock and also list three basic techniques to control deadlock with example.
Define Normalization. What are the steps in normalization? What are the advantages of normalized
relation over un normalized relation?
Draw ER diagram for Library Management System.
Explain 3NF & BCNF. What is the difference between them?
What is functional dependency? Explain its usage in database design.
List and explain various issues while transactions are running concurrently in DBMS.
Why Time stamp based concurrency control allows schedule that are not recoverable. Explain and
describe how it can be modified through buffering to disallow these schedules
Explain Concurrency control with locking methods.
Draw an ER diagram of Bank database with 5 entities and 5 attribute for each entity. Specify the
cardinality ratio on each of the relationships existing between entities.
Explain different states of transaction with a neat diagram.
Explain with example:
i) Key constraints ii) Integrity constraints.
Database backup and Database recovery

Concurrency Control and Serializability
Data Security and Data recovery
Data and information and Knowledge
Candidate Key and Foreign Key
3NF AND 4NF
where clause and group by clause
Logical Independence and Physical Independence
Data Consistency and Data Redundancy
File System Approach and DBMS Approach
Distinguish strong entity set with weak entity set? Draw an ER diagram to illustrate weak entity set?
a) Why concurrency control is needed? Explain the problems that would arise when concurrency
control is not provided by the database system.
b) What is serialization? Explain it.
Create an employee table using the following fields
Field Data type

name
EMPNO NUMBER
ENAME CHAR
DOB DATE
DEPT STRING
SALARY REAL
A. Create table.
B. Insert 5 tuples.
C. Find the sum of salaries.
D. Find the department wise count of Employees.
E. Display the tuples in the order of average salaries of Employees.
Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes. R1 and R2
are two relationships between E1 and E2, where R1 is one1 to-many and R2 is many-to-many. R1
and R2 do not have any attributes of their own. Calculate the minimum number of tables required
to represent this situation in the relational model?
Construct an ER diagram for university registrar’s office. The office maintains data about each class,
including the instructor, the enrollment and the time and place of the class meetings. For each student
class pair a grade is recorded. Determine the entities and relationships
Consider the following relational schemes for a library database:

Book (Title, Author, Catalog_no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no)
the following are functional dependencies:
a. Title Author --> Catalog_no
b. Catalog_no --> Title Author Publisher Year
c. Publisher Title Year --> Price
d. Assume {Author, Title} is the key for both schemes.
Apply the appropriate normal form for Book and Cancellation?
Write SQL query for following consider table stud (rollno, name, sub1, sub2, sub3)
i) Display name of student who got minimum marks in sub1
ii) Display name of student who obtained highest marks in sub3
iii) Display number of students failed in sub2
iv) Find total marks of sub1 of all student
Write SQL queries for following

i) Create table EMP with following attributes using suitable data types (Eno, Ename, Deptname,
Salary, designation, Joining_ Date)
ii) Display names of employee whose name start with alphabet ‘A’
iii) Display names of employee who joined before ‘1/1/2000’
iv) Increase the salary of employees by 20% who joined after ‘1/1/2005’.
Write SQL query for following consider table
EMP (empno, deptno, ename, salary, Designation, joining date, DOB, city)
i) Display employees name and number in an increasing order of salary
ii) Display employee name and employee number dept wise
iii) Display total salary of all employee
iv) Display number of employees dept wise
v) Display employee name having experience more than 3 years
vi) Display employee name staring with “S” and working in deptno 1002.
What is 2-phase locking protocol? How does it guarantee serializability?

What is Data Independence? Why is it essential?
What is normalization? Explain its need.
Discuss in detail about various normal forms.

Model Question DBMS

Uploaded by

Copyright:

Available Formats

Model Question DBMS

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Model Question DBMS

Uploaded by

Copyright:

Available Formats

MODEL/PRACTICE QUESTIONS

List and brief four aggregate functions.

Define normalisation. Explain its uses.

Describe ACID property of transactions.

Discuss the role of DBA.

Differentiate among Relational model and Hierarchical model.

Database backup and Database recovery

Field Data type

Consider the following relational schemes for a library database:

Write SQL queries for following

What is 2-phase locking protocol? How does it guarantee serializability?

You might also like