CE153 – Introduction to Databases – Assignment

About this assignment

Submission Deadline: Thursday the 4th of December, by 11:59:59 via electronic submission (week 10).
Demonstration: The work is to be partially marked in the labs on Monday, 8th December (week 11).
Electronic submission: Make sure you have read the assignment submission guidelines in the Undergraduate Student Handbook.
Marks: This assignment is worth 20% of the marks available for the module, and 50% of the marks available for the module's coursework.

Important Advice

Please read the entire assignment before you begin, and make sure you address all of the aspects. Pay very close attention to the submission requirements. Failure to follow the instructions may result in a mark of zero.
Ensure you are familiar with the University's rules governing plagiarism. Any breach of anti-plagiarism rules can have serious consequences.
University regulations require that late submissions receive a mark of zero.

Background

You’ve recently started a job as a web programmer for Lala Software Solutions Ltd, who has secured a contract for a small, but fully functional, online journal/web log (blog) system. The initial requirements specification has recently been completed, and the major components of the system have been identified. The next phase is to design and implement a suitable database within the allotted schedule.

Specification

The proposed database is to hold information about ‘blogs’, including uniquely identifiable details on Editors, Posts, Categories, and Comments.

Each Editor (identified by their username) can make many Posts, and must have a name, email address, date of birth, and a title for their blog.

Posts are authored by only one Editor, and must have a title, the date/time (timestamp) that the post was made, and a content attribute capable of storing large amounts of text.

Each Post can also be assigned to more than one Category. Many Posts can be assigned to the same Category. Initially only the following Categories are required: ‘General’, ‘Musing’, ‘Social’, and ‘Work’; this list is expected to change over time, and your design should reflect this.

Each Post can have any number of Comments, made (possibly anonymously) by people who are not registered in any way in the system. Comments can only be made against a single Post, and must contain the commenter’s (possibly null) name, (possibly null) email, the date/time that the Comment was made, and a content attribute capable of storing large amounts of text.

Deliverables

Marks in paranthesis give an indication of the marks available as a percentage of the total marks available for the assignment. Up to 5% of the marks may be reserved for presentation quality.

1 Database Design (40%)

You should present your design for the database described above, using the ER notation described during this course. The final design should have identified all of the following:

Entities and their attributes
Primary and composite keys where appropriate
Appropriately named (e.g. ‘holds’) logical relationships between entities, including their cardinality (e.g. 1:N)

You have the freedom to define what attributes you wish, even if they have not been explicitly identified; indeed you are encouraged to do so. However you should not include additional entities beyond those you can identify from the specification. Make as many simplifications as you see fit, for example: assume that once a Post has been made it cannot be edited.

You can start by expanding the following ER diagram.

./blog-outline-ER.png

Outline ER Diagram

It is recommended that producing your ER diagram should be accomplished using an appropriate drawing software be used as long as your result is in the appropriate notation (as taught in the labs), is clearly and neatly laid out, and can be submitted electronically and readable on a lab machine.

The ER diagram should be submitted as a PDF file called ER-Diagram.pdf, or similar, or if you cannot produce a PDF file, then a PNG or JPG file (ER-Diagram.jpg or ER-Diagram.png, or similar). Ensure that all text is readable.

It is acceptable to draw your diagram by hand, as long as it is clearly presented (please use a ruler!) and scanned into your report.

2 Normalisation Analysis (15%)

Submit a discussion (maximum 1 page) on the application of Boyce-Codd Normal Form to your design in the format:

A short summary of Boyce-Codd Normal Form (1 paragraph)
Begin with the database you have designed. For each entity, state: (a) The superkeys; (b) The dependencies
Summarize the changes you would have to make (if any) to your database for it to be normalized in BCNF.

Note: You are not to modify your existing design. You will be marked on your knowledge and understanding of the normal form, your ability to apply it, and knowing when to apply it.

Your analsis should be submitted in a formatted PDF document called BCNF.pdf, or similar. Please remember to include your name in the report (e.g. in the page header).

3 Database Implementation (25%)

Write an SQL script for MySQL that constructs a database called ‘BlogDB’ that corresponds to your design after normalisation. You should ensure that every entity, attribute and relation that you included in your design is correctly implemented. You should also populate your database with logical sample data. The sample data should include at least two editors, six posts and ten comments.

Your answer to this part should be included in a file called Script.sql, or similar, together with an appropriate execute.cmd command file. The script should create the tables, and populate the database with some sample data. This script will also contain the SQL queries that you provide as answers to the next part.

See the course website area for scripts you can work from.

It is up to you to investigate and use appropriate types (available in MySQL) for your attributes.

4 Data Manipulation (20%)

Using the database implemented, and populated, as a result of Part 3, write SQL queries for the following problems at the end of your script file.

Query 1 (4%)

For each Editor, calculate and retrieve their Name, Email, and Age.

Note: Age must be calculated; no marks will be given otherwise.

Query 2 (6%)

For each Editor, calculate and retrieve their Username, Blog Title, and a count of how many Posts they have made.

Query 3 (10%)

For all posts calculate and retrieve the Editor’s Username, Post Title, Post Timestamp, and a single attribute listing all the Categories to which the Post is assigned.

(See the MySQL group_concat aggregate function.)

Your answer to this part should be included in the Script.sql file used for the previous part of the assignment.

Submission

This is an individual assignment. It is a serious academic offence to pass off the work of others as your own, or to fail to acknowledge sources that you relied on in order to complete the work. You may discuss the task in a group, but you must complete and submit the work on your own. If your submission builds on the work of others, or on work that you have done before, this must be stated in the written report.
Your submission must be made electronically by the deadline at the start of this document: University regulations require that late submissions be awarded a mark of zero.
You must demonstrate Parts 3 and 4 in your Lab session on Monday of Week 11. Failure to demonstrate your submission may result in lower marks.
You will be expected to be able to answer questions on Parts 1 and 2 during the demonstration.

Your electronic submission can be individual files (preferred), or a zipped archive of files whose names and content are as follows:

ER-Diagram.pdf: A PDF file, or a standard image type such as JPEG or PNG, that contains your solution to Part 1. (This could be produced in a drawing package such as Smartdraw, Microsoft Visio, or Dia, but must be submitted in a standard format that can be viewed outside the drawing tool, such as PDF. If you cannot produce a PDF file for some reason, you may submit the diagram as a bitmapped image, e.g. ER-Diagram.jpg or ER-Diagram.png. Ensure that all text is readible in any bitmapped version.)
BCNF.pdf: A PDF document that contains your discussion for Part 2. Please include your name in this report. (This document should also contain any acknowledgements of work that has been used or reused for this assignment.)
Script.sql: A MySQL script containing your solutions to Parts 3 and 4, along with a relevant execute.cmd file.
execute.cmd: A file that executes the script Script.sql.
log.txt: It is recommended that you also submit the logfile that is created when you run your script, as specified in the execute.cmd file. (This can help with the assessment of your work in the event that bugs are encountered.)

If you submit individual files, please ensure that as far as possible they have the names indicated. If you use different file names, ensure that whatever you submit as the "execute.cmd" file runs the appropriate "Script.sql" file.

Ensure that:

all your PDF files (ER-Diagram.pdf and BCNF.pdf) can be read by standard PDF tools, such as Adobe Acroread (don't just change the filename extension to "pdf" and expect it to work);
that the text is legible in any bitmapped images;
that your MySQL script (Script.sql) works correctly (when run by execute.cmd); and
that the log.txt logfile is the one created when you run the script as submitted.

Good luck!