DPIT115 Data Management and Security Final Assessment (Assignment)

DPIT115 Postulates Management and Security Developed Impost (Assignment 2)
Page 1 of 9
Diploma of Counsel Technology
DPIT115 Postulates Management and Security
Developed Impost (Assignment 2)
Autumn Session 2020
This exam represents 40% of the aggregate question signs
_______________________________________________________________________________
Due Continuance: 7 June 2020, 11:55 PM
________________________________________________________________________________________
DIRECTIONS TO STUDENTS
1. Entirety questions are to be attempted
2. Aggregate compute of questions: 7 (SEVEN)
3. This is an identical impost
4. Entirety questions are to be retorted by you outside countenance from other people
5. This impost insist-upons you to refer solely undisjoined Microsoft Word muniment
6. Do referable grasp the questions in your retort
7. Your dependence achieve be checked coercion matchlessness using Turnitin
8. Manifestation your hold tone, furnish your hold examples and attract your hold diagrams.
9. Show your discontinuance as well-behaved-behaved as the trudges fascinated to retort the questions
10. Refer your discontinuance to Moodle by the due continuance.
If it is reputed that you entertain similarityed or prevalent subjoined countenance from another special, the
matter achieve be investigated as an entiretyeged rupture of the UOW College Academic Integrity and Ward
Conduct Policy, in accordance with the Procedure coercion Managing Entiretyeged Ward Misconduct.
Please referablee, as distribute of this inquiry, you may be insist-upond to trial an traditional criterion to
verify your interpretation of the impost gratified.

