Assignment:
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
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
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
3.1 Conceptual database design using ER modeling
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
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 whats 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
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
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., whats 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 shouldnt be fancy and doesnt 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.