CSC8500 | Database Design Assignment 4 | Database

1. Course Project Instructions

As a postgraduate student taking CSC8500, you are expected to conduct a research project to demonstrate your understanding of the key concepts and methodology of relational database design that have been covered in the course.

In this project, you will need to conduct a task of database design from scratch. You need to specify a particular application scenario where the database is to be designed. For example, you can design a relational database for a university or a company or other organizations. In this project, you need to go through several major milestones in the database design lifecycle as taught in the course (relevant chapter/section in the textbook of the milestones is also noted below).

Please note that the database you design in the project will be subject to several requirements from views, conceptual design, logic design, functional dependencies and normalization. Please read the instructions below carefully for those requirements before designing your database.

System definition (Chapter 10.4)

You need to describe the scope and boundaries of the database system and the major user views that will be involved. Typically, the system boundary (scoping) of a database system is mainly about the specification of the functions to be delivered by the system with an emphasis on the limits of what the system can do.

It is expected that your database should provide at least three different (possibly overlapping) views. Besides using the circles to visually present different views, it is also required to provide the detailed description of different views including who they are, what are their needs, and how they are going to use the database system, etc.

2. Requirements collection and analysis (Chapter 10.5)

You need to collect and analyze the information about the part of the enterprise/organisation to be served by your database. Information is gathered for each major user view about the description of the data used or generated and the details of how these data are to be used or generated. Then, such information is analyzed in order to identify the requirements or features to be included in the database systems.

You need to utilize one of the two approaches discussed in Section 10.5 to manage multiple user views.

3. Database design (Chapter 10.6)

3.1 Conceptual database design using ER modeling (Chapter 12)

ER modeling technique will be used to conduct conceptual design of your database. You need to incorporate into the ER diagram all the entities, relationships and constraints involved in the database.

Please make sure that your database is designed as such so that the following requirements are met for the ER diagram:

  • There should be at least five different entities in the ER diagram;
  • All the three different types of relationships, i.e., 1-to-1, 1-to-many and many-to-many relationships, should exist in the ER diagram.

There may be multiple different ways to draw the ER diagrams. For ease of marking, please stick to the way taught in the course for drawing the ER diagram.

Please use the word drawing tool and other third-party drawing tools for producing the ER diagram.

Please do not draw the diagram by hand.

3.2 Logic database design (Chapter 17)

The ER diagram produced in the conceptual design will be transformed into the logical structure, i.e., relational tables, based on the transformation rules covered in the course. The primary key should be highlighted for each of the tables.

Please make sure that your database is designed as such so that the following requirements are met for the transformation:

  • There should be at least five relational tables generated after the transformation;
  • All the three different types of relationships, i.e., 1-to-1, 1-to-many and many-to-many relationships, should exist in the database so that different transformation strategies will be applied accordingly;
  • There should be at least one superclass/subclass relationship in the ER diagram.

You need to state explicitly in each transformation what's the type of the relationship it is and the corresponding strategy to be applied for the transformation. Only presenting the transformed relational tables without the detailed transformation explanation will be subject to a significant loss of marks.

Please present the transformed tables in both their full and abbreviated syntax.

3.3 Normalization (Chapter 14)

The normalization process should start off with a single big table containing all the possible attributes involved in the database. To carry out normalization, you should first identify all the functional dependencies existing in the database. Please present only the full functional dependencies and try to minimize their LHS and maximize the RHS. Then, identify the candidate keys, primary keys and secondary keys using the functional dependencies.

In the possible, but less likely, case where you cannot identify any primary key for such a single table containing all the attributes, you may need to start with two or more smaller tables, each of which satisfies the first normal form.

Please make sure that your database is designed as such so that the following requirements are met for the functional dependencies and the keys:

  • There are at least five full functional dependencies;
  • At least one table has more than one candidate key (i.e., has a unique secondary key).

The normalization will start from 1NF and continue until reaching BCNF. Please note that your database should feature different normalization results for at least 2nd Normal Form (2NF) and 3th Normal Form (3NF).

