INTRODUCTION TO DATABASE APPLICATIONS

INTRODUCTION TO DATABASE APPLICATIONS

INTRODUCTION TO DATABASE APPLICATIONS

Definitions

·         File or table is a group of related records

·         File are classified according to (1) application, (2) type of data they contain or (3) permanence

·         Application: payroll file, inventory file etc.

·         Data type: document file, graphical image file etc.

·         Permanence: master file (monthly, yearly), and transaction file (daily, weekly)

Database is an integrated collection of logically related data elements

It’s a single, large repository of data which can be used simultaneously by all departments and users of an organisation.

·         Consolidates file previously stored in separate files into a common pool of data elements

·         Data stored in a database are independent of (1) application programs using them and (2) type of storage devices on which they are stored

Management of data as a resource

Database management approach (DBMA)

·         Development of databases and Database management software is the foundation of managing organisational data.

·         DBMA consolidates data records and objects into databases that can be accessed by many different application programs

·         In DBMA, an important software package called Database Management System (DBMS) serves as a software interface between users and database

·         DBMS helps users easily access the records in a database

·         Database Management Involves use of DBMS to control how databases;

o   Are created

o   Interrogated and

o   Maintained to provide information needed by users and organisations

·         Example, customers records in banking are needed for several applications such as check processing, Automated Teller systems, bank credit cards, savings accounts, and instalment loan accounting. These data can be consolidated into one database

 

DBMA activities are three

·         Updating and maintaining common databases to reflect new business transactions and other events requiring changes to an organisation’s records (operational applications)

·         Providing information needed for each end user’s application programs that share the data in common databases

·         Providing an inquiry/response and reporting capability through DBMS software so that end users can use web browsers and the internet or corporate intranets to easily interrogate databases, generate reports and receive quick responses to their ad hoc requests for information (analytical applications).

Functions of DBMS

·         Database development

o   Database management packages like MS Access or Lotus, allow end-users to easily develop the database they need

o   In large organisations database development is vested in hands of Database Administrators and specialists. This improves the security and integrity of the organisational databases

·         Database Interrogation

o   Database interrogation ability is the major benefit of database management systems

o   End users can use a DBMS by asking from a database using a query language or a report generator. They can receive immediate response in the form of video displays or printed reports

·         Database Maintenance

o   Databases of organisations need to be updated continuously to reflect new business transactions and other events

o   This database maintenance process is accomplished by transaction processing programs and other end user application packages with support of DBMS

·         Application development

o   End users, system analysts and other application developers can use internal 4GL programming languages and inbuilt software development tools provided by many DBMS packages to develop custom application programs

o   Example, you can use DBMS to easily develop data entry screens, forms, reports, or web pages of a business application

 

Commercial DBMS include;

·         Oracle

·         MS-SQL server

·         IBM DB2

·         MS-Access

·         IMS

·         Sybase

·         PostgreSQL

 

Advantages of DBMS software

·         Reduced data redundancy

o   Data redundancy means that the same data files appear in many different files and often in different formats

o   In a file-based system, separate files tend to repeat the same data over and over again

o   In database approach same information is available to different users

·         Data integrity

o   Data is accurate, consistent and up to date

o   In DBMS reduced data redundancy increases chances of data integrity

·         More programs independence

o   With files-based management file systems, different files were often written by different programmers using different file formats, hence programs dependence

o   DBMS programs and files formats are the same, thus one or several programmers can spend less time in maintaining files

·         Increased user productivity

o   DBMS are fairly easy to use i.e. users can get their requests without having to resort to technical manipulations

o   Users don’t need wait for professionals to provide what they need

·         Increased security

o   Although various departments may share data in common, access to specific information can be limited to selected users. E.g. through the use of passwords, a student financial, medical and grade information in a university database is made available to legitimate individuals

 

Disadvantages of DBMS software

·         Cost issues

o   Installing and maintaining a database is expensive particularly in large organisations

o   Users training costs

·         Security issues

o   Possibility of unauthorised user to get past the safeguards

o   If successful, they access all files not few

o   Natural disasters e.g. earthquakes, fire etc

o   Theft

o   Hardware or software problems

o   Advise: make backup copies of files and store them elsewhere

·         Privacy issues

o   DB may hold information they should not and be used for unintended purpose, perhaps intruding people’s privacy

