none
Normalisation to 3NF - Whats the best approach?

    Question

  • I don't need a solution more the best approach to take, given there is no technical specification or data to work with as was told I don't need any. I think the task was designed to get a better understanding of relational database design more than anything. How would you tackle this?

    To learn and understand the process of restructuring a relation database in accordance with the normal form rules. To demsontrate an understanding of this process, apply the rules up to 3rd Normal Form to the data structure and document the structure as it appears in each Normal Form between UNF and 3NF and create a presentation to cascade what has been learned to the BI development team, then the wider development team, and finally the Analyst team by the end of October 2018

    The data structure is below


    CREATE TABLE [dbo].[PatientMart](

                    [LVL1SWALLOW] [varchar](max) NULL,

                    [PRACTITIONER1] [varchar](max) NULL,

                    [PRACTITIONER3] [varchar](max) NULL,

                    [PRACTITIONER2] [varchar](max) NULL,

                    [OTASSREFTM] [varchar](max) NULL,

                    [PCBQUESTION5] [varchar](max) NULL,

                    [PLATELETTHRPY] [varchar](max) NULL,

                    [DIAGNSTICS] [varchar](max) NULL,

                    [DIAGULTRA] [varchar](max) NULL,

                    [PLATELETTHRPYCONTRA] [varchar](max) NULL,

                    [PHARMTABNOTES] [varchar](max) NULL,

                    [DISCHARGELOC] [varchar](max) NULL,

                    [PHARMOWNDRUGSBROUGHT] [varchar](max) NULL,

                    [SKINBUNDLE] [varchar](max) NULL,

                    [BOOKINGNUMBER] [varchar](max) NULL,

                    [PATIENTPROPERTY] [varchar](max) NULL,

                    [FALLRISK] [varchar](max) NULL,

                    [PUDATIX] [varchar](max) NULL,

                    [NEUROLOGY] [varchar](max) NULL,

                    [ASSESSMENT10] [varchar](max) NULL,

                    [LTCTEXTUNUSED] [varchar](max) NULL,

                    [DISCHARGELOC2] [varchar](max) NULL,

                    [WASHDRESS] [varchar](max) NULL,

                    [WARDTFRCHGDATE] [varchar](max) NULL,

                    [PLANSTATUS] [varchar](max) NULL,

                    [OTASS] [varchar](max) NULL,

                    [DESTINATION] [varchar](10) NULL,

                    [BLANK] [varchar](max) NULL,

                    [LTCTEXT] [varchar](max) NULL,

                    [WARDPLACEMENT] [varchar](max) NULL,

                    [MEDTOGO] [varchar](max) NULL,

                    [NURSEASSESS] [varchar](max) NULL,

                    [CONSULTATION] [varchar](max) NULL,

                    [PRESSAIDS] [varchar](max) NULL,

                    [PCBQUESTION4] [varchar](max) NULL,

                    [NUTRTION] [varchar](max) NULL,

                    [PCBQUESTION3] [varchar](max) NULL,

                    [LVL1SWALLOWNA] [varchar](max) NULL,

                    [DIETICIAN] [varchar](max) NULL,

                    [PCBQUESTION2] [varchar](max) NULL,

                    [PCBQUESTION1] [varchar](max) NULL,

                    [MACPATIENT] [varchar](max) NULL,

                    [DATAPMWARDTFRCHGDAT] [datetime] NULL,

                    [MHADATES] [varchar](255) NULL,

                    [PHARMDRUGSDATE] [varchar](max) NULL,

                    [CUBREQD] [varchar](max) NULL,

                    [PATHWAY18WEEK] [varchar](max) NULL,

                    [HL7CATCODE] [varchar](max) NULL,

                    [READYTOGO] [varchar](max) NULL,

                    [REHABGOALS] [varchar](max) NULL,

                    [PCBQUESTION2T] [varchar](max) NULL,

                    [EWSDATE] [varchar](max) NULL,

                    [DIABETICDIET] [varchar](max) NULL,

                    [DIABETICMED] [varchar](max) NULL,

                    [GENFLAGS] [varchar](100) NULL,

                    [DIAGDISCUSSED] [varchar](max) NULL,

                    [PCBQUESTION2D] [varchar](max) NULL,

                    [UNUSED288] [varchar](max) NULL,

                    [UNUSED289] [varchar](max) NULL,

                    [REFERRALREASON] [varchar](max) NULL,

                    [PHYSIO] [varchar](max) NULL,

                    [UNUSED286] [varchar](max) NULL,

                    [PROGRESS] [varchar](max) NULL,

                    [UNUSED287] [varchar](max) NULL,

                    [ISOLATIONREQUIRED] [varchar](max) NULL,

                    [RECNURSE] [varchar](max) NULL,

                    [CLERKING] [varchar](max) NULL,

                    [ADMITTEDWITH] [varchar](max) NULL,

                    [HL7EXPECTEDSURGERY] [varchar](max) NULL,

                    [ASSESSMENT5A] [varchar](max) NULL,

                    [RECONCOMPLIST] [varchar](max) NULL,

                    [ASSESSMENT5B] [varchar](max) NULL,

                    [DIAGBLOODS] [varchar](max) NULL,

                    [WARDADMIS] [varchar](max) NULL,

                    [WARDDISCHRGDATE] [varchar](max) NULL,

                    [COMPDISNEEDED] [varchar](max) NULL,

                    [HOMESTATUS] [varchar](max) NULL,

                    [ORALSUP] [varchar](max) NULL,

                    [SKINDATE] [varchar](max) NULL,

                    [DIABETICINFO] [varchar](max) NULL,

                    [HL7CASENOTE] [varchar](max) NULL,

                    [SPECIALITYP1] [varchar](max) NULL,

                    [SEATING] [varchar](max) NULL,

                    [NOTES] [varchar](max) NULL,

                    [BASEWARD] [varchar](max) NULL,

                    [BRAINIMGNGTIME] [varchar](max) NULL,

                    [OBSPRESET] [varchar](max) NULL,

                    [DIAG24] [varchar](max) NULL,

                    [SALT] [varchar](max) NULL,

                    [THEATRETIME] [varchar](max) NULL,

                    [PHYSICALHEALTH] [varchar](max) NULL,

                    [H6TCIWARD] [varchar](max) NULL,

                    [DIAGANGIO] [varchar](max) NULL,

                    [OUTDOORCLOTHING] [varchar](max) NULL,

                    [FOODRECORD] [varchar](max) NULL,

                    [PHYSIOASSREFTM] [varchar](max) NULL,

                    [ADMITSITE] [varchar](max) NULL,

                    [PNAMEX] [varchar](max) NULL,

                    [WEIGHED] [varchar](max) NULL,

                    [PHARMDRUGSTIME] [varchar](max) NULL,

                    [SITECODE] [varchar](10) NULL,

                    [DISCHARGELET] [varchar](max) NULL,

                    [UNUSED258] [varchar](max) NULL,

                    [UNUSED257] [varchar](max) NULL,

                    [PHARMACY] [varchar](max) NULL,

                    [IDL] [varchar](max) NULL,

                    [OTASSNA] [varchar](max) NULL,

                    [PCBQUESTION5D] [varchar](max) NULL,

                    [HL7OUTLIERIND] [varchar](max) NULL,

                    [PATIENTPASSPORT] [varchar](max) NULL,

                    [LVL1SWALLOWPASS] [varchar](max) NULL,

                    [LTCINFO] [varchar](max) NULL,

                    [HL7OLDBED] [varchar](max) NULL,

                    [DIAGCTTIME] [varchar](max) NULL,

                    [PCBQUESTION5T] [varchar](max) NULL,

                    [AVPU] [varchar](max) NULL,

                    [GIVEN] [varchar](max) NULL,

                    [UNUSED247] [varchar](max) NULL,

                    [PHYSIOREFDATE] [varchar](max) NULL,

                    [DIABETICINFO2] [varchar](max) NULL,

                    [USERID] [varchar](100) NULL,

                    [LVL1SWALLOWTIME] [varchar](max) NULL,

                    [MDT] [varchar](max) NULL,

                    [REVIEWER] [varchar](100) NULL,

                    [ASSESSMENT6A] [varchar](max) NULL,

                    [PHARMTABREQ] [varchar](max) NULL,

                    [DOCTORCLERKED] [varchar](max) NULL,

                    [BACKGROUND4] [varchar](max) NULL,

                    [SURGERYDATE] [varchar](max) NULL,

                    [REHAB] [varchar](max) NULL,

                    [DNAR] [varchar](max) NULL,

                    [MOBILITY] [varchar](max) NULL,

                    [UNUSED12] [varchar](max) NULL,

                    [BREACHTIME] [varchar](max) NULL,

                    [UNUSED11] [varchar](max) NULL,

                    [UNUSED10] [varchar](max) NULL,

                    [BACKGROUND1] [varchar](max) NULL,

                    [DIETETICS] [varchar](max) NULL,

                    [UNUSED14] [varchar](max) NULL,

                    [USEBEDLIST] [varchar](10) NULL,

                    [UNUSED13] [varchar](max) NULL,

                    [PREFPLACEOFCARE] [varchar](max) NULL,

                    [DIAGCAROTID] [varchar](max) NULL,

                    [DIAGREFINFO] [varchar](max) NULL,

                    [DIAGREF] [varchar](max) NULL,

                    [OT] [varchar](max) NULL,

                    [DCBDD] [varchar](max) NULL,

                    [WEIGHEDDATE] [varchar](max) NULL,

                    [SPECIALITY] [varchar](max) NULL,

                    [PLATELETTHRPYTIME] [varchar](max) NULL,

                    [TCICUBREQD] [varchar](max) NULL,

                    [PROCDURE] [varchar](max) NULL,

                    [MEDICATION] [varchar](10) NULL,

                    [DIALYSISACCESS] [varchar](max) NULL,

                    [DCBQUESTION9] [varchar](max) NULL,

                    [SOCIAL] [varchar](max) NULL,

                    [SWALLOWASS] [varchar](max) NULL,

                    [PASTHISTORY] [varchar](max) NULL,

                    [SPECREVIEW] [varchar](max) NULL,

                    [PHYSIOASS] [varchar](max) NULL,

                    [SWALLOWASSREFDT] [varchar](max) NULL,

                    [SWALLOWASSTIME] [varchar](max) NULL,

                    [ID] [int] NOT NULL,

                    [DCBQUESTION1] [varchar](max) NULL,

                    [DCBQUESTION2] [varchar](max) NULL,

                    [DCBQUESTION3] [varchar](max) NULL,

                    [DCBQUESTION4] [varchar](max) NULL,

                    [DCBQUESTION5] [varchar](max) NULL,

                    [DCBQUESTION6] [varchar](max) NULL,

                    [DCBQUESTION7] [varchar](max) NULL,

                    [DCBQUESTION8] [varchar](max) NULL,

                    [TRANSPORTMOBILITY] [varchar](max) NULL,

                    [CURRMEDICATION] [varchar](max) NULL,

                    [DDA] [varchar](max) NULL,

                    [INFECTIONP1] [varchar](max) NULL,

                    [MENTALHEALTH] [varchar](max) NULL,

                    [WOUNDCHART] [varchar](max) NULL,

                    [IDLUNUSED] [varchar](max) NULL,

                    [DIAGCTDATE] [varchar](max) NULL,

                    [OTASSREFDT] [varchar](max) NULL,

                    [RESERVEDFOR] [varchar](max) NULL,

                    [RECONCOMP] [varchar](max) NULL,

                    [SENIORREVIEW] [varchar](max) NULL,

                    [LEGALSTATUS] [varchar](255) NULL,

                    [WAITAREA] [varchar](max) NULL,

                    [FLUIDBALANCE] [varchar](max) NULL,

                    [REGISTERED] [varchar](10) NULL,

                    [DATAPMWARDDISCHRGDAT] [datetime] NULL,

                    [BEDREASON] [varchar](max) NULL,

                    [DISTRICTNURSE] [varchar](max) NULL,

                    [ADDRESS] [varchar](255) NULL,

                    [CONFIRMEDTO] [varchar](max) NULL,

                    [SALTREFDATE] [varchar](max) NULL,

                    [INVESTIGATIONS] [varchar](max) NULL,

                    [WARDSTATUS] [varchar](10) NULL,

                    [SITUATION5] [varchar](max) NULL,

                    [TRANSPORTDEST] [varchar](max) NULL,

                    [GENDER] [varchar](max) NULL,

                    [DIAGXRAY] [varchar](max) NULL,

                    [PCT] [varchar](max) NULL,

                    [RECOMMENDATIONS2] [varchar](max) NULL,

                    [ICT] [varchar](max) NULL,

                    [RECOMMENDATIONS1] [varchar](max) NULL,

                    [DIAGNOSIS] [varchar](max) NULL,

                    [STROKEDATE] [varchar](max) NULL,

                    [PTOT] [varchar](max) NULL,

                    [SITUATION6] [varchar](max) NULL,

                    [SITUATION7] [varchar](max) NULL,

                    [SITUATION8] [varchar](max) NULL,

                    [ADMITDEST] [varchar](max) NULL,

                    [SITUATION9] [varchar](max) NULL,

                    [SWALLOWASSNA] [varchar](max) NULL,

                    [WASHDRESSDATE] [varchar](max) NULL,

                    [BEDNAME] [varchar](10) NULL,

                    [SOCSERVDATE] [varchar](max) NULL,

                    [DIAGECHO] [varchar](max) NULL,

                    [HL7ACCTNUM] [varchar](max) NULL,

                    [DIAGCTANGIO] [varchar](max) NULL,

                    [OBSLIST] [varchar](max) NULL,

                    [ACTION] [varchar](50) NULL,

                    [BEDAVAIL] [varchar](max) NULL,

                    [STROKEINFO] [varchar](max) NULL,

                    [EWSSCORE] [varchar](max) NULL,

                    [HL7HOSPCODE] [varchar](max) NULL,

                    [PHYSIOASSDATE] [varchar](max) NULL,

                    [OTHERSTATUS] [varchar](max) NULL,

                    [CURBREFER] [varchar](max) NULL,

                    [DIALYSISACCESSDATE] [varchar](max) NULL,

                    [PHARMPATIENT] [varchar](max) NULL,

                    [TRANSPORT] [varchar](max) NULL,

                    [WARDTFRCHGTIME] [varchar](max) NULL,

                    [COMHOSP] [varchar](max) NULL,

                    [OTASSTIME] [varchar](max) NULL,

                    [PALLIATIVERAPIDDISCHARGE] [varchar](max) NULL,

                    [PHARMTABREQTEXT] [varchar](max) NULL,

                    [PHYSIOASSNA] [varchar](max) NULL,

                    [PLANTYPE] [varchar](max) NULL,

                    [TRANSNURSE] [varchar](max) NULL,

                    [LVL1SWALLOWDATE] [varchar](max) NULL,

                    [HOMESTATUS2] [varchar](max) NULL,

                    [CONSENT] [varchar](max) NULL,

                    [TRANSPORTTIMEBOOKED] [varchar](max) NULL,

                    [DCBQUESTION4T] [varchar](max) NULL,

                    [SERVICE] [varchar](100) NULL,

                    [FROMBED] [varchar](10) NULL,

                    [FOODIN] [varchar](max) NULL,

                    [PUMPPATIENT] [varchar](max) NULL,

                    [DCBQUESTION5D] [varchar](max) NULL,

                    [INFECTION] [varchar](max) NULL,

                    [RELATIVEAWARETRANS] [varchar](max) NULL,

                    [DIAGDISCUSSEDDATE] [varchar](max) NULL,

                    [MOODASSDATE] [varchar](max) NULL,

                    [THEATREDATE] [varchar](max) NULL,

                    [MACTIME] [varchar](max) NULL,

                    [ANTIPLATELET] [varchar](max) NULL,

                    [S2] [varchar](max) NULL,

                    [HL7EPISODE] [varchar](max) NULL,

                    [OWNER] [varchar](50) NULL,

                    [BACKGROUND12] [varchar](max) NULL,

                    [SIDEROOM] [varchar](10) NULL,

                    [PLATELETTHRPYDATE] [varchar](max) NULL,

                    [MEDICALREVIEW] [varchar](max) NULL,

                    [RISKTOSELF] [varchar](255) NULL,

                    [PREVMEDICATION] [varchar](max) NULL,

                    [SOCIALSERV] [varchar](max) NULL,

                    [TRANSPORTTIME] [varchar](max) NULL,

                    [DIETETICSREFDATE] [varchar](max) NULL,

                    [PATIENTAWARETRANS] [varchar](max) NULL,

                    [TOWARD] [varchar](50) NULL,

                    [WARDNAME] [varchar](50) NULL,

                    [PU] [varchar](max) NULL,

                    [MOBILITYINFO] [varchar](max) NULL,

                    [TRANSFERREASON] [varchar](max) NULL,

                    [PHYSIOASSTIME] [varchar](max) NULL,

                    [CAREBUNDLEPROG] [varchar](max) NULL,

                    [DATAPMWARDTFRCHGDATE] [datetime] NULL,

                    [TRANSDATE] [varchar](max) NULL,

                    [NURSENEEDS] [varchar](max) NULL,

                    [POSTCODE] [varchar](max) NULL,

                    [KNOWNTOSITE] [varchar](255) NULL,

                    [DISCHARGEREASON] [varchar](max) NULL,

                    [ADMISTIME] [varchar](max) NULL,

                    [TRANSPORTISSUES2] [varchar](max) NULL,

                    [TRANSPORTNOTE] [varchar](max) NULL,

                    [AGE] [varchar](max) NULL,

                    [HOMETODAY] [varchar](max) NULL,

                    [NHSNUM] [varchar](max) NULL,

                    [DCBQUESTION12] [varchar](max) NULL,

                    [DCBQUESTION10] [varchar](max) NULL,

                    [DCBQUESTION11] [varchar](max) NULL,

                    [DISDATE] [varchar](max) NULL,

                    [SPECIALISTPRACTITIONER3] [varchar](max) NULL,

                    [ENTITYID] [varchar](50) NULL,

                    [S5] [varchar](max) NULL,

                    [DIAGMRI] [varchar](max) NULL,

                    [GPADDRESS] [varchar](255) NULL,

                    [TRANSPORTTYPE] [varchar](max) NULL,

                    [WARDTIME] [varchar](max) NULL,

                    [CONSULTANT] [varchar](max) NULL,

                    [HARMRISK] [varchar](max) NULL,

                    [OUTPATIENTSFOLLOWUP] [varchar](max) NULL,

                    [SPECIALISTPRACTITIONER2] [varchar](max) NULL,

                    [SPECIALISTPRACTITIONER1] [varchar](max) NULL,

                    [TTA] [varchar](10) NULL,

                    [DIABETIC] [varchar](max) NULL,

                    [OTASSDATE] [varchar](max) NULL,

                    [ASSESSMENT5] [varchar](max) NULL,

                    [ASSESSMENT4] [varchar](max) NULL,

                    [ASSESSMENT3] [varchar](max) NULL,

                    [ASSESSMENT2] [varchar](max) NULL,

                    [ASSESSMENT9] [varchar](max) NULL,

                    [ASSESSMENT8] [varchar](max) NULL,

                    [ASSESSMENT7] [varchar](max) NULL,

                    [ASSESSMENT6] [varchar](max) NULL,

                    [WATERLOWSCORE] [varchar](max) NULL,

                    [FROMLOC] [varchar](max) NULL,

                    [WARDDISCHRGTIME] [varchar](max) NULL,

                    [LPOOLCAREPATHWAY] [varchar](max) NULL,

                    [TOBED] [varchar](10) NULL,

                    [HL7WARDCODE] [varchar](max) NULL,

                    [UNUSED4] [varchar](max) NULL,

                    [UNUSED5] [varchar](max) NULL,

                    [VTE] [varchar](max) NULL,

                    [CONTACTNUMBER] [varchar](max) NULL,

                    [RESUS] [varchar](10) NULL,

                    [S5DATE] [varchar](max) NULL,

                    [UNUSED1] [varchar](max) NULL,

                    [UNUSED9] [varchar](max) NULL,

                    [UNUSED8] [varchar](max) NULL,

                    [SPECIALITYREVIEW] [varchar](max) NULL,

                    [UNUSED7] [varchar](max) NULL,

                    [UNUSED6] [varchar](max) NULL,

                    [HL7PATIENTID] [varchar](max) NULL,

                    [ADMIT] [varchar](max) NULL,

                    [CARES] [varchar](max) NULL,

                    [SPECOTHER] [varchar](max) NULL,

                    [RECONCOMPDATE] [varchar](max) NULL,

                    [EWS] [varchar](max) NULL,

                    [DIAGNSTICSOTHER] [varchar](max) NULL,

                    [TRANSPORTFROM] [varchar](max) NULL,

                    [OBS11] [varchar](max) NULL,

                    [DISCHARGE] [varchar](10) NULL,

                    [HL7OLDWARD] [varchar](max) NULL,

                    [DIAGBIOP] [varchar](max) NULL,

                    [OTREFDATE] [varchar](max) NULL,

                    [DIAGOTHER] [varchar](max) NULL,

                    [MEDICALLYFIT] [varchar](max) NULL,

                    [CONTINENCE] [varchar](max) NULL,

                    [QUERYHOMETODAY] [varchar](max) NULL,

                    [SKIN] [varchar](max) NULL,

                    [REHABLOC] [varchar](max) NULL,

                    [PTVSCR] [varchar](max) NULL,

                    [REHABGOALSDATE] [varchar](max) NULL,

                    [SITUATION10] [varchar](max) NULL,

                    [PHARMDRUGSREADY] [varchar](max) NULL,

                    [DIAGCT] [varchar](max) NULL,

                    [PRIORITY] [varchar](max) NULL,

                    [SWALLOWASSDATE] [varchar](max) NULL,

                    [RESPONSE] [varchar](50) NULL,

                    [SOCSERVLIST] [varchar](max) NULL,

                    [FLUIDIN] [varchar](max) NULL,

                    [PODSCHECKEDDISCHARGE] [varchar](max) NULL,

                    [ATTENDERCOMPLETE] [varchar](max) NULL,

                    [SPECREGISTER] [varchar](max) NULL,

                    [DOB] [varchar](max) NULL,

                    [WASHDRESSTIME] [varchar](max) NULL,

                    [HL7EXPECTEDLOS] [varchar](max) NULL,

                    [DOD] [varchar](max) NULL,

                    [S2DATE] [varchar](max) NULL,

                    [ANTICOAG] [varchar](max) NULL,

                    [DCBQUESTION5T] [varchar](max) NULL,

                    [NUTRITION] [varchar](max) NULL,

                    [DNR] [varchar](max) NULL,

                    [LTC] [varchar](max) NULL,

                    [EWSREVIEW] [varchar](max) NULL,

                    [ADMISDATE] [varchar](max) NULL,

                    [TRANSFERS] [varchar](max) NULL,

                    [DIAGBRONC] [varchar](max) NULL,

                    [SOCSERV] [varchar](max) NULL,

                    [TRANSPORTMOB] [varchar](max) NULL,

                    [PENDINGTHEATRE] [varchar](max) NULL,

                    [BRAINIMGNG] [varchar](max) NULL,

                    [DIETFLUID] [varchar](max) NULL,

                    [TRANSPORTDATEBOOKED] [varchar](max) NULL,

                    [PLANOFCARE] [varchar](max) NULL,

                    [MOODASS] [varchar](max) NULL,

                    [SPECTYPE] [varchar](max) NULL,

                    [LTCAAU] [varchar](max) NULL,

                    [PREVHIST] [varchar](max) NULL,

                    [TRANSPORTISSUES] [varchar](max) NULL,

                    [DIABETICINS] [varchar](max) NULL,

                    [SWALLOWASSREFTM] [varchar](max) NULL,

                    [OTHER] [varchar](max) NULL,

                    [MEDICATIONAIDS] [varchar](max) NULL,

                    [SUPPORTNURSE] [varchar](max) NULL,

                    [CAREPATHWAY] [varchar](max) NULL,

                    [EWSTIME] [varchar](max) NULL,

                    [REFERREDDATE] [varchar](255) NULL,

                    [AUDITTIME] [datetime] NULL,

                    [DATAPMADMISDATE] [datetime] NULL,

                    [NILBYMOUTH] [varchar](max) NULL,

                    [ALLERGIES] [varchar](max) NULL,

                    [RTT] [varchar](max) NULL,

                    [FROMWARD] [varchar](50) NULL,

                    [PUW] [varchar](max) NULL,

                    [PREFX] [varchar](max) NULL,

                    [DIABETICSTATUS] [varchar](max) NULL,

                    [PHYSIOASSREFDT] [varchar](max) NULL,

                    [TCIINFO] [varchar](max) NULL,

                    [ONE2ONE] [varchar](10) NULL,

                    [DATAPMWARDDISCHRGDATE] [datetime] NULL,

                    [CURBSCORE] [varchar](max) NULL,

                    [HOSPNUM] [varchar](max) NULL,

                    [BRAINIMGNGDATE] [varchar](max) NULL,

                    [BEDSTATUS] [varchar](max) NULL,

                    [REVIEWDATE] [varchar](max) NULL,

                    [RISKTOOTHERS] [varchar](255) NULL,

                    [KEYS] [varchar](max) NULL,

                    [DIAGENDO] [varchar](max) NULL,

                    [SUPPORTSERVICESONDISCHARGE] [varchar](max) NULL,

                    [TRANSTIME] [varchar](max) NULL,

                    [PATIENTAWAREDIAG] [varchar](max) NULL

    )

    Monday, June 25, 2018 11:25 AM

