Homework 3

In this assignment you will create a database and get it into position so that you can put it into PostgreSQL. You will not actually put it into PostgreSQL; just get it ready. Then you will document your database in an html page.

Database requirements

The database you find/create must meet the following requirements:
  1. The data must be publicly available or of your own creation. If the data is of your own creation, then the tables themselves must have some meaningful referent. For instance, both the univ and sakila databases are synthetic data. However, the design of the tables has a structure that makes sense as does the data contained in the databases.
  2. The data must not contain any private information
  3. The data must be contained in a least 3 separate tables. So for instance, the hurricane data would not pass this requirement as it has only two tables.
  4. The tables must be related to each other. For instance the rocket database would pass this and the previous requirements. Indeed all of the databases we have used this semester would pass this requirement.
  5. The relationship between the tables must be either one-to-many or many-to-many. For instance, in the rocket database, the launch and vehicle tables are one-to-many. In the launch table, there is a vehicle column that selects a unique item in the vehicle table. But each item in the vehicle table may be used in many launches. One-to-one relationships between tables are not uncommon, but will not count as separate tables for this assignment. Many-to-many relations are described next.
  6. There must be at least one many-to-many relationship among the information in the tables. The sakila database has several many to many relations. For example, movies can have more than one actor (most (all?) do although there are Broadway plays with only one actor e.g. "The Search for Signs of Intelligent Life in the Universe"). Likewise, actors can be in more than one movie. Hence, the actors/movies relationship is many to many. The hurricane data would fail here because the while a given storm may have more that one observation, each observation is related to only one storm. The rocket database also fails this requirement.
  7. The tables must have a reasonable amount of data. Reasonable means probably more than 10 rows in the smallest table and at least one table should be significantly larger than that.
  8. The dataset should be one in which you are interested.
  9. You dataset must be distinct from any dataset currently in postgres (univ, sakila, rocket, hurricane, flight, volleyball).
  10. Your dataset cannot simply be a clone of some existing dataset on the web. For example, I did not create the the univ or sakila sets, I just copied someone's SQL statements. Hence, these would not be acceptable. On the other hand, the rocket dataset would be OK. While I copied the actual data of the rocket dataset from the web, all I actually got was a set of flat files. I had to design the tables and relations myself and then write import methods to get the data into PostgreSQL.

Make a web page for your data

Start by putting your data into files in a machine readable form on your personal class website (ie, your public_html directory). By "machine readable" I mean a format that is easily parsed. For example: CSV, JSON, XML and even fixed width columns would be OK.

Write a web page that describes the tables you created and the topic area of yor database. The description should have all of the following:

  1. Clickable links in your description page that take the reader to each data file. Your data may not render nicely in a browser; I do not care, but you should have the link.
  2. Include a drawing showing the relationships between the tables. It is OK (even expected) to do this drawing by hand, then take a picture of it and include the picture of your drawing into the page as an image.
  3. Define the meaning and data type of each column in each table. Column descriptions need not be long. For example "The year in which the move was made, as in integer" would be enough for a column named year. A bulleted list would be appropriate for presenting this information and would a multi-column table. (There are many other appropriate styles.)
  4. A description of how the tables inter-connect. That interconnection should also be shown in the picture. A likely thing to do here is have the text describe the picture. Here provide some text that explains the interconnections.
  5. Finally, your page should indicate the source of your data.

The web page you write should be written directly in HTML (probably using VSC, but you can use other editors (emacs, nano, atom, vi, ...)). Do NOT use, for example, MS Word and have it output html. Do not use any other HTML authoring tool (e.g. WordPress.) (Yes, I know that almost no one actually writes web pages this way.)

Grading

I will NOT judge this homework on content of your tables. That said the content must have some reasonable size as described above. Tiny databases will get tiny grades. I will be grading on the structure of the database and whether it meets the requirements.

Further the grade will be dependent on your web-page written report. I will grade that page principally on its containing the information I asked for. I will also consider aesthetics ... i.e. does the page look nice; is it well organized; and does it take advantage of being a web page. (The web page for this assignment rates poorly on any and all aesthetic grounds.)

As usual, the work must be your own. That said, talk to yor classmates; judge the aesthetics of each others work and discuss how those aesthetics might be improved.

A part of your grade will be on how many different html tags you use -- meaningfully -- in your page. This page sets a really low bar at 10 (or 13 if I count the head tag and stuff within the head tag). With only modest cleverness you should be able to exceed 20.

If you obtained information from the web, it must be appropriately referenced in your web page.

What to hand in

Send an email to gtowell@brynmawr.edu with a link to your web page. In addition, your email should have the count of all of the html tags you used in your pages as well as a listing of those tags. For example here is a listing of the 10 tags I used in this document (including the ul tag that is used only to make this listing)