DATABASES COMPUTER TEACHER NOTES- MICROSOFT ACCESS LATEST

DATABASES.

What is a Database?

• It is a collection of information related to a particular subject or purpose.
• A collection of related data or information grouped together under one logical structure.
• A logical collection of related files grouped together by a series of tables as one entity.

Examples of databases.

You can create a database for;

• Customers’ details. – Library records.
• Personal records. – Flight schedules.
• Employees’ records. – A music collection.
• An Address book (or Telephone directory), where each person has the Name, Address, City & Telephone no.

 

DATABASE CONCEPTS.

 

Definition & Background.

 

A Database is a common data pool, maintained to support the various activities taking place within an organization.

 

The manipulation of database contents to yield information is by the user programs.

The database is an organized set of data items that reduces duplications of the stored files.

 

INTEGRATED FILE SYSTEMS.

 

These refer to the traditional methods of storing files, i.e., the use of paper files.  E.g., Manual & Flat files.

 

• In Integrated file systems, several inter-independent files are maintained for the different users’ requirements.
• The Integrated file systems have the problems of data duplication.
• In order to carry out any file processing task(s), all the related files have to be processed.
• Some information resulting from several files may not be available, giving the overall state of affairs of the system.

 

DATABASE MAINTENANCE.

 

A Database cannot be created fully at once.  Its creation and maintenance is a gradual and continuous procedure.  The creation & the maintenance of databases is under the influence of a set of user programs known as the Database Management Systems (DBMS).

 

Through the DBMS, users communicate their requirements to the database using Data Description Languages (DDL’s) & Data Manipulation Languages (DML’s).

 

In fact, the DBMS provide an interface between the user’s programs and the contents of the database.

 

During the creation & subsequent maintenance of the database, the DDL’s & DML’s are used to:

 

• Add new files to the database.
• Incorporate fields onto the existing records in the database.
• Delete the obsolete (outdated) records.
• Carry out adjustments on (or amend) the existing records.
• Expand the database capacity, for it to cater for the growth in the volume for enhanced application requirements.
• Link up all the data items in the database logically.

 

 

 

 

 

Data Dictionary.

 

All definitions of elements in the system are described in detail in a Data dictionary.

 

The elements of the system that are defined are: Dataflow, Processes, and Data stores.

 

If a database administrator wants to know the definition of a data item name or the content of a particular dataflow, the information should be available in the dictionary.

 

Notes.

 

• Databases are used for several purposes, e.g., in Accounting – used for maintenance of the customer files within the base.
• Database systems are installed & coordinated by a Database Administrator, who has the overall authority to establish and control data definitions and standards.
• Database storage requires a large Direct Access storage (e.g., the disk) maintained on-line.
• The database contents should be backed up, after every update or maintenance run, to supplement the database contents in case of loss. The backup media to be used is chosen by the organization.

 

Data Bank.

 

A Data Bank can be defined as a collection of data, usually for several users, and available to several organizations.

A Data Bank is therefore, a collection of databases.

 

Notes.

 

• The Database is organizational, while a Data Bank is multi-organizational in use.

 

• The Database & the Data Bank have similar construction and purpose. The only difference is that, the term Data Bank is used to describe a larger capacity base, whose contents are mostly of historical references (i.e., the Data Bank forms the basis for data or information that is usually generated periodically).  On the other hand, the contents of the Database are used frequently to generate information that influences the decisions of the concerned organization.

 

TYPES OF DATABASE MODELS.

 

• Relational database model.

 

A Relational database is a set of data where all the items are related.

 

The data elements in a Relational database are stored or organized in tables.  A Table consists of rows & columns. Each column represents a Field, while a row represents a Record.  The records are grouped under fields.

 

~ A Relational database is flexible and easy to understand.

 

~ A Relational database system, has the ability to quickly find & bring information stored in separate tables together using queries, forms, & reports.  This means that, a data element in any one table can be related to any piece of data in another table as long as both tables share common data elements.

 

Examples of Relational database systems;

 