o   E.g. when medical data are used to evaluate the employee for a job promotion

o   Manipulation of sound, photos or video

File organization and Data structures

A file-based system

·         Is a collection of application programs that perform services for the end users such as accounting packages, personnel management, report generators etc

·         Each application program defines and manages its own data

·         Departments own their respective files

·         Predecessor of the database

File organisation;

·         A sequential file: records have been arranged into ascending or descending order of a key field.

·         When new records are added the sequence is maintained in respect of magnetic tape by the insertion of records in the correct sequence in a new reel of tape.

·         It is necessary to generate a completely new tape when applying file amendments because it is not possible to physically insert or delete them on the same tape

·         On a disk file when records cannot be stored in the correct location on a track, the logical sequence of records, not the physical one, can be maintained using pointers and an overflow area.

 

Advantages of sequential file:

·         Very efficient when a majority of the records are updated

·         Files easy to design

·         Can be stored on an inexpensive storage media such as magnetic tape

·         Old master file and transaction files are automatic back-up files since updating produces a new file

 

Disadvantage of sequential file:

·         If only a few records need updating, entire file must still be processed and a new file written

·         Master files must be sorted into same key field sequence

·         Transaction files must be sorted into same key field sequence as master files they are updating

·         Files are only current immediately after an update

·         Records cannot be accessed directly 

·         Sequential organisation

A Direct file: each record, as with sequential file, contains a key field. However, records need not appear on the file in a key field sequence

·         Any record stored on a direct file can be accessed if its location or its address is known.

·         Previous records need not be accessed

·         Each record on a file is assigned to a storage address

·         Not used for processing an entire file, but where a limited number of records need to be accessed quickly and directly as the case of online enquiry

·         Used by airlines to obtain up-to-date flight information quickly and directly

·         Generally maintained on magnetic disks, optical disks (when the file is static), or on mass storage systems

 

Advantage of direct files;

·         Terminals can be used to update files online as they occur, keeping the file current

·         No need for separate transaction file as transactions are processed as they occur

·         Any record can be accessed in milliseconds

·         Not necessary to read entire file to update it; only master records to be updated need be accessed

·         Several files can be updated at the same time as transactions occur

·         Files do not have to be sorted into key field sequence

 

Disadvantage of direct file:

·         Back-ups must be created because updating destroys old records

·         Appropriate algorithm/address often difficult to devise

·         Accidental destruction a problem

·         Security difficult to control because many users have access

·         More storage space may be required than for a sequential file

·         Does not facilitate sequential processing

·         Hardware and software requirements greater than for sequential files

 

An indexed file: records are stored on the file in sequential order according to key field.

·         Offers the simplicity of sequential file processing as well as a capability for direct access

·         As the records are recorded, the system establishes one or more indexes to associate the key field value(s) with the storage location of the record on the file

·         The system then uses indexes to access desired records directly

·         Indexes function in the same way as book indexes, that is in order to access a record directly indexes are searched for the key value(s) of the desired record, which provide the record to be accessed from the file

·         Indexes files are maintained on magnetic disk, optical disk (static files), or on a mass storage system

 

Advantages of indexed file:

·         Well suited for applications requiring both sequential and direct access

·         Access to specific records faster than with sequential files

·         Use of indexes eliminates need for an algorithm as is required with the direct files

Disadvantage of indexed file:

·         Storage requirements greater than with sequential files since space is required for the indexes

·         Random access is slower than with a direct file

·         Require more complex hardware and software than needed with sequential files

 

Differences between direct & indexed files

·         Records may be accessed randomly on a directly organised file, whereas records may be accessed sequentially or randomly from an indexed organised file

·         Direct-organised files utilise algorithm to determine the location of a record, whereas indexed organised files utilise indexes to locate a record to be randomly accessed

o   An algorithm is a mathematical method devised to convert the key field value into an address

Examples of files

Payroll master file; typical contents are

·         Clock number

·         Name

·         Tax code

·         National Insurance number

·         National Insurance category

·         Taxable gross to date

·         Tax to date

·         Taxable gross previous employment

·         Holiday credit to date

·         Sickness holiday credit weeks to date

·         Employee’s national insurance to date

·         Total National Insurance contributions to date

·         Fixed deductions; charities, overalls, savings, loans

