GEIT $3341$: Database I

Assignment# $1$

$1)$ Let R$($A$1,$ A$2,$ A$3)$ be a relation schema such that:

dom$($A$1)=\{$A$,$B$,$C$\}$

dom$($A$2)=\{1,2\}$

dom$($A$3)=\{10,20\}$

$1\mathrm{.}1$ Give an example of a valid $43-$tuples relation state. $(1$ point$)$

$1\mathrm{.}2$ Give an example of an invalid $33-$tuples relation state. $(1$ point$)$

$2)$ Consider the following schema and then answer the questions that follow:

EMPLOYEE:

EMP$\_$CODE EMP$\_$LNAME EMP$\_$FNAME EMP$\_$DOB EMP$\_$PHONE EMP$\_$SALARY STORE$\_$CODE

$1$ Williamson John $12/29/1962(06)434-009525001$

$2$ Ratula Nancy $3/12/1967(02)526-119235002$

$3$ Greenboro Lottie $11/2/1959(03)231-629228002$

$4$ Rumpersto Jennie $4/11/1964(06)224-112229003$

$5$ Smith Robert $12/23/1960(06)599-040630001$

$6$ Jones Rose $9/13/1965(06)111-626225003$

$7$ Rosten Peter $10/25/1965(02)111-113327004$

$8$ Smith Sherry $8/12/1967(02)796-112231001$

$9$ Ron Frank $11/11/1960(03)432-135626502$

$10$ Elain Roberts $2/23/1962(06)732-196728003$

STORE:

STORE$\_$CODE STORE$\_$NAME STORE$\_$YTD$\_$SALES REGION$\_$CODE

$1$ Access Jungle $20,000\mathrm{.}651$

$2$ Database Corner $55,000\mathrm{.}002$

$3$ PC Master $110,000\mathrm{.}443$

$4$ Computer City $82,000\mathrm{.}114$

REGION:

REGION$\_$CODE REGION$\_$DESCRIPT

$1$ East

$2$ West

$3$ North

$4$ South

$2\mathrm{.}1$ For the EMPLOYEE relation, give an example of a superkey and an example of a key. $(1$ point$)$

$2\mathrm{.}2$ Does the STORE relation satisfy the referential integrity constraint? If No explain why? $(1$ point$)$

$2\mathrm{.}3$ Does the REGION relation satisfy the entity integrity constraint? If No explain why? $(1$ point$)$

$3)$ Using the COMPANY database state, express the following queries in relational algebra:

$3\mathrm{.}1$ Select all female employees. $(0\mathrm{.}5$ point$)$

$3\mathrm{.}2$ Select all the employees supervised by the employee whose Social Security Number $($SSN$)$ is $333445555.(0\mathrm{.}5$ point$)$

$3\mathrm{.}3$ Select all female employees that work in department $5.(0\mathrm{.}5$ point$)$

$3\mathrm{.}4$ Select all projects controlled by department $5.(0\mathrm{.}5$ point$)$

$3\mathrm{.}5$ Select the dependent name and birth date of all dependents that belong to the employee whose SSN is $333445555.(1$ point$)$

$3\mathrm{.}6$ Produce a relation showing the employee first name, last name, name of his$/$her dependent, dependent$$s birth date, and relationship to the employee. $(1$ point$)$

$3\mathrm{.}7$ Find the first and last name of all non$-$managers. $(1\mathrm{.}5$ point$)$

$3\mathrm{.}8$ Use the natural join operation to find the social security number of the employee, the project number he$/$she works on$,$ and the project name she$/$she works on$.(1\mathrm{.}5$ point$)$

$4)$ Draw the intermediate relations R$1,$ R$2$ and the final RESULT relation produced by the following relational algebra expression: $(3$ points$)$

R$1$ DEPARTMENT PROJECT

Dnumber$=$Dnum

R$2\backslash$sigma $($R$1)$

Dname $!=$Research$$

RESULT $($R$2)$

Dname, Dnumber, Pname, Pnumber