CSCU9B3 Page 1 of 4 Final Assignment
CSCU9B3 Database Principles and Applications
Every week, Computer Science students from various universities in China meet for
programming competitions. In any given competition, more than two universities can compete
and students win points for their university rather than winning the whole competition. Each
week of the year there is one competition, which takes place at one of a few possible campuses.
Not all universities compete every week. Universities can have different numbers of students.
At the end of the year, the average points scored by each university are calculated and a
competition table is created.
The data from the competitions, including university students and other information is stored
in a very badly designed way. One long file is kept and at the end of each competition, the
points scored by each student are added to the file. The problem is that all the information
about the student, the university and the competition must be included in every row of the file,
so there is massive data duplication.
The following data is stored about each student:
Unique ID
Name (Given name followed by Surname)
Date of Birth
University name
Study level (Master or Bachelor)
Programming skills
This is stored about each university:
University name
City
Postcode
All of this data is stored in each row of the file, along with the following data about a
competition:
The date of the competition
The campus of the competition and its classroom (where it took place)
The number of points scored by the student
CSCU9B3 Page 2 of 4 Final Assignment
For example:
ID
Student
Name Birth Date University
Study
Level Skill University City Postcode Campus Classroom Date Points
12981 Sheng Li 2001-12-09
Shanghai
University Master Java
Shanghai
University Shanghai SHG Campus North R-9 2022-01-03 3
12981 Sheng Li 2001-12-09
Shanghai
University Master SQL
Shanghai
University Shanghai SHG Campus North R-9 2022-01-03 3
12554
Xander Chang
2001-05-07
Chengdu
University Master Python
Chengdu
University Chengdu BJG Campus North R-25 2022-01-03 7
12554
Xander Chang
2001-05-07
Chengdu
University Master
Ruby on
Rails
Chengdu
University Chengdu BJG Campus North R-25 2022-01-03 7
12554
Xander Chang
2001-05-07
Chengdu
University Master PHP
Chengdu
University Chengdu BJG Campus North R-25 2022-01-03 7
What is worse, if a student has multiple skills, then the same data is repeated for each of the
skills, as shown in the table. The data does not provide a comprehensive list of every university
that took part in each competition: to see that you would need to look at every row for a given
date. Note that there is only one event on any given date.
You are required to turn this data into a relational database.
The data is stored in the rawdata.csv file (and a version without header information, for
loading into the database, in noheaddata.csv), which you can download from your VLE.
Your assignment is to complete the following steps and present your results with comments
in a written report. These instructions are detailed and following them properly should ensure
you get good marks:
1. Design a set of tables for a relational database to store this data(decomposition).
2. In your report give an ER diagram showing the relationships between the tables. In your
diagram, make sure you:
a. Put the table name at the top of each table
b. List the attributes in each table
c. Highlight the primary key attributes with keyword PK
d. Highlight the foreign key attributes with keyword FK
e. Mark the cardinality of each relationship at both ends of the connecting line
f. Indicate optionality with a dashed line or circle symbol.
3. In your report, write a justification for your design, considering aspects such as data
integrity and normalization.
4. Create these tables in MySQL by writing and executing the SQL for creating each of the
tables, including all primary and foreign keys. Make sure you choose the right data type for
each column. In your report, show the SQL you have used.
5. Write and execute SQL to create a table to hold the data from the nohead.csv file and then
upload the data into your database via the phpMyAdmin import facility (no need to
mention this step in the report).
6. Use SQL statements to extract the data from your table created in step 5 into the correct
tables that you created in step 4. In your report, show the SQL for doing this for one
example table only.
7. In your report, write the SQL you would use to answer each of the following questions, and
also include the results you get from executing the query. You must not use the table from CSCU9B3 Page 3 of 4 Final Assignment
step 5- make all your queries from the tables corresponding to your ER decomposition
made in step 1.
a. Search for all the students who are from Chengdu University or Chongqing University.
b. List each student name and birthdate who was born before 1
st
January 2022.
c. List the total number of days competed by each university.
d. Search for all the students with Rui somewhere in their names.
e. List the campus names and classrooms of all the competitions where students played
during March month of year 2022.
8. Starting with the template file, assignment.php (available from VLE), using the PHP mysqli
package (either the procedural or object-oriented version) complete the PHP and SQL
required to take whatever text is entered in the form box and do the following:
a. Search for any students whose names (given name or surname) contain the text
entered.
b. Display neatly in the web page the following characteristics of all matching students
found: ID, name, birth date, university, study level, skill
c. Try out your code by placing this file (DO NOT rename it, and do not change the
default settings on the localhost server) in your web folder on www for Windows and
htdocs for Mac.
d. Submit your report through the VLE, including a copy of your code.
Make sure your code is robust against any mistakes or malicious intent in text entered in the
form box by a user of your webpage. During marking, your webpage will be trialled, and
this will be checked. As above, you should carry out your queries on the tables
corresponding to your ER decomposition in part 1.
Marking breakdown and criteria
Marks will be awarded both for the technical correctness of what you have done and for the
clarity and organization of how you describe it.
Your work will be marked out of 100, according to the following breakdown:
1. ER diagram: 20%
2. Justification of design: 20%
3. Table creation: 10%
4. Data transfer: 5%
5. Searches: 25%
6. PHP (Assignment.php): 15%
Overall quality of report: 5%
Submitting your work and assessment procedures
The assignment will be submitted as an electronic (PDF) type-written report uploaded (via
Turnitin) to VLE by the above-mentioned deadline. DO NOT put your name in the report,
only your registration number. CSCU9B3 Page 4 of 4 Final Assignment
In the report, include all the components listed in the assignment steps. The report should be
professionally presented and easy to read. 5% of the marks will be given for the quality of the
report.
Late submission
If you cannot meet the assignment hand-in deadline and have good cause, please use
the extension request link on the left-hand navigation to explain your situation and ask for an
extension. Coursework will be accepted up to seven days after the hand-in deadline (or expiry
of any agreed extension), but the mark will be lowered by three marks per day or part thereof.
After seven days, the work will be deemed a non-submission.
Plagiarism
Work that is submitted for assessment must be your own work. Plagiarism means presenting
the work of others as though it were your own. The University takes a very serious view of
plagiarism, and the penalties can be severe (ranging from a reduced mark in the assessment,
through a failure mark for the module, to expulsion from the University for more serious or
repeated offences). We check submissions carefully for evidence of plagiarism and pursue
those cases we find. Further details can be found here:
https://www.stir.ac.uk/media/stirling/services/academic-registry/documents/Policy-andProcedure-Academic-Integrity_v4_FINAL.docx
Note
Students should be aware that a copy of their coursework will be retained in Canvas, and it may
be used anonymously to create an exemplar answer for future students. If you do not wish your
coursework to be used for this purpose, please inform the module leader upon submission
using the Comments box.