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...

DPIT115 Data Management and Security Final Assessment (Summer 2022)

QUESTION 2 (10 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 modifications may require more than one SQL data definition statement.

1. 
   

   
   (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. 
   

   
   (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. 
   

   
   (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. 
   

   
   (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. 
   

   
   (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.

Page

4

of

6

DPIT115 Data Management and Security Final Assessment (Summer 2022)

QUESTION 3 (10 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. 
   

   
   (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. 
   

   
   (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. 
   

   
   (3) Change the status of all the drivers who have performed more than
   
   24
   
   trips to be
   
   ON LEAVE
   .
   
   

   
   

   
   (2 marks)
   
   

   
   


4. 
   

   
   (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. 
   

   
   (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.