Question

1. Very carefully read the following data requirements for a prospective document translation database:

i. Document translation initially relies on a source document. The source document is a text provided in the originally recorded, valid language (see below, and assume only one language for the original document), and has an associated author and publication date. Source document authors are not related to translators in any manner.

ii. Translation of a document also relies on a translator, who is capable of translating documents from one language to another. A translator may be uniquely identified by a numeric international translation association (ITA) number (ITAN). A translator must also be fluent in one or more languages*, and may serve on an ITA committee for up to one (but no more than one) particular language.

iii. The document translation database also requires potential languages to be specified. In addition to the ITA committee outlined in ii., each language is associated with a single ITA committee chair.

iv. A translation is associated with a source document, a language (the one to which it has been translated), and a translator. In addition, it is associated with a translation date, and a certification status (Y or N). Multiple translators can create translations of any given document into a given language, and a single translator can create translations of a given document into multiple languages. Assume, however, that any given translator can only translate a given document into a single language once and only once.

1. Create an entity-relationship diagram to model the document translation database. While it’s ultimately up to you to select appropriate entities and relationships, the italicized words should give you a good starting point for the major ones. Note that there are at least two ways to interpret and depict requirement iv. within the database, and both are entirely acceptable. Be certain to include cardinality and participation concerns within your diagram, and document the attributes/keys involved (be it within the ER diagram or in a separate UML diagram, etc.) (30 pts)
2. Follow the protocol we discussed in class to map this ER diagram into a relational database schema. (20 pts)

Answer 1

a. Please follow the below steps to create ERD for the given data requirements in document translation database:

Step 1: Find Entities:

SourceDocument

Language

Author

Translator

Translation: A weak entity based on a document and a translator.

Step 2: Add attributes in the entities:

SourceDocument(document_id, language, author, publish_date)

Translator(ITAN, Name)

ITACommettiee(commettiee_no, name)

Language(id, name)

Translation(sdocument_id, translator_ITAN, isCertified)

Step 3: List relationship among the entities:

SourceDocument has one language, whereas many document can have same language. It is a Many to one relationship.

Translator may be fluent in one or many langugaes, similarly one language may have many translator. This is a many to many relationship with full participation from both sides.

Translator serve on one ITACommettiee but one ITACommettiee can have many translator.It is a Many to one relationship.

ITACommettiee associated with one language; similary a language is associated with one ITACommettiee.It is a One to One relationship.

Translator is associated with one source document, one language and one translator

Step 4: Create ERD:

**********

b. Converting ERD to relational data model:

Please follow below steps to convert ERD to relational model:

Step 1: Create table for entities:

The database will have below tables initially with primary key highlighted and foreign key having * with attribute name:

1. SourceDocument(document_id, language, author, publish_date)

2. Translator(ITAN, Name)

3. ITACommettiee(committee _no, name)

4. Language(language_id, name)

5. Translation(*sdocument_id,* translator_ITAN, isCertified)

Step 2: Solve relationships by adding references to other tables:

· Source Document to language:

Document will have a reference to its original language as below:

SourceDocument(document_id, language, author, publish_date, *language_id)

· ITA_ committee to Language

It’s a one to one relationship, let’s add the reference in committee relation as below:

ITACommettiee(committee _no, name, *language)

· Translator to Language

As it is a Many to many relationship, let’s create new relation as below:

TranslatorLanguages(*ITAN,* language_id)

· Translator to ITA_Committee

Translator can serve in one committee, let’s add committee to its translator:

Translator(ITAN, Name, *committee _no)

· Translation to language, document, translator:

A translation will have one document, one translator and one language. Let’s store it in translation:

Translation(*sdocument_id,* translator_ITAN, language, isCertified): as one translator can translate a document only once

Step 3: Final tables:

1. SourceDocument(document_id, language, author, publish_date, *language_id)

2. Translator(ITAN, Name, *committee _no)

3. TranslatorLanguages(*ITAN,* language_id)

4. Language(language_id, name)

5. ITACommettiee(committee _no, name, *language)

6. Translation(*sdocument_id,* translator_ITAN, language, isCertified)