Creating a Database Manual

The African proverbs, sayings & stories website

www.afriprov.org

Creating a Database

www.afriprov.org’s

Creation of an African Stories Database

By Jennifer Ehrentraut

With Contributions from

Nicholas Adongo

Rev. Joseph G. Healey

Table of Contents

FOREWORD 2

INTRODUCTION 3

Priniciples of afriprov.org

Background / History 4

Administration 5

Early modifications 6

Devising a plan

Brainstorming 7

Implementing ideas into actions 9

PREPARATION

Fields for database 10

Updating to FrontPage 11

Sorting thru data 12

Purchasing new scanning software 13

OBJECTIVES

Redefining the purpose of our database 14

Constructing the database

Creating the database 15

Choosing Table(s) and Fields names 16

Creating the Table(s) 17

Creating Fields 18

Accessing the database 19

Adding data 20

Formatting Text 21

Adding data 22

Editing data 23

Creating an “Input Story” page 24

Knowledge of PHP 25

Writing a PHP Program 26

Creating simple search fields 27

Design and layout 28

Posting database to site 29

Beta version

Feedback from team members 30

marketing

Encouraging the use of our database 31

Work in Progress

Tweaking search features 32

Audio files of stories 33

Contact Information 34

index 35

FOREWORD

By Rev. Joseph G. Healey

AAfter we started the Internet Website on African Proverbs, Sayings and Stories (www.afriprov.org), in 1998 the first five years focused on collecting African Proverbs and Sayings. During these last four years we have concentrated on expanding our collection of African Stories. After a great deal of trial and error that is well documented below, we installed the online database management software MySQL (Structured Query Language) through our host CNET Global and created the expandable online African Stories Database. In the last year we have received requests/ideas from other people to explain our “process” so they might start something similar on another subject/topic:

1. Jeanne Hinton: Website that includes Small Christian Community Stories.

2. Sally Freeman: Website on Folk Medicine.

3. Professor Wolfgang Mieder: Suggestion that we have a large proverb collection database that includes all registered African proverbs (see his answer No.120 in interview with Chilukuri Bhuvaneswar in DPL V Bibliographer).

So we hope this full explanation of our experience will help others to start their own expandable online databases on subjects/topics of their interest.

Please do not be put off by the considerable technical language. You may need to consult a specialist for such things as PHP programming information.

Introduction

The lifespan of www.afriprov.org broadly resembles the message expressed in the Sukuma (Tanzania) proverb: That which is good is never finished.

In relation to this Sukuma proverb, we continue to treat our site as a “work in progress”. With a solid foundation of information, purpose and resources, we have been able to make modifications based upon the “times” (advances in technology, software upgrades and users needs).

Included please find the creation process of our African Stories Database, as well as the journey of ideas and improvements that have taken place in order for us to maintain an informative, purposeful, user-friendly web site.

Principles of Afriprov.org

Background / History

In 1998, through the help of the Urban Ministries Serving God (UMSG) based in Nairobi, Kenya, we started the Internet Website, www.afriprov.org, with the intent to share work and enthusiasm for African Proverbs, Sayings and Stories; compile an ongoing collection of a wide variety of resources; and support and encourage the global community involved in the world of African Proverbs, Sayings and Stories.

We introduced an Email Mailing List (a type of “Electronic Mailing List” in the Internet World Wide Web language). This feature was created to develop and expand upon the worldwide network of people interested in African Proverbs, Sayings and Stories. The Email Mailing List enabled interested parties to ask questions of and obtain materials from those who shared similar interests. In order to receive emails sent to the list, one must “subscribe” by clicking the “Un/Subscribe to E-Mailing List” link on the left hand side of the main page under the category “Services” and follow the designated procedures.

From its inception, Afriprov.org was interested in not only sharing African literature, but also focusing on promoting user participation. The web site team was aware of the importance of viewer contributions and the unique aspects of knowledge and insight individuals could provide. In taking this into consideration, the site adopted a Message Board. This feature provided list members of the www.afriprov.org community with a public place to congregate and exchange ideas, share resources, and participate in discussions regarding topics of their interest.

Along with these two main features, we provide 14 various types of resources for our users: African Proverbs Calendar, African Proverb of the Month, African Stories By Season, African Stories Database, Bibliography, Book Reviews, CDs, E-Books, Links, Maps of Africa, Meetings, News Archive, Weekly African Proverbs, What’s New.

Together with our 14 resources, we provide users with three main services:

Email Mailing List, Message Board, Regional Centers.

Principles of Afriprov.org

Administration

