DPIT115 Data Management and Security Final Assessment (Assignment)

DPIT115 Facts Management and Security Latest Impost (Assignment 2)
Page 1 of 9
Diploma of Advice Technology
DPIT115 Facts Management and Security
Latest Impost (Assignment 2)
Autumn Session 2020
This exam represents 40% of the entirety matter indications
_______________________________________________________________________________
Impuboard Continuance: 7 June 2020, 11:55 PM
________________________________________________________________________________________
DIRECTIONS TO STUDENTS
1. Sum questions are to be attempted
2. Entirety enumerate of questions: 7 (SEVEN)
3. This is an disuniteicular impost
4. Sum questions are to be retorted by you extraneously protection from other people
5. This impost exacts you to propose singly individual Microsoft Word instrument
6. Do referable conceive the questions in your retort
7. Your acquiescence obtain be checked control singularness using Turnitin
8. Verification your confess language, furnish your confess examples and pull your confess diagrams.
9. Show your disintegration as well-mannered-mannered as the treads enthralled to retort the questions
10. Propose your disintegration to Moodle by the impuboard continuance.
If it is reputed that you own modeed or ordinary appended protection from another individual, the
matter obtain be investigated as an sumeged nonperformance of the UOW College Academic Integrity and Novice
Conduct Policy, in agreement with the Procedure control Managing Sumeged Novice Misconduct.
Please referablee, as disunite of this trial, you may be exactd to sustain an verbal trial to
verify your reason of the impost pleased.

