ALTER TABLE SAVINGS FOREIGN KEY SSSN (S_SSN) REFERENCES CUSTOMER ON DELETE RESTRICT
ALTER TABLE LOAN FOREIGN KEY LSSN (L_SSN) REFERENCES CUSTOMER ON DELETE RESTRICT
ALTER TABLE CHECKING FOREIGN KEY CSSN (C_SSN1) REFERENCES CUSTOMER ON DELETE RESTRICT
ALTER TABLE DEBITS FOREIGN KEY DACCT (DB_ACCNUMB) REFERENCES CHECKING ON DELETE CASCADE
ALTER TABLE CREDITS FOREIGN KEY CACCT (CR_ACCNUMB) REFERENCES CHECKING ON DELETE CASCADE
COMMIT
REVOKE CREATETAB ON DATABASE FROM PUBLIC
REVOKE BINDADD ON DATABASE FROM PUBLIC
REVOKE CONNECT ON DATABASE FROM PUBLIC
GRANT DBADM ON DATABASE TO GDBADM, BADMIN
GRANT CONNECT, BINDADD ON DATABASE TO GPREP, PREP
GRANT CONNECT, CREATETAB ON DATABASE TO GCREATE, TCREATE
GRANT CONNECT ON DATABASE TO GQRYSEL, QRYSEL, GEDIT, TEDIT, GTVCON, TVCON, GSELINS, SELINS, REFER, TDELETE, TUPDATE, SELUPDEL, TEXECUTE, EDITALT
GRANT SELECT ON TABLE CUSTOMER TO GQRYSEL, QRYSEL
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE CUSTOMER TO GEDIT, TEDIT
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE CHECKING TO GEDIT, TEDIT
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE SAVINGS TO GEDIT, TEDIT
COMMIT
CREATE VIEW VLOAN AS SELECT CU_SSN,CU_NAME,CU_ADDR1,CU_ADDR2,CU_ZIP,L_NUMB,L_TYPE,L_RATE, L_PAYMT,L_BAL,L_INTODAT,L_DESCRIP,L_DATE,L_SSN,L_TERM FROM CUSTOMER, LOAN WHERE CUSTOMER.CU_SSN = LOAN.L_SSN
CREATE VIEW VSAVE AS SELECT CU_SSN,CU_NAME,S_INTTOD,S_ACCNUMB,S_BAL,S_SSN FROM CUSTOMER, SAVINGS WHERE CUSTOMER.CU_SSN = SAVINGS.S_SSN
COMMIT
CREATE VIEW VIEWBOX AS SELECT B_NUMB, B_SIZE, B_RENT FROM BOX WHERE B_SIZE = 'M'
COMMIT
CREATE VIEW VIEWCUST AS SELECT * FROM NEWCUST
COMMIT
CREATE VIEW SOLLOAN (SSN, SSNSC) AS ((SELECT CU_SSN, S_SSN FROM CUSTOMER, SAVINGS WHERE (CU_SSN = S_SSN) AND (S_BAL > 1000.00)) INTERSECT (SELECT CU_SSN, C_SSN1 FROM CUSTOMER, CHECKING WHERE (CU_SSN = C_SSN1) AND (C_ENDBAL > 1000.00)) EXCEPT (SELECT CU_SSN, L_SSN FROM CUSTOMER, LOAN WHERE CU_SSN = L_SSN))
COMMIT
CREATE VIEW CHKACT (ACCNUMB) AS ((SELECT C_ACCNUMB FROM CHECKING WHERE C_ACCNUMB NOT IN (SELECT DB_ACCNUMB FROM DEBITS)) INTERSECT (SELECT C_ACCNUMB FROM CHECKING WHERE C_ACCNUMB NOT IN (SELECT CR_ACCNUMB FROM CREDITS)))
COMMIT
CREATE VIEW INACTCUS (SSN) AS (SELECT CU_SSN FROM CUSTOMER WHERE CU_SSN NOT IN ((SELECT C_SSN1 FROM CHECKING) UNION (SELECT S_SSN FROM SAVINGS) UNION (SELECT L_SSN FROM LOAN) UNION (SELECT B_SSN FROM BOX)))
COMMIT
COMMIT
COMMIT
COMMIT
COMMIT
INSERT INTO PERFDAT3 SELECT CU_SSN , CU_NAME, CU_ADDR1 , CU_ADDR2 ,CU_ZIP , L_NUMB , L_TYPE, L_RATE , L_TERM ,L_PAYMT, L_BAL, L_INTODAT,L_DESCRIP, L_DATE, S_ACCNUMB , S_IRATE, S_INTTOD, S_BAL, B_NUMB , B_SIZE,B_RENT , B_PAYUP, C_ACCNUMB , C_STARTBAL, C_ENDBAL FROM CUSTOMER, LOAN, SAVINGS, BOX, CHECKING WHERE CU_SSN = L_SSN AND CU_SSN = S_SSN AND CU_SSN = B_SSN AND CU_SSN = C_SSN1
INSERT INTO PERFDAT4 SELECT CU_SSN , CU_NAME, CU_ADDR1 , CU_ADDR2 ,CU_ZIP , L_NUMB , L_TYPE, L_RATE , L_TERM ,L_PAYMT, L_BAL, L_INTODAT,L_DESCRIP, L_DATE, S_ACCNUMB , S_IRATE, S_INTTOD, S_BAL, B_NUMB , B_SIZE,B_RENT , B_PAYUP, C_ACCNUMB , C_STARTBAL, C_ENDBAL FROM CUSTOMER, LOAN, SAVINGS, BOX, CHECKING WHERE CU_SSN = L_SSN AND CU_SSN = S_SSN AND CU_SSN = B_SSN AND CU_SSN = C_SSN1