The Moderator of African Proverbs, Sayings and Stories Website and Email Mailing List is Father Joseph G. Healey, based in Nairobi, Kenya. His e-mail address with America Online (available worldwide) is: JGHealey@aol.com. The Assistant Moderator is Mr. Joseph Kariuki based in Nairobi, Kenya at: kariukiprov@yahoo.co.uk. The host of our website is CNETGlobal International that has a registered Internet Domain at: www.cngs.com. The site host is Sam Wanyoike, based in Ontario, Canada at: sam@cngs.com. The Administrator of the website is Mr. Nicholas Adongo based in Manhattan, Kansas, USA at: adongoadongo@yahoo.com.

Principles of Afriprov.org

Early modifications

Our Email Mailing List needed to be regulated. In order to do so, controls needed to be set to moderate the two-way sending of email messages to recipients of the mailing list. Any messages sent to the list, thereafter, would require moderator approval before being distributed to the list.

This feature helps to regulate the quality and quantity of messages from users. In July 2006, new Message Board software named vBulletin was purchased to better manage Spam and junk posts while giving legitimate users a friendlier environment in which to congregate. With an approval feature along the same lines as the Email Mailing List moderator approval function, this software provided the moderator with the ability to review and approve or disapprove of each new message before it is posted.

DEVISING A PLAN

Brainstorming / ideas for the development of an African story collection

The brainstorming phase of this project took place at the Healey Development office in Morristown, NJ, including:

Betti Baldan, Office Manager, William E. Simon & Sons

Jennifer Ehrentraut, Project Coordinator, Healey Development

Father Joseph G. Healey, Moderator of Afriprov.org

Melanie Hudson, Office Manager, Healey Development

Rob Oriolo, Systems Administrator, William E. Simon & Sons

Objective: To compile all collected / published / written African stories in a uniform format.

The following four segments were the main ideas for the creation of our collection.

Microsoft Access

Objective: To provide a collection of African Stories data organized by designated field for use in Maryknoll Library computer system.

Pros

This program was previously used for collections of Father Joseph G. Healey’s African Stories
Available help in the office from a specialist in Access

Cons

Not easily accessible for those unfamiliar with Access (not user-friendly)
Unpopular program for computer owners to have

Paper Copy

Objective: To compile all gathered material into one tangible resource for Maryknoll archives.

Pros

Concrete resource of all African stories
Book of African Stories would be added to Maryknoll Library

DEVISING A PLAN

Cons

Complications in organizing / indexing stories
Only available at Maryknoll Library (not easily accessible)

CD Version

Objective: To provide a compact disk version of collection of stories for use on Maryknoll Library computer system or to be checked out by interested parties for use on their personal computers. (Similar to “The Wisdom of African Proverbs” CD edited by Stan Nussbaum, produced by GMI.)

Pros

More user friendly version (than Access) of computerized collection of stories
Entire collection of stories is stored on CD’s that can be used at various locations

Cons

Awareness of collection of stories limited to Maryknoll
CD’s can be an outdated way to share computerized information (updates, editions, etc.)

Web- Based

Objective: To provide a user-friendly collection of stories with maximum options for accessibility.

Pros

Can be located and used by anyone with internet access
Maximum availability / accessibility / user-friendliness of all of our options
Already in possession of domain and website in which database can be added to
Free access to database software (MySQL) through our host CNET Global

Cons

Complex programming / setup by afriprov.org team
Exceeds original expectations to project size, depth

DEVISING A PLAN

Implementing ideas into actions

Moderator, Father Joseph G. Healey, and Project Coordinator, Jennifer Ehrentraut, proposed the idea of a story database to Site Administrator, Nicholas Adongo who in turn, would need to create a plan to propose to Site Host, Sam Wanyoike.

The Project Coordinator researched databases for samples and ideas of structures and formats for this story project. Such examples that were researched are: public library databases, college library databases, Yahoo search engine features, Google search engine features, and ProQuest database structures.

PREPARATION

Fields for Database

Designated fields to categorize and contain information are needed not only for search capabilities for users, but are also needed to enter information into the database. In trying to cover as many aspects as possible, we came up with eleven fields,

1. 1. Title
  2. Theme
  3. Sub-Theme
  4. Author’s Name
  5. Author’s City
  6. Author’s Country
  7. Genre/Type
  8. Locale
  9. Year of first use
  10. Publication information
  11. Story Text

PREPARATION

Updating to Microsoft FrontPage

Due to the modifications in planning, our team decided to revolutionize from the previously existing ways of HTML editing and purchase Microsoft FrontPage. Rather than using the past ways of editing HTML codes in notepad and uploading to the File Transfer Protocol, Microsoft FrontPage is a website creation and management tool that can easily manipulate text, pages, etc. into an HTML format for easier modifications to the site.

