Test 1

Sample paper

SAMPLE PAPER:

Perusal: 10 minutes

Time: 90 mins

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!!!!! This text was written for the previous syllabus and a SHORTER exam period, a simpler time. It is useful to see how the whole exam revolves around ONE database however

This paper deals exclusively with the maintenance and operation of an information system being used to model certain aspects of a game of similar to Monopoly. Details of the table system are included as a loose information sheet. Read this sheet carefully as it contains important information about the nature of this information system.

Where an SQL query is required, full marks will be awarded only to a single select statement. Where alternative solutions are possible efficiency is not an issue (i.e. you will not be penalised for failing to find the most efficient solution)

You are encouraged to use a pencil (as opposed to the less reversible biro).

Please observe standard formatting convention for the select statement wherever possible.

K = KNOWLEDGE; A= ANALYSIS, S=SYNTHESIS, C=COMMUNICATION, E=EVALUATION

BRISBANOPOLY

The Cheap and Nasty Co. in association with Sea Bird on a Stick Pty. Ltd. proudly presents BRISBANOPOLY - the same old game with a local context added to give you more of the same in a new package.

Players pit themselves against each other to buy Properties, monopolising entire neighbourhoods, developing them by building Houses and Hotels, avoid Tax, pay Rent and generally have a jolly time in the hustle and bustle of a property market run by the Bank.

Every POSition on the board is uniquely identified, and has a POSTYPE identifying it as either purchasable land, tax, GO and so on. Additionally, each POS has a PLAYSTATE which indicates whether it is active, mortgaged or never for sale (Chance for example is never for sale) Players throw dice and advance around the board (incrementing PLACEDAT as they go).

PosTypes Code Meaning P Property U Utility C Chance Card H Community CHest T Tax F Free Parking G GO O GO To Jail V Just Visiting J Jail = Pos 0

Players are uniquely identified by PID, choose a MARKER (like a boot, car or albatross) that represents them on the board and have a set amount of CASH - initially $1500 to spend. Each time a player increments past GO, their cash reserves receive $200 (and the PLACEDAT counter is reset by some procedural mechanism). Players who land themselves In Jail have their JAILSTATE changed to 'Y' and their PLACEDAT set to 11 in readiness for their release. Players In Jail are NOT allowed to trade in property until such time as they are released

Properties can be PURCHASEd from the bank (OWNER = 0), and can be MORTGAGEd if the owner is short of cash.

Once MONOP = 'Y' for a particular colour (ie. all properties of a colour are owned by one player), the owner may develop each of the properties in the monopoly by buying houses and subsequentially a hotel for each the property. Rent for 0 Houses in a monopoly is TWICE the stated DOLLARS for that POS in the RENT TABLE. The only POSTYPES that are able to be developed are type 'P' (as opposed to Utilities like Railway Stations, Water Works etc.)

THIS GAME IS PARTIALLY MODELLED USING A RELATIONAL DATABASE

Table: PLAYER

PID is the unique identification number of PlayerName who plays with Marker.
PlacedAt is the current POS of player PID. JailState ('Y' or 'N') records 'In Jail'.
Cash is the amount of money player PID has at the present.

Table: BOARD

Pos uniquely identifies a board position (1..40), this board position has an Owner (0=bank, 1= player 1..etc),
PropName is what the board position is called, NumHouses records how many houses are built on this site (0..4, 5=hotel),
PosType identifies what type of board position (see attached codes listing),
PlayState indicates its status ('A'=active, 'M'=Mortgaged, 'N'=not for sale),
Purchase records purchase cost (0 if not for sale), Mortgage records its mortgage value and
Colour indicates which Monopoly (colour group) this property belongs to, NULL if not applicable.

Table: RENT

Table: STREETS

Colour refers to a particular group of properties
Monop ('Y' or 'N') depending on whether one player owns all one Colour
HouseCost is the cost of building a house (hotel = 5 houses)

Questions

1. STRUCTURE OF THE SYSTEM

a. Regarding the RENT table,

1. What is the primary key? [1 K ]
2. Describe why this makes the logical choice for a key. [1 K ]
3. 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 ]
4. Write a create table command that would adequately define this table according to the information provided. [3 K ]

b. Regarding the PLAYER table,

1. What is its ARITY and what does this mean? [2 K]
2. This table conforms to the relational model of data. Describe two (2) characteristics of such tables. [2 K]

2. QUERYING THE SYSTEM

PLEASE NOTE: These questions are NOT arranged easiest to hardest - use the marks assigned to each question as a guide to the complexity of the solution. Don't assume that your 'simple' solution must be wrong because it is not complicated enough.

ASSUMING that we are half way through a game with 4 players, write SINGLE SELECT STATEMENTS that form result tables for the following:

1. List the name of the player who is playing with the Albatross marker. [3 A]
2. List the largest amount of rent chargeable for any Property with 3 houses. [4 A]
3. List the POS of Free Parking. [3 A]
4. List the amount of cash currently held by the player 'Alan Bund'. [3 A]
5. List the number of Properties or Utilities that are still for sale at the bank. [4 A]
6. List the average amount of cash players own. [2 A]
7. List PlayStates along with the number of properties in each state. [3 A]
8. List the POS of all Properties that have a HOTEL on them. [3 A]
9. List the names of all Properties owned by Player 2 in colour groups. [4 A]
10. List the number of colours that are currently monopolies [3 A]
11. 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 A]
12. 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 A]
13. List the most expensive Property (whether it is still for sale or not) money can buy in a game. [3 A]
14. List the name of the most expensive real estate (Property or Utility) still for sale? (use a simple intersection query) [6 A]
15. List the amount raised by mortgaging all of player 3's un-mortgaged GREEN Properties (assuming that none of them is developed). [4 A]
16. List the colours of the monopolies currently owned by Player 2. (use a simple intersection query) [4 A]
17. List the names of those players that have yet not bought any real estate (use a simple difference query). [4 A]

3. MAINTAINING THE SYSTEM

1. Suppose Player 1 lands on 'Go To Jail', write the update command necessary to record this fact. [3 S]
2. Player 4 just passed 'GO', write the appropriate update command to allow him/her to collect $200. [3 S]
3. 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 S]
4. 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 C]

4. MODIFYING THE SYSTEM

1. Suppose, instead of using EFTPOS as is the current situation in this game, we need to keep track of the number and denomination ($1, $2, $5, $10, $20, $50, $100, $500) of monies held by each player. Suggest a relationally acceptable modification/addition to the current structure to cope with this new requirement. [4 E]

SOLUTIONS