Databases and Database Management Systems (Dbms)

Databases and Database Management Systems (DBMS)

A Database

There are many definitions of a database

An organised collection of related data

An organised collection of files in a specific subject area

A filing cabinet is a database. An address book or telephone directory is a database. However, we are concerned with computerised databases, that is files that are stored in an organised way in a computer using a database software application

Database Management System (DBMS)

Special software for managing a database which allows users to record, store, query, change and update the data stored in the database. It also allows the data to be manipulated in many ways using queries, forms, reports etc

Database Models

The database model is to do with how the data is organised and the relationships between different data items in the database. This is transparent to the user but important for the person who organises the database. Access is a relational database model. This means that it stores data in tables (or relations as they are correctly called!). Relationships are made between tables and in this way we can access data that is stored in a number of tables in a single query. Other database models are the Network model, the Hierarchical Model and the Object-oriented model. We are only concerned with the relational model

Data Structures

We record data about objects that we see around us in the real world. An object might be a person, place or in fact anything that we can describe. The object we describe is called an 'Entity'.

Fields are used to describe the entity ie for the entity Customer we might have the customer address, name, telephone no etc and these will all be stored in fields. These items of information are 'attributes' that describe our entity

Data is organised according to fields, records, files

Fields hold the attributes

Each field in the same table must contain the same type of data

A collection of fields about a certain object is called a record

A record stores all the attributes on a single entity (Customer)

A group of related records is called a file so in our relational database each of the tables represents a 'file'

A Database

This is a collection of files within an organisation

Flat File Organisation

Until the advent of DBMS most organisations previously used a flat file system (until the 1970's). A flat file system meant each section of the organisation maintained its own filing system. There was no relationship between the methods used in different department or units in an organisation. It meant that often the same data was recorded in different departments leading to anomalies and waste of resources. It was often difficult to access information and there were many different programs used so often the data could not be linked

Result

• DUPLICATION ofdata - data redundancy
• RESOURCEWASTAGE -computer and human as the same data was stored and maintained in a number of different places in the firm
• ACCESSTOINFORMATION - difficult as it was spread all over the organisation
• DIFFERENTSTANDARDS within the same company. The same customer name might be recorded differently in 2 different files thus giving the impression that there were 2 customers instead of just one
• NORELATIONSHIP between different databases in the organisation. This integration of datadifficult
• Increased opportunity for error. If two files must be updated instead of one there is a greater chance for error

DBMS

A DBMS solves many of the problems mentioned above. Data is recorded only once in the database thus it only needs to be updated in a single location

• Easier access to the data in the database
• Through use of relationships there is greater data integrity
• Data can be shared easily between different departments
• Better data security as it is held in one place

Setting up a Database

The steps in creation of a database are

Designing the structure

Inputting the data

Database Terminology

Entity

This is the object, person, place or thing on which we wish to record data eg Student, Grant, Course

Attribute

This is something that describes an entity. If the entity is a student then some of the student's attributes would be Name, Address, Student No, Tel No, Course, etc

Field

This is the location in the database in which we record the attributes. The database designer decides on the fields when the database is being constructed initially. All fields with the same name should contain the same time of data. A field can contain numeric, alphabetic or logical data to name but some. A field is just a location in which the database holds data.

Record

A record contains all the data we have recorded on a certain 'entity'. Each Row in a table represents a Record

Structure Design

Involves describing what data is needed to be recorded in our database

1Decide on field names

2Determine field typewhich can be any of the following

(i)Text

(ii)Number

(iii)Data

(iv)Logical

(v)Memo

(vi)Date

3Determine field width

4Define key fields (The Primary Key uniquely identifies a record)

5The desired structure is set up using the software (MsAccess etc)

6Once the database structure has been decided and set up, data can be input

Features ofa DBMS

The DBMS makes it easy for the user to carry out the following tasks

List records (all or selected)

List only certain fields

Query - request data from the database

Create reports

Sort records

Update records

Add/Delete records

Advantages of DBMS

The DBMS is the user interface. It also has the following advantages over other methods of storing data

Reduced data redundancy

Easy retrieval of data

Easy update of data

Integrated data - so reports can be generated easily

Data integrity

Allows connectivity - over a network

Allows different views

Data security easier to implement

A DBMS allows the whole organisation to use a company-wide database. This is called a Management Information System. Information is available to the members of the organisation where and when they need it.

Database Models

Not all DBMS use the same methods to organise and store data. Different models used are:

Hierarchical

Network

Object-oriented

Relational

The models differ in how they organise the data. This is often transparent to the user

Relational Database Model

The logical view of the relational model is as follows:

• Tables (Relations) with rows and columns. A column represents a field and a row represents a record
• The complete set of tables (relations) is a file
• A number of these files make up the company database

The Relational Model Structure

Let us take a small example of the College Database. This is an oversimplification of the college database but it should do to illustrate the concepts. Here we see 3 files - the student file, the Course file and the Grants file.

Each file is shown as a Table

Tables in a relational database are called 'relations'

Links can be made between tables by linking fields that contain the same type of data

Above is an extract of what the college Student File might look like.

Field Contents or 'Attributes'

Field Names eg Grant_Status and Student_ID, Street, Town etc

Here is what the Grants file might look like. We record whether or not each student is in receipt of a grant

Below we have the Course table that shows what each course code means

These are just 3 of the many possible files that will make up the college database. Realistically it could have any number of files each representing an entity on which we have to maintain records

Advantages of Relational Database Model

Ease of update and retrieval of data

More information from same data (we can join tables to perform a query)

Data sharing is made easier (distributed databases)

Enforcement of standards

Controlled data redundancy (sometimes we need to repeat a field in another table so that we can create a relationship between the tables. This is OK)

Consistency of data (use of input masks and data formats to ensure consistency)

Data Integrity (enforcing integrity rules)

Security (data in one location easier to secure)

Disadvantages

Size

Complexity

Cost

Backup procedures (Since data only in one place it would be critical if data is lost. Good backup procedures necessary)

Additional hardware and software requirements

Higher impact of failure

Recovery more difficult

Una DooneyPage 1 of 5Databases and DBMS