·         Weakly salary amount (as appropriate)

·         Hourly rate (as appropriate)

·         Holiday credit flat rate

·         Employee bank details

 

Customer file: typical contents

·         Account number

·         Name and address

·         Credit limit

·         Account balance

·         Category/discount rate

·         Age analysis of account balance

 

Customer sales history file: may be structured as

·         Account number

·         Representative code

·         Customer type

·         Area code

·         Turnover this period – analysed by product

·         Turnover to date since week 1- analysed by product

·         Number of order to date since week 1

·         Discount received to date

·         Cost of sales (if appropriate)

·         Profitability (if appropriate)

 

Product file: could be structured as follows

·         Product code

·         Product description

·         Pack size

·         Price

·         VAT code

·         Recommended retail selling

·         Location code

·         Quantity in stock

·         Reorder level

·         Reorder quantity

·         Ordered but outstanding

 

Stock file: row materials or components

·         Stock number

·         Description

·         Location in stores

·         Price

·         Actual stock

·         Control parameters; reorder level, maximum stock level, minimum stock level

·         Used on

·         Quantity: free stock

·         Quantity allocated stock

 

Orders file: may be structured as follows

·         Order number

·         Account number

·         Product code

·         Quantity

·         Customer name (online)

·         Representative code (online)

·         Customer type (online)

·         Further product may be added from the product file

Plant register file - Contents of this file provide information for asset accounting requirements, giving details relating to specific items of plant equipment

·         General information

o   Type of asset

o   Plant code

o   Supplier

o   Manufacturer

o   Rating (horsepower or kilowatt rating)

o   Floor area

o   Date of installation

o   Location

o   Transfers

·         Accounting information

o   Date of purchase

o   Original cost

o   Installation cost

o   Depreciation class

o   Annual amount of depreciation

o   Cumulative depreciation

o   Written down book value

o   Maintenance cost

o   Cost of additions

o   Disposal value

 

DATA STRUCTURES OR MODELS

Structure of data in a database are either (1) physically or (2) logically arranged

·         Physical data structure refers to the physical arrangement of data on a secondary storage device

·         Physical structure is the concern of the specialist who designs Database Management systems (DBMSs)

·         Logical data structure concerns how the data ‘seems’ to be arranged and the meanings of the data elements in relation to one another

·         The concern of users and programmers

·         Database management system packages are designed to use a specific data structure to provide end users with quick, easy access to information stored in the database

 

Types of logical data structures

Logical Data structures or models are generally defined in terms of schema

Schema is a conceptual view of the logical relationships between the data elements in the database

Includes: names of major elements, their attributes, and logical relationships between them

 Hierarchical structures, also called tree structure

·         All records are dependent and arranged in multilevel structures consisting of one root record (parent) and any number of subordinate levels i.e. one to many relationship

·         Any data element can be accessed by moving progressively downward from a root and along the branches of the tree until the desired record is located

·         Application programs process hierarchical databases one record at a time

·         Limitation;

o   Was a natural model for the databases used for structured, routine types of transaction processing

o   Records with multiple relations or those that lack hierarchical relations cannot be represented by this model. E.g. employees from more than one department can work on more than one project

 

Network structure

·         Similar to the hierarchical structure except that in this structure a node may have more than one parent

·         Can represent more complex logical relationships and it is still used by some mainframe DBMS package

·         It allows many to many relationships among records i.e. the model can access data by following one of several paths. E.g. departmental records

·         More flexible than hierarchical model in support of databases for many types of business operations

·         Limitation

o   The model cannot easily handle ad hoc requests formation because relationships must be specified in advance just like the hierarchical model

 

Relational structure

·         Most popular of the three database structures

·         Used by most microcomputer DBMS packages as well as by most midrange and mainframe systems

·         Organises data in terms of dimensional tables

·         All data elements within the database are viewed as being stored in the form of a simple table.

·         Database management systems such as Access create new tables of data relationships using parts of the data from several tables

·         Allow users to easily receive information in response to ad hoc requests

·         Relational databases are easier for programmers to work with  and easier to maintain  than hierarchical and network models

·         Limitation

o   Relational database management systems cannot process large amounts of business transactions as quickly and efficiently like other models

0 comments for "INTRODUCTION TO DATABASE APPLICATIONS"

Back To Top