Week 6.2 - LLM-generated SQL evaluation

Please use Canvas to return the assignments: https://ucsb.instructure.com/courses/32934/assignments/474659

You may have already discovered that the new AI/LLM services can readily generate SQL given a prompt. Your job is to evaluate the SQL returned by those services.

For this assignment, you will need to use and modify the schema that was used to create our bird database. The schema can be found in the class GitHub repository at database/schema-build-script.sql. Prepare a copy of this file and make one modification: Remove the UNIQUE declaration from column Personnel.Name, i.e., for this assignment names are not unique.

Now:

0. Did you remember to modify the schema as directed above?

1. Choose at least three LLMs (ChatGPT, Claude, Gemini, etc.) to evaluate.

2. For each LLM, supply both the modified schema and the text of homework assignment Who’s the culprit?.

3. Evaluate the SQL that is returned. Specifically:

   • Is the SQL correct against the given schema? In other words, will it return the expected answer?
   • Does the SQL represent good understanding of the database? An example of poor understanding would be use of COUNT(Nest_ID) where COUNT(*) would suffice, i.e., not recognizing that Nest_ID is a primary key and hence cannot be NULL.

Be sure to include the LLM outputs with your evaluation.

Credit: 40 points