DIRECTIONS TO STUDENTS 1. All questions are to be attempted 2. Total number of questions: 7 (SEVEN) 3. This is an individual assessment 4. All questions are to be answered by you without assistance from other people 5. This assessment requires you to submit your answer to each question separately in Moodle 6. You will be required to use your virtual machine to run some of your SQL scripts 7. Your submission will be checked for uniqueness using Turnitin 8. Use your own words, provide your own examples and draw your own diagrams 9. Show your solution as well as the steps taken to answer the questions 10. Submit your solution to Moodle by the due date If it is suspected that you have accessed or received additional assistance from another person, the matter will be investigated as an alleged breach of the UOW College Academic Integrity and Student Conduct Policy, in accordance with the Procedure for Managing Alleged Student Misconduct. Please note, as part of this investigation, you may be required to undergo an oral examination to verify your understanding of the assessment content. DPIT115 Data Management and Security Final Assessment (Spring 2020) 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 number */ FNAME VARCHAR(50) NOT NULL, /* First name */ LNAME VARCHAR(50) NOT NULL, /* Last name */ DOB DATE NULL, /* Date of birth */ CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(ENUM) ); CREATE TABLE DRIVER( ENUM DECIMAL(12) NOT NULL, /* Employee number */ LNUM DECIMAL(8) NOT NULL, /* Driving license number */ STATUS VARCHAR(10) NOT NULL, /* Driver status */ 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 number */ CAPACITY DECIMAL(7) NOT NULL, /* Capacity */ WEIGHT DECIMAL(7) NOT NULL, /* Weight */ STATUS VARCHAR(10) NOT NULL, /* Present status */ 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, /* trip number */ lnum decimal(8) not null, /* driving license number */ regnum varchar(10) not null, /* truck registration number */ tdate date not null, /* trip date */ 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, /* trip number */ legnum decimal(2) not null, /* leg number */ departure varchar(30) not null, /* departure city */ destination varchar(30) not null, /* destination 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 data management and security final assessment (spring 2020) page 3 of 9 question 1 (10 marks) read and analyse the following specification of a sample database domain. a university would like to create a database to record information about some of its activities. the university offers a number of degrees to students. a degree is described by a unique name, the total number of credit points required to complete a degree, and several requirements that must be satisfied by the future students. the university offers three types of degrees: diploma degrees, undergraduate degrees, and postgraduate degrees. diploma degrees are a pathway to undergraduate degrees and include a list of subjects that equate to first-year undergraduate degree subjects. postgraduate degrees are available only for the students who have already completed an undergraduate degree. a description of a postgraduate degree includes a list of acceptable undergraduate degrees. each degree consists of an ordered sequence of subjects. a description of a subject consists of its number in a sequence and unique code, unique title, total number of credits points a subject is worth, and a list of learning objectives. the university employs academic staff members, tutors and support staff members. a common description of a university employee consists of a unique employee number, first name last name and date of birth. first name, last name and date of birth uniquely identify each employee. additionally, academic staff members and tutors are described by an academic degree achieved. support staff members are described by a list of qualifications acquired in the past together with a date when each qualification has been acquired. the university assigns the academic staff members and tutors to the subjects. a subject has one or two academic members assigned and a number of tutors. academic staff members and tutors can be assigned to many subjects. support staff members are assigned to the degrees. each support staff member is assigned to one degree, and a degree has one or more support staff members assigned. the university is divided into faculties and faculties are divided into schools. academic staff members and tutors belong to one school, and each school consists of many academic staff members and tutors. the faculties and schools are described by the unique names. the university records information when the academic staff members and tutors first join the schools. the university also keeps information about the former employees who worked at the university in the past. a description of a former employee is the same as a current employee, and additionally, it includes a hire date and end of employment date. (1) draw a conceptual schema for the specification of the database domain listed above. use the uml simplified class diagrams explained during this subject. note: you are not allowed to use any artificial identifiers (create new ids) and or add any attributes that are not explicitly mentioned in the specification. use umlet to draw the schema and paste images of your drawings into your microsoft word document using the template provided. add your name, student number and the date to your diagram. there is no need to provide a detailed analysis of how the conceptual schema has been created. (7 marks) (2) add two (2) new object classes with at least three (3) attributes each and appropriate associations. the choice of object classes, attributes and associations are up to you; however, these should relate to the existing scenario. use umlet to draw the changes to the schema and paste the second diagram into your microsoft word document under a heading called variation. write a text description that explains the additional objects, attributes and associations below this diagram. (3 marks) convert your microsoft word document based on the template provided into a pdf and submit your answer as a file question1.pdf if a submitted file has an incorrect filename or file type, it will receive no marks. dpit115 data management and security final assessment (spring 2020) page 4 of 9 question 2 (10 marks) consider the conceptual schema given below. your task is to perform the steps of logical database design, i.e. to transform a conceptual schema given above into a collection of relational schemas. before transforming add the attributes ‘date-made’ and ‘rego-renew-date’ to the bus class. add ‘homecity’ to the driver class. draw the resulting conceptual schema adding your name, student number and the date to the drawing. use umlet and paste images of your drawings into your microsoft word document using the template provided. list the names of attributes, primary key, candidate keys (if any), and foreign keys (if any) for each class in the relational schema. assume that an association method is used to implement the generalization. show your working and explain as you step through the transformation process. convert your microsoft word document based on the template provided into a pdf and submit your answer as a file question2.pdf if a submitted file has an incorrect filename or file type, it will receive no marks. dpit115 data management and security final assessment (spring 2020) page 5 of 9 question 3 (8 marks) write the data definition statements of sql that modify the structures of a database listed on page 2 of this assessment in the way described below. note, that some of the modification may require more than one sql data definition statement. (1) modify the consistency constraint of the sample database such that after the modification, it is possible to record in the database information about the trucks that have a capacity up to and including 200. (2 marks) (2) modify the structure and consistency constraints of the sample database such that it is possible to store information in the database about the total number of legs a trip contains. assume that a trip cannot contain of more than 10 legs. (2 marks) (3) modify the structure and consistency constraints of the sample database such it is possible to store information in the database about the mechanics employed by a transportation company. assume that a description of mechanic consists of an employee number, first name, last name, date of birth and qualification level. a qualification level is a positive integer number 1 or 2 or 3 or 4 or 5. remember that a mechanic is an employee. (2 marks) (4) modify the consistency constraints of the sample database, so it is possible to store information about the trip without providing information about a driver licence number. such modification is required when a driver leaves a transportation company, and we would like to keep information about all trips performed by the driver. (2 marks) add your code into the question3.sql template provided and output your report file on your virtual machine to a fine question3.rpt add your name, student number and the date to the comments section of your sql script. submit your answers as the files question3.sql and question3.rpt note your script may be tested and should not have any errors when run. if a submitted file has an incorrect filename or file type, it will receive no marks. dpit115 data management and security final assessment (spring 2020) page 6 of 9 question 4 (8 marks) write the data manipulation statements of sql that modify the contents of a database listed on page 2 of this lab task in the ways described below. note that you are not allowed to modify and/or to drop any consistency constraints. also note, that to implement some of the modifications listed below, you may need more than one data manipulation statement. (1) a new trip has been completed today. the trip was from sydney to newcastle. the trip has been performed by the driver with the licence number 504229 (column lnum in a relational table trip) who used the truck with registration number abc82d. insert the appropriate information into the sample database assuming the next trip number is 1201. (2 marks) (2) delete the information from the database about trip number 60. remember, that the foreign keys in all create table statements have no on delete cascade clause. (2 marks) (3) change the status of all the drivers who have performed more than 90 trips to on on leave. (2 marks) (4) copy information about all employees born before the year 2000 to a new table e20cent. there is no need to enforce any consistency constraints on the new table. (2 marks) submit your answer as a file question4.sql using the template provided. add your name, student number and the date to the comments section of your sql script. if a submitted file has an incorrect filename or file type, it will receive no marks. dpit115 data management and security final assessment (spring 2020) page 7 of 9 question 5 (10 marks) write select statements that implement the following queries. (1) find the first and the last names of all drivers who are not on leave. (1 mark) (2) find the registration numbers of all trucks that have not been used so far. (1 mark) (3) find the registration numbers of all trucks that have been used more than 50 times. (1 mark) (4) find the distinct names of all cities visited during the trip number 3 or the trip number 31. note: a city is visited if it is either a departure city or a destination city of any leg included within a trip. (1 mark) (5) find the registration numbers of all trucks that have been used by both drivers with a driving licence number 311606 and with a driving license number 901108. (1 mark) (6) data security is an important consideration when operating a database system, consider the database as outlined on page 2, as the database administrator what security considerations would you address. how would you address these issues to reduce the chance of the problem occurring? what would you do if the problem occurs? provide an original example to illustrate your answer. (5 marks) submit your answer as the files question5.sql and question5.pdf using the templates provided. add your name, student number and the date to the comments section of your sql script. note your script may be tested and should not have any errors when run. if a submitted file has an incorrect filename or file type, it will receive no marks. dpit115 data management and security final assessment (spring 2020) page 8 of 9 question 6 (7 marks) assume that the user ‘root’ with a password 'dpit115' created a database called transport and the user ‘root’ executed create table statements given on page 2 of the examination paper to create the relational tables in the database transport. write a sql script that performs the following operations by the user ‘root’. assume that the user ‘root’ has already connected to the database. (1) the user ‘root’ nominates a database transport as a default database, and then the user creates two roles ‘driver’ and ‘admin’. (1 mark) (2) the user ‘root’ grants read access rights to the relational tables employee and driver to the role ‘admin’. the read access rights cannot be propagated to other roles or users. (1 mark) (3) the user ‘root’ grants the rights to insert the rows into a relational tables trip and tripleg to the role ‘driver’. the access rights can be propagated to other roles or users. (1 mark) (4) the user ‘root’ grants the update privilege on all relational tables in the transport database to the role ‘admin’. the privilege cannot be propagated to other roles or users. (1 mark) (5) the user ‘root’ grant the read access rights to information about the total number of trips performed by each driver to a role ‘driver’. (1 mark) (6) the user ‘root’ creates five (5) new users and grants the role ‘driver’ to three (3) of the users, and the role ‘admin’ to the other users. the names and passwords of the new user accounts are up to you. (1 mark) (7) the user ‘root’ sets the resource limits for the users created in the previous step allowing six (6) maximum concurrent connections. finally, the user ‘root’ locks all the user accounts created in the previous step. (1 mark) add your code into the question6.sql template provided and output your report file on your virtual machine to a file named question6.rpt add your name, student number and the date to the comments section of your sql script. submit your answers as the files question6.sql and question6.rpt note your script may be tested and should not have any errors when run. if a submitted file has an incorrect filename or file type, it will receive no marks. dpit115 data management and security final assessment (spring 2020) page 9 of 9 question 7 (7 marks) the implementation and testing of complex select statements that operate on many relational tables and involve many sophisticated search conditions is a time-consuming and challenging task. (1) describe the sql programming technique you would choose to simplify the implementation and testing of a complex select statement. note that we expect you to describe a technique that has been covered in tasks in this subject (csit115 lecture - 15 - select statement (5)). you should add your answer to question (1) in the comments section of the template provided. (4 marks) (2) use the technique you described in the previous step to implement the following query as a select statement: find the first and the last names of all drivers who performed more than 42 trips so far in 2020. (3 marks) submit your answer as a file question7.sql using the template provided. add your name, student number and the date to the comments section of your sql script. note your script may be tested and should not have any errors when run. if a submitted file has an incorrect filename or file type, it will receive no marks. end of assessment 100="" )="" );="" create="" table="" trip(="" tnum="" decimal(10)="" not="" null,="" *="" trip="" number="" */="" lnum="" decimal(8)="" not="" null,="" *="" driving="" license="" number="" */="" regnum="" varchar(10)="" not="" null,="" *="" truck="" registration="" number="" */="" tdate="" date="" not="" null,="" *="" trip="" date="" */="" 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,="" *="" trip="" number="" */="" legnum="" decimal(2)="" not="" null,="" *="" leg="" number="" */="" departure="" varchar(30)="" not="" null,="" *="" departure="" city="" */="" destination="" varchar(30)="" not="" null,="" *="" destination="" 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="" data="" management="" and="" security="" final="" assessment="" (spring="" 2020)="" page="" 3="" of="" 9="" question="" 1="" (10="" marks)="" read="" and="" analyse="" the="" following="" specification="" of="" a="" sample="" database="" domain.="" a="" university="" would="" like="" to="" create="" a="" database="" to="" record="" information="" about="" some="" of="" its="" activities.="" the="" university="" offers="" a="" number="" of="" degrees="" to="" students.="" a="" degree="" is="" described="" by="" a="" unique="" name,="" the="" total="" number="" of="" credit="" points="" required="" to="" complete="" a="" degree,="" and="" several="" requirements="" that="" must="" be="" satisfied="" by="" the="" future="" students.="" the="" university="" offers="" three="" types="" of="" degrees:="" diploma="" degrees,="" undergraduate="" degrees,="" and="" postgraduate="" degrees.="" diploma="" degrees="" are="" a="" pathway="" to="" undergraduate="" degrees="" and="" include="" a="" list="" of="" subjects="" that="" equate="" to="" first-year="" undergraduate="" degree="" subjects.="" postgraduate="" degrees="" are="" available="" only="" for="" the="" students="" who="" have="" already="" completed="" an="" undergraduate="" degree.="" a="" description="" of="" a="" postgraduate="" degree="" includes="" a="" list="" of="" acceptable="" undergraduate="" degrees.="" each="" degree="" consists="" of="" an="" ordered="" sequence="" of="" subjects.="" a="" description="" of="" a="" subject="" consists="" of="" its="" number="" in="" a="" sequence="" and="" unique="" code,="" unique="" title,="" total="" number="" of="" credits="" points="" a="" subject="" is="" worth,="" and="" a="" list="" of="" learning="" objectives.="" the="" university="" employs="" academic="" staff="" members,="" tutors="" and="" support="" staff="" members.="" a="" common="" description="" of="" a="" university="" employee="" consists="" of="" a="" unique="" employee="" number,="" first="" name="" last="" name="" and="" date="" of="" birth.="" first="" name,="" last="" name="" and="" date="" of="" birth="" uniquely="" identify="" each="" employee.="" additionally,="" academic="" staff="" members="" and="" tutors="" are="" described="" by="" an="" academic="" degree="" achieved.="" support="" staff="" members="" are="" described="" by="" a="" list="" of="" qualifications="" acquired="" in="" the="" past="" together="" with="" a="" date="" when="" each="" qualification="" has="" been="" acquired.="" the="" university="" assigns="" the="" academic="" staff="" members="" and="" tutors="" to="" the="" subjects.="" a="" subject="" has="" one="" or="" two="" academic="" members="" assigned="" and="" a="" number="" of="" tutors.="" academic="" staff="" members="" and="" tutors="" can="" be="" assigned="" to="" many="" subjects.="" support="" staff="" members="" are="" assigned="" to="" the="" degrees.="" each="" support="" staff="" member="" is="" assigned="" to="" one="" degree,="" and="" a="" degree="" has="" one="" or="" more="" support="" staff="" members="" assigned.="" the="" university="" is="" divided="" into="" faculties="" and="" faculties="" are="" divided="" into="" schools.="" academic="" staff="" members="" and="" tutors="" belong="" to="" one="" school,="" and="" each="" school="" consists="" of="" many="" academic="" staff="" members="" and="" tutors.="" the="" faculties="" and="" schools="" are="" described="" by="" the="" unique="" names.="" the="" university="" records="" information="" when="" the="" academic="" staff="" members="" and="" tutors="" first="" join="" the="" schools.="" the="" university="" also="" keeps="" information="" about="" the="" former="" employees="" who="" worked="" at="" the="" university="" in="" the="" past.="" a="" description="" of="" a="" former="" employee="" is="" the="" same="" as="" a="" current="" employee,="" and="" additionally,="" it="" includes="" a="" hire="" date="" and="" end="" of="" employment="" date.="" (1)="" draw="" a="" conceptual="" schema="" for="" the="" specification="" of="" the="" database="" domain="" listed="" above.="" use="" the="" uml="" simplified="" class="" diagrams="" explained="" during="" this="" subject.="" note:="" you="" are="" not="" allowed="" to="" use="" any="" artificial="" identifiers="" (create="" new="" ids)="" and="" or="" add="" any="" attributes="" that="" are="" not="" explicitly="" mentioned="" in="" the="" specification.="" use="" umlet="" to="" draw="" the="" schema="" and="" paste="" images="" of="" your="" drawings="" into="" your="" microsoft="" word="" document="" using="" the="" template="" provided.="" add="" your="" name,="" student="" number="" and="" the="" date="" to="" your="" diagram.="" there="" is="" no="" need="" to="" provide="" a="" detailed="" analysis="" of="" how="" the="" conceptual="" schema="" has="" been="" created.="" (7="" marks)="" (2)="" add="" two="" (2)="" new="" object="" classes="" with="" at="" least="" three="" (3)="" attributes="" each="" and="" appropriate="" associations.="" the="" choice="" of="" object="" classes,="" attributes="" and="" associations="" are="" up="" to="" you;="" however,="" these="" should="" relate="" to="" the="" existing="" scenario.="" use="" umlet="" to="" draw="" the="" changes="" to="" the="" schema="" and="" paste="" the="" second="" diagram="" into="" your="" microsoft="" word="" document="" under="" a="" heading="" called="" variation.="" write="" a="" text="" description="" that="" explains="" the="" additional="" objects,="" attributes="" and="" associations="" below="" this="" diagram.="" (3="" marks)="" convert="" your="" microsoft="" word="" document="" based="" on="" the="" template="" provided="" into="" a="" pdf="" and="" submit="" your="" answer="" as="" a="" file="" question1.pdf="" if="" a="" submitted="" file="" has="" an="" incorrect="" filename="" or="" file="" type,="" it="" will="" receive="" no="" marks.="" dpit115="" data="" management="" and="" security="" final="" assessment="" (spring="" 2020)="" page="" 4="" of="" 9="" question="" 2="" (10="" marks)="" consider="" the="" conceptual="" schema="" given="" below.="" your="" task="" is="" to="" perform="" the="" steps="" of="" logical="" database="" design,="" i.e.="" to="" transform="" a="" conceptual="" schema="" given="" above="" into="" a="" collection="" of="" relational="" schemas.="" before="" transforming="" add="" the="" attributes="" ‘date-made’="" and="" ‘rego-renew-date’="" to="" the="" bus="" class.="" add="" ‘homecity’="" to="" the="" driver="" class.="" draw="" the="" resulting="" conceptual="" schema="" adding="" your="" name,="" student="" number="" and="" the="" date="" to="" the="" drawing.="" use="" umlet="" and="" paste="" images="" of="" your="" drawings="" into="" your="" microsoft="" word="" document="" using="" the="" template="" provided.="" list="" the="" names="" of="" attributes,="" primary="" key,="" candidate="" keys="" (if="" any),="" and="" foreign="" keys="" (if="" any)="" for="" each="" class="" in="" the="" relational="" schema.="" assume="" that="" an="" association="" method="" is="" used="" to="" implement="" the="" generalization.="" show="" your="" working="" and="" explain="" as="" you="" step="" through="" the="" transformation="" process.="" convert="" your="" microsoft="" word="" document="" based="" on="" the="" template="" provided="" into="" a="" pdf="" and="" submit="" your="" answer="" as="" a="" file="" question2.pdf="" if="" a="" submitted="" file="" has="" an="" incorrect="" filename="" or="" file="" type,="" it="" will="" receive="" no="" marks.="" dpit115="" data="" management="" and="" security="" final="" assessment="" (spring="" 2020)="" page="" 5="" of="" 9="" question="" 3="" (8="" marks)="" write="" the="" data="" definition="" statements="" of="" sql="" that="" modify="" the="" structures="" of="" a="" database="" listed="" on="" page="" 2="" of="" this="" assessment="" in="" the="" way="" described="" below.="" note,="" that="" some="" of="" the="" modification="" may="" require="" more="" than="" one="" sql="" data="" definition="" statement.="" (1)="" modify="" the="" consistency="" constraint="" of="" the="" sample="" database="" such="" that="" after="" the="" modification,="" it="" is="" possible="" to="" record="" in="" the="" database="" information="" about="" the="" trucks="" that="" have="" a="" capacity="" up="" to="" and="" including="" 200.="" (2="" marks)="" (2)="" modify="" the="" structure="" and="" consistency="" constraints="" of="" the="" sample="" database="" such="" that="" it="" is="" possible="" to="" store="" information="" in="" the="" database="" about="" the="" total="" number="" of="" legs="" a="" trip="" contains.="" assume="" that="" a="" trip="" cannot="" contain="" of="" more="" than="" 10="" legs.="" (2="" marks)="" (3)="" modify="" the="" structure="" and="" consistency="" constraints="" of="" the="" sample="" database="" such="" it="" is="" possible="" to="" store="" information="" in="" the="" database="" about="" the="" mechanics="" employed="" by="" a="" transportation="" company.="" assume="" that="" a="" description="" of="" mechanic="" consists="" of="" an="" employee="" number,="" first="" name,="" last="" name,="" date="" of="" birth="" and="" qualification="" level.="" a="" qualification="" level="" is="" a="" positive="" integer="" number="" 1="" or="" 2="" or="" 3="" or="" 4="" or="" 5.="" remember="" that="" a="" mechanic="" is="" an="" employee.="" (2="" marks)="" (4)="" modify="" the="" consistency="" constraints="" of="" the="" sample="" database,="" so="" it="" is="" possible="" to="" store="" information="" about="" the="" trip="" without="" providing="" information="" about="" a="" driver="" licence="" number.="" such="" modification="" is="" required="" when="" a="" driver="" leaves="" a="" transportation="" company,="" and="" we="" would="" like="" to="" keep="" information="" about="" all="" trips="" performed="" by="" the="" driver.="" (2="" marks)="" add="" your="" code="" into="" the="" question3.sql="" template="" provided="" and="" output="" your="" report="" file="" on="" your="" virtual="" machine="" to="" a="" fine="" question3.rpt="" add="" your="" name,="" student="" number="" and="" the="" date="" to="" the="" comments="" section="" of="" your="" sql="" script.="" submit="" your="" answers="" as="" the="" files="" question3.sql="" and="" question3.rpt="" note="" your="" script="" may="" be="" tested="" and="" should="" not="" have="" any="" errors="" when="" run.="" if="" a="" submitted="" file="" has="" an="" incorrect="" filename="" or="" file="" type,="" it="" will="" receive="" no="" marks.="" dpit115="" data="" management="" and="" security="" final="" assessment="" (spring="" 2020)="" page="" 6="" of="" 9="" question="" 4="" (8="" marks)="" write="" the="" data="" manipulation="" statements="" of="" sql="" that="" modify="" the="" contents="" of="" a="" database="" listed="" on="" page="" 2="" of="" this="" lab="" task="" in="" the="" ways="" described="" below.="" note="" that="" you="" are="" not="" allowed="" to="" modify="" and/or="" to="" drop="" any="" consistency="" constraints.="" also="" note,="" that="" to="" implement="" some="" of="" the="" modifications="" listed="" below,="" you="" may="" need="" more="" than="" one="" data="" manipulation="" statement.="" (1)="" a="" new="" trip="" has="" been="" completed="" today.="" the="" trip="" was="" from="" sydney="" to="" newcastle.="" the="" trip="" has="" been="" performed="" by="" the="" driver="" with="" the="" licence="" number="" 504229="" (column="" lnum="" in="" a="" relational="" table="" trip)="" who="" used="" the="" truck="" with="" registration="" number="" abc82d.="" insert="" the="" appropriate="" information="" into="" the="" sample="" database="" assuming="" the="" next="" trip="" number="" is="" 1201.="" (2="" marks)="" (2)="" delete="" the="" information="" from="" the="" database="" about="" trip="" number="" 60.="" remember,="" that="" the="" foreign="" keys="" in="" all="" create="" table="" statements="" have="" no="" on="" delete="" cascade="" clause.="" (2="" marks)="" (3)="" change="" the="" status="" of="" all="" the="" drivers="" who="" have="" performed="" more="" than="" 90="" trips="" to="" on="" on="" leave.="" (2="" marks)="" (4)="" copy="" information="" about="" all="" employees="" born="" before="" the="" year="" 2000="" to="" a="" new="" table="" e20cent.="" there="" is="" no="" need="" to="" enforce="" any="" consistency="" constraints="" on="" the="" new="" table.="" (2="" marks)="" submit="" your="" answer="" as="" a="" file="" question4.sql="" using="" the="" template="" provided.="" add="" your="" name,="" student="" number="" and="" the="" date="" to="" the="" comments="" section="" of="" your="" sql="" script.="" if="" a="" submitted="" file="" has="" an="" incorrect="" filename="" or="" file="" type,="" it="" will="" receive="" no="" marks.="" dpit115="" data="" management="" and="" security="" final="" assessment="" (spring="" 2020)="" page="" 7="" of="" 9="" question="" 5="" (10="" marks)="" write="" select="" statements="" that="" implement="" the="" following="" queries.="" (1)="" find="" the="" first="" and="" the="" last="" names="" of="" all="" drivers="" who="" are="" not="" on="" leave.="" (1="" mark)="" (2)="" find="" the="" registration="" numbers="" of="" all="" trucks="" that="" have="" not="" been="" used="" so="" far.="" (1="" mark)="" (3)="" find="" the="" registration="" numbers="" of="" all="" trucks="" that="" have="" been="" used="" more="" than="" 50="" times.="" (1="" mark)="" (4)="" find="" the="" distinct="" names="" of="" all="" cities="" visited="" during="" the="" trip="" number="" 3="" or="" the="" trip="" number="" 31.="" note:="" a="" city="" is="" visited="" if="" it="" is="" either="" a="" departure="" city="" or="" a="" destination="" city="" of="" any="" leg="" included="" within="" a="" trip.="" (1="" mark)="" (5)="" find="" the="" registration="" numbers="" of="" all="" trucks="" that="" have="" been="" used="" by="" both="" drivers="" with="" a="" driving="" licence="" number="" 311606="" and="" with="" a="" driving="" license="" number="" 901108.="" (1="" mark)="" (6)="" data="" security="" is="" an="" important="" consideration="" when="" operating="" a="" database="" system,="" consider="" the="" database="" as="" outlined="" on="" page="" 2,="" as="" the="" database="" administrator="" what="" security="" considerations="" would="" you="" address.="" how="" would="" you="" address="" these="" issues="" to="" reduce="" the="" chance="" of="" the="" problem="" occurring?="" what="" would="" you="" do="" if="" the="" problem="" occurs?="" provide="" an="" original="" example="" to="" illustrate="" your="" answer.="" (5="" marks)="" submit="" your="" answer="" as="" the="" files="" question5.sql="" and="" question5.pdf="" using="" the="" templates="" provided.="" add="" your="" name,="" student="" number="" and="" the="" date="" to="" the="" comments="" section="" of="" your="" sql="" script.="" note="" your="" script="" may="" be="" tested="" and="" should="" not="" have="" any="" errors="" when="" run.="" if="" a="" submitted="" file="" has="" an="" incorrect="" filename="" or="" file="" type,="" it="" will="" receive="" no="" marks.="" dpit115="" data="" management="" and="" security="" final="" assessment="" (spring="" 2020)="" page="" 8="" of="" 9="" question="" 6="" (7="" marks)="" assume="" that="" the="" user="" ‘root’="" with="" a="" password="" 'dpit115'="" created="" a="" database="" called="" transport="" and="" the="" user="" ‘root’="" executed="" create="" table="" statements="" given="" on="" page="" 2="" of="" the="" examination="" paper="" to="" create="" the="" relational="" tables="" in="" the="" database="" transport.="" write="" a="" sql="" script="" that="" performs="" the="" following="" operations="" by="" the="" user="" ‘root’.="" assume="" that="" the="" user="" ‘root’="" has="" already="" connected="" to="" the="" database.="" (1)="" the="" user="" ‘root’="" nominates="" a="" database="" transport="" as="" a="" default="" database,="" and="" then="" the="" user="" creates="" two="" roles="" ‘driver’="" and="" ‘admin’.="" (1="" mark)="" (2)="" the="" user="" ‘root’="" grants="" read="" access="" rights="" to="" the="" relational="" tables="" employee="" and="" driver="" to="" the="" role="" ‘admin’.="" the="" read="" access="" rights="" cannot="" be="" propagated="" to="" other="" roles="" or="" users.="" (1="" mark)="" (3)="" the="" user="" ‘root’="" grants="" the="" rights="" to="" insert="" the="" rows="" into="" a="" relational="" tables="" trip="" and="" tripleg="" to="" the="" role="" ‘driver’.="" the="" access="" rights="" can="" be="" propagated="" to="" other="" roles="" or="" users.="" (1="" mark)="" (4)="" the="" user="" ‘root’="" grants="" the="" update="" privilege="" on="" all="" relational="" tables="" in="" the="" transport="" database="" to="" the="" role="" ‘admin’.="" the="" privilege="" cannot="" be="" propagated="" to="" other="" roles="" or="" users.="" (1="" mark)="" (5)="" the="" user="" ‘root’="" grant="" the="" read="" access="" rights="" to="" information="" about="" the="" total="" number="" of="" trips="" performed="" by="" each="" driver="" to="" a="" role="" ‘driver’.="" (1="" mark)="" (6)="" the="" user="" ‘root’="" creates="" five="" (5)="" new="" users="" and="" grants="" the="" role="" ‘driver’="" to="" three="" (3)="" of="" the="" users,="" and="" the="" role="" ‘admin’="" to="" the="" other="" users.="" the="" names="" and="" passwords="" of="" the="" new="" user="" accounts="" are="" up="" to="" you.="" (1="" mark)="" (7)="" the="" user="" ‘root’="" sets="" the="" resource="" limits="" for="" the="" users="" created="" in="" the="" previous="" step="" allowing="" six="" (6)="" maximum="" concurrent="" connections.="" finally,="" the="" user="" ‘root’="" locks="" all="" the="" user="" accounts="" created="" in="" the="" previous="" step.="" (1="" mark)="" add="" your="" code="" into="" the="" question6.sql="" template="" provided="" and="" output="" your="" report="" file="" on="" your="" virtual="" machine="" to="" a="" file="" named="" question6.rpt="" add="" your="" name,="" student="" number="" and="" the="" date="" to="" the="" comments="" section="" of="" your="" sql="" script.="" submit="" your="" answers="" as="" the="" files="" question6.sql="" and="" question6.rpt="" note="" your="" script="" may="" be="" tested="" and="" should="" not="" have="" any="" errors="" when="" run.="" if="" a="" submitted="" file="" has="" an="" incorrect="" filename="" or="" file="" type,="" it="" will="" receive="" no="" marks.="" dpit115="" data="" management="" and="" security="" final="" assessment="" (spring="" 2020)="" page="" 9="" of="" 9="" question="" 7="" (7="" marks)="" the="" implementation="" and="" testing="" of="" complex="" select="" statements="" that="" operate="" on="" many="" relational="" tables="" and="" involve="" many="" sophisticated="" search="" conditions="" is="" a="" time-consuming="" and="" challenging="" task.="" (1)="" describe="" the="" sql="" programming="" technique="" you="" would="" choose="" to="" simplify="" the="" implementation="" and="" testing="" of="" a="" complex="" select="" statement.="" note="" that="" we="" expect="" you="" to="" describe="" a="" technique="" that="" has="" been="" covered="" in="" tasks="" in="" this="" subject="" (csit115="" lecture="" -="" 15="" -="" select="" statement="" (5)).="" you="" should="" add="" your="" answer="" to="" question="" (1)="" in="" the="" comments="" section="" of="" the="" template="" provided.="" (4="" marks)="" (2)="" use="" the="" technique="" you="" described="" in="" the="" previous="" step="" to="" implement="" the="" following="" query="" as="" a="" select="" statement:="" find="" the="" first="" and="" the="" last="" names="" of="" all="" drivers="" who="" performed="" more="" than="" 42="" trips="" so="" far="" in="" 2020.="" (3="" marks)="" submit="" your="" answer="" as="" a="" file="" question7.sql="" using="" the="" template="" provided.="" add="" your="" name,="" student="" number="" and="" the="" date="" to="" the="" comments="" section="" of="" your="" sql="" script.="" note="" your="" script="" may="" be="" tested="" and="" should="" not="" have="" any="" errors="" when="" run.="" if="" a="" submitted="" file="" has="" an="" incorrect="" filename="" or="" file="" type,="" it="" will="" receive="" no="" marks.="" end="" of="">