ICT SME, SSE, LE, ECLPE...

Key Unit Competence:

To create, manipulate and query an access database

Introductory activity

In a certain school the following activities are performed:

New students get registered after paying 5000Frw as registration fees and are admitted in Ordinary level or in different options depending on their performance in Ordinary level national examination. These students pay school fees on termly basis and the first payment is done upon coming to the school and after being registered

As they follow their lessons students undergo some evaluations and the results of those evaluations are kept. The marks in different subjects and those in Discipline are based on in promoting students to next levels or making them repeat classes or expelled from school in case of serious discipline issues.

Students engage in different extra curricula activities like clubs, games, etc.

The school wants to keep all the above information in a computer based database.

a) Suggest tables that can be used to keep the above information

b) What is the benefit of keeping that information in a computer database compared to keeping it on a paper?

c) Which Office program to use to create that database?

d) Why is it preferable to use the program given in c) instead of using other programs?

1.1. Understanding database

Activity 1.1

1. Explain the term Database

2. Give examples of databases you know in the society you live in.

In our information society, record keeping and data processing using database

has become an important aspect of every organization such as Hospital, School,

Universities and Governments. Databases are used all the time, often without

knowing it. For example, withdrawing money from the Bank’s ATM.

1.1.1. Database concepts

A. Definitions:

• Database

A database is an organized collection of related data so that its contents can easily be accessed, managed, and updated. It is considered to be organized because the data is stored in categories that are accessible in a logical manner.

• Data

Data are unprocessed raw facts that include text, number, images, audio, and video. The data are useless for decision-makers until they have been processed or refined in some manner.

•  Information

Information is data that has been processed, refined and then given in the format that is suitable for decision making or other organizational activities.

For example: Report about student fee paid is useful information for finance section.

•  Database management

Database management refers to a set of activities performed on the database. Some of these activities are:

Insertion of new records in tables are,

•  Deletion of unnecessary records,
•  Updating records if the information is changed,
•  Searching for records according to the given criteria whenever needed.

Database Management System (DBMS)

The Database Management System (DBMS) is an application software that enables users to define, create, and maintain the database and which provides controlled access to this database.

The DBMS receives requests (queries) encoded in SQL and translates those queries into actions on the database.

A database system comprises of five major components:

1. Data: The database contains both the operational data and the meta-data (the data about data).

2. Hardware: this consists of the secondary storage (hard disk) on which the database reside together with the associated devices.

3. Software: Software refers to the collection of programs used within the database system. It includes the DBMS software the application programs, together with the operating system, including network software if the DBMS is being used over a network.

The application programs that allow users to store information in an ordered manner for timely and quick retrieval is called Database Management System (DBMS).

4. Procedures: These are the instructions and rules that govern the design and use of the database. This may include instructions on how to log on the DBMS, make backup copies of the database, and how to handle hardware or software failures.

5. People (users): This includes the database designers, database administrators (DBAs), application programmers and end users.

B. Why Study Database

Databases are useful for example many computing applications deal with large amounts of information; they have become an essential component of everyday life in this modern society. Database systems give a set of tools for storing, searching and managing this information

Importance of database

•  A database enables to put data on the disposition of users for consulting or updating
•  The database should manage the privileges granted to its users.
•  A database can be local in one machine just used by one user or distributed in many machines accessible by many users through a network (sharing of data).

The main advantage of using database is its possibility to be used simultaneously by many users as per the image below:

Figure 1. 1. An interaction between a database server and client computers

1.1.2. Approaches of Database

A. Paper based database

This is a kind of database where information is kept on papers.

Keeping a database on papers makes its management difficult and the size of papers cumbersome.

It presents the following disadvantages:

•  Papers can be lost, damaged over time or due to calamities like fire and flood
•  If there is change in the stored information the hard copies are hard to update or may even rewriting the information on a new copy having the new update
•  Compared to information kept in a computer, paper-based databases are difficult to copy and search
•  Papers need a lot of space to store, can be misplaced hence difficult to find.

B. Computerized database

When a database is stored electronically on a computer and can be manipulated using a computer, it is called a computerized database. Computerized databases can be of different types:

i) Traditional File Processing Systems (TFPS) approach

This is an approach which was used before the invention of modern Database Management Systems (DBMS) but this approach was difficult to use as it required advanced knowledge and to use it one had to have advanced

programming knowledge which was not easy for a common user. Users had to write application programs to store data in form of files on the computer permanent storage device (Hard Disk) and each application program written by a user had to define and manage its own data.

Compared to manual management of information, the Traditional File Processing presents the following advantages:

•  Simplicity: the design of file processing is more simple than designing a database
•  Efficiency: file processing cost less and can be more speedy than database
•  Customization: you can customize file processing more easily and efficiently than database because files are related with the application and it has all the data needed for that application.

In File-based processing, for each database there is a separate application program as shown by the following figure:

Traditional File Processing System presents the following disadvantages:

•  Separation and isolation of data: as data is isolated in many files, it is not easy to have access to data.
•  Duplication of Data: because of the decentralized management, data may be scattered on different computers and this has as consequences a misuse of storage space as data is repeatedly stored in different locations

(This is known as Data Redundancy) and a loss of data integrity as data is stored in different locations and the different copies may be slightly different.

Centralized Data Management: In DBMS all files are integrated into one system thus reducing redundancies and making data management more efficient. One user can know data in another department without moving from one department to another. This is called data integration.

Data Independence: Data independence means that programs are isolated from changes in the way the data are structured and stored.

Data consistency: In DBMS, data inconsistency is avoided. Data inconsistency means different copies of the same data will have different values.

For example: Consider a person working in a branch of an organization. The details of the person will be stored both in the branch office as well as in the main office. If that particular person changes his address, then the “change of address” has to be maintained in the main office as well as the branch office. In case the “change of address” is maintained in the branch office but not in the main office, then the data about that person is inconsistent. DBMS is designed to have data consistency which makes DBMS have the quality of increased data integrity.

Data sharing: Due to the fact that data is centralized, many different users from different locations can share data.

Data recovery after a crash: After a break down, DBMS allows to recover data after a crash. The crash may be caused by power failure or hardware failure.

Concurrent transaction control: A transaction means a collection of operations that perform a single action in a database. Example: Transferring money from one account to another.

Concurrent transactions mean that many transactions are being done simultaneously or concurrently.

Concurrent transaction problem occurs when multiple users want to access data in the database at the same time like for example when someone wants to withdraw money from his/her account while at that time there is someone else who is using ATM card to withdraw money from the same account. DBMS ensures that many users are accessing data and these problems are controlled.

