Questions

Filter

Subject: All Subjects

Topic: All Topics

Consider a relation R with the schema R = (A, B, C, D, E, F, G, H) and a set of functional dependencies F as:

{AB→C, A→DE, B →F, F→GH}

 

Find the super key for this relation.

View Answer

Normalize the following table into 3NF.

EmpName Dept Age Address
John ECE, EEE 28 Butwal
Peter ME 31 Pokhara
James BBM, BBS 30 Dang
View Answer

Consider the following schema of the relational database.

  • Publisher(pid, name, location)
  • Book(bid, title, author, page, price)
  • Publish(bid, pid, publish_date)

 

Write Relational Algebra expressions for the following:

  1. Find title of book published by Addison_wisely.
  2. Update the author name to "MNP" of book "Introduction to Programming".
  3. Find the publish date of book "Software Engineering".
  4. Delete all books published by "Mc_graw_hill".
  5. Insert new record of book named "Database" published by "Pearson" in "2010-12-11" and written by "Patterson".
View Answer

Consider the relational database:

  • Account(acc_number, branch_name, balance)
  • Depositor(cust_name, acc_number)
  • Branch(branch_name, city)

 

Write the SQL statement for the following: 

  1. Find the record of the customer with balance in between Rs 10,000 and Rs 100,000.
  2. Find the name of depositor consisting of at most 6 characters.
  3. Find all the account maintained at branch Bhaktapur.
  4. Increase the interest by 10% to the account with balance over Rs 1,000,000.
  5. Find the name of depositor whose Name starts with 'Z'.
View Answer

Consider the relational database:

  • Account(acc_number, branch_name, balance)
  • Depositor(cust_name, acc_number)
  • Branch(branch_name, city)


Write the Relational Algebra for the following:

  1. Find the name of all depositors who have opened at 'XYZ' branch.
  2. Delete all accounts at branches located in Kathmandu.
  3. Insert information in the database specifying that Jhon has Rs 13,000 in account 006000134T at the Lalitpur branch.
  4. Increase the interest by 2% to the account with a balance over Rs 10,000.
  5. Change the name of the customer to Sushila whose account number is 005000142T.
View Answer

Design a database using ER diagram for a commercial bank. This commercial bank maintains information about entities: Depositor, Account, Loan, and Branch. State any assumptions made in the design of the ER diagram.

View Answer

Consider the following relational database of a college.

  • Student(RollNumber, StudentName, Address)
  • Teachers(TeacherlD, TecherName, TeachingSubject)
  • College(RollNumber, TeacherlD)

 

Write SQL for the following requests.

  1. Find the name of Students who live in Lalitpur.
  2. Find the name of the teacher who teaches the Database Management System subject.
  3. Find the name of the teacher who teaches Computer Organization subject to student John Smith.
  4. Insert a new tuple into relation teachers.
  5. Delete records of students whose address is "Pokhara''.
View Answer

Consider a relation R with the schema R (A, B, C, D, E, F) with a set of functional dependencies F as follows:

{AB→C, BC→AD, D → E, CF→ B}.

 

Find the super key for this relation and convert it to 2NF.

View Answer

Consider the following relational schema.

Employee(E_ID, E_Name, Dept_Name, Salary, Address)

 

Now write down the SQL for the following:

  1. Display E_Name who is taking maximum salary.
  2. Display all Dept_Name where number of Employees are less than 10.
  3. Display E_Name of employee whose address is either Mechi, Nepalgunj or Pokhara.
  4. Display E_ID and E_Name of employees whose name starts with “S” and Ends with “M”.
  5. Increase the salary of employees by 50% whose salary is less than 10000.
View Answer

Following table is provided to you:

Employee

Emp_Id Name Address Salary
1 Anil Parajuli Chabahil 50000
2 Gokul Pandey Pokhara 60000
3 Krishna Bdr Oli Chabahil 70000
4 Sumedha Prajapati Bhaktapur 80000
5 Pravin Shrestha Lazimpat 90000
6 Alka Maharjan Lalitpur 100000

 

Write SQL statements for the above table.

i. To create above table named Employee.

ii. To insert all the values in the table.

iii. To increase the salary of Pravin by 15%.

iv. To change the name of Alka to Mamata.

v. To delete the record of Anil.

View Answer

Consider the following relations.

  • Users(userID, username, password, email, dateOfBirth, gender, registerDate)
  • Categories(categoryID, categoryName, upperLimit)
  • Expenses(expenseID, spentDate, amount, categoryID, userID)

 

Write down the SQL statements for the following:

  1. Insert a new user.
  2. Find users whose date of birth is before Jan 01, 2000.
  3. Find top 5 categories on which users spend their money.
  4. Find categories in which no expenditure has been made so far.
  5. Find those users whose expenditure is not less than that of userID 405.
View Answer

Generalize the given relational table up to 2NF.

S_ID S_name Contact_no Address C_ID C_name
S01 Ram 9841, 3762 Birgunj C01 Database
S01 Ram 9841, 3762 Birgunj C02 E-Commerce
S02 Sita 5532 Kohalpur C03 MIS
S03 Krishna 5361, 9857 Bhojpur C01 Database
S04 Radha 2734 Damak C03 E-Commerce
S05 Rawan 5736 Dolpa C03 MIS
S03 Krishna 5361, 9857 Bhojpur C03 MIS
View Answer