Database Management

ICT5

Database Administration (DBA)

The DBA’s tasks will include the following:1. 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.

2. Keeping users informed of changes in the database structure that will affect theme.g. if the size or format of a particular field is altered or additional fields added.

ICT5

DBA (continued)3. Maintenance of the data dictionary for the database

and responsibility for establishing conventions for naming tables, columns, indexes and so on.

4. Implementing access privileges for all usersspecifying which items can be accessed and/or changed by each user.

5. Allocating passwords to each user

6. Providing training to users in how to access and use the database

ICT5

The data dictionaryThe data dictionary is a ‘database about the database’.It will contain information such as: Which 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 readthe data or change it

ICT5

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.

1. Data storage, retrieval and updateThe 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 recoveryThe DBMS must provide the ability to recover the database in the event of system failure.

5. SecurityThe DBMS must handle password allocation and checking, and the ‘view’ of the database that a given user is allowed.

ICT5

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 “mail shot”, for example

ICT5

Querying the database e.g. Query by Example from the Northwind Database

ICT5

Structured Query Language (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(s) or relation(s) in the from clause.The where clause specifies the condition that must be met for items to be selected.

ICT5

SQL Example For example, to find all the female students in

the STUDENTS table seen before, we could enter: SELECT tblStudents.StudentID, tblStudents.Surname, tblStudents.Forename, tblStudents.DateOfBirth, tblStudents.SexFROM tblStudentsWHERE (((tblStudents.Sex)="F"));

ICT5

Querying the database e.g. Query by Example from the Northwind Database in SQL view

SELECT [Product List].ProductID, [Product List].ProductNameFROM Products AS [Product List]WHERE ((([Product List].Discontinued)=No))ORDER BY [Product List].ProductName;

ICT5

Querying the database e.g. A more complicated query from Northwind Database

SELECT DISTINCTROW Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, [FirstName] & " " & [LastName] AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice, Orders.Freight

FROM Shippers INNER JOIN (Products INNER JOIN ((Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Shippers.ShipperID = Orders.ShipVia;

ICT5

Using indexes

A DBMS will allow the application developerto 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.

ICT5

Indexes – example For example, suppose the following records were added in the

sequence shown by the record number:Record # Student ID Surname Firstname

1 5321 Bates Joseph2 1963 Scully Anne3 4218 Chatterjee Sara4 3727 Fidler Lillian5 2858 Deacon Michael

If the Student ID field is indexed, the index will have entries as follows: Student ID Record #

1963 22858 53727 44218 35321 1

ICT5

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.

ICT5

Client-Server Database Modern databases often have to operate on

a network This would be a client / server operation DMBS server software processes requests

from DBMS client software on network workstations

Process results can be sent from server to client for further processing if required

ICT5

Client-server database advantages

An expensive resource available to large number of users Client stations can, with permission, update the server

database Consistency is maintained because only one copy of

database (on server) Client sends query, server does processing, results

returned to client Communications time between server and client is

minimised because only results are sent back to client Appropriate programs and report formats can be held on

client workstations for particular departments