This is using SQL Oracle DB: 1.Using the Oracle database engine, design and deve
ID: 671677 • Letter: T
Question
This is using SQL Oracle DB:
1.Using the Oracle database engine, design and develop a relational database to store user information to support a large online shipping company. Information that needs to be stored includes user name, detailed mailing address, and shipping information including number of packages, weight, cost, destination, origin, and tracking information allowing users to determine where a package is, and when and who signed for the package once it arrives. Other business data should be present showing how much the shipment costs and invoicing capability to send bills to customers. Your design should be normalized and well-designed for scalability for additional customers and shipment locations.
2.Your design should allow for inserting, updating, selecting and deleting information
3.A trigger should be used to generate invoices once a customer reaches $200 in shipping costs.
4.An instead-of trigger should be employed to update and/or populate multi-table views.
5.You should include packages, indexes, views and other helpful database components that will assist you in your design.
6.Your scripts should work perfectly allowing the creation of all of the tables, triggers, views and other database components and provide test scripts that demonstrate the use of the system.
7.You do not need a GUI component but your scripts should demonstrate all of the functionality of the system.
Deliverables:
All sql scripts and run scripts and instructions for successfully building your database and testing your database.
An ERD diagram of your database design showing primary keys, foreign keys and other constraints.
3. Well-written word document describing:
a.The database design
b.How to set up and test your database
c.The test data and results you used for your database,
Your approach, lessons learned, design strengths, limitations and suggestions for future improvement and alternative approaches, the most challenging aspects of developing this application.
Explanation / Answer
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER
UNION
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2;
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER
UNION ALL
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2;
We have shown both Oracle and MS Access query formats:
Oracle
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER
INTERSECT
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2;
MS Access
SELECT C.CUST_LNAME, C.CUST_FNAME
FROM CUSTOMER AS C, CUSTOMER_2 AS C2
WHERE C.CUST_LNAME=C2.CUST_LNAME AND C.CUST_FNAME=C2.CUST_FNAME;
Because Access doesn’t support the INTERSECT SQL operator, you need to list only the rows in which all the attributes match.
We have shown both Oracle and MS Access query formats:
Oracle
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2
MINUS
SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER;
MS Access
SELECT C2.CUST_LNAME, C2.CUST_FNAME
FROM CUSTOMER_2 AS C2
WHERE C2.CUST_LNAME + C2.CUST_FNAME NOT IN
(SELECT C1.CUST_LNAME + C1.CUST_FNAME FROM CUSTOMER C1);
Because Access doesn’t support the MINUS SQL operator, you need to list only the rows that are in CUSTOMER_2 that do not have a matching row in CUSTOMER.
This command will run in Oracle and in MS Access:
SELECT INV_NUM, CUSTOMER.CUST_NUM, CUST_LNAME, CUST_FNAME, INV_DATE, INV_AMOUNT
FROM INVOICE INNER JOIN CUSTOMER ON INVOICE.CUST_NUM=CUSTOMER.CUST_NUM
WHERE CUST_BALANCE>=1000;
There are at least two ways to do this query.
SELECT INV_NUM, AVG_INV, (INV_AMOUNT - AVG_INV) AS DIFF
FROM INVOICE, (SELECT AVG(INV_AMOUNT) AS AVG_INV FROM INVOICE)
GROUP BY INV_NUM, AVG_INV, INV_AMOUNT- AVG_INV
Another way to write this query is:
SELECT INV_NUM, INV_AMOUNT,
(SELECT AVG(INV_AMOUNT) FROM INVOICE) AS AVG_INV,
(INV_AMOUNT-(SELECT AVG(INV_AMOUNT) FROM INVOICE)) AS DIFF
FROM INVOICE
GROUP BY INV_NUM, INV_AMOUNT;
The preceding code examples will run in both Oracle and MS Access.
The following code will only run in Oracle:
CREATE SEQUENCE CUST_NUM_SQ START WITH 1000 NOCACHE;
CREATE SEQUENCE INV_NUM_SQ START WITH 5000 NOCACHE;
In Oracle:
ALTER TABLE CUSTOMER ADD (CUST_DOB DATE) ADD (CUST_AGE NUMBER);
The SQL code required to enter the date values is:
UPDATE CUSTOMER
SET CUST_DOB = ’15-MAR-1969’
WHERE CUST_NUM = 1000;
UPDATE CUSTOMER
SET CUST_DOB = ‘2-DEC-1977’
WHERE CUST_NUM = 1001;
In Oracle:
SELECT CUST_LNAME, CUST_FNAME, ROUND((SYSDATE-CUST_DOB)/365,0) AS AGE
FROM CUSTOMER;
In MS Access:
SELECT CUST_LNAME, CUST_FNAME, ROUND((DATE()-CUST_DOB)/365,0) AS AGE
FROM CUSTOMER;
In Oracle:
UPDATE CUSTOMER
SET CUST_AGE = ROUND((SYSDATE-CUST_DOB)/365,0);
In MS Access:
UPDATE CUSTOMER
SET CUST_AGE = ROUND((DATE()-CUST_DOB)/365,0);
SELECT AVG(CUST_AGE) FROM CUSTOMER;
8005, 1001, ’27-APR-04’, 225.40
Name the trigger trg_updatecustbalance.
CREATE OR REPLACE TRIGGER TRG_UPDATECUSTBALANCE
AFTER INSERT ON INVOICE
FOR EACH ROW
BEGIN
UPDATE CUSTOMER
SET CUST_BALANCE = CUST_BALANCE + :NEW.INV_AMOUNT
WHERE CUST_NUM = :NEW.CUST_NUM;
END;
To test the trigger you do the following:
SELECT * FROM CUSTOMER;
INSERT INTO INVOICE VALUES (8005,1001,’27-APR-04’,225.40);
SELECT * FROM CUSTOMER;
1002, ‘Rauthor’, ‘Peter’, 0.00
Name the procedure prc_cust_add. Run a query to see if the record has been added.
CREATE OR REPLACE PROCEDURE PRC_CUST_ADD
(W_CN IN NUMBER, W_CLN IN VARCHAR, W_CFN IN VARCHAR, W_CBAL IN NUMBER) AS
BEGIN
INSERT INTO CUSTOMER (CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_BALANCE)
VALUES (W_CN, W_CLN, W_CFN, W_CBAL);
END;
To test the procedure:
EXEC PRC_CUST_ADD(1002,’Rauthor’,’Peter’,0.00);
SELECT * FROM CUSTOMER;
CHAPTER 9
1.Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. And each time the product ABC is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P9.1
Table P9.1 The Database for Problem 1
Table name: PRODUCT Table name: PART
PROD_CODE
PROD_QOH
PART_CODE
PART_QOH
ABC
1,205
A
567
B
498
C
549
Given this information, answer questions a-e.
There are two correct answers 4 or 2. Depending in how the SQL statements are done.
The database requests are shown in the following table.
Four SQL statements
Two SQL statements
UPDATE PRODUCT
SET PROD_QOH = PROD_OQH + 1
WHERE PROD_CODE = ‘ABC’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘B’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘C’
UPDATE PRODUCT
SET PROD_QOH = PROD_OQH + 1
WHERE PROD_CODE = ‘ABC’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’ OR
PART_CODE = ‘B’ OR
PART_CODE = ‘C’
The transactions are shown in the following table.
Four SQL statements
Two SQL statements
BEGIN TRANSACTION
UPDATE PRODUCT
SET PROD_QOH = PROD_OQH + 1
WHERE PROD_CODE = ‘ABC’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘B’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘C’
COMMIT;
BEGIN TRANSACTION
UPDATE PRODUCT
SET PROD_QOH = PROD_OQH + 1
WHERE PROD_CODE = ‘ABC’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’ OR
PART_CODE = ‘B’ OR
PART_CODE = ‘C’
COMMIT;
We assume that product ‘ABC’ has a PROD_QOH = 23 at the start of the transaction and that the transaction is representing the addition of 1 new product. We also assume that PART components “A”, “B” and “C” have a PROD_QOH equal to 56, 12, and 45 respectively.
TRL
ID
TRX
NUM
PREV
PTR
NEXT
PTR
OPERATION
TABLE
ROW
ID
ATTRIBUTE
BEFORE
VALUE
AFTER
VALUE
1
1A3
NULL
2
START
**START TRANSACTION
2
1A3
1
3
UPDATE
PRODUCT
‘ABC’
PROD_QOH
23
24
3
1A3
2
4
UPDATE
PART
‘A’
PART_QOH
56
55
4
1A3
3
5
UPDATE
PART
‘B’
PART_QOH
12
11
5
1A3
4
6
UPDATE
PART
‘C’
PART_QOH
45
44
6
1A3
5
NULL
COMMIT
** END
TRANSACTION
The text’s Table 9.13 is the template for the problem solution. Use the solution to problem 1d as the input segment.
CHAPTER 10
(1) At Site C:
a. SELECT *
FROM CUSTOMER;
This SQL sequence represents a remote request.
b. SELECT *
FROM INVOICE
WHERE INV_TOTAL > 1000;
This SQL sequence represents a remote request.
c. SELECT *
FROM PRODUCT
WHERE PROD_QOH < 10;
This SQL sequence represents a distributed request. Note that the distributed request is required when a single request must access two DP sites. The PRODUCT table is composed of two fragments, PRO_A and PROD_B, which are located in sites A and B, respectively.
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-2002’, 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;
This SQL sequence represents a distributed request.
Note that UPDATE CUSTOMER and the two INSERT statements only require remote request capabilities. However, the entire transaction must access more than one remote DP site, so we also need distributed transaction capability. The last UPDATE PRODUCT statement accesses two remote sites because the PRODUCT table is divided into two fragments located at two remote DP sites. Therefore, the transaction as a whole requires distributed request capability.
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-1999’, 2.00);
COMMIT WORK;
This SQL sequence represents a distributed transaction. Note that, in this transaction, each individual request requires only remote request capabilities. However, the transaction as a whole accesses two remote sites. Therefore, distributed request capability is required.
At Site A:
f. SELECT CUS_NUM, CUS_NAME, INV_TOTAL
FROM CUSTOMER, INVOICE
WHERE CUSTOMER.CUS_NUM = INVOICE.CUS_NUM;
This SQL sequence represents a distributed request. Note that the request accesses two DP sites, one local and one remote. Therefore distributed capability is needed.
g. SELECT *
FROM INVOICE
WHERE INV_TOTAL > 1000;
This SQL sequence represents a remote request, because it accesses only one remote DP site.
h. SELECT *
FROM PRODUCT
WHERE PROD_QOH < 10;
This SQL sequence represents a distributed request. In this case, the PRODUCT table is partitioned between two DP sites, A and B. Although the request accesses only one remote DP site, it accesses a table that is partitioned into two fragments: PROD-A and PROD-B. A single request can access a partitioned table only if the DBMS supports distributed requests.
At Site B:
i. SELECT *
FROM CUSTOMER;
This SQL sequence represents a remote request.
j. SELECT CUS_NAME, INV_TOTAL
FROM CUSTOMER, INVOICE
WHERE INV_TOTAL > 1000 AND
CUSTOMER.CUS_NUM = INVOICE.CUS_NUM;
This SQL sequence represents a distributed request.
k. SELECT *
FROM PRODUCT
WHERE PROD_QOH < 10;
This SQL sequence represents a distributed request. (See explanation for part h.)
2. The CUSTOMER table must be partitioned horizontally by state. (We show the partitions in the answer to 3c.)
3. Given the scenario and the requirements in Problem 2, answer the following questions:
a. What recommendations will you make regarding the type and characteristics of the required database system?
The Magazine Publishing Company requires a distributed system with distributed database capabilities. The distributed system will be distributed among the company locations in South Carolina, Georgia, Florida, and Tennessee.
The DDBMS must be able to support distributed transparency features, such as fragmentation transparency, replica transparency, transaction transparency, and performance transparency. Heterogeneous capability is not a mandatory feature since we assume there is no existing DBMS in place and that the company wants to standardize on a single DBMS.
b. What type of data fragmentation is needed for each table?
The database must be horizontally partitioned, using the STATE attribute for the CUSTOMER table and the REGION attribute for the INVOICE table.
c. What must be the criteria used to partition each database?
The following fragmentation segments reflect the criteria used to partition each database:
Horizontal Fragmentation of the CUSTOMER Table By State
Fragment Name
Location
Condition
Node name
C1
Tennessee
CUS_STATE = 'TN'
NAS
C2
Georgia
CUS_STATE = 'GA'
ATL
C3
Florida
CUS_STATE = 'FL'
TAM
C4
South Carolina
CUS_STATE = 'SC'
CHA
Horizontal Fragmentation Of the INVOICE Table By Region
Fragment Name
Location
Condition
Node name
I1
Tennessee
REGION_CODE = 'TN'
NAS
I2
Georgia
REGION_CODE = 'GA'
ATL
I3
Florida
REGION_CODE = 'FL'
TAM
I4
South Carolina
REGION_CODE = 'SC'
CHA
d. Design the database fragments. Show an example with node names, location, fragment names, attribute names, and demonstration data.
Fragment C1 Location: Tennessee Node: NAS
CUS_NUM
CUS_NAME
CUS_ADDRESS
CUS_CITY
CUS_STATE
CUS_SUB_DATE
10884
James D. Burger
123 Court Avenue
Memphis
TN
8-DEC-01
10993
Lisa B. Barnette
910 Eagle Street
Nashville
TN
12-MAR-02
Fragment C2 Location: Georgia Node: ATL
CUS_NUM
CUS_NAME
CUS_ADDRESS
CUS_CITY
CUS_STATE
CUS_SUB_DATE
11887
Ginny E. Stratton
335 Main Street
Atlanta
GA
11-AUG-01
13558
Anna H. Ariona
657 Mason Ave.
Dalton
GA
23-JUN-01
Fragment C3 Location: Florida Node: TAM
CUS_NUM
CUS_NAME
CUS_ADDRESS
CUS_CITY
CUS_STATE
CUS_SUB_DATE
10014
John T. Chi
456 Brent Avenue
Miami
FL
18-NOV-01
15998
Lisa B. Barnette
234 Ramala Street
Tampa
FL
23-MAR-02
Fragment C4 Location: South Carolina Node: CHA
CUS_NUM
CUS_NAME
CUS_ADDRESS
CUS_CITY
CUS_STATE
CUS_SUB_DATE
21562
Thomas F. Matto
45 N. Pratt Circle
Charleston
SC
2-DEC-01
18776
Mary B. Smith
526 Boone Pike
Charleston
SC
28-OCT-01
Fragment I1 Location: Tennessee Node: NAS
INV_NUM
REGION_CODE
CUS_NUM
INV_DATE
INV_TOTAL
213342
TN
10884
1-NOV-01
45.95
209987
TN
10993
15-FEB-02
45.95
Fragment I2 Location: Georgia Node: ATL
INV_NUM
REGION_CODE
CUS_NUM
INV_DATE
INV_TOTAL
198893
GA
11887
15-AUG-01
70.45
224345
GA
13558
1-JUN-01
45.95
Fragment I3 Location: Florida Node: TAM
INV_NUM
REGION_CODE
CUS_NUM
INV_DATE
INV_TOTAL
200915
FL
10014
1-NOV-01
45.95
231148
FL
15998
1-MAR-02
24.95
Fragment I4 Location: South Carolina Node: CHA
INV_NUM
REGION_CODE
CUS_NUM
INV_DATE
INV_TOTAL
243312
SC
21562
15-NOV-01
45.95
231156
SC
18776
1-OCT-01
45.95
e. What type of distributed database operations must be supported at each remote site?
To answer this question, we must first draw a map of the locations, the fragments at each location, and the type of transaction or request support required to access the data in the distributed database.
Node
Fragment
NAS
ATL
TAM
CHA
Headquarters
CUSTOMER
C1
C2
C3
C4
INVOICE
I1
I2
I3
I4
Distributed Operations Required
none
none
none
none
distributed request
A trigger is just like a stored procedure, but with one major difference: it fires automatically when certain types of events take place. These events are table- and row-based; for example, you can define a trigger that is invoked after a row is updated.
Procedures and triggers are usually created in an ASCII file which is then executed from the ISQL Client program within InterBase. This file contains statements to create the procedure or trigger as part of the database. Once the transaction is committed, the procedure or trigger is immediately available for use by client applications.
Procedures and triggers use a special programming language that is based on SQL. Included with InterBase, this language is optimized to manipulate data stored in tables. It has full access to SQL data manipulation language (DML) statements such as SELECT, INSERT, UPDATE and DELETE. It also provides extensions to SQL that support variables, comments, declarative statements, conditional testing, branching and looping. It is a language that is designed to run inside a database, against the data in that database.
PROD_CODE
PROD_QOH
PART_CODE
PART_QOH
ABC
1,205
A
567
B
498
C
549
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.