Coursework Tasks
This coursework assignment has two types of tasks:
- Basic tasks: those explicitly listed in the requirements and marking items corresponding to basic marks.
- Advanced tasks: anything that are technically more complicated or advanced than basic tasks.
To do this coursework assignment choose a website of your interest, such as a website for a university research group, and do the following tasks sequentially:
1. Draw an EER diagram (which represent an EER model) for some meaningful data corresponding to this topic (e.g. a university research group).
- To earn all basic marks, the EER model should fulfil the following basic requirements: 1) it should have at least one supertype and two subtypes; 2) it should have at least one additional entity type which is neither the supertype nor a subtype in the first requirement; 3) it should have at least two (binary) relationships whose cardinality constraints are one-to-many or many-to-many; 4) it should have at least one multi- valued attribute in at least one entity type.
- The additional entity type itself in the second requirement may be just a normal entity type, or part of a different group of a supertype and subtypes.
- If you choose to produce a more complicated EER model beyond the above basic requirements, some additional marks will be considered (see Section "Advanced Tasks" for more details).
2. Convert the EER data model into a conceptual/logical relational database schema.
- You don't have to convert the whole EER data model to the relational database schema but to earn all basic marks ALL basic elements in the EER model must be covered in the relational database schema.
- If you choose to convert more parts of the EER model to your relational database schema, some additional marks will be considered (see Section "Advanced Tasks" for more details).
3. Implement the relational database schema using MySQL and populate it with data.
- You don't have to convert the whole relational database schema to the MySQL database but to earn all basic marks the MySQL database must have at least 3 relations which are all inter-connected by at least 2 foreign keys.
- If you choose to convert more parts of the relational database schema to the MySQL database, some additional marks will be considered (see Section "Advanced Tasks" for more details).
4. Use PHP to create some dynamic web content on a table on a webpage by interacting with the MySQL database.
- Please note that for the website part only PHP code for manipulating the MySQL database will carry marks, so do not over-do the website part for this coursework - instead pay more attention to the data modelling and databases parts.
A. EER Data Modelling
As the first step of your CW work, you should create an EER model to represent the data of the coursework topic (e.g. university research group). The EER model should be represented as an EER diagram and your report should include the EER diagram and explain different components of the EER model including all entity types, all relationships and their degrees, all attributes, all binary relationships' cardinality and participation constraints, associative entity type(s) if any, supertypes/subtypes and disjointness/participation constraints of the relationship among each supertype and its subtypes.
The EER diagram must be drawn following the notations taught in this module, and not those used by software tools like MySQL Workbench, phpMyAdmin or Microsoft Access.
You can use any diagram drawing software tool to create your notation-compatible EER diagram or draw it on paper and then scan it to get a high-quality image that can be embedded into your report.
B. Relational Database Design
Based on the EER model, your report should make your relational database design - a conceptual/logical relational database schema translated from the EER model.
The relational database schema should follow the more detailed edition including both relation names, attribute names and corresponding domains, and also linkages between different relations via foreign keys. The domains can be general enough rather than be very specific.
C. MySQL Code
This part is about the MySQL code in the .sql file that you will submit and the MySQL code embedded in your PHP code of your web pages. In your report you should give explanation to key issues about your MySQL code for implementing the relational database schema designed and for reading/writing data from the MySQL database. Inline comments should be added to your .sql file to explain different sections and MySQL statements.
To pre-populate your database in MySQL, you may use either real-world data or dummy data. If real- world data is used, your report should give the source of the data.
D. Bridging MySQL Database with the Web Using PHP
After you have a MySQL database implemented, you should create some PHP-based dynamic web page to show data stored in the MySQL database.
The PHP scripts that you will submit should contain embedded MySQL code for at least retrieving from the MySQL database. You should use at least one basic SELECT statement, one sub-query and one JOIN statement in your MySQL code embedded in PHP to demonstrate your understanding of both basic and advanced MySQL statements. There should be at least one dynamic MySQL query where "dynamic" means the MySQL statement is not static but dynamically constructed based on one or more PHP variables (e.g. input from a URL query string or an HTML form, data retrieved from another MySQL query, data read from a PHP function such as date(), or data read from a data file such as an XML configuration file).
E. Advanced Tasks
Some examples of advanced tasks are given below as useful references.
- Create a more complicated EER model with more entity types and/or relationships.
- Note that you do not have to convert all the additional ER/EER components to the relational database schema. If you do, those parts will be considered as advanced tasks.
- Create a more complicated conceptual/logical relational database schema with more optimisation constraints, relations and/or foreign keys.
- Note that you do not have to implement all the additional components of the relational database schema in MySQL. If you do, those parts will be considered as advanced tasks.
- Create a more complicated MySQL database with more optimised relations, constraints and/or foreign keys.
- Note that you do not have to use all components of the implemented MySQL database in your CW website. If you do, those parts will be considered as advanced tasks as well.
- Explicitly design and/or check your relational databases to fulfil a particular level of normalisation. For this one you need to explain why the certain normal form is fulfilled based on lecture material.
- Use more advanced MySQL statements, options, clauses, keywords and any other things that are clearly marked as beyond material or not covered by the teaching material.
- Develop more PHP scripts to implement more advanced database queries and handle the validation of the input data in PHP.
- Develop PHP scripts to enable privileged access control and navigation among various data and forms.
