Classnotes

Database Languages

eXercise #2

---

Relational Diagrams

Download an Access2000 Version of this exercises database tables

1. Closely Examine the tables that follow:

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:

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:

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