SCENARIO

In a software company. workers are standardly identified by their initials, but also have a unique name. Each worker has access to exactly one personal computer (PC). and each PC is accessed by at least one worker. For each PC a record is kept of its room location, the worIcerfs) who access it, and the programming language(s), if any. installed on this PC. A sample report from the software company with the above discussed information is proposed below.

Each programming language is one of three types (declarative, functional, or procedural). The PC a worker accesses must be in the room in which he/she works. Some workers are hived as experts in one or several programming languages. The next table gives a full record of the languages. their types. experts at languages. and each expert's room.

A workshop on programming languages is to be delivered by some of the workers. The full workshop program, shown in the final table below, indicates how many hours (h) speakers talk about each language and the total hours for each language type.

TASK 1

As of today. all the information described in the scenario is kept on paper. You are hired to design an information model that suggests an effective and efficient way for storing this information in a to be developed information system. As you are an expert in ORM, you decide to develop an ORM model.

Complete the drawing of the ORM conceptual schema proposed below for the universe of discourse specified in the scenario on page 2 by performing steps 1-5 of the Conceptual Schema Design Procedure. Use only those entity types and value types proposed below (do NOT introduce new entity or value types).

Step 1: Transform familiar examples into elementary facts, and apply quality checks.

List all the deep structure sentences that you can identify based on the familiar examples from the scenario.

For example:
The Worker with Workerinitials 'EFC' uses the PC with PCNr -pc01"*. Step 2: Draw the fact types, and apply population checks.
flint: Do NOT use nesting.

Use the deep structure sentences from Step I to draw fact types. As an example. consider the only binary fact type shown in the diagram above. Note that this fact type is introduced based on the example deep structure sentence proposed in the description of Step I.

Perform population checks by populating identified fact types with the fact instances captured in the deep structure sentences listed in Step I. This task can be accomplished by drawing the corresponding fact tables.

For all the identified fact types discuss/demonstrate that they are indeed elementary by performing all the necessary split and join operations on sample populations.

Step 3: Check for entity types to be combined, and note any arithmetic derivations.
Discuss, in text, if it does or does not make sense to combine any of the entity types proposed in the diagram. In this discussion, do not exceed the word limit of 300 words.

Introduce ONE arithmetically derivable fact type from the scenario into your ORM model. You can use any notation, e.g., a simple textual description, to explain the rule for this derivable fact type.

Step 4: Add uniqueness constraints, and check the arity (length) of fact types.
Introduce all the uniqueness constraints in your ORM model that you can identify based on the scenario.
For each introduced uniqueness constraint, briefly explain the rationale behind your decision to include it in the model.

Step 5: Add mandatory role constraints, and check for logical derivations.
Introduce all the mandatory role constraints in your ORM model that you can identify based on the scenario. For each introduced mandatory role constraint, briefly explain the rationale behind your decision to include it in the model.

Introduce ONE logically derivable fact type from the scenario into your ORM model. You can use any notation, e.g., a simple textual description, to explain the rule for this derivable fact type.

TASK 2

Suggest some additional piece of information that the software company can store in an Information System by extending the ORM model obtained as your answer to Task I. Your extension should introduce 3-4 fresh entity/value types and several fact types to the model.

For example, one can propose to the software company to keep track of teams/groups of workers that are currently involved in different software projects. One can start implementing this extension by introducing entity types Team and Project to the model.

For the extension, implement and discuss steps 1-5 of the Conceptual Schema Design Procedure by following the structure proposed as part of Task 1.

In your final report, present the extended model separately from the model you developed to answer Task 1.

TASK 3

The conceptual schema diagram shown below incorporates the use of various constraints to enforce the rules of a universe of discourse. Apply each transaction shown in the table on the figure to the population shown on the diagram and indicate whether the transaction is accepted (A) or rejected (R). If a transaction is rejected. indicate which constraint has been violated. Each of the requests applies to the same schema population shown below. Treat each request as if it was the first to be made with this population.

Step I;
- All elementary fact types from the scenario are recognized and are exemplified by at least one correct deep structure sentence.
- The proposed deep structure sentences correctly reflect most of the elementary fact types from the scenario.
- The proposed deep structure sentences correctly reflect some of the elementary fact types from the scenario.
- The proposed deep structure sentences do not reflect any of the elementary fact types from the scenario.

Step 2;
- All elementary fact types from the scenario are correctly drawn in the diagram. Each elementary fact type is supplied with a fact table that correctly contains some facts from the scenario.
For each elementary fact type, a short explanation is given on why this fact type is elementary.
- Most of the proposed fact types, fact tables, and explanations are correct.
- Some of the proposed fact types, fact tables, and explanations are correct.
- None of the proposed fact types, fact tables, and explanations is correct.

Step 3:
Two sub-tasks are accomplished correctly: (i) Entity types that can be combined are correctly identified, or an explanation on why none of the entity types from the diagram can be combined is provided, and (ii) all the required arithmetically derivable fact types are identified and introduced in the diagram correctly, or an explanation on why no arithmetically derivable fact type should be introduced in the diagram is provided.

One of the above two sub-tasks is accomplished correctly.

None of the above two sub-tasks is accomplished correctly

Step 4:
- All the introduced uniqueness constraints are correct with respect to the familiar examples from the scenario. Each uniqueness constraint is supported with a short explanation that justifies its inclusion in the diagram.
Most of the proposed uniqueness constraints are correct.
- Some of the proposed uniqueness constraints are correct.
- None of the proposed uniqueness constraints is correct.

Step 5:
- All the introduced mandatory role constraints are correct with respect to the familiar examples from the scenario. Each mandatory role constraint is supported with a short explanation that justifies its inclusion in the diagram.
Most of the proposed mandatory role constraints are correct.
- Some of the proposed mandatory role constraints are correct.
- None of the proposed mandatory role constraints is correct.