Lab: Table Design

You've learned a lot of SQL and now you're going to put it all together by designing a whole database. The goal is to create a set of well-constructed tables and relationships that could actually be used by the application you've been using for all your data modeling exercises.

By trying to fit your app's data into a spreadsheet, you learned how to separate all the unique entities that your app cares about. The process of normalizing the data to fit into columns helped you discover all of the attributes for each entity. Then you mapped out the relationships between those entities in a database diagram, which gave you a better idea of how to structure your tables and add key columns.

Requirements

Open your code editor and make a new SQL file. Save it with your name, like last_first.sql. At the top of the file, write the name of the application using SQL comments. This file will contain all the SQL queries to create the tables/columns/constraints/relationships and insert all of your data.

• Write CREATE TABLE statements for all of your entity types.
  • Table and column names should follow SQL conventions.
  • Columns should have appropriate data types.
  • Columns should also have appropriate constraints when needed.
  • Each table needs a primary key.
• Make sure all the entity relationships are represented.
  • Enforce relationships with foreign key columns.
  • Each foreign key should be labeled with a comment to show the relationship type and explain how the entities are related in the application.
  • Don't forget to use constraints or join tables for the proper relationship type.
• Then write INSERT INTO statements to fill your tables with some data.
  • Each table should have enough records to accurately represent the possible data.
  • Your data should conform to all your constraints.
  • Each relationship should be represented with data.
• And finally, you should write a comment before each query explaining everything that it's doing and why it's doing it. Be thorough.

Grading

To grade your lab, I'm going to create a new database and then run your file all at once. There shouldn't be a single error. If you've designed your table well, I'll be able to tell at a glance what data it contains and how entities relate to each other.

You should do the same thing to make sure it all works. For example, say someone is working the school portal. I'll create a new database called portal and then run their queries with the following command:

$ psql portal -f last_first.sql

Things to Think About

• Spend more time thinking than coding. Design is a solution to a problem, so spend time thinking about the problem.
• There are no minimum requirements since each database will be different. I will grade based on effort, as usual, and on how accurately you represented the application.
• Like most programming we've done so far, order matters in SQL. You can't insert data into a table that doesn't exist yet. This is especially true for related entities.
• Do not miss potential relationships between your tables. And don't add any that don't need to exist.
• When you make a mistake, don't forget about the DROP or DELETE statements. It might take a few attempts to get everything right.

Good luck!