Recap of Week 8

Working on Projects! For this entire class period, we ran through a sample project. This project was to help you walk through the steps on your final project. Regardless of whether you use Oracle or the alternative MySQL, remember that the purpose of your project is to:

  • Gather needed requirements for designing a database system
  • Go through the process of database normalization
  • Write SQL code
  • Create an intuitive form for the end user.

Our sample project was to create a database solution for a Video collection. As a collective group, we determined that we needed to track: 

Movie Title,  Actors,  Rating,  Genre, Location, Release Date, Length of Movie, Quality of Video, Number of media (how many CD's), studio, format (compression)

After working through normalization (aka making more tables), we came up with the following. 

mysql> show tables;
+------------------+
| Tables_in_videos |
+------------------+
| actor            |
| actor_lookup     |
| genre            |
| genre_lookup     |
| location         |
| movie            |
| studio           |
+------------------+
7 rows in set (0.01 sec)

mysql> describe actor;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| act_num     | smallint(3) |      | PRI | NULL    | auto_increment |
| actor_fname | varchar(15) |      |     |         |                |
| actor_lname | varchar(25) |      |     |         |                |
+-------------+-------------+------+-----+---------+----------------+

mysql> describe actor_lookup;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| al_id    | mediumint(5) |      | PRI | NULL    | auto_increment |
| act_num  | smallint(6)  |      |     | 0       |                |
| movie_id | smallint(6)  |      |     | 0       |                |
+----------+--------------+------+-----+---------+----------------+

mysql> describe genre_lookup;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| gl_id    | mediumint(9) |      | PRI | NULL    | auto_increment |
| g_num    | tinyint(2)   |      |     | 0       |                |
| movie_id | smallint(6)  |      |     | 0       |                |
+----------+--------------+------+-----+---------+----------------+

mysql> describe genre;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| g_num | tinyint(2)  |      | PRI | 0       |       |
| genre | varchar(15) |      |     | 0       |       |
+-------+-------------+------+-----+---------+-------+

mysql> describe location;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| loc_id   | tinyint(4)  |      | PRI | NULL    | auto_increment |
| location | varchar(30) |      |     |         |                |
+----------+-------------+------+-----+---------+----------------+

mysql> describe movie;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| movie_id  | smallint(5) |      | PRI | NULL    | auto_increment |
| title     | varchar(50) |      |     |         |                |
| date      | smallint(4) |      |     | 0       |                |
| length    | smallint(4) |      |     | 0       |                |
| quality   | tinyint(1)  |      |     | 0       |                |
| rating    | varchar(4)  |      |     |         |                |
| loc_id    | tinyint(4)  |      |     | 0       |                |
| num_media | tinyint(4)  |      |     | 0       |                |
| studio_id | smallint(6) |      |     | 0       |                |
| format    | varchar(10) |      |     |         |                |
+-----------+-------------+------+-----+---------+----------------+

mysql> describe studio;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| stud_id | tinyint(4)  |      | PRI | NULL    | auto_increment |
| studio  | varchar(25) |      |     |         |                |
+---------+-------------+------+-----+---------+----------------+

We created our database, but we forgot to setup the security, so let's do that. 

mysql > grant insert, select on videos.* to videoguy@localhost IDENTIFIED by 'videopass';

Here is some meaning to the above statement:

  • The insert and select permissions are being added to
  • videos.*, which is to say the videos database including all tables (denoted by *)
  • We are assigning these permissions specifically to the user videoguy
  • who has to use the password videopass
Now that we have the tables, how do we get info in there? We worked on a web form that would allow input for one movie at a time. We also want to have a web page that will allow us to search and sort the list of videos.

There is still some tweaking to do on the form, but here is what it looks like so far
If you want to see the code used to create it, check out the