Database Theory and Practice To develop a MySQL database which can be accessed via a web page

Title CPU5002 Database Theory and Practice – Assessment Item 1 of 2

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

1. Key dates:
Issue Date: Friday. 7th November 2014 (teaching week 8)
Deadline Date: Wed. 17th December 2014 (teaching week 13). Files also to be demoed in the timetabled class session of teaching week 13.
Students who do not submit work by the above specified deadline (without an extension being granted or without approved mitigating circumstances) will be subject to the penalties specified in section 5 of the Student Handbook (see http://www.bolton.ac.uk/Students/Student-Handbook-2013-14.pdf ).
The rules and regulations relating to suspected plagiarism are also detailed in the handbook.
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.

3. Assessment Criteria
This coursework assignment is the first of two equally weighted assessment items. The second item will be the written examination at the end of the semester.
You must pass EACH of the two assessments (minimum assessment pass mark = 35%) and score an overall average mark of 40% to pass the module.
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.
6. Marking guidelines (remember that these are only guidelines and the tutor’s awarded mark is final)

1. The development of the MySQL database, website and short report detailed above (100%). The tutor will use the following guidelines when marking these elements…
3rd class honours level.
Create a normalised database that can hold all the data from the spreadsheet. Populate it with data about at least years 0 and 1 of the course.
Ensure that appropriate primary keys are used for each relation.
Produce a report which provides a definition for each relation, and lists functional dependencies for the tables created.
Show that the database can be queried through a web interface.

2.2 honours level

As above, but also:
Demonstrate that the database is normalised to at least 3rd Normal Form, including a description of how this is achieved in the report.
Use foreign keys to ensure referential integrity.
Calculate and store the total number of hours worked by a member of staff.
Provide a web interface that can run most expected queries, including updates to existing tables.

2.1 honours level

As above, but also:
Provide a good web interface which makes use of menu choices and selection boxes for ease of use, so the user does not need to know the contents of the database.
Ensure that the user can update existing tables and also add fields such as new modules or new members of staff. Users should not need any knowledge of SQL.
Use appropriate referential action for foreign keys and demonstrate how this works.

1st class honours level

As above, but also:
Demonstrate a solid understanding of functional dependencies and normalisation in the report.
Add extra features of your own design to the web interface to improve usability. Some examples might include being able to generate personalised reports for each member of staff, producing departmental rankings of which staff are working the most hours per week, or simulating security features so that a member of staff can only view their own workload unless the username they enter is associated with extra privileges (i.e. a head of department)

Can you please use the file evil_genius_BSc %286%29 you need this to do your database tables in this assignment? I will uploaded this file with this order? Can you please use XAMPP software https://www.apachefriends.org/ for this assignment this should be your creating the database and a web interface use https://www.apachefriends.org/ this link to download a free version of XAMPP it is free.
Can you please use basic coding so it can be easy to understand and document the program fully use basic comments so it can be easy to understand? Before you start this assignment read the assignment brief carefully and make sure you understand the instructions then create the tables on the computer using Microsoft Excel include the data inside the tables I have uploaded the document that includes the data then normalise it use Microsoft Access for this the tables then you can create the tables using the Xampp software you have to download this free from the Internet to create the tables using PHP to create a MySQL Database is that OK. Can you also please normalise the Normalization exercise for this assignment I have uploaded it with this order for this assignment?