• Microsoft Access.
• FileMaker Pro.

 

• Hierarchical database model.

 

It is a data structure where the data is organized like a family tree or an organization chart.

 

In a Hierarchical database, the records are stored in multiple levels.  Units further down the system are subordinate to the ones above.

In other words, the database has branches made up of parent and child records.  Each parent record can have multiple child records, but each child can have only one parent.

 

Components of Data hierarchy.

 

Databases (logical collection of related files).

 

 

 

Files (collection of related records).

            

Records (collection of related fields).

 

 

Fields (Facts, attributes – a set of related characters).

 

Characters (Alphabets, numbers & special characters or symbols).

• Network database model.

 

A Network database model represents many-to-many relationships between data.  It allows a data element or record to be related to more than one other data element or record.  For example, an employee can be associated with more than one department.

 

DATA BASE MANAGEMENT SYSTEMS (DBMS).

 

• These are programs used to store & manage files or records containing related information.

 

• A collection of programs required to store & retrieve data from a database.

 

• A DBMS is a tool that allows one to create, maintain, update and store the data within a database.

 

A DBMS is a complex software, which creates, expands & maintains the database, and it also provides the interface between the user and the data in the database.

 

A DBMS enables the user to create lists of information in a computer, analyse them, add new information, delete old information, and so on.  It allows users to efficiently store information in an orderly manner for quick retrieval.

 

A DBMS can also be used as a programming tool to write custom-made programs.

 

CLASSIFICATION OF DATABASE SOFTWARE.

 

Database software is generally classified into 2:

 

1. PC-based database software (or Personal Information Managers – PIMs).
2. Corporate-based database software.

 

PC-based database software.

 

The PC-based database programs are usually designed for individual users or small businesses.

 

They provide many general features for organizing & analyzing data.  For example, they allow users to create database files, enter data, organize that data in various ways, and also create reports.

 

They do not have strict security features, complicated backup & recovery procedures.

 

Examples of PC-based systems; 

 

* Microsoft Access.                * FoxPro.

* Dbase III Plus                      * Paradox.

 

Corporate database software.

 

They are designed for big corporations that handle large amounts of data.

Issues such as security, data integrity (reliability), backup and recovery are taken seriously to prevent loss of information.

 

Examples of Corporate-based systems;

 

 

* Oracle.                      * Informix                   * Ingress.

* Progress.                   * Sybase.                     * SQL Server.

 

Common features of a database packages.

 

• Have facilities for Creating
• Have facilities for Updating records or databases.

 

Using a DBMS, you can define relationships between records & files maintained in a database.  In this case, a transaction in one file of the database can also cause a series of updates in parts of other tables.  Thus, the data is input only once to the database and is made available to the many files composing it.

 

• Have facilities for generating Reports.
• Have a Find or Search facility that enables the user to scan through the records in the database so as to find information he/she needs.
• Allow Sorting that enables the user to organize & arrange the records within the database.
• Contain Query & Filter facilities that specify the information you want the database to search or sort.
• Have a data Validating

 

FUNCTIONS OF A DATABASE MANAGEMENT SYSTEM.

 

The DBMS is a set of software, which have several functions in relation to the database as listed below:

 

1. Creates or constructs the database contents through the Data Manipulation Languages.
2. Interfaces (links) the user to the database contents through Data Manipulation Languages.
3. Ensures the growth of the database contents through addition of new fields & records onto the database.
4. Maintains the contents of the database. This involves adding new records or files into the database, modifying the already existing records & deleting of the outdated records.
5. It helps the user to sort through the records & compile lists based on any criteria he/she would like to establish.
6. Manages the storage space for the data within the database & keeps track of all the data in the database.
7. It provides flexible processing methods for the contents of the database.
8. Protects the contents of the database against all sorts of damage or misuse, e.g. illegal access.
9. Monitors the usage of the database contents to determine the rarely used data and those that are frequently used, so that they can be made readily available, whenever need arises.
10. It maintains a dictionary of the data within the database & manages the data descriptions in the dictionary.

 

