CSC343-Assignment 1 Relational Algebra Solved

You will be working on a schema and queries for a database used by a zoological institute to track an archive of their artifacts.

During a eld trip collectors gather a variety of artifacts of the animals they study, resulting in tissue samples, images, physical models (such as casts of paw prints), or live colonies.

After arriving at the institute, artifacts must be safely stored and maintained by technicians. Some artifacts are cited in one or more publications. In all cases the o cial species name must be recorded, and must appear in the Catalogue of Life database. If correct taxonomic practices are followed, each species belongs to exactly one genus, and each genus to exactly one family. Tables COL, Genus, and Species are derived from Catalogue of Life database. relations

Collection(CID, date, SID)

Tuples here represent entire collections from a eld trip, where CID is the collection ID, date is the starting date of the eld trip, and SID is the sta ID of the collector.

Collected(CID, AN)

 

A tuple here represents the fact that collection CID includes artifact number AN. A single collection usually contains multiple artifacts, and a single artifact may be aggregated from more than one collection.

Artifact(AN, species, type, location, SID)

Tuples here represent single artifact collected in the eld. AN is the artifact number, species is the scienti c species name, type is one of tissue, image, model, or live, location is where it was collected, and SID is the sta number of the technician who maintains this artifact.

Published(AN, journal, date)

 

A tuple here represents the fact that artifact AN was mentioned in scholarly publication journal with publication date date.

Sta (SID, name, email, rank, date)

These tuples represent a member of the institute’s scienti c sta . SID is the sta ID, name is their full name, email is their professional email, rank is one of: technician, student, pre-tenure, or tenured, and date is the date when they attained that rank.

COL(family)

 

A singleton tuple here means that family is a scienti c zoological family name that appears in the Catalogue of Life.

Genus(genus, family)

 

A tuple here means that genus is in family family.

Species(species, genus)

 

A tuple here means that species is in genus genus.

our constraints
For each of the following constraints give a one sentence explanation of what the constraint implies, and why it is required.

species(Artifact) species(Species) = ;. rank(Staff) f’technician’, ’student’, ’pre-tenure’, ’tenure’g.

family(Genus)           family(COL) = ;. genus(Species)          genus(Genus).

                CID(Collected) =          CID(Collection).

                 AN(Artifact) =          AN(Collected).

                SID(Collection)               SID(Staff).

                 SID(Artifact)               SID(Staff).

type(Artifact) f0tissue0;0 image0;0 model0;0 live0g

                AN(Published)             AN(Artifact)

queries
Write relational algebra expressions for each of the queries below. You must use notations from this course and operators:

; ; ;./;./condition;              ;\;[;               ;= You may also use constants:

                                                      today (for current date)            ; (for the empty set)

In your queries pay attention to the following:

All relations are sets, and you may only use relational algebra operators covered in Chapter 2 of the course text.

Do not make assumptions that are not enforced by our constraints above, so your queries should work correctly for any database that obeys our schema and constraints.

Other than constants such as 23 or "lupus", a select operation only examines values contained in a tuple, not aggregated over an entire column.

Your selection conditions can use arithmetic operators, such as +; ;6=; ;>;< and friends. You can use logical operators such as _;^, and :, and treat dates and numeric attributes as numbers that you can perform arithmetic on.

Use good variable names and provide lots of comments to explain your intentions.

Return multiple tuples if that is appropriate for your query.

There may be a query or queries that cannot be expressed in the relational algebra you have been taught so far, in which case just write \cannot be expressed." The queries below are not in any particular order.

1.   Rationale: Performance reviews include seeing how current the work is of sta who have held their current rank for a long time.

Query: Find the most recent collection date of any artifact collected by a sta member who has held their current rank the longest. Keep ties.

2.   Rationale: Sta who maintain every artifact in some collection should be considered favourably in performance reviews.

            Query: Find all sta         who maintain all artifacts in at least one collection.

3.   Rationale: An artifact collected and maintained by the same sta may have some special requirements that should be investigated.

               Query: Find all artifacts that were collected by the same sta         who maintains them.

4.   Rationale: Identify multi-talented       eld workers.

            Query: Find all sta       who have collected at least 3 artifacts from every species in some family.

5.   Rationale: Which publications might have some specialized niche focus?

Query: Find all publications that have used exactly 2 of our artifacts.

6.   Rationale: Identify motherlode locations.

Query: Find all locations where at least one artifact from every family has been collected.

7.   Rationale: Exclusively tissue sample collectors may need extra support for special reagents and shipping costs.

            Query: Find all sta       who have collected only tissue samples.

8.   Rationale: Collection sta        who should be encouraged to diversify their network.

            Query: Find all sta         pairs who have worked only with each other on collections.

9.   Rationale: Track the in uence of a given sta       member.

Query: Sta member SID1 is in uenced by sta member SID2 if (a) they have ever worked together on a collection or (b) if SID1 has ever worked with a sta member who is in uenced by SID2. Find SIDs of sta members in uenced by SID 42.