Question 1 (50 marks) You are provided below a design of a relational database for a non-profit organization that is con

[answerhappygod]

Question 1 (50 marks)
You are provided below a design of a relational database for anon-profit organization that is concerned with the welfare of poorpeople in their community. It accepts donations from people.Donations could be either money or products. It processes the itemsand sell them to the local people who are interested in them. Partof the donations is used to cover the expenses to run theorganization and the rest is used to help poor people in thecommunity. The database contains information about Branches,Donors, Donations, Products, and Sales.
Branches is the relation that holds information about eachbranch of the organization. Every branch has an ID, name, address,city, postalCode, province, phone, and email.
Donors is the relation that holds information about the peoplewho donate to the organization. Every donor has an ID, first-name,last-name, middle-initial, date-of-birth, gender, social securitynumber, address, city, postalCode, province, phone, and email.
Donations is the relation that holds information about eachdonation. Every donation has a branch ID, an ID that is anincremental number for each branch, the donor ID, date of thedonation, type of donation, and amount of donation.
Products is the relation that holds information about every itemdonated. Every product has a branch ID, an ID that is anincremental number for each branch, the description of the product,the donation date of the product, and the selling price of theproduct. The branch ID indicates at which branch the product hasbeen donated.
Sales is the relation that holds the information about the itemssold. Every sale has branch ID, an ID that is an incremental numberfor each branch, date of the sale, and amount of the sale.
Some information about how this organization runs:
The organization has many branches.
Each donation is done at a specific branch.
A donation type can be either money or products.
If donation at a branch is products, then the estimated sellingprice of all the donated
products at the branch is registered.
A donor can have many donations throughout the year.
A donor can donate at one or many branches.
One sale in each branch can include one or many products.
The total amount of all products sold in one sale in each branchis registered.
Every item that is sold in each branch is removed from theproducts relation.
Each branch has its own inventory.
Each branch has its own sales.
The database schema is as follows, where the underlinedattribute(s) in each relation collectively form the primary key ofthat relation:
Branches (bID, bName, address*, city, postalCode, province,phone, email)
Donors (donorID, firstName, lastName, middleInitial,dateOfBirth, gender, SSN, address*,
city, postalCode, province, phone, email)
Donations (bID, dID, donorID, date, type, amount)
Products (bID, pID, description, date, price)
Sales (bID, sID, date, amount)
* Address consists of civic number.
Part I (25 points):
a) Write SQL “CREATE TABLE” statements for the aboveschema using appropriate data
types for the various attributes. [10 points]
b) A declaration to alter the Branches relation schema bydeleting the attribute email. [2 points]
c) A declaration to alter the Branches relation schema byadding attribute country. Use Canada as the default value for theattribute. [2 points]
d) Provide three INSERT statements with data that willpopulate the table Branches [3 points]
e) Provide SQL statements that delete all data that youpopulated in table Branches. [3 points]
f) Provide several SQL statements that delete all tablesthat you created in the database. [5 points]
Part II (25 points):
Express the following queries in SQL:
a) List the information of all the Branches of theorganization that are located in the
province of Québec. Information includes branch ID, branchname, address, city, postal code, and phone.
b) For all the Donors who lives in the province of Québecand have at least five donations in 2022, give the total amount ofdonations they donated in 2022. Result should be displayed inascending order of total amount donated.
c) Give a report of the donations done at all the branchesin 2021. The report includes branch name, branch ID, city, totaldonations at the branch in 2021. The report should be displayed indecreasing order of the total donations in each branch.
d) Give a monthly report of sales for all branches of thecity of laval for 2021. The report includes for every month in2021, the total sales of the month.
e) Give a list of all the products that have been donatedfor more than one year and are not sold at every branch in the cityof Montréal. The list should include the branch name, the productID, the description, the date of the donation, and the sales amountof the product. The result should be displayed in increasing orderof branch name, then product ID.