HW#3 --- last modified March 02 2019 21:20:26..
Solution set.
Due date: Oct 24
Files to be submitted:
Problems.pdf
Dungeon.sql
Dungeon.mdb
Purpose: To get practice writing queries in the
relational algebra, tuple, and domain calculus. To practice going from an
ER model to actual tables in Oracle and Access.
Specification:
To begin write each of the express each of the following queries in (a)
the relational algebra, (b) the tuple relational calculus, and (c) in the
domain relational calculus. The underlying database schema is that
of the COMPANY described in the book. Submit your answer in
Problems.pdf.
1. Retrieve all EMPLOYEEs who earn as much as some EMPLOYEE in the
Research DEPARTMENT.
2. For each BDATE value of some EMPLOYEE count the number of EMPLOYEEs
with that BDATE. (For this one, you don't have to write calculus
expressions)
3. Retrieve all the DEPENDANTs of EMPLOYEEs who supervise at least two
other EMPLOYEEs.
4. Retrieve all the EMPLOYEEs who work on all of the PROJECTs located in
the Engineering DEPARTMENT.
5. Retrieve the first name and last names of EMPLOYEEs who either
have the highest or the lowest salaries in their DEPARTMENTs. (Do not use
aggregation).
Next imagine you are on the road to becoming a total geek. You have been
placed in charge of coming up with a database for a
Dungeons and Dragons conference (Google if never heard of). Besides
needing to know the usual name and address, etc of PARTICIPANTs, you need
to model that PARTICIPANT may play in several GAMEs, and for each GAME
they may play different CHARACTERs. Some PARTICIPANTs might also be
DUNGEON MASTERS for some games. There might even be an OVERALL_STANDINGS
and some PARTICPANTs might receive different kinds of AWARDS. In addition
to PARTICIPANTs, there might be various VENDORs, selling D&D
MERCHANDISE. VENDORs might rent BOOTHs and sponsor certain SPECIAL GAMES.
Model this situation with an ER or EER diagram which you also should put in
Problems.pdf. Then using the ER to Relational mapping algorithm convert
this diagram to a relational database schema. Be sure to incorporate
all applicable constraints. Put this schema as well
in Problems.pdf. Next create a script Dungeon.sql which creates all of
the tables of your schema in SQL so that it could be run in your Oracle
account. Also, include in your script commands to insert at least 5 rows
into every table you have. Finally, using Microsoft Access (available in
the labs) recreate your database in a file Dungeon.mdb. Again, be sure to
incorporate all constraints (in particular, foreign key constraints).
Point Breakdown
Queries (1pt each) | 5pts
|
ER Diagram | 1pt
|
Dungeon.sql script as described | 2pts
|
Dungeon.mdb file as described | 2pts
|
Total | 10pts |
|