Increased Data security and safety: DBMS allows data to be highly protected against unauthorized access.

Application activity 1.1

1. Differentiate paper-based database from a computerized database and give examples

2. Give some loopholes in Traditional File Processing system which called for another approach to keep and manage data

3. Give examples of DBMS

1.2. Key terms used in Database

By doing a research using books or the internet explain the followings database terms

     i) Entity

    ii) Attributes

   iii) Relational Database

   iv) Cardinality

   v) Table

    vi) Primary Key

    vii) Foreign key constraint

    viii) Alternate Key

A. Relational Database

There are different types of database but the most common type is the one that use tables commonly known as Relational Database

A relational database is the one having one or many Relations which are the mathematical concept representing physically a table

In this kind if database, the table also known as an Entity may have two or many columns also known as Attributes or Fields and details on items are stored in rows also known as Tuples or records. A certain column has some values that is allowed to accept and those are called Domain

                             Figure 1. 4. An example of a Relation with rows and columns

Cardinality refers to the number of rows in a table. In the above table cardinality is 6

Degree of a relation (table) equals to the number of its attributes (columns/ properties/fields). For example, in the above table, degree is 5.

Properties of Relations in a relational database

A table (relation) in a relational database has properties which have to be met:

• The relation has a name that is distinct from all other relation names in the database
•  Each cell of the relation contains exactly one atomic (single) value;
•  Each attribute has a distinct name;
•  The values of an attribute are all from the same domain. The domain is the type of data acceptable in a column. For example, a Name column should not accept only numbers data
•  Each tuple is distinct; there are no duplicate tuples;
•  The order of attributes and tuples has no significance

B. Primary Key

In a database table, a primary key is a column or combination of columns that uniquely identifies table records. This column does not accept null values and all its values are unique. The primary key column can be created by the database manager or can be generated by the database according to the defined sequence.

In Access, when an auto generated primary key is ID which is a sequence of numbers starting from 1.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are combined and used as a primary key, they are called a composite key. If the primary key is already defined, it is not possible to have two records having the same value in that field.

The figure below shows an Access table in which an IDNumber column plays the role of a primary key. The values in that column are unique, no value is like another and no cell is empty (no null values)

C. Foreign Key

A foreign key is a field or collection of fields in one table that refers to the primary key in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced table, parent table or master table.

The image below shows a table with a unique value column whose values are those from the master table, the IDNumber is the primary key of the IDENTIFICATION table while in the MARKS table the IDNumber is the foreign key. All values in the Master table don’t need to be in the child table.

D. Candidates keys

All attributes that can uniquely identify an entity are called candidates keys.

They are never NULL and cannot allow duplication of values. When multiple possible identifiers exist, each of them is a candidate key. For example, in IDENTIFICATION table, IDNumber and PhoneNumber are both candidate keys.

E. Alternate key

A relation may have many candidate keys. If only one candidate key is selected as a primary key of that relation, the remaining candidate keys are called alternate keys.

F. Composite key

A composite key is a combination of two or more columns which play the role of a primary key and therefore uniquely identify each row in a table. A key composed of more than one column is created when there is no single column alone that can satisfy the conditions of a primary key (with unique values and no null values).

G. Structured Query Language (SQL)

SQL is a programming language used for managing data in a relational database

SQL can be used to create a database and tables inside that database, insert and delete data in tables, query the database and whatever other task to be done on a database.

SQL statements look like ordinary English though the words may appear straight or are in a shortened form. Below is a statement in Access that displays information from the table IDENTIFICATIONS.

When the SQL statement above is run, it displays all the records in the table

IDENTIFICATIONS

H. Distributed database

A distributed database is the one that consists of two or more database files located in different sites either on the same network or on entirely different networks. Portions of the database are stored in multiple physical locations and processing is distributed among multiple database nodes.

The concept of a distributed database is contrary to centralized database which keeps the database data in the same location, on the same computer.

I. Object oriented database

This is a kind of database in which, like in Object Oriented Programming, data is represented in the form of objects and classes. Objects are real life entities which model.

J. NoSQL

Structured Query Language is used when the database is organized in tabular form. SQL can be used to query a database only when they are in tables and when they are not other means have to be used.

When the database is not relational, is very large and can store non structured, structured, semi structured and polymorphic data then it is a NoSQL database and therefore requires a non Sql language to query it.

The concept of NoSQL databases was first thought when giant firms like Facebook and Google faced the problem of dealing with huge volume of data and thus making their relational database very slow to respond. To resolve this problem it came in their minds that they could upgrade the existing hardware and this is very expensive. The last solution is to distribute the database load on multiple host (scaling out) whenever the load increases.

NoSQL database being non-relational is more suitable to scaling out better than relational database.

K. Database Management System (DBMS),

DBMS serves as an interface between the database and end users or application programs to make sure that data is well organized and remains easily accessible.

DBMS helps provide concurrency, security, data integrity and uniform data

administration procedures. Advanced query languages such as SQL are used

along with the DBMS package to interact with a database.

Some DBMS examples include MySQL, SQL Server, Oracle, dBASE, FoxPro, Access, etc

L. Constraints of integrity

These are conditions to be respected by all valid relations to emphasize the data integrity.

Types of constraints:

• Primary key constraints: Every table should have a primary key to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce the entity integrity.
• Unique constraints: The unique constraint enforces the uniqueness of the values in a set of column, so no duplicate values are entered. The unique constraints are used to enforce the entity constraints as the primary key constraints.
•  Foreign key constraints: The foreign key constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table point to a primary key in another table. The foreign key constraints are used to enforce the referential integrity.
•  Check constraints: These are used to limit the values acceptable in a column. For example, in a column called marks, if the maximum is 100, values should be between 0 and 100. The check constraints are used to enforce the domain constraints.
•  Not null constraints: a not null constraint enforces that a column will not accept null values.
•  Default constraints: Default constraints define what to do when a row is inserted with data for the column on which no constraint has been defined.

For example, a default value can be set to be 0 for a column that contains numerical values.

Application activity 1.2

1. What do you understand by the relational Database

2. List properties of Relations in a relational database

3. Explain the following terms:

       a) composite key,

      b) candidate key

4. Discuss what will happen if the primary key must contain 2 separate columns

5. State the primary key constraint

6. Using the below Students table

Find the below:

     a) Degree of the Students table

     b) Cardinality of the Students table

1.3. Data type

Activity 1.3

Karenzi was given a word document containing information. He had to enter into the MS Access but he got many error messages from MS Excel .He found it difficult to proceed with entering data. The image below shows the table and the message he got as he entered data.

    a) What may be the cause of such error messages?

    b) What can he do to avoid those error messages and proceed with entering data?

   c) When Karenzi wrote in The YearOfStudy column the word “Year Two” he got the same error. What can be the cause?