FrontPage is also used to convert the text of stories into proper paragraph format for the database. (Later explained in Adding stories thru the Control Panel)

PREPARATION

Sorting thru data

In hopes to include over 1,000 African Stories, the designated stories would come from a variety of locations including:

1. Joe Healey African Stories

217 stories in Microsoft Access document
Information already sorted into designated fields

Content for each field would need to be individually copied and pasted into database

1. Complete Revised Text of African Stories American Version
868 page Microsoft Word Document containing stories
Information is not previously sorted by designated database fields
Designate fields
Content for each field would need to be individually copied and pasted into database

1. Once Upon a Time in Africa

CD Version of book containing 86 stories
Information is not previously sorted by designated database fields
Designate fields
Content for each field would need to be individually copied and pasted into database

1. African Stories for Preachers and Teachers

CD Version of book containing 139 stories
Information is not previously sorted by designated database fields
Designate fields
Content for each field would need to be individually copied and pasted into database

1. Other stories (Collection of chosen stories on loose-leaf, single sheets of papers, from books, from magazines, etc)

Scanning of stories required to convert paper forms to manipulative text forms for computerized use
Information is not previously sorted by designated database fields
Designate fields
Content for each field would need to be individually copied and pasted into database

PREPARATION

Purchasing new scanning software

Rather than retyping hundreds of “other stories” we opted to purchase Omnipage 15.0, a form of ScanSoft software that enables users to convert scanned paper documents into editable digital files. The program provides an option to manipulate scanned files to be edited and saved as Microsoft Word documents.

OBJECTIVES

Redefining the purpose of our database

In a matter of weeks we were on our way to executing our original objective of “compiling all collected / published / written African stories in a uniform format.” All things considered, we wanted to redefine our objective to better suit the tailoring of our plan.

New objective: To dedicate a specific section of the African Proverbs, Sayings and Stories website to an extensive, interactive collection of over 1,000 African Stories, which would further enhance www.afriprov.org’s purpose to “share collections, research, writings, applications, experiences, and enthusiasm for African Proverbs, Sayings and Stories” (taken from the site’s Welcome Message)

CONSTRUCTING THE DATABASE

Creating the database

To create the database, log on to the Control Panel (one of the administration software options provided by the service provider) of the website.

Click on “MySQL Databases” icon.

Type the name of the database where it says,

MySQL Database:

Click on “Create MySQL database” button.

NOTE: The service provider has a setting that automatically prefix’s the name of the database with the website name followed by underscore character. Hence, if we were to type in data, this sequence of commands would create the database afriprov_data.

CONSTRUCTING THE DATABASE

Choosing names for the Table(s) and Fields

Before actually creating the database, there is a two-step process users must take. First, one must determine the name of the database, names of table(s) and the names and types of the fields in the tables. Second, one must write or issue commands provided by the database management software (MySQL, in this case) to create the above-mentioned items.

For example, the designated information for our African Stories Database contains the following:

The database name is afriprov_data that has one table called Story.

Story has 13 fields that store the Internal Identification Number, Title, Theme, Sub-Theme, Author’s Name, Author’s City of Origin, Author’s Country of Origin, Genre/Type, Locale, Year of Original Publication, Publication, Story text, and Link to Audio File (a newly added feature, later explained in Adding audio files of stories on page 33).

To create the tables and fields, please see the following section Creating the table(s) and fields.

NOTE: The “Internal Identification Number” field does not require any inputting of information. It automatically counts the number of stories contained in the database.

CONSTRUCTING THE DATABASE

Creating the Table(s)

To create the table we issue SQL commands. To do this:

Log on to your Control Panel.

Open “phpMyAdmin”

On the left hand side of page, select your database.

Type the name of your table.

Insert the number of fields you would like your database to contain.

Click, “Go.”

NOTE: The above mentioned process, should look similar to the following:

Create new table on database(your database name will appear here)

Name: Number of fields:

CONSTRUCTING THE DATABASE

Creating Fields

After clicking “Go” in the previous section Creating the Table(s), you are now ready to insert the names of your fields into the database.

Type in the name of the “Field”

Under the next column labeled “Type”, make sure VARCHAR is selected. (VARCHAR stands for various characters, which will enable all aspects of your field to contain various characters, numbers, letters, etc.)

Under the next column labeled “Length/Values” type in the desired number of characters you wish for each section of this field to contain.

NOTE: We suggest choosing a number such as 255, which will enable flexibility in the lengths of the information you will be entering.

The “Collation” and “Attributes” columns shall be left blank for the default settings to go into effect.

In the “Null” column, you must choose “Null” or “Not Null”. If “Null” is chosen, in specific instances, the field may be left blank. If “Not Null” is chosen, the field must contain inserted information (a value).

The “Default” and “Extra” columns shall be left blank.

Click Save when you have finished creating your fields.

You are now ready to begin inserting your data into the database. (Please see

CONSTRUCTING THE DATABASE

Accessing the database

- Log on to the Control Panel of your site.
- Open “phpMyAdmin”
- On left hand side of page, choose your created database and click on it to select.
- A page will be displayed containing the following options across the top:
  - - - Structure
      - SQL
      - Search
      - Query
      - Export
      - Import
      - Operations

On the left hand side of page, select your table by clicking on the name listed.

You will now see the following options listed across the top of the page:

- Browse
- Structure
- SQL
- Search
- Insert
- Export
- Import
- Operations
- Empty
- Drop

You are now in the correct place to add data (See page 21, Adding data) and edit data (See page 22, Editing data).

CONSTRUCTING THE DATABASE

Adding data

Manually adding stories to the database is actually quite simple. Assuming you have recently created your table and fields, and you are logged into your database (according to previous section, Accessing the database on page 19) the next step would be to insert your designated information into the according fields.

Choose Insert from the options listed across the top of the screen.

Insert your data into the fields under the column Value.

After information is entered, click “Go” on the bottom of the page.

The information in your first row has now been inserted.

To view the newly inserted information, click on Browse from the options listed on top of page.

CONSTRUCTING THE DATABASE

Formatting Text

Fields that will contain formatted text, require additional procedures before the formatted text can be inserted properly into the database.

To enter information into your text field (or any field where formatted text will be inserted) one must arrange the text in html format (whether it be with FrontPage or your preferred method of coding your text)

The following is a sample of how FrontPage can be used for this step:

Invoke FrontPage and open a new blank page (File -> New -> Blank page)

Make sure you are in the design view. Check bottom of screen. There are four views – Design, Split, Code, and Preview.

Copy only the text from word and paste it in FrontPage.

Format text if necessary

Go to code view (click code at the bottom).

Copy all the text between <body> and </body> and paste in your desired field in the Control Panel (NOTE: do not include the body tags.)

CONSTRUCTING THE DATABASE

Adding data thru the Control Panel of your site

Log on to the control panel of your website

Click on phpMyAdmin (located under Site Management Tools)

On left hand side of page, choose your designated “Table”

Click on tab labeled “Insert”

Under the “Value” column insert your desired information into the chosen fields

Save by clicking “go”

Repeat above procedure for the others.

CONSTRUCTING THE DATABASE

Editing data

Log on to the control panel of your website

Click on phpMyAdmin (located under Site Management Tools)

On left hand side of page, choose your designated “Table”

Click the “Browse” tab

Go to appropriate row (record) and click on edit (has a pen picture) on that row.

Make your desired changes

Save by clicking “go”

CONSTRUCTING THE DATABASE

Creating an “Input Story” page

An improvement to adding stories without going thru the Control Panel is to “automate” the input process by creating an HTML form with chosen fields and then write a php (a free programming language software provided by the serve provider) program and “tie” it to the “submit” button of the form. . So all the person responsible for entering the records in the database has to do is fill in the form and click “submit”. Once the “submit” button is clicked, the php program is executed. This program connects (logs on) to the database using a username and password, runs the above SQL command and finally disconnects (logs off). The user name and password are created by invoking “MySQL databases” from the control panel, typing the desired username and password and clicking “Create MySQL user” button.

To view our sample of this page, please click on the following link: Input Story page

NOTE: To learn how to write a “php” program and “tie” it to “submit” Please see following sections titled Knowledge of PHP on page 25 and Writing a PHP program to “tie” to a command, on page 26.

CONSTRUCTING THE DATABASE

Knowledge of PHP

Fortunately, our Site Administrator possesses a knowledge and background in PHP command writing.

We understand not all database developers and readers of this manual may contain this knowledge.

Our best offer of advice is to check out some of the following websites or look into acquiring a book containing PHP programming information.

www.php.net

www.en.wikipedia.org/wiki/php

www.phpusergroups.org

www.php.meetup.com

Books on PHP at Amazon.com

CONSTRUCTING THE DATABASE

Writing a php program to “tie” to a command

A php program is a text file whose contents are lines of commands that the php software

(interpreter) executes.

To write a php program:

Open a text editor e.g. notepad.

Type in the program then save and give it a name with a “.php” extension e.g. “myfile.php”.

Next, to “tie” it to the Submit button on the HTML form, specify the name (e.g. myfile.php) as the action value of the FORM tag (e.g. <FORM action=”myfile.php” method=”post”> </FORM>)

Finally, remember to upload these two files to the website.

CONSTRUCTING THE DATABASE

Creating simple search fields

In order for the records to be displayed on the users web browser, we create a HTML form that has “search by”, “sort by” and “search for” fields and “go” button. We then write a program in php and “tie” it to the “go” button. All the program does is connect to the database using the user name and password, query the database and finally display the data in good format in the user’s browser.

CONSTRUCTING THE DATABASE

Design and layout

Like any page, there are many ways to change the design/layout to suit your personal needs and interests. To modify the template or design/layout of the main database search page, we created a document in PowerPoint, which was than transferred to FrontPage for uploading to the site.

CONSTRUCTING THE DATABASE

Displaying the contents of the database / Viewing it on your site

By creating the database as described, the database has been posted to your site already (remember we logged on to the control panel of the site). Also by creating the input story program page and the simple search fields / feature as above we have posted the remaining pieces to complete the search application.

Domain names (e.g. afriprov.org) are usually registered by the service provider. If one has a website already (like we did),

1. The database files are created automatically when creating the database via control panel as explained in the creation section.

2. However, you need to create a folder where your PHP programs and HTML files will be stored.

3. User must then upload the stored files to the designated folder.

For example, we created the folder “story” that contains the files like “searchstory.htm”, etc.

This is why you see: http://www.afriprov.org/story/searchstory.htm

NOTE: To create the folders, you use the control panel.

BETA VERSION

Feedback from team members

Before publicizing our African Stories Database, we sent the Beta Version to all of our web site team members as well as several other associates and asked for their feedback (personal suggestions, ideas, likes, dislikes, etc.).

We found this to be a very positive idea, for the responses of others provided valuable insight as to whether or not we have fulfilled the objectives of the database.

MARKETING

Encouraging the use of our database / creatively marketing our new product

To help promote our new feature, the African Stories Database, we posted the following message on the African Stories Database Search Page, to our Email Mailing List, and on our Message Board:

The African Stories Database has been created to provide searchable, user-friendly access to a collection of over 1,000 African stories (of which 222 stories are presently in the database) including folk tales, historical fiction pieces, myths, parables, poems, prayers, riddles, song-proverbs and true stories. This feature enables users to find African stories for a wide variety of interests and occasions. The content of this database is made of up stories containing over 140 different themes and sub-themes. The stories integrated in this database are in the public domain and can be re-used and interpreted as you wish. If you use a story please acknowledge the source. If you use a story we would appreciate you letting the Moderator (Moderator@afriprov.org) know so we can share this news with others.

To locate the African Stories Database go to our main page. On the left-hand side, under “Resources,” please find the newly added category “African Stories Database” or click on the following link http://www.afriprov.org/story/searchstory.htm.

WORK IN PROGRESS

Tweaking our search features

Tweaking / improving Simple Search features / options on our Search page to cover more specific search ranges.

For example, we are working on being able to accommodate to questions similar to the ones listed below:

What if a person wanted to search for all the stories with “Smith” as author and theme as “health”?
One might want to search for all stories in locale “Kenya”, “Uganda”, or “Tanzania”.
One might want to search for all stories mentioning the “Sukuma Ethnic Group” that do NOT have locale as “Tanzania”.

Essentially, after further testing and modifications, we would like to add an “Advanced Search Feature” as well.

WORK IN PROGRESS

Adding audio files of stories

The following is from our What’s New page:

In November 2006, our Moderator Father Joseph Healey, received a unique request from a woman who works in the audio library department of a public library in Los Angeles, California, USA. She reads local news, current events and short stories for the blind. “I came across your stories on http://www.afriprov.org and absolutely loved what I read. I think African Proverbs and Stories should be told/read/heard regularly in our society and media, especially around children,” she wrote.

The woman was looking particularly for African Christmas Stories for blind children. She requested permission to create audio files of several stories featured on our African Proverbs, Sayings and Stories website to add to the library’s system where “children can listen via a CD player/audio system in-house.”

Now we are especially proud to introduce the incorporation of these created audio files to our African Stories Database. Presently, we have added the first audio file to the story I Had Lunch with God (presently Story No. 71 in our alphabetical database, but this will change as more stories are added).

To read this story in our database and listen to the audio file, you can click on the above link. Then click on Listen to audio version. To simply listen to the audio file, click here: Listen to “I Had Lunch with God”

CONTACT INFORMATION

For additional information regarding this document, please contact: