To develop a MySQL database which can be accessed via a web page.

2. Aim and Objectives Aim - To create a database in MySQL that can be accessed via a web page using PHP. Objectives To demonstrate understanding of database design and creation. To show competence in SQL queries for data manipulation and data definition. To produce an appropriately structured and normalised database from a specified data set. To develop a web interface capable of interacting with the database. 4. Instructions A university department currently has a spreadsheet which holds timetabling information for staff, in order to keep track of the number of hours that a person teaches and the classes they are associated with. This is not a very efficient solution, and so they would like to move this information to a database. Your job is to create this database from the data given. The information that the department currently has on file is as follows: Module codes and titles Which semester a module is taught in (1 or 2) Which year of the course the module is taught in (Year 0, 1, 2 or 3) The name of the staff member teaching each module, and their room number The number of hours per week that each module is taught for A member of staff then currently also has to calculate the total number of hours worked by each member of staff, by adding up the hours that they teach for each module they are associated with. Task 1: creating the database Create a database using MySQL that stores all the information the department holds. This database should aim to have all the following requirements: It should be normalised to at least 3rd Normal Form Foreign keys should be used to link tables, with appropriate referential action being taken The total number of hours worked by each member of staff should be calculated and stored in either the most appropriate base table or in a view. This database should then be populated using example data from the document provided. You do not need to enter all the data, but enough should be added that the database can be tested. Task 2: creating a web interface You should already be able to access your database using MySQL to run queries and make updates to the data. For ease of use by staff members, it would be better if they could access the data through a web interface. Using PHP, create a web page that allows users to query and update the database according to likely roles and tasks. These may include the following: Search for a staff member and return the classes they are teaching Search for a module and return information about who is teaching it Display all staff who are timetabled for more than 12 hours a week of classes Show all modules that do not currently have staff assigned Add a module, or make changes to any existing modules Remember that the users will not be expected to have knowledge of SQL queries -- they should be able to query the database by selecting options on the web page, for example using menus or selection boxes. 5. Deliverables You will need to deliver a short report as part of your assignment. This will include the following: a definition for each relation, listing the attributes and the primary/foreign keys for each relation, details of the functional dependencies which exist within it a short description of how you ensured that the database is normalised The exported SQL files and PHP files that you created need to be delivered. These will also be demonstrated to your tutor in class so they can test the database and you can explain how it works. The weekly worksheets for weeks 1-5 should be submitted along with the files and report.