COMP 3421 PDA #2

(40 Points)

Due 10AM, January 19, 2016

Step 2 of Your PDA (Personal Database Application)

Note1: this assignment is a slight modification of material developed by the Stanford Database Group

Note2: see Recording Your Session in the on-line Getting Started With mySQL document for a guide to preparing output to hand in with your assignment. It will be useful for this and subsequent PDA parts.

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

(a)

(10 pts.) Consider the E/R schema you designed for your PDA in PDA Assignment #1. Please attach a copy of the schema. Use the method described in the text and in class for translating an ER schema to relations to produce a set of relations for your database design. Please specify your relational schema and underline key attributes.

(b)

(20 pts.) Write an 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 balance 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;

(d)

(10 pts.) Execute about five 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.

Don't forget to save a copy of your PDA for reference as you do Step 3 of the PDA.