Test 1
Some Solutions
SAMPLE PAPER - SOME SOLUTIONS:
1. STRUCTURE OF THE SYSTEM
a. Regarding the RENT table,
i. What is the primary key? [1 K]
a combination of POS and NumH
ii. Describe why this makes the logical choice for a key. [1 K]
this combination uniquely identifies the state of each property on the board
iii. Name the column that is a foreign key, the table it references, and describe in PLAIN ENGLISH what constraint this places on data in this table. [2 K]
POS is the foreign key, referencing POS in BOARD. This enforces referential integrity - ie. rent cannot be recorded for a POS that is not already known
iv. Write a create table command that would adequately define this table according to the information provided. [3 K]
create table rent(
POS dec(2) not null,
NumH dec(1) not null,
Dollars dec(8) not null,
Primary Key(POS, NumH))
b. Regarding the PLAYER table,
i. What is its ARITY and what does this mean? [2 K]
arity - degree of relationship = number of cols = 6
ii. This table conforms to the relational model of data. Describe two (2) characteristics of such tables. [2 K]
rows unique, row order not important, rows instantiate same relationship, column headings, each entry is a single valued field (ie. no repeating groups, ie. flat)
2. QUERYING THE SYSTEM
a. List the name of the player who is playing with the Albatross marker. [3 R&D A]
select playername
from player
where marker = 'albatross'
b. List the largest amount of rent chargeable for any Property with 3 houses. [4 R&D A]
select max(dollars)
from rent
where numH = 3
c. List the POS of Free Parking. [3 R&D A]
select pos
from board
where postype = 'F'
d. List the amount of cash currently held by the player 'Alan Bund'. [3 R&D A]
select cash
from player
where playername = 'Alan Bund'
e. List the number of Properties or Utilities that are still for sale at the bank. [4 R&D A]
select count(*)
from board
where owner = 0
and postype = 'P'
or postype = 'U'
f. List the average amount of cash players own. [2 R&D A]
select avg(cash)
from player
g. List PlayStates along with the number of properties in each state. [3 R&D A]
select playstate, count(*)
from board
where postype = 'P'
group by playstate
h. List the POS of all Properties that have a HOTEL on them. [3 R&D A]
select pos
from board
where numhouses = 5
i. List the names of all Properties owned by Player 2 in colour groups. [4 R&D A]
select propName, colour
from board
where owner = 2
order by 2
j. List the number of colours that are currently monopolies [3 R&D A]
select count(*)
from streets
where monop='y'
k. List Property Names of those properties that are in the Colour that is the most expensive to build on (use a simple intersection query) [5 R&D A]
select propName
from board
where postype = 'p'
and colour in
(select colour
from streets
where houseCost =
(select max(houseCost)
from streets))
l. Suppose as part of a monopoly, I own 'Queen Street'. List the cost of building 2 houses on this property? (use a simple intersection query) [6 R&D A]
select 2*houseCost
from streets
where colour in
(select colour
from board
where propName = 'Queen St')
m. List the most expensive Property (whether it is still for sale or not) money can buy in a game. [3 R&D A]
select propName
from board
where purchase =
(select max(purchase)
from board)
n. List the name of the most expensive real estate (Property or Utility) still for sale? (use a simple intersection query) [6 R&D A]
select propname
from board
where purchase in
(select max(purchase)
from board
where proptype in ('p','u')
and owner = 0)
o. List the amount raised by mortgaging all of player 3's un-mortgaged GREEN Properties (assuming that none of them is developed). [4 R&D A]
select sum(mortgage)
from board
where colour = 'green'
and owner = 3
and playState = 'a'
p. List the colours of the monopolies currently owned by Player 2. (use a simple intersection query) [4 R&D A]
select colour
from board
where owner = 2
and colour in
(select colour
from streets
where monop = 'y')
q. List the names of those players that have yet not bought any real estate (use a simple difference query). [4 R&D A]
select pid
from player
where pid not in
(select owner
from board)
PLEASE NOTE - there is NO correlation featured here, nor very many difficult questions involving joins - you will need to be ready for then in the actual exam!!!!!
3. MAINTAINING THE SYSTEM
a. Suppose Player 1 lands on 'Go To Jail', write the update command necessary to record this fact. [3 R&D S]
update player
set jailState = 'Y',placedAt = 11
where pid = 1
b. Player 4 just passed 'GO', write the appropriate update command to allow him/her to collect $200. [3 R&D S]
update player
set cash = cash+200
where pid = 4
c. Suppose the same 4 people want to start another game (they just can't get enough). Write the sequence of commands that will reset the tables to a new game (ignore the bank). [6 R&D S]
update player
set cash = 1500, placedAt = 1, jailState = 'n'
update board
set owner = 0, numHouses = 0, playState = 'A' where posType = 'P'
or posType = 'U'
update streets
set monop = 'n'
d. Player 2 just landed on 'Wickham Tce', it is for sale and Player 2 has the appropriate amount of cash.
In CLEAR CONCISE ENGLISH, describe the actions that would be necessary to record this sale. Ensure you name relevant table fields. [3 R&D C]
player 2's cash would need to be reduced by the purchase price of the property and the owner of that pos would need to be set to 2. The Monop status would also have to be checked
|
