MAIN BODY NOTES | DATABASES

DATABASES - Write notes.

A database is a collection of structured and related data. Items are organized so as to provide a consistent and controlled access to the items. Items in a database are stored in a computer system.

DATABASE CONCEPTS

Traditionally Filling methods.

This is the old way of organizing files. This method is where data is stored within a single paper file or table.

Disadvantages of traditional filling methods.

Unnecessary duplication of data.
Boredom and time wasting while searching for a record.

Misleading reports due to poor data entry and organization.

Poor updates of records.

Computerised Database (DBMS).)

Computerised database use database management software (DMBS) to manipulate data.
DMBS facilitates the creation, organization and maintenance of databases.

Examples of databases.

Oracles.
FoxPro.
Dbase.
Lotus Approach.
Microsoft access (Ms Access).

Functions of database.

Allow the user add or delete records.
Update or modify existing records.
Organise data for easy access, retrieval and manipulation of records.
Acts as an interface between database and other application programs.
Ensures security for data in the database by safeguarding it against unauthorised access and corruption (damage).
Keeps statistics of data items in the database.
Features of a database management software.
- Most database software contains a collection of features that provide the user with a means to manipulate data in a database.
- These features include:
- Tables,
- Queries,
- Report Generators,
- Form interface and
- computer programming language.
- Microsoft Access database software which will be considered in this book, it has two distinct features used to automate operations namely;
- Macros and
- - Tables/file structure.
- This is a database structure that is used to hold related records. Tables are organised in rows and columns with each row representing a record while each column represents common fields in each record.
  - Queries.
- A query is a statement used to extract, analyze or request for specific data from one or more tables. Because query is a question posed to the database, it returns a result for the user.
- Query statements are written using a special language called structured query language [SQL].
- The user creates a query by writing structured query language statements.
  - Forms/input system.
- A form is a graphical interface that resembles an ordinary paper form.
- Enter into or view data from database. However, a database form enables the user to view and enter into a table. Form interface is a more convenient and easy way of entering and viewing records from a table.
- Reports.
- Most database system provides the user with a tool for generating reports from underlying tables or queries.
- It is the report generator that provides the user with a means to specify the output layout and what is to be output or printed on a report.
- Macros
- Some database software provides the user with a tool called a macro that can be used to automate frequently performed procedures or tasks. For example, if you frequently use a particular form when you start a database program, you need to create a macro that automates the opening of the form.
- - Programming module.
- When your database becomes more and more complex, you may need a more powerful tool than the macros to automate your database operations further.
- Some database software comes with their own computer languages associated with them. For example Microsoft Access comes with a language called Visual Basic included as a Module in the software. Using this feature, you can create a program that will print a query result over and over again until a certain condition is true. This can be Illustrated using a simple statement like;
Print StudentsReport until NumberofStudents = 40(Not a real command

Anyway).

