Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

please i want hand draw A Non - Profit Local Organization called NPLO that is concerned with the welfare of poor people in your community

please i want hand draw
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 processes the donated products and sell them to the local people who are interested in them. Part of the donations is 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 and Expenses.
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 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, and amount of the sale.
SalesItems is the relation that holds the information about the items sold for every sale.
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.
Asale 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:
a)[15 Points] Develop an ER diagram to represent the conceptual database scheme for the above organization.
b)[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.
c)[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.
d)[3 Points] How would your design change if the expense needs the approval of both the president and the vice president of the organization?
please i want hand draw

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Concepts International Edition

Authors: David M. Kroenke

6th Edition International Edition

0133098222, 978-0133098228

More Books

Students also viewed these Databases questions