CSG102: $3^{rd}$ Assignment

Due: Thurday, April 15

1. Consider the following ER-diagram,
   
   • Write the corresponding db schema
     

     Solution

     Employee(ID, name, salary, position, trainer_id, department)

     Department(ID, name)

     Project(ID, description cost)

     Works_On(employee, project, hours)

     Customer(SSN, name)

     Funds(project, customer, department, fund)
     

   • Create the appropriate tables in SQL taking into account the following constraints,

     [Employee's Properties]

     • id should be of the form Exx, where x is a digit
     • salary cannot be less than $15000.
     • position cannot get NULL value.

     [Works_on Properties]

     • hours cannot be negative

     [Funds Properties]

     • funds cannot be negative

     [Customer's Properties]

     • SSN should be of the form xxx-xx-xxxx , where x is a digit

     [Project Properties]

     • id should be of the form Pxx, where x is a digit
     • cost cannot be negative

   Sample Solutions for Employee and Project

   CREATE TABLE Employee
   (id CHAR(3) CONSTRAINT id_value CHECK(id LIKE 'E__'),
   salary NUMBER CONSTRAINT salary_value CHECK(salary$>$15000),
   position VARCHAR(10) CONSTRAINT position_nn NOT NULL,
   trainer_id CHAR(3) CONSTRAINT trainer_id_value CHECK(trainer_id LIKE 'E__'),
   department VARCHAR(5),
   PRIMARY KEY (id),
   FOREIGN KEY (trainer_id) REFERENCES Employee,
   FOREIGN KEY (department) REFERENCES Department);

   
   
   CREATE TABLE Project
   (id CHAR(3) CONSTRAINT pid_value CHECK(id LIKE 'P__'),
   cost NUMBER CONSTRAINT cost_value CHECK(cost$>$0),
   PRIMARY KEY (cost));
   

2. Consider the following db schema,

   Lab(labID, labName, location)

   Students(studentID, fname, lname)

   Guinea-Pig(animalID, description)

   Working(studentID, labID, hours)

   Feeding(studentID, animalID, time, amount_of_food)

   • Create an ER-diagram to capture the design of the db schema above.
     Solution
     

   • The student with USERNAME EKANOU wants to have update access on the amount_of_food only for the guinea pig with animalID='A1'. Create the appropriate view and grand the appropriate privilege to EKANOU.
     Solution

     CREATE VIEW AnimalA1 AS
     SELECT *
     FROM Guinea_Pig
     WHERE animal_id$=$'A1';

     GRAND update(amount_of_food)
     ON AnimalA1
     TO EKANOU;