DMLab3 This lab is in two parts. The first part is worth 50points and consists of 5 questions worth 10 points apiece fromchapter 5 of the text. The second part consists of making a modelof the Student database from DMLab1 with a couple of modifications.The modifications are shown in part 2 of the lab. Part 1 5.10Define relationship. Give an example of a relationship (other thanone presented in this chapter). Name your relationship. 5.11Explain the difference between a relationship class and arelationship instance. 5.12 What is the degree of relationship?Give an example of a relationship of degree three (other than onepresented in this chapter). 5.28 What is a weak entity? How do weakentities relation to ID-dependent entities? 5.29 What distinguishesa weak entity from a strong entity that has a required relationshipto another entity? Part 2 The un-normalized elation to be modeledis: STUDENT(Name, Major, Nickname, EmailAddress, AdvisorNumber,AdvisorName, Club, ClubCost) 1. A student has exactly one name 2. Astudent has exactly on major (though the major can be“undeclared”). 3. A student may have zero or more nicknames 4. Astudent must have at least one email address but may have multipleemail addresses. 5. A student must have an advisor. An advisor mayhave zero or more students to advise. Advisors are identified bytheir number and have an additional attribute, name. 6. A studentmay belong to zero or more clubs. All members of a specific clubpay the same club cost, but a different clubs may have differentcosts. For example, all members of the sailing club pay the samecost, but the cost to be a sailing club member could be differentthan, say, the tennis club. Keep in mind that you will create andpopulate this database for DMLab4. You will also have to write aselect query to join the separate relations together to get theoriginal relation shown at the beginning of the part 2. Here arethe dependencies and normalized (to BCNF and 4F) relationsFunctional dependencies: Number => (Name, Major, AdvisorNumber)AdvisorNumber => AdvisorName Club => ClubCost Multivalueddependencies: Number =>=> Club Number =>=> NicknameNumber =>=> EmailAddress Relations STUDENT(Number, Name,Major, AdvisorNumber) FK: AdvisorNumber must exist inADVISOR.AdvisorNumber ADVISOR(AdvisorNumber, AdvisorName)CLUB(Club, ClubCost) CLUB_MEMBERSHIP(Number, Club) FK:Number mustexist in STUDENT.Number FK:Club must exist in CLUB.ClubEMAIL(EmailAddress, Number) FK:Number must exist in STUDENT.NumberNICKNAME(Number, Nickname) FK:Number must exist in STUDENT.NumberPart A( 25 points): Create an ER diagram for this model. The ERdiagram should have a entity for each of the above relations aswell show the relationships between them. The entity boxes shouldlist the attributes of the entity with the primary keys and foreignkeys marked by PK and FK respectively. If you used a surrogate key,use AK to mark the possible candidate key. An example might be ifyou modeled NICKNAME with a surrogate key. In that case (Number,Nickname) is a compound candidate key. Use either crows feet orcardinality labels to show both the minimum and maximum cardinalityfor the relationships. The diagram below gives the required diagrampartially filled out you can use as an example. Consider therelationship shown in the above diagram. It says that a student isassigned exactly advisor, but an advisor may advise zero or morestudents. Note about ER diagramming software. All of you haveaccess to Visio through DreamSpark. However, Visio is not the bestsoftware to use as it can’t model a many-to-many relationship.Instead it must model a many-to-many relationship as two 1-manyrelationships to a link (or intersection) table. See chapter 6 formore information about intersection tables. Note that in thisexample, the normalization process created the intersection tablefor us (e.g. CLUB_MEMBERSHIP is an intersection table between CLUBand STUDENT; there is one other in the relations given above). Iuse yEd, http://www.yworks.com/en/products_yed_about.html, whichdoes a pretty good job, but takes some getting use to; not becauseit’s hard, but because it’s different. Here’s how to draw therelationships 1. Draw a line between the two entities. Don’t worryabout 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 datadictionary is just the column property tables shown in chapter 6 ofthe text at page 291 in the section Column Properties for the ViewRidge Database Design Tables. Here is a possible data dictionaryfor the STUDENT table (you can use this or a variation). Note thatin general VARCHAR(50) is used for a full name. Column Name TypeKey NULL Status Remarks Number Int Primary Key Not Null NameVARCHAR(50) Not Null Major VARCHAR(25) Not Null Default is“undeclared” AdvisorNumber Int Foreign Key
We are a professional writing service that provides original papers. Our products include academic papers of varying complexity and other personalized services, along with research materials for assistance purposes only. All the materials from our website should be used with proper references.