CSG102: $3^{rd}$ Assignment

Due: Thursday, April 15

1. Consider the following ER-diagram,
   
   • Write down the corresponding db schema
   • 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

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.
   • 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.