Part – A (5*2=10)

1 Illustrate how database management System differ from File processing System

2 Define candidate key, primary key and foreign key.

3 What is TCL commands? Give example

4 Define is E-R Model?

5 Define No Partial Dependency.

6 Define weak and strong entity sets?

7 What are the different Constraints supported by SQL?

8 Define Multi valued Dependency with example.

9 Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional

dependencies hold: {A–>B, BC–>D, E–>C, D–>A}. What are the candidate keys of R?
10 Consider the following relation :
EMP (ENO, NAME, DATE_OF_BIRTH, SEX, DATE_OF_JOINING, BASIC_PAY,
DEPT) Develop an SQL query that will find and display the average BASIC_PAY in each
DEPT.
11 What is a database management System?

12 Distinguish primary key and foreign key.

13 Define DML commands.

14 What are the drawbacks of Hierarchical data model?

15 List the anomalies of 1NF.

16
Define Database Management System.
17
Rewrite what is schema and instance.
18
Reproduce the select and project operator in Relational Algebra.
19 Write a SQL query to find all the instructors from the Information Technology Department.

20 Define Normalization.

Part – B (2*13 = 26)

1. a Discuss in detail about the architecture of database management system

)
OR

K1 – Remember; K2 – Understand; K3 – Apply; K4 – Analyze; K5 – Evaluate; K6 - Create

b Draw an E-R Diagram for Online bookstore.
) i)List the entity sets and their primary keys.
ii)Suppose the bookstore adds music cassettes and compact disks to its collection.The
same music item may be present in cassette or compact disk format,with differing
prices.extend the e-R diagram to model this addition,ignoring the effect on shopping
baskets.
iii)Extend the E-R diagram,using generalization,to model the case where a shopping
basket may contain any combination of books,music cassettes or compact disks
2. a Write SQL query for the following condition for the given table
) Employee(empname,empno,salary,desgn)
(i) How to retrieve a second highest salary from a table?
(ii) Query to find the list of employees age > 30
(iii) Find the Latest Salary of each Employee in Employee table.
(iv) To display all employee details.
OR
b Consider the relational database
) employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)
Give an expression in the relational algebra to express each of the
following queries:

a. Find the names of all employees who work for First Bank Corporation.
b. Find the names and cities of residence of all employees who work for First Bank
Corporation.
c. Find the names, street address, and cities of residence of all employees who work for
First Bank Corporation and earn more than $10,000 per annum.
d. Find the names of all employees in this database who live in the same city as the
company for which they work.

3 a) i. Design an E-R diagram for keeping track of the exploits of your favorite sports team.
You should store the matches played, the scores in each match, the players in each match
and individual player statistics for each match. Summary statistics should be modeled as
derived attributes
ii. Write short notes on mapping cardinality
(OR)

b) Explain in detail about DBMS Architecture with neat diagram

4 a) Explain the select, project, Cartesian product and join operations in relational Algebra
with an example.

(OR)

b) Construct SQL queries, Consider the relations Country (name, continent, population,
GDP, life‐expectancy) River (name, origin, length) City (name, country, population)
GDP and population in million.
i. Find all countries whose GDP is greater than $400 Billion but less than $1
K1 – Remember; K2 – Understand; K3 – Apply; K4 – Analyze; K5 – Evaluate; K6 - Create
trillion.
ii. List the life expectancy in countries that have river originating in them.
iii. Find all cities that are either in china or whose population is less than 2
million.
iv. List all cities which are not in India.
5. a) Explain different types of data models with suitable examples

(OR)

b) Construct an E-R diagram for a car insurance company whose customers own one or
more cars each. Each car has associated with it zero to any number of recorded accidents.
Each insurance policy covers one or more cars, and has one or more premium payments
associated with it. Each payment is for a particular period of time and has an associated
due date, and the date when the payment was received?
6 a) Explain in detail about SQL fundamentals

(OR)

b)
Assume the following table. Degree (degcode, name, subject)

