DPIT115 Data Management and Security Final Assessment (Summer 2022) QUESTION (10 marks) QUESTION (10 marks) QUESTION (10 marks) Information Technology Data Management and...

1 answer below »
please don't keep it plagiarized i will face penalty otherwise


DPIT115 Data Management and Security Final Assessment (Summer 2022) QUESTION (10 marks) QUESTION (10 marks) QUESTION (10 marks) Information Technology Data Management and Security Summer Session 2022 This exam represents 40% of the total subject marks _______________________________________________________________________________________ DIRECTIONS TO STUDENTS 1. This is an individual assessment; complete questions without assistance from other people. 2. Total number of questions: 4 (FOUR). 3. Answer all 4 (FOUR) questions. 4. This assessment requires you to submit your answer to each question separately in Moodle. 5. You will be required to use your virtual machine to run some of your SQL scripts. 6. Your submission will be checked for uniqueness using Turnitin. 7. Use your own words, provide your own examples and draw your own diagrams. 8. Show your solution as well as the steps taken to answer the questions. 9. 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 and you will face heavy penalties. QUESTIONS 2, 3, and 4 REFER TO THE RELATIONAL TABLES LISTED BELOW CREATE TABLE EMPLOYEE( EMPNUM DECIMAL(12) NOT NULL, /* Employee number */ FIRSTNAME VARCHAR(50) NOT NULL, /* First name */ LASTNAME VARCHAR(50) NOT NULL, /* Last name */ DOB DATE NULL, /* Date of birth CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(EMPNUM) ); CREATE TABLE DRIVER( */ EMPNUM DECIMAL(12) NOT NULL, /* Employee number */ LICENSENUM DECIMAL(8) NOT NULL, /* Driving license number */ STATUS VARCHAR(10) NOT NULL, /* Driver status CONSTRAINT DRIVER_PKEY PRIMARY KEY(EMPNUM), CONSTRAINT DRIVER_UNIQUE UNIQUE(LICENSENUM), CONSTRAINT DRIVER_FKEY FOREIGN KEY(EMPNUM) REFERENCES EMPLOYEE(EMPNUM), 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 < 300="" ),="" constraint="" truck_capacity="" check="" (="" capacity=""> 0.0 AND CAPACITY < 120 ) ); create table trip( */ tripnum decimal(10) not null, /* trip number */ licensenum 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 (tripnum), constraint trip_ckey unique (licensenum, regnum, tdate), constraint trip_fkey1 foreign key (licensenum) references driver(licensenum), constraint trip_fkey2 foreign key (regnum) references truck(regnum) ); create table tripleg( */ tripnum 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 (tripnum, legnum), constraint tripleg_unique unique(tripnum, departure, destination), constraint tripleg_fkey1 foreign key (tripnum) references trip(tripnum) ); */ 1 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 attribute ‘phone’ to the branch and the attribute ‘mobile-phone’ to the customer 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 generalisation. 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 question1.pdf if a submitted file has an incorrect filename or file type, it may lose marks. dpit115 data management and security final assessment (summer 2022) question (10 marks) page 1 of 6 page 1 of 6 page 1 of 6 2 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 modifications 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 225. (2 marks) (2) modify the structure and consistency constraints of the sample database such that after the modification, it is possible to store information in the database about the total number of legs a trip contains. assume that a trip cannot contain more than 10 legs. (2 marks) (3) modify the structure of the sample database so it is possible to store information in a new table in the database about mechanics employed by a transportation company. assume that a description of a 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 or 6. 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) (5) explain how the use of consistency constraints supports the management of data security. use original examples that you make up yourself to illustrate your answer. (2 marks) add your code into the question2.sql template provided and output your report file on your virtual machine to a file named question2.rpt. add your name, student number and the date to the comments section of your sql script. submit your answers as the files question2.sql,question2.rpt and question2.pdf using the templates provided. 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 may lose marks. 3 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 wollongong to orange. the trip has been performed by the driver with the licence number 412443 (column licensenum in a relational table trip) who used the truck with registration number ad29fz. insert the appropriate information into the sample database assuming the next trip number is 3452. (2 marks) (2) delete the information from the database about trip number 53. 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 24 trips to be on leave. (2 marks) (4) copy information about all employees born before the year 2000 to a new table s20cent. there is no need to enforce any consistency constraints on the new table. (2 marks) (5) explain how the data definition languate (ddl) statements of sql are used in data management and security. use original examples including sample code that you make up yourself to illustrate your answer. (2 marks) submit your answer as files named question3.sql and question3.pdf using the templates 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 may lose marks. 4 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 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’ grants 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 two (2) of the users, and the role ‘admin’ to the all 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 ten (10) maximum concurrent connections. finally, the user ‘root’ locks all the user accounts created in the previous step. (1 mark) (8) explain in your own words the difference between authentication and authorisation. use original examples that you make up yourself and diagrams that you draw yourself to illustrate your answer. (3 marks) add your code into the question4.sql template provided and output your report file on your virtual machine to a file named question4.rpt add your name, student number and the date to the comments section of your sql script. submit your answers as files named question4.sql, question4.rpt and question4.pdf, use the templates provided. 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 may lose marks. end of assessment page 1 of 6 page 1 of 6 page 1 of 6 student number: student name: question 1 (10 marks) page 2 of 2 120="" )="" );="" create="" table="" trip(="" */="" tripnum="" decimal(10)="" not="" null,="" *="" trip="" number="" */="" licensenum="" 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="" (tripnum),="" constraint="" trip_ckey="" unique="" (licensenum,="" regnum,="" tdate),="" constraint="" trip_fkey1="" foreign="" key="" (licensenum)="" references="" driver(licensenum),="" constraint="" trip_fkey2="" foreign="" key="" (regnum)="" references="" truck(regnum)="" );="" create="" table="" tripleg(="" */="" tripnum="" 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="" (tripnum,="" legnum),="" constraint="" tripleg_unique="" unique(tripnum,="" departure,="" destination),="" constraint="" tripleg_fkey1="" foreign="" key="" (tripnum)="" references="" trip(tripnum)="" );="" */="" 1="" 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="" attribute="" ‘phone’="" to="" the="" branch="" and="" the="" attribute="" ‘mobile-phone’="" to="" the="" customer="" 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="" generalisation.="" 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="" question1.pdf="" if="" a="" submitted="" file="" has="" an="" incorrect="" filename="" or="" file="" type,="" it="" may="" lose="" marks.="" dpit115="" data="" management="" and="" security="" final="" assessment="" (summer="" 2022)="" question="" (10="" marks)="" page="" 1="" of="" 6="" page="" 1="" of="" 6="" page="" 1="" of="" 6="" 2="" 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="" modifications="" 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="" 225.="" (2="" marks)="" (2)="" modify="" the="" structure="" and="" consistency="" constraints="" of="" the="" sample="" database="" such="" that="" after="" the="" modification,="" it="" is="" possible="" to="" store="" information="" in="" the="" database="" about="" the="" total="" number="" of="" legs="" a="" trip="" contains.="" assume="" that="" a="" trip="" cannot="" contain="" more="" than="" 10="" legs.="" (2="" marks)="" (3)="" modify="" the="" structure="" of="" the="" sample="" database="" so="" it="" is="" possible="" to="" store="" information="" in="" a="" new="" table="" in="" the="" database="" about="" mechanics="" employed="" by="" a="" transportation="" company.="" assume="" that="" a="" description="" of="" a="" 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="" or="" 6.="" 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)="" (5)="" explain="" how="" the="" use="" of="" consistency="" constraints="" supports="" the="" management="" of="" data="" security.="" use="" original="" examples="" that="" you="" make="" up="" yourself="" to="" illustrate="" your="" answer.="" (2="" marks)="" add="" your="" code="" into="" the="" question2.sql="" template="" provided="" and="" output="" your="" report="" file="" on="" your="" virtual="" machine="" to="" a="" file="" named="" question2.rpt.="" add="" your="" name,="" student="" number="" and="" the="" date="" to="" the="" comments="" section="" of="" your="" sql="" script.="" submit="" your="" answers="" as="" the="" files="" question2.sql,question2.rpt="" and="" question2.pdf="" using="" the="" templates="" provided.="" 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="" may="" lose="" marks.="" 3="" 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="" wollongong="" to="" orange.="" the="" trip="" has="" been="" performed="" by="" the="" driver="" with="" the="" licence="" number="" 412443="" (column="" licensenum="" in="" a="" relational="" table="" trip)="" who="" used="" the="" truck="" with="" registration="" number="" ad29fz.="" insert="" the="" appropriate="" information="" into="" the="" sample="" database="" assuming="" the="" next="" trip="" number="" is="" 3452.="" (2="" marks)="" (2)="" delete="" the="" information="" from="" the="" database="" about="" trip="" number="" 53.="" 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="" 24="" trips="" to="" be="" on="" leave.="" (2="" marks)="" (4)="" copy="" information="" about="" all="" employees="" born="" before="" the="" year="" 2000="" to="" a="" new="" table="" s20cent.="" there="" is="" no="" need="" to="" enforce="" any="" consistency="" constraints="" on="" the="" new="" table.="" (2="" marks)="" (5)="" explain="" how="" the="" data="" definition="" languate="" (ddl)="" statements="" of="" sql="" are="" used="" in="" data="" management="" and="" security.="" use="" original="" examples="" including="" sample="" code="" that="" you="" make="" up="" yourself="" to="" illustrate="" your="" answer.="" (2="" marks)="" submit="" your="" answer="" as="" files="" named="" question3.sql="" and="" question3.pdf="" using="" the="" templates="" 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="" may="" lose="" marks.="" 4="" 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="" 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’="" grants="" 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="" two="" (2)="" of="" the="" users,="" and="" the="" role="" ‘admin’="" to="" the="" all="" 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="" ten="" (10)="" maximum="" concurrent="" connections.="" finally,="" the="" user="" ‘root’="" locks="" all="" the="" user="" accounts="" created="" in="" the="" previous="" step.="" (1="" mark)="" (8)="" explain="" in="" your="" own="" words="" the="" difference="" between="" authentication="" and="" authorisation.="" use="" original="" examples="" that="" you="" make="" up="" yourself="" and="" diagrams="" that="" you="" draw="" yourself="" to="" illustrate="" your="" answer.="" (3="" marks)="" add="" your="" code="" into="" the="" question4.sql="" template="" provided="" and="" output="" your="" report="" file="" on="" your="" virtual="" machine="" to="" a="" file="" named="" question4.rpt="" add="" your="" name,="" student="" number="" and="" the="" date="" to="" the="" comments="" section="" of="" your="" sql="" script.="" submit="" your="" answers="" as="" files="" named="" question4.sql,="" question4.rpt="" and="" question4.pdf,="" use="" the="" templates="" provided.="" 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="" may="" lose="" marks.="" end="" of="" assessment="" page="" 1="" of="" 6="" page="" 1="" of="" 6="" page="" 1="" of="" 6="" student="" number:="" student="" name:="" question="" 1="" (10="" marks)="" page="" 2="" of="">
Answered 4 days AfterJan 20, 2023

Answer To: DPIT115 Data Management and Security Final Assessment (Summer 2022) QUESTION (10 marks)...

Shweta answered on Jan 24 2023
31 Votes
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here