IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
 
 
IIS home
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help
 
 

Database Languages

eXercise #2


Relational Diagrams

Download an Access2000 Version of this exercises database tables

1. Closely Examine the tables that follow:

PEOPLE
Name Sex BirthYr MarriageYr
Fred M 1962 1986
Barney M 1964 ?
Wilma F 1958 1987
Olivia M 1969 1990
Mergatroid F 1975 1971
Popeye M 1935 ?
Alexander M ? 1983
GRADUATIONS
Name GradYr
Wilma 1990
Olivia 1986
Alexander 1989

Constraint: No one can appear in the GRADUATIONS table without first appearing in the PEOPLE table

(a) Identify the PRIMARY KEYs of the two tables (assuming the populations are significant).

(b) Draw Relational diagrams (and attempt the SQL equivalent) for the following queries: (asterisks indicate complexity of question)

  1. the entire PEOPLE table
  2. the name of all people in the database (assuming the constraint has been upheld)
  3. the name and sex of each person
  4. the name of all females
  5. the name of any male born after 1970, along with his birth year
  6. all married persons
  7. anyone for whom we do not know a marriage year
  8. people who are missing either a marriage or birth year
  9. a list of all years recorded in the people table (either in the Birth or marriage cols) *
  10. years in which there was at least one graduation and marriage **
  11. anyone who is not recorded as having graduated ***
  12. anyone who violates the constraint ***
  13. people who were married in the same year they graduated ***

2. Given the following single column table called DIGITS:

Num
KEY
0
1
2
3
:
9

Draw relational diagrams (and attempt the SQL) for the following (hint: Join queries will solve these questions):

  1. all 2 digit numbers between 00 and 99
  2. all 2 digit numbers between 50 and 99
  3. all 2 digit couples (eg. 11, 22, 33 ..)
  4. all 2 digit combinations where the first digit is less than the second (eg. 23, 69)

3. Consider the following tables which hold data about various video sub-systems available for personal computers:

SYSTEMS
SystemId VidMode Cost
PC-X37 CGA 175
XLG45-1 EGA 242
T55A EGA 197
PS45610 VGA 410
LQ-4432 SVGA 793
1024i XVGA 1345
ATI-W XVGA 1407
RESOLUTIONS
VidMode Horiz Vert
CGA 320 200
EGA 640 350
VGA 640 480
SVGA 800 600
XVGA 1024 768
  1. Identify the PRIMARY KEYS for each of the tables
  1. Draw relational diagrams, and then attempt the mSQL equivalents for the following queries:
    1. systems with a horizontal resolution better that 640
    2. the name and cost of all 800x600 systems
    3. the cost and resolution (horiz and vert) of the LQ-4432 system
    4. the name and cost of systems with better than VGA resolution

Solutions

wonko@wonko.info
©Copyright t 1992..2017+. Edition 25.150117
wonkosite
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License
.