代写CSCU9B3、代做Python，Java编程

日期：2023-12-26 09:28

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.