Question 1

The ANSI-SPARC Architecture, where ANSI-SPARC stands for American National Standards Institute, Standards Planning And Requirements Committee, is an abstract design standard for a Database Management System (DBMS). The architecture has three distinct layers in its description and design. Most modern commercial database systems are based on this architecture.

(i) This layered representation offers several benefits to database designers and users. Identify and describe four key benefits of this architectural approach.

(ii) A Database Administrator makes the following changes to application programs within a database. Explain the changes required to the various components within an ANSIâ€“SPARC architecture and explain how they are affected by the following changes:

a. An application program is modified so it provides alternate ways of selecting data defined in its user schema.

b. An application program is modified so that it additionally can store data for a column of a table that is not defined in the logical schema.

c. An application program is modified so that it no longer requires a column of a table that is defined in the logical schema.

(iii) Briefly describe the meaning of the terms: Integrity Independence and Distribution Independence.

Question 2

A) The following questions relate to administrating a database (you have ADMIN rights). You are to assume the tables â€˜customerâ€™ and â€˜staffâ€™ exist within a database named â€˜companydbâ€™ and are populated with data. The users â€˜smithâ€™, â€˜jonesâ€™ and â€˜clearâ€™ exist but have no current privileges, all activities are undertaken in one ADMIN session. Issue MySQL / MariaDB style commands that:

(i) Give â€˜smithâ€™ (with PASSWORD â€˜hammerâ€™) the ability to perform all functions on the â€˜customerâ€™ table and â€˜jonesâ€™ (with PASSWORD â€˜clawâ€™) SELECT rights on â€˜customerâ€™ table only.

(ii) Give â€˜clearâ€™ rights to update and insert data on the â€˜staffâ€™ table only.

(iii) Prevent â€˜smithâ€™ from updating or altering the â€˜customerâ€™ table only.

(iv) The table â€˜staffâ€™ has become fragmented and requires action to remedy this, issue the necessary command to achieve this.

(v) The table â€˜customerâ€™ has become damaged and requires action to remedy this, issue the necessary command to achieve this.

(vi) The table â€˜staffâ€™ requires a new attribute to record the name of their â€˜departmentâ€™, issue the command to achieve this.

(vii)

A) frequent query is run against â€˜customerâ€™ table to search for customers based on their postal code. Provide commands that enable modification of this attribute to enhance and optimise the search and retrieval of records based on the â€˜postal_codeâ€™ attribute.

B) There is a requirement for a new database â€˜rentalsdbâ€™. Assume this is a new ADMIN session. Issue MySQL / MariaDB style commands that: (i) Create the â€˜rentalsdbâ€™ database

Question 3

Business intelligence (BI) is the use of computing technologies for the identification, discovery and analysis of business data - like sales revenue, products, costs and incomes. These technologies and functions are often referred to as information management systems.

(i) Describe reasons why an enterprise would deploy Business Intelligence Systems
(ii) Describe three broad categories of Business Intelligence Systems
(iii) What are the potential issues with the deployment of Business Intelligence Systems?

Question 1 The ANSI-SPARC Architecture, where ANSI-SPARC stands...