\
Computer Study/ICT Study Notes

DATABASE NOTES

DATABASES

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
  • Dictionaries
  • 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)

 

  • Oracle
  • Lotus Approach
  • Corel paradox
  • MYSQL
  • Informix
  • Microsoft Access etc

We shall deal much with the basic one which is Microsoft Access

 

TERMS USED

Common terms used in database management systems

  1. Data entry: The process of getting information into a database, usually done by people
  2. Objects: These are the components that makeup a database. Examples of database objects include, tables, forms, queries, reports, modules, macros
  3. An entity: is a person, place, thing, activity, or event for which data is collected
  4. A field: is a single piece of information from a record. In a database table every column represents a field
  5. A record: Is a row on a datasheet and is a set of values defined by fields
  6. 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.
  7. Field Length: Is the maximum number of characters that can be stored for data in a

Particular field.

  1. Data type (Field Type): is an attribute which determines the kind of data which users can store in a field
  2. Data redundancy: Is the repeating of data in more than one file.
  3. Query: is a database object used to request for specific type of data from a database table or combination of tables.
  4. Form: is a database object that allows you to enter or view data stored in your tables.
  5. Report: is a formatted screen display or printout of the contents of one or more tables in a database.
  6. Design view: This provides the tools for creating fields in a table and other objects.
  7. Datasheet view: Allows one to update, edit and delete information from a table.
  8. 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.
  9. 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
  1. 15. Foreign key: Is a primary key of one table that also appears in another table
  2. 16. Data integrity: Is the degree to which the data in any file is accurate and up-to date.
  3. 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.
  4. 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
Wildcard Description Example
* 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.
  1. 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

 

Database objects

These are the basic components that make up a database and they include:

  • Tables
  • Queries
  • Forms
  • Reports
  • macros
  • 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.

Modules:

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.

Database tables

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

Data type Stores Size
Text Alphanumeric characters

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.

8 bytes.
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.
Yes/No Boolean values.

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.

Note

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.
Hyperlink Hyperlinks.

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.

Note

  • 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.

Field properties

Use this field
property
To
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.

Character Use
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

/* ]]> */