To know advanced SQL concepts

To know advanced SQL concepts

MISY 3311  –  Homework 3

Advanced Database Concepts
Dr. Sotirios Steven Zygiaris
Szygiaris@pmu.edu.sa
Room:F084, tel. ext 5471

Overview
It covers an overview of PL-Structured Query Language
Objectives
?    To know advanced SQL concepts
Study Guide
Week 11-13
Chapter 6 textbook, PL/SQL, triggers
Week 13-15
Chapter 10 Distributed database management systems

Timeline
The homework covers weeks 11-15

Weight
This homework counts 8% towards your final grade. Total marks 8.

Late
To late homework assignments 10%. 0.8 points out of 8. Half a point will be deducted for each unexcused absence during weeks 8-10.
Delivery:
ONLY through BB fro on-time homeworks. Late homeworks are printed.

Grade distribution:
50% Lab performance
50% Homerwork
Deadline: 15 May

QSet3.1- Week 11-13

  1. Write a PL/SQL script to update the products that have price more than average price by reducing price by 10%. Display appropriate messages.
  2. Write a PL/SQL script to count and display all products with P_QOH between 0-25, 25 and 50, 50 to 75 ..up to 250. Display messages.
  3. What is a trigger?
  4. Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE table every time you add a new LINE row. (The LINE_TOTAL value is the product of the LINE_UNITS and the LINE_PRICE values.)
  5. Create a trigger named trg_line_prod that will automatically update the product quantity on hand for each product sold after a new LINE row is added.

QSet3.2- Week 13-15

  1. Define DDBMS  and its components
  2. Explain the levels of data and process distribution and how they are related with client/server architecture.
  3. Explain distribution transparency with examples. and the role of DDC.
  4. Explain the requests and transactions in transaction transparency
  5. What is the concurrency problem and how the two phase commit protocol provides a solution
  6. Explain why we do data fragmentation and data replication and allocation
  7. What are the three data fragmentation techniques?
  8. What are the main replication strategies?
  9. What are the 12 commandments for DDBMS
  10. Specify the minimum type of operations  the database must support to perform the following operations. These operations incude remote request, remote transactions, distributed transactions and distributed requests.

At C:

a. SELECT *
FROM CUSTOMER;

b. SELECT *
FROM INVOICE
WHERE INV_TOTAL > 1000;

c. SELECT *
FROM PRODUCT
WHERE PROD_QOH < 10;

d. BEGIN WORK;
UPDATE CUSTOMER
SET CUS_BALANCE = CUS_BALANCE + 100
WHERE CUS_NUM=’10936′;
INSERT INTO INVOICE(INV_NUM, CUS_NUM, INV_DATE, INV_TOTAL)
VALUES (‘986391’, ‘10936’, ‘15-FEB-2012’, 100);
INSERT INTO INVLINE(INV_NUM, PROD_CODE, LINE_PRICE)
VALUES (‘986391’, ‘1023’, 100);
UPDATE PRODUCT
SET PROD_QOH = PROD_QOH – 1
WHERE PROD_CODE = ‘1023’;
COMMIT WORK;

e. BEGIN WORK;
INSERT CUSTOMER(CUS_NUM, CUS_NAME, CUS_ADDRESS, CUS_BAL)
VALUES (‘34210′,’Victor Ephanor’, ‘123 Main St’, 0.00);
INSERT INTO INVOICE(INV_NUM, CUS_NUM, INV_DATE, INV_TOTAL)
VALUES (‘986434’, ‘34210’, ‘10-AUG-2011’, 2.00);
COMMIT WORK;

At A:
f. SELECT CUS_NUM, CUS_NAME, INV_TOTAL
FROM CUSTOMER, INVOICE
WHERE CUSTOMER.CUS_NUM = INVOICE.CUS_NUM;

g. SELECT *
FROM INVOICE
WHERE INV_TOTAL > 1000;

h. SELECT *
FROM PRODUCT
WHERE PROD_QOH < 10;

At B:
i. SELECT *
FROM CUSTOMER;

j. SELECT CUS_NAME, INV_TOTAL
FROM CUSTOMER, INVOICE
WHERE INV_TOTAL > 1000 AND CUSTOMER.CUS_NUM = INVOICE.CUS_NUM;

k. SELECT *
FROM PRODUCT
WHERE PROD_QOH < 10;

    1. The following data structure and constraints exist for a magazine publishing company.
      a. The company publishes one regional magazine each in Florida (FL), South Carolina (SC), Georgia (GA), and Tennessee (TN).
      b. The company has 300,000 customers (subscribers) distributed throughout the four states listed in Part a.
      c. On the first of each month, an annual subscription INVOICE is printed and sent to each customer whose subscription is due for renewal. The INVOICE entity contains a REGION attribute to indicate the state (FL, SC, GA, TN) in which the customer resides:
      CUSTOMER (CUS_NUM, CUS_NAME, CUS_ADDRESS, CUS_CITY, CUS_STATE, CUS_ZIP,
      CUS_SUBSDATE)
      INVOICE (INV_NUM, INV_REGION, CUS_NUM, INV_DATE, INV_TOTAL)

The company’s management is aware of the problems associated with centralized management and has decided that it is time to decentralize the management of the subscriptions in its four regional subsidiaries. Each subscription site will handle its own customer and invoice data. The company’s management, however, wants to have access to customer and invoice data to generate annual reports and to issue ad hoc queries, such as:
• _List all current customers by region.
• _List all new customers by region.
• _Report all invoices by customer and by region.

I.    What recommendations will you make regarding the type and characteristics of the required database system?
II.    What type of data fragmentation is needed for each table?
III.    What must be the criteria used to partition each database?
IV.    Design the database fragments. Show an example with node names, location, fragment names, attribute names, and demonstration data
V.    What type of distributed database operations must be supported at each remote site?
VI.    What type of distributed database operations must be supported at the headquarters site?