Database Management System I SS2 Data Processing Lesson Note

A database is an application that is used to store information in a structured way. It is a collection of records or data in an organized form.

A database management system is a collection of computer software that enables users to define, create and maintain a database. A database management system (DBMS) is a computer application that interacts with the user, other applications and the database itself to capture and analyze data. The general purpose of the database management system (DBMS) is designed to allow the definition, creation, querying and administration of databases. The DBMS serves as an interface between the database and end users or application programs, ensuring that data is consistently organized and remains easily accessible. Database Administrators are those that control the database system. They also control the access and security aspects of the database.

FUNCTIONS OF DBMS

A DBMS makes it possible for users to create, edit and update data in database files. The functions also include:

1. Concurrent access to the same database at the same time.
2. It creates security rules to determine the access rights of users.
3. It improves the integrity of the data in the database.
4. It also provides a data dictionary for description of data.

EXAMPLES OF DATABASE MANAGEMENT PACKAGES

There are various database management systems. Some of these are:

1. Microsoft Access: It is a DBMS developed by Microsoft. It stores data in its format based on the access jet engine. It has the facilities like importing or linking directly to data stored in other databases.
2. MySQL: It is an open-source DBMS. One of the most popular DBMS. 
3. Oracle: it is an object-relational database management system.
4. Microsoft SQL server: It was developed by Microsoft. The primary function of this software is to store and retrieve the data as requested by other applications, whether those applications are on the computer or running on other computers across the network.
5. Filemaker: It was begun as an MS-DOS-based computer program. It is a cross-platform relational database management system.

DATABASE TERMINOLOGIES:

1. Rows: are the records.
2. Column: is vertical and labelled alphabetically.
3. Field: is a group of related characters in a file
4. Character: a single symbol in a file.
5. Record: is made of several fields that are related together and treated as an entity.

MICROSOFT ACCESS

Database packages are used to design a database in a computer. An example of a common database package is Microsoft Access. MS Access is a Relational Database Management System used to create and modify databases. 

USING MS- ACCESS FOR DATABASE

To create a database on the computer with MS Access

1. Load MS Access: Do the following;

• Click on the Start Menu
• Point to All program
•  Point to Microsoft Office
• Click on Microsoft Office Access

2. Creating Database

• From the displayed window, click on the blank database.
• By the right-hand side of the windows, where the arrow is pointing in the picture screen above, click inside the file name text box and type the desired database name.
• Click on the Create command button. A database with the filename given will be created
• Files are created as tables in the database

3. Creating a file

• Click on the Create menu and select Table
• At the All Tables tab, right-click on any of the tables.
• Select Design View

In the Save As dialogue box, type a desired table name (e.g. Student Table) in the Table Name text box and click ok.

Tables in the database on a computer are composed of rows and columns. A table in MS Access is organized into rows and columns like the picture screen shown below.

A row contains records or diverse fields. The rows specify the number of records in the table. For example, in the picture screen above, there are six records in the student table.

A column usually represents a field in a database table. It contains the type of information. For example, in the picture screen above, there are three fields (data fields), REG. No., Surname, and first name.

CREATE FIELDS WITH DATA TYPES 

Fields are assigned field names relevant to the information they keep. Field names are assigned data types which determine the kind of data they accept as input. For example in the MS Access table above, Surnames are alphabetic, and the fields will not accept numeric (numbers) inputs 10 or 500 as surnames. To set the data type for the field in MS Access, follow the steps below; 

After creating the table in the design view under the field name tab, then type the field name and under the data type tab next to the field name, click the drop-down menu and select AutoNumber as shown below.

For example, using the above picture screen, the field name ID Number will be assigned a Number data type since the ID Numbers will be numeric. In the same way, the TEXT data type will be assigned to Surname and First name.

UNIQUE IDENTIFIER

A table contains a unique identifier i.e. a KEY. In MS Access, a default primary key is usually specified for the first field. To set another field of your choice as the primary key, right-click on the first cell and select Primary Key.

NOTE: The symbol of a key should appear beside the field, after setting that field as a unique identifier. If it does not appear, repeat the previous steps.