Recap of Week 3

Fun with Oracle! Ok, after spending last week trying out general SQL commands, we spent this week..

  • Running Oracle (Click Here for steps to start Oracle)
  • Running Commands in Oracle
  • Successfully run those commands in MySQL
  • Eating Tortilla Chips
  • Viewing examples of how to tie web frontends to database backends for both input forms and retrieving information
  • Database concepts and design
Remember that Database design never begins by just starting to code. You need to spend time with the owners and users of the system to define requirements (owners pay for it, users actually use it). You may also have to talk to the DBA that is to maintain it. Good ways to collect information are to look at existing input forms (both electronic and paper) that are currently in use. Ask what is important on those forms and is it missing anything(due to technical barriers, lack of knowledge..).

Drawing out a Data Dictionary is important as it serves as a template to building the layout for the columns (attributes or fields) that go into the tables.

Start the database design by laying out all of the fields in a table, and then just throw in some sample data. Remember that this is still on paper. After throwing in some sample data, reevaluate the table. Normalize the table to 1NF (First Normal Form) by making sure that there are no repeating values in any field. Remember: The way to fix most database problems-CREATE A NEW TABLE! Example:

BeforeAfter 
+-----------------+-----------------+-----------------+-----+
| Lname           | Fname           | Pets            | Key*|
+-----------------+-----------------+-----------------+-----+
| Miller          | Amos            | Wolfy           |  1  |
|                 |                 | Buttercup       |     |
+-----------------+-----------------+-----------------+-----+
| Yoder           | Levi            | Horse 1         |  2  |
|                 |                 | ChickenLittle   |     |
+-----------------+-----------------+-----------------+-----+
| Stutzman        | Muhammed        | Llama Bean      |  3  |
+-----------------+-----------------+-----------------+-----+

                    Owner                                           Pet
+-----------------+-----------------+-----+       +-------+---------------------------+---------+ 
| Lname           | Fname           | OID*|       | PNum* | PName        |  Birthdate | OID (FK)|
+-----------------+-----------------+-----+       +-------+---------------------------+---------+
| Miller          | Amos            |  1  |       | 1     | Wolfy        | 01-13-01   |    1    |
+-----------------+-----------------+-----+       +-------+---------------------------+---------+
| Yoder           | Levi            |  2  |       | 2     | Buttercup    | 08-12-96   |    1    |
+-----------------+-----------------+-----+       +-------+---------------------------+---------+
| Stutzman        | Muhammed        |  3  |       | 3     | Horse 1      | 02-13-02   |    2    |
+-----------------+-----------------+-----+       +-------+---------------------------+---------+
                                                  | 4     | ChickenLittle| 03-12-02   |    2    |
                                                  +-------+---------------------------+---------+
                                                  | 5     | Llama Bean   | 04-21-97   |    3    |
                                                  +-------+---------------------------+---------+
I still need to format the code for you for that database access..

Don't forget to do your homework, dig through sourceforge and play around with Oracle or MySQL on your own PC. See you next week.