my learning experiences in building a better web page

Further begining MySQL

If you remember, our database is Normalized. That is, each table has specific information that makes the data non repetitive and preserves its integrity. The Database has several tables. Here is a list.

Because there is a many to many relationship between books and authors, there is a seperate table, Bookswritten, that manages that relationship. The volumes table manages who has what book, and when it is due. The client table manages the clients, who have a one to many relationship with the volumes. One volume can be checked out to only one client (We have multiple copies. Volumes 232 and 237 are different copies of the same book. The Volumes table and the BooksWritten manage the many to many relationships in the database. bookwritten deals with many authors to many books, and the volumes table manages the many books to many clients relationship. It is also assumed that each book is shelved in one place, but that one place can have many books there. So the topic-books relationship is one to many.

Some tables have lots of data, like the books table. Because the table is so large, we won't look at the whole thing. Just pieces. But this is how it is organized.

The field gives the name of each column. The type gives the kind of data stored, and how much space is given the field. There aren't that many topics, so 999 topics is ample.

Each field name that has a relation has to have the same type in each table with the relation. Book IDs are integers, date fields have date information and so on.

mysql> show columns in books;
+--------------+---------------------+------+-----+-------------------+----------------+
| Field        | Type                | Null | Key | Default           | Extra          |
+--------------+---------------------+------+-----+-------------------+----------------+
| book_id      | smallint(7)         | NO   | PRI | NULL              | auto_increment | 
| topic_id     | tinyint(3) unsigned | NO   |     | NULL              |                | 
| date_aquired | timestamp           | YES  |     | CURRENT_TIMESTAMP |                | 
| description  | tinytext            | NO   |     | NULL              |                | 
| title        | varchar(37)         | NO   |     | NULL              |                | 
+--------------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

We can also look at the patron table.

mysql> show columns in patron;
+-----------------+------------+------+-----+---------+----------------+
| Field           | Type       | Null | Key | Default | Extra          |
+-----------------+------------+------+-----+---------+----------------+
| patron_id       | int(8)     | NO   | PRI | NULL    | auto_increment |
| efname          | char(20)   | YES  |     | NULL    |                |
| mname           | char(20)   | YES  |     | NULL    |                |
| elname          | char(35)   | NO   |     | NULL    |                |
| addy_one        | char(40)   | NO   |     | NULL    |                |
| addy_two        | char(20)   | YES  |     | NULL    |                |
| zip             | char(10)   | NO   |     | NULL    |                |
| dob             | date       | NO   |     | NULL    |                |
| overduerestrict | tinyint(1) | YES  |     | 0       |                |
| fines_owed      | float(4,2) | YES  |     | NULL    |                |
+-----------------+------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

Both tables have similar orginizations. There is an ID field, which is labled a primary key. Keys are a way of uniquly identifiying a row in a table. Primary keys must be unique. In MySQL you can set fields to auto increment, which is a handy way of managing this.

Each field has a type. This explains to the database what kind of data we expect to find there. As far as the computer is concerned, it is all numbers, but for us we need to know what is charachter data and numberic and date. Some of the data is stuff that is required. Some of the data is optional. We can set the table requirements before we enter the first data. We can check out our patron list like so

mysql> select patron_id as patron, concat(efname,'  ',elname) as name, 
    ->      concat (addy_one,'     ',zip) as address,
    ->>     dob from patron ;
+--------+------------------------+-----------------------------+------------+
| patron | name                   | address                     | dob        |
+--------+------------------------+-----------------------------+------------+
|      1 | Sheldon  Cooper        | 2754 SE Woodstock     97202 | 1970-08-12 |
|      2 | Leonard  Hoffstedder   | 2754 SE Woodstock     97202 | 1974-07-15 |
|      3 | Penny  LaVache         | 2754 SE Woodstock     97202 | 1975-02-14 |
|      4 | Rajiv  Kulliphipothong | 6212 SE Hawthorn     97215  | 1977-01-31 |
|      5 | Howard  Melman         | 2104 SW Sheridan     97211  | 1976-04-15 |
|      6 | Sam  Vimes             | 3434 NE Mason     97211     | 1963-11-22 |
|      7 | Carrot  Ironfounderson | 2912 N Longview     97217   | 1985-04-12 |
+--------+------------------------+-----------------------------+------------+
7 rows in set (0.00 sec)

I don't yet have an employee table. We can use that to explain the process of creating tables next.