question 1

A power station company has a manual incident reporting system. Due to security concerns every movement inside the power station needs to be reported and stored involving staff or visitors. The power station has hired you to develop an Incident and movement reporting system.

Below is the transcript from an interview the data architect conducted with the person responsible for the new system.

Details must be recorded for each power station, including the name of the power station, the location, date of construction and number of reactors. All movements in and out of a power station must be recorded. Each movement is called an ENTRY, and a particular entry is only for one power station but the power station will experience many entries. For each entry, power station wishes to record a unique entry number, the entry date/time, the date/time of departure, the duration of the entry, the reason for the entry and the number of people in the party. The reason for the entry is also called the entry type. Each entry must have one reason and we maintain a list of entry codes. There are currently three reasons for entry (entry types), namely visit (V), contract/maintenance (CM) and full-time employee (E).

For entry type E, we store the employee id only. For entry type CM, we store the contractor id and the reason for entry (one only). Employee id and contractor id relate to two entities (EMPLOYEE and CONTRACTOR) outside the scope of this system. For entry type V, we store the temporary identification card number and visit authorisation number of the visitor. Each visit must be authorised after a formal application, but a single authorisation could cover many visits (or one). For the authorisation, we store name, address, age, gender, email, reason for entry, name of person issuing authorisation, date of issue and period of validity.

During any entry, we record details of any incidents that might occur. A particular incident always relates to a specific entry but an entry may have many incidents associated with it. For incident, we store a unique incident id, the time of the incident and the location. For this, we use a list of locations which comprises a location id and a description. A particular incident must occur in a specific location but a location can be associated with many incidents over time.

An incident must be of a specific type, for example minor accident, but more complex incidents could comprise multiple incident types. We maintain a list of incident codes with descriptions. Each incident type could apply to many incidents over time. If an incident is complex and comprises more than one incident type, we rank the incident types in order of impact.

Prepare the following:

a)An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.

b)A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.

c)An Oracle SQL table create statement for the relation that you think is most critical in this system. This relation must have a primary key and at least one foreign key.