MonaVh UniYeUViW FIT2094 – DaWabaVeV MOCK SCHEDULED FINAL ASSESSMENT SAMPLE SOLUTIONS AXWhRU: FIT DaWabaVe TeachiQg TeaP LiceQVe: CRSUighW MRQaVh UQiYeUViW, XQOeVV RWheUZiVe VWaWed. AOO RighWV ReVeUYed. COPYRIGHT WARNING Warning ThiV PaWeUiaO iV SURWecWed b cRSUighW. FRU XVe ZiWhiQ MRQaVh UQiYeUViW RQO. NOT FOR RESALE. DR QRW UePRYe WhiV QRWice. PaJe 1 Rf 21 PART A RelaWional Model [ToWal: 10 MaUkV] Q1 [3 MaUkV] A cRPSaQ ZiVheV WR UecRUd Whe fROORZiQg aWWUibXWeV abRXW WheiU ePSOReeV: ePSORee ID, deSaUWPeQW QXPbeU, QaPe, hRPe addUeVV, edXcaWiRQ TXaOificaWiRQV aQd VkiOOV Zhich Whe ePSORee haV. A VPaOO VaPSOe Rf daWa iV VhRZ beORZ: Emploee ID DepaUWmenW NXmbeU Emploee Name Home AddUeVV QXalificaWion Skill 101 21 GiYeQ QaPe: JRe FaPiO QaPe: BORggV SWUeeW: 12 Wide Rd TRZQ: MWRZQ PRVWcRde: 1234 BacheORU Rf CRPPeUce MBA PURjecW MaQagePeQW HadRRS R 102 13 GiYeQ QaPe: WeQd FaPiO QaPe: XiX SWUeeW: 55 NaUURZ SW TRZQ: MWRZQ PRVWcRde: 1234 BacheORU Rf CRPSXWeU ScieQce MaVWeU Rf IT DRcWRU Rf PhiORVRSh SQL PL/SQL 103 13 GiYeQ QaPe: SaUah FaPiO QaPe: GUeeQ SWUeeW: 25 High SW Rd TRZQ: MWRZQ PRVWcRde: 1234 CeUWificaWe IV iQ BXViQeVV AdPiQiVWUaWiRQ SQL JaYa PhWRQ UVe WhiV daWa WR e[SOaiQ Whe diffeUeQce beWZeeQ a ViPSOe aWWUibXWe, a cRPSRViWe aWWUibXWe aQd a PXOWiYaOXed aWWUibXWe. YRXU aQVZeU PXVW iQcOXde e[aPSOeV dUaZQ fURP WhiV daWa. Simple – an aWWUibXWe Zhich cannoW be VXbdiYided eg. emploeeid, depaUWmenW nXmbeU CompoViWe – an aWWUibXWe Zhich can be VXbdiYided inWo addiWional aWWUibXWeV eg. emploee name, home addUeVV MXlWiYalXed – an aWWUibXWe Zhich haV man poWenWial YalXeV eg. TXalificaWion, Vkill PaJe 2 Rf 21 Q2 [7 MaUkV] The fROORZiQg UeOaWiRQV UeSUeVeQW a SXbOicaWiRQV daWabaVe: aXWhRU (aXWhRU_id, fiUVW_QaPe, OaVW_QaPe) aXWhRU_SaSeU (aXWhRU_id, SaSeU_id, aXWhRU_SRViWiRQ) SaSeU (SaSeU_id, SaSeU_WiWOe, jRXUQaO_id) jRXUQaO (jRXUQaO_id, jRXUQaO_WiWOe, PRQWh, eaU, ediWRU) * ediWRU iQ jRXUQaO UefeUeQceV aXWhRU(aXWhRU_id) ± WhiV iV aQ aXWhRU acWiQg aV Whe jRXUQaO ediWRU AXWhRUV ZUiWe SaSeUV Zhich aUe SXbOiVhed iQ aQ ediWiRQ Rf a jRXUQaO. Each ediWiRQ Rf a jRXUQaO iV aVVigQed a jRXUQaO id aQd aSSRiQWV aQ ediWRU. A giYeQ SaSeU Pa be aXWhRUed b VeYeUaO aXWhRUV, iQ VXch caVeV each aXWhRU iV aVVigQed a SRViWiRQ UeSUeVeQWiQg WheiU cRQWUibXWiRQ WR Whe SaSeU: WUiWe Whe UeOaWiRQaO aOgebUa fRU Whe fROORZiQg TXeUieV (RXU aQVZeU PXVW VhRZ aQ XQdeUVWaQdiQg Rf TXeU efficieQc): LiVW of VmbolV: pUojecW: , VelecW: , join: , lefW oXWeU join , UighW oXWeU join , fXll oXWeU join , inWeUVecW , Xnion , minXV – (a) ShRZ Whe SaSeU WiWOe, jRXUQaO WiWOe aQd PRQWh aQd eaU Rf SXbOicaWiRQ fRU aOO SaSeUV SXbOiVhed befRUe 2012 (3 PaUkV) papeU_WiWle, joXUnal_WiWle, monWh, eaU ( ( joXUnal_id, joXUnal_WiWle, monWh, eaU ( eaU < 2012 (JOURNAL)) ( joXUnal_id, papeU_WiWle(PAPER)) ) OR ANSWER1 = joXUnal_id, joXUnal_WiWle, monWh, eaU ( eaU < 2012 (JOURNAL)) ANSWER2 = joXUnal_id, papeU_WiWle(PAPER) ANSWER3 = ANSWER1 ANSWER2 ANSWER4 = papeU_WiWle, joXUnal_WiWle, monWh, eaU (ANSWER3) Here A
NSWER1 could be done in tZo steps, a select and then a project. PaJe 3 Rf 21 (b) ShRZ Whe QaPeV Rf aOO aXWhRUV ZhR haYe QeYeU beeQ OiVWed aV fiUVW aXWhRU (aXWhRU_SRViWiRQ = 1) iQ aQ SaSeU (4 PaUkV) aXWhoU_fname, aXWhoU_lname(AUTHOR) – ( aXWhoU_fname, aXWhoU_lname( AUTHOR ( aXWhoU_id ( aXWhoU_poViWion = 1 (AUTHOR_PAPER))) ) ) OR ANSWER1 = aXWhoU_id ( aXWhoU_poViWion = 1 (AUTHOR_PAPER)) ANSWER2 = AUTHOR ANSWER1 ANSWER3 = aXWhoU_fname, aXWhoU_lname(ANSWER2) ANSWER4 = aXWhoU_fname, aXWhoU_lname(AUTHOR) – ANSWER3 PaJe 4 Rf 21 PART B DaWabaVe DeVign [ToWal: 20 MaUkV] Q3 [20 maUkV] MRQaVh CRPSXWiQg SWXdeQWV SRcieW (MCSS) iV RQe Rf Whe VWXdeQW cOXbV aW MRQaVh UQiYeUViW. SWXdeQWV aUe ZeOcRPe WR jRiQ aV a PePbeU. WheQ a VWXdeQW jRiQV MCSS, a PePbeU id iV aVVigQed, aQd Whe VWXdeQWV fiUVW QaPe, OaVW QaPe, daWe Rf biUWh, ePaiO aQd ShRQe QXPbeU ZiOO be UecRUded. ThiV cOXb haV aQ aQQXaO PePbeUVhiS fee. WheQ a PePbeU haV Said Whe PePbeUVhiS fee fRU Whe cXUUeQW eaU, Whe cXUUeQW eaU iV UecRUded agaiQVW Whe eaU Rf PePbeUVhiS aV SaUW Rf WheiU PePbeUVhiS deWaiOV. MCSS hRVWV VeYeUaO eYeQWV WhURXghRXW Whe eaU. The eYeQWV aUe cXUUeQWO caWegRUiVed iQWR Professional EYents, General EYents, aQd Social EYents. MCSS ZRXOd Oike WR be abOe WR add fXUWheU caWegRUieV aV Whe deYeORS QeZ eYeQWV. WheQ aQ eYeQW iV VchedXOed, MCSS aVVigQV aQ eYeQW id WR Whe eYeQW. The eYeQW daWe aQd WiPe, deVcUiSWiRQ, ORcaWiRQ, aOORcaWed bXdgeW, Whe WickeW SUice aQd Whe diVcRXQW UaWe (eg 5%) fRU PePbeUV. SRPe eYeQWV aUe RUgaQiVed aV fUee eYeQWV fRU PePbeUV. IQ WhiV ViWXaWiRQ, Whe diVcRXQW UaWe iV UecRUded aV 100% fRU PePbeUV. FRU aOO eYeQWV, RQO PePbeUV caQ SXUchaVe Whe WickeWV. HRZeYeU, PePbeUV caQ bX addiWiRQaO WickeWV fRU WheiU fUieQdV RU faPiO aW fXOO SUice. FRU each Rf Whe VaOeV, Whe UeceiSW QXPbeU, QXPbeU Rf WickeWV VROd, WRWaO aPRXQW Said aQd Whe PePbeU id aUe UecRUded. SRPe eYeQWV aWWUacW VRPe VSRQVRUVhiSV. The VSRQVRU Pa be aQ RUgaQiVaWiRQ RU aQ iQdiYidXaO. The VSRQVRUV SURYide fiQaQciaO VXSSRUW WR Whe eYeQW. SRPe eYeQWV Pa haYe VeYeUaO VSRQVRUV. The aPRXQW Rf fiQaQciaO VXSSRUW SURYided b each VSRQVRU iV UecRUded fRU Whe eYeQW. Each VSRQVRU iV ideQWified b a VSRQVRU id. The QaPe, cRQWacW ePaiO aQd VSRQVRU WSe aUe aOVR UecRUded. A VSRQVRU Pa VXSSRUW VeYeUaO eYeQWV WhURXghRXW Whe eaU. FRU VRPe eYeQWV VXch aV caUeeU QighW, MCSS Pa aOVR iQYiWe VRPe gXeVW VSeakeUV WR VhaUe WheiU e[SeUieQce. The daWabaVe UecRUdV aOO gXeVWV iQfRUPaWiRQ, Whe gXeVWV fXOO QaPe, ePaiO aQd ShRQe QXPbeU aUe UecRUded. If a gXeVW cRPeV fURP aQ RUgaQiVaWiRQ RU aQ iQdiYidXaO WhaW SURYideV a VSRQVRUVhiS WR aQ Rf Whe MCSS eYeQWV (dReV QRW haYe WR be aW Whe eYeQW ZheUe Whe gXeVW VSeakV), WhiV facW ZiOO aOVR be UecRUded. A gXeVW Pa be iQYiWed WR VeYeUaO eYeQWV. CUeaWe a logical leYel diagram Xsing CroZ s foot notations WR UeSUeVeQW Whe “MRQaVh CRPSXWiQg SWXdeQWV SRcieW” daWa UeTXiUePeQWV deVcUibed abRYe. COeaUO VWaWe aQ aVVXPSWiRQV RX Pake ZheQ cUeaWiQg Whe PRdeO. POeaVe QRWe Whe fROORZiQg SRiQWV: Be VXUe WR iQcOXde aOO UeOaWiRQV