G51D.B.S. Coursework1of1

08 Jun G51D.B.S. Coursework1of1

Submission Method: Electronic Submission

(http://support.cs.nott.ac.uk/coursework/cwstud)

CW Submit ID: 463

Deliverables:cw2012part1.doc or cw2012part1.docx

cw2012part2.sql

index.php

Important notes:All three parts of this coursework have template files. You will add to/alterthe marked parts of the template files then submit the three files (ONLY) as one submission using “cw submit”.

Please ensure that you fill in your username at the top of each of the submitted files.

General Suggestions

1. All three parts of the coursework are related and logically follow on from one another. Therefore working through Part 1, Part 2 and Part 3 in order will be beneficial.

2. There are a small number of marks allocated for “neatness” and presentation. Marks are also awarded for appropriate naming, such as using prefixes and capitalisation in MySQL.

3. Your submission for Part 3 only includes your “index.php” file. For this reason, do not add functions or other code to any other files. If you wish to write helper functions, put them in index.php so that they can be run by the marker.

4. You can find information on electronic submission at http://support.cs.nott.ac.uk/coursework/cwstud

Late Submissions

Late submissions will be treated as per the University’s standard procedure. In short, 5% is deducted from your mark per working day.

Plagiarism Policy

Any material that you submit which is not your own work must be clearly identified as such. This includes (but is not limited to) written text, figures, diagrams, and SQL statements. Any material that is not your own work and is not clearly identified and referenced will be considered a case of plagiarism. Plagiarism will be treated seriously, and will be dealt with through the University‟s disciplinary procedures. Any code that is directly used to solve the

 

problems of creating, populating, or searching the database must be entirely your own work. Text that is not your own must be identified as such by typesetting it in italicsand

placing it in quotation marks and following it immediately by a reference to the source. For

example, “The database is such an integral part of our day-to-day life that often we are not

aware that we are using one” (Connolly and Begg, 2002).

Part 1: Designing the database

This section of the coursework involves designing a number of tables based on a problem specification. The problem specification document (cw2012part1.docx) can be downloaded from the module website and includes spaces to add images and text for the answers to part 1. Please add your answers into the relevant spaces in the document then submit the document as a part of your electronic coursework submission.

Note:If you want to continue to test this document, you may get errors the second timedue to tables already existing. I suggest that you add statements at the top of the file, in the indicated space, to drop all of your tables that you create, one at a time, in reverse order that you create them, so that your foreign key constraints do not prevent the dropping. Running the file again will then recreate the tables.

Part 2: Creating the database

In part 2 you will need to implement your design from part 1. To achieve this you will be required to edit the file cw2012part2.sql, which is also downloadable from the module website. All instructions and questions have been placed within SQL comments. You will need to add the actual SQL code in the indicated spaces.

Important:

1) During marking the code you submit in this file will be executed, so you need to take care to ensure that it is correct. I recommend that you try executing this script yourself once you have finished each part (see Note 3 below)

2) Please use only the information in a question in your SQL. Do NOT look up information yourself in the database. E.g. If a question asks you to do something for ‘Georgina Smith’ then your SQL should refer to the person with first name ‘Georgina’ and surname ‘Smith’, it should not use information which is not in the question, such

as that you know from Q4 that this person’s ID is 10004 in the supplied data. Note 1:Create the tables using your design for Part 1. Use the data in the tables in Q4 to

populate your own tables. Do not try to use the structure of the tables in Q4 as a database design – they are deliberately NOT the tables that you will get from part 1.

Note 2: You can edit the .sql file using a text editor.Notepad++ works well for this.

Note 3: To execute the file for testing: Login to a Linux machine and move directory so thatyou are in the same directory as the .sql file. Login to MySQL as your user. Finally, run ‘source cw2012part2.sql’ to execute the file. You should see your database tables get created, populated and modified if you have done this correctly. If you add statements at the top of the file, to drop the tables in the reverse order that you created them (i.e. last created is first dropped) then it will clear out your current database before creating the new ones, and you can run multiple tests without having to remove any tables manually.

 

Part 3: Using the database

These questions continue from the database tables and information that you have created in coursework parts 1 and 2. Before you begin, you must download and install the website template that you will build upon during the coursework. This includes all directories, php files, and css files that you will need.

