Q11. List and discuss 5 areas where threats to data security may occur.
i. The operating system
Hackers may check whether a given operating system has its defult settings intact. Once they realise that the operator has not changed any of the settings, they will find it very easy to launch an attack. A hacker may therefore use the vulnerable OS to transmit sensitive data to some third parties or perform some functions in the system that can easily compromise the security of the data.
ii. Web server
They are responsible for running services that gather user requests and then respond to the requests which are made by users through the web browsers that they use. If there are flaws and vulnerabilities in a web server, unauthorised users may gain some remote access to the operating system thus jeopardizing the operation of the operating system.
iii. The server-side technologies
iv. Database Server
The database servers are often targeted at different levels. The database server can be easily targeted and therefore the database administrators should ensure that security measures are taken in order to ensure data security.
v. Application servers
Most hackers use this section of the computers to attack the system. Launching an attack through the Application servers is very easy and therefore commonly used by several hackers to accomplish their mission.
Q12. Explain how creating a view may increase data securtiy. Also explain why one should not rely completely on using views to enforce data security.
Views helps in increasing data security by hiding sensitive data from the end user. Only some limited part of the data in the database are exposed to the end user.
When using the views, the most sensitive columns are not exposed to to the end user. This helps in improving the security of the sensitive data in the tables. However, a person should not rely entirely on the use of views to enforce data security since they are very slow. Their speed is usually the same as the speed of the query used to define them. The performance of the views can be further degraded if the views are based on other views. Therefore in order to achieve an optimal performance of the views, they should all be created against base tables in the database.
Q13. List and briefly explain how integrity controls can be used for database securtiy.
Integrity control involves keeping data consistent and correct through a means of controls that the database administrators may put on the database. Integrity controls ensures that only those people who have priviledged accounts can load data into the database. Usually there are some rules on the table that needs to be followed whenever a certain type of data is to be inserted into the database.
Q13.What is the difference between an authentication scheme and an authorization scheme?
Authentication scheme involves the actions and the laid procedure that is used to verify the person accessing the system. In most cases the the PAM is used as a low-level authentication scheme while the authorisation scheme is used to verify what a person is allowed to do in the system. Authorisation usually occurs after a succeful authentication has taken place.
Q21. What is transaction integrity? Why is it important?
Transaction integrity refers to the degree to which a transaction flowing through a network reasches the intended destination without its functions , contents or meaning being impaired. Transaction integrity is important as it ensures that information has not been modified in any way.
Section C Q2. CREATE INDEX myIndex ON my Table (myColumn).
What type of index will get created after executing the above statement?
Index with included columns
Section C Q3. When processing transactions the ACID properties are used. Describe what the acronym ACID means and briefly explain them using examples.
ACID means atomicity, consistency, isolation, and durability
i. Atomicity – this attribute involves performing all changes to data as if they are a single operation. An example of atomicity involves a transaction like transfering data from one account to another, one account will be credited while the other one will be debited. All these operations are done simultaneously
ii. Consistency – a transaction can besaid to be consistent if the attributes of the transaction are maintained throughout the transaction (starting and ending). An example of consistent data can be during a transaction, the total values in both accounts should be the same when the transaction starts and when it ends.
iii. Isolation
This is an attribute that involves the intermediate state of a transaction being invisible to other transactions in the system. An example of an isolation case my involve during the process of a transaction, a third party may see the transfered funds in only one of the accounts and not in both or in neither.
iv. Durability
This involves maintaining changes to data even if a system fails once a transaction has succesfully completed. An example of a case of durability may involve a situation when a transaction is done. The durability property will ensure that changes which are made to each account will not be reversed even in an event of system failure.
Section D Q1. Please see the uploaded picture. You need to answer questions A, B and C.
Part A
Indexes recommended for the query
Clustered – the data in the tables are sorted and arranged in a table
Index with included columns – the data are arranged in a table.
Reason:
Part B
Commands required to create the indexes
>>-CREATE INDEX--+------------------+--index-name FOR TEXT ON--->
'-index-schema-"."-'
>--+------------------+--table-name----------------------------->
'-table-schema-"."-'
>--+-(text-column-name)--------------------------------------------------+-->
'-(--+-----------------------+--function-name-(-text-column-name-)--)-'
'-|function-schema "."|-'
Part C
Commands used to generate the statistics for the product and vendor tables
Create Relational Index
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON
