$1.(10$ points$)$

$($a$)$ What is a $1-$tier architecture in DBMS$,$ and what are its limitations$?$

$($b$)$ Describe a $2-$tier architecture in DBMS and its main advantage.

$($c$)$ What is a $3-$tier architecture in DBMS and its main advantage?

Solution:

$2.(10$ points$)$ Describe the differences in meaning between the terms $$relation instance$$ and $$relation

schema$.$

Solution:

$2$

$3.(10$ points$)$ List two reasons why null values might be introduced into a database.

Solution:

$4.(10$ points$)$ What is the difference between a primary key and a foreign key?

Solution:

$3$

$5.(40$ points$)$ Consider the following employee database.

employee

ID person name street city

$1$ Alice Main St New York

$2$ Bob Pine St San Francisco

$3$ Carol Oak St New York

$4$ David Elm St Boston

$5$ Eve Birch St San Francisco

works

ID person name company name salary

$1$ Alice BigBank $9000$

$2$ Bob TechCorp $12000$

$3$ Carol BigBank $11000$

$4$ David HealthCare $8000$

$5$ Eve TechCorp $13000$

company

company name city

BigBank New York

TechCorp San Francisco

HealthCare Boston

Give an expression in the relational algebra to express each of the following queries. Also show

the result obtained from the sample database according each queries.

$($a$)$ Find the ID and name of each employee who works for $$BigBank$.$

$($b$)$ Find the ID$,$ name, and city of residence of each employee who works for $$BigBank$.$

$($c$)$ Find the ID$,$ name, street address, and city of residence of each employee who works for $$Big$-$

Bank$.$ and earns more than $$10000.$

$($d$)$ Find the ID and name of each employee in this database who lives in the same city as the

company for which she or he works.

Solution: