Lesson 04: Modeling and Designing Databases

Notes/errata/updates for Chapter 4:
See the official book errata at http://tahaghoghi.com/LearningMySQL/errata.php – Chapter 4 includes pages 109 – 133.

On page 114, it says “For some applications, no combination of attributes can uniquely identify an entity (or it would be too unwieldy to use a large composite key), so we create an artificial attribute that’s defined to be unique and can therefore be used as a key: student numbers, Social Security numbers, driver’s license numbers, and library card numbers are examples of unique attributes created for various applications.” This is known as a “surrogate key”, because the number is a surrogate for the “real” unique way of identification.

On page 131, it says that reverse engineering an ER model from an existing database in MySQL Workbench is in beta testing phase. This function has been stable for a long time, so it’s safe to use.

In the chapter it talks about making sure your data doesn’t repeat itself, and how to design so that there aren’t consistency problems. If you want to learn more about normalization, feel free to listen to the OurSQL Podcast (www.oursql.com), episode 7 about normalization and episode 64, about different normal forms.

As you are reading this chapter, if you are wondering how a model relates to reality, know that for the most part, entities become tables and attributes become fields in a table. Relationships may or may not become tables, though for many-to-many relationships, they usually do.

For the homework, DO NOT USE MySQL Workbench to make Entity/Relationship diagrams. It is not a true E/R diagram and you will not be able to show everything you need to (e.g. the symbols for a weak entity do not exist in MySQL Workbench). Use flowcharting software or just draw it on paper and scan it in or take a picture, and submit the scan/picture as the homework

Topics covered:
Entity-Relationship diagramming

Supplemental material – podcasts on normalization:
Normalization concepts and 1NF: http://technocation.org/content/oursql-episode-7-whats-it-be-normal
2NF, 3NF and when to denormalize: http://technocation.org/content/oursql-episode-64-are-we-normal-yet

Reference/Quick Links for MySQL Marinate

One response to “Lesson 04: Modeling and Designing Databases

  1. Pingback: MySQL Marinate Quick Links | Sheeri Dot Com