What is a database?

A database is a collection of data that is organized in a specific way for easy access using queries. It could also be defined as a data structure that stores organized information. It is also a collection of related and logically coherent data used by the application programs in an organization.[1]

Database Models

Database models are used to determine how data are logically structured or designed. It generally explains the relationship between different parts of the data in the database. A database model fundamentally determines how data in a database is organized, stored, accessed and manipulated.

Flat file model

This is a traditional database model. Traditionally, data storage uses individual and unrelated files to store data. This type of data storage is called a flat file or table model. This system of storage uses limited rows and columns and can be achieved using a spreadsheet application such as Excel. The flat file system of data management allows for individual record keeping per department in an organization. As a result, there are difficulties in data interconnectivity and retrieval process. Therefore, there is no relationship between data store in flat files. An example of a flat file model is given below:

Students table

Hierarchical databases model

The hierarchical model organizes data in a hierarchical tree-like structure. In this model, each entity has one parent called the root but it can have several children. An example is illustrated below.

Network databases model

The network model uses two fundamental concepts to organize data in a tree-like hierarchical model such that data structures can have multiple parents, allowing many-to-many relationships. In the network model, two concepts define the database design, namely records and sets.

Records contain fields while sets define one to many relationships between records. Generally, think of the network model as a hierarchical model, but instead of a single-parent tree in the hierarchy, you can have multiple parents such that the child tables were allowed to have more than one parent. An example is given below.

In the above diagram, there are two parent tables (students and teachers) and each of them has child tables (subjects and class respectively). However, each of these child tables is interconnected to one another, which establishes the relationship in the database.

Relational databases model

The relational model is in use today. It defines data organized in two-dimensional tables called relations. The tables or relations are organized such that they are related to each other using primary keys and foreign keys. An example of a relational database is shown below.

In the figure above, the TID in the subjects table is a foreign key while TID in the teacher’s table is a primary key. These keys help to create and maintain relationships in a relational databases.

Features of a relational databases

A relation in a relational database is a two-dimensional table, i.e. a table with columns and rows. A relational database has three key features, namely, entity, attributes, and tuples.

Entity: This can also be called the table name or relation. It is a unique name given to the relation in the database. It is the name of a table with columns and rows, e.g. STUDENTS, CLASS.

Attributes: It can also be called fields. Each column in an entity is represented with an attribute or a field name. This gives meaning to the data type stored in the relation. For example, the STUDENTS table above has an ID, Names, telephone, and address. The total number of attributes in a relation is called the degree of the relation, e.g. the STUDENTS table has a degree of 4.

Tuples: This represents each row in a relation, otherwise called records. It defines a collection of attributes values. The total number of rows in a relation is called the cardinality of the relation. This number can change in a dynamic table whenever a record is added or deleted. Using the STUDENTS table as an example, if there are 1250 students in the school, then the STUDENT table or relation will have 1250 records or we say that the cardinality of the STUDENTS table is 1250.

Databases Components and Terms

Advantages of Databases

1. There is less redundancy. For example, in a flat file, the names of teachers can be stored in more than one Storing the same data in more than one table in a database system creates redundancy.
2. It helps to prevent data inconsistency. In a relational database, since records appear once throughout the database, it helps to maintain consistency of data. For example, if a data changes, you need to change it in one table only and it will be updated in all other tables. This is not so in flat file model.
3. It enhances data efficiency since data will not be stored in different locations at the same time
4. Because data is stored in only a few locations in a database, it is easier to maintain data integrity
5. Confidentiality of data is easy to maintain in a database since there is the centralized location of data.

Components of a database management system (DBMS)

DBMS is a software for creating and managing the database. It defines, creates, stores, modifies, retrieves and maintains the integrity of data in a database. Examples of DBMS include Microsoft Access, MySQL, Oracle, Microsoft SQL server, MS FoxPro, SQLite, Postgres, NoSQL, Ingres, etc.

There are five basic components of a DBMS.

1. Hardware: this defines the physical computer system that allows access to the data, e.g. hard disk, workstations, etc.
2. Software: is the actual program that allows users to create, access and maintain data, e.g. Access, SQL, etc.
3. Data: is the information being maintained in the database. It is different from the software and is stored in a physical location. This allows for software updates without tampering with data.
4. Users: there are two major users of the database, namely, application program and end users. Application programs refer to applications that have access to the database, e.g. MS Excel, etc. End users are divided into two: normal users and administrators. Database administrators have maximum privileges to use and maintain the database as well as assign privileges to normal users, while normal users have limited privilege to the database.
5. Procedures: This is clearly defined procedures or rules that should govern the use of the database by users.

Database Terminologies

We have explained some terms above, including, tables, fields, records, database, tuples, etc. Other terms are defined below:

Data Type – This defines the basic kind of data that can be stored in a column. E.g. number, date, currency, etc.

Key – A column or columns on which an index is constructed to allow rapid and/or sorted access to a table’s row.

Primary Key – A column or group of columns in a table that uniquely identifies each row of the table. The primary key is used in conjunction with a foreign key in another table to relate the two tables together.

Foreign Key – One or more columns in a table intended to contain only values that match the related primary/unique key column(s) in the referenced table. Foreign and primary keys explicitly define the direct relationships between tables.

Referential Integrity – This is maintained when every foreign key refers to one and only one existing primary key.

Working with MS Access

How to Start MS Access 2013

1. Open Run command by holding the windows logo key and press R key on the keyboard (Windows logo + R)

1. On the dialog box that appears, type MSAccess and click OK, MSAccess opens.

 

1. To open an existing database, On the left pane, under Recent, select a database file to open it. In the above window, I selected KIMSDB. The database opens as shown below.

1. To open a blank database, on the Access DB window, select Blank desktop database. The database opens as follow:

1. Type a name for your database and click, Create to create a blank database as shown below.