DATABASE SYSTEM:

This is a data dictionary the value range for the attributes of the tables are not specified.

specify value range for the attributes of the tables:

drop table team; CREATE TABLE team ( teamno varchar(4), members int not null, projects int not null check(projects >0 and projects

drop table memberinfo; CREATE TABLE memberinfo( matricno varchar(10) not null, fname varchar(20) not null, lname varchar(20) , sex char(1) check(sex IN ('M','F')), teamno varchar(4), CONSTRAINT FK_teammemberinfo FOREIGN KEY (teamno) REFERENCES team on delete set null ); Insert into MemberInfo values ('191668','Ahmed','Ali','M','T003'); Insert into MemberInfo values ('163556','Ramiza','Noori','F','T002'); Insert into MemberInfo values ('191885' ,'Alishba','Hannan','F','T001'); Insert into MemberInfo values ('198553','Reda','Nusair','M','T003'); Insert into MemberInfo values ('183675','Sarwana','Nasrallah','F','T004'); Insert into MemberInfo values ('181456','Subhi','Safi','M','T001'); Insert into MemberInfo values ('162554','Yasin','Shareef','M','T003'); Insert into MemberInfo values ('173557','Aroosa','Hussein','F','T004'); Insert into MemberInfo values ('191668','Ahmed','Ali','M','T001'); Insert into MemberInfo values ('191885','Alishba','Hannan','F','T005'); Insert into MemberInfo values ('191668','Ahmed','Ali','M','T002'); Insert into MemberInfo values ('163556','Ramiza','Noori','F','T005'); Insert into MemberInfo values ('181336','Kateb','Shakoor','M','T003'); Insert into MemberInfo values ('183675','Sarwana','Nasrallah','F','T002');

drop table project; CREATE TABLE project( projectno varchar(4), teamno varchar(4), projectname varchar(50) not null unique, requirements varchar(200), approval char(1) check(approval IN ('Y','N')), year int default(2020), primary key(projectno), CONSTRAINT FK_teamproject FOREIGN KEY (teamno) REFERENCES team on delete set null ); Insert into Project values ('P001','T002','VegetablesProject','Gloves','Y',2020); Insert into Project values ('P002','T003','FruitsProject','Shovel','Y',2020); Insert into Project values ('P003','T005','HerbsPoject','Hand trowel','N',2019); Insert into Project values ('P004','T004','SpiceProject','Garden forks','Y',2020); Insert into Project values ('P005','T002','FlowersProject','Spade','N',2017); Insert into Project values ('P006','T001','PlantsProject','Rake','Y',2020); Insert into Project values ('P007','T003','TreesProject','Hoe','N',2018); Insert into Project values ('P008','T001','SeedsProject','Watering wand','N',2020);

drop table FinancialAssistant; CREATE TABLE FinancialAssistant( projectno varchar(4), moneysupport int not null check(moneysupport >=0 and moneysupport

drop table FridayMarket; CREATE TABLE FridayMarket( marketno varchar(4), projectno varchar(4) not null unique, product varchar(100) not null, price float not null, primary key(marketno), CONSTRAINT FK_projectFridayMarket FOREIGN KEY (projectno) REFERENCES project on delete set null ); Insert into FridayMarket values ('M001','P001','Leafy vegetables',10.25); Insert into FridayMarket values ('M002','P002','Fruits',15.5); Insert into FridayMarket values ('M003','P004','Spices',5.99); Insert into FridayMarket values ('M004','P006','Herbs',8);

drop table TeamFeedback; CREATE TABLE TeamFeedback ( teamno varchar(4) not null, viewdate date not null, teamfeedback int check(teamfeedback >0 and teamfeedback

Business Scenario: KICT Garden Management System (KGMS)

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.

Table Column Data Type Value Range Precision Scale Primary key Nullable Default Comment Length Description Team number in a number of teams 4 TEAM TEAMNO VARCHAR2 MEMBERS NUMBER 0 PROJECTS NUMBER 0 MEMBERINFO MATRICNO VARCHAR2 10 Team member names in a 22 team Number of projects for a 22 team Id number of team member Team member firstname Team member last name Gender of members Members assigned to teams FNAME VARCHAR2 20 LNAME VARCHAR2 20 SEX CHAR 1 TEAMNO VARCHAR2 4 D E G PROJECT PROJECTNO VARCHAR2 TEAMNO VARCHAR2 PROJECTNAM E VARCHAR2 Project name as of number 4 type Team name assigned to 4 each project Project name that has been assigned to that particular 50 team Requirement needed for the team to work 200 on Approval of the 1 project Project undertaken 22 year REQUIREMEN TS VARCHAR2 APPROVAL CHAR YEAR NUMBER 0 -2020 - FINANCIALASSIS TANT PROJECTNO VARCHAR2 MONEYSUPPORT NUMBER FRIDAYMARKET MARKETNO VARCHAR2 Prject name that money has 4 been granted Money that has been granted 22 for a project Market name that consists of 4 products Project name that has been assigned a particular 4 market Product name that has been given to a team 100 to work on 126 Price of product PROJECTNO VARCHAR2 PRODUCT PRICE VARCHAR2 FLOAT 126 - TEAMFEEDBA CK TEAMNO VARCHAR2 Team name that their project is assigned to 4 review Date that the project has 7 been reviewed Feedback earned for that 22 team VIEWDATE DATE TEAMFEEDBA CK NUMBER 0