Database Management System

Which SQL command is used to remove a table from a database?
(i) REMOVE
(ii) DELETE
(iii) DROP
(iv) CLEAR

For relation: R(A,B,C); FDs={A $\rightarrow$ B, B $\rightarrow$ A}. Which statement is true?

(i) A and B both are candidate keys
(ii) Only A is key
(iii) Only B is key
(iv) Neither A nor B is key

A relational database developer refers to a record as
(i) a criterion
(ii) a relation
(iii) a tuple
(iv) an attribute

The relational algebra expression $\pi_A(R) - \pi_A((\pi_A(R) \times S) - R)$ is equivalent to:

(i) Selection
(ii) Join
(iii) Division
(iv) Projection

Which query is best solved using division operation?

(i) Find students enrolled in DBMS
(ii) Find students enrolled in all courses
(iii) Find students with marks >80
(iv) Find students from CSE

The term _____ is used to refer to a row.
(i) attribute
(ii) tuple
(iii) field
(iv) instance

Which of the following is true?
(i) Every relation in 3NF is also in BCNF
(ii) A relation R is in 3NF if very non-prime attribute of R is fully functionally dependent on every key of R
(iii) Every relation in BCNF is also in 3NF
(iv) No relation can be in both BCNF and 3NF

Which of the following is true?
(i) a super-key is always a candidate key.
(ii) every 3NF schema is also a BCNF.
(iii) generalization is bottom-up approach.
(iv) none of these.

Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies {{E, F} → {G}, {F} → {I, J}, {E, H} → {K, L}, K → {M}, L → {N}} on R. What is the key for R?
(i) {E, F}
(ii) {E, F, H}
(iii) {E, F, H, K, L}
(iv) {E}

Which of the following are the five built-in functions provided by SQL?
(i) COUNT, SUM, AVG, MAX, MIN
(ii) SUM, AVG, MIN, MAX, MULT
(iii) SUM, AVG, MULT, DIV, MIN
(iv) SUM, AVG, MIN, MAX, NAME

What is the purpose of project operation:
(i) It selects certain columns.
(ii) It selects certain rows.
(iii) It selects certain strings.
(iv) It selects certain integers.

A relation is in BCNF if:

(i) Every non-prime attribute depends on key
(ii) Every FD is trivial
(iii) Every determinant is a superkey
(iv) Every attribute is atomic

Given the Students' relation as shown below:

StudentID	StudentName	StudentE-mail	StudentAge	CPI
2345	Shankar	shankar@math	X	9.4
1287	Swati	swati@ee	19	9.5
7853	Shankar	shankar@cse	19	9.4
9876	Swati	swati@mech	18	9.3
8765	Ganesh	ganesh@civil	19	8.7

For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to:
(i) 18
(ii) 19
(iii) 15
(iv) 20

Suppose relation R(A,B,C) is decomposed into: R1(A,B) and R2(B,C). The decomposition is lossless if:

(i) B $\rightarrow$ A or B $\rightarrow$ C
(ii) A $\rightarrow$ B
(iii) A $\rightarrow$ C
(iv) C $\rightarrow$ B

From the instance of a relation scheme R (A, B, C)

A	B	C
1	1	1
1	1	0
2	3	2
2	3	2

we can conclude that:
(i) A functionally determines B and B functionally determines C
(ii) A functionally determines B and B does not functionally determine C
(iii) B does not functionally determine C
(iv) A does not functionally determine B and B does not functionally determine C

Which normal form is considered adequate for normal relational database design?
(i) 2 NF
(ii) 5 NF
(iii) 4 NF
(iv) 3 NF

Which of the following is an example of a DML command in SQL?
(i) CREATE
(ii) SELECT
(iii) DROP
(iv) ALTER

To include integrity constraint in an existing relation use
(i) create table
(ii) modify table
(iii) alter table
(iv) drop table

Which of the following is not a super key in R(V, W, X, Y, Z) with primary key VY?
(i) VXYZ
(ii) VWXZ
(iii) VWXY
(iv) VWXYZ

Consider R (A, B, C, D, E) with following FDs:-
A → B, A → C, CD → E, B → D, E → A which of the following FDs is not implied by above set?
(i) CD → AC
(ii) BD → CD
(iii) BC → CD
(iv) AC → BC

2NF removes which kind of dependency?
(i) Transitive
(ii) Partial
(iii) Functional
(iv) Multivalued

What is attribute closure X⁺ of a set of attributes X with respect to a set of FDs F? Give the algorithm for commuting X⁺ for X.

Consider the relation R (P, Q, S, T, X, Y, Z, W) with the following functional dependencies.
PQ → X; P → YX; Q → Y; Y → ZW
Consider the decomposition of the relation R into the constituent relations according to the following two decomposition schemes.
D1 : R = [(P, Q, S, T); (P, T, X); (Q, Y); (Y, Z, W)]
D2 : R = [(P, Q, S); (T, X); (Q, Y); (Y, Z, W)]
Identify whether it is a lossless or lossy decomposition and justify your answer.

Relation R (ABCDEFGH) contains only atomic values for all of its attributes. F = {CH → G, A → BC, B → CFH, E → A, F → EG} is a set of functional dependencies (FDs) so that F is exactly the set of FDs that hold for R.
(i) Find all the candidate keys the relation R have?
(ii) Find the highest normal form with justification in which R exist?

Employee (ename, street, city)
Worksfor (ename, company_name, salary)
Company (Company_name, city)

Construct the SQL statements for the following
(i) Create the above given tables with suitable data types and size.
(ii) Find the name of employees who live in the same city where they work.
(iii) Find the name of employees who have salary more than Rs. 50000.
(iv) Find the name of employees who don't work in "tcs" company.
(v) Find all employees whose name has second letter 'A'.
(vi) Find the employee's name having second highest salary.

