代做CS-250留学生、代写SQL/PHP设计、代做PHPMyAdmin、代写SQL

日期：2018-11-07 10:12

Coursework 2: ER, Normalization, SQL, PHP

Task 2 (40 marks):

You are required to submit your PHP code (in pdf) that generates the “Student Record Card” for any student id.

30 marks will be awarded if your PHP code works as expected:

Up to 10 marks will be awarded if the generated record cards look as close as possible to the given ones:

a) Provide screenshots of three record cards with different student ids (see specification in Task2).

b) Clear presentation and formatting (including comments for your code).

Coursework Submission (please read carefully the instructions)

You should submit the following to the Blackboard assignment collection page:

a written report (pdf only), electronic submission.

B. SQL+PHP (worth 10% of CS-250)

There are two tasks in this part B. You will use SQL knowledge to help University to set up and answer some

questions regarding their student records database, and to help generate a “Student Record Card” page.

This part gives you practical opportunities to write SQL queries of a large database. Full marks can only be obtained if

you demonstrate clearly that you have tested the queries on an actual implementation of the database. All test data is

randomly generated, and all data is fake - they are for testing purposes only. Data is provided to you in the form of

text files, as explained below:

Underline: primary key : foreign key

No table allows NULL values.

Enrl Status:

NE “Not enrolled” W “Withdrawn”

E “Enrolled” S “viSiting”

ER “Enrolled Repeat”

Table descriptions:

stud – student details

prog – program details

dept – department details

mods – module details

smod – student’s annual module selection

comp – compulsory modules of particular levels of the

programmes

enrl – student’s annual enrolment on programme

Attribute descriptions:

attribute description example

pid programme code G500

Ptitle programme title Computer Science

Paward programme award BSc

length programme length 3 or 4

did department code CS

dname department name Computer Science

mid module code CS-244

mtitle module title Software Laboratory

credits module credits value 10, 20, 40, 60, or 120

ayr academic year 2001/02

sid student number 123456

lastname student's lastname Stein

firstname student's firstname Frank Norman

title student's title Mr, Miss, Mrs, Ms

dob date of birth 250781

gender gender m or f

lvl level 1, 2, 3, or 4

status enrolment status NE, E, ER, W or S

Before you proceed, you are required to create respective tables in MySQL, populate those tables with provided data.

You can use the following syntax to load data into MySQL (for example into stud table) via console:

load data infile 'stud.txt'

INTO TABLE stud

fields terminated BY ',' enclosed BY '"'

lines terminated BY '\r\n';

Task 1 (SQL):

You are required to give one single SQL statement to answer each of the questions. Present your answer concisely:

a) Show the SQL statement(s) you used and

b) The results obtained from your database (e.g., copy results or screenshots from console / PHPMyAdmin).

Important Notes:

- you must use an SQL formatter to tidy up your SQL statements (http://www.dpriver.com/pp/sqlformat.htm)

- you must observe the font size limit. This applies to all screenshots, figures and tables.

Otherwise, marks will be deducted for poor presentation, or no marks will be given if it is incomprehensible.

You are NOT allowed to use natural join keyword. Otherwise, no marks will be given.

Question 1. For each department, get the department name and how many modules they have available. (15 marks)

Question 2. How many students in 2001/02 have not selected a module which is compulsory for their programme and

level? Count the student only once if he/she has multiple such modules. You are NOT allowed to use IN or

Left/Right (Outer) Join for this question. Otherwise, 50% marks will be deducted. (15 marks)

Question 3. Find the student(sid) who have ever enrolled on 2 or more programmes offered by different departments.

You are NOT allowed to use group by / having in this question. Otherwise, 50% marks will be deducted. (20 marks)

Task 2 (SQL + PHP):

Your final task is to generate an “Student Record Card” that looks as close as possible to the ones below using PHP,

given a student id (to be submitted from a form textbox). See the two figures below.

1) The card should show the personal, course, enrolment and module selection details.

2) In the module detail section, all modules should be listed and grouped in descending order of academic year.

3) The order of modules in each academic year does not matter.

4) The student record card should show the total credits taken per academic year (if there are any).

To show that you have done this part, you should submit (in the pdf) the following:

1) Syntax highlighted PHP code that you wrote (only one php file, called sustudent.php)

2) Screenshots of three receipts with different student ids.

- All student ids must be students who have selected modules and enrolled for at least two years.

Strong Tips: Don’t waste time on fancy CSS styles or layout. Nested HTML tables are all you need!