Questions

Filter

Subject: All Subjects

Topic: All Topics

Normalize the following relation to 3NF.

emp_id emp_name emp_zip emp_state emp_city emp_district
1001 Hari 282005 state7 Dipayal Doti
1002 Keshab 555780 state2 Nepalgunj Banke
1003 Pawan 822005 state7 Dhangadhi Kailali
1004 Aardi 772290 state4 Pokhara Kaski
1005 sunita 885009 state3 Kathmandu Ktm
View Answer

Consider the following relational database:

  • Patient(PID, Pname, Paddress, Pgender, disease)
  • Doctor(DID, Dname, Daddress, Department, salary)
  • Appointment(PID, DID, time)

 

Write SQL statements for the following:

  1. Insert a new tuple in the relation patient.
  2. List all the patients who were checked by doctors Pankaj and Rubi.
  3. List name and id of doctors whose salary is less than Rs. 120,000.
  4. Find name of patients whose name begins with 'Ru'.
  5. Increase salary of all doctors by 10% who works in Forensic department.
View Answer

Draw ER diagram for the given scenario:

Consider a bus ticketing system that records information about the passegner, bus and route. Passenger is assigned to a bus that travels to a route. A  bus contains many passegners and a passegner can be assigned into only one bus. Many busses travel in same route but a bus can travel in only one route. The attributes of passegner are pid(unique), gender and phone_no(multi-values). Similarly bus contains regno(unique) and color and route contains rid(unique), distance and rate.

View Answer

Let

R = {A, B, C, D, E}

and

F = { AB → C, A → D, D → E, AC → B }

Then compute {AB}+ and {DC}+.

View Answer

Consider the relation:

student(sid, sname, address, gender, age)

 

Write relational algebra(RA) for the following:

  1. To display name of student whose age is greater than 15.
  2. To remove the record of the student who are from Kathmandu.
  3. To update address of student to Kathmandu whose sid is 'S1101'.
View Answer

Normalize the following table to 3NF.

Sid Cid SName C_Name Grade Faculty F_Phone
1 IS208 Adams Database A Padam B. Karki 601245
1 IS301 Adams Programming B Ravi Gurung 458695
2 IS208 Jones Database A Padam B. Karki 601245
3 IS208 Smith Database B Padam B. Karki 601245
4 IS301 Baker Programming A Ram Sundar 452368
4 IS208 Baker Database B Padam B. Karki 601245
View Answer

Normalize the following table(question corrected).

Cust_Id Cust_name city Zip_code Cust_address House_no Items Company Price
1 Bob Ktm, Pok 001 Sanothimi 009 Mobile Samsung 50000
2 John But, Bhair 002 Lalitpur 010 TV Sony 80000
3 Cristina Narayangadh 003 Baneshwor 111 Camera LG 55000
4 Katrina Dang 004 New Road 112 Laptop Dell 65000
View Answer

Normalize the following table.

Cust_Id Cust_name Cust_address Zip_code city House_no Items Company Price
1 Bob Ktm, Pok 001 Sanothimi 009 Mobile Samsung 50000
2 John But, Bhair 002 Lalitpur 010 TV Sony 80000
3 Cristina Narayangadh 003 Baneshwor 111 Camera LG 55000
4 Katrina Dang 004 New Road 112 Laptop Dell 65000
View Answer

Use the following schema to write relational algebra statements for the questions that follow. The primary keys are underlined in the schema:

STUDENT(Ssn, Name, Major, DOB)
COURSE(Cnum, Cname, Dept)
ENROLL(Ssn, Cnum, Semester, Grade)
BOOK_ADOPTION(Cnum, Semester, ISBN)
TEXTBOOK(ISBN, Title, Publisher, Author)

 

Specify the following queries in relational algebra:

  1. List the course number taken by all students named 'John Smith' in winter 1999 (i.e., Semester = 'W99').
  2. Produce a list of textbooks (include Course#, ISBN, Title) for courses offered by the Computer Science Department (i.e., Dept = 'CS').
  3. List any department that has its adopted books published by 'BC Publishing'.
  4. Find the names of students who have not been enrolled in any course.
  5. Find all the textbooks that were authored by Mike Wilkins.
View Answer

Consider the schema R = {A, B, C, D} such that following functional dependency holds on it:

F = {A → B, A → BC, C → D}

If R is decomposed into relations R1 = {A, B} and R2 = {B, C, D}, check if decomposition results in good design or not.

View Answer

Explain how aggregation and generalization help to minimize redundancy while designing ER Diagram.

View Answer

Consider the following relational database:

  • Employee(empid, gender, name, dob, salary, address, deptno, start_date)
  • Department(dpetno, name)
  • Students(std_id, gender, name, dob, projectno, deptno)
  • Project(projectno, name, location)


Write Relational algebra expression for the following:

  1. Find employee name and address who works in "xyz company".
  2. Delete all employees whose salary is greater than Rs. 1,00,000.
  3. Update department name to "SitaGroup" where student "Sita" is involved.
  4. Find the average salary of all employees.
  5. Insert student record named "Gopal Shrestha" for department "Ram Group".
View Answer