Data Type defines the type of value that can be stored in a table column.

Database Management Systems and programming languages have a range of data sets they can accept.

DBMS tables accept specific values depending on the choice of the database administrator who created the database tables. Other DBMs may have different data types not found in Access.

For example: Oracle has these data types: Float, Long, Binary_Float, Clob, etc

The following are the data types to choose from while creating an Access table in Office 2013: Short Text, Long Text, Number, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, Attachment, Calculated and Lookup Wizard.

A. Different data types

The description of each data type is in the table below:

A. Which data type to use

The data type to use is dependent on the size to store in the cell and the operations to perform on the stored data.

For example: numbers can be stored as text, mathematical calculations like addition and multiplication, cannot be performed on such numbers.

Take into account the size and length of the data to store and also don’t define

unnecessary big data type. It would be a waste of memory if the database

administrates defines a column data type as Long Text while every cell in this

column will store less than 255 characters, here define it as a Short Text.

When a data type is defined, it takes its space and if one defines a Long Text instead of a Short Text, he/she would be losing 63,744 characters (equivalent to 63999-255). When bigger data types are unnecessarily defined the database becomes big therefore slow to access.

Application activity 1.3

In the table below, you are given a column name and an example of data that can be contained in this column. Complete the Data Type column, indicating the appropriate data type to use in each column.

1.4. Database design steps

Activity 1.4

Read the following scenario and give answers to the asked questions At Groupe Scolaire Kinazi, the management of students’ information is not computerized. The Head teacher is looking for a solution so that the

database of students can be managed digitally and hence speed up the production of school reports and access to information when needed.

Suppose you are given the task to design that database:

1. Discuss the different steps you will go through to design a database that will manage student information

2. Discuss the benefits for the school when teachers start using the new computerized database.

The most important thing to do to start designing a database is to plan ahead.

When a case that needs a database creation is presented, before switching ona computer and starting creating the database it is better to think about the type of information to work with and the types of questions that the database should answer, what information needs to be stored and what specifically are the links between them. The next phases are to verify all requirements specifications, to represent the data with diagram, and to plan the database.

When data is more complex, there are more needs to plan. Even the simplest database should be thought thoroughly on paper before being created using Database Management System such as Microsoft Access, SQL, MySQL, etc.

A well-designed database performs well and adapts to future needs by giving users access to essential information. Poor planning often results in a database that fails to meet the current needs or the needs that may arise in the future cannot easily be integrated in the existing database

In planning the database, regardless of its size and complexity the following basic steps are used:

•  Gathering information (investigation)
•  Identify the objects (Important Entities and their Attributes).
•  Model the objects.
•  Identify the types of information for each object.
•  Identify the relationships between objects.
•  Database optimization through normalization.
•  Data entry and manipulation

1.4.1. Gathering information

Before creating a database, there is a need to understand the problem that the database is expected to solve. If the database is to replace the traditional method, file based approach method, then the existing system will give most of the information needed.

In this step there is a need to work with everyone involved in the existing system which can be a paper based database, an old database or no database at all. Gathering techniques include collecting copies of customer information, management reports, and any other documents that are part of the existing system or interviewing staff who are familiar of the work done in that institution, the staff who are expected to use the new database or who use the old one.

This step is useful in designing the database and the interfaces.

The output of this step is information on how the business of an institution is carried out and this can be analyzed to derive the different tables (entities) that will be needed.

1.4.2. Identifying the important entities and their attributes

During the process of gathering information, the key objects or entities that will be managed by the database must be identified. The object can be a tangible thing such as a person (for example student, employee, and patient), a product, or it can be a more intangible item, such as a department in an institution, a Combination in a school. Each distinct item in the database should have a corresponding table for which column titles are attributes of the entity

1.4.3. Identifying the Relationship Between entities

It is good to relate or associate information about various items in a database.

Identifying the relationship between entities in the design process requires looking at the entities and determining how they are logically related and adding relational columns (foreign key) that establish a link from one table to another.

After identifying the relationship between entities an Entity Relationship (E-R)

Diagram is created using appropriate symbols.

1.4.4. Modeling the objects

As the objects in the system and their attributes are identified, they are recorded in a way that represents the system visually. They are recorded using Relational model and Entity Relational model.

1.4.5. Identifying the types of information for each object

After identifying the primary objects/entities in the database as candidates for tables, the next step is to identify the types of information that must be store for each object.

These are the columns in the table of the object. Fields/columns should be kept simple, the more atomic are the fields the more flexible will be the database.

For example, in a database of names and addresses, it is better to keep each part of the person’s name as a separate field.

1.4.6. Database optimization through normalization

One of the most important step to consider when designing a database is database definition. If tables are not set up properly, it can cause a lot of headaches at the time of extracting/retrieving required data.

Normalizing a database eliminates redundancy, and other anomalies that may arise as the database is being used. Redundancy means that the same data is saved more than once in a database.

Those anomalies are: insertion anomalies, deletion anomalies and updation anomalies. Normalization will be developed in details in the subsequent sections.

• Update anomalies: If data items are scattered and are not linked to each other properly, then it could lead to strange situations where one row is updated but others are left with old values leading to inconsistency in database.
•  Deletion anomalies: As data may be scattered somewhere else, in a database that is not normalized when one record is deleted, other related records may remain in the database.
• Insert anomalies: In a very long table which is not normalized, one may need to insert some values but may not have values to insert for some columns which may lead to tables in which some cells are filled while others are empty.

Normalization is a method to remove all these anomalies and bring the database to a consistent state.

A Properly normalized design allows to use storage space efficiently, eliminate

redundant data, reduce or eliminate inconsistent data and ease the database maintenance

1.4.7. Data entry and manipulation

When the database is completely designed the last step is using it by putting in it the data. Data can be manually entered from hard copy forms or it can be imported from other files like excel or other files supported by the current one.

While entering data or importing it from other files make sure the data is valid because if the database has strong validation rules, which is an ideal, it will not accept those data.

Application activity 1.4

1. Discuss the activities carried out during the data gathering phase in the design of the database

2. What is the difference between insertion anomalies and update anomalies?

1.5. Creating, Saving, Closing and Opening a database

Activity 1.5

Using Ms Access, you are tasked to create a database of a mini supermarket:

1. Name and create three table (Customer, Employee and Product)

2. Identify for each table the name of the column that will have unique values (values that don’t resemble and no duplicate values in the whole column and that cannot contain null values)

3. Discuss the importance of such a column (given in 2) in a table

