/*
  The Database Schema for the Internet Banking System.
*/


CREATE TABLE Transaction_Type 
(Trans_Type_Code			        VARCHAR(12) NOT NULL,
 Trans_Type_Description			    VARCHAR(20),
 CONSTRAINT Type_code_pk 
					    PRIMARY KEY(Trans_Type_Code)
						
 );
 
CREATE TABLE Account_Type 
(Account_Type_Code			        VARCHAR(12) NOT NULL,
 Account_Type_Description		    VARCHAR(20),
 CONSTRAINT Type_code_pk 
					    PRIMARY KEY(Account_Type_Code)						
 );
	
	
CREATE TABLE Transaction 
(Trans_ID			                  NUMBER(12)  NOT NULL,
 Account_ID				    		  NUMBER(16)  NOT NULL,
 Account_Type_Code	                  VARCHAR(16) NOT NULL,
 Branch_ID				    		  NUMBER(16)  NOT NULL,
 Trans_Type_Code                VARCHAR(5),
 Trans_Date/Time                DATE NOT NULL,
 Trans_Amount				  	NUMBER(12,2),
 Dr/Cr 						   	VARCHAR(3),
 Trans_Other_Party				VARCHAR(15),			
 
 CONSTRAINT Type_code_pk 
						 PRIMARY KEY(Trans_ID,Trans_Date/Time),
 CONSTRAINT account_ID_fk 
						 FOREIGN KEY(Account_ID)
						 REFERENCES Account(Account_ID),
 CONSTRAINT account_type_fk 
						 FOREIGN KEY(Account_Type_Code)
						 REFERENCES Account_Type(Account_Type_Code),
 CONSTRAINT trans_type_fk 
						 FOREIGN KEY(Trans_Type_Code)
						 REFERENCES Account_Type(Trans_Type_Code),
 	
 CONSTRAINT branch_id_fk 
						 FOREIGN KEY(Branch_ID)
						 REFERENCES Branch(Branch_ID)
 CONSTRAINT crdr_check
 					  	 CHECK(Dr/Cr IN ('Debit','Credit'))
 		
 );
	
	
 
 CREATE TABLE Branch
 (Branch_ID	  			    				 NUMBER(12)  NOT NULL,
  Branch_Name			      				 VARCHAR(12),
  Branch_Address		    				 VARCHAR(15),
  Branch_Phone			    				 VARCHAR(9),
  CONSTRAINT branch_id_pk 
						 PRIMARY KEY(Branch_ID)
  );
  
 
 CREATE TABLE Account
 (Account_ID		 		         NUMBER(12)  NOT NULL,
 Account_Type_Code			         VARCHAR(16) NOT NULL,
 Branch_ID					         NUMBER(16)  NOT NULL,
 Current_Balance			         NUMBER(12,2),
 Date/Time_last_summary 	         DATE NOT NULL,
 Balance_Last_Summary		         NUMBER(12,2),
 Interest_Rate				         Number(4,2),
 Monthly_Service_Charge		         NUMBER(4,2),			
 CONSTRAINT account_Id_pk 
						 						 PRIMARY KEY(Account_ID,Branch_ID,Account_Type_Code),
 CONSTRAINT branch_Id_fk
                         FOREIGN KEY(Branch_ID)
						 						 REFERENCES Branch(Branch_ID), 
 CONSTRAINT account_type_fk 
						 						 FOREIGN KEY(Account_Type_Code)
												 REFERENCES Account_Type(Account_Type_Code)
 );
 
	
 CREATE TABLE Customer
 (Cust_ID	  			      NUMBER(12)  NOT NULL,
  Cust_First_Name		    VARCHAR(20),
  Cust_Last_Name		    VARCHAR(20),
  Cust_Address			    VARCHAR(15),
  Cust_Phone			    VARCHAR(9),
  CONSTRAINT cust_id_pk 
						 PRIMARY KEY(Cust_ID)
						 ON DELETE CASCADE
  );					 
 
 CREATE TABLE Biller
 (Biller_Code			 VARCHAR(12)  NOT NULL,
  Biller_Description	 VARCHAR(20),
  Biller_Address		 VARCHAR(15),
  Biller_Phone			 VARCHAR(12),
  Biller_Fax			 VARCHAR(12),
  Biller_Password		 VARCHAR(15),
  CONSTRAINT biller_code_pk 
						 PRIMARY KEY(Biller_Code)
						 ON DELETE CASCADE
  );
  
 CREATE TABLE Bill
 (Biller_Code			    VARCHAR(12)  NOT NULL,
  Cust_ID				    VARCHAR(20),
  When_Bill_Posted		    DATE NOT NULL,
  Amount				    NUMBER(6,2),
  CONSTRAINT bill#_pk 
						 PRIMARY KEY(Biller_Code,Cust_ID,When_Bill_Posted),
  CONSTRAINT Cust_Id_fk
                         FOREIGN KEY(Cust_ID)
						             REFERENCES Customer(Cust_ID) 
 
  );
  
  
 CREATE TABLE Card
 (
   Cust_ID	  			    NUMBER(12)  NOT NULL,
   Account_ID			    NUMBER(12)  NOT NULL,
   Account_Type_Code		VARCHAR(16) NOT NULL,
   Branch_ID				NUMBER(16)  NOT NULL,
   Card_Category			VARCHAR(8)  NOT NULL,
   Credit_Card_Type			VARCHAR(10)
   When_Card_Issued			DATE NOT NULL,
   Expiry_date				DATE NOT NULL,
   Pin_Number				VARCHAR(6),
   Credit_Card_Limit		Number(7,2),
   			
   CONSTRAINT card_pk 
						 PRIMARY KEY(Cust_ID,Account_ID,Account_Type_Code,Branch_ID,
						 Card_Category),
   CONSTRAINT branch_Id_fk
                         FOREIGN KEY(Branch_ID)
						 						 REFERENCES Branch(Branch_ID), 
   CONSTRAINT account_type_fk 
						 							FOREIGN KEY(Account_Type_Code)
												  REFERENCES Account_Type(Account_Type_Code), 
   CONSTRAINT Cust_Id_fk
                         FOREIGN KEY(Cust_ID)
						 						 REFERENCES Customer(Cust_ID),
   CONSTRAINT account_ID_fk 
						 						 FOREIGN KEY(Account_ID)
						 						 REFERENCES Account(Account_ID),
   CONSTRAINT card_check 
						 CHECK(Card_Category IN ('BankCard', 'CreditCard')),
   CONSTRAINT credit_card_check
   			  	 CHECK(Card_Card_Type IN ('VISA', 'MASTERCARD','NOTHING')) 		   						 						 
 );
 
 
 CREATE TABLE Account_Open
 (Cust_ID	  			       NUMBER(12)  NOT NULL,
  When_Account_Opened	       DATE NOT NULL,
  Branch_ID				       NUMBER(16)  NOT NULL,
  Account_Type_Code		       VARCHAR(16) NOT NULL,
  CONSTRAINT acc_open_pk 
						 PRIMARY KEY(Cust_ID,Account_ID,Account_Type_Code,Branch_ID),
  CONSTRAINT branch_Id_fk
                         FOREIGN KEY(Branch_ID)
						 						 REFERENCES Branch(Branch_ID), 
  CONSTRAINT account_type_fk 
						 						 FOREIGN KEY(Account_Type_Code)
						 						 REFERENCES Account_Type(Account_Type_Code), 
  CONSTRAINT Cust_Id_fk
                         FOREIGN KEY(Cust_ID)
												 REFERENCES Customer(Cust_ID),
  CONSTRAINT account_ID_fk 
						 						 FOREIGN KEY(Account_ID)
						 						 REFERENCES Account(Account_ID)
 
  );	
