This page lists a variety of resources related to the course, including answers to issues frequently encountered by students, diagramming tools, and learning resources. Please contact the instructor if you have suggestions for additional resources to include.

ER diagrams

Knowing how irritating it is to fix mistakes when drawing a diagram by hand, I encourage you to use a tool to create entity-relationship (ER) diagrams.

Some of the tools that I’ve used in the past are as follows:

  • draw.io: draw.io’s mission is to “provide free, high quality diagramming software for everyone” based on open source technology. Most of the ER diagrams on this website were created using this web application.
  • Microsoft Visio: If available (e.g., thanks to a site license), then definitely worth checking out. Visio popularized the style of diagrams used in the second edition of Fundamentals of Database Management Systems.
  • yEd: I’ve used this tool for a number of projects in the past and have been pleased with its ability to provide good graph layouts. An online version is also available: https://www.yworks.com/yed-live/.

You are also welcome to search for other diagramming tools. If you do use a tool, be sure to mention it in your documentation statement (and I’d also be interested in what you thought of it – the good, the bad, and the ugly).

Database Design

Deciding the appropriate size for various data types (e.g., CHARACTER VARYING) can be a challenge for software developers. The following table provides some recommendations based on government standards, ISO recommendations, and real-world data (courtesy of Geek Slop):

Attribute Length (US) Length (World)
Given name (first) 35 50
Surname (last) 35 50
Street address 40 100
City 25 60
State / Province 15 50
Postal Code 9 9
Country 55 55
Email 254 254
Phone number 15 15
Phone extension 11 11
Company name 100 100

Of course, always use your best judgment regarding what is appropriate. For example, an international phone number may have 15 digits, but phone numbers in the United States have only 11 digits including the country code. Some database management systems (DBMSs), such as PostgreSQL, also do not require the maximum length of variable-length character types to be specified.

SQL

Without a doubt, practice is critical to mastering the Structured Query Language (SQL). While you can certainly create your own database and execute queries against it, the following online resources offer a more structured approach to learning:

All these sites offer interactive exercises to practice writing SQL statements within a web browser. I recommend working through one or even two of these tutorials to cement what we cover in class.

For anyone curious about SQL syntax, PostgreSQL’s documentation of SQL Commands is the most comprehensive that I’ve found. Granted, the amount of detail can be overwhelming at times, but it is extraordinarily thorough, including examples, and explicitly indicates deviations from the SQL standard and other popular relational database management systems. I do not expect this resource to be the first that you consult, but it is excellent when you have specific questions.

Please contact me if you use any of these resources. I’d love to know what you think of them, especially if they’re a useful supplement as you learn SQL.

Web Development

Apache

The software installation instructions configure a LAMP (Linux, Apache, MySQL, and PHP) stack that you can use for web development, including server-side scripting and database connectivity. As part of the configuration, Apache has been configured with per-user web directories.

Content stored in the public_html directory in your home directory (you’ll see this directory if you open the terminal and type ls to list directory contents) will be served by Apache. Open Firefox and enter http://localhost/~student to have Apache display the directory contents, which it does by default when there isn’t an index page (traditionally index.html or index.php). Note: If your username isn’t “student,” then you’ll need to replace “student” in the URL with your username.

Create a new file in ~/public_html (the tilde is shorthand for your home directory) named test.html. The GNOME text editor, gedit, can be used to edit files and supports syntax highlighting as well as a host of other features. Enter the following text in the test.html file and save it:

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
    <title>Test HTML Page</title>
  </head>

  <body>
    Hello, World!
  </body>
</html>

Enter http://localhost/~student/test.html in the web browser’s address bar (be sure to do this in the virtual machine (VM), not the host machine!) to verify that Apache is serving files correctly, and don’t forget to replace “student” with your username. You should see “Hello, World!” – contact the instructor if you don’t see this text.

If Apache is not serving files correctly, then you will not be able to complete examples in lessons, the web development programming exercise (PEX), and the project’s web application.