DATABASE SYSTEM

Create value range for all these tables in order to make an attribute for data dictionary? thankssss

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);