Data organisation in a database.
- One of the functions of a database system is to organise data for easy Access, retrieval and manipulation. Data is organised from the simplest Form called a field to a very complex structure called a database.
- Fields.
- A field is a character or a logical combination of characters that represent a data item. For example, in a class, the student name is a field.
- Records
- This is a collection of related fields that represent a single entity. An example of a record is the student report card that may contain the students name, admission number, class, total marks, average and grade.
- Files/tables
- A file is a collection of related records. For example, the students file in a school database contains the details of all the students in the school.
- Database.
- This is the highest in data organization hierarchy that holds all related files or tables. For example, a school database may contain students and staff tables/files.
- Creating a database using Microsoft access
  - Microsoft access (Ms Access) is a package in Microsoft office suite used for creating and manipulating databases.
  - Because there are different versions of Ms Access such as Ms Access 95/97/2000 and XP, a general approach for carrying out tasks has been adopted in this book.
  Starting Microsoft Access.
  1. From Windows desktop, click the start button.
  2. Select Microsoft access from the programs menu.
  3. In the dialog box that appears when Microsoft Access starts, click blank Access database then OK.
  4. The Save As dialog box appears. This lets you specify the name and location where your database will be stored. Click Create to save the database.
  5. Microsoft Access database objects window is placed from which you can choose the type of object to create such as tables, forms, queries etc.
  Starting Microsoft access.
  
  To create a new database:
  1. Click start, point to programs, Microsoft office then access 2003
  2. On the New File, click Create New.
  3. On the New file pane click Blank database
  4. Select the location where the database will be created, type the database name then click.
  The new Database dialog box appears. This lets you specify the name and location where your database will store .Click Create.
  1. Microsoft Access database objects window is displayed, from which you can choose the object you wish to create such as tables, forms, queries.
  The objects window.
  - the objects include:
  - Tables
  - Queries
  - Forms
  - Reports
  - Pages
  - Macros
  - Modules
  Microsoft Access screen layout.
  - Like in other application packages discussed earlier, MS Access application Window has a title bar, menu bar toolbars and status bar. However instead of having a work area like that of MS word or excel, it provides the user with an object window from which you choose the type of an object to work with such as tables, forms reports, etc.
  Exiting from Access
  - Like any other application discussed earlier, you exit from Microsoft Access by pressing Alt+F4 or on file menu click Exit
  Guidelines on designing good database
  - Good database design mean better storage, security and little maintenance effort. To design a good database:
  1. Carefully study the requirements of the user in order to define all the data inputs, outputs and relationship required.
  2. Design a draft database on the paper to determine the number of files or tables required.
  3. Divide the information into separate fields, records and tables to allow flexibility in manipulating the database.
  - This process of dividing information into independent tables to avoid repetition of data entries items is referred to as normalizing a database.
  - Normalization is the process of dividing information into independent tables to avoid repetition of data entries items.
  1. Define a field for each table that will be used to identify each record uniquely. This field is referred to as a primary key..
  - Primary key is a field for each table that will be used to identify each record uniquely.
  1. Give the most important fields the first priority when constructing a table structure. Important field are those that are used in sorting and querying the database.
  2. Design data entry forms needed for the database.
  Creating a table structure.
  - To define a table structure:
  1. In Microsoft Access objects window, click the Tables tab then New
  2. In new table dialog box, select Design View
  3. Using the grid displayed, enter a unique name for each field in the table. A field name must start with a letter and can be up to a maximum of 64 characters including letters, numbers, spaces and punctuation
  4. Choose appropriate data type before adding the next field. By default, Ms access insert text as a data type.
  5. To save the table, click the save button on the standard tiil bar or save from the file menu.
  6. Access will ask you whether you want to create a Primary Key, click Yes.
  Description of field data types.
  - The type of data to be used in a database must be clearly defined for the purpose of manipulation and storage.
  - Data types are items in table design that describes the field and what it represents.
  - For example, if a field is to be used for calculation, it must be defined as a number (is an example of a data type).
  The data types allowed in Ms Access include:
  - Text.
  - This type includes alphabetic letters, number, space and manipulation. Use this data type for fields that do not need to be used for calculations such as names, place, identification numbers etc. This type of field accommodates a maximum of 255 characters.
  - Number.
  - These are fields made up of numeric numbers 0 to 9 that are to be manipulated mathematically.
  - Memo.
  - This is a field made up of alphanumeric (both alphabetic and numeric) data. Instead of using text, use this data type if you need to enter several paragraphs of text as it accommodates a maximum of 32000 characters.
  - Date / Time.
  - Used to identify a field as either a date or time. This is because date/ time values can be manipulated mathematically in a database. For example, you can calculate the age of person from the date of birth to the current age.
  - Currency.
  - Used to identify numeric values that have decimal or fractions. Use this data type especially when dealing with monetary values such as fees balance, amount sold, etc.
  - AutoNumber
  - This is a numeric value used if you wish Ms Access to automatically increase the values in s field. For example, when entering a list of fourty students and you have a field labelled StudNumber; the numbers will increase by one every time you enter a new record.
  - Yes/No
  - This is a logical field where an entry yes or no, true or false. For example, a field may require you to answer whether you are male or female.
  - OLE Object
  - OLE stands for Object Linking and Embedding. This type of field is mostly used with graphical user interface application for inserting geographical objects such as pictures, drawing, charts etc.
  Field properties.
  - As you create more and more complex tables, you will find a need to use field properties to specify finer details related to fields and table entries expected.
  - Field properties depend on the type of field selected. For example, when you click on a text, then the general tab, you will see properties associated to text data type as shown.
  - Field size
  - Field size property allows the user to set the number of characters in a field instead of the default 50. For numeric field we use properties such as integer, long integer, Byte, Single and Double.
  Integer and longer integer: Accepts numbers with no decimals
  
  Byte: Only accepts numbers between 0 and 255
  
  Single and double: accepts numbers with decimals. Single accommodates up to 38 decimal places while double, accommodates up to 308 decimal places.
  - Format
  - Determines how information appears on the screen when printed. For example, you can format a number to specific, currency, percentage or general format
  - Decimal places
  - For number and currency fields, you can specify the number of decimal places
  - Input mask
  - Input mask automatically formats the field entry into a specified format. For example, if you enter a number such as 02000100409874 and the input mask is set as 000-(00000)-000000, it is automatically displayed as 020-(00100)-409874. this property is mostly used to format phone and address entries
  - Caption
  - This is a more descriptive name for a field to be used in a table or a form display. For example, the caption for StuName could be student Name
  - Default value
  - This value appears automatically in the datasheet or form if nothing is entered by the user to change it. For example, =Date ( ) automatically displays the current date in a date field
  - Validation Rule
  - Logical expression that restricts the value to be entered in a field. For example, if you want to restrict marks entered in a filed to values between zero and a hundred, type >=0 And<=100
  - Validation text
  - This is a message that appears once the validation rule is violated. For example, you may create a validation text for the above validation rule to display “enter a number between 0 and 100” whenever the user enters a value outside this range
  - Required
  - Determines if an entry must be made in the field before you proceed to the next field or record. For example, if a primary key is required, you must enter it before you proceed
  - Allow Zero Length
  - This allows the user to proceed without making any entry in the field set as Zero length
  - Indexed
  - An index facilitates the organization of records for easy search. A primary key is an example of an index set to No duplicates
  Primary keys and indexes
  - An index is a key (s) used to speed up searching and sorting records in a table.
  - A Primary key is an index that uniquely identifies each record stored in the table.
  - A primary key prevents the user from making null or double entries into a table. Access uses the primary key to order records, and controls redundancy.
  - Once a filed is set as a primary key, the data sheet is automatically indexed or sorted using the primary key.
  To set a primary key:
  1. Open the table in design view
  2. Select the field you want to set as primary key by clicking in the row header to the left of the field name
  3. Click set primary key button on the tool bar.
  To set another field as an index other than the primary key:
  1. Open the table in design view
  2. Click the indexes button next to the primary key. The dialog box is displayed on the screen.
  3. In the index name column, type the name of the index
  4. In the field name column, select the corresponding fieldname