You should provide a detailed explanation for each normalization step to elaborate on how the tables are split and why the higher normalization form level can be satisfied by doing so. Only presenting the normalization results without the detailed transformation explanation will be subject to a significant loss of marks.

4. Database queries (Chapter 5-7)

You are supposed to demonstrate your ability in querying your database created.

You need to generate at least five different SQL queries against your database, one of which should be used to update the content of the database and the others be used to retrieve information that should cover all the operations of selection, projection, join of up to three different tables, views and outer join (either left, right or full). It is not required that a single SQL query covers all the required operations though. You need to provide explanations on the purpose (i.e., what's the information to be retrieved) by each query.

There must be at least one query where you can provide two possible SQL statement solutions that produce the same correct results. Please present both solutions to this query.

Besides generating SQL queries, please write out the corresponding relational algebra and relational calculus (tuple or domain-based at your choice) statements for only the SQL queries that retrieve information from the database (namely, data manipulation SQL queries without making changes to the tables). You need to also provide the corresponding relational algebra and relational calculus expressions for the task with two different possible SQL queries. Yet, you do not need to present relational algebra and relational calculus expressions for the SQL queries that change the content of database and the query for the view. It is not required to express outer join expression using relational calculus either.

Please use the XAMPP system that we used in the practicum of this course to test your SQL queries. Screen snapshots of the query results are supposed to provide to prove the correctness of the query results. The screen snapshots should be included in the project report.

5. Implementation of the database system and video demonstration

A fully functional database system is expected to implement based on the database that you have designed in this project. The system can be standalone or web-based using a two-tier or three-tier database system architecture.

This system should feature necessary user-friendly graphical user interface (GUI) for users to interact with the database, enabling them to submit SQL queries against the database and view the results. The interface will allow users to both update the content of the database and retrieve information from it. The interface should accept the input from users for different attributes in order to create the predicates necessary for the queries. Different objects may be used on the interface such as the text fields and/or the drop-down lists. Directly typing the whole SQL query statements on the interface is not acceptable.

You can choose appropriate programming languages and database platforms for the implementation. Please note that the choice of programming languages and database platforms is up to you and, because of the possible variety of choices made by students, the technical support from examiners is minimum.

To facilitate the evaluation of your database system implemented, you need to make and submit a short (3-5 min.) video clip as a demonstration to show how your system is executed and working. This video demonstration is important as it provides a good opportunity for training your skills in presenting your good work to others and significantly facilitate the evaluation of your project by the marker. Your video should cover the whole operation of your system, from the start of the system until finishing going through all the major functions/features that are developed.

You can use ANY existing on-screen activity capturing software to produce this video. One of the good candidates is Camtasia Studio or Camtasia Mac ( This software is not free though but you can download a trial version for producing your video for the purpose of this course. Both Windows and Mac versions of this software are available for download.

Your video shouldn't be fancy and doesn't need to have any (sophisticated) editing. A simple recording of running your system is sufficient. It is required to record voice in the video at the same time for any explanations you may want to provide. Please export the video using the lowest resolution in order to keep its size minimum.

Please submit the video in some commonly-used formats such as MP4 or AVI. Please try to play the video file on your own computer using the mainstream players such as Microsoft Media Player or VLC Player ( before submission to ensure that it works well.

You can submit the video together with other documentations of your project to Studydesk or you can upload your video to some other websites such as YouTube and provide a link in your submission.

A sample video demonstration of the course project from a student of the previous year has been posted on Studydesk (under the instruction of the course project of CSC8500). It will be used as a reference for you to get some ideas about the video demonstration. However, it is not used as a benchmark and providing it as a sample does not mean that it is perfect. You don't have to follow the exactly same way to design your database system including the interface or make the video demonstration.

What to submit

Following is a checklist of items to be submitted for the project:

  • A single project report (a PDF/Word document) covering the design details as required by Section 1-4 of this instruction;
  • A video demonstration file or its URL address if the video is uploaded elsewhere.

All the above three items must be compressed to a single .zip file for submission. Only an electronic submission through the Studydesk is accepted.