INM343: Databases Coursework
1. Overview
The coursework is split into two parts, with two separate submission dates. Both courseworks
carry equal marks. You are required to design and build a database (part 1) and provide
access to that database by PhP server scripts (part 2). You need not provide any sophisticated
front end to your database, but if you are undertaking the INM316 Web Applications
Development module, you may wish to provide a front end for the database using the code
you build for that module.
2. Deadlines
The deadline is for coursework part 1 is Thursday 20th March 2020 @ 12noon, (50%) and
for coursework part 2 is Friday 1st May 2019 @ 12noon (50%).
3. Coursework theme
You can choose your own content topic, provided that it fits within a similar scope - i.e. as long
as it would require a similar amount of work to those listed below.
3.1 Example themes
• Reviews: books, films, restaurant, travel music, consumer/electronic goods.
• Library catalogues: public, private, university or special libraries.
• E-commerce site: buy and sell items.
• Electronic Health Records
• Implementing part or all of your INM401 Information Architecture coursework adapting
it as necessary to meet the technical requirements of INM343 (for those who have
undertaken that module), in conjunction with your INM316 coursework.
The text content of the site will not be assessed. Please contact the module leader if you want
to discuss ideas not listed here.
4. Part 1 Specification and Requirements
4.1 Specification
The brief is to design and build a database that contains a collection of material on a specific
topic. You will create a conceptual and logical design for your database together with SQL
DDL/DML statements which implement your design. Your database must model FOUR
entities at least, and therefore your database must have at least FOUR tables. You must
provide the DDL statements to create these FOUR tables, and provide INSERT statements
for each of the tables (10-20 for each table). You must also provide UPDATE and DELETE
statements for your database. Finally, you need to demonstrate that you are able to write SQL
statements, by providing a set of specific SELECT statements on your database.
4.2 Requirements
Marks are allocated as follows:
Database Design (50 marks)
• Create an entity-relationship model for your scenario. Ensure all the requirements are
captured. Use the UML class diagram notation introduced in the module, showing
strong and weak entities, relationships, attributes and multiplicity. Show primary keys
(PKs) but not foreign keys (unless they are part of a composite PK) in the diagram.
[35 marks]
• Translate the Entity Relationship diagram from your answer in a) into a relational
model using the mapping rules established during the module. Ensure that the
primary keys are indicated (i.e. underlined). Make sure that you also reference all the
foreign keys. [15 marks]
Database Construction (50 marks)
Evidence of effective use of SQL, as follows:
• Evidence of appropriate database construction using the data definition language –
functioning CREATE TABLE statements. All tables must have appropriate keys:
primary and foreign and appropriate data types for each field. [10 marks]
• A set of functioning SQL INSERT statements for each table, 10-20 for each table. [8
marks]
• Appropriate use of the Data Manipulation language; 2 UPDATE, 2 DELETE and 12
SELECT statement to both single and multiple tables. That is 16 in all, 8 for single
and 8 for statements on multiple tables. 2 marks are allocated for functional SQL
statements. [32 marks]
4.3 Delivery and Submission Requirements
This task requires a ZIP archive to be submitted through the Part 1 Assignment link on
Moodle. The archive much contain the following two components:
1. Design documentation.
A word document which contains your conceptual and logical design.
2. A text file that contains the details of your database
Provide the CREATE TABLE, INSERT, UPDATE, DELETE and SELECT statements. This is
required to assess the Database technical requirements
Once both files have been created, please wrap them up into a single ZIP archive and
submit this to the appropriate link on Moodle. Note: non submission of any files will lead to
zero marks for the relevant component.
5. Part 2 Specification and Requirements
5.1 Specification
You must build a backend script to access the databases you created in part 1.The website
must allow anyone to search and browse the content of the database, and additionally allow
users to register and add new information or comment on existing content. There are specific
functionality and technical requirements for the website, listed below. This website is to be
built using the technologies you learnt in this module: SQL, PHP and HTML. Please note that
you will need to record the details of ANY user login in other tables not associated with
database you created in part 1, which requires the creation of further tables for this element
of the coursework.
5.2 Requirements
Functional Requirements (40 marks)
You website must demonstrate the following functionality. Each item is worth [4 marks]:
1. The ability for users to search for an item.
2. The ability for users to browse for an item through links.
3. The ability for registered users to add a new item (either add a new record or
comment on an existing item).
4. The ability for registered users to update an existing item.
5. The ability for registered users to delete an existing item.
6. The ability for people to register as a user on the system.
7. The ability for users to log in if registered.
8. Ensure that SQL injections are prevented on your website.
9. Present output of results from the database in a table.
10. Ability to navigate between the different pages on the website seamlessly.
You may either demonstrate this in person, or submit a video which shows how each
functional element is successfully achieved.
Technical Requirements (60 marks)
Evidence of effective use of PHP, such as the following:
• Use of PHP to support access and display of data to the user e.g. for loops, if/else
statements, arrays etc. [40 marks]
• Use of sessions/cookies, Authentication/Authorisation AND security techniques
allowing users to log in and secure their data. The site should have
username/password functionality, and only users with verified logins should be
allowed access to secure part of the site (e.g. pages which allow insert, update and
delete access to the database you have created). You should have a table to record
user login details. The site should filter all inputs and escape all outputs to prevent
problems such as SQL injection. Passwords must be protected i.e. via encryption
methods. [20 marks]
5.3 Delivery and Submission Requirements
The website should be hosted on the City University web server, and a link must be provided
(using the https://smcse.city.ac.uk/student/username [where username is your login name]).
The source code files that make up the website, along with an SQL file that describes the
database, should be zipped and uploaded to Moodle.
This task requires a ZIP archive to be submitted through the Part 2 Assignment link on
Moodle. The archive much contain the following components:
1. A text file that contains the URL of your web page.
The web page must be hosted in your web space on the server at City University London.
So it should look something like:
https://smcse.city.ac.uk/student/abcd123/cw03a.html
where abcd123 is your user name and feel free to give your html (cw03a.html in this case)
the name you like. Please note that you must not edit the file in your web space that relates
to this URL after the submission deadline. Any submissions involving files found to have
been edited after the deadline will receive a score of zero. The submitted file must be
named:
abcd123url.txt
where abcd123 is replaced with your City username. This is required to assess the
functionality requirements.
2. All HTML/PhP files for your submission.
Please submit all the files for your submission, which must be identical to the files which are
on your web space and accessible via a URL. The simplest way to do this is to create a ZIP
archive for all the files. This is required to assess the PhP technical requirements.
3. A Video showing demonstration of the Website [optional]
If you wish to demonstrate your website using a video, please run through each functional
requirement and show that each of them works. You can do this in Microsoft PowerPoint –
please see this video on how to record your screen with that software:
https://www.youtube.com/watch?v=6T020-TI_3U Alternatively you can show this in person
on Friday 3rd May 2019 between 12noon and 5pm.
Once all files have been created, please wrap them up into a single ZIP archive and submit
this to the appropriate link on Moodle. Note: non submission of any files will lead to zero
marks for the relevant component.
6. Grade Related Criteria
General guidelines for what is expected in your assessed work are as follows:
Class % Description
High
Distinction
80+ A database of a professional standard, showing high level
knowledge of design techniques and SQL DDL/DML. A
sophisticated server-side solution that offers comprehensive
functionality using PhP together with secure database
interaction. Critical and independent thought are in evidence and
novel solutions developed with evidence of innovation in
decision-making. Both database and website would be well
received in a professional portfolio. No errors or omissions are
permitted in excellent work that achieves this grade.
Distinction 70+ A database and website of a high standard. A strong level of
knowledge is demonstrated by consistently meeting the
coursework brief, using appropriate solutions that have been
applied correctly using both design and technical methods. The
database must, show very good knowledge of design techniques
and SQL DDL/DML. A database fully connected to the web
application with an error-free server-side solution that contains all
(or almost all) the required functionality. Very good work
achieves this grade.
Merit 60-
69%
A database design and technical solution that is effective in that
it addresses the coursework requirements in many ways and is
robust on the whole. It shows some sophistication but may not
meet all of the coursework criteria. Submissions in this category
may lack some of the functionality or sophistication using
conceptual/logical design techniques or PhP/SQL than in
submissions receiving a higher grade. Some minor omissions
and errors are acceptable here, such as databases not being
completely connected to the application (and with minor SQL
errors), small non-critical server-side coding errors, or minor
errors in design. However, databases graded in this category
demonstrate a sound level of knowledge and the ability to apply
it. Solutions awarded this grade can be considered to be good
work.
Pass 50-
59%
A database and technical solution that is adequate, in that it
broadly addresses the coursework requirements. It may be
limited in terms of design technical sophistication using UML,
PhP and SQL. Some misunderstanding and minor mistakes and
omissions are acceptable here, and solutions may not address
all requirements listed. On the whole, however, students
achieving this grade will show that they are able use the various
design methods and technologies to develop viable solutions and
demonstrate key competencies in designing and building
databases for server-side web applications, Work awarded this
grade can be considered to be satisfactory at Masters level.
Fail - Poor 40-
49%
Whilst some knowledge is apparent and some aspects of the
website do work, solutions do not indicate that authors are able
to use their knowledge to design and build a database in a way
that adequately meets the coursework requirements. Key
functionality will be missing from the database and significant
misunderstanding and fundamental design/technical errors will
be evident in work graded at this level - for example databases
that are not connected to the web application (with major SQL
errors), PhP server-side code containing critical errors, that is
semantically and syntactically incorrect or conceptual design
which is incomplete or inaccurate.
Fail –
Very poor
39%
or
lower
There is little evidence that students submitting work graded in
this category have key competencies in designing and creating a
database. Both the design and application are incomplete or has
serious flaws in terms of approach, content and functionality
rendering it inadequate. Work that receives this grade is
considered to be poor and typically contains errors and
omissions that are indicative of a significant lack of knowledge or
understanding in key technologies presented on the module i.e.
UML, PhP, and SQL. Solutions are likely to fail to show that
fundamental ideas and concepts have been acknowledged and
utilised. Partial submissions and work that demonstrates poor
academic practice will be likely to be awarded marks in the lower
regions of this grade band.
Submission Process:
The following information on coursework submission re-emphasises the information
in your programme handbook. All submissions are by Moodle. No other form of
submission will be accepted.
• Please note that you are not required to submit a coversheet when submitting by
Moodle. Clicking the Submit button on the Assignment Submission screen indicates that
you have read and agreed to the declaration on the left of the submission screen. This
takes the place of the coversheet previously used for paper-based submissions.
• Once the deadline has passed coursework cannot be changed, nor can additional
materials be submitted.
• Text beyond any specified word limit will not be marked.
• Plagiarism will not be tolerated under any circumstances and where found will lead to a
formal investigation of your work and reference to the Academic Misconduct Panel. This
might result in penalties ranging from mark deduction to withdrawal from the University.
See your programme handbook for details on the nature of plagiarism and the
department's policy.
• IT IS ENTIRELY YOUR RESPONSIBILITY TO ENSURE THAT YOUR WORK IS
SUBMITTED FULLY, CORRECTLY AND ON TIME.
• It is therefore strongly recommended that you set yourself a 'hard' personal deadline for
submission well in advance of the Moodle closing date.