A. Creating and saving a database

A database is a collection of information. In Access, every database is stored in a single file and has to have a name and different objects.

Different objects in an Access database are tables, queries, forms, reports, macros, and modules.

Tables: They store information. A database can have as many tables as are needed.

•  Queries: They let database users perform actions on tables which can be data definition queries or data manipulation queries.
•  Forms: Those are attractive windows created by the database user and provide a way to view or change information in a table
•  Reports: They are summary of the information contained in different tables which have a common characteristic.
•  Macros: They are mini-programs that automate custom tasks.
• Modules: they are files that contain Visual Basic code that can be used to perform different tasks like updating 10,000 records or firing off an email.

To create an Access database first start Microsoft Office Access by clicking on the start button then on Access 2013, in the window that will appear write the database name and click on Create

The new database is now created but has no table. Tables can be created usingone of the available options: Datasheet view, Design View

i) Creating a table in Datasheet view

When the database is created, the default option to create tables is using the Datasheet view. The Datasheet view is the option which allows to view many rows in a table at the same time. This option also allows to filter and sort data using the built in sorting and filtering options. This view is also useful for quickly viewing the details of many records, adding new records and deleting records from a table.

To create a table in Datasheet view, do the following:

•  Click on Create menu then on the table icon. New table with two columns and one row appears

To add new columns to the existing table in the image above click on the drop down arrow next to Click to Add. In the list of data types that will appear choose one suitable to the information to be kept in the column

Figure 1. 9. Window to choose data type (Left), to set the column name (Right) and name the table (Bottom Right)

• Write the name of the new column. Repeat the same steps of choosing the data type and renaming the column up to when you have all the needed columns.
•  Save the table by clicking on the save icon , write the table name and click on OK

ii) Creating a table in Design view

A table can also be created in Design view and this provides additional options compared to the Datasheet view like adding/changing the primary key and changing the column data type. In Design view, the table structure is created but entering data requires using the Datasheet view

To create a table in Design view go through these steps:

• On the Create tab, in the Tables group, click Table Design or in the Views group click on the View tab then choose Design View

• In the new window that will appear, specify the field names, Data types and the descriptions of the fields (optional) and set the primary key by selecting the field name then clicking on the Key icon . The primary key column will then have a key icon on in front of it
•  Save the new table by clicking on the save icon and specifying the name

B. Opening and closing a database

A database created can be closed by clicking on the close icon of the Access window in which it is opened. This same database can also be opened by first opening Microsoft Office Access then going to the File tab then choosing the Open option and browsing the database to open.

Once the database is opened, it will show its different objects (Tables, Queries, Forms, Report), choose the one you want to have access to.

Application activity 1.5

Create a database in Ms Access and name it “Marks“ and save it on the desktop

2. Create two tables in which we are going to keep marks for SET for Primary 2 and Primary 3 , name it respectively “SET marks for P 2” and “SET marks for P 3”and specify the primary key for each table

Table “SET marks for P2”

Table “SET marks for P3”

3. Discuss the role played by the primary key in the relational database

1.6. Creation of table relationship

Activity 1.6

What do you understand by relationship in relational database?

2. What is the Entity Relationship Diagram?

Table relationship is the backbone of database management as it helps in making the many tables seem one. Without this option it would not be possible to retrieve data from different tables at the same time but every table would be taken as a separate entity. For tables to be logically related they have to have a common column which binds them; this is the foreign key.

A relationship is established between two tables when one table uses a foreign key that references the primary key of another table. This is the basic concept behind the term relational database.

Here is in brief why table relationships are needed:

• As most of the time a database has more than one table to work while running queries it is necessary that tables be related as the query works by matching the values in the primary key field of the first table with a foreign key field in the second table.
• While designing a form or a report, MS Access uses the information it gathers from the table relationships you have already defined to present you with informed choices and to prepopulate property settings with appropriate default values.These foreign key-primary key pairings form the basis for table relationships and multi-table queries.

A. Different relationships

There are three main relationships in database tables namely one to one, one to many (or many to one) and many to many.

i) One to one (1:1)

In a one to one relationship one record in one table can be related to only one other record in the other table. This kind of relationship can be implemented in a single table and therefore does not use a foreign key which is needed only when

two tables are to be related.

This kind of relationship is not so common as the data stored in table B could just have easily been stored in table A. However, there are some valid reasons for using this relationship type like security purposes, the need to diminish the number of columns in a table and slash it into smaller tables, and various other specific reasons.

These are examples of One to One relationships in real life:

•  In a marriage, each spouse has only one other spouse.
•  A son has only one mother
•  A car can be driven by one driver at a time
•   A President and a country

ii) One to many /many to one (1:M / M:1)

A one-to-many relationship allows a single record in table A to be related to multiple records in table B but a record in table B can have only one matching record in table A. This is the most common type of relationship and it is used to relate one record from the ‘primary’ table with many records in the ‘related’ table.

Examples:

• Consider a business with a database that has Customers and Orders tables. A single customer can purchase multiple orders, but a single order could not be linked to multiple customers.
•  A mother can have many children
•  A businessman can have many cars
•  One federate country can have more than one state

iii) Many to many (M:M)

Two tables have a many-to-many relationship when a single record in the first table can be related to one or more records in the second table and a single record in the second table can be related to one or more records in the first table.

Briefly two tables A and B are said to have a many-to-many relationship when A record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A.

Examples:

•  The relationship between TEACHER entity and STUDENT entity is an example of many-to-many relationship. One teacher teaches many students and one student is taught by many teachers.
•  Many students are taught by many teachers
•  Many customers may buy many products

B. Entity relationship diagram for different relationships using Peter Chen notation

An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. These entities can have attributes that define their properties. By defining the entities, their attributes, and showing the relationships between

them, an ER diagram illustrates the logical structure of a database. ER diagrams are used to show the design of a database.

There are different reasons to create a database diagram but the min one  is to document it so as to facilitate the task for anyone who wants to know the organization of the tables and be able to administer that database. Once the database is documented in this way, it is easy to manage it by learning any queries. It is also easy to know which other tables can be created as the business changes and how they will be related to the existing tables.

a) Different diagrams used

For drawing an entity relationship diagram of a database the following symbols summarized in the table below are used:

The type of relationship between entities is on the connecting lines by M to symbolize many and 1 to symbolize one.

b) Linking different shapes to construct an Entity Relationship Diagram

The relationship is shown by a table diagram on one side and another table diagram on the other side which are separated by a diamond in which is the relationship name. Between the rectangles and the diamond are lines on which is the cardinality of the relationship. The tables below summarizes how these are linked:

c) An example of an Entity Relationship Diagram for a Library database