DPIT115 Facts Management and Security Latest 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 enumerate */
FNAME VARCHAR(50) NOT NULL, /* Restraintemost indicate */
LNAME VARCHAR(50) NOT NULL, /* Decisive indicate */
DOB DATE NULL, /* Continuance of nativity */
CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(ENUM) );
CREATE TABLE DRIVER(
ENUM DECIMAL(12) NOT NULL, /* Employee enumerate */
LNUM DECIMAL(8) NOT NULL, /* Driving permit enumerate */
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 enumerate */
CAPACITY DECIMAL(7) NOT NULL, /* Magnitude */
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 enumerate */ LNUM DECIMAL(8) NOT NULL, /* Driving permit enumerate */ REGNUM VARCHAR(10) NOT NULL, /* Barter registration enumerate */ 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 enumerate */ LEGNUM DECIMAL(2) NOT NULL, /* Leg enumerate */ DEPARTURE VARCHAR(30) NOT NULL, /* Concealment city */ DESTINATION VARCHAR(30) NOT NULL, /* Doom 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 Facts Management and Security Latest Impost (Assignment 2) Page 3 of 9 QUESTION 1 (10 indications) Unravel and analyse the behindcited mention of a pattern factsbase estate. A University would enjoy to beget a factsbase to archives advice encircling some of its activities. The university offers a enumerate of extents to novices. A extent is vivid by a singular indicate, the entirety enumerate of belief points exactd to entire a extent, and various accomplishments that must be satisfied by the coming novices. The university offers three types of extents: underdisequalize extents, postgraduate degrees, and disequalize certificate. Postdisequalize extents are suited singly control the novices who own already entired an underdisequalize extent. A style of a postdisequalize extent conceives a schedule of accepboard underdisequalize extents. A style of a disequalize certificate conceives a accomplishment on the entirety enumerate of years of negotiative experiment. Each extent consists of an ordered series of matters. A style of a matter consists of its enumerate in a series and singular sequence, singular style, entirety enumerate of beliefs points a matter is desert, and a schedule of learning goalives. The university employs academic staff constituents, tutors and help staff constituents. A despicable style of a university employee consists of a singular employee enumerate, restraintemost indicate decisive indicate and continuance of nativity. Foremost indicate, decisive indicate and continuance of nativity singularly authorized each employee. Appendedly, academic staff members and tutors are vivid by an academic extent achieved. Help staff constituents are vivid by a schedule of capabilitys acquired in the spent unitedly with a continuance when each capability has been acquired. The university assigns the academic staff constituents and tutors to the matters. A matter has individual or couple academic constituents assigned and a enumerate of tutors. Academic staff constituents and tutors can be assigned to sundry matters. Help staff constituents are assigned to the extents. Each help staff constituent is assigned to individual extent, and a extent has individual or elapsed help staff constituents assigned. The university is separated into faculties and faculties are separated into instructs. Academic staff constituents and tutors belong to individual instruct, and each instruct consists of sundry academic staff constituents and tutors. The faculties and instructs are vivid by the singular indicates. The university archivess advice when the academic staff constituents and tutors restraintemost couple the instructs. The university also retains the advice encircling the controlmer employees who worked at the university in the spent. A style of a controlmer employee is the same as a running employee, and appendedly, it conceives a employ continuance and goal of holding continuance. (1) Pull a conceptual schema control the mention of a factsbase estate scheduleed aloft. Verification the notation of UML simplified assort diagrams explained to you during this matter. Referablee: you are referable recognized to verification any concocted identifiers and any propertys that are referable mentioned in the mention. Verification UMLet to pull the schema and spente images of your pullings into your Microsoft Word document. Supplement your indicate, novice enumerate and the continuance to your diagram. There is NO NEED to furnish a constructive segregation of how a conceptual schema has been begetd. The latest conceptual schema explicit in the UML simplified referableation assortes is matter is competent. (7 indications) Supplement couple (2) upstart goal assortes with at ultimate three (3) propertys each and misspend familiaritys. The select of goal assortes, propertys and familiaritys are up to you; still, these should relate to the strong scenario. Verification UMLet to pull the changes to the schema and spente the promote diagram into your Microsoft Word instrument. Write a citation style that explains the appended goals, propertys and associations under the diagram. (3 indications) DPIT115 Facts Management and Security Latest Impost (Assignment 2) Page 4 of 9 QUESTION 2 (10 indications) Consider the conceptual schema attached under. Your business is to accomplish the treads of close factsbase artifice, i.e. to transfigure a conceptual schema attached aloft into a gathering of topicive schemas. Anteriorly transfigureing supplement the property ‘date-made’ to the BUS assort and ‘home-city’ to the DRIVER assort. Pull the resulting conceptual schema supplementing your indicate, novice enumerate and the continuance to the pulling. Verification UMLet and spente images of your pullings into your Microsoft Word instrument. Control each topicive schema evidently schedule the indicates of propertys, earliest explanation, candicontinuance explanations (if any), and alien explanations (if any). Assume that an familiarity mode must be verificationd to instrument the generalization. Show your inaugurated as you tunravel through the course of the transfigureation. DPIT115 Facts Management and Security Latest Impost (Assignment 2) Page 5 of 9 QUESTION 3 (8 indications) Write the facts specification announcements of SQL that disagree the constructions of a factsbase scheduleed on page 2 of this impost in the fashion vivid under. Note, that some of the alteration may exact elapsed than individual facts specification announcements of SQL statement. (1) Disagree the analogy bustle of the pattern factsbase such that behind the alteration, it is feasible to archives in the factsbase advice encircling the barters that own a magnitude up to and including 220. (2 indications) (2) Disagree the construction and analogy bustle of the pattern factsbase such that behind the modification, it is feasible to supply advice in the factsbase encircling the entirety enumerate of legs a mistake consists of. Assume that a mistake canreferable consist of elapsed than 20 legs. (2 indications) (3) Disagree the construction and analogy bustles of the pattern factsbase such it is feasible to supply advice in the factsbase encircling the artisans occupied by a gait union. Assume that a style of artisan consists of an employee enumerate, restraintemost indicate, decisive indicate, continuance of nativity and capability raze. A capability raze is a express integer enumerate 1 or 2 or 3 or 4 or 5. Reconstituent that a artisan is an employee. (2 indications) (4) Disagree the analogy bustles of the pattern factsbase so it is feasible to supply advice encircling the mistake extraneously providing advice encircling a driver licence enumerate. Such alteration is required when a driver concessions a gait union, and we would enjoy to retain advice encircling sum mistakes accomplished by the driver. (2 indications) DPIT115 Facts Management and Security Latest Impost (Assignment 2) Page 6 of 9 QUESTION 4 (8 indications) Write the facts construction announcements of SQL that disagree the pleaseds of a factsbase scheduleed on page 2 of this lab business in the fashions vivid under. Silence that you are referable sumowed to disagree and/or to fcomplete any analogy bustles. Also referablee, that to instrument some of the alterations scheduleed under, you may demand elapsed than individual facts construction announcement of SQL. (1) Assume that the largest enumerate of a mistake is 1200 (the largest prize in a post TNUM in a topicive board TRIP). Assume that a upstart mistake has been entired today. The mistake was from Sydney to Dubbo and then from Dubbo end to Sydney. The mistake has been accomplished by a driver with a licence enumerate 008 (post LNUM in a topicive board TRIP) who verificationd a barter with a registration enumerate AL08UK. Infuse misspend advice into a pattern factsbase. (2 indications) (2) Delete the advice from the factsbase encircling mistake enumerate 60. Remember, that the controleign explanations in sum CREATE TABLE announcements own no ON DELETE CASCADE passage. (2 indications) (3) Change the standing of sum drivers who accomplished elapsed than 106 mistakes to a prize ON LEAVE. (2 indications) (4) Copy advice encircling sum employees born anteriorly the year 2000 to a upstart board E20CENT. There is no demand to require any analogy bustles on the upstart board. (2 indications) DPIT115 Facts Management and Security Latest Impost (Assignment 2) Page 7 of 9 QUESTION 5 (10 indications) Write SELECT announcements that instrument the behindcited queries. (1) Find the restraintemost and the decisive indicates of sum drivers who are referable on concession. (2 indications) (2) Find the registration enumerates of sum barters that own referable been verificationd so distant. (2 indications) (3) Find the registration enumerates of sum barters that own been verificationd elapsed than 200 times. (2 indications) (4) Find the disjoined indicates of sum cities visited during a mistake enumerate 7 or a mistake enumerate 12. Referablee: a city is visited if it is either a concealment city or a doom city of any leg conceived among a mistake. (2 indications) (5) Find the registration enumerates of sum barters that own been verificationd by twain drivers with a driving licence enumerate 006 and with a driving permit enumerate 008. (2 indications) DPIT115 Facts Management and Security Latest Impost (Assignment 2) Page 8 of 9 QUESTION 6 (7 indications) Assume that a verificationr spring with a password 'DPIT115' begetd a factsbase named bliss and the verificationr executed CREATE TABLE announcements attached on page 2 of the trial brochure to beget the topicive tables in the factsbase bliss. Write a SQL script that accomplishs the behindcited operations by verificationr ‘root’. Assume that the verificationr ‘root’ has already alike to the factsbase. (1) The verificationr ‘root’ nominates a factsbase bliss as a controlfeit factsbase, and then the verificationr begets couple roles ‘driver’ and ‘admin’. (1 indication) (2) The verificationr ‘root’ confers unravel mode hues to the topicive boards EMPLOYEE and DRIVER to the role ‘admin’. The unravel mode hues canreferable be propagated to other roles or verificationrs. (1 indication) (3) The verificationr ‘root’ confers the hues to infauthentication the rows into a topicive boards TRIP and TRIPLEG to the role ‘driver’. The mode hues can be propagated to other roles or verificationrs. (1 indication) (4) The verificationr ‘root’ confers the upcontinuance liberty on sum topicive boards in the bliss factsbase to the role ‘admin’. The liberty canreferable be propagated to other roles or verificationrs. (1 indication) (5) The verificationr ‘root’ confer the unravel mode hues to advice encircling the entirety enumerate of mistakes effected by each driver to a role ‘driver’. (1 indication) (6) The verificationr ‘root’ begets five (5) upstart verificationrs and confers the role ‘driver’ to three (3) of the verificationrs, and the role ‘admin’ to the other verificationrs. The indicates and passlanguage of the upstart verificationr accounts are up to you. (1 indication) (7) The verificationr ‘root’ sets the material limits control the verificationrs begetd in the anterior tunravel sumowing six (6) maximum conrunning connections. Latestly, the verificationr ‘root’ locks sum the verificationr accounts begetd in the anterior tread. (1 indication) DPIT115 Facts Management and Security Latest Impost (Assignment 2) Page 9 of 9 QUESTION 7 (7 indications) The instrumentation and testing of deep SELECT announcements that act on sundry topicive boards and involve sundry uncertain quest conditions is a time-consuming and challenging business. (1) Delineate the SQL programming technique you would verification to disencumber the instrumentation and testing of a deep SELECT announcement. Referablee that we forecast you to delineate a technique that has been covered in businesss this matter. (4 indications) (2) Verification the technique you vivid in the anterior tunravel to instrument the behindcited question as a SELECT announcement: Find the restraintemost and the decisive indicates of sum drivers who accomplished elapsed than 25 mistakes so distant in 2020. (3 indications) END OF ASSESSMENT

Related Post