Normalize the database tables, and explain how it was normalized Please Fix the mistakes, show the results
Question:
Normalize the database tables, and explain how it was normalized
Please Fix the mistakes, show the results both in screenshots of tables and by pasting the code after editing it.
Create table Volunteers (
Phone_Number int not null,
Volunteer_id int not null,
first_name varchar(50) not null,
Last_name varchar(50) not null,
PRIMARY KEY (Volunteer_id),
FOREIGN KEY (Supervise) REFERENCES Volunteers (Volunteer id);
FOREIGN KEY (RankNo) REFERENCES RankofCharities (RankNo)
);
/*
(IoEC=Index of Charities)
*/
Create table IoEC (
Name varchar(50) not null,
Gov_Id int not null,
Website_Link varchar(50) not null,
PRIMARY KEY (Gov_Id),
FOREIGN KEY (RankNo) REFERENCES RankofCharities (RankNo),
FOREIGN KEY (Donation_Refrence_Number, Gov_Id, IBAN) REFERENCES DonationtoIndex (Donation_Refrence_Number, Gov_Id, IBAN)
);
Create table Donors (
IBAN varchar(50) not null,
first_name varchar(50) null,
Last_name varchar(50) null,
Phone_Number int null,
PRIMARY KEY (IBAN),
FOREIGN KEY (RankNo) REFERENCES RankofCharities (RankNo),
FOREIGN KEY (Donation_Refrence_Number, Gov_Id, IBAN) REFERENCES DonationtoIndex (Donation_Refrence_Number, Gov_Id, IBAN)
);
Create table Charities (
Telephone_Number int,
Gov_Id int not null,
Address varchar(50) null,
Charity_Name varchar(50) not null,
IBAN varchar(50) not null,
PRIMARY KEY (Gov_Id),
FOREIGN KEY (RankNo) REFERENCES RankofCharities (RankNo),
FOREIGN KEY (Gov_Id) REFERENCES IoEC (Gov_Id)
);
Create table RankofCharities (
RankNo int not null,
Final_impact int null,
cost_effectiveness int null,
Percantge_of_spending_on_operational_costs_out_of_total_funds int null,
overall_score_out_of_100 int null,
transparency int null,
average_spending_per_riyal_on_projects int null,
PRIMARY KEY (RankNo),
FOREIGN KEY (Gov_Id) REFERENCES IoEC (Gov_Id)
);
Create table DonationtoIndex (
Donation_Refrence_Number int not null,
PRIMARY KEY (Donation_Refrence_Number, Gov_Id, IBAN),
FOREIGN KEY (IBAN) REFERENCES Donors (IBAN),
FOREIGN KEY (Gov_Id) REFERENCES IoEC (Gov_Id)
);
Create table DonationRefNo (
Donation_Refrence_Number int not null,
PRIMARY KEY (Donation_Refrence_Number, Gov_Id, IBAN, DonationRefNo),
FOREIGN KEY (Donation_Refrence_Number, Gov_Id, IBAN) REFERENCES DonationtoIndex (Donation_Refrence_Number, Gov_Id, IBAN)
);
CharitiesnDonors (
PRIMARY KEY (Gov_Id, IBAN),
FOREIGN KEY (IBAN) REFERENCES Donors(IBAN),
FOREIGN KEY (Gov_Id) REFERENCES Charities(Gov_Id)
);
Accounting Information Systems
ISBN: 978-1133935940
10th edition
Authors: Ulric J. Gelinas, Richard B. Dull