tables.sql

DROP TABLE REPAIRS;
DROP TABLE SERVICECENTRE;
DROP TABLE FAULTSHEET;
DROP TABLE APPLIANCE;

DROP TABLE STOCK;
DROP TABLE CUSTOMER;
DROP TABLE MANUFACTURER;


--  Customer
CREATE TABLE Customer
(
  cust_id     INT,
  cust_name   VARCHAR2(32),
  cust_addr   VARCHAR2(64)
);
--  Manufacturer
CREATE TABLE Manufacturer
(
  man_id   INT,
  man_name VARCHAR2(32),
  man_addr VARCHAR2(64)
);
 

 
CREATE TABLE Stock
(
    man_id INT,
  brand           VARCHAR2(64),
  guarantee_time  INT,
  stock_price     NUMBER(7,2)
);
 


--  Appliance
CREATE TABLE Appliance
(
  date_sold         DATE,
  serial_no     VARCHAR2(8),
  brand             VARCHAR2(64),
  cust_id       INT
);
 
--  Fault Sheet
CREATE TABLE FaultSheet
(
  fault_no          INT,
  serial_no      VARCHAR2(8),
  fault             VARCHAR2(128),
  date_submitted    DATE,
  condition         VARCHAR2(64),
  cust_id           INT
);

--TKT001, WM001, PDW001, HFR001,RZ
--  Service Center
CREATE TABLE ServiceCentre
(
  service_id    INT,
  service_name  VARCHAR2(32),
  service_addr  VARCHAR2(32),
  service_phone VARCHAR2(32)
);
 
--  On Site Repairs
CREATE TABLE Repairs
(
  fault_no          INT,
  date_repair       DATE,
  part_replaced     VARCHAR2(32),
  service_id        INT
);
 
 
 
ALTER TABLE CUSTOMER      ADD PRIMARY KEY (cust_id);
ALTER TABLE MANUFACTURER  ADD PRIMARY KEY (man_id);
ALTER TABLE STOCK         ADD PRIMARY KEY (brand);
ALTER TABLE APPLIANCE     ADD PRIMARY KEY(serial_no);
--ALTER TABLE APPLIANCE     ADD PRIMARY KEY (date_sold);
ALTER TABLE FAULTSHEET    ADD PRIMARY KEY (fault_no);
ALTER TABLE ServiceCentre ADD PRIMARY KEY (service_id);
 
ALTER TABLE APPLIANCE     ADD CONSTRAINT app FOREIGN KEY (cust_id)     REFERENCES CUSTOMER(cust_id);
ALTER TABLE APPLIANCE     ADD CONSTRAINT app1 FOREIGN KEY (brand)       REFERENCES STOCK(brand);
ALTER TABLE FAULTSHEET ADD CONSTRAINT fault FOREIGN KEY (serial_no) REFERENCES APPLIANCE(serial_no);
--ALTER TABLE FAULTSHEET    ADD CONSTRAINT fault1 FOREIGN KEY (date_purchased)   REFERENCES APPLIANCE(date_sold);
ALTER TABLE REPAIRS       ADD CONSTRAINT rep FOREIGN KEY (service_id)  REFERENCES ServiceCentre(service_id);
ALTER TABLE REPAIRS       ADD CONSTRAINT rep1 FOREIGN KEY (fault_no)    REFERENCES FAULTSHEET(fault_no);
ALTER TABLE STOCK         ADD CONSTRAINT stk  FOREIGN KEY (man_id)      REFERENCES MANUFACTURER(man_id);
 
 
--GRANT STATEMENTS      

GRANT SELECT,INSERT,UPDATE,DELETE ON MANUFACTURER TO RZELVYTE;
GRANT SELECT,INSERT,UPDATE,DELETE ON STOCK TO RZELVYTE;
 
 
GRANT SELECT,INSERT,UPDATE,DELETE ON CUSTOMER TO JWARD;
GRANT SELECT,INSERT,UPDATE,DELETE ON APPLIANCE TO JWARD;
GRANT SELECT,INSERT,UPDATE,DELETE ON STOCK TO JWARD;
 
GRANT SELECT,INSERT,UPDATE,DELETE ON FAULTSHEET TO DWOODS;
GRANT SELECT,INSERT,UPDATE,DELETE ON REPAIRS TO DWOODS;
GRANT SELECT,INSERT,UPDATE,DELETE ON SERVICECENTRE TO DWOODS;
GRANT SELECT,INSERT,UPDATE,DELETE ON CUSTOMER TO DWOODS;
GRANT SELECT,INSERT,UPDATE,DELETE ON MANUFACTURER TO DWOODS;
COMMIT;