COMP353 Department of Computer Science and Software Engineering Assignment 2 Question

COMP353 Question 1 (50 marks) 

A Non-Profit Local Organization called NPLO that is concerned with the welfare of poor people in your community would like you to design a database to represent all the data that they need to store for their operations. The following are the requirements specified by the company:

NPLO accepts donations from people. Donations could be either money or products. Employees of NPLO process the donated products and sell them to the local people who are interested in them. Part of the donations are used to cover the expenses to run the organization, and the rest is used to help poor people in the community. The database contains information about Donors, Donations, Products, Sales, SalesItems, Employees, Expenses, Categories of donated items, and Donor Interests.. 

Member is the relation that holds information about people known to the application. Every member has an ID, first-name, last-name, middle-initial, date-of-birth, address, gender, phone- number, email-address, social-security-number, and start date of membership. A member could be either a donor, a client, or an employee.

Donations is the relation that holds information about each donation. Every donation has an ID, the donor ID, date of the donation, type of donation, and amount of donation.

Products is the relation that holds information about every item donated. Every product has an ID, the description of the product, the category of the product, the donation date of the product, the selling price of the product, the weight (in Kilogram) of the product, and a flag indicating whether the product is in stock or has been sold. 

Sales is the relation that holds the information about the items sold. Every sale has an ID, date of the sale, amount of the sale, total weight (in kilogram) of the sale, and the delivery fee if the sale is to be delivered. 

SalesItems is the relation that holds the information about the items sold for every sale.

Categories is the relation that holds information about the category of the items donated. Every category has a category ID, and a description of the category. Every donated item belongs to only one category. Many donated items can belong to the same category. 

DonorInterests is the relation that holds information about the interests of every donor to the organization. A donor can have an interest in zero, one or many categories.

Employees is the relation that holds the information about every employee working for the organization. In addition to the properties of Persons, every employee has a job-title (president, vice-president, cashier, other), salary. If the salary is zero or null means that the employee is a volunteer. 

Expenses is the relation that holds the information about all the expenses paid by the organization. Every expense has an ID, the ID of the president who approved the expense, the date of the payment of the expense, the amount of the expense, the type of the expense, and the description of the expense. The type could be either rent, bill payment, or charity payment. Only president of the organization can approve an expense.

Some information about how this organization runs: 

• A donation type can be either money or products. 
• If the donation is products, then the estimated selling price of all the donated products is registered. 
• A donor can have many donations throughout the year. 
• One sale can include one or many products. 
• The total amount of all products sold in one sale is registered. 
• For every new product added to the system, the default value for its in-stock flag is set to True. 
• Every item that is sold, its corresponding in-stock flag is set to False. 
• A product can be sold only if its in-stock value is true. 
• A sale could be delivered only to members that are registered in the system. 
• Every sale that needs to be delivered, its associated client to be delivered to should be registered. 
• If the sale is to be delivered, then its associated delivery fee should be registered and set based on the total weight of the items to be delivered. Delivery fee is calculated based on the rate of 6.50$ per kilogram. The delivery fee should also be added to the total amount of sales.

These are the minimum requirements for your application. You may need to add additional entities and attributes to meet the requirements for the queries in Part II.

Part I (30 points): 

With this information, do the following initial steps in your database design process: 

1. [15 Points] Develop an ER diagram to represent the conceptual database scheme for the above "organization".
2. [2 Points] In the diagram, mark the various constraints (keys, cardinalities of the relationships, etc.). Identify any constraints that are not captured by the ER diagram.
3. [10 Points] Convert your ER diagram into a relational database scheme. Make refinements to your scheme if possible. Identify the primary keys and the foreign keys in the relational schemes, and hence note the referential integrity constraints in the scheme.
4. [3 Points] How would your design change if the expense needs the approval of both the president and the vice president of the organization?

Part II (20 points): 

Express the following queries in SQL: ( Your queries should work with any valid database instance.) 

1. List the details of all the members that are volunteer employees and donors as well and that they have made at least two donations. Details include member's ID, first-name, last- name, middle-initial, date-of-birth, address, gender, phone number, email-address, social- security-number, job-title, and the start date of membership. 
2. List the details of all the members who have donated items in at least 5 different categories, Details include member's ID, first-name, last-name, middle-initial, number of different categories donated. Results should be displayed sorted in descending order by number of different categories donated, then by first name, then by last name. 
3. Give a report that provides the total expenses paid to rent, and the total expenses paid to charity in the months of Jan to June of 2025.
4. List the details of all the members who have an interest in the items donated between June 1stof 2025 and June 7thof 2025. Details include member's ID, first-name, last- name, middle-initial, email-address, name of category of interest of the donated item. Results should be displayed sorted in ascending order by first name, then by last name, then by name of donated category. 
5. Give a monthly report of the sales that have been delivered to members in 2025, For every month of 2025, details include number of products delivered, average price of all products delivered, average weight of the products delivered, and average delivery fee of the products delivered. Results should be displayed in descending order by the number of products delivered.