The database below is of a school library which lends Books to students upon their request. Those students study in different Combinations and order books that they will return after reading them within 15 days. The Entity Relationship

Diagram below shows the relationship between the tables (STUDENTS,ORDERS, BOOKS, COMBINATIONS) of this database

Application activity 1.6

You are required to create a conceptual data model of the data requirementsfor a company that specializes in IT training. The Company has 30 instructors  and can handle up to 100 trainees per training session. The Company offers five advanced technology courses, each of which is taught by a teaching team of two or more instructors. Each instructor is assigned to a maximum of two teaching teams or may be assigned to do research. Each trainee undertakes one advanced technology course per training session.

a) Identify the main entity types for the company.

b) Identify the main relationship types and specify the multiplicity for each relationship. State any assumptions you make about the data

c) Using your answers for (a) and (b), draw a single ER diagram to represent the data requirements for the company.

1.7. Establishing table relationships in Access

Activity 1.7

1. Find attributes for STUDENTS, BOOKS, COMBINATIONS, ORDERS

2. Draw the ERD for STUDENTS, BOOKS, COMBINATIONS, ORDERS

3. Open Microsoft Access 2013 and create a tables STUDENTS, BOOKS, COMBINATIONS, ORDERS.

4. Create relationships among these tables.

A. Quick creation of the tables

•  STUDENTS table

The student table will have 4 columns namely StdID, First Name, Last Name and Sex. The data types for all the columns is Short Text. The Sex column will allow only two values: male and female.

The StdID field does not need to be too long, that is why we specify its length as 15. The length of the a field is specified in the Field Properties shown in the image below which is the part of the window for fields definition.

             Figure 1. 14. Field Properties where to define different fields specifications

Using this same Field Properties window, one can specify different properties of the table fields like a customized error message when wrong data type is entered in a table and validation rules.

•  The ORDERS table

This table will contain information about students who bollow books in the library, the bollowed books and the details on borrow and return dates. For this table to store information on students and on books it is not necessary to have all the columns for these entities as just one field referencing those table is enough. That field plays the role of the foreign key.

The values of StdID column will be those that exist in the Student table StdID column while those in the BkNumber column are those that exist in the Books table BkNumber column

                            Figure 1. 15. Structure of the ORDERS table

•  The BOOKS table

The BOOKS table has seven rows namely BookNumber which uniquely identify every book (primary key), the BkCategory, BkTitle, Author, PublicationDate, ValueRWF and Status. This table does not have a foreign key meaning that it does not have a field which refer to fields in other tables but has the BkNumber column which is referred to in the ORDERS table

• COMBINATIONS table

This table keeps information on students with their combinations. The fields are Combination which keeps the name of the combination in which the student studies, the StdID column which contain the unique identification number for the student who studies in that combination, the SchoolYear and the YearOfStudy.

The StdID column in this table serves as a foreign key referencing the student table.

               Figure 1. 17. The structure of the COMBINATIONS table

B. Creating relationships

As it has been seen tables which are not normalized present many anomalies which make such a database very bad. When database is normalized, its tables are broken down into smaller tables which help eliminate redundancy but there must be a mechanism to link those tables so that they can be queried as one.

The mechanism used is to relate different tables by using foreign.

Referring to the ERD of the Library database the following relationship is built:

• Relationship between Students and Combinations tables

This relationship links the two tables Students and Combinations by using the StdtID column which is the primary key in the Students table and is a foreign key in the Combinations table. This means student identification numbers in the Combinations table must first appear in the Students table. Any attempt to enter in the StdID column of the Combination table data that is not in the Student table will result in an error.

A relationship between tables is built like this:

• Under the Database Tools tab click on Relationships
•  Drag and drop the tables between which to create a relationship from the left pane to the
•  relationships area. Tables are dragged
• Arrange the tables properly so as to make an easy to ready diagram once the relationship is created
• Click and hold down the column name from which the relationship is to be established and move the cursor (from left to right) to the other related column in the other table. The window below that will appear will help in editing the relationship

              Figure 1. 18. Different options for establishing table relationship

• Continue the process in the above bullets until all the tables to be linked are linked.

Note that the columns to link have to be of the same data type and of the same length if not so the attempt to create a relationship will result in an error.

After linking all the tables, the relationships between the library database tables will look like in the image below:

                               Figure 1. 19. The library database tables with their relations

C. Calculated fields

There are fields in this database that have rules they have to follow in terms of format, length. They must also to be unique and not allow null values for all these rules to be easily respected, it is better to let access determine their content.

Those columns are the StdID column in the Students table, the OrderID column in the Orders table and the BkNumber column in the Books table.

To set a calculated field in Access do the following:

• While defining the table structure and choosing the data type select Calculated
• In the new window write the expression builder that will generate data you want

Note that to build an expression you have the inbuilt functions, table columns, constants and operators

               Figure 1. 20. Window used to set an expression builder for some fields

Application activity 1.7

Discuss the benefits of having a calculated expression in an Access table

2. By doing a research explain the use of different expression builder elements and use them in defining calculated expressions.

3. Using Entity in the below table , Create tables using these entities in Ms Access and create relationships among these tables

1.8. Adding Data to a database (Data Entry)

Activity 1.8

Using your own terms, explain what is data entry in database

2. What are the 3 difficulties you may encounter when entering data into Ms Access database?

3. Discuss ways you can use to add data to an Ms Access database

Data entry is a direct input of data in the appropriate data fields of a database, through the use of a human data-input device such as a keyboard, mouse, or touch screen, or through speech recognition software.

Before doing a data entry make sure you know which fields to enter and the validation rules associated with those fields. This will help in avoiding to enter a wrong data type in a field and knowing that the database will not accept that data type, it will take time to figure out the right data to enter. Know which fields get data from other fields (lookup), fields that have specific values (like yes/no). In short, study your table/form before entering data.

Adding data to an Access database can be done in 3 main ways:

•  Data entry through a front end interface
•  Entering data in a Datasheet view table
•  Entering data using an Access form

A. Data entry through a front end interface

In programming and development, a front-end is a term that describes a user interface that is used to interact with the database instead of working directly with the database. For example, a website front-end developer handles the visual aspects of how the web page looks and responds to the visitor. The front end interacts with the back end (database) which may be on the local computer or remotely placed on a server.

The front end provides forms which are developed using different languages like HTML (HyperText Markup Language), HTML5 and programming languages to link the webpages to the database like PHP.

The forms provided by the front end has different fields which correspond to the fields in the database and alert the data entry clerk whenever form validation rules are not followed. They also provide a “submit” button on which to click for sending data in the database

