Question
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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started