EssayproSolutions

DATABASE SYSTEMS:

Normalize all the tables…

drop table team;
CREATE TABLE team (
teamno varchar(4),
members int not null,
projects int not null check(projects >0 and projects <3),
primary key(teamno)
);
Insert into Team values (‘T001’,3,2);
Insert into Team values (‘T002’,3,2);
Insert into Team values (‘T003’,4,2);
Insert into Team values (‘T004’,2,1);
Insert into Team values (‘T005’,2,1);

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 <=1000),
CONSTRAINT FK_projectFinancialAssistant FOREIGN KEY (projectno)
REFERENCES project on delete set null
);
Insert into FinancialAssistant values (‘P001’,855);
Insert into FinancialAssistant values (‘P002’,785);
Insert into FinancialAssistant values (‘P004’,990);
Insert into FinancialAssistant values (‘P006’,760);

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 <6),
CONSTRAINT FK_memberinfoTeamFeedback FOREIGN KEY (teamno)
REFERENCES team on delete set null
);
Insert into TeamFeedback values (‘T002′,to_date(’26-Oct-2020′,’DD-MON-YYYY’),5);
Insert into TeamFeedback values (‘T003′,to_date(’02-Nov-2020′,’DD-MON-YYYY’),3);
Insert into TeamFeedback values (‘T001′,to_date(’05-Oct-2020′,’DD-MON-YYYY’),4);
Insert into TeamFeedback values (‘T004′,to_date(’07-Nov-2020′,’DD-MON-YYYY’),5);