CASE STUDY MHA

Create table Sponsor (SID char(3), Name char(20), Saddress char(20), Sphone char(10), Primary key (SID));

Create table Tract (TractId char(2), Location char(10), M_income number(8,2), primary key(TractID));

Create table Donation_M (DonID char(3), amount number(8,2), TractID char(2), SID char(3), primary key (DonID), foreign key (TractID) references Tract, foreign key (SID) references Sponsor);

Create table Department (DID char(2), D_name char(20), budget number(8,2), phone char(10), TractID char(2) not null, primary key (DID), foreign key (TractID) references Tract);

Create table Building (B_name char(15), B_address char(20), Hf char(1) check (Hf in ('Y','y','N','n')), TractID char(2), B_Size number(2), primary key (B_name), foreign key (TractID) references Tract);

Create table Unit (UnitID char(3), B_name char(15), N_bdr number(1), rent number(6,2), primary key (B_name, UnitID), foreign key (B_name) references Building on delete cascade);

Create table Apartment (UnitID char(3), B_name char(15), a_floor number(2), Primary key (B_name, UnitID), foreign key (B_name, UnitID) references Unit on delete cascade);

Create table Townhouse (UnitID char(3), B_name char(15), Charges number(6,2),B_yard char(1) check (B_yard in ('Y','y','N','n')), Primary key (B_name, UnitID), foreign key (B_name, UnitID) references Unit on delete cascade);

Create table Tenant (TID char(5), T_name char(20), T_phone char(10), T_income number(8,2), UnitID char(3), B_name char(15), primary key (TID), foreign key (B_name, UnitID) references Unit);

Create table Employee_R (eid char(9), e_name char(20), mid char(9), e_did char(2) not null, primary key (eid),Foreign key (mid) references Employee_R (eid), foreign key (e_did) references Department (did));

Create table Is_maintained (eid char(9), B_name char(15), primary key (eid,B_name), foreign key (eid) references Employee_R on delete cascade, foreign key (B_name) references Building);