Part 3a: Describing Your Database (optional)

This part has three sections. It mirrors some of the tasks from Parts 1 and 2, but also has some important modifications.

Section 1: Overview of Your Dataset (10 points)

Provide a description of your dataset, about two pages in length. We will not be strict about the submission length, but half a page is definitely too short, and five pages definitely too long.

- What is the dataset? Describe the data you have obtained (including the parts you may have generated), answering the following questions:

o What is the source of the data? Provide a URL or other reference. Please also clearly state the license information.

o What have the data been used for in the past? Has anyone else done anything useful or fun with the dataset? Provide citations / URLs where appropriate.

o Provide details of how you generated any simulated part of your data, including scripts, ChatGPT prompts, etc.

- What do you plan to do with the dataset? Explain the key questions you would like to ask using SQL operations on the dataset.

Section 2: Description of Your Tables (10 points)

For each of your tables, provide the following information:

-      The name of the table

-      The attributes it contains, including brief explanations of any attributes that are particularly important or non- obvious

-      Any primary keys / foreign keys, including brief explanations of how the foreign keys link the tables to each other

-      The dimensions (rows and columns)

Section 3: Internal Schema and Normalization (10 points)

Note that these figures must not be hand drawn; they must be generated using tools such as the MySQL reverse engineering tool, diagrams.net, PowerPoint, etc.

-      Internal schema, including all tables, attributes, datatypes, relationships including cardinality information

-      Dependency diagrams for each table, with primary keys identified, and arrows showing determinant / dependent relationships

-      Explanation of why these tables are in 3NF.

To submit: A single PDF file, text in a legible font (e.g., 12 point Times New Roman), with reasonable margins (e.g., Word or LaTeX default, or narrow)

Part 3b: Using the Database (4 sections)

At long last, it is time to submit the database and any supporting data (if necessary). We will attempt to build your

database, run your supplied queries, and test your stored procedures. Our intention is to test these in MySQL; if for some reason you used a different DBMS or made other nonstandard alterations, or have a large file as part of your dataset,

please let us know before the deadline.

Section 1: Everything needed to construct the database (10 points)

The first part of your submission is an .sql file that contains everything needed to construct your database. This includes all the DDL statements (which will probably be CREATE TABLEs) as well as the DMLs that populate those tables with data using INSERT INTO and/or LOAD DATA statements.

Requirements:

-      The SQL file must run from start to finish, so for example if we load it into MySQL Workbench and hit Ctrl-Shift- Enter, your entire database should be created.

-      We should also be able to successfully re-run the script, so it should contain the appropriate DROP TABLE IF EXISTS statements and anything else needed to “clean” the database before it is re-run.

-      Every statement (CREATE TABLE, etc.) must be commented.

Section 2: Stored procedures (5 points)

You must submit a second .sql file that contains two stored procedures and CALL() statements to invoke them. These must satisfy the following requirements:

-      They must contain at least two separate DML statements that update, insert, and / or delete data in a related   way, for example adding a row to a table and updating every other row in the table, or deleting a row if it is no longer needed.

-      They should use IN, OUT, or INOUT parameters

-      They should have basic transaction control to ensure that the entire sequence of statements either completes successfully or is rolled back.

-      The file should also contain at least one CALL() statement for each of the stored procedures. Since the

procedures need to have at least one parameter, you will need to declare / use variables in this scope as well.

-      Each stored procedure must be commented.

Rubric (Assignment 3a):