=========================preview======================
(COMP231)quiz00SU_sol1.pdf
Back to COMP231 Login to download
======================================================
COMP231-- Database Management Systems Summer 00 Quiz1 Module Solution
Part1: ER Data Model
Q1 & Q2:

Assumptions: Its possible that a player does not play in any match. The doubles players must be a pair ( not related palyers).
Q3:
a) True. Refer the answer of question 2. The weak entity pair has two identifying relationship types with the same entity type player.
b) Ture. The three binary relationship types have to be identifying relationship types. Refer Supply ternary relationship type in the text book.
c) True. For example, Let the foreign key (FK) in relation R be the prime key (PK) in relation S. Following query:
Select *
From S
Where S.PK = val

val is the value of FK in R. If above query results in NULL, the relational integrity constraint is violated.
d) True. Both tuple relational calculus and SOL are relational complete language.
Part II: Relational Algebra, SQL and Tuple Relational Calculs.
Q4:
.
Retrieve the name and rank of players who represent Hong Kong.

{ P.Name, P.Rank . PLAYER(P) AND P.Country = Hong Kong}

.
Retrieve the names of players from Hong Kong who won a match in Wimbledon 2000.


{ P.Name1 . PLAYER(P) and P.Country =Hong Kong and .m (MATCH(m) AND m .Name1 = P.Name) AND .t (TOURNAMENT(t) AND t .TID = m .TID AND t .Tname = Wimbledon AND t .Year = 2000 )}
Q5:
Update PLAYER Set RANK = 1 Where Name in (
Select distinct Name1
From MATCH m, TOURNAMENT t
Where m .TID = t .TID AND

t .Tname = Wimbledon and T.Year = 2000
minus
Select distinct Name2
From MATCH m, TOURNAMENT t
Where m .TID = t .TID AND

t .Tname = Wimbledon and T.Year = 2000)
or
Update PLAYER Set RANK = 1 Where Name not in (
Select distinct Name2
From MATCH m, TOURNAMENT t
Where m .TID = t .TID AND

t .Tname = Wimbledon and T.Year = 2000)
Q6:
. List player names with age greater than 20 from Hong Kong.
RA: Name (Age > 20 AND Country = Hong Kong) ( PLAYER)
SQL: Select Name
From PLAYER
Where Age > 20 AND Country = Hong Kong

. List the name of players who played a match in Wimbledon 2000.
RA: Name1 (MATCH * TID (Tname = Wimbledon AND Year = 2000) (TOURNAMENT))
U

Name2 (MATCH * TID (Tname = Wimbledon AND Year = 2000) (TOURNAMENT))
SQL: Select Name1 From MATCH m, TOURNAMENT t Where m.TID = t .TID AND Tname = Wimbledon and T.Year = 2000 UNION Select Name2 From MATCH m, TOURNAMENT t Where m.TID = t .TID AND Tname = Wimbledon and T.Year = 2000
. List the player names and the number of matches they won in Wimbledon 2000.
RA: Name1 . count(Name1) (MATCH * TID (Tname = Wimbledon AND Year = 2000) (TOURNAMENT))
SQL: Select Name1, count(*)
From MATCH m, TOURNAMENT T
Where m .TID = t .TID AND

Tname = Wimbledon AND
Year = 2000
Group by Name1