Figure 1. 21. An example of a form that can be used to enter data to the database

B. Entering data in a Datasheet view table

Datasheet view is one of the table viewing in Access which allows a user to enter directly data in a table. FILL THIS PARAGRAPH

To add records to a table in datasheet view in Access, click into this row and enter the new record. The asterisk will then change to a picture of a “pencil” as data is being entered. The pencil will let you know which record that is being edited. Another new “New Record” row also appears when you are done with the current row.

While entering data in an Access table make sure you follow the validation rules of the table because of they are not respected, the data will not be accepted.

To add records to a table in datasheet view, open the desired table in datasheet view and follow these steps:

•  In the Home tab click the “New Record” button located in the Records group to make the cursor go in the first cell of the new record or just click in the first cell of the last empty row.
• Enter the information into the fields in the new row by making sure validation rules are respected
• Move to the next field by clicking in it or by using the keyboard backspace.

Using the backspace key while the last cell is reached will move the cursor to the new record

•  Close the table when you are finished adding records.

C. Entering data using an Access form

Microsoft Access 2013 provides different objects the common ones being tables, queries, forms and reports.

Access forms are accessed by clicking on the Create tab then select one of the form options provided in the Forms group

The Form tool is used to view table data in a form format and is accessed just by opening the table and clicking on 

the Form tool 

To navigate in the form’s data or to enter new data use the tools available on Record tools group. By using the tools in this group one will be able to move to the next row, move to the last row, empty the form so as to enter new data and searching for a row containing specific text

C.1. Designing a form from scratch

A form can also be designed from scratch and not created from an existing table. The created form will then create a table to which it will be linked.

To create a form from scratch go through these steps:

•  Under the Create tab click on the Form Design tool
•  In the form area drag and drop the form design tool to use but before this name your form by using the Title tool found in the header/footer group. If needed also set the form’s logo.

From left to right the different tools to choose from are: Select tool, Text Box, Label, Button, Tab Control, Hyperlink, Web Browse Control, Navigation Control, Option Group, Insert Page Break, Combo Box, Chart, Line, Toggle Button, List Box, rectangle, Check Box, Unbound Object Frame, Attachment, Option Button, Subform/Subreport, Bound Object Frame and Image.

C.2. School library form

Here is a form created from scratch which accepts data and submits it to the database table. It has been created by taking the different tools available for building a form. The different tools dragged from the Form Layout tool are: Logo, Title, Text Boxes, Option Group and Button

The above Form viewed in the Form view looks like this:

Application activity 1.8

Create the below form that can be used to enter student registration information in the database.

a) Add your school logo.

b) Make sure the table/form is validated to accept only valid data.

1.9. Querying a Database in design view

Activity 1.9

a) Create and name the table” Own Books” which has columns as below:

b) Display Book title, Author, Publication Date, and Value for the books in the category of science.

c) Display books which have value above 40,000

A query is a request for data results, and for action on data. You can use a query to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data.

A query is a request to the database tables for data results or for action on data. This query can help answer a question, combine data from multiple tables, perform calculations, change or delete table data.

In Access, querying a database can be done in two ways: by writing a query in a language that is understandable to the database that is known as SQL query or by designing or by using the Design View approach. This section is going to talk about querying an Access database in Design view.

The benefits of using queries are so multiple:

•  As the table increases in size and may have thousands of records, it is so difficult to spot one record by scrolling through the database. The query approach solves this problem by spotting the needed records and performing needed actions on them
•  A query can help in applying a filter on table’s data and get only what is needed
•  When one same action is to be performed on many records, it would be a loss of time to repeat every action on every record. Doing it automatically in a query quickens the process

There are mainly two types of queries in Access:

•  Select queries which are used to display data from tables or to perform calculations
•  Action queries which are used to add, change or delete data

The tables that are going throughout this sections are here below with at least their four columns:

A. Select queries

A select query is the one which displays records from one or more tables of a database The SQL SELECT statement returns a result set of records from one or more tables. A SELECT statement retrieves zero or more rows from one or more database tables or database views basing on the selection criteria if there is one

•  A select query on one table

To design a select query, do the following:

•  Under the Create tab click on Query Design
•  In the “Show Table” window that will appear choose the table on which the query is to be run and click on Add. In this case the table BOOKS was chosen

A. Select queries

A select query is the one which displays records from one or more tables of a database The SQL SELECT statement returns a result set of records from one or more tables. A SELECT statement retrieves zero or more rows from one or more database tables or database views basing on the selection criteria if there is one

A select query on one table

To design a select query, do the following:

•  Under the Create tab click on Query Design
•  In the “Show Table” window that will appear choose the table on which the query is to be run and click on Add. In this case the table BOOKS was chosen

•  After selecting the table click on Close to close the window
•  In the table, select the columns to appear in the result. In this case only these columns were selected: BkNumber, BkCategory, BkTitle, and Author.

        Figure 1. 31. Options to select columns to appear in results of a query

•  If there is a criterion for selection specify it and if there is not one leave the criteria field empty. In this case only books in the science category are going to be displayed
•  Click on Run to get the results of the query

•  Select query on multiple tables

In a normalized database it is possible to link more than two table in order to get a more revealing display (report). For example, in the table that have been used one may need to know the names of students who borrowed science books and their combinations.

To answer such a question requires to combine columns from three tables and thanks to the foreign keys in the tables this combination is possible. The tables to combine are: STUDENTS, BOOKS and COMBINATIONS.

To make such a query do the following:

•  Under the Create tab click on Query Design
•  Chose the table that are to be used in the new query. Here select all the tables (STUDENTS, BOOKS, ORDERS and COMBINATIONS)
•  Select the columns that will appear in the results. Those columns are: First Name, Last Name, Combination and BkCategory
•  Set the selection criteria which is Science

• Run the query by clicking on the Run tab which is found under the Query Tools

Note that this kind of query that combines many tables is possible only if tables

are related by the use of foreign key.

•  Wild card characters in query criteria

Wildcard characters are special characters that can stand for unknown characters in a text value and are handy for locating multiple items with similar, but not identical data. Wildcards can also help with getting data based on a pattern match.

These characters can be used where specific characters are not known like for example one wants to search for names which start with Mu. Such names can be Muhire, Mugabo, Mutuyimana, etc

Example of a query with a wild card character:

The query that was created to display students who borrowed Science books can be modified in the criteria field by writing in it the criteria: Like “Sci*”. If there is no BkCategory field which start with Sci other than Science this query will return the same results as the previous one.

Application activity 1.9

Using the four tables (STUDENTS, BOOKS, ORDERS, and COMBINATIONS) used through this section create queries in Design View

