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)
http://www.dpriver.com/pp/sqlformat.htm
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!