ICT A2 ICT5

1
ICT A2 ICT5

• Chapter 57
• Database Management

2
Database Administration (DBA)

• The DBAs tasks will include the following
• The design of the database.
• After the initial design, the DBA must monitor
  performance and, if problems surface, changes
  must be made to the database structure.
• Keeping users informed of changes in the database
  structure that will affect them
• e.g. if the size or format of a particular field
  is altered or additional fields added.
• Maintenance of the data dictionary for the
  database
• and responsibility for establishing conventions
  for naming tables, columns, indexes and so on.
• Implementing access privileges for all users
• specifying which items can be accessed and/or
  changed by each user.
• Allocating passwords to each user
• Providing training to users
• in how to access and use the database.

3
The data dictionary

• The data dictionary is a database about the
  database. It will contain information such as
• What tables and columns are included in the
  present structure
• The names of the current tables and columns
• The characteristics of each item of data, such as
  its length and data type
• Any restrictions on the value of certain columns
• The meaning of any data fields that are not
  self-evident e.g. a field such as course type
• The relationships between items of data
• Which programs access which items of data, and
  whether they merely read the data or change it.

4
Database Management System (DBMS)

• The DBMS is an application program that provides
  an interface between the operating system and the
  user in order to make access to the data as
  simple as possible. It has several other
  functions as well, and these are described below.

5
Database Management System (DBMS)

• 1.   Data storage, retrieval and update
• The DBMS must allow users to store, retrieve and
  update information as easily as possible, without
  having to be aware of the internal structure of
  the database.
• 2.   Creation and maintenance of the data
  dictionary
• 3.   Managing the facilities for sharing the
  database
• The DBMS has to ensure that problems do not arise
  when two people simultaneously access a record
  and try to update it.
• 4.   Backup and recovery
• The DBMS must provide the ability to recover the
  database in the event of system failure.
• 5.   Security
• The DBMS must handle password allocation and
  checking, and the view of the database that a
  given user is allowed.

6
Querying the database

• Different database systems all have their own way
  of performing queries to extract data. However
  all perform similar functions, allowing the user
  to
• Combine into one table the information from two
  or more related tables
• Select the fields to be shown in the Answer
  table
• Specify criteria for searching on
• e.g. find the names and addresses of all club
  members whose subscriptions are due
• Save the query so that it can be executed
  whenever necessary
• Save the Answer table so that it can be
  displayed or used as the basis for a report or a
  mailshot, for example

7
SQL

• The basic retrieval facility in SQL is the select
  statement, which consists of three clauses in the
  general form select.... from .... where
  .... 
• The select clause specifies columns to be
  extracted from the table or relation in the from
  clause. The where clause specifies the condition
  that must be met for items to be selected.

8

• For example, to find all the female students in
  the STUDENT table given below we could
  enter select Student-name from
  STUDENT where Sex 'F' STUDENT Student-name
  Student ID Date-of-birth Sex Heathcote,R 1234
  5 20-08-73 M Head,J 22433 13-02-73 F Hargrave,
  R 66688 13-09-54 M Daley,C 87656 24-12-72 M

9
Using indexes

• A DBMS will allow the application developer to
  create an index for any field in the database,
  whether or not that field is unique.
• All the indexes are then held in memory while the
  database is open to allow fast retrieval of data.
• For example, suppose the following records were
  added in the sequence shown by the record number
• Record no. Student ID Surname
  Firstname 1 5321 Bates Joseph 2 1963 Scully
  Anne 3 4218 Chatterjee Sara 4 3727 Fidler Lil
  lian 5 2858 Deacon Michael
• If the Student ID field is indexed, the index
  will have entries as follows Student
  ID Record number 1963 2 2858 5 3727 4
  4218 3 5321 1

10
Why index?

• Indexing a particular field will speed up access
  to data, but will slow down record updating since
  every time an indexed field is changed, the index
  entry will have to be changed as well.
• When a new record is added, all indexes have to
  be updated. It makes sense, in general, to index
  any foreign keys in a table, and any fields like
  surname or department that you frequently need in
  alphabetical order in a report.
• It is also a good idea to index fields that you
  often use in a query criteria e.g. to quickly
  find all club members whose subscriptions are
  overdue, the Due Date field needs to be indexed.
• Indexed fields other than the primary key field
  are known as secondary keys.

11
Exercises

• Use a select clause to find all the lecturers on
  salary grade 13 in the LECTURER table below.
• LECTURERLec-name Payroll-number Department Sala
  ry-gradeGlover,T T345267 Computing 13Reader,
  B T773351 Humanities 23Newman,P
  T666758 Business 12