Databases Exam 18 March 2016: Solutions.

Notice: there can be other correct solutions than the ones below.

--------------------------------
1. 

a. E-R model:

ENTITY Mountain _position
ENTITY Name _str
RELATIONSHIP HasName -- Mountain -- Name
ENTITY Country _name
RELATIONSHIP LiesIn -- Mountain -- Country
RELATIONSHIP IsHighestIn -- Country -) Mountain

b. Schema as direct translation:

Mountain(_position)

Name(_str)

HasName(_mountainPosition,_nameStr)
  mountainPosition -> Mountain.position
  nameStr -> Name.str

Country(_name,mountainPosition)
  mountainPosition -> Mountain.position

LiesIn(_mountainPosition,_countryName)
  mountainPosition -> Mountain.position
  countryName -> Country.name


c. The extra constraint: change Country to

Country(_name,mountainPosition)
  (name,mountainPosition) -> LiesIn(_countryName, mountainPosition)

------------------------------

2.

a. Functional dependencies:
name    -> capital area population density gdp gdpCapita currency timeZone timeZoneDiff
capital -> name area population density gdp gdpCapita currency timeZone timeZoneDiff
area population -> density
area density -> population
population density -> area
gdp population -> gdpCapita
population gdpCapita -> gdp
gdp gdpCapita -> population
timeZone -> timeZoneDiff
timeZoneDiff -> timeZone

b. Keys:
name
capital


c. Normalization:
All FD's but the first two violate BCNF, since they are not keys.

1. decomposition: area population -> density

R1(_area,_population,density)
FD area population -> density (LHS key, no violation)

R2(_name,capital, area, population, gdp, gdpCapita, currency, timeZone, timeZoneDiff)

  2. decomposition: gdp population -> gdpCapita
  R21(_gdp,_population,gdpCapita)
  
  R22(_name,capital, area, population, gdp, currency, timeZone, timeZoneDiff)

    3. decomposition: timeZone -> timeZoneDiff
    R221(_timeZone,timeZoneDiff)

    R222(_name,capital, area, population, gdp, currency, timeZone)
    FD name -> *
       capital -> * (both with a key, no violation)
       
Tables R1 and R21 could be eliminated, since the third attribute can
be computed from the key by division (see 3b below!)


-------------------------------
3.

--a.

CREATE TABLE Countries (
  name TEXT PRIMARY KEY,
  population INT,
  area INT,
  timeZoneDiff INT CHECK (timeZoneDiff BETWEEN -12 AND 12)
) ;


--b.

SELECT name,population/area AS density
FROM Countries
WHERE timeZoneDiff > 0 ;

--c.

SELECT name, 'western' AS hemisphere FROM Countries WHERE timeZoneDiff <= 0
UNION
SELECT name, 'eastern' AS hemisphere FROM Countries WHERE timeZoneDiff > 0
;


------------------------------
4.

a. gamma_[timeZone,sum(population)] Countries

b. delta (pi_[timeZone] Countries)  -- returns distinct time zones
   pi_[timeZone] (delta Countries)  -- repeats time zones that different countries have

It is OK to use some other relation than Countries in question b.

------------------------------
5.

a. 

CREATE TABLE AtMostOne (
  theRow INT PRIMARY KEY CHECK (theRow = 1)
) ;


b.

CREATE FUNCTION notNoTeachers() RETURNS TRIGGER AS $$
BEGIN
  IF ((SELECT COUNT(name) FROM Teachers) < 1)
    THEN RAISE EXCEPTION 'no teacher left' ;
END IF ;
RETURN NEW ;
END
$$ LANGUAGE 'plpgsql' ;

CREATE TRIGGER guaranteeTeachers
  AFTER DELETE ON Teachers
  FOR EACH ROW
  EXECUTE PROCEDURE notNoTeachers() ;

c.

CREATE TABLE Distances (
  fromCity TEXT,
  toCity TEXT,
  distance INT,
  CONSTRAINT only_one_direction CHECK (fromCity < toCity) 
  ) ;



-------------------------------
6.

a.

<!DOCTYPE BT [
  <!ELEMENT BT ((BT,BT) | Leaf)>
  <!ELEMENT Leaf (#PCDATA)>
  ]>

b.

<BT>
  <BT><Leaf>1</Leaf></BT>
  <BT>
    <BT>
      <BT><Leaf>2</Leaf></BT>
      <BT><Leaf>3</Leaf></BT>
    </BT>
    <BT><Leaf>4</Leaf></BT>
  </BT>
</BT>

c.

//Leaf/*