Course Project Instructions
CSC8500 Advanced Relational Database Design and Technology
As a postgraduate student taking CSC8500, you are expected to conducted 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.
1. 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.
It is expected that your database should provide at least three different (possibly overlapping) views.
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 to the logical structure, i.e.,
relational tables, based on the transformation rules covered in the course. 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.
You need to state explicitly in each transformation what's 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.
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 a 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 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 the 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 the SQL queries that retrieve information
from the database (namely, data manipulation SQL).
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 implemented 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 interface 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. 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 softwares to produce this video. One of the good
candidates is Camtasia Studio or Camtasia Mac (www.techsmith.com/Camtasia). This software is not
free though but you can download a trial version for producing your video.
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. You can submit the
video together with other documentations and source code 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.
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;
A folder containing all the source code files for the database system implementation.
All the above three items must to be compressed to a single .zip file for submission. Only an electronic
submission through the Studydesk is accepted.
Marking Scheme
Total: /100
System definition (5 marks)
Requirement collection and analysis (5 marks)
Database design
o Conceptual database design (20 marks)
o Logic database design (15 marks)
o Functional dependencies and normalization (20 marks)
Database queries (20 marks)
Database implementation and video demonstration (15 marks)