- How do I represent a distributed database in an ER diagram.
This is the Problem:
*Create a database link to a remote product table.
Relations
1st Entity
CREATE TABLE CUSTOMER (
C_ID CHAR(9),
C_NAME CHAR(30),
C_PHONE CHAR(7),
C_ADDRESS CHAR(30),
ZIP CHAR(9),
CONSTRAINT C_ID_PRIMARY_KEY PRIMARY KEY (C_ID));
customer zip relationship M:1
create table has
no attributes
2nd Entity
CREATE TABLE ZIP (
ZIP_ID CHAR(9),
CITY CHAR(50),
STATE CHAR(2),
CONSTRAINT ZIP_ID PRIMARY_KEY PRIMARY KEY (ZIP_ID));
customer orders relationship M:N
TABLE Places (
O_ID CHAR(9),
C_ID CHAR(9),
CONSTRAINT PROSESS_PK PRIMARY KEY (C_ID)
CONSTRAINT PROSESS_PK PRIMARY KEY (O_ID));
3rd Entity
TABLE ORDERS (
O_ID CHAR(5),
c_id Char (5), do I need this FK here?
O_DATE CHAR(8),
QUANTITY NUMBER(6),
PRICE NUMBER(6,2),
Sales_TAX NUMBER(6,2),
CONSTRAINT O_ID_PRIMARY_KEY PRIMARY KEY (O_ID),
CONSTRAINT C_ID_FOREIGN_KEY FOREIGN KEY (C_ID) REFERENCES CUSTOMER (C_ID));
orders products relationship
CREATE TABLE HAS (
O_ID CHAR(9),
P_ID CHAR(9),
CONSTRAINT REQUEST_PK PRIMARY KEY (O_ID)
CONSTRAINT REQUEST_PK PRIMARY KEY (P_ID));
CREATE TABLE PRODUCT (
P_ID NOT NULL NUMBER(38),
P_NAME VARCHER2(20),
ONHAND_Q NUMBER(38),
REORDER_Q NUMBER(38),
P_PRICE NUMBER(8,2),
CONSTRAINT P_ID_PRIMARY_KEY PRIMARY KEY (P_ID));
product supplier relationship M:N
Create TABLE from (
P_ID CHAR(9),
S_ID CHAR(9),
CONSTRAINT P_ID_PRIMARY_KEY PRIMARY KEY (S_ID)
CONSTRAINT P_ID_PRIMARY_KEY PRIMARY KEY (P_ID));
TABLE SUPPLIER (
S_ID CHAR(9),
S_NAME CHAR(50),
PHONE CHAR(7),
CONSTRAINT S_ID_PRIMARY_KEY PRIMARY KEY (S_ID));
More problem
*create customer and transaction tables.
Input
*enter customer name, product, and quantity
P_Code
ACCEPT C_NAME PROMPT "Enter the customer name: ";
ACCEPT P_NAME PROMPT "Enter the product name: ";
ACCEPT QUANITY PROMPT "Enter the product quanity: ";
Output
*order number and total price
P_Code
PROMPT "The order number is:" &O_ID
PROMPT "The total price is:" &T_PRICE
Calculate
*add 8% sales tax
Commit
*transaction
Update
*product table
Locks
P_Code
create before and after triggers
before trigger
determines if there is enough product available to fill order request.
if quantity is below reorder quantity
prompt to user 'time to reorder';
begin
lock table product in exclusive mode;
update product
select * from product where ;
end;
accept prompt 'How much do you want to reorder?'
update product table by reorder quantity
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Sun Mar 14 1999 - 13:51:52 CST