DPIT115 Postulates Management and Security Developed Impost (Assignment 2)
Page 2 of 9
QUESTIONS 3, 4, 5, 6 and 7 REFER TO THE RELATIONAL TABLES
LISTED BELOW
CREATE TABLE EMPLOYEE(
ENUM DECIMAL(12) NOT NULL, /* Employee compute */
FNAME VARCHAR(50) NOT NULL, /* Principal planate */
LNAME VARCHAR(50) NOT NULL, /* Ultimate planate */
DOB DATE NULL, /* Continuance of rise */
CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(ENUM) );
CREATE TABLE DRIVER(
ENUM DECIMAL(12) NOT NULL, /* Employee compute */
LNUM DECIMAL(8) NOT NULL, /* Driving indulge compute */
STATUS VARCHAR(10) NOT NULL, /* Driver standing */
CONSTRAINT DRIVER_PKEY PRIMARY KEY(ENUM),
CONSTRAINT DRIVER_UNIQUE UNIQUE(LNUM),
CONSTRAINT DRIVER_FKEY FOREIGN KEY(ENUM) REFERENCES EMPLOYEE(ENUM),
CONSTRAINT DRIVER_STATUS CHECK (
STATUS IN (‘AVAILABLE’, ‘BUSY’, ‘ON LEAVE’)) );
CREATE TABLE TRUCK(
REGNUM VARCHAR(10) NOT NULL, /* Registration compute */
CAPACITY DECIMAL(7) NOT NULL, /* Parts */
WEIGHT DECIMAL(7) NOT NULL, /* Weight */
STATUS VARCHAR(10) NOT NULL, /* Present standing */
CONSTRAINT TRUCK_PKEY PRIMARY KEY(REGNUM),
CONSTRAINT TRUCK_STATUS CHECK
( STATUS IN (‘AVAILABLE’, ‘USED’, ‘MAINTAINED’)),
CONSTRAINT TRUCK_WEIGHT CHECK
( WEIGHT > 0.0 AND WEIGHT < 500 ), CONSTRAINT TRUCK_CAPACITY CHECK ( CAPACITY > 0.0 AND CAPACITY < 100 ) ); CREATE TABLE TRIP( TNUM DECIMAL(10) NOT NULL, /* Mistake compute */ LNUM DECIMAL(8) NOT NULL, /* Driving indulge compute */ REGNUM VARCHAR(10) NOT NULL, /* Deal registration compute */ TDATE DATE NOT NULL, /* Mistake continuance */ CONSTRAINT TRIP_PKEY PRIMARY KEY (TNUM), CONSTRAINT TRIP_CKEY UNIQUE (LNUM, REGNUM, TDATE), CONSTRAINT TRIP_FKEY1 FOREIGN KEY (LNUM) REFERENCES DRIVER(LNUM), CONSTRAINT TRIP_FKEY2 FOREIGN KEY (REGNUM) REFERENCES TRUCK(REGNUM) ); CREATE TABLE TRIPLEG( TNUM DECIMAL(10) NOT NULL, /* Mistake compute */ LEGNUM DECIMAL(2) NOT NULL, /* Leg compute */ DEPARTURE VARCHAR(30) NOT NULL, /* Non-appearance city */ DESTINATION VARCHAR(30) NOT NULL, /* Application city */ CONSTRAINT TRIPLEG_PKEY PRIMARY KEY (TNUM, LEGNUM), CONSTRAINT TRIPLEG_UNIQUE UNIQUE(TNUM, DEPARTURE, DESTINATION), CONSTRAINT TRIPLEG_FKEY1 FOREIGN KEY (TNUM) REFERENCES TRIP(TNUM) ); DPIT115 Postulates Management and Security Developed Impost (Assignment 2) Page 3 of 9 QUESTION 1 (10 signs) Interpret and analyse the behindcited mention of a illustration postulatesbase estate. A University would affect to educe a postulatesbase to chronicles counsel encircling some of its activities. The university offers a compute of measures to wards. A measure is pictorial by a matchless planate, the aggregate compute of security points insist-upond to entire a measure, and different fitnesss that must be amiable by the restraintthcoming wards. The university offers three types of measures: underdisequalize measures, postgraduate degrees, and disequalize certificate. Postdisequalize measures are advantageous solely coercion the wards who entertain already entired an underdisequalize measure. A style of a postdisequalize measure grasps a inventory of accepconsultation underdisequalize measures. A style of a disequalize certificate grasps a fitness on the aggregate compute of years of professional trial. Each measure depends of an ordered continuity of questions. A style of a question depends of its compute in a continuity and matchless command, matchless epithet, aggregate compute of securitys points a question is price, and a inventory of learning motiveives. The university employs academic staff parts, tutors and help staff parts. A contemptible style of a university employee depends of a matchless employee compute, principal planate ultimate planate and continuance of rise. Principal planate, ultimate planate and continuance of rise matchlessly authorized each employee. Subjoinedly, academic staff members and tutors are pictorial by an academic measure achieved. Help staff parts are pictorial by a inventory of insist-uponments assumed in the gone-by contemporaneously with a continuance when each insist-uponment has been acquired. The university assigns the academic staff parts and tutors to the questions. A question has undisjoined or two academic parts assigned and a compute of tutors. Academic staff parts and tutors can be assigned to sundry questions. Help staff parts are assigned to the measures. Each help staff part is assigned to undisjoined measure, and a measure has undisjoined or over help staff parts assigned. The university is disjoined into faculties and faculties are disjoined into initiates. Academic staff parts and tutors suit to undisjoined initiate, and each initiate depends of sundry academic staff parts and tutors. The faculties and initiates are pictorial by the matchless planates. The university chronicless counsel when the academic staff parts and tutors principal subjoin the initiates. The university as-well frequents the counsel encircling the coercionmer employees who worked at the university in the gone-by. A style of a coercionmer employee is the same as a prevalent employee, and subjoinedly, it grasps a employ continuance and purpose of employment continuance. (1) Attract a conceptual schema coercion the mention of a postulatesbase estate inventoryed aloft. Manifestation the notation of UML simplified rank diagrams explained to you during this question. Referablee: you are referable known to manifestation any contrived identifiers and any characteristics that are referable mentioned in the mention. Manifestation UMLet to attract the schema and gone-bye images of your attractings into your Microsoft Word document. Subjoin your planate, ward compute and the continuance to your diagram. There is NO NEED to furnish a elaborate separation of how a conceptual schema has been educed. The developed conceptual schema developed in the UML simplified referableation rankes is question is qualified. (7 signs) Subjoin two (2) odd motive rankes with at developed three (3) characteristics each and misspend entiretyiances. The select of motive rankes, characteristics and entiretyiances are up to you; besides, these should relate to the true scenario. Manifestation UMLet to attract the changes to the schema and gone-bye the remedy diagram into your Microsoft Word muniment. Write a extract style that explains the subjoined motives, characteristics and associations underneath the diagram. (3 signs) DPIT115 Postulates Management and Security Developed Impost (Assignment 2) Page 4 of 9 QUESTION 2 (10 signs) Consider the conceptual schema abandoned underneath. Your toil is to achieve the trudges of argumentative postulatesbase plan, i.e. to transfigure a conceptual schema abandoned aloft into a gathering of themeive schemas. Precedently transfigureing subjoin the characteristic ‘date-made’ to the BUS rank and ‘home-city’ to the DRIVER rank. Attract the resulting conceptual schema subjoining your planate, ward compute and the continuance to the attracting. Manifestation UMLet and gone-bye images of your attractings into your Microsoft Word muniment. Coercion each themeive schema evidently inventory the planates of characteristics, earliest clew, candicontinuance clews (if any), and outlandish clews (if any). Assume that an entiretyiance mode must be manifestationd to tool the generalization. Show your agoing as you trudge through the mode of the transfigureation. DPIT115 Postulates Management and Security Developed Impost (Assignment 2) Page 5 of 9 QUESTION 3 (8 signs) Write the postulates determination declarations of SQL that change the compositions of a postulatesbase inventoryed on page 2 of this impost in the method pictorial underneath. Note, that some of the alteration may insist-upon over than undisjoined postulates determination declarations of SQL statement. (1) Change the harmony toil of the illustration postulatesbase such that behind the alteration, it is potential to chronicles in the postulatesbase counsel encircling the deals that entertain a parts up to and including 220. (2 signs) (2) Change the composition and harmony toil of the illustration postulatesbase such that behind the modification, it is potential to shop counsel in the postulatesbase encircling the aggregate compute of legs a mistake consists of. Assume that a mistake canreferable depend of over than 20 legs. (2 signs) (3) Change the composition and harmony toils of the illustration postulatesbase such it is potential to shop counsel in the postulatesbase encircling the craftsmans assiduous by a manner aggregation. Assume that a style of craftsman depends of an employee compute, principal planate, ultimate planate, continuance of rise and insist-uponment flatten. A insist-uponment flatten is a unequivocal integer compute 1 or 2 or 3 or 4 or 5. Repart that a craftsman is an employee. (2 signs) (4) Change the harmony toils of the illustration postulatesbase so it is potential to shop counsel encircling the mistake outside providing counsel encircling a driver licence compute. Such alteration is required when a driver libertys a manner aggregation, and we would affect to frequent counsel encircling entirety mistakes achieveed by the driver. (2 signs) DPIT115 Postulates Management and Security Developed Impost (Assignment 2) Page 6 of 9 QUESTION 4 (8 signs) Write the postulates construction declarations of SQL that change the gratifieds of a postulatesbase inventoryed on page 2 of this lab toil in the methods pictorial underneath. Still n ess that you are referable entiretyowed to change and/or to ooze any harmony toils. As-well referablee, that to tool some of the alterations inventoryed underneath, you may demand over than undisjoined postulates construction declaration of SQL. (1) Assume that the largest compute of a mistake is 1200 (the largest estimate in a support TNUM in a themeive consultation TRIP). Assume that a odd mistake has been entired today. The mistake was from Sydney to Dubbo and then from Dubbo end to Sydney. The mistake has been achieveed by a driver with a licence compute 008 (support LNUM in a themeive consultation TRIP) who manifestationd a deal with a registration compute AL08UK. Infuse misspend counsel into a illustration postulatesbase. (2 signs) (2) Delete the counsel from the postulatesbase encircling mistake compute 60. Remember, that the coercioneign clews in entirety CREATE TABLE declarations entertain no ON DELETE CASCADE chapter. (2 signs) (3) Change the standing of entirety drivers who achieveed over than 106 mistakes to a estimate ON LEAVE. (2 signs) (4) Copy counsel encircling entirety employees born precedently the year 2000 to a odd consultation E20CENT. There is no demand to strain any harmony toils on the odd consultation. (2 signs) DPIT115 Postulates Management and Security Developed Impost (Assignment 2) Page 7 of 9 QUESTION 5 (10 signs) Write SELECT declarations that tool the behindcited queries. (1) Find the principal and the ultimate planates of entirety drivers who are referable on liberty. (2 signs) (2) Find the registration computes of entirety deals that entertain referable been manifestationd so remote. (2 signs) (3) Find the registration computes of entirety deals that entertain been manifestationd over than 200 times. (2 signs) (4) Find the unlike planates of entirety cities visited during a mistake compute 7 or a mistake compute 12. Referablee: a city is visited if it is either a non-appearance city or a application city of any leg graspd amid a mistake. (2 signs) (5) Find the registration computes of entirety deals that entertain been manifestationd by twain drivers with a driving licence compute 006 and with a driving indulge compute 008. (2 signs) DPIT115 Postulates Management and Security Developed Impost (Assignment 2) Page 8 of 9 QUESTION 6 (7 signs) Assume that a manifestationr rise with a password 'DPIT115' educed a postulatesbase designated rapture and the manifestationr executed CREATE TABLE declarations abandoned on page 2 of the criterion monograph to educe the themeive tables in the postulatesbase rapture. Write a SQL script that achieves the behindcited operations by manifestationr ‘root’. Assume that the manifestationr ‘root’ has already alike to the postulatesbase. (1) The manifestationr ‘root’ nominates a postulatesbase rapture as a absence postulatesbase, and then the manifestationr educes two roles ‘driver’ and ‘admin’. (1 sign) (2) The manifestationr ‘root’ admits interpret similarity hues to the themeive consultations EMPLOYEE and DRIVER to the role ‘admin’. The interpret similarity hues canreferable be propagated to other roles or manifestationrs. (1 sign) (3) The manifestationr ‘root’ admits the hues to infconservation the rows into a themeive consultations TRIP and TRIPLEG to the role ‘driver’. The similarity hues can be propagated to other roles or manifestationrs. (1 sign) (4) The manifestationr ‘root’ admits the upcontinuance advantage on entirety themeive consultations in the rapture postulatesbase to the role ‘admin’. The advantage canreferable be propagated to other roles or manifestationrs. (1 sign) (5) The manifestationr ‘root’ admit the interpret similarity hues to counsel encircling the aggregate compute of mistakes manufactured by each driver to a role ‘driver’. (1 sign) (6) The manifestationr ‘root’ educes five (5) odd manifestationrs and admits the role ‘driver’ to three (3) of the manifestationrs, and the role ‘admin’ to the other manifestationrs. The planates and passtindividual of the odd manifestationr accounts are up to you. (1 sign) (7) The manifestationr ‘root’ sets the riches limits coercion the manifestationrs educed in the earlier trudge entiretyowing six (6) maximum conprevalent connections. Developedly, the manifestationr ‘root’ locks entirety the manifestationr accounts educed in the earlier trudge. (1 sign) DPIT115 Postulates Management and Security Developed Impost (Assignment 2) Page 9 of 9 QUESTION 7 (7 signs) The toolation and testing of obscure SELECT declarations that have-effect on sundry themeive consultations and involve sundry enigmatical pursuit stipulations is a time-consuming and challenging toil. (1) Explain the SQL programming technique you would manifestation to disencumber the toolation and testing of a obscure SELECT declaration. Referablee that we foresee you to explain a technique that has been covered in toils this question. (4 signs) (2) Manifestation the technique you pictorial in the earlier trudge to tool the behindcited inquiry as a SELECT declaration: Find the principal and the ultimate planates of entirety drivers who achieveed over than 25 mistakes so remote in 2020. (3 signs) END OF ASSESSMENT

Related Post