One of the Sustainable Development Goal (SDG) propel by the United Nation to promote for a more sustainable future for all is the importance of integration of urban agriculture. This is not a just the typical rural agricultural activities but instead create innovative agriculture technologies that involve the public to participate. The examples of recent project in these forms are rooftop garden, greenhouse, vertical farming, and edible green walls.

The Dean of Kulliyyah of Information and Communication Technology (KICT) has reserved a huge plot of unused land beside Block B as the KICT Garden. He has even invited the staffs and students to start their own urban agriculture projects. He has also consulted with your team to design and produce a database application to manage the projects in KICT Garden. This system will be called KICT Garden Management System (KGMS). Projects can be proposed by any staffs or students in the International Islamic University (IIUM) but not open to the public. However, approval from the KICT Office must be sought before running these projects.

KICT Office wishes to build a database application that will register the team members, project details, financial assistant and materials required for the project. The list of goods produced and sold to the IIUM community must also be recorded. Every Friday in IIUM, there will be a market open to the public to sell the goods from our KICT Garden. A coordinator has been appointed by KICT Office to take the lead in developing this application. The database application system will have to fulfill the following requirements:

• Allow the team members to register their team and set up their projects. The maximum number of TWO projects per team can be created per year.
• A staff/student can join a different team to set up and register for a different project. However, each staff/student can be part of a maximum THREE teams at a time.
• These projects must be approved by KICT Office before allowing the team members to ask for financial assistant. The maximum of RM 1000 can be allocated for each project.
• For each project allow the team members to provide the list materials required by the project and its cost of purchased.
• For each week, record the goods produce from each project and the price that they were sold in the market.
• The team can modify their project details. For example, change the team members, change the project duration, project plot location in the garden, materials used and the good produced.
• Track the status of each project i.e., approval by the KICT Office, unused budget allocation, project completion progress, number of goods produced and sold in the market.
• At the end of each project undertaken in KICT Garden, the team members can leave feedback (with scoring mechanism) to the KICT Office in term of their satisfaction with the overall project support i.e., the significant of the project undertaken, the adequacy of the time allocated, the impact of the project to the community and etc.

It is your group duty to propose, design and implement a database that can address the above requirements as well as store the necessary information related to KGMS.

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 also 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.