Lab 2 – Designing and Implementing a JavaScript Calculator



Lab 6 Postlab – Database Design, MySQL Introduction & SQL Practice

In this week’s post-lab activity we’ll practice the database design process discussed and practiced in lecture this week. We will then build the databases we design using the MySQL database management system (DBMS). MySQL is one of the most popular open source database systems available, and is the one we’ll be using shortly to store data for web sites. Those who have used Access or another DBMS will find the features of MySQL very familiar.

Finally, we’ll practice some basic SQL SELECT commands, building on this week’s lab activity.

Specification

You are to construct a database to store information about your MP3 collection.

Design

Before creating anything in MySQL (or any DBMS) it is necessary to first plan out the details of the data you wish to store. This includes:

1. Deciding what tables you’ll need. You should have a table for every distinct entity in your system (e.g. “Customers” and “Products” from the class example). Also decide on the relationships between entities, and whether they’re 1-to-1, 1-to-many or many-to-many. (Remember that many-to-many relationships will require tables of their own.) Keep in mind that each table should ideally contain information about only one entity. Obviously, a table to store song information is the first one you’d think of for this database. Two or three others should also come to mind, though maybe not until after the next step.

2. Deciding what attributes each table should have. These are the specific values that you’ll store, like “custnum”, “name” and “phone” from the class example. If you define a table and realize that there will be a lot of repeated information, you should consider creating a new, related table, because the odds are that you really have a second distinct entity represented in your first table. (This process of breaking down tables that are too big is called normalization.) For instance, suppose your Songs table includes the attributes “album_name”, “release year”, and “genre”. This might suggest to you a separate Albums table that would be linked to your Songs table. (NOTE: For this specific project, you might select attributes that really don’t lend themselves to the creation of any more than one table. I’d like you to think of enough that would logically result in at least two or three tables.)

3. Deciding which of your attributes in each table will comprise the primary key. Remember, these are the attributes that are guaranteed to be unique in every record of the table; that is, to uniquely identify a particular record. Very often it is useful to create an attribute specifically for this purpose (e.g., “song_id”). In this case, it is usually best to let the DBMS assign the specific ids using the auto increment feature. (You set this in the “Extra” field in phpMyAdmin when you’re creating the table.)

4. Deciding which attributes will be used to link tables together if necessary. In the preceding example, your Songs table would include an attribute like “album_id” or “album_name” – usually the primary key of the table you’re linking to. (This is called the foreign key.) Remember that a simple rule of thumb when you have a one-to-many relationship is to put a copy of the primary key of the entity on the “1-side” over on the “many-side”.

5. Finally, decide what types all of the attributes will be. MySQL provides many choices for types (as you can see in the phpMyAdmin pull-down menus). To keep things simple, I recommend the following – use VARCHAR(length) for string information (with some maximum length specified), INT for numbers, DATE for dates and TEXT for larger passages of text. As you become more familiar with MySQL you can experiment with other types.

When you’re finished sketching your design using an E-R Diagram like we did in class, show it to me for comment/analysis.

Implementation

The hard part is done. To build the actual database, you just have to know how to use the DBMS that is available (MySQL in this case). If you have a good design, you literally work straight from the design.

Use your browser to open the URL



and log in as you did in lab.

Testing

Once you’ve created your tables, use the “Insert” link to add 6-10 rows to each table. (Basically, add enough to allow you to try some interesting SELECT queries that actually return results.) Then try some basic SELECT queries. (Experiment with different features of SQL that you learned in this week’s lab – including JOINs, IN, LIKE, AND, OR, aggregate functions, etc.) Some queries might actually suggest re-designs of the database itself.

To Hand In

Write SQL queries for the following tasks and submit them to me in a Word document:

1. List the artists and names of all songs in the rock genre, ordered by last name of artist.

2. Find the title of the longest song.

3. List all artists in decreasing order of number of songs recorded by them that you own (along with the number of songs owned for each).

4. List the artist and song title of all songs with the word “love” in either the title or the lyrics.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download