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.
Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. You must use the Finkelstein methodology as used in the study book and tutorials.
GYM(gym number, gym name, address, gym contact, ((class id, class time, class start date, class end date, number of session per week, ((trainer id, trainer name, qualification)) )), ((member id, member name, member contact, age, gender, join date, pay date, pay frequency)), staff name, staff contact)
Notes:
1. Staff name uniquely identifies staff in the gym.
2. Each class may have 1 or many trainers.
3. There are many members in the gym and always one staff.
Below is a reproduction of the ERD from the JustLee books database. It should help you navigate the tables in the database. The database script to create the tables is located on the Moodle site under the assignment specifications. If you have run a version of the script earlier in the semester please run it again to ensure that you are using the correct version of the tables. The ERD diagram may not necessarily have all the fields listed so you might have to refer to the description of the table by using the DESCRIBE command in Oracle.
FIGURE 1-5 JustLee Books's table structures after normalization see image.
The question in this section are challenging. Most require a number of tables and/or nested queries. When solving each question it is best not to try and write the solution as a single activity. Instead try and write a separate query to solve each of the parts and once you understand the data and the results rewrite the query into a solution.
Each question below is worth 5 marks. For each question, provide the SQL queries to meet the question's specifications and the output result of running your query.
1) Display ISBN, book title, Category, Author name (FNAME||' '||LNAME) and Publisher Name that had no sales recorded for them.
2) Using a subquery, display the client name for all clients who have placed an order where any order items has more than 1 item. Do not use any table joins in your query.
3) Display book title, Category, publisher name, author first name and author last name for all books that have been co-authored and the title of the book has letter 'SS' anywhere and ends with letter Z. Order the result in ascending order by author last name.
4) Display the book Category and the average retail price after discount in all categories where average discounted price is less than the average retail price of books for all the categories. Sort the result in category order descending.
5) Display Book Title, Category, Publication date and Author last name where 1) Author's last name and first name starts with the same letter, 2) Category starts with C and 3) title contains the word 'THE. Order the output by author last name in descending order.
6) Display the Customer number, customer name in a format firstname followed by a comma and last name (renamed as CUSTOMER NAME) and the total number of orders the customer has placed for all customer who have placed at least one order in March.