A database is a collection of data organized in manner that allows access, retrieval, use and maintenance of that data.
Examples of databases
- Telephone books (directories)
- Customer address books
- Employee information forms
- Television guides e.t.c
Characteristics of databases
- The data is arranged in columns(fields) and rows()
- Each column has similar data items
- Each row contains information belonging to a single individual
Types of databases
(i). Flat file database
- It is a database made up of only one table
- It is easy to set up and use
(ii). A relational database
It is a database which can take information from two or more database tables and combine them into a new table or report.
NB: A relationship determines the way in which the details in one table are related to the details in another table. It can be a one to one relationship, one to many or many to many relationship.
A database can also be categorized as a paper/manual database or a computerized (electronic) database
An example of a paper database is a personal address book on the other hand, an electronic inventory tracking system is one example of computerized database
Advantages of using electronic database system
- It is easy to enter and retrieve data in a short period of time.
- Data is frequently updated after each single entry.
- An electronic database can store data for a very long period of time in an archive.
- An electronic database is flexible since it can be redesigned, to hold thousands of data.
- An electronic database can be used by many people at the same time.
- Data can be retrieved in different formats e.g. Query, forms, reports, etc.
- An electronic database stores data that is consistent and reliable since at each stage, it is checked for consistency and reliability.
Disadvantages of database system
- They are also difficult and time consuming to develop
- They are expensive to set up as they may require sophisticated programs and hardware.
- Data in a database may be more susceptible to sabotage, theft or destruction.
- It requires much time for training to be effective in using an electronic database
A DATABASE MANAGEMENT SYSTEM (DBMS)
A database management system (DBMS) is a software tool that lets users add, view, and work with the data in a database
Examples of (DBMS)
- Lotus Approach
- Corel paradox
- Microsoft Access etc
We shall deal much with the basic one which is Microsoft Access
Common terms used in database management systems
- Data entry: The process of getting information into a database, usually done by people
- Objects: These are the components that makeup a database. Examples of database objects include, tables, forms, queries, reports, modules, macros
- An entity: is a person, place, thing, activity, or event for which data is collected
- A field: is a single piece of information from a record. In a database table every column represents a field
- A record: Is a row on a datasheet and is a set of values defined by fields
- Field name (Field Labels): Is a title of a particular column in a database table e.g. titles like ID No, Name, Sex, District, Allowance.
- Field Length: Is the maximum number of characters that can be stored for data in a
- Data type (Field Type): is an attribute which determines the kind of data which users can store in a field
- Data redundancy: Is the repeating of data in more than one file.
- Query: is a database object used to request for specific type of data from a database table or combination of tables.
- Form: is a database object that allows you to enter or view data stored in your tables.
- Report: is a formatted screen display or printout of the contents of one or more tables in a database.
- Design view: This provides the tools for creating fields in a table and other objects.
- Datasheet view: Allows one to update, edit and delete information from a table.
- Attribute: a part of the description of an entity. The entity itself is described by one or more attributes e.g. the attributes for student can be name, Address, Telephone, etc.
- Primary key (key field): is a field in a data file that uniquely identifies each record. It is also used to connect two or more tables in a database
Characteristics of a primary key
- It uniquely identifies each record ie cannot have repeated values
- It is never empty or null
- 15. Foreign key: Is a primary key of one table that also appears in another table
- 16. Data integrity: Is the degree to which the data in any file is accurate and up-to date.
- 17. A string is a data type used to represent text rather than numbers. It is comprised of a set of characters that can also contain spaces and numbers.
- Wild cards: Is a special character that represents one or more other characters. Wild card characters may be used to represent a letter or letters in a word. Wild cards can be used in access queries to look for specific information
|*||Matches any number of characters. You can use the asterisk (*) anywhere in a character string.||wh* finds what, white, and why, but not awhile or watch.|
|?||Matches any single alphabetic character.||B?ll finds ball, bell, and bill.|
|[ ]||Matches any single character within the brackets.||B[ae]ll finds ball and bell, but not bill.|
|!||Matches any character not in the brackets.||b[!ae]ll finds bill and bull, but not ball or bell.|
|–||Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A).||b[a-c]d finds bad, bbd, and bcd.|
|#||Matches any single numeric character.||1#3 finds 103, 113, and 123.|
- Data normalization
The process of applying the normalization rules to your database design is called normalizing the database, or just normalization.
When do we use normalization rules?
When you want ensure that you have divided your information items into the appropriate tables i.e. when you want to see if your tables are structured correctly.
You apply the rules in succession, at each step ensuring that your design arrives at one of what is known as the “normal forms.” Five normal forms are widely accepted ie
- the first normal form
- second normal form
- third normal form
- fourth normal form
- fifth normal forms
These are the basic components that make up a database and they include:
- Modules etc
Macros (Mini programs):
These are tools used to automate the way one uses his database. Macros can be used to instruct the computer to print specific reports at a given time.
Macros in Access can be thought of as a simplified programming language which you can use to add functionality to your database. For example, you can attach a macro to a command button on a form so that the macro runs whenever the button is clicked. Macros contain actions that perform tasks, such as opening a report, running a query, or closing the database. Most database operations that you do manually can be automated by using macros, so they can be great time-saving devices.
A module is a collection of declarations, statements, and procedures that are stored together as a unit.
Modules, like macros, are objects you can use to add functionality to your database. Whereas you create macros in Access by choosing from a list of macro actions, you write modules in the Visual Basic for Applications (VBA).
Visual Basic for Applications (VBA): is a macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.
Characteristics of a good database table
- It should have a primary key
- It should have the required fields
- It should have required data types and formats
Data types in Microsoft Access
Use for text, or text and numbers that are not used in calculations (for example, a product ID).
|Up to 255 characters.|
|Memo||Alphanumeric characters (longer than 255 characters in length) or text with rich text formatting.||Up to 1 gigabyte of characters, or 2 gigabytes of storage (2 bytes per character), of which you can display 65,535 characters in a control.|
|Number||Numeric values (integers or fractional values).
Used for storing numbers to be used in calculations, except for monetary values (use the Currency for data type for monetary values).
|1, 2, 4, or 8 bytes, or 16 bytes when used for replication ID.|
|Date/Time||Dates and times.
Used for storing date/time values. Note that each value stored includes both a date component and a time component.
|Currency||Used for storing monetary values (currency).||8 bytes.|
|AutoNumber||A unique numeric value that Office Access automatically inserts when a record is added.
Use for generating unique values that can be used as a primary key. Note that AutoNumber fields can be incremented sequentially.
|4 bytes or 16 bytes when used for replication ID.|
Use for True/False fields that can hold one of two possible values: Yes/No or True/False, for example.
|1 bit (8 bits = 1 byte).|
|OLE(Object Linking and Embedding) Object||OLE objects or other binary data.
Use for storing OLE objects from other Microsoft Windows applications.
|Up to 1 gigabyte.|
|Attachment||Pictures, Images, Binary files, Office files.
This is the preferred data type for storing digital images and any type of binary file.
Field name should be written as a Caption
|For compressed attachments, 2 gigabytes. For uncompressed attachments, approximately 700k, depending on the degree to which the attachment can be compressed.|
Use for storing hyperlinks to provide single-click access to Web pages through a URL (Uniform Resource Locator) or files through a name in UNC (universal naming convention) format. You can also link to Access objects stored in a database.
|Up to 1 gigabyte of characters, or 2 gigabytes of storage (2 bytes per character), of which you can display 65,535 characters in a control.|
|Lookup Wizard||Not actually a data type; instead, this invokes the Lookup Wizard.
Use to start the Lookup Wizard so you can create a field that uses a combo box to look up a value in another table, query or list of values.
|Table or query based: The size of the bound column.
Value based: The size of the Text field used to store the value.
- For phone numbers, part numbers, and other numbers you don’t intend to use for mathematical calculations, you should select the Text data type, not the Number data type.
- For the Text and Number data types, you can specify the field size or data type more specifically by setting a value in the FieldSize property box.
|Use this field
|FieldSize||Set the maximum size for data stored as a Text, Number, or AutoNumber data type.|
|Format||Customize the way the field appears when displayed or printed.|
|DecimalPlaces||Specify the number of decimal places to use when displaying numbers.|
|NewValues||Set whether an AutoNumber field is incremented or assigned a random value.|
|InputMask||Display editing characters to guide data entry.|
|Caption||Set the text displayed by default in labels for forms, reports, and queries.|
|DefaultValue||Automatically assign a default value to a field when new records are added.|
|ValidationRule||Supply an expression that must be true whenever you add or change the value in this field.|
|ValidationText||Enter text that appears when a value violates the ValidationRule expression.|
|Required||Require that data be entered in a field.|
|AllowZeroLength||Allow entry (by setting to Yes) of a zero-length string (“”) in a Text or Memo field.|
|Indexed||Speed up access to data in this field by creating and using an index.|
|UnicodeCompression||Compress text stored in this field when a large amount of text is stored (> 4,096 characters)|
|IMEMode||Control conversion of characters in an Asian version of Windows.|
|IMESentenceMode||Control conversion of characters in an Asian version of Windows.|
|SmartTags||Attach a smart tag to this field.|
|AppendOnly||Allow versioning (by setting to Yes) of a Memo field.|
|TextFormat||Choose Rich Text to store text as HTML and allow rich formatting. Choose Plain Text to store only text.|
|TextAlign||Specify the default alignment of text within a control.|
|Precision||Specify the total number of digits allowed, including those both to the right and the left of the decimal point.|
|Scale||Specify the maximum number of digits that can be stored to the right of the decimal separator.|
- If you want more space to enter or edit a property setting in the property box, press SHIFT+F2 to display the Zoom If you are entering an input mask or validation expression and would like help in building it, click next to the property box to display the appropriate builder
Understanding input masks
An input mask is a set of literal characters and mask characters that control what you can and cannot enter in a field. For example, an input mask can require users to enter dates or telephone numbers that follow the conventions for a specific country/region
When and where to use an input mask
- You use an input mask whenever you want users to enter data in a specific way. For example, if you want users to enter phone numbers in the British format or German format, use an input mask.
- By default, you can apply input masks to table fields that are set to
- the Text,
- Number (except ReplicationID),
- Currency, and
- Date/Time data types.
- You can also apply input masks to form controls, such as text boxes, that you bind to table fields that are set to those data types.
How to use input masks
You can add input masks to table fields by running the Input Mask Wizard, or by manually entering masks in a field property (the Input Mask property).
Input mask character reference
The following table lists and describes the placeholder and literal characters that you can use in an input mask.
|0||Digit. You must enter a single digit in this position.|
|9||Digit. Single digits in this position are optional.|
|#||Enter a digit, a space, or a plus or minus sign in this position. If you skip this position, Access enters a blank space.|
|L||Letter. You must enter a single letter in this position.|
|?||Letter. Single letters in this position are optional.|
|A||Letter or digit. You must enter a single letter or digit in this position.|
|a||Letter or digit. Single letters or digits in this position are optional.|
|&||Any character or space. You must enter either a single character or a space in this position.|
|C||Any character or space. Characters or spaces in this position are optional.|
|. , : ; – /||Decimal and thousands placeholders, date and time separators. The character you select depends on your Microsoft Windows regional settings.|
|>||All characters that follow appear in uppercase.|
|<||All characters that follow appear in lowercase.|
|!||Causes the input mask to fill from left to right instead of from right to left.|
|\||Forces Access to display the character that immediately follows. This is the same as enclosing a character in double quotation marks.|
|“Literal text”||Encloses any text that you want users to see in double quotation marks.|
|Password||In Design view for tables or forms, setting the Input Mask property to Password creates a password entry box. When users type passwords in the box, Access stores the characters but displays asterisks (*).|
Examples of input masks