### Relational Diagrams

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
 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

wonko@wonko.info