# Provided by the Open.Michigan initiative at the University of Michigan
# Licensed under a Creative Commons Attribution 3.0 License:
# http://creativecommons.org/licenses/by/3.0/.
# Copyright 2009, Chuck Severance.

SI502 - Assignment 10 - Data Modelling

Due Date: Friday April 10, at 11:55PM

Answer the following questions and turn your edited document document into CTools under Assignments.

Keep the file a Text file with a .txt extension - Use your programmer editor (i.e. JEdit to edit the file).

Bring this to Discussion in paper or laptop form - you can talk about the questions in Discussion and improve your answers - and then turn the assignment in after Discussion.

1) Download the SQLLite browser from 

http://sourceforge.net/projects/sqlitebrowser/

There is a mac version (look for ".dmg" in the file name) and a Windows
version (look for "win" in the file name) since they are not well marked.

2) Go through the example in class - make the entire data model with at least two artists, two albums, two genre's and six tracks.  DO not use the ones I used in class - pick something else.  Get your model completed and the data entered - then do the large join across the four tables to produce the "iTunes-like" output - and take a screen shot of that screen and hand it in.

3) You are handed a spreadsheet with data and the following column labels:  Student, Course, Semester, Grade, and Comments.  Build a full data model including the table names, primary keys, logical keys, and foreign keys.  Describe the relationships between the tables (i.e. which foreign keys map to which tables and primary keys).  Identify every primary key, logical key, and foreign key.   Look at slide 43 for an example.  You can draw a picture and upload an image or draw on paper and show your GSI.

4) Extra Pride Question - this is optional - and no it is not extra credit.  It just shows extra coolness.  Write a Python application to open your database from (2) above - do the four table join and print out the rows which you retrieve from the database.  See slides 58-61 in the the lecture.  Also I uploaded a ZIP file of sample Python database code - but the samples are easy to type in from the slides.  (Remember - don't paste from PDF into text - it can go bad and be hard to figure out.)