Assignment 3: Step 2 of Your PDA (Personal Database Application)

Note 1: This assignment is a slight modification of material developed by the Stanford Database Group.

Note 2: Make sure you back up your data and files for executing commands. It is your computer, and you are responsible for your data. I suggest backing up frequently to an external device.

1. (10 points) Consider the ER schema you designed for your PDA in PDA Assignment 1. Please include a copy of the schema. Use the method described in class for translating an ER schema to the relational model to produce a set of relations for your database design. Please specify your relational schema

and underline or boldface attributes that make up the key for each relation.

1. (20 points) Write a SQL database schema for your PDA, using the CREATE TABLE commands described in the handout Getting Started With MySQL. Pick suitable datatypes for each attribute. Hand in a printout of the commands you use to create your database schema. (It is a good idea to keep this file for the rest of the course.) Show the response of MySQL to a request to describe each of your relation schemas. For example, to see the schema for relation Foo type describe Foo;.

1. (10 points) Execute three INSERT commands to insert tuples into one of your relations. Show the response of MySQL and the relation that results when you issue a SELECT * command. Again, the information on how to do this step is in Getting Started With MySQL.

What to upload (as a single .pdf file):

• Your ER diagram

• Your relational model with keys boldfaced or underlined. This can be in the "concise" format, that is, just relation names, then comma-separated attributes with keys boldfaced or underlined.

• The SQL commands you used to create the tables (relations).
• The results of "describe relationName" for each relation in your database.

• The response of inserting a tuple and the SELECT * on that relation for three different relations.

To capture this information you can cut and paste from your window or grab screenshots. If you are using MacOS or Linux you can use "script" (which creates a file called typescript). Basically, however you want to capture it is fine—just end up with a single .pdf file that you upload.

Don't forget to save a copy for subsequent assignments.