8500 project


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


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)