What is a view? Can we update a view? Justify your answer.

Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price), Collection (Title, Author, Catalog_no) with in the following functional dependencies:
I. Title Author → Catalog_no
II. Catalog_no → Title, Author, Publisher, Year
III. Publisher Title Year → Price

Analyze and find the highest normal form of the relation Book and Collection.

When we try to modify any table in a database system, we encounter some side-effects if the tables are insufficiently normalized. Can you explain those side-effects with the respective examples?

Consider the following schema for institute library:
Student (RollNo, Name, Father_Name, Branch)
Book (ISBN, Title, Author, Publisher)
Issue (RollNo, ISBN, Date-of-Issue)

Write the following queries in SQL and relational algebra:
(i) List roll number and name of all students of the branch 'CSE'.
(ii) Find the name of student who has issued a book published by 'ABC' publisher.
(iii) List title of all books and their authors issued to a student 'RAM'.
(iv) List title of all books issued on or before December 1, 2020.
(v) List all books published by publisher 'ABC'

What is a JOIN in SQL? Explain various JOIN operations with a suitable example for each and discuss their applications in database systems.

Explain the reasons for the update, insertion and deletion anomalies.

Consider:

• Student(SID, Name, Dept, Age)
• Course(CID, CName, Faculty)
• Enroll(SID, CID, Marks)

1. Write relational algebra expressions to find:
   • a) Students enrolled in DBMS
   • b) Students scoring >80 marks
   • c) Students not enrolled in any course
2. Write SQL queries:
   • a) Find highest marks
   • b) Find average marks department-wise
   • c) Display students enrolled in more than two courses
3. Translate the following query into tuple relational calculus:
   • "Find names of students from CSE department."
4. Convert the following relational algebra into SQL:
   • $\pi_{Name}(\sigma_{Marks > 85}(Student \bowtie Enroll))$

What is the use of relational query language in DBMS? Use the example to explain tuple and domain relational calculus.

Explain the following operations with the help of examples:
(i) Generalized projection
(ii) Outer join
(iii) Aggregate function

For relation R(A,B,C,D,E) functional dependencies are given as follows:

• A $\rightarrow$ BC
• CD $\rightarrow$ E
• B $\rightarrow$ D

Find candidate keys.

Consider the universal relation $R = \{A, B, C, D, E, F, G, H, I\}$ and the set of functional dependencies
$F = \{\{A, B\} \rightarrow \{C\}, \{A\} \rightarrow \{D, E\}, \{B\} \rightarrow \{F\}, \{F\} \rightarrow \{G, H\}, \{D\} \rightarrow \{I, J\}\}$
What is the key for $R$? Decompose $R$ into 2NF, then 3NF relations.

Considering the following schema, create the appropriate tables and insert at least 5 records:
AUTHOR (author-id, name, city, country)
PUBLISHER (publisher-id, name, city, country)
CATALOG (book-id, title, author-id, publisher-id, category-id, year, price)
CATEGORY (category-id, description)
ORDER-DETAILS (order-no, book-id, quantity)

Write each of the following queries in SQL and relational algebra:
(a) Obtain the names of authors who have 2 or more books in the catalog.
(b) Find the author of the book which has maximum sales.
(c) Obtain the names of author who have maximum number of publisher.
(d) Obtain the name of the city, author, publisher where publisher and author belong to same city.
(e) Obtain the title of books which has maximum sales.
(f) Obtain the book-id, description for the author who have exactly 3 books in the catalog.
(g) Obtain the author and publisher who have published books in more than or equal to 2 categories.

What are the typical phases of query processing? With a sketch, discuss these phases in high level query processing.

Describe Armstrong's axioms in detail. What is the role of these rules in database development process?

Explain different normal forms up to BCNF with examples.

When is the decomposition of relation schema R into two relation schemes X and Y, said to be a loss-less-join decomposition? Why is this property so important? Explain with example.

Describe the term MVD in the context of DBMS by giving an example. Discuss 4NF and 5NF also.

You are given the following set F of functional dependencies for a relation:
R(A, B, C, D, E, F): F = {ABC → D, ABD → E, CD → F, CDF → B, BF → D}

(i) Find all keys of R based on these functional dependencies.
(ii) Is this relation in Boyce-Codd normal form? Is it 3NF? Explain your answer.
(iii) Can the set F be simplified (by removing functional dependencies or by removing attributes from the left-hand side of functional dependencies) without changing the closure of F (i.e. F+)?

Explain the terms ‘partial functional dependency’ and ‘transitive dependency’. Define 2NF and 3 NF in relation with these terms.

Compute the closure of the following set F of functional dependencies for relation schema:
R = (A, B, C, D, E). A → BC, CD → E, B → D, E → A

List the candidate keys for R.

Explain the select, project, join and division with the help of example.

What is the need of normalization? How many types of normalization exist? Explain in detail with suitable examples.

Consider the following employee database, primary keys are underlined.
Employee (ename, street, city)
Works (ename, cname, salary)
Company (cname, city)
Manages (ename, manager-name)

Write SQL queries to
(i) Find the names of all the employees who work for XYZ.
(ii) Find all employees who live in the same city as the company for which they work.
(iii) Find all employees who live in the same cities and on the same streets as do their managers.
(iv) Find all employees who earn more than the average salary of all employees of their company.

What is trigger? When are they used and why? Explain.

Compute the closure (F+) of the following set of functional dependencies for the relational schema (A, B, C, D, E) = R. List the candidate keys for R:
A → BC
CD → E
B → D
E → A