ODT Database

a) database tables: see image.

b) Business at ODT Company has been steadily increasing and the old database structure no longer meets their requirements.

• The current system doesn't hold enough information on the trainers. ODT need to store trainers names, addresses and email addresses.
• As well as contact details ODT needs to know which courses each trainer is qualified to teach on. As a number of trainers are qualified to teach each course ODT has assigned one trainer as the lead for each course. It is the course leaders responsibility to make ensure the quality of the course, and to develop the teaching material. Of course a trainer could be the lead for one course, but qualified to teach on other courses as well. Also, not all trainers will necessarily lead a course.
• Due to expansion, ODT has now expanded over three buildings where their training rooms are located. Each course instance must be timetabled in its own room that is located in one of the buildings. This information needs to be recorded so that students can be informed of the location.
• ODT have found it difficult to allocate the correct training rooms to some of the courses. This is because some of the courses require specialist hardware or software. They would like to keep a record of which courses require particular specialist set-up and also what is available in each training room. They would then be able to ensure that a course instance is allocated to an appropriate training room.

c) Enhance the ODT ERD to take account of the additional requirements above. Justify your solution, explaining how it meets the new business requirements, and also discussing any constraints your solution may put on future business enhancements.

d) Implement the changes in (c) using SQL scripts. Don't forget to test your system with sample data. Document your changes and your testing of the system (you do not need to change your ERD in part (c) but a DB diagram should be produced to show the final tables in your DB you will need to copy this onto your documentation). Show your testing is comprehensive by justifying the test data used and the tests carried out. You must implement and test using SQL code.

1) Create a stored procedure that will add a new trainer to the system. The procedure should also assignment the qualification of the trainer to teach courses. The procedure should return a list of the upcoming (in the next 2 months) course instances that the trainer is qualified to teach (you do not need to allocate the trainer to a course instance). Show the testing of your stored procedure.

2) documentation explaining how you created ER Diagram (the first diagram that you created for this project), what tools used, why PK and FK are set, the way that they are