----------------  PRIMARY KEY CONSTRAINTTS  --------------------

ALTER TABLE customers
    ADD CONSTRAINT conPK_Customers PRIMARY KEY(customer_id);
ALTER TABLE tellers
    ADD CONSTRAINT conPK_Tellers PRIMARY KEY(teller_id);
ALTER TABLE transactions
    ADD CONSTRAINT conPK_Transactions PRIMARY KEY(trans_ctr);
ALTER TABLE customer_accounts
    ADD CONSTRAINT conPK_Accounts PRIMARY KEY(account_id);

----------------  FOREIGN KEY CONSTRAINTTS  --------------------

ALTER TABLE customers
    ADD CONSTRAINT conFK_Cust_Pcredit FOREIGN KEY(sin)
	REFERENCES hq_national.personal_credit(sin);
ALTER TABLE customer_accounts
    ADD CONSTRAINT conFK_Accnts_Cust FOREIGN KEY(customer_id)
	REFERENCES customers(customer_id);
ALTER TABLE customer_accounts
    ADD CONSTRAINT conFK_Accnts_ATypes FOREIGN KEY(accounttype_id)
	REFERENCES HQ_National.account_types(accounttype_id);
ALTER TABLE transactions
    ADD CONSTRAINT conFK_Trans_fCaccnt FOREIGN KEY(from_acct)
	REFERENCES customer_accounts(account_id);
ALTER TABLE transactions
    ADD CONSTRAINT conFK_Trans_tCaccnt FOREIGN KEY(to_acct)
	REFERENCES customer_accounts(account_id);
ALTER TABLE transactions
    ADD CONSTRAINT conFK_Trans_Tellers FOREIGN KEY(teller_id)
	REFERENCES tellers(teller_id);

----------------  NOT NULL CONSTRAINTS   --------------------

ALTER TABLE customers
    MODIFY(sin char(11) not null,customer_id number(10) not null,
	last_name varchar2(25) not null); 
ALTER TABLE customer_accounts
    MODIFY(account_id char(9) not null,customer_id number(10) not null,
	accounttype_id number(10) not null);
ALTER TABLE tellers
    MODIFY(teller_id varchar2(10) not null,
	pass_word varchar2(10) not null);
ALTER TABLE transactions
    MODIFY(trans_ctr number(12) not null,trans_date date not null,
	trans_type char(3) not null,amount number(11,2) not null,
	teller_id varchar2(10) not null);

----------------  CHECK CONSTRAINTS  ------------------------

ALTER TABLE customers
    ADD CONSTRAINT conCK_Cust_Province CHECK(province in ('NS',
	'NB','PE','NF','PQ','ON','MB','SK','AB','BC','YT','NT'));
ALTER TABLE customers
    ADD CONSTRAINT conCK_Cust_Country CHECK
	(country = upper('CANADA'));
ALTER TABLE tellers
    ADD CONSTRAINT conCK_Tellers_Alevel CHECK
	(authority_level in (0,1));
ALTER TABLE customer_accounts
    ADD CONSTRAINT conCK_Accnts_Status CHECK
	(status in ('OPN','HLD','CLS'));
ALTER TABLE transactions
    ADD CONSTRAINT conCK_Trans_Type CHECK
	(trans_type in ('WTH','DEP','PAY','PUR','TRF','INT'));




Create Sequences

CREATE SEQUENCE seq_Customer_ID
INCREMENT BY 100
START WITH 30000;


CREATE SEQUENCE seq_Account_ID
INCREMENT BY 150
START WITH 100650;


CREATE SEQUENCE seq_Transaction_CTR
INCREMENT BY 10
START WITH 50000;




Package specification

CREATE OR REPLACE PACKAGE prc_mb_spec
IS  
	FUNCTION fn_logon_request
   	(pIN_teller_id       varchar2,
    	pIN_pass_word       varchar2)
    	RETURN    NUMBER;

	PROCEDURE sp_customer_add
	(pIN_sin varchar2, pIN_last_name varchar2, pIN_first_name varchar2,
	pIN_middle_name varchar2, pIN_address_line1 varchar2, pIN_address_line2 varchar2,
        pIN_city varchar2, pIN_province varchar2, pIN_country varchar2, pIN_postal varchar2,
        pIN_phone varchar2, pIN_pin varchar2, pIN_teller_id varchar2, pOT_newid OUT number,
        pOT_credit_allowed OUT number, pOT_credit_description OUT varchar2);
  
	PROCEDURE sp_customer_modify
    	(pIN_customer_id number, pIN_last_name varchar2, pIN_first_name varchar2, 
        pIN_middle_name varchar2, pIN_address_line1 varchar2, pIN_address_line2 varchar2,
        pIN_city varchar2, pIN_province varchar2, pIN_country varchar2, pIN_postal varchar2,
        pIN_phone varchar2, pIN_pin varchar2, pIN_teller_id varchar2);
 
 	PROCEDURE sp_customer_delete
    	(pIN_customer_id     NUMBER,
     	pIN_teller_id       VARCHAR2); 
  
	PROCEDURE SP_CUSTOMER_SEARCH
	(pIN_Search_Name varchar2, pOT_Results OUT varchar2);
 
	PROCEDURE SP_CUSTOMER_FIND
	(pIN_Customer_ID number, pOT_Result OUT varchar2);
 	PROCEDURE SP_CUSTOMER_FIND
	(pIN_First_Name varchar2, pIN_Last_Name varchar2, pIN_Middle_Name varchar2,
        pOT_Result OUT varchar2); 
	PROCEDURE SP_CUSTOMER_FIND
	(pIN_SIN number, pOT_Result OUT varchar2);
	PROCEDURE sp_trans_add
    	(pIN_type varchar2, pIN_amount number, pIN_from varchar2, pIN_to varchar2,
        pIN_teller_id varchar2, pOT_trans_ctr OUT number);

	PROCEDURE SP_ACCOUNT_ADD
	(pIN_Customer_ID number, pIN_Account_Type number, pOT_Account_ID  OUT varchar2,
        pIN_Teller_ID varchar2);
  
	PROCEDURE sp_apply_interest
   	(pIN_teller_id       VARCHAR2);
 
	PROCEDURE sp_account_list
    	(pIN_customer_id      number,
     	pOT_results   OUT      varchar2);
  
END prc_mb_spec;
/