In Unit 9 we are dealing with databases and learning how to, first of all, design a database. The main concepts in the unit related to databases are entities, attributes, relationships and database normalization . In addition we need to use the MySQL Monitor command line interface and design and model a simple database.
I found the idea of relationships in the entity relationship model to be a difficult concept to grasp What is a one-to-many relationship or a one-to-one relationship and so on. The other difficult concept is normalization which is the process of normalizing a database.
Entity Relationship Models
An entity relationship model (ERM) according to Wikipedia, “is an abstract conceptual representation of structured data.” and “depicts data in terms of the entities and relationships described in the data. The recommended Wikipedia article on data modeling is a helpful introduction but the section on the entity relationship diagrams, which we are working with, could be more suitable for professionals than a beginner like me.
A much simpler introduction to data modeling is the section on Data Modeling in the UACBT course by Joshua Mostafa on MySQL. See http://www.vtc.com/products/mysql.htm
Mostafa starts with a data model based on a library database. He recommends writing out the main elements of the database on a piece of paper before designing the database. Teh elements he suggested were related to a book and included genre, publisher and ISBN number. I followed this procedure and found it helpful in developing my entity relationship diagram.
He then stated that formulating possible search queries or items that you need to search for would help to find out what you needed to do with the database. Do you need to search for a book using its ISBN number, its author or a genre? This is an important consideration which may get overlooked later on.
Data Modeling II deals directly with the entity relationship diagram and reviews the annotations such as one-to-many, one-to-one, and so on. Mostafa dealt directly with a genre entity for books. One genre could have zero to many books so it has a zero to many relationship. However, a book can only belong to one genre. I used some ideas from this to construct my own entity relationship diagram with genres and artist’s works. One genre, for instance, could be abstract paintings.
However, Mostafa’s introduction to entity relationships was not enough to answer all my questions. The Wikipedia article on entity relationship diagrams at http://en.wikipedia.org/wiki/Entity-relationship_diagram helped answer more especially about the different types of relationships. However, the most helpful resource was the Entity Relationship Diagram Example by Lee Richardson at http://rapidapplicationdevelopment.blogspot.com/2007/06/entity-relationship-diagram-example.html I also used database models at http://databaseanswers.org/data_models/index.htm I looked at the section on libraries and found help for my database model task in the base model for libraries, the books model and the sheet music model.
The “recipe” I’ve outlined above is ideal for a beginner and fulfills the concept of a colleague who maintains that any software task should in the beginning be done as simply as possible. He called this technique, the “peanut butter sandwich technique.” Creating an entity relationship diagram for the first time is challenging but starting with the basic information and creating a simple database structure as I did with three entities and three to five attributes per entity is the best way, I believe, to learn the entity relationship diagram concept. As Mostfa suggests in Data Modeling II, the task of creating entity relationship diagrams and database modeling also gets easier when you have created a number of entity relationship diagrams.
The most helpful definition of normalization that I found is,”Normalization is the process of efficiently organizing data in a database.” from http://databases.about.com/od/specificproducts/a/normalization.htm Normalization is “a set of rules” applied to help in the design of databases.
I watched the videos in normalization in Mostafa’s course and then reviewed the tutorial at http://www.phlonx.com/resources/nf3/ However, I found writing about the normalization rules (below) in relation to my proposed database a great exercise in trying to understand normalization. I, however, need to come back to this document and study it to gain a better understanding of normalization. I also used About.com’s section on normalization which had simple examples which were easy to grasp.
Normalization Rules and My Database.
For my proposed database I choose 19th century artists. I then considered different types of people and landscape in artist’s paintings and came up with the categories below. Wikipedia’s classification of paintings at http://en.wikipedia.org/wiki/Category:Paintings_by_subject helped me to think about different categories.
However, under the first normal form which eliminates redundant data having a painting with multiple subject such as royalty and rural landscape would not work. Each painting needs to have one subject as the database user would get confused about the definitions of a subject. For instance, a painting could have both people and a landscape and the question would be whether or not the painting could be classified as depicting a landscape or people.
The first normal state also requires the creation of a primary key which must be related to other fields in the particular primary key’s table. For instance, genre types such as Impressionist and Abstract are related to the genre primary key.The idea of a genre for different paintings is much more easily and strictly defined and I located a number of genres at http://www.almost-art.org/aa-chart-taxonomy.html
Finally, I have a number of resources that I intend to consult in future if I need to create any more databases and am happy to have had an assignment which allowed me to have some practice at creating an entity relationship diagram.
- An Interview with Ian Robinson, Author of Graph Databases, from O’Reilly (architects.dzone.com)
- Design Methodology for Relational Databases: Issues Related to Ternary Relationships in Entity-relationship Model and Higher Normal Forms (slideshare.net)
- Descriptions and Information of Careers for Database Professionals (jobs.answers.com)