that do the following:

          a) Students whose sex is Male

          b) Students whose name start with letter “U”

          c) Books which are in the Mathematics category

          d) Students who study in MPC option in 2010

1.10. Database normalization

Activity 1.10

Create a database “Nziza Supply Ltd” and the table “ORDERS” identified by the columns: CustomerName, Address, TelephoneNumber, CreditLimit, ItemOrdered, Quantity and Price.

This can easily be implemented in a table as follow:

However, a customer may order several items and each customer in the database may order a different number of items. This situation makes it difficult to implement the data in a relational database, since we do not know how many order entries to allow

                  1. How many order entries our database design would allow every customer?

                  2. Which problem that a database having such tables present?

The main objective while designing a database is to create an accurate representation of the data, relationships between the data, and constraints on the data designed to solve existing problems in an enterprise.

Normalization is a database design technique, which begins by examining the relationships between attributes and use a series of tests (described as normal forms) to help identify the optimal grouping for these attributes to ultimately identify a set of suitable relations that supports the data requirements of the enterprise.

The goal is to generate a set of relation schemas that allows us to store information without unnecessary redundancy, yet also allows to retrieve information easily.

The approach is to design schemas that are in an appropriate normal form.

Normalization is a series of steps designed to remove repeating groups and unwanted functional dependencies. It is also used to avoid insertion, deletion and updating anomalies. Therefore, it used to improve on existing table designs.

1.10.1. Normal Forms

The normal forms are a linear progression of rules that are applied to database. With each higher normal form achieving a better, more efficient design.

Normalization theory defines six normal forms (NF) but the most used are three.

Each normal form involves a set of dependency properties that a schema must satisfy and each normal form gives guarantees about the presence and/or absence of update anomalies. To correct update anomalies in a database, you must convert tables to various types of normal forms.

The most common normal forms are First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF) and Fourth Normal Form (4NF).

B. First Normal Form

A relation (table) that contains a repeating group (multiple entries for a single record) is called unnormalised relation. Removing repeating groups is the starting point in the process to create tables that are as free of problems as possible.

The conversion to first normal form (lNF) requires splitting the data into two groups. Tables without repeating group are said to be in the First Normal Form (1NF).

First Normal Form (1NF) says that all column values must be atomic.1NF dictates that for every row by column position in a given table, there exists only one value, not an array or list of values. If lists of values are stored in a single column, there is no simple way to manipulate those values. Retrieval of data becomes much more difficult.

A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain

Example 1:

A database has been designed to store information about property owners and rental in a table called Client Rental

As it is obvious in the table above there are cells which contain information in merged cells, all cells are not of equal size (they are atomic cells)

For a relation to be in First Normal Form, all cells have to be non-atomic cells.

The above table can be converted into a 1NF table by unmerging the cells and rewriting the information in the new cells

                    Table 1. 3. ClientRental table after being converted to the 1NF

C. Second Normal Form (2NF)

Before a table be in the Second Normal Form, it has to be in the First Normal Form. A relation is in 2NF if it is in 1NF and every non key attribute is fully functionally dependent on the primary key. Functional dependency means that wherever appears the same value in one column also must appear the same values in another column.

In the table below, wherever the RegistrationNumber 0001 appears the column Names has as value UWIMANA Claudine in the same way wherever there is 0003 as RegistrationNumber, there is RWIGAMBA Eric in the Names column.

This link between one column and another is called functional dependency and can be even in more than two columns.

In the ClientRental table above, though it is in the 1NF it has got a remarkable issue: the primary key column (ClientNo) does not contain unique values. It contains also a lot of repeated information which consumes a lot of memory

space if the table has many records and if there are many tables in the database having the same anomalies.

To convert the above table in the 2NF identify the functional dependency and put together in one table the columns that are functionally related.

After identifying the functional dependencies, it is clear that the whole table has two sets of functional dependencies which can help generate two tables. Those tables can be Client (ClientNo, CName) and PropertyOwner (propertyNo, pAddress, rent, ownerNo, oName). When these two tables are created, two columns rentStart and rentFinish are alone. We create its table and because the content in those two columns is understandable only when there are the ClientNo and propertyNo those columns are added to that table and may serve as a concatenated primary key.

Figure 1. 35. Resulting tables after putting the database in 2NF

Note: the columns ClientNo and propertyNo serve as the concatenated primary key for the Rental table

However, a close analysis of the ProperyOwner table reveals that there is functional dependency between the columns ownerNo and oName. That functional dependency can be removed by splitting the tables into two. We call the two resulting tables owner and the propertyOwner. To keep a link between these new tables the propertyOwner table will have the ownerNo column as a foreign key.

D. Third Normal Form (3NF)

Relation in 2NF have less redundancy than those in 1NF. However these relations may still have update anomalies. This update anomaly is caused by a transitive dependency and must be removed for the database to be in Third

Normal Form.

To be in third normal form, the relation must be in second normal form, all transitive dependencies must be removed and there is no non primary key attribute which is transitively dependent on the primary key. The transitively dependent attribute is removed from the relation by placing that attribute in a new relation.

In the tables shown above there is no transitive dependency therefore the database is already in the 3NF

Application activity 1.10

      1. Explain why it is necessary to have a normalized database

      2. Convert the table below to the 3NF

1.11. Database project

A company that rent cars to tourists manages its information manually by using papers and pens. It wants to have a computerized Access system that will help them efficiently manage its information.

           1. By doing a research explain the steps to go through in order to have this database built

           2. By going through those steps build that database

Activity 1.11

Database project is aimed at providing content, that, when properly mastered will enable the reader to create an effective Access database that can solve a real life problem. Examples of databases are: school database, library database, Home expenses database, Gate management database, fixed asset management database and many others.

The development of any database system goes through a number of steps that will have to be gone through even in the project in this book because when those steps are not followed, the solution (new database system) does not accurately solve the original problem which was in place before thinking about the database system.

The steps in developing this project are those used in developing a database system and those are:

•  Investigation
•  Analysis
•  Logical design
•  Implementation
•  Data entry

Project I. Investigation

Task one: Go to an institution for which an Access database is needed like a secondary school and investigate to come up with information that can be based on in thinking of the tables of the future database

A. Information/Requirements gathering

The first step in the process aimed at coming up with a new database is requirements Gathering. During this step, the database designers have to interview the customers (database users) to understand the proposed system, obtain and document the data and functional requirements. The result of this step is a document including the detail requirements provided by the users.

As a person, consultant given the task to build a new database system or entrusted to improve the one already in place, you have the task to thoroughly understand the business process of the institution for which you want to have a system built.