Note.  Database Management System (DBMS) is used for database;

• Control, and
• Report generation.

 

ADVANTAGES OF USING A DBMS.

 

1. Database systems can be used to store data, retrieve and generate reports.
2. It is easy to maintain the data stored within a database.
   3. A DBMS is able to handle large amounts of data.
   4. Data is stored in an organized format, i.e. under different fieldnames.
   5. With modern equipment, data can easily be recorded.
   6. Data is quickly & easily accessed or retrieved, as it is properly organized.
   7. It helps in linking many database tables and sourcing of data from these tables.
   8. It is quite easy to update the data stored within a database.

 

A database is a collection of files grouped together by a series of tables as one entity.  These tables serve as an index for defining relationships between records and files maintained in the database.  This makes updating of the data in the related tables very easy.

 

9. Use of a database tool reduces duplication of the stored files, and the reprocessing of the same data items. In addition, several independent files are maintained for the different user requirements.

10. It is used to query & display records satisfying a given condition.
11. It is easy to analyse information stored in a database & to prepare summary reports & charts.
12. It cost saving. This results from the sharing of records, reduced processing times, reduced use of software and hardware, more efficient use of data processing personnel, and an overall improvement in the flow of data.
13. Use of Integrated systems is greatly facilitated.

 

An Integrated system – A total system approach that unifies all the aspects of the organization.  Facilities are shared across the complete organization.

 

14. A lot of programming time is saved because the DBMS can be used to construct & process files as well as retrieve data.
15. Information supplied to managers is more valuable, because it is based on a widespread collection of data (instead of files, which contain only the data needed for one application).
16. The database also maintains an extensive Inventory Control file. This file gives an account of all the parts & equipment throughout the maintenance system.  It also defines the status of each part and its location.
17. It enables timely & accurate reporting of data to all the maintenance centres. The same data is available and distributed to everyone.
18. The database maintains files related to any work assigned to outside service centres.

Many parts are repaired by the vendors from whom they are purchased.  A database is used to maintain data on the parts that have been shipped to vendors and those that are outstanding from the inventory.  Data relating to the guarantees and warranties of individual vendors are also stored in the database.

 

DISADVANTAGES OF DATABASES.

 

1. A Database system requires a big size, very high cost & a lot of time to implement.
2. A Database requires the use of a large-scale computer system.
3. The time involved. A project of this type requires a minimum of 1 – 2 years.
4. A large full-time staff is also required to design, program, & support the implementation of a database.
5. The cost of the database project is a limiting factor for many organizations.

Database-oriented computer systems are not luxuries, and are undertaken when proven economically reasonable.

 

Exercise (a).

 

1. (a). What is a database?

(b). What are Database management system software?

2. Name and explain the THREE types of database models.        (6 marks).
3. Explain THREE major concerns in a database system.                                        (6 marks).
4. How are database software generally classified? Give examples of range of products in        each type of classification.
5. State 5 features of an electronic database management system.
6. Explain the importance of using a Database management system for storage of files in an organization.

 

Exercise (b).

 

1. Write short notes on:
   • Database maintenance.
   • Data bank.
2. State the components of a data hierarchy.
3. (a). List the TWO classes of database software.

(b). Give FOUR widely used Database management systems today.

4. Identify FIVE functions of a Database management system.
5. Describe the advantages and disadvantages of a database.

 

Exercise (c).

 

1. Define the following terms:
   • (4 marks)
   • Database Management System (DBMS). (4 marks).
   • Relational database.
   • Hierarchical database.
   • Network database.
2. List and briefly describe THREE advantages of using the electronic database approach in data storage as compared to the file-based approach.
3. List and briefly describe TWO features found in a typical Database Management System.
4. Identify and describe three major shortcomings of the conventional file structures that are being addressed by the database approach. (6 marks).
5. Describe the functions of the following tools found in a database management system (DBMS).
   • Data Definition Language (DDL) (2 marks).
   • Data Manipulation Languages (DML) (2 marks).
   • Data Dictionary (DD) (3 marks).