CS713 Assignment 2 Solution and Discussion

[zareen]

CS713 – Object Oriented DBMS

Due Date: 4-12-2019
Total Marks: 50
Assignment 2

Instructions to Solve Assignments

The purpose of assignments is to give you hands on practice. It is expected that students will solve the assignments themselves. Following rules will apply during the evaluation of assignment.

• Cheating from any source will result in zero marks in the assignment.
• Any student found cheating in any two of the assignments submitted will be awarded “F” grade in the course.
• No assignment after due date will be accepted.
 
Q.1 Total Points (5*4=20)

Write SQL queries for the following:

1. Write a SQL query to get the number of students working in a project.
2. Write a SQL query to get the teachers name having salary greater than or equal to 50000 and less than or equal to 100000.
3. Write a query to get project wise count of students sorted by project’s count in descending order.
4. Write a SQL query to get all the teachers details from the “Teacher_Info” table who joined in year 2017.
5. Write a SQL query to fetch duplicate records from a table.

Total Points (15)
Q.2
Draw database model for the inventory system. Entities are given below:

• Customer (custID, lastName, firstName, street, city, state, zip, creditLimit, areaCode, number)
• Order (orderNo, date, totalAmount, tax, street, city, state, zip, areaCode, number, custId)
• Supplier (supplierNo, supName, street, city, state, zip, country, contactName, countryCode, areaCode, number)
• Item (ItemNo, ItemName, unitPrice, qtyOnHand, reorderPoint)
• Contains (orderNo, ItemNo, Quantity)
• Supplies (supplierNo, ItemNo, unitCost)
Note:
Constraints: A customer can place one or many orders at the same time. There is a many to many relationship between order and item. There is a many to relationship between supplier and the Item.

Question 3: Total Points (7.5+7.5=15)

Read the paper titled “AIDA - Abstraction for Advanced In-Database Analytics”
, and answer the following questions:
(a) Write the brief summary of the given paper.
(b) How data abstraction can be achieved using Python interpreter and RBDMS.

Note: Research paper is available in the zip folder.