Be aware of the existing ICT materials and how they are used to manage the current database whether it be a paper based database, or a computerized database that needs improvement. Know also the institution’s documents and their use to get clues of the whole institution’s business

•  Existing database system description

Collect all the information in the institution so as to come up with the service offered in that institution and how the different employees who are the prospective users of the new system interact.

In that institution there may be receptionists, cashiers, accountants, trainers, professionals, supervisors. Know the role of every one and the flow of service from one to another.

Example: If the database to be built is for a secondary school, at the end of this stage you are going to know that there are administrative staff and teaching staff. The administrative staff group is composed of: Headmaster, Accountant, Secretary, Director of Studies, Discipline Master, Matron, Patron and Teachers. There are also other staff like cooks, gate keeper, cleaners, etc.

Know the organization structure of this institution (school) in order to know the chain of command which must also be modelled in the database as if the database is to be accessed through an interface supervisors will view more options than subordinates.

•  Available ICT materials

Gathering this information will make you aware of the readiness of the investigated institution. If the institution has no single computer, it will not be able to use the database you want to create for them and that can prove a waste of money.

Check the specifications of the computer and make sure the Access version you want to use will be accepted by that computer and that staff have the basic required knowledge.

•  Difficulties with the existing system

If the institution has no database system and manages information in a traditional way by using papers, show the difficulties raised by that way of managing information and therefore the reason why they need to have a computerized database.

If there is already a database which can be Microsoft Word and Excel files, show why there is an need to shift form that way of managing information to the new Access database.

The difficulties faced by the existing system are the ones based on in determining whether the solution to replace or improve the current system is valid. Show the proposed solution and its advantages so as to finally weigh the advantages against the cost.

•  Establish the Data Flow Diagram for the institution

By the use of specific symbols a dataflow diagram is created to represents how information about different operations in an institution circulates.

Symbols used to build that data flow diagram are to show the origin and destination of information, data flow, treatment flow and data deposit or storage.

These symbols are used:

Example of an institution Data Flow Diagram:

The DFD below is for a car admission in a garage where the client comes at the reception with the car, requests for information, have the car sent to the mechanics by the receptionist for checkup, the mechanics give feedback to the receptionist to inform the car owner and the car is sent to the workshop for repair.

Project II. Analysis

Task 2: Analyze the information gathered in “Project One -Task One” and deduce tables and the relationship among them. The result to this is tables in an Entity Relationship Diagram with table columns but without data types

The aim of analysis is to obtain a detailed description of the data that will meet the requirements of the future system users so that both high and low level properties of data and their use are dealt with. These include properties such as the possible range of values that can be permitted for attributes such as, in the School Database example; for instance, the Student course code, course title and credit points.

By the end of data analysis a Conceptual Data Model is produced

The conceptual data model provides a shared, formal representation of what is being communicated between clients and developers during database development it is focused on the data in a database, irrespective of the eventual use of that data in user processes or implementation of the data in specific computer environments. Therefore, a conceptual data model is concerned with the meaning and structure of data, but not with the details affecting how they are implemented.

The conceptual data model then is a formal representation of what data a database should contain and the constraints the data must satisfy. This should be expressed in terms that are independent of how the model may be implemented.

Project III. Logical design

Task 3: Improve the ERD model by showing the data types used, the primary and the foreign keys.

Database design starts with a conceptual data model and produces a specification of a logical schema; this will determine the specific type of database system (network, relational, object-oriented) that is required. The relational representation is still independent of any specific DBMS, it is another conceptual data model.

We can use a relational representation of the conceptual data model as input to the logical design process. The output of this stage is a detailed relational specification, the logical schema, of all the tables and constraints needed to satisfy the description of the data in the conceptual data model. It is during this design activity that choices are made as to which tables are most appropriate for representing the data in a database. These choices must take into account various design criteria including, for example, flexibility for change, control of duplication and how best to represent the constraints. It is the tables defined by the logical schema that determine what data are stored and how they may be manipulated in the database.

Logical data models add further information to the conceptual model elements. It defines the structure of the data elements and set the relationships between them.

Project IV. Implementation

Task 4: Referring to the logical model of your database construct it using Microsoft Office Access 

Implementation involves the construction of a database according to the specification of a logical schema. This will include the specification of an appropriate storage schema, security enforcement, external schema, and so on.

In our case the database will be built using Microsoft Access 2013 and forms are needed in order to have an interface through which to interact with the database.

The tables that will be created will have columns shown in the logical model and the same goes for the primary keys and foreign keys.

Project V. data entry

Task 5: Now that the database has already been built enter in the data that the institution had on papers, word or excel files so as to make it usable.

Data can be entered from the hard copies but it can be imported from Word or Excel applications. To import data from other applications to Access follow these steps:

•  On the External Data tab click on one of the type of applications (Excel, Access, Txt File, XML file) from which to import data. Here we choose Excel
•  In the new window that will appear browse the file to import and choose one of the three options and click OK

                               Figure 1. 38. Window to browse for files to import in Access

In the new window specify that the first row contains column headings if it meets the criteria of a valid Access table heading and click Finish or Next for additional options.

Note that if the option to make the first row be the Access table heading, Access will create default headings in the form of ID, Field1, Field2, Field3, etc

                                          Figure 1. 39. Window to specify heading

In the window that will appear specify some information on the columns to import like Field names which can be changed or kept as it is, specify if the column is to be indexed or not, specify the data type or specify which columns not to import

      Figure 1. 40. Access window to specify field names, data types and columns to import

In the next window follow the prompts and specify the primary key and the table name once it is in Access.

Application activity 1.11

Your school has a problem of managing movements of goods and people in and outside of the school as this is currently done manually by using a registry book which is not easy to read.

Develop an Access database system called GATE MANAGEMENT SYSTEM in all that pass by the get is registered and all that gets out of it is marked.

1. The system will be able to show in real time the number of students, teachers and administrative staff in the school and those who are absent in working hours.

2. It will also be able to show the number of items like hoes available in the school (as these passed by the gate when they were brought in the school)

End Unit Assessment 1

Question 1.

The database below is composed of two tables. Use it to answer the asked questions

i) What is the primary key for each table and give the reasons.

ii) Identify the foreign key in the BOOKS table.

iii) Identify the candidate keys in both tables.

iv) Identify the relationship between BORROWERS and BOOKS tables and draw the E-R diagram.

v) Does the BOOKS table respect the referential integrity rule? Why or why not ?

Question 2.

A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company.

Draw an ER diagram that captures this information.

Question 3.

a) Is the above table normalized? Explain your answer?

b) Explain the anomalies found in the table?

c) Normalize the table as required

i)          Many to many (M:M)