HOMEWORK
Given the relations below, answer the following queries using SQL. Note that the tables contain sample records only and do not represent all the possible records that could be stored in these tables.
MEMB ERS
|
MEMBER_ID
|
NAME
|
ADDRESS
|
BALANCE
|
|
1
|
Brooks
|
1 Main Street
|
9.50
|
|
2
|
Smith
|
2 Main Street
|
-30.00
|
|
3
|
Brown
|
3 Main Street
|
0.00
|
|
4
|
Jones
|
4 Main Street
|
10.00
|
ORDERS
|
ORDER_NO
|
MEMBER_ID
|
SNO
|
ITEM
|
QUANTITY
|
|
1
|
1
|
1
|
Vegetable Oil
|
2
|
|
2
|
1
|
3
|
Flour
|
1
|
|
3
|
2
|
1
|
Cauliflower
|
10
|
|
4
|
2
|
2
|
Sugar
|
2
|
|
5
|
2
|
1
|
Lettuce
|
2
|
|
6
|
2
|
3
|
Flour
|
1
|
|
7
|
4
|
3
|
Flour
|
1
|
|
8
|
4
|
2
|
Sugar
|
5
|
|
9
|
4
|
1
|
Vegetable Oil
|
1
|
SUPPLIERS
|
SNO
|
SNAME
|
CITY
|
|
1
|
A Produce
|
Chicago
|
|
2
|
B Supply
|
Oak Park
|
|
3
|
C Inc
|
Chicago
|
SUPPLIER_ITEM
|
SNO
|
ITEM
|
PRICE
|
|
1
|
Vegetable Oil
|
3.99
|
|
1
|
Cauliflower
|
1.00
|
|
1
|
Lettuce
|
0.75
|
|
2
|
Sugar
|
2.99
|
|
3
|
Flour
|
1.50
|
|
3
|
Vegetable Oil
|
4.00
|
1. Find the names of those members who have ordered something from a supplier that is located in chicago.
2. Get the supplier names and items of all suppliers that supply at least one item ordered by Brooks.
3. Use two different SQL statements, find the names of those members who have not placed any orders.
Week 1
1. Can we have different conceptual schema design for the same application?
2. How database design process differs from a system design process?
3. What are the lessons learned when you created your first ER-diagram?
4. Is ER-Model helpful in providing all the required information about the application for which this conceptual schema has been designed?
Week 2
1. How helpful are the SEVEN steps provided to you for mapping ER-Schema to a Relational schema?
2. Is there a situation that is not covered by the SEVEN steps provided to you for mapping ER-Schema to a Relational schema?
3. What issues you see if you have to design Relational schema without ER-Schema?
Week 3
1. How constraints can be enforced in SQL while implementing a given schema?
2. Is it fine to use too many JOIN operations in a query?
3. What issues you see if you have to design Relational schema without ER-Schema?
4. How can we reduce the amount of space used by query execution?
Week 4
1. Show an example data set organized in two different ways illustrating the difference between the application-oriented and subject-oriented organization of data?
2. Why do some organizations prefer not to create a data warehouse? What are other options?
3. What is business intelligence? Give some recent examples of BI usage.
4. What BI benefits have companies found?