1.
The One World Translation Bureau does commercial and technical translations for high-tech companies. In a typical project, a company brings in a set of documents (package labels, installation instructions, user manuals, help files, etc.) and expects One World to deliver translations in the languages of the country where the product is to be sold. All of the original material is in one language, but each piece of documentation might be translated into a different set of languages. For example, if the product was originally Korean and is now being retargeted for Europe, the package labels might need translating into 6 or 7 languages, but the manuals into only German and English. The cost to the company hiring the project is based on the total number of pages of translation being delivered. The project is complete when all the translations for all the documents are finished. It is possible that a given company might have more than one project under contract at a given time.One World accepts the project, and then assigns (or hires) translators to do the work. Each translator is certified in one or more language capabilities (and just because a person is certified for Dutch to Vietnamese (for example) doesn't automatically mean they can do Vietnamese to Dutch.) Translators are paid by the page; the fee they get is based on the language combination and not on the individual person's skill or experience. The project has to get underway immediately even if translators are not available for all the material. Some translators may be working on more than one project at a time, or on no project at all. Some large documents may be assigned to several translators.
One World wants you to design a database to help keep track of projects and translators. The database should be designed so that all reasonable questions about the nature and status of projects and of translators can be answered.
Draw an E/R diagram for a possible database. Use as much standard E/R notation as is applicable (cardinalities, etc.)
2.
Answer the following questions with respect to the AIRLINE relational database schema (on handout, from textbook p.181).2a.
Give an example (of relations, relation instances, tuples, or whatever is appropriate) to illustrate each of the following concepts:
2b.
Write the following queries in each of the languages as requested.
List all flights which originate in (1st leg leaves from) Spokane. [RA]
List types of airplanes which cannot land in Spokane. [RA]
List types of airplanes which cannot land in Spokane. [SQL]
List types of airplanes which CAN land in Spokane. [Tuple RC]
How many American Airlines planes took off exactly on time on 10/31/97? [SQL]
List the companies which might compete well with Boeing. That is, for each type of plane made by Boeing, these companies make a plane with exactly the same seating capacity as the Boeing plane. [RA]
Prepare a list showing how many airports there are in each state contained in the database. [SQL]
3.
Describe briefly the difference between:DML and DDL
key and partial key (with respect to E/R modeling)
atomic and non-atomic value?
schema and instance
natural join vs joins in general?