TUTORIALS on Fundamentals of Database Management Systems (FDBMS)

 

TUTORIAL on Fundamentals of Database Management System (FDBMS)

^{TUTORIAL I from UNIT 1}

Q1. List four applications which you have used, that most likely used a database system to store persistent data.

Q2. List four significant differences between a file-processing system and a DBMS.

Q3. Explain the concept of physical and logical data independence.

^Q4. List five responsibilities of a database-management system. For each responsibility, explain the problems that would arise if the responsibility were not discharged.

Q5. What are the five main functions of a database administrator?

Q6. Explain three-schema Architecture. How does it help in designing the structure of a Database?

Q7. What is SQL? How do we classify the statements given in SQL for creating and accessing a database.

Q8. What are the characteristics of a DBMS? List down the advantages and disadvantages of using DBMS.

^{TUTORIAL II from UNIT 2}

Q1. What is E-R Model? How does it help a database designer in designing a database?

Q2. What is the role of a key in designing a database? Discuss the various types of keys used in maintaining the integrity of a database?

Q3. Write notes on Entity Set and Relationship Set. Differentiate Strong Entity Set from Weak Entity Set.

Q4. Develop an E-R Diagram for designing a database of a banking application.

Q5. What is relation? Differentiate between the terms: relation schema and relation instance.

Q6. What are domain constraints? Explain various domain constraints used in defining the schema of a database.

^{TUTORIAL III from UNIT 3}

Q1. Consider the following schemas in a DBMS as follows:

        (i)   Resort(resortNo, resortName, resortType, resortAddress, resortCity, numSuite)

        (ii)  Suite(suiteNo, resortNo, suitePrice)

        (iii) Reservation(reservationNo, resortNo, visitorNo, checkIn, checkOut, totalVisitor, suiteNo)

        (iv) Visitor(visitorNo, firstNmae, lastName, visitorAddress)

Write the query for the following:

        (a)  Write the SQL to list full details of all the resorts in Los Angels.

        (b)  Write the SQL to list full details of all resorts having number of suits more than 30.

        (c)  Write the SQL to list visitors in ascending order by their first name.

Solution:

        (a) SELECT * FROM Resort WHERE resortCity = "Los Angels";

        (b) SELECT * FROM Resort WHERE numSuite > 30;

        (c) SELECT * FROM Visitor ORDER BY lastName ASC;

Q2. Consider the following schemas in a DBMS as follows:

        (i)   Customer(customerName, customerStreet, customerCity)

        (ii)  Branch(branchName, branchCity, branchAssets)

        (iii) Account(accountNumber, branchName, accountBalance)

        (iv) Depositor(customerName, accountNumber)

Write the query for the following:

        (a)  Find all the bank customers having a loan, an account, or both at the bank.

        (b)  Find all customers who have both a loan and an account at the bank.

        (c)  Find all customers who have an account but no loan at the bank.

    Solution:

    (a) (SELECT customerName FROM depositor) 

            UNION 

          (SELECT customerName FROM borrower)

    (b) (SELECT DISTINCT customerName FROM depositor)

            INTERSECT

          (SELECT DISTINCT customerName FROM borrower)

    (c) (SELECT DISTINCT cusomerName FROM depositor)

            EXCEPT

         (SELECT cusomerName FROM borrower)

Q3. Consider the following schema of a Banking Application:

        (i)   customer(customerName, customerStreet, customerCity)

        (ii)  branch(branchName, branchCity, Assets)

        (iii) account(accountNumber, branchName, accountBalance)

        (iv) depositor(customerName, accountNumber)

Write a SELECT statement using a sub query that can be used to find the number of accounts each and every customer have with the bank.

Answer:

SELECT cust.customerName,

        (SELECT count(*) FROM account acc, depositor dep

        WHERE acc.accountNumber = dep.accountNumber AND

                       dep.customerName = cust.customerName) as Num_of_Accounts

FROM customer cust

