Database management system exercise if you don't know don't answer, thank you.
KICT Waste Recycling Management System (K-WRMS) Garbage is a global problem. Every household in the world produce huge amount of waste such as plastics, glass bottles, boxes, and aluminum cans daily. If we don't recycle and reuse these materials, we can eventually destroy the natural habitats including the marine life. According to the report by the Organization for Economic Cooperation and Development (OECD), only 16% of the waste produce by the world get recycled every year. For this reason, more effort needed to boost the recycling effort. The Sejahtera Center for Sustainability and Humanity (SCSH) was established by the International Islamic University Malaysia (IIUM) to spearhead the environmental protection in Gombak. In the spirit to encourage the recycling effort, Kulliyyah of ICT (KICT) in collaboration with SCSH has initiated a pilot waste recycling program to reward points to KICT students when they recycle their waste in campus. The objective of this program is to allow a more coordinated recycling process in the campus. KICT have installed many large bins at strategic areas around KICT for students to deposit their waste for recycling. There are five categories of bins that are available: cans, papers, boxes, plastics, and e-waste. Students must sort their waste accordingly and deposit them into the right bins. For every item deposited, student will earn reward points. These points can be used to redeem fruits and vegetables from KICT garden. The statistics for the waste disposal and redemption of reward points must be made available for reporting and further analysis. The granularity of the reporting should be done at individual (i.e., for a particular student) and group level (e.g., for all year 1, 2, 3 and 4 students respectively) on a daily, monthly, and yearly basis. The Dean of KICT had consulted with the IT coordinator to design and produce a database application to manage the waste recycling program at KICT. 2 The system will be called KICT Waste Recycling Management System (K-WRMS). The Dean wishes to build a database application that can register students that are interested in this program, deposit recyclable waste, earn and redeem reward points, update catalog from KICT garden, and to provide feedback from the participants for program improvement. The IT coordinator will take the lead in the development of this system. The database application system will have to fulfill the following requirements: - Allow KICT students to register for the waste recycling program. - For each student, allow them to deposit recyclable waste into the correct bins. The details of every waste disposal must be recorded in the system: the date, time, location, type, quantity (can also use weight as an alternative measurement). - At the end of every waste disposal effort, calculate the reward points earned by the student. The maximum number of times that a student can make a waste deposit is not more than 7 per week. - Accumulated reward points can be used to redeem products (i.e., fruits and vegetables) from KICT garden but subject to their availability and having enough points. The details of every product redemption must be record in the system: the date, time, product redeemed, reward points used, and remaining reward points. - Allow the catalog from KICT garden to be updated. The addition and removal of products, its availability, quantities, and rewards points required for redemption. - Generate statistical reports at individual (i.e., for a particular student) and group level (e.g., for all year 1, 2, 3 and 4 students respectively). This can be useful to gauge the quantities of recycled waste between students or between year of study on multiple time frames such as daily, weekly, monthly, and yearly. - Provide feedback for improvement. The students participating in this program can leave feedback (with a scoring mechanism in place) to KICT in term of the effectiveness of overall program objectives i.e., the rationale of the recycling program, the effectiveness of waste disposal platform, the 3 attractiveness of reward points, the quality of the reports generated, the impact of the program to the University and so on. Assume that you are the IT coordinator and assigned to this project. Your team is responsible to propose, design and implement a database application that can address the above requirements as well as store the necessary information related to K-WRMS. The tasks breakdown for your project are detailed out below: PROJECT GUIDELINES: Project deliverables: Task 1 1. Identify all the entities and attributes. You can add necessary attributes to the identified entities. 2. Specify the business rules based on your scenario, draw the ER/EER diagram and Relational Schema to show the relationships between the entities and attributes. Schema should be normalized. 3. Create the data dictionary for the above schema. Task 2 Write a script to create tables and sequences based on the ER/EER diagram. Populate each table with not more than 15 records (practical representation of the business scenario). The objective is to ensure that you understand how to read an ER/EER diagram and know how to create tables and table constraints. Please ensure that your script contains these items: - Drop table statements. - Create table statements complete with primary key and foreign key declarations (make sure that you give a proper name to each constraint). - Insert record statements. Task 3 1. Write any five (5) SQL queries of your own but you must include the use of joins and functions. Example of SQL queries are given below: a. Show all the year 1 students, waste disposal details, and reward points details. Show ONLY the student ID, student name, deposit ID, date, time, type, quantity, before and after reward points. b. Show all the product redemption details that have used up for more than 500 reward points between May 2022 and April 2022. Show ONLY the redemption ID, date, time, product redeemed, quantity, before and after reward points. c. Find the top TEN (10) students with the MOST number of recycled waste that were disposed (referring to the quantities of recycled items and not the number of deposits made) between May 2022 and April 2022. d. Based on the feedback evaluation forms, list all evaluation ID and its total score (by adding up the individual score in each of the evaluation categories) and sort these records in descending order
