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. 
 
 请加QQ：99515681 或邮箱：99515681@qq.com   WX：codehelp