Q4. Write DDL commands that can implement integrity constraints for the following:

        (i)   No two accounts can have the same account number

        (ii)  Every account number in the depositor relation must have a matching account number in the account relation

        (iii) The balance attribute of account table should not be null and can hold only value greater than zero (0)

Solution:

The following DDL commands create three relations belong the a database created for a banking application:

(a)  CREATE TABLE branch(branch_name CHAR(15), branch_city CHAR(30), assets NUMERIC(16,2), PRIMARY KEY(branch_name));

(b)  CREATE TABLE account(account_number CHAR(10), branch_name CHAR(15), balance NUMERIC(12,2), PRIMARY KEY(account_number), FOREIGN KEY(branch_name) REFERENCES branch, CHECK(balance>=0));

(c) CREATE TABLE depositor(customer_name CHAR(20), account_number CHAR(10), PRIMARY KEY(customer_name, account_number) FOREIGN KEY(customer_name) REFERENCES customer, FOREIGN KEY(account_number) REFERENCES account)

^{TUTORIAL IV from UNIT 4}

Q1. Apply Armstrong's Axioms for FD's.

Solution: Armstrong Axioms

Q2. Illustrate the types of Functional Dependency.

1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional dependency
4. Transitive functional dependency

1. Trivial Functional Dependency

In Trivial Functional Dependency, a dependent is always a subset of the determinant.
i.e. If X → Y and Y is the subset of X, then it is called trivial functional dependency

For example,

roll_no	name	age
42	abc	17
43	pqr	18
44	xyz	18

Here, {roll_no, name} → name is a trivial functional dependency, since the dependent name is a subset of determinant set {roll_no, name}
Similarly, roll_no → roll_no is also an example of trivial functional dependency. 

2. Non-trivial Functional Dependency

In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant.
i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional dependency.

For example,

roll_no	name	age
42	abc	17
43	pqr	18
44	xyz	18

Here, roll_no → name is a non-trivial functional dependency, since the dependent name is not a subset of determinant roll_no
Similarly, {roll_no, name} → age is also a non-trivial functional dependency, since age is not a subset of {roll_no, name} 

3. Multivalued Functional Dependency

In Multivalued functional dependency, entities of the dependent set are not dependent on each other.
i.e. If a → {b, c} and there exists no functional dependency between b and c, then it is called a multivalued functional dependency.

For example,

roll_no	name	age
42	abc	17
43	pqr	18
44	xyz	18
45	abc	19

Here, roll_no → {name, age} is a multivalued functional dependency, since the dependents name & age are not dependent on each other(i.e. name → age or age → name doesn’t exist !)

4. Transitive Functional Dependency

In transitive functional dependency, dependent is indirectly dependent on determinant.
i.e. If a → b & b → c, then according to axiom of transitivity, a → c. This is a transitive functional dependency  

For example,

enrol_no	name	dept	building_no
42	abc	CO	4
43	pqr	EC	2
44	xyz	IT	1
45	abc	EC	2

Here, enrol_no → dept and dept → building_no, 
Hence, according to the axiom of transitivity, enrol_no → building_no is a valid functional dependency. This is an indirect functional dependency, hence called Transitive functional dependency.

Q3. Evaluate 1NF, 2NF, 3NF and BCNF with an example.

Solution: Normalization of Databases using Normal Forms

Q4. Find the highest Normal Form of the relation F of functional dependencies for a relational schema R(A, B, C, D, E) F.D.=(A-->BC, CD-->E, B-->D, E-->A)

Reference: Finding the highest Normal Form of a Relation

^{TUTORIAL V from UNIT 5}

Q1. Elaborate different States of Traction with a neat sketch.

Q2. Elaborate Testing of Serializability and Precedence Graph of a Schedule.

Q3. Elaborate the following:

        (i) Lock        (ii) Time Stamp based Protocol        (iii) Validation based Protocol

Q4. Illustrate Shared and Exclusive Locking and 2-Phase Locking Protocol.