Levels Tought:
Elementary,Middle School,High School,College,University,PHD
Teaching Since: | Jul 2017 |
Last Sign in: | 211 Weeks Ago, 3 Days Ago |
Questions Answered: | 15833 |
Tutorials Posted: | 15827 |
MBA,PHD, Juris Doctor
Strayer,Devery,Harvard University
Mar-1995 - Mar-2002
Manager Planning
WalMart
Mar-2001 - Feb-2009
Need visio diagram from the attached instructions.
Â
Â
B.Lab 5A of7:Databasedesign usingVisio,andbased ondatarequirements andbusinessrules focusingonnormalizingdatatothirdnormalform.
Â
C.Lab Overview—Scenario/Summary:
Â
TCO(s):
2.Givenasituationcontaining entities,business rules,anddatarequirements, createtheconceptualmodel ofthedatabaseusinga databasemodelingtool.
Â
Scenario:
You havebeenaskedtocreatea databasemodelusing the MSVisioDatabase ModelDiagram Template. Thepurpose ofthisiLabis toprovideexperience normalizing thedatabasetothirdnormalformbasedonlimitedinstructions,data requirements,andassociatedbusinessrules.
Â
Uponcompleting this iLab,youwillbeableto
1.create a new Visio file fordatabasedesign;and
2.usingthedatarequirementsandthebusinessrulesprovided,developa conceptualmodel(ERD), includingattribute datatypesandrequiredfield lengths.
Â
D.Deliverables
Â
Â
Section |
Deliverable |
Points |
Step 1 |
YourName_Lab5A_ERMatrix.docx |
 |
Step 3 |
YourNameLab5A.vsdx (VisioDiagram) |
 |
Â
E.Lab Steps
Â
Preparation
1.Download the ER matrix starter filedocumentfromthe Week 5 iLab page,andsavethe filetoyourlocaldrive.
2.UsingCitrixfor MSVisioand/orMSAccess
a.Ifyouare using the Citrix remotelab,follow thelogininstructions locatedinthe iLab area inCourseHome.
3.StartMSVisio
a. openMicrosoft Office,Visioapplication,or
b.ifyouare usingCitrix,click on MicrosoftOfficeApplicationsfolder tostartVisio.
Lab
Â
Step 1:Identifythe entitiesand relationships.
Â
a.Overthepast 4 weeks,you have createdERDs based on information provided.This week,youwillcompletetheERD beingsuretonormalizeit to
3NF(thirdnormalform).Normalizationis thesystematic applicationofrules designedto eliminateredundancyfrom thedatabase.Datainprevious weeks was,tosome extent,normalizedforyou.This week,itis not. Anabbreviated list ofthedata requirementsfollows.
Â
CollegeScheduling Data Requirements
Â
StudentID
StudentFirstName StudentLastName Course Code
Course Name
CourseDescription Course CreditHours Course Time
CourseDays InstructorID InstructorFirstName Instructor LastName RoomNumber
RoomCapacity
Â
Â
Â
b.Openthe Wordfileinthe Week5LabMaterials,Lab5A_ERMatrix.docx c.Savethe Wordfile as YourName_Lab5A_ERMatrix.
d.AnERMatrixhelps defineboththeentities and the relationshipsbetweenthe entities.UsingthepartiallycompletedERMatrixbelow as a guide,complete
thematrix.
Â
Hint:Youshould end upwithfour entities.
Â
1. Listeachentitybothacross anddown.
2. Determinewhether a relationshipexists betweenentities anddefinethat relationshipwitha verbphrase. (NOTE:The entitywiththe greatest number ofrelated entities isusuallythecenter ofthe ERdiagram.)
3. Identifyminimum(Optional orMandatory)andmaximum (onlyone,or one or more).
Â
Â
Student        Course |
|
Student     Course |
none              Enrolls in; Mandatory Oneor more |
Istakenby:Â Â Â Â Â none Optional Oneor more |
|
 |
|
 |
Â
e.Savethecompleted matrixto submitas part ofyouriLab.
Â
Step 2:Createthe initialERD basedonthe matrix.
Â
a.Openanew blankDatabaseModelDiagraminVisio.Ifyouneedassistancewith this,refertothe Week1 LabInstructions.Besurethatall optionsareset consistent tothose usedinprevious weeks sothatyougenerate your modelin Crow’sFootnotation.
Â
b.Oneofthe issues indenormalizeddata is that itcan result in many-to-many relationships thatarenotcompatiblewiththe relationaldatabase. Visiodoes notallow creation ofamany-to-many relationshipbecause ofthis incompatibility.Therefore,for anymany-to-manyrelationships inyourdata, youwillneed tocreatetwo one-to-manyrelationships.Seethe example below.
Â
Â
c.Based onthe informationfrom Step 1,createtheinitialERDforthe College Schedulingdatabaseincluding the many-to-manyrelationships.Ifyouneed assistancetocreatethe entities, refertoiLabsfromWeeks1and 2.
Â
d.Assigntheprimarykeys andattributesfromthedata requirementstothe proper entities.
Â
e.Savethe file as YourName_Lab5A.vsdx.
Â
Step 3:NormalizetheERD
Â
a.Whilethe initialERD is accurate,ifyoudesigna database withoutdeviating from it,youwillencounterproblems.Forexample,a course isofferedmany times.Ifyou usethecoursecode,for example, BIS245,astheprimarykey,
youwillonlybe abletolist thecourseonce.Therearethreepossible solutions.
1. Â Â Â Assignanotherprimarykey,suchas anauto-number field,sothat thecoursecanbelisted multipletimes.However,this isnot acceptablebecause itintroducesredundancyratherthanreducing it.
2. Â Â Â Usea compositeprimarykeyconsistingofthe Course Codeandthe
Sectioncode.Ifyoucreatea uniquesectioncode(EXAMPLE: YearTerm_Section or2016SpringA_A),thenyoucanlist thecourse repeatedly. Again,this introducesredundancy,andisnotan acceptablesolution.
3. Â Â Â Splitthetabletocreatetwotables. To stayconsistentwithDeVry terminology,create asecondSectiontable.To ensurethatacourseis notenteredtwice,youmightmodifytheSectioncodetoincludethe course:BIS245_2016SpringA_A. Byconsistentlyusingthisformat, youshouldnotbeabletoduplicatethe section,andcanavoida complicatedcompositekey.However,astheSectiontable willserve asanassociative entity,it is acceptabletoborrow theprimarykeysof theparent tables.
Â
b.Openthe Visio file from Step 2,andadda new page.Changethepagetabto read Revised. Revisethe initialERD basedonthe following information.
1. Â Â Â ACoursemayexistwithoutbeing offered.It exists inthecatalog.
Courses arescheduledfora termandgivenasection.Itis the schedulecourse (Section)thatstudents enrollin,thatare scheduled
inroomsandassignedinstructors.
2. Â Â Â A Studentcanexistwithoutbeingenrolledina course.
3. Â Â Â AnInstructorcan existwithoutbeingassignedtoteacha course.
4. Â Â Â ARoomcanexistwithoutbeingscheduledfora course.
c.Savethe filewithyour revisedERD.
Lab 5A FinalDeliverables
a.   YourName_Lab5A_ERMatrix.docx  (WordDocument)fromLab5A, Step1.
b.   YourName_Lab5A.vsdx (VisioDiagram)fromLab5A, Step 3.
Â
Submitthese files tothe Week5 iLabDropbox.
Â
END OF LAB
----------- Â ----------- H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly