08bExpandFinalProject

Database Management

Mick McQuaid
mcq@utexas.edu

University of Texas at Austin

10 May 2026

Your final project

The pdf file you turn in is worth fifteen points! Get it right!

You need a schema for your final project

  • There are several ways to create a schema
  • The most straightforward and, in the long run, the most tedious, is to write it out by hand in a .sql file
  • I want you to have the experience of doing this, even though you won’t always do it this way in real life
  • The reason is so that you can easily and naturally read schemas
  • You can’t learn this by watching, just as you can’t learn to golf by studying photographs
  • So use the schemas in Files > sqliteExamples as models and write it out

Shortcuts

  • The most obvious shortcut is to use the sqlite .dump command on an existing database to get a schema you can use as a model
  • If you’re using spreadsheet data, you can easily convert that to CSV data and use the sqlite .import command to import each table into a table
  • sqlite3 has rules for importing CSV data that lets you use the table names from the spreadsheet, if it has them
  • sqlite3 has a very specific CSV format it adheres to, found in RFC-4180 (RFC stands for Request For Comments and is an information standard you can search online)
  • The most prevalent shortcut in 2026 is to use a large language model to write the initial draft for you, then edit it by hand to root out mistakes

Using LLMs (large language models)

  • As I will repeat to you in a later lecture, LLMs make mistakes
  • If you go this route, be prepared to either lose points or somehow proofread your work properly (the best way to proofread is to gain experience writing schemas by hand but, if you’re going this route, you’ve already ignored that advice)
  • The nature of LLM mistakes is mainly twofold
    • They have a cutoff date and don’t know about things that appeared on the web after that date (and the date is not always apparent)
    • They represent an amalgam of patterns that appeared in their training data and those amalgams can be like Frankenstein’s monsters—not anything that actually appeared in the wild—instead, a combination of an Oracle example and a SQL Server example, for instance

More about your final project

  • The sqlite .schema command is the most reliable way to get the schema as actually used.
  • While the schema is the most important part, remember the other components listed in the assignment description, such as the ER diagram, a sample of the INSERT INTO statements, the SELECT statements, the Appendix of LLM prompts, and more.
  • For the INSERT INTO statements, it is enough to include an ellipsis (three dots) after about a dozen records have been entered.
  • It should be easy to find stuff in the pdf file. Some programs that create pdfs (e.g., Quarto, the program Mick uses) will automatically build a table of contents for you.
  • You should be able to make sense of the pdf file a year from now when you actually have to create and use a database, so err on the side of putting in too much explanatory material rather than too little.

END

Colophon

This slideshow was produced using quarto

Fonts are Roboto, Roboto Light, and Victor Mono Nerd Font