Spring Semester 2008

Here's the problem: old final exam in A114/I111.

Now I first want to say that that's different from actually creating databases.

Here's how we work out the table of judges:

mysql> create table judges (username char(8) primary key, lastName char(32), firstName char(32));
Query OK, 0 rows affected (0.01 sec)

mysql> describe judges;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| username  | char(8)  | NO   | PRI | NULL    |       | 
| lastName  | char(32) | YES  |     | NULL    |       | 
| firstName | char(32) | YES  |     | NULL    |       | 
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into judges values ('lbird', 'Bird', 'Larry');
Query OK, 1 row affected (0.00 sec)

mysql> insert into judges values ('mjordan', 'Jordan', 'Michael'), ('tkukoc', 'Kukoc', 'Toni');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from judges;
+----------+----------+-----------+
| username | lastName | firstName |
+----------+----------+-----------+
| lbird    | Bird     | Larry     | 
| mjordan  | Jordan   | Michael   | 
| tkukoc   | Kukoc    | Toni      | 
+----------+----------+-----------+
3 rows in set (0.00 sec)

mysql> 

The design preceded this, and it guides this execution.

So what is it based on, then, and what does it look like?

Relational Database Management Systems: a tutorial. Can we provide that?

• Introduction to Database Management
• The Relational Model (Part I)
• The Relational Model (Part II)
• Database Design: Normalization
• Database Design: Design Methodology

Another example, a related problem: exampleTwo.

We can give more examples.

What does it mean for our problem?

It means that we might try to start with a spreadsheet type of approach:

Larry Bird     lbird   Eric Gordon     egordon    9 
                       D.J. White      djwhite   10
                       Deandre Thomas  dthomas    7
                       A.J. Ratliff    ajratlif   8
Michael Jordan mjordan Eric Gordon     egordon   10
                       Mike White      mwhite     8
                       Armon Bassett   abassett   7
Toni Kukoc     tkukoc  Jamarcus Ellis  jmellis   10
                       Lance Stemler   lstemler   5

In other words it's not normalized.

Turning it into NF1 we get:

Larry Bird     lbird   Eric Gordon     egordon    9 
Larry Bird     lbird   D.J. White      djwhite   10
Larry Bird     lbird   Deandre Thomas  dthomas    7
Larry Bird     lbird   A.J. Ratliff    ajratlif   8
Michael Jordan mjordan Eric Gordon     egordon   10
Michael Jordan mjordan Mike White      mwhite     8
Michael Jordan mjordan Armon Bassett   abassett   7
Toni Kukoc     tkukoc  Jamarcus Ellis  jmellis   10
Toni Kukoc     tkukoc  Lance Stemler   lstemler   5

How do we add a new judge: Dennis Rodman, drodman?

How do we add a new player: Joe Crawford, jcrawfor?

We'd have to do this:

                       Joe Crawford    jcrawfor
Larry Bird     lbird   Eric Gordon     egordon    9 
Larry Bird     lbird   D.J. White      djwhite   10
Larry Bird     lbird   Deandre Thomas  dthomas    7
Larry Bird     lbird   A.J. Ratliff    ajratlif   8
Michael Jordan mjordan Eric Gordon     egordon   10
Michael Jordan mjordan Mike White      mwhite     8
Michael Jordan mjordan Armon Bassett   abassett   7
Toni Kukoc     tkukoc  Jamarcus Ellis  jmellis   10
Toni Kukoc     tkukoc  Lance Stemler   lstemler   5
Dennis Rodman  drodman                              

What if Larry Bird changes his name to Larry Börd-Fjord to honor his Scandinavian grandmother?

So this is a combination of reasons (of NF2, NF3 provenance) which lead us to split this into three tables:

A table of judges:

Larry Bird     lbird   
Michael Jordan mjordan 
Toni Kukoc     tkukoc  
Dennis Rodman  drodman                              

Joe Crawford    jcrawfor
Eric Gordon     egordon   
D.J. White      djwhite   
Deandre Thomas  dthomas   
A.J. Ratliff    ajratlif  
Eric Gordon     egordon   
Mike White      mwhite    
Armon Bassett   abassett  
Jamarcus Ellis  jmellis   
Lance Stemler   lstemler  

The third table, for the ratings, models a relationship between the entities:

lbird   egordon    9 
lbird   djwhite   10
lbird   dthomas    7
lbird   ajratlif   8
mjordan egordon   10
mjordan mwhite     8
mjordan abassett   7
tkukoc  jmellis   10
tkukoc  lstemler   5

This set up does not suffer from what's called update anomalies.

The way everything was together before:

• some non-key column (Larry Bird) was dependent on just a portion (lbird) of the primary key
• some determinants (lbird, for example) were not candidate keys (just a part thereof, at most)

So this is the same reason from two different perspectives.

The result is in NF3.

It is also in NF4 but we won't need to discuss here why.

That's the design.

The rest is execution, as I said:

mysql> create table players (username char(8) primary key, lastName char(32), firstName char(32));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into players values ('egordon', 'Gordon', 'Eric'), ('djwhite', 'White', 'D.J.'), ('jcrawfor', 'Crawford', 'Joe');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> describe players;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| username  | char(8)  | NO   | PRI | NULL    |       | 
| lastName  | char(32) | YES  |     | NULL    |       | 
| firstName | char(32) | YES  |     | NULL    |       | 
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from players;
+----------+----------+-----------+
| username | lastName | firstName |
+----------+----------+-----------+
| egordon  | Gordon   | Eric      | 
| djwhite  | White    | D.J.      | 
| jcrawfor | Crawford | Joe       | 
+----------+----------+-----------+
3 rows in set (0.00 sec)

mysql>