CS 207, Lab #5

For 13 February 2017

In brief:

  1. Find some summary data for courses and registrations, using SQL on the "timetable" database.
  2. Validate one page for correctness of the HTML and CSS.
  3. Read about ongoing requirements for labs in this course, or else do something interesting on your own site, instead of #1.
    1. Add some functionality to your site, which wasn't there before. Tell me what it is, and how to use it.
    2. Make your site easy to use and navigate. (This is an ongoing requirement!)
    3. Handle error conditions.
  4. fill out the form with your sql, or telling me what you have done. Form below

SQL for statistics

Once you have considerable data in a database, it is nice to get summaries, this can usually be done with suitable joins, aggregate functions (count, min, max, sum, and avg -- the last 2 for numeric types), and GROUP BY.
The "timetable" database (will) have information on courses,  students, and who is enrolled (a many-many relationship.) The schema is:

This is historical data, that is 3-6 years old, representing mostly computer science students.

Do any 2 of the following in SQL, an paste both the query and the result into form, or any one, and a php page to show the result.

  1. Count the number of students with each major.
  2. Count the number of courses taken by each student, the sum of credits this represents, order by number of credits, most credits first. Identify the students only by their "linux" code.
  3. Count the number of students in each course, and the average grade
  4. Overall, count the number of enrolments there are, the number for which there is a grade, and the average of all those grades.
  5. For each prof, number of courses, sum of credits, and earliest time (minimum) that the professor teaches a class. Order by sum of credits, most credits first.
  6. Some other summary you find more interesting.

Validating a page

The standards for HTML and CSS are published by the World Wide Web Consortium, w3. Your pages may be displayed as you wish by a browser, but do they conform to the standard? If they do, there is a better chance that they will appear correctly in all browsers.
(Unfortunately, one of the most widely used browsers does not always comply with the standards, even though everybody benefits by the existence of a standard.)

There are on-line validation services for both html and css at www.w3.org.  For html, you need to specify DOCTYPE, since text/html can be either HTML (version 4.01 is a standard) or XHTML.

Pick a page with both HTML and CSS in it, and put at the top either:  (the second is for HTML 5)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<!DOCTYPE HTML>

Then go to each verifier page, and put in the URL of your page on Osiris.

  1. For HTML, http://validator.w3.org/
  2. for CSS:  http://jigsaw.w3.org/css-validator/

You will probably find some errors, and perhaps many warnings. Try to understand them, and correct what you can. If you get a successful validation, you get a "sticker", a validator image that you can put on your page if you want to. (As HTML 5 is "experimental" no sticker.)
In any case, tell me the page you attempted to validate, in the form below.

Your Linux code
Name of the page you validated:
What you did new this week: Either paste two sql queries, and their results here, or one query, and tell me the page that shows it, or what you did new on your own site.

Read these instructions for future work

Add functionality

By now, you should all be working on your own unique projects, so I can't say, in general, "Do loop-de-do this week." So, using the philosophy of "incremental development," decide what the next thing you can do that will make your site more useful, and next week, the thing after that...  Hopefully you have some goal in mind.
If not, give serious thought to what you can do that will be useful or at least interesting to yourself, for the rest of the term.

Do this one thing, and tell me about it, using the form ABOVE.

Example: To realistically allow a logged in customer to order a music CD, You would need a page that lists all or part of the available ones, and also has access to the identity of the customer. This might entail changes to your database, or setting a cookie, or creating a form to search the product table.

Ease of site navigation

Think about how users can find their way among your numerous pages. If someone takes the trouble to register, will they then be able easily to sign in next time? Is their password secure? What if they forget? Once signed in, can you tell, at the various other pages, that this is so? (I wouldn't want to have to give my password for each new page, for example.)

Handling errors

Test your forms with missing or bad data, as well as good. design your forms and instructions so this is less likely, bu expect the worst.

Your users may generate a variety of errors, such as input too long, or wrong type. Handle these gracefully. Set maxlength in form, check for blank fields, note where integer is expected, for example.
It is not required to show Database Error messages, nor do you always want to "die" on them. For instance, if a missing entry would cause a violation of not null constraint, or an entry would violate uniqueness, the database's refusal to insert  row is probably the correct action, so you should continue to process the page. At most you would want to say "information not updated."

Error messages are wanted by you during development, you don't really want to show them to the public, for two reasons: They undermine confidence in your site, and hackers can use the information in them, such as table name, to attack your database