Problem Description

Consider the following SQL tables:

CREATE TABLE person $($

name VARCHAR$(100),$

email VARCHAR$(100),$

work INTEGER,

hobbies INTEGER,

sleep INTEGER

;

CREATE TABLE hobbies $($

email VARCHAR$(100),$

hobby VARCHAR$(100),$

hours INTEGER

;

Problem $1(4$ points$)$: Rewrite the person schema $($using ALTER TABLE statements$)$ to satisfy

the following constraints:

A$.$ None of the five columns can be NULL

B$.$ email contains at least one $\text{'}$@$\text{'}$

C$.$ No two emails are the same

D$.$ work, hobbies, and sleep are all $0$

E$.$ work, hobbies, sleep should sum up to at most $24($hours in a day$)$

F$.$ The defaults for work, sleep, and hobbies are $0.$

Hint: consider using the LIKE operator.

Problem $2(2$ points$)$: Now redeclare the table hobbies $($using CREATE TABLE$)$ so that:

A$.$ email is a foreign key to the email column in the person table

B$.$ email, hobby, hours are not NULL

C$.$ hours is a positive integer

Problem $3(2$ points$)$: Obviously, person. hobbies needs to be the sum of hobbies. hours for

all hobbies for the person.

A$.$ First write a SELECT statement that checks whether this constraint is met. Remember

that you can put a boolean expression in the SELECT clause to display true or false for

some condition.

B$.$ Explain whether what you wrote in $($A$)$ can be a CHECK constraint in PostgreSQL.

Answer questions $1-3.$