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. ]> b. 1 2 3 4 c. //Leaf/*