程序代写案例-FIT2094
时间：2021-08-09
MonaVh UniYeUViW\
FIT2094 - DaWabaVeV
MOCK SCHEDULED FINAL ASSESSMENT
SAMPLE SOLUTIONS
AXWhRU: FIT DaWabaVe TeachiQg TeaP
LiceQVe: CRS\UighW ? MRQaVh UQiYeUViW\, XQOeVV RWheUZiVe VWaWed. AOO RighWV ReVeUYed.
COPYRIGHT WARNING
Warning
ThiV PaWeUiaO iV SURWecWed b\ cRS\UighW. 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 ePSOR\eeV: ePSOR\ee ID,
deSaUWPeQW QXPbeU, QaPe, hRPe addUeVV, edXcaWiRQ TXaOificaWiRQV aQd VkiOOV Zhich Whe ePSOR\ee
haV.
A VPaOO VaPSOe Rf daWa iV VhRZ beORZ:
Emplo\ee
ID
DepaUWmenW
NXmbeU
Emplo\ee
Name
Home
AddUeVV
QXalificaWion Skill
101 21 GiYeQ QaPe:
JRe
FaPiO\ QaPe:
BORggV
SWUeeW: 12
Wide Rd
TRZQ: M\WRZQ
PRVWcRde:
1234
BacheORU Rf
CRPPeUce
MBA
PURjecW
MaQagePeQW
HadRRS
R
102 13 GiYeQ QaPe:
WeQd\
FaPiO\ QaPe:
XiX
SWUeeW: 55
NaUURZ SW
TRZQ: M\WRZQ
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: M\WRZQ
PRVWcRde:
1234
CeUWificaWe IV iQ
BXViQeVV
AdPiQiVWUaWiRQ
SQL
JaYa
Ph\WRQ
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. emplo\eeid, depaUWmenW nXmbeU
CompoViWe - an aWWUibXWe Zhich can be VXbdiYided inWo addiWional aWWUibXWeV eg. emplo\ee
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 V\mbolV:
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 ANSWER1 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
W\Se 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, aWWUibXWeV aQd UeOaWiRQVhiSV (XQQeceVVaU\ UeOaWiRQVhiSV
PXVW QRW be iQcOXded)
? IdeQWif\ cOeaUO\ Whe PUiPaU\ Ke\V (P) aQd FRUeigQ Ke\V (F), aV SaUW Rf \RXU deVigQ
? IQ bXiOdiQg \RXU PRdeO \RX PXVW cRQfRUP WR FIT2094 PRdeOOiQg UeTXiUePeQWV
? The fROORZiQg aUe NOT UeTXiUed RQ \RXU diagUaP
? YeUbV/QaPeV RQ UeOaWiRQVhiS OiQeV
? iQdicaWRUV (*) WR VhRZ if aQ aWWUibXWe iV UeTXiUed RU QRW
? daWa W\SeV fRU Whe aWWUibXWeV
NOTE: ThiV TXeVWiRQ haV beeQ deVigQed VXch WhaW Whe PRdeO ZiOO fiW RQ a ViQgOe A4 Sage. YRX
aUe aOORZed WR XVe WZR bOaQk ZRUkVheeWV WR dUafW \RXU PRdeO aQd WheQ VXbPiW \RXU fiQaO
UeVSRQVe RQ ONE Sage.
PaJe 5 Rf 21
MonaVh CompXWing SWXdenWV SocieW\ (MCSS) Logical Model
PaJe 6 Rf 21
PART C NoUmaliVaWion [ToWal: 10 MaUkV]
Q3 [10 maUkV]
The SXSeU EOecWURQicV IQYRice VhRZQ beORZ diVSOa\V Whe deWaiOV Rf aQ iQYRice fRU Whe cOieQW AOice
PaXO.
SXpeU ElecWUonicV
INVOICE
ClienW NXmbeU: C3178713 InYoice No.: 132
ClienW Name: AOice PaXO InYoice DaWe: 02/11/2018
ClienW AddUeVV: 43 High SWUeeW,
CaXOfieOd, VIC 3162
ClienW Phone: 0411 245 718
IWemID IWem Name PXUchaVe
PUice
E[pecWed
DeliYeU\ DaWe
QXanWiW\ CoVW
316772 SRQiT S55UV16B 55" 499.00 2 ZeekV 1 499.00
452550 MicURVRfW SXUface PUR 1198.00 1-3 ZeekV 1 1198.00
483041 DeORQghi DigiWaO CRffee 299.00 SaPe Da\ 2 598.00
SUB TOTAL: $ 2295.00
DELIVERY: $145.00
ORDER TOTAL: $2440.00
ReSUeVeQW WhiV fRUP iQ UNF. IQ cUeaWiQg \RXU UeSUeVeQWaWiRQ \RX VhRXOd QRWe WhaW SXSeU EOecWURQicV
ZiVh WR WUeaW Whe cOieQW QaPe aQd addUeVV aV ViPSOe aWWUibXWeV. CRQYeUW \RXU UNF WR fiUVW QRUPaO
fRUP (1NF) aQd WheQ cRQWiQXe Whe QRUPaOiVaWiRQ WR WhiUd QRUPaO fRUP (3NF). AW each QRUPaO fRUP
VhRZ Whe aSSURSUiaWe deSeQdeQcieV fRU WhaW QRUPaO fRUP, if WheUe aUe QRQe ZUiWe "NR
DeSeQdeQcieV"
Do noW add neZ aWWUibXWeV dXUing Whe noUmaliVaWion. COeaUO\ ZUiWe Whe UeOaWiRQV iQ each VWeS
fURP Whe XQQRUPaOiVed fRUP (UNF) WR Whe WhiUd QRUPaO fRUP (3NF). COeaUO\, iQdicaWe SUiPaU\ ke\V RQ
aOO UeOaWiRQV fURP 1NF RQZaUdV.
[10 maUkV]
PaJe 7 Rf 21
UNF
INVOICE (inYoice_nbU, inY_daWe, clienW_nXmbeU, clienW_name, clienW_addUeVV, clienW_phone,
(iWem_id, iWem_name, iWem_pXUchaVe_pUice, iWem_deliYeU\_Wime, TW\_oUdeUed, line_coVW)
VXb_WoWal, deliYeU\_fee, oUdeU_WoWal)
1NF
INVOICE (inYoice_nbU, inY_daWe, clienW_nXmbeU, clienW_name, clienW_addUeVV, clienW_phone,
VXb_WoWal, deliYeU\_fee, oUdeU_WoWal)
INVOICE_LINE (inYoice_nbU, iWem_id, iWem_name, iWem_pXUchaVe_pUice, iWem_deliYeU\_Wime,
TW\_oUdeUed, line_coVW)
PaUWial DependencieV:
iWem_id -> iWem_name
2NF
INVOICE (inYoice_nbU, inY_daWe, clienW_nXmbeU, clienW_name, clienW_addUeVV, clienW_phone,
VXb_WoWal, deliYeU\_fee, oUdeU_WoWal)
INVOICE_LINE (inYoice_nbU, iWem_id, iWem_pXUchaVe_pUice, iWem_deliYeU\_Wime,
TW\_oUdeUed, line_coVW)
ITEM (iWem_id, iWem_name)
TUanViWiYe DependencieV:
clienW_nXmbeU -> clienW_name, clienW_addUeVV, clienW_phone
3NF
INVOICE (inYoice_nbU, inY_daWe, clienW_nXmbeU, VXb_WoWal, deliYeU\_fee, oUdeU_WoWal)
CLIENT (clienW_nXmbeU, clienW_name, clienW_addUeVV, clienW_phone)
INVOICE_LINE (inYoice_nbU, iWem_id, iWem_pXUchaVe_pUice, iWem_deliYeU\_Wime,
TW\_oUdeUed, line_coVW)
ITEM (iWem_id, iWem_name)
FXll DependencieV:
inYoice_nbU -> inY_daWe, clienW_nXmbeU, VXb_WoWal, deliYeU\_fee, WoWal_coVW
PaJe Rf 21
clienW_nXmbeU -> clienW_name, clienW_addUeVV, clienW_phone
inYoice_nbU, iWem_id -> iWem_pXUchaVe_pUice, iWem_deliYeU\_Wime, TW\_oUdeUed, line_coVW
iWem_id -> iWem_name
PaJe Rf 21
PART D SQL [ToWal: 40 MaUkV]
Emplo\ee S\VWem Model and Schema File foU PaUW D
The fROORZiQg UeOaWiRQaO PRdeO deSicWV aQ ePSOR\ee V\VWeP:
GiYeQ WhiV PRdeO aQd aVVXPiQg Whe WabOeV haYe beeQ cUeaWed aQd SRSXOaWed iQ aQ OUacOe
daWabaVe, SURYide Whe SQL VWaWePeQWV fRU Whe fROORZiQg TXeVWiRQV iQ PaUW D.
NRWe iQ cRdiQg \RXU SQL each SELECT, FROM, WHERE, GROUP BY, HAVING aQd ORDER BY
cOaXVe mXVW VWaUW on a neZ line.
The VchePa fiOe WR cUeaWe WheVe WabOeV iV:
The VchePa fiOe WR cUeaWe WheVe WabOeV iV:
CREATE TABLE SALGRADE (
salgrade NUMBER(2) NOT NULL ,
sallower NUMBER(6,2) NOT NULL ,
salupper NUMBER(6,2) NOT NULL ,
salbonus NUMBER(6,2) NOT NULL ,
PaJe 10 Rf 21
CONSTRAINT salgrade_pk PRIMARY KEY (salgrade),
CONSTRAINT salgrade_chk1 CHECK (sallower >= 0),
CONSTRAINT salgrade_chk2 CHECK (sallower <= salupper));
COMMENT ON COLUMN salgrade.salgrade IS 'Salary Grade';
COMMENT ON COLUMN salgrade.sallower IS 'Salary Lower Limit';
COMMENT ON COLUMN salgrade.salupper IS 'Salary Upper Limit';
COMMENT ON COLUMN salgrade.salbonus IS 'Salary Bonus';
CREATE TABLE course (
crscode VARCHAR(6) NOT NULL ,
crsdesc VARCHAR(30) NOT NULL ,
crscategory CHAR(3) NOT NULL ,
crsduration NUMBER(2) NOT NULL ,
CONSTRAINT course_pk PRIMARY KEY (crscode),
CONSTRAINT course_chk1 CHECK (crscode = upper(crscode)),
CONSTRAINT course_chk2 CHECK (crscategory in ('GEN','BLD','DSG')));
COMMENT ON COLUMN course.crscode IS 'Course Code';
COMMENT ON COLUMN course.crsdesc IS 'Course Description';
COMMENT ON COLUMN course.crscategory IS 'Course Category';
COMMENT ON COLUMN course.crsduration IS 'Course Duration';
CREATE TABLE DEPARTMENT (
deptno NUMBER(2) NOT NULL ,
deptname VARCHAR(10) NOT NULL ,
deptlocation VARCHAR(8) NOT NULL ,
empno NUMBER(4) ,
CONSTRAINT department_pk PRIMARY KEY (deptno),
CONSTRAINT department_un UNIQUE (deptname),
CONSTRAINT department_chk1 CHECK (deptname = upper(deptname)),
CONSTRAINT department_chk2 CHECK (deptlocation = upper(deptlocation)));
PaJe 11 Rf 21
COMMENT ON COLUMN department.deptno IS 'Department Number';
COMMENT ON COLUMN department.deptname IS 'Department Name';
COMMENT ON COLUMN department.deptlocation IS 'Location of department';
COMMENT ON COLUMN department.empno IS 'Employee who manages department';
CREATE TABLE EMPLOYEE (
empno NUMBER(4) NOT NULL ,
empname VARCHAR(8) NOT NULL ,
empinit VARCHAR(5) NOT NULL ,
empjob VARCHAR(8) ,
empbdate DATE NOT NULL ,
empmsal NUMBER(6,2) NOT NULL ,
empcomm NUMBER(6,2) ,
deptno NUMBER(2) ,
mgrno NUMBER(4) ,
CONSTRAINT employee_pk PRIMARY KEY (empno),
CONSTRAINT employee_fk1 FOREIGN KEY (mgrno)
REFERENCES EMPLOYEE (empno),
CONSTRAINT employee_fk2 FOREIGN KEY (deptno)
REFERENCES DEPARTMENT (deptno));
COMMENT ON COLUMN employee.empno IS 'Employee number';
COMMENT ON COLUMN employee.empname IS 'Employee name';
COMMENT ON COLUMN employee.empinit IS 'Employee initials';
COMMENT ON COLUMN employee.empjob IS 'Employee job';
COMMENT ON COLUMN employee.empbdate IS 'Employee birthdate';
COMMENT ON COLUMN employee.empmsal IS 'Employee monthly salary';
COMMENT ON COLUMN employee.empcomm IS 'Employee commission';
COMMENT ON COLUMN employee.deptno IS 'Department Number';
COMMENT ON COLUMN employee.mgrno IS 'Employees manager (empno of manager)';
PaJe 12 Rf 21
ALTER TABLE DEPARTMENT
ADD (CONSTRAINT department_fk FOREIGN KEY (empno)
REFERENCES employee (empno));
CREATE TABLE HISTORY (
empno NUMBER(4) NOT NULL ,
histbegindate DATE NOT NULL ,
histbeginyear NUMBER(4) NOT NULL ,
histenddate DATE ,
histmsal NUMBER(6,2) NOT NULL ,
histcomments VARCHAR(60) ,
deptno NUMBER(2) NOT NULL ,
CONSTRAINT history_pk PRIMARY KEY (empno, histbegindate),
CONSTRAINT history_chk CHECK (histbegindate < histenddate),
CONSTRAINT history_fk1 FOREIGN KEY (empno)
REFERENCES EMPLOYEE (empno)
ON DELETE CASCADE,
CONSTRAINT history_fk2 FOREIGN KEY (deptno)
REFERENCES DEPARTMENT (deptno));
COMMENT ON COLUMN history.deptno IS 'Department Number';
COMMENT ON COLUMN history.histbegindate IS 'Date history record begins';
COMMENT ON COLUMN history.histbeginyear IS 'Year history record begins';
COMMENT ON COLUMN history.histenddate IS 'Date history record ends';
COMMENT ON COLUMN history.histmsal IS 'Monthly Salary for this history
record';
COMMENT ON COLUMN history.histcomments IS 'Comments for this history record';
COMMENT ON COLUMN history.empno IS 'Employee number';
CREATE TABLE OFFERING (
offbegindate DATE NOT NULL ,
crscode VARCHAR(6) NOT NULL ,
PaJe 13 Rf 21
offlocation VARCHAR(8) ,
empno NUMBER(4) ,
CONSTRAINT offering_pk PRIMARY KEY (offbegindate, crscode),
CONSTRAINT offering_fk1 FOREIGN KEY (crscode)
REFERENCES course(crscode),
CONSTRAINT offering_fk2 FOREIGN KEY (empno)
REFERENCES EMPLOYEE (empno));
COMMENT ON COLUMN offering.offbegindate IS 'Begin date for offering';
COMMENT ON COLUMN offering.crscode IS 'Course Code';
COMMENT ON COLUMN offering.offlocation IS 'Location for offering';
COMMENT ON COLUMN offering.empno IS 'Employee number for employee running
offering';
CREATE TABLE REGISTRATION (
offbegindate DATE NOT NULL ,
crscode VARCHAR(6) NOT NULL ,
empno NUMBER(4) NOT NULL,
regevaluation NUMBER(1) ,
CONSTRAINT registration_pk PRIMARY KEY (offbegindate, crscode, empno),
CONSTRAINT resgitration_chk CHECK (regevaluation in (1,2,3,4,5)),
CONSTRAINT registration_fk1 FOREIGN KEY (empno)
REFERENCES EMPLOYEE (empno),
CONSTRAINT registration_fk2 FOREIGN KEY (offbegindate, crscode)
REFERENCES OFFERING (offbegindate, crscode));
COMMENT ON COLUMN registration.offbegindate IS 'Begin date for offering';
COMMENT ON COLUMN registration.crscode IS 'Course Code';
COMMENT ON COLUMN registration.regevaluation IS 'Grade for course completed';
COMMENT ON COLUMN registration.empno IS 'Employee number of employee
completing course';
PaJe 14 Rf 21
Q5 [10 maUkV]
The cRPSaQ\ QeedV WR UecRUd a QeZ deSaUWPeQW. ThiV QeZ deSaUWPeQW'V QXPbeU ZiOO be 10 higheU
WhaQ Whe higheVW cXUUeQW deSaUWPeQW QXPbeU aQd ZiOO be caOOed EXAM aQd iV ORcaWed iQ BOSTON.
The ePSOR\ee QaPed KING ZhR haV a jRb aV Whe RQO\ cRPSaQ\ DIRECTOR haV beeQ aVVigQed WR
PaQage Whe QeZ EXAM deSaUWPeQW.
The cRPSaQ\ haV aOVR decided WhaW Whe\ ZiVh WR UecRUd, fRU each deSaUWPeQW, Whe QXPbeU Rf
ePSOR\eeV cXUUeQWO\ ZRUkiQg iQ Whe deSaUWPeQW (Whe ePSOR\ee cRXQW). FRU QeZ deSaUWPeQWV Whe
QXPbeU Rf ePSOR\eeV iQ Whe deSaUWPeQW VhRXOd be VeW WR 0. FRU WhRVe deSaUWPeQWV Zhich cXUUeQWO\
haYe ePSOR\eeV, Whe ePSOR\ee cRXQW VhRXOd cRUUecWO\ UefOecW Whe cXUUeQW QXPbeU Rf ePSOR\eeV iQ
Whe deSaUWPeQW.
CRde Whe SQL VWaWePeQWV WR PRdif\ Whe daWabaVe WR PeeW WheVe UeTXiUePeQWV.
INSERT INTO department VALUES (
(
SELECT
MAX(deptno)
FROM
department
) + 10,
'EXAM',
'BOSTON',
(
SELECT
empno
FROM
employee
WHERE
empname ó 'KING'
AND empjob ó 'DIRECTOR'
)
);
COMMIT;
ALTER TABLE department ADD deptcount NUMBER(3, 0) DEFAULT 0 NOT NULL;
UPDATE department d
SET
deptcount ó (
SELECT
COUNT(empno)
FROM
employee e
WHERE
e.deptno ó d.deptno
);
COMMIT;
PaJe 15 Rf 21
Q6 [18 maUkV]
(a) LiVW Whe ePSOR\ee QXPbeU, Whe ePSOR\ee QaPe, Whe ePSOR\ee jRb aQd Whe \eaUO\ VaOaU\ Rf aOO
ePSOR\eeV WhaW beORQg WR Whe μSaOeV? deSaUWPeQW. The QaPe Rf Whe ePSOR\ee PXVW be VhRZQ iQ a
cROXPQ caOOed 3EPSOR\ee NaPe′ aQd Whe \eaUO\ VaOaU\ PXVW be VhRZQ iQ Whe fRUP Rf $34,200 iQ a
cROXPQ caOOed 3YeaUO\ SaOaU\′. ShRZ Whe ePSOR\ee ZiWh Whe higheVW VaOaU\ fiUVW, if WZR ePSOR\eeV
haYe Whe VaPe VaOaU\, RUdeU iW baVed RQ Whe ePSOR\ee QXPbeU. (8 PaUkV)
SELECT
e.empno,
empname AS "Employee Name",
empjob,
to_char(empmsal * 12, '$99,990') AS "Yearly Salary"
FROM
employee e
JOIN department d
ON e.deptno ó d.deptno
WHERE
upper(deptname) ó upper('Sales')
ORDER BY
empmsal DESC,
e.empno;
(b) FRU each cRXUVe Zhich haV beeQ cRPSOeWed b\ aW OeaVW 5 ePSOR\eeV, OiVW Whe cRXUVe cRde, Whe
cRXUVe deVcUiSWiRQ aQd Whe cRXUVe dXUaWiRQ. The cRXUVe dXUaWiRQ PXVW be VhRZQ iQ a cROXPQ caOOed
3CRXUVe DXUaWiRQ′ aQd iQcOXde Whe ZRUd μda\V? (e.g. 4 da\V). OUdeU Whe RXWSXW b\ Whe cRXUVe cRde.
(10 PaUkV)
SELECT
c.crscode,
crsdesc,
crsduration || ' days' as "Course Duration"
FROM
registration r
JOIN course c
ON r.crscode ó c.crscode
WHERE
r.regevaluation IS NOT NULL
GROUP BY
c.crscode,
crsdesc,
crsduration
HAVING
COUNT(empno) ?ó 5
ORDER BY
c.crscode;
PaJe 16 Rf 21
Q7 [12 maUkV]
LiVW ALL ePSOR\eeV ZhRVe WRWaO cRXUVe UegiVWUaWiRQV aUe OeVV WhaQ Whe aYeUage QXPbeU Rf
UegiVWUaWiRQV fRU ePSOR\eeV ZhR haYe UegiVWeUed fRU a cRXUVe. NRWe WhaW VRPe ePSOR\eeV Pa\
UeSeaW a cRXUVe, WhiV UeSeaW dReV QRW cRXQW aV a diffeUeQW cRXUVe. IQ Whe OiVW, iQcOXde Whe ePSOR\ee
QXPbeU, QaPe, daWe Rf biUWh aQd Whe QXPbeU Rf diffeUeQW cRXUVeV Whe\ haYe UegiVWeUed fRU. OUdeU Whe
RXWSXW b\ ePSOR\ee QXPbeU.
SELECT
e.empno,
empname,
to_char(empbdate, 'dd-Mon-yyyy') AS dob,
COUNT(DISTINCT crscode) AS crscount
FROM
employee e
LEFT JOIN registration r ON e.empno ó r.empno
GROUP BY
e.empno,
empname,
to_char(empbdate, 'dd-Mon-yyyy')
HAVING
COUNT(DISTINCT crscode) ? (
SELECT
AVG(COUNT(DISTINCT crscode))
FROM
registration
GROUP BY
empno
)
ORDER BY
e.empno;
PaJe 17 Rf 21
PART E Web Technolog\ [ToWal: 10 MaUkV]
Q8 [6 maUkV]
UViQg Whe daWa PRdeO VhRZQ iQ PaUW D, Ze ZiVh WR deYeORS a ShS baVed Zeb Sage Zhich VhRZV aOO
deSaUWPeQWV, aQd WheiU PaQageU'V QaPe aQd PRQWhO\ VaOaU\ aV VhRZQ beORZ:
HeUe iV Whe iQcRPSOeWe PHP cRde fRU Whe Sage:
?table border ó? widthó?????
?tr?
?th widthó?????b?Department Number??b???th?
?th widthó?????b?Department Name??b???th?
?th widthó?????b?Department Location??b???th?
?th widthó?????b?Manager??b???th?
?th widthó?????b?Manager?s MonthlT SalarT??b???th?
??tr?
??php
′querT ó complete this part??
′stmt ó oci-parse?′conn?′querT??
if ??′stmt? ￡
′e ó oci-error?′conn??
print ˉError on parse of statement??br?ˉ ?
print ′e??message?￠ ?
eSit?
¤
oci-define-bT-name?′stmt?ˉDNOˉ?′dno??
oci-define-bT-name?′stmt?ˉDNAMEˉ?′dname??
oci-define-bT-name?′stmt?ˉDLOCˉ?′dloc??
oci-define-bT-name?′stmt?ˉMGRNAMEˉ?′mgrname??
oci-define-bT-name?′stmt?ˉMGRMSALˉ?′mgrmsal??
′r ó oci-eSecute?′stmt??
if ??′r? ￡
′e ó oci-error?′stmt??
print ˉError eSecute of statement??br?ˉ ?
print ′e??message?￠ ?
eSit?
¤
while ?oci-fetch?′stmt?? ￡
print?ˉ
?tr?
?td widthó????′dno??td?
?td widthó????′dname??td?
?td widthó????′dloc??td?
?td widthó????′mgrname??td?
?td widthó????′mgrmsal??td?
??tr?ˉ??
¤
print ?ˉ??table?ˉ??
PaJe 1 Rf 21
WUiWe Whe PiVViQg $TXeU\ VWaWePeQW fRU Whe PHP cRde abRYe iQ Whe aQVZeU VSace beORZ:
"SELECT
d.deptno AS dno,
deptname AS dname,
deptlocation AS dloc,
empinit
|| ' '
|| empname AS mgrname,
to_char(empmsal, '$9990.99') AS mgrmsal
FROM
department d
JOIN employee e
ON e.empno ó d.empno
ORDER BY
dno"
Q9 [4 maUkV]
SceQaUiR: \RXU WeaP?V cOieQW ZaQWV a daWabaVe aSS iPSOePeQWed iQ MicURVRfW .NET ± fRU OiceQViQg
UeaVRQV, Whe cOieQW Vaid WhaW \RX CANNOT XVe JaYa.
YRXU cROOeagXe BUXce WheQ SURSRVeV Whe XVe Rf JDBC fRU OUacOe cRQQecWiYiW\, aV he WhiQkV iW iV YeU\
VXiWabOe fRU Whe cOieQW.
DR \RX agUee RU diVagUee ZiWh BUXce? PURYide a fXOO e[SOaQaWiRQ aQd jXVWificaWiRQ.
(SiPSO\ Va\iQg μagUee?/?diVagUee? ZiWhRXW a fXOO e[SOaQaWiRQ = QR PaUkV).
<>
I diVagUee ZiWh BUXce. UVing JDBC Wo connecW Wo OUacle fUom a .NET applicaWion iV noW
appUopUiaWe Vince JDBC cannoW be XVed diUecWl\ in a .NET applicaWion. The applicaWion
needV JDBC-ODBC bUidge Zhich ma\ caXVe Vome peUfoUmance iVVXeV and Vome poVVible
dUiYeU-UelaWed pUoblemV. InVWead of XVing JDBC Whe Weam VhoXld XVe ODBC DUiYeU foU
OUacle.
PART F TUanVacWion [ToWal: 10 MaUkV]
Q10. [ 5 maUkV]
GiYeQ WZR WUaQVacWiRQV:
T1 ± R(X), W(X)
T2 ± R(Y), W(Y), R(X), W (X)
WheUe R(X) PeaQV Read(X) aQd W(X) PeaQV WUiWe(X).
PaJe 1 Rf 21
(a) If Ze ZiVh WR cRPSOeWe bRWh Rf WheVe WUaQVacWiRQV, e[SOaiQ Whe diffeUeQce beWZeeQ a serial
aQd non-serial RUdeUiQg Rf WheVe WZR WUaQVacWiRQV. PURYide aQ e[aPSOe Rf each aV SaUW Rf
\RXU aQVZeU.
(b) WhaW WUaQVacWiRQ ACID SURSeUW\ dReV a QRQ-VeUiaO RUdeUiQg Rf WheVe WZR WUaQVacWiRQV
SRWeQWiaOO\ YiROaWe.
(a)
SeUial ± all of one WUanVacWion folloZed b\ all of Whe oWheU
T1 R(X), T1 W(X), T2 R(Y), T2 W(Y), T2 R(X), T2 W(X)
Non-SeUial ± inWeUleaYing of Whe WUanVacWionV
T1 R(X), T2 R(Y), T2 W(Y), T1 W(X), T2 R(X), T2 W(X)
(b)
IVolaWion oU ConViVWenc\
PaJe 20 Rf 21
Q11 [5 maUkV]
A Zrite through daWabaVe haV fiYe WUaQVacWiRQV UXQQiQg aV OiVWed beORZ (Whe WiPe iV VhRZQ
hRUi]RQWaOO\ fURP OefW WR UighW):
AW WiPe tc a checkSRiQW iV WakeQ, aW WiPe tf Whe daWabaVe faiOV dXe WR a SRZeU RXWage.
E[SOaiQ fRU each WUaQVacWiRQ ZhaW UecRYeU\ RSeUaWiRQV ZiOO be Qeeded ZheQ Whe daWabaVe iV
UeVWaUWed aQd Zh\.
T1 ± noWhing UeTXiUed, commiWWed befoUe checkpoinW
T2 ± ROLL FORWARD, commiWWed afWeU checkpoinW and befoUe fail
T3 ± ROLL BACK, neYeU Ueached commiW
T4 ± ROLL FORWARD, VWaUWed afWeU checkpoinW commiWWed befoUe fail
T5 - ROLL BACK, neYeU Ueached commiW
PaJe 21 Rf 21
