A.Lab #:BSBA BIS245A-5A

 

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

 

 

 

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).

 

 

 

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.