Question

HW HELP MySQL

A. What are Flat, Hierarchical & Network database models ? What is their advantages and disadvantages ?

B.

Using DB : Northwind

1. List of Products whose listprice between 15 and 20 and sort by category.

2. Display the shipping address (address, city, state, zip) of Sales Representative (Table Hint: Employees, Orders using Sub query)

3. Display list of Product Names along with 'WhenToOrder' based on following condition.
    If difference between TargetLevel and ReorderLevel is greater than 50 units display WhenToOrder as 'After 50 Days'
    If difference between TargetLevel and ReorderLevel is greater than 30 units display WhenToOrder as 'After 30 Days'
    If difference between TargetLevel and ReorderLevel is greater than 20 units display WhenToOrder as 'After 20 Days'
    If difference between TargetLevel and ReorderLevel is less than 20 units display WhenToOrder as 'Order now'

4.Display Orders which has New status. (Hint : Orders and Order_status table using sub query)

5.Display unique categories in Products table.

6. Display customer details sorted by job title in following order

Owner
Purchasing Manager
Accounting Manager
Purchasing Representative
Purchasing Assistant
Accounting Assistant

7. In Orders table display Order date in place of Shipped date when shipped date is not available

8. Display Orders which took more than 2 days to get shipped. (Hint: date function)


DB : Chinook

9. Find the total cost and avg play time for each album and generation.

10. Write a query to find albums which has at least 4 tracks.

Answer 1

FLAT Database:

Flat database uses files to store information.

Data is stored in files so retrieving the data can be faster and quicker.

flat file is one dimensional and  represented in tabular form.

Data might have stored many number of times (redundant data) like data stored in file may have been stored in another file.

There is a relationship between each record.

Ex: spreadsheets, address books.

advantage : this is the first method for converting raw data i.e un ordered data into useful information.

drawback: redundant data.

Hierarchical DB:

As name itself suggests "Hierarchical" means it is like parent and child relationship.

So it have one main root (one and only one main parent) and it may have child which in turn have another child.

structure looks like this.

Parent

Child 1 Child 2

Child 3 Child 4 Child 5 Child 6

  Child 7 Child 8

So main parent is first table, remaining are child tables.

Note:; child should have only one parent, but a parent can have multiple children.

Advantage: From above structure we can easily identify relationship between parent and child.

Each has its separate child table, so redundant data can be eliminated.

Drawback:

User should know the Hierarchy i.e structure of tree implementation to find the details or records.

We cant add record to child table until unless we reference it from parent table.

Network DB:

It is exactly like Hierarchical DB but one main difference is that Child can have more than one parent which is not possible

Hierarchical DB.

Structure:

Parent

Child 1 Child 2

Child 3 Child 4 Child 5 Child 6

  Child 7 Child 8

Here child 5 has the 2 parents.

Advantages:

reduces the data redundancy

very fast and storage efficient

it can perform better than Hierarchical with those one to many relationships.

Drawbacks:

It is very difficult to visualize the data in this because we have many to many relationships between parent and child.

Also difficult to implement and maintain due to complex structure.

B.

NOTE:

I am extremely sorry to inform you that i didnt find some tables like order_status and some attributes with proper name like (listprice or unit price) in product table). if u think any thing is wrong in below queries , please please send me atleast structure of northwing DB so that i can send u the queries with proper attributes and table names.

1. SELECT * FROM PRODUCTS WHERE listPrice BETWEEN 15 and 20 ORDER BY Category

HERE i assume table name is Orders.

2.

SELECT address, city, state, zip FROM ORDERS WHERE EMPLOYEE_ID in (SELECT EMPLOYEE_ID FROM EMPLOYEES)

here i am assuming SALE REPRESENTATIVE as EMPLOYEE_ID (IF u can give the atleast table names and attributes it can be better understood.)

3.

SELECT productName ,

CASE

WHEN (TargetLevel-ReorderLevel) >50 THEN "After 50 Days"
WHEN (TargetLevel-ReorderLevel) >30 THEN "After 30 Days"

WHEN (TargetLevel-ReorderLevel) >20 THEN "After 20 Days"

WHEN (TargetLevel-ReorderLevel) < 20 THEN "Order Now"
END AS WhenToOrder

FROM PRODUCTS

4.

SELECT * FROM ORDERS WHERE ORDER_ID IN (SELECT ORDERID FROM ORDER_STATUS WHERE STATUS="New")

5.

SELECT DISTINCT CATEGORYID FROM PRODUCT TABLE

6.

I didnt find the attribute job title and jodcode in online NORTHwind , please send the structure.

7.

SELECT ORDERDATE FROM ORDERS WHERE SHIPPEDDATE IS NULL;

8.

SELECT * FROM ORDERS WHERE DATEDIFF(SHIPPEDDATE, ORDERDATE)>2;

Please i need Chinook DB structure to write queries, please dont mind.

if u send the structure , i will get u the queries.

NOTE: inorder to write the remaining queries i need structure please.

if u have any doubt, regarding explanation of above queries, please comment. i will explain.