You have chosen to sponsor your bid up to a maximum amount of .
Aim - To create a database in MySQL that can be accessed via a web page using PHP.
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.
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.
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.