CS计算机代考程序代写 database — The first part of the exercise simply involved re-ordering the

— The first part of the exercise simply involved re-ordering the
— data in the data.sql file so that tables were inserted in an
— order such that there would never be references to keys that
— were not already inserted into the database.

— Valid orders for populating tables:
— Employee, Department, Mission, WorksFor
— Employee, Department, WorksFor, Mission

— The second part of the exercise required addition of constraints
— to the original schema. One possible solution for this is given
— below. (Question 10 excluded)

create table Employees (
tfn char(11)
constraint ValidTFN
check (tfn ~ ‘[0-9]{3}-[0-9]{3}-[0-9]{3}’),
givenName varchar(30) not null, — must have a given name
familyName varchar(30), — some people have only one name
hoursPweek float
check (hoursPweek >= 0 and hoursPweek <= 168), --7*24 primary key (tfn) ); create table Departments ( id char(3) -- [[:digit:]] == [0-9] constraint ValidDeptId check (id ~ '[[:digit:]]{3}'), name varchar(100) unique, manager char(11) not null unique constraint ValidEmployee references Employees(tfn), primary key (id) ); create table DeptMissions ( department char(3) constraint ValidDepartment references Departments(id), keyword varchar(20), primary key (department,keyword) ); create table WorksFor ( employee char(11) constraint ValidEmployee references Employees(tfn), department char(3) constraint ValidDepartment references Departments(id), percentage float constraint ValidPercentage check (percentage > 0.0 and percentage <= 100.0), primary key (employee,department) ); create function check_worksfor_insert() returns trigger as $sum_of_percentage$ declare percentage1 float; percentage2 float; begin select sum(percentage) into percentage1 from WorksFor where employee = NEW.employee; percentage2 = percentage1 + NEW.percentage; if percentage2 > 100 then
raise exception ‘work percentage cannot exceed 100 percent’;
end if;
return NEW;
end;
$sum_of_percentage$ language plpgsql;

create trigger sum_of_percentage before insert or update on WorksFor
for each row execute procedure check_worksfor_insert();