Classnotes

Database Languages

eXercise 1

---

The Relational Model

A. Referring to the following tables that attempt to summarise sporting prowess at an international meet (only partial data is shown).

(a) Nation Medals Russia 11G, 8S, 3B Czecoslovakia 9G 6S, 5B France 5G, 4S, 2B South Africa 2G, 5B Australia 1G, 3S	(b) Nation Gold Silver Bronze Russia 11 8 3 Czecoslovakia 9 6 5 France 5 4 2 South Africa 2 0 5 Australia 1 3 0
(c) Nation Medal Tally Russia Gold 11 Russia Silver 8 Russia Bronze 3 Czecoslovakia Gold 9 Czecoslovakia Silver 6 : : :

1. Which of the above conforms to the relational model of data? In cases where there are exceptions to the model, detail them.
2. For those tables that conform to the relational model, detail the possible benefits of having the data stored in that form, along with possible problems. (hint: ask yourself what types of questions (queries) are difficult with one form that are simpler in another)

B. The following tables make different assumptions about the data, what is the difference?.

(a) Team Wins Broncos 4 Cockroaches 6 Tigers 0

(b) Team Wins Broncos 4 Cockroaches 6 Tigers

C. Given that the illustrated populations in the following tables are signifigant, identify the Primary Key for each relation.

(a) Name Gender Fred M Phyllis F Olivia M	(b) Student Subject Fred English Craig English Fred Geography	(c) Language SQL Prolog Pascal COBOL ASM Modula 2 Visual Basic
(d) Language Recursive Pascal Yes SQL No Prolog Yes COBOL No	(e) Manufacturer Model Year Ford Mondeo 1997 Ford Falcon 1995 Mitsubishi Magna 1997 Toyota Corolla 1997 Toyota Corolla 1988	(f) Day Subject Period Mon Physics 3 Tues Physics 3 Tues Physics 5 Tues Geography 5

D. Given the population in the following tables, identify the Primary Key for the relation.

School	Event	Place
BSHS	13	2
BSHS	14	2
GT	13	1

1. If ties are allowed?
2. If ties are not allowed?

---

Solutions