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.)
Only two common problems. 1) "Company" is surely an entity, not an attribute. It enters into "Projects" in a 1 to N relationship, and would certainly have attributes (name, address, etc.) that one would want to keep track of. 2) Somehow you need to capture the "language pair to/from" idea, which is needed for the fee, for the document translation, and for the translator's capabilities. Having two individual relationships to a "language" entity doesn't quite do it. (Probably language is an attribute, i.e., think of it as an adjective rather than a noun).
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: Lots of possible examples. Only one is needed for each question.FLIGHT_NUMBER within FLIGHT_LEG (or within LEG_INSTANCE, FARES, SEAT_RESERVATION)
AIRPLANE_TYPE_NAME within CAN_LAND (or within AIRPLANE)
AIRPLANE_ID within LEG_INSTANCE
AIRPORT_CODE within FLIGHT_LEG, etc.
There are others involving compound keys.
Example of a violation: suppose FLIGHT_LEG contains a tuple with a FLIGHT_NUMBER of #477, but that FLIGHT has no tuple with that value. Since FLIGHT_NUMBER is a foreign key, this violates referential integrity.
Suppose AIRPORT had two different tuples with an AIRPORT_CODE of "LAX". Since AIRPORT_CODE is a key, this violates the key constraint (which is that keys are unique).
or
you could give any key a null value; that's another key constraint violation.
2b.
Write the following queries in each of the languages as requested.List all flights which originate in (1st leg leaves from) Spokane. [RA]
P FLIGHT_NUMBER (S LEG_NUMBER=1 (FLIGHT_LEG) JN DEPARTURE_AIRPORT_CODE=AIRPORT_CODE (S CITY="SPOKANE" (AIRPORT)))
List types of airplanes which cannot land in Spokane. [RA]
The idea in both versions is to find all the planes that can land in Spokane, and subtract that from all the types of planes.
P
TYPE_NAME (AIRPLANE_TYPE) - P AIRPLANE_TYPE_NAME (CAN_LAND * (S CITY="SPOKANE" (AIRPORT)))List types of airplanes which cannot land in Spokane. [SQL]
The SQL version could be written using the EXCEPT operator, or without it as follows:
SELECT TYPE_NAME
FROM AIRPLANE_TYPE
WHERE TYPE_NAME NOT IN
(SELECT AIRPLANE_TYPE_NAME
FROM CAN_LAND, AIRPORT
WHERE CAN_LAND.AIRPORT_CODE = AIRPORT.AIRPORT_CODE AND
AND CITY="SPOKANE")
List types of airplanes which CAN land in Spokane. [Tuple RC]
{t.AIRPLANE_TYPE_NAME | CAN_LAND(t) AND ($ u) (AIRPORT(u) and t.AIRPORT_CODE=u.AIRPORT_CODE and u.CITY="SPOKANE")}
How many American Airlines planes took off exactly on time on 10/31/97? [SQL]
SELECT COUNT (*)
FROM FLIGHT, FLIGHT_LEG L, LEG_INSTANCE I
WHERE AIRLINE="AMERICAN AIRLINES"
AND FLIGHT.NUMBER = F.FLIGHT_NUMBER
AND F.FLIGHT_NUMBER=I.FLIGHT_NUMBER
AND F.LEG_NUMBER=I.LEG_NUMBER
AND SCHEDULED_DEPARTURE_TIME=DEPARTURE_TIME
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]
This is a typical division query. It is not required that the airplane types be the same (in fact, one might expect them to be different for different companies), so type needs to be removed before the division is done.
P
MAX_SEATS,COMPANY (AIRPLANE_TYPE) DIVIDED-BYP
MAX_SEATS (S COMPANY="BOEING" (AIRPLANE_TYPE))
Prepare a list showing how many airports there are in each state contained in the database. [SQL]
SELECT STATE, COUNT (*)
FROM AIRPORT
GROUP BY STATE
3.
Describe briefly the difference between:DML and DDL
DML (data manipulation language): operations to add, modify, retrieve data
DDL (data definition language): operations to define or modify the schema and other administrative operations
key and partial key (with respect to E/R modeling)
key: uniquely identifies entities
partial key: applies only to weak entities. A partial key must be combined with the "owning" entity's key to get a unique key for the weak entity.
atomic and non-atomic value
(textbook p.44)atomic: a non-divisible piece of data (e.g. an elementary data type)
non-atomic: has some visible internal structure (multiple values, nested attributes, etc.)
schema and instance
schema: describes the structure only
instance: actual data
natural join vs joins in general?
natural join: joined columns have the same name (don't have to in general join); it is not necessary to specify the name (is necessary in a general join); and the superfluous columns are automatically removed (not so in general).