Week 11 - MySQL Assignment 1 This assignment will test your understanding of database design. You should find the following file in your MySQL directory: MYSQL_Assignment_1 When you are finished the assignment, you should have the following files in your MySQL directory: design.txt MYSQL_Assignment_1 HAND IN: design.txt This file will be collected for marking at 11:59pm on Tuesday April 6, 2010. Make sure your final copy is in your MySQL directory! ============================================================================= PART 1: DEFINING THE DATA This assignment will be focused on designing a normalized relational database for weather media and publications. 1. Create/open a file called design.txt using your favorite editor. 2. Put your name and student number on the first line. 3. Put a header for this first section that looks like: PART 1 ==================================================== 4. This first part is about identifying data and entities we need to store. Make a list of all relevant entities and their attributes. You can list this out however you like, but it should be obvious what are entities and what are attributes. For example, you might have the entity book with attributes as follows: book [ author, ISBN, title, published_city, published_country ] There are two entities you need to track: books and websites. Don't worry about how these entities relate to each other yet. Be thorough about their attributes (ie what do you need to track for a website?). Don't worry if you can't think of every detail yet. Some will become more obvious as you normalize your design. 5. Make sure each entity has a primary key. Write down the primary key for each entity as follows: book: ISBN, published_city 6. Save design.txt. ============================================================================= PART 2: FIRST NORMAL FORM Now that you have a list of entities and attributes for your database, it is time to start normalizing. This part will put your design into first normal form. 1. Open design.txt with your favorite editor (if it isn't already). 2. Add a header for this section like: PART 2 ==================================================== 3. Examine each entity to make sure each non-key attribute has a single value. Using the example of book above book [ author, ISBN, title, published_city, published_country ] Each book will have only one ISBN and one title. However, there may be multiple authors. So author should be it's own entity. That would have to be broken out and attributes assigned. Check all your entities, even any new ones, and make sure all attributes are single valued. For any new entities you create, give a brief explanation why you created it, and where it came from (ie book can have more than one author). 4. As in PART 1, list out the entities with their primary key. 5. Create a listing of relationships between your entities. Use -- to indicate a one-to-one relationship, -< to indicate a one-to-many relationship, and >< to indicate a many-to-many relationship. For example, with book and author we would have: book [ ISBN, title, published_city, published_country ] >< author [ name ] Each book can have multiple authors, and each author can write multiple books. 6. Save design.txt. ============================================================================= PART 3: SECOND NORMAL FORM Now you will put your design into second normal form. 1. Open design.txt with your favourite editor. 2. Add a header for this section like: PART 3 ==================================================== 3. Entities that have keys conssting of a single attribute are already in second normal form. All other entities must be checked. Attributes that are dependent on part of the primary key must be broken out. In the book entity, published_country is dependent on published_city but not on the ISBN. published should be made a new entity encapsulating published_city with published_country. Check that all your new entities are in second normal form. 4. List out the entities with their primary keys as in PART 1. 5. List out your relationships between entities using the notation from PART 2. 6. Save design.txt. ============================================================================= PART 4: THIRD NORMAL FORM In this part, you will put your design into third normal form and finalize it. 1. Open design.txt in your favourite editor. 2. Add a header for this section like: PART 4 ==================================================== 3. Examine your entities to see if there are any cases where attributes rely on each other (changing one value would affect the other value) where both attributes are not identifying attributes. Considering the book example, ISBN would be a good identifier for books since each book has a unique ISBN. Title only relys on ISBN for a given book. Suppose we added the attributes province and province abbreviation to the author table. In this case, province abbreviation would be dependant on province. In that case, province together with province abbreviation should be broken out and made into its own entity. Again, review any new entities to make sure they are in first, second and third normal form. Give a brief explanation for each change you make. It is quite possible that your design will already be in third normal form before you start this part. If it is, give a brief review and explanation of each entity you currently have and what their attributes represent. 5. List out the entities with their primary keys as in PART 1 4. List out your relationships between entities using the notation from PART 2. 5. Save design.txt. ============================================================================= PART 5: UNIQUE IDENTIFIERS Now that the design is normalized, you need to add unique identifiers for entities that have primary keys defined by strings. 1. Open design.txt in your favourite editor. 2. Add a header for this section like: PART 5 ==================================================== 3. Examine each entity and decide if the primary key is defined using strings. If it does, add an attribute that will stand for the unique identifier. In the example of book, the ISBN attribute is already a perfect unique identifier. For author however, name is not a good identifier. We would need to add an attribute AuthorID to author. 4. List out all of your entities and their unique identifiers like this: book -> ISBN author -> authorID 5. Save design.txt. ============================================================================= PART 6: DEALING WITH MANY-TO-MANY RELATIONSHIPS Many-to-many relationships pose a special problem when it comes to coding a database. The easiest way to deal with them is to create an entity, even a non-realistic one, that acts as a go between for the two entities. 1. Open design.txt in your favourite editor. 2. Add a header for this section like: PART 6 ==================================================== 3. Examine your design. If you do not have any many-to-many relationships, just make a note (ie No many-to-many relationships.), save design.txt, and go to part 7. If you do have many-to-many relationships, create a new entity to act as a go between for each relationship. Consider the book example. We have a many-to-many relationship with author. Each book can have multiple authors and each author can write multiple books. To fix this, we can create a new entity like this book -< book_to_author author -< book_to_author book_to_author isn't a real entity, all it does is catalogue all the combinations of books and their authors. So it's attributes would have a unique ID along with foreign keys for book and author. Usually, bridging entities like this are real things, rather than abstract relationships. Check all relationships. Remove any many-to-many relationships you have by creating bridging entities (real or abstract). 4. List out your relationships between entities using the notation from part 2. 5. Save design.txt. ============================================================================= PART 7: ONE-TO-ONE RELATIONSHIPS One-to-one relationships are another special case in database design. There is no particular problem to coding them, however, they usually indicate two entities that are essentially the same. It is important to check and see if those entities cannot be made into one entity. 1. Open design.txt in your favourite editor. 2. Add a header for this section like: PART 7 ==================================================== 3. Examine your design. If you do not have any one-to-one relationships, just make a note (ie No one-to-one relationships.), save design.txt, and go to part 8. If you do have one-to-one relationships, examine the entities that share the relationship and decide if they are really different. Even if they seem different, it is usually best to make the entities into one, melding all the attributes together into a single entity with a single unique indentifier. Check all relationships. Remove any one-to-one relationships. 4. List out your relationships between entities using the notation from part 2. 5. Save design.txt. ============================================================================= PART 8: CODE TRANSLATION Finally, you will translate your design into code structure. 1. Open design.txt in your favourite editor. 2. Add a header for this section like: PART 8 ==================================================== 3. Taking your design, and following the rules in the course notes, write out the code translation for your design. It should look similar to this: TABLE COLUMN TYPE KEY -------------------------------------------------------------- book ISBN String Primary Key Title String -------------------------------------------------------------- author AuthorID Integer Primary Key Name String -------------------------------------------------------------- book_to_author BtoAID Integer Primary Key ISBN String Foreign Key AuthorID Integer Foreign Key 4. Save design.txt. ============================================================================= PART 9: MAKE SURE YOUR FINISHED FILES ARE IN YOUR MySQL DIRECTORY! DEADLINE: 11:59pm Tuesday April 6, 2010