Candidate (seatno, degcode, semester, month, year, result) Marks (seatno, degcode,
semester, month, year, papcode, marks) Degcode-degree code, Name-name of the degree
(MSc, MCOM) Subject-subject of the course. E.g. Phy, Papcode- Paper Code E.g. A1
Solve the following queries using SQL:

(i) Write a SELECT statement to display all the degree codes which are there in the
candidate table but not present in the degree table in the order of degcode.

(ii) Write a SELECT statement to display the name of all the candidates who have got
less than 40 marks in exactly 2 subjects.

(iii)Write a SELECT statement to display the name, subject and number of candidates for
all degrees in which there are less than 5 candidates.

(iv) Write a SELECT statement to display the names of all the candidates who have got
highest total marks in MSc.,(Maths)

7 a) i. Detail the disadvantages of File Processing System over Database Management

System.
ii. Explain in detail the database System Architecture and Application Architecture
(OR)

b) Draw the E_R diagram for the E-commerce DBMS.  

       i)List the entity sets and their primary keys.
       ii)Extend the e-R diagram to model this addition and deletion of products in cart.
    iii)Extend the E-R diagram using generalization to model the case where a shopping
basket may contain any combination of products. 

8 a) Write SQL query for the following condition for the given table
Student(stu_id,stu_name,semester,year,cgpa) 
K1 – Remember; K2 – Understand; K3 – Apply; K4 – Analyze; K5 – Evaluate; K6 - Create
i. How to get the rank of the students from a table?  
ii. Query to find the list of students studying in II year.
iii. Query to list the students whose CGPA is greater than 8.
iv. Consider the query:

with dept total (dept_name, value) as

(select dept_name, sum(salary)
from instructor group by dept_name,
dept_total_avg(value)
as (select avg(value) from dept_total) select dept_name
from dept total, dept_total_avg
where dept_total.value >= dept_total_avg.value;
Rewrite this query without using the with construct.

(OR)

b) i. What is the result of first performing the Cartesian product of student and advisor, and
then performing a selection operation on the result with the predicate s id = ID? (Using
the symbolic notation of relational algebra, this query can be written as σs id=ID(student
× advisor).).

ii. Consider the employee database. Give an expression in the relational algebra to
express each of the following queries:
a. Find the name of each employee who lives in city “Miami”.
b. Find the name of each employee whose salary is greater than $100000.
c. Find the name of each employee who lives in “Miami” and whose salary is greater
than $100000
Part –C (1*14=14)

1. a) i)Explain in detail about normalization, types and why normalization is required?

ii)Consider the following relation
CAR_SALE(car#,date_sold,salesman#,commission, discount-amt)
Assume that a car may be sold by multiple salesman, and hence
(car#,salesman#) is the primary key. Additional dependencies are
Date-sold->discount-amt
Salesman#v-> commission
Based on the primary key,is the relation in 1NF and 2NF?                                      

2. a) Define Database Normalization. Explain First Normal Form & Second Normal Form
with Suitable Example.

(OR)

b)
i. Define Decomposition & Explain in detail
ii. Explain About Functional Dependency in details.
3. a) Explain in detail about normalization and its types.

(OR)

K1 – Remember; K2 – Understand; K3 – Apply; K4 – Analyze; K5 – Evaluate; K6 - Create

b) i)Describe functional dependencies in detail
ii) Given a relation R( A, B, C, D) and Functional Dependency set FD = { AB → CD, B
→ C }, determine whether the given R is in 2NF? If not, convert it into 2 NF.

4. a) Explain in detail about normalization. Consider the following relation

CAR_SALE (car#, date_sold, salesman#, commission,discount_amt)
Assume that a car may be sold by multiple salesman, and hence
(car#,salesman#) is the primary key. Additional dependencies are
Date-sold->discount-amt
Salesman#v-> commission
Based on the primary key,is the relation in 1NF and 2NF?                                              

(OR)

b)
Explain in detail about normalization, types and why normalization
is required with an example?

K1 – Remember; K2 – Understand; K3 – Apply; K4 – Analyze; K5 – Evaluate; K6 - Create