DATABASE What is a database Database is an

DATABASE

What is a database ? • Database is an organized collection of structured data. • Database can be used to store information of students in school, about vehicle registrations, stock in supermarket, books in library. . etc.

Examples of database 1. Microsoft Access 2010 2. Open Office programs like Calc and Writer.

types of Database

Continued Ø Flat file database will have one table in database file. Ø Relational database will have two or more tables that are linked in a database file.

Creating a database To create a database in access: 1. Select new from office button menu. 2. Click on blank database 3. Select a location to store the database 4. Give filename 5. Click on create

Objects Database objects available are listed at the lefthand side of the database window.

Continued Tables, forms, queries, report and other objects in a database can make it easier to perform a wide variety of tasks- such as entering data into a form, adding or removing tables, finding and replacing data, and running queries.

Continued Ø Table: a collection of related data about a subject (person, place, thing). It is divided into rows and columns. Ø Query: are used to search and extract information in a database. After you run a query, a group of records are displayed in a table that satisfies or answers the query. Ø Report: is an attractive display on the monitor screen or printout of information in a table.

Designing a database a) Determine the purpose of the database b) Analyze each purpose c) Determine the data you need to meet the purpose. d) Design your tables e) State the field names and its data type f) Determine how the table will be related

Tables Table an organized collection of related records about a subject (person, place, thing). It is divided into rows and columns.

FIELDS

Primary key • A Primary Key is often referred to as Key field. • Primary Key is a field used to uniquely identify each record in a database. Eg. Itemcode, Customerno, Registrationno. . etc.

Advantages of primary key • It can speed up retrieval of data and running of queries. • It enables you to link tables in a database

Steps involved in designing a database • Determine the purpose of the database • Analyze each purpose • Determine the data you need to meet the purpose. • Design your tables • State the field names and its data type • Determine how the table will be related

Methods used in MS Access to create a Table • Design View: This method enables you to create table by naming the fields, selecting their data types. • Datasheet view: You enter the data into datasheet grid which consists of rows and columns labeled Field 1, Field 2, Field 3 and so on. The database software determines the data type on the data you enter.

Data - types Text / Alphanumeric: can store any alphanumeric character (alphabetic or numeric) and special characters such as punctuation marks. eg. name, address , telephone numbers

Numeric: can store numbers. Two types of numeric data are: Integer : will store only whole numbers. e. g. 14, 871, -12 Real : will store decimal numbers. e. g. 34. 5, 9. 25

Currency: can store currency values which allow currency symbols to be added to the display. E. g. $20. 00

Date: Stores date in different formats. (dd/mm/yy, dd. mm. yy etc) e. g. 23/10/12, 23. 10. 12, 23/10/2012, 23 rd October 2012

Boolean/Logical : can store one of two values e. g. Yes/No (Y/N), True/False (T/F) , Male/Female (M/F)

advantages and disadvantages of Paper-Based Databases

Advantages : 1. Cheap to set up a paper – based database and do not need power supply to use them. 2. Don’t need to buy an expensive computer to use them 3. Most can easily be carried around with you (as ledgers) 4. You don’t need training to learn how to use them.

Disadvantages of paper-based databases: 1. They can be lost or misplaced and very hard to make copies. 2. Hard to correct mistakes made to data entries. 3. Order of data can get jumbled up. This is difficult to re-sort. 4. Difficult to search and find specific data.

advantages and disadvantages of Computer-Based Databases

Advantages : 1. Very easy to make back-up copies 2. Easy to update data and correct mistakes 3. Easy to sort data into order (alphabetical for example) 4. Very easy and quick to search for specific data 5. They can be secured with passwords.

Disadvantages : 1. They can be expensive to set up (especially if you hire an expert) 2. The computers need power supply in order to run the database software. 3. They can be difficult to use so you would need lots of training.