You can either download a zip file containing the files from the assessment page of the course website, or you can use the following instructions to download and install the files directly into the correct location on your home directory. Begin by logging onto one of the school Linux servers, then execute the following commands:

cd ~

wget http://www.cs.nott.ac.uk/~jaa/dbs/coursework2012.tar.gz tar -pxzvf coursework2012.tar.gz

The first line makes sure that you are in your Linux home directory, the second downloads the files. Then the third extracts them, preserving the file permissions. After this, you will be able to find your website atavon.cs.nott.ac.uk/~username/coursework/index.php and you will be able to edit the files inH:/public_html/coursework/

Instructions for part 3:These questions continue from the database tables and informationyou have created in coursework parts 1 and 2.

· Some of these questions are difficult, and have easier versions. Do as many as you can. If you cannot do the complex version then try the easier version. You will get fewer (around half) marks for the easier version than for the complex version, but at least you will get some. If you do only the simpler version then you need to change the heading to say so. E.g. if you do the simpler version of Q14 then please change

the text from: “start_div_region(‘Q14’, ‘#AAC0E9’);” to “start_div_region(‘Q14 Simpler’, ‘#AAC0E9’);”

· All code should be written in the index.php file, which has already been started for you. You can use any PHP and SQL code you see fit to complete each question, however, some marks will be awarded for a clear and concise approach. Remember that you should focus on correctness, clarity and conciseness, in that order. If special HTML formatting should be used, the question will specify this. If no formatting is required then you may format the data as you wish as long as you ensure that the resulting output is clear, so that it is easy to verify that you have given the correct answer to the question. However, in all cases where a table is output, the first row of the table should contain column titles formatted using the “tablehead” class as defined in styles.css. Microsoft Internet Explorer might render the page slightly differently to Firefox or Chrome; do not concern yourself with this.

· Each question should be completed using a single SQL query. You may manipulate the data in the PHP code for purposes of formatting, but you should not use PHP to

 

remove table rows, concatenate tables, order the result rows or execute multiple queries for any of the questions. There is at least one answer for each question (and probably many answers) which requires only a single SQL query.

· You may find it easier to develop and test your queries within the MySQL command line first, before generating the PHP code.

· Important:Because you will only be submitting the index.php file, do not addadditional functions or code to functions.php, or dbconnect.php. If you wish to write functions to make things easier, put them inside your index.php file.

Requirements for Part 3: Write PHP, SQL and HTML to do the following:

Question 11:There is a need to see which students are enrolled on which modules. Outputan HTML table listing the student names and the modules they are enrolled upon, showing the columns: Full Name, Module Code, Module Title. Your table should be sorted alphabetically, by last name then first name.

Clarification/suggestion:I suggest that you retrieve two columns from the database for firstand last name (separately) and join them together in your PHP code. Alternatively, you can retrieve a last name column (which is not delayed, used for sorting) and a combined full name using the CONCAT statement as in Q14 below. I don’t mind which you do. Formatting:In order to make the modules which are associated with each student moreobvious, colour your rows in two colours, so that the colour changes for each person, but stays the same for consecutive rows which represent the same person.

Question 12:A report is needed to show which assessments have been set for the G51modules. Output an HTML table which shows the module name, assessment name and assessment weighting for every assessment for all of the G51 modules, without using a join.

Question 13:A report is needed to cover all coursework and exam submissions which havebeen made. Output an HTML table to show for each submission, the full name and student ID of student who submitted it, the module code that it was submitted for, the assessment name, the weight of the assessment and the mark which has been awarded, if any. Output should be presented in ascending order by student id then module code then assessment name.

Formatting:It is required to highlight missing and low marks. If a mark for a submission ismissing, colour the entire row with a light grey (c0c0c0) background. If a mark is below 30, colour the entire row with a red (ff0000) background. For any mark between 30 and 39, leave the background colour white, but change the text colour to red (ff0000).

Question 14:A further report is required to calculate the average mark for each module foreach student. Assume that the average mark for a module is the sum of the assessment mark * assessment weight, divided by the total assessment weight for that module. Output

 

an HTML table which displays the full name of each student (you should use the MySQL CONCAT command to do this rather than relying upon PHP code), the module code and module title and both the average mark and the rounded average mark (use the MySQL ROUND() function for this). Order this table by the last name, first name and module code, in ascending order in each case. You may assume that any missing mark is the same as a mark of zero. You may also assume that you only have to consider the submissions which have been made, and may ignore any assessments without submissions.

Formatting:Colour rows as for question 11, so that rows change colour when the studentname changes, however you should also change the background for the total mark and rounded total mark columns to be red for any mark of 39 or lower, to highlight these.

Question 15:

Simpler version:It would be useful to see the total course mark for each student, creditweighted across the different modules. Assume that the total mark for each student is the sum of the module mark * the credits for the module, divided by the sum of the credits across all modules with marks. Output an HTML table which displays the full name of each student (you should use the MySQL CONCAT command to do this rather than relying upon PHP code) and both the total mark and the rounded total mark (use the MySQL ROUND() function for this). Order this table by the last name then first name of the student, in ascending order in each case. You may assume that any missing mark is the same as a mark of zero. You may also assume that you only have to consider the submissions which have been made, and may ignore any assessments without submissions.

Formatting:No specific requirements.

Complex version:For full marks on this question, combine the tables for Q13 and thesimpler version of Q14 to make a single combined table for question 14. To do this, set the module code to “” and the module title to “Total” for the total rows.

Formatting:Format this as for Q13.

Question 16:Each module also has a reading list. A report is required to ensure that theappropriate books are on the appropriate reading lists. Produce a table which shows the module code, module title, book code, book title and book edition for every book on every reading list. Order the list by ascending alphabetical order of module code then book code then edition.

Formatting:Format this so that consecutive rows for the same reading list have the samebackground colour, but the colour changes when the module code changes. This will make it easy for the viewer to see which books are on which list.

Question 17:A report is needed to see which students have common interests. Simpler version:Produce a table which shows which students have reservations on the

same books. Your table should have the columns: Student name 1, student name 2, book name, book id.

More complex version:Consider both reservations and past and current loans. Display atable showing which pairs of students have reservations or loan records for the same books. i.e. two students should appear in the list if they both have reservations on it, they both have loan records for it, or one has a reservation and one has a loan record.

 

Question 18:A report is needed in order to see who is actually getting books out which areon the reading list. [See next page…]

Simpler version:In a single table, for each student, show all of the books on the reading listfor each module that they are enrolled on. The columns of the table should be: student id, full student name, module id (for each module the student is enrolled on), book name (for each book on the reading list for that module), book id, and copy id (if the student has/had a copy on loan). If the student currently has the book on loan, or has taken it out on loan in the past (i.e. if a loan record exists for that student for that book), then show the id of the copy which the student has/had on loan in the last column. If there is no loan record for the student for that book then leave the last column blank. You may assume that each student will only ever have (had) one copy of each book if you wish.

Order the table by student last name, first name, module code and book id.

Hint: Think of a common way in SQL to add data into columns only if matching records exist.

Complex version:Also add to the table from the simpler version rows for each student forany books which they have (had) on loan which are not on the reading list for any of their modules. For these records, leave the module code blank.

Hint: Think of the reverse of the simpler version, and combine the tables.

Question 19:An audit is required, listing all of the book copies in the library.

Simple version:Display a table showing a list of all of the copies of the books which are inthe library. This should show the book title, book id and copy id for each book.

More complex version:Display the same table as in the simple version but do not includeany books which are currently out on loan (i.e. where there is a loan entry for the book, with a return date which is set (probably, not NULL – see the ISNULL SQL function, but this may depend upon how you have implemented your database).

Question 20:In order to determine which books are particularly useful, it is useful to seewhich books are on more than one reading list.

Simpler version:Produce a table which will show the book id, book title, book editionnumber and number of readings lists the book is on (as a number). Order the table in descending count of reading lists. E.g. if a book called “DBS”, edition 2, ID “DBS1” was on one reading list, the table row would be: DBS1, DBS, 2, 1

More complex version:Produce the same table as for the simpler version, but only includebooks which are on at least 2 reading lists, and exclude the book ID ‘QA76.9.M91 MAN21’ for the reading list for the ‘G64DBS’ module.