All replies

  • Hi Simon,

    Is this for a school assignment? If so, what have you been taught about the rules for normalizing a database? When I was studying it, we were given seven steps for normalizing a table.

    Monday, June 25, 2018 2:32 PM
  • Hi SimonKEvans,

    The core purpose of normalization process is to reduce/remove redundancy and modeling entities and relationship in order to make an efficient system for storing and retrieving data. You need to first understand various entities in the system and how they are related to each other for example one-to-one, one-to-many, many-to-many etc. This is called E-R modeling (http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html). After that you can proceed with normalization. Each normal form has defined set of rules and if you follow the steps you will finally reach to the required NF.

    In my opinion, you should take following steps:

    1- Identify the entities in the given dataset e.g. Practitioner, Nurse etc.

    2- Identify and attach attributes of each entity.

    3- Identify relationship among various entities along with their cardinality.

    4. Now for each entity apply normalization rules and further redefine the data base structures. Refer this post for the process: http://agiledata.org/essays/dataNormalization.html


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    Monday, June 25, 2018 3:29 PM
  • Hi Simon,

    Is this for a school assignment? If so, what have you been taught about the rules for normalizing a database? When I was studying it, we were given seven steps for normalizing a table.


    No it's not a school assignment given I am 40. Very new into completely new role as a BI Developer, I have no developer experience at all and was brought in for my knowledge (as an Information Analyst) of the organisation and the data stored within the tables and my continued improvement and drive within the organisation to constantly improve myself daily. Yes I was shocked too as thought I was the 100-1 outsider given zero developer experience. This task is more a theory test and personal development to gather a greater understanding of relational databases. I understand the concept of normalising but never done this in practice. The first thing I asked is where is the data or technical specification but was told I don't need it and guessing would be fine as the task isn't about accuracy of the structure but more the structure is normalized to the 3NF and how I went about this. I was enquiring if other than the standard approaching which can easily be found online if there were any differing approaches others have taken and the consideration taken etc. I find it and have found it wise to find a range of answers look to see which one suits the needs at the time rather than look at the first thing that pops up on Google.
    Tuesday, June 26, 2018 11:22 AM
  • Hello Simon,

    You define every column as varchar(max), that don't make any sense. Before you start to normalize table you should first consider the right data types. I see some column named with "date", but you use varchar(max) instead of a date/time type; bad design.

    And to assist you on normalization we have to know more of the data content; just some column name is here a useless information.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, July 14, 2018 2:38 AM
    Moderator