DMLab3 This lab is in two parts. The first part is worth 50 points and consists of 5 questions worth 10 points apiece from chapter 5 of the text. The second part consists of making a model of the Student database from DMLab1 with a couple of modifications. The modifications are shown in part 2 of the lab. Part 1 5.10 Define relationship. Give an example of a relationship (other than one presented in this chapter). Name your relationship. 5.11 Explain the difference between a relationship class and a relationship instance. 5.12 What is the degree of relationship? Give an example of a relationship of degree three (other than one presented in this chapter). 5.28 What is a weak entity? How do weak entities relation to ID-dependent entities? 5.29 What distinguishes a weak entity from a strong entity that has a required relationship to another entity? Part 2 The un-normalized elation to be modeled is: STUDENT(Name, Major, Nickname, EmailAddress, AdvisorNumber, AdvisorName, Club, ClubCost) 1. A student has exactly one name 2. A student has exactly on major (though the major can be undeclared). 3. A student may have zero or more nicknames 4. A student must have at least one email address but may have multiple email addresses. 5. A student must have an advisor. An advisor may have zero or more students to advise. Advisors are identified by their number and have an additional attribute, name. 6. A student may belong to zero or more clubs. All members of a specific club pay the same club cost, but a different clubs may have different costs. For example, all members of the sailing club pay the same cost, but the cost to be a sailing club member could be different than, say, the tennis club. Keep in mind that you will create and populate this database for DMLab4. You will also have to write a select query to join the separate relations together to get the original relation shown at the beginning of the part 2. Here are the dependencies and normalized (to BCNF and 4F) relations Functional dependencies: Number => (Name, Major, AdvisorNumber) AdvisorNumber => AdvisorName Club => ClubCost Multivalued dependencies: Number =>=> Club Number =>=> Nickname Number =>=> EmailAddress Relations STUDENT(Number, Name, Major, AdvisorNumber) FK: AdvisorNumber must exist in ADVISOR.AdvisorNumber ADVISOR(AdvisorNumber, AdvisorName) CLUB(Club, ClubCost) CLUB_MEMBERSHIP(Number, Club) FK:Number must exist in STUDENT.Number FK:Club must exist in CLUB.Club EMAIL(EmailAddress, Number) FK:Number must exist in STUDENT.Number NICKNAME(Number, Nickname) FK:Number must exist in STUDENT.Number Part A( 25 points): Create an ER diagram for this model. The ER diagram should have a entity for each of the above relations as well show the relationships between them. The entity boxes should list the attributes of the entity with the primary keys and foreign keys marked by PK and FK respectively. If you used a surrogate key, use AK to mark the possible candidate key. An example might be if you modeled NICKNAME with a surrogate key. In that case (Number, Nickname) is a compound candidate key. Use either crows feet or cardinality labels to show both the minimum and maximum cardinality for the relationships. The diagram below gives the required diagram partially filled out you can use as an example. Consider the relationship shown in the above diagram. It says that a student is assigned exactly advisor, but an advisor may advise zero or more students. Note about ER diagramming software. All of you have access to Visio through DreamSpark. However, Visio is not the best software to use as it cant model a many-to-many relationship. Instead it must model a many-to-many relationship as two 1-many relationships to a link (or intersection) table. See chapter 6 for more information about intersection tables. Note that in this example, the normalization process created the intersection table for us (e.g. CLUB_MEMBERSHIP is an intersection table between CLUB and STUDENT; there is one other in the relations given above). I use yEd, http://www.yworks.com/en/products_yed_about.html, which does a pretty good job, but takes some getting use to; not because its hard, but because its different. Heres how to draw the relationships 1. Draw a line between the two entities. Don't worry about picking what type of line just draw it. 2. Click on the line. 3. Change the source and target arrows (see image below). Part B (25 points): Create a data dictionary for this model. The data dictionary is just the column property tables shown in chapter 6 of the text at page 291 in the section Column Properties for the View Ridge Database Design Tables. Here is a possible data dictionary for the STUDENT table (you can use this or a variation). Note that in general VARCHAR(50) is used for a full name. Column Name Type Key NULL Status Remarks Number Int Primary Key Not Null Name VARCHAR(50) Not Null Major VARCHAR(25) Not Null Default is undeclared AdvisorNumber Int Foreign Key