DROP TABLE Contribution;                                
DROP TABLE DonorTypeTarget;                             
DROP TABLE DonorTarget;                                 
DROP TABLE CampusTarget;                                
DROP TABLE CommTarget;                                  
DROP TABLE CampusProgram;                               
DROP TABLE CommitteeAssign;                             
DROP TABLE Committee;   
DROP TABLE Program;                                     
DROP TABLE Campus;                                      
DROP TABLE Donor;                                       
DROP TABLE Donor_Types;                                 
                          


CREATE TABLE Committee (                                
        memberID        NUMBER (5)      PRIMARY KEY,    
        memberName      CHAR (30)       NOT NULL
);        
                                
create table donor_types (S_ROOT:[MROBBERT]SAMPLE.TXT;1            
        donorType       char (1)        primary key,               
        description     char (30)       not null                   
);                                                                 
                                                                   
CREATE TABLE CommitteeAssign (                                     
        memberID        number (5)      not null,                  
        year            date            not null,                  
        donorType       char            not null,                  
        primary key(memberID,year),                                
        foreign key(memberID) references Committee(memberID),      
        foreign key(donorType) references Donor_Types(donorType)   
);                                                                 

                                                                    
CREATE TABLE Program (                                              
        programID       NUMBER (5)      PRIMARY KEY,                
        programName     CHAR (30)       NOT NULL                    
);                                                                  
                                                                    
CREATE TABLE Campus (                                               
        campusID        NUMBER (5)      PRIMARY KEY,                
        campusName      CHAR (30)       NOT NULL                    
);                                                                  
                                                                    
CREATE TABLE Donor (                                                
        donorID         NUMBER (5)      PRIMARY KEY,                
        donorName       CHAR (30)       NOT NULL,                   
        donorType       CHAR            NOT NULL,                   
        foreign key(donorType) references Donor_Types(donorType)    
);                                                                  
                                                                                                                                  
                                                         
CREATE TABLE CampusProgram (                                
        campusID        NUMBER (5)      NOT NULL,           
        programID       NUMBER (5)      NOT NULL,           
        year            date            not null,           
        primary key(campusID,programID,year),               
        FOREIGN KEY (campusID)                              
                REFERENCES Campus (campusID)                
                ON DELETE CASCADE,                          
        FOREIGN KEY (programID)                             
                REFERENCES Program (programID)              
                ON DELETE CASCADE                           
);                                                          
                                                            
CREATE TABLE CommTarget (                                   
        memberID        NUMBER (5),                         
        quarter         DATE,                               
        amount          NUMBER (6)      NOT NULL,           
        PRIMARY KEY (memberID, quarter),                    
        FOREIGN KEY (memberID)                              
                REFERENCES Committee (memberID)             
                ON DELETE CASCADE,                          
        CHECK (quarter = TRUNC (quarter, 'Q')),             
        CHECK (amount >= 0)                                 
);                                                          
                                                            
                                                                 
CREATE TABLE CampusTarget (                                      
        campusID        NUMBER (5),                              
        quarter         DATE,                                    
        amount          NUMBER (6)      NOT NULL,                
        PRIMARY KEY (campusID, quarter),                         
        FOREIGN KEY (campusID)                                   
                REFERENCES Campus (campusID)                     
                ON DELETE CASCADE,                               
        CHECK (quarter = TRUNC (quarter, 'Q')),                  
        CHECK (amount >= 0)                                      
);                                                               
                                                                 
CREATE TABLE DonorTarget (                                       
        donorID         NUMBER (5),                              
        year            DATE,                                    
        amount          NUMBER (6)      NOT NULL,                
        PRIMARY KEY (donorID, year),                             
        FOREIGN KEY (donorID)                                    
                REFERENCES Donor (donorID)                       
                ON DELETE CASCADE,                               
        CHECK (year = TRUNC (year, 'YYYY')),                     
        CHECK (amount >= 0)                                      
);                                                               
                                                                 
                                                                 
CREATE TABLE DonorTypeTarget (                                         
        donorType       CHAR            not null,                      
        quarter         DATE            not null,                      
        amount          NUMBER (6)      NOT NULL,                      
        PRIMARY KEY (donorType, quarter),                              
        foreign key(donorType) references Donor_Types(donorType),      
        CHECK (quarter = TRUNC (quarter, 'Q')),                        
        CHECK (amount >= 0)                                            
);                                                                     
                                                                       
CREATE TABLE Contribution (                                            
        contribID       NUMBER (5)      PRIMARY KEY,                   
        contribDate     DATE            NOT NULL,                      
        donorID         NUMBER (5)      NOT NULL,                      
        programID       NUMBER (5)      NOT NULL,                      
        memberID        NUMBER (5)      NOT NULL,                      
        amount          NUMBER (6)      NOT NULL,                      
        FOREIGN KEY (donorID)                                          
                REFERENCES Donor (donorID)                             
                ON DELETE CASCADE,                                     
        FOREIGN KEY (programID)                                        
                REFERENCES Program (programID)                         
                ON DELETE CASCADE,                                     
        FOREIGN KEY (memberID)                                         
                REFERENCES Committee (memberID)    
                ON DELETE CASCADE,                                          
        CHECK (amount > 0)                               
);