9. Database Systems (in practice) – 3marks

Question 1

The three schema architecture involves the levels of:

Answer 1

• External (end user view)
• Conceptual (mapping)
• Internal (how data is mapped and stored in the DB)

Question 2

The three main DBMS languages used by SQL are:

Answer 2

Acronym to remember the three groups: DMC
• Data Definition Language (DDL): Provides commands to create and modify database structure and constraints.
• Data Manipulation Language (DML): Provides commands to insert, delete, update and retrieve
• Data Control Language (DCL): Provides commands to control the access of data

Question 3

Pro’s of DBMS’s include:

Answer 3

• Separation of data from applications
• Push-down common functions (general-purpose systems!)
• Separation of physical structures and logical structures
• Relational model and theory
• Non-procedural query language
• Concurrency control and recovery
• High performance query processing

Question 4

Cons of DBMS’s include:

Answer 4

• The Closed-World assumption
• A piece of software, independent of hardware platforms (for too long!)
• A victim of its own success (extensions not well supported)
• Limited data types

Question 5

Characteristics of database approach are:

Answer 5

• Insulation between programs and data
• Support of multiple views of data
• Use of a catalog to store schema

Question 6

Three schema architecture was proposed as it provided two types of data independence, being:

Answer 6

• Logical Data Independence

* Physical Data Independence

Question 7

Describe the two types of Data Independence?

Answer 7

LOGICAL - The capacity to allow independent changes on conceptual schema without having to change external schemas or the application programs that access the database via the external schemas
PHYSICAL - Capacity to change internal schema without having to change the conceptual (or external) schemas

Question 8

A System Catalog does what?

Answer 8

Stores database definitions (metadata) which describe the structure, type & constraints of the data

Question 9

Semantic integrity constraints can be created, such as:

Answer 9

• triggers (SQL CREATE TRIGGER)

* assertions (SQL CREATE ASSERTION)

Question 10

Assertions involve:

Answer 10

• Keys, entity constraints and referential integrity are structural constraints that are managed by the DBMS

Question 11

Triggers involve:

Answer 11

• Like Assertions, Triggers are also stored in the database, and hence managed by the DBMS
• However, rather than aborting an operation because of a constraint violation, the DBMS, through Triggers, can make an alternative option available