DATABASE NOTES

msomimaktaba,

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
WildcardDescriptionExample
*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 typeStoresSize
TextAlphanumeric characters

Use for text, or text and numbers that are not used in calculations (for example, a product ID).

Up to 255 characters.
MemoAlphanumeric 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.
NumberNumeric 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/TimeDates and times.

Used for storing date/time values. Note that each value stored includes both a date component and a time component.

8 bytes.
CurrencyUsed for storing monetary values (currency).8 bytes.
AutoNumberA 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/NoBoolean 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) ObjectOLE objects or other binary data.

Use for storing OLE objects from other Microsoft Windows applications.

Up to 1 gigabyte.
AttachmentPictures, 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.
HyperlinkHyperlinks.

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 WizardNot 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
FieldSizeSet the maximum size for data stored as a Text, Number, or AutoNumber data type.
FormatCustomize the way the field appears when displayed or printed.
DecimalPlacesSpecify the number of decimal places to use when displaying numbers.
NewValuesSet whether an AutoNumber field is incremented or assigned a random value.
InputMaskDisplay editing characters to guide data entry.
CaptionSet the text displayed by default in labels for forms, reports, and queries.
DefaultValueAutomatically assign a default value to a field when new records are added.
ValidationRuleSupply an expression that must be true whenever you add or change the value in this field.
ValidationTextEnter text that appears when a value violates the ValidationRule expression.
RequiredRequire that data be entered in a field.
AllowZeroLengthAllow entry (by setting to Yes) of a zero-length string (“”) in a Text or Memo field.
IndexedSpeed up access to data in this field by creating and using an index.
UnicodeCompressionCompress text stored in this field when a large amount of text is stored (> 4,096 characters)
IMEModeControl conversion of characters in an Asian version of Windows.
IMESentenceModeControl conversion of characters in an Asian version of Windows.
SmartTagsAttach a smart tag to this field.
AppendOnlyAllow versioning (by setting to Yes) of a Memo field.
TextFormatChoose Rich Text to store text as HTML and allow rich formatting. Choose Plain Text to store only text.
TextAlignSpecify the default alignment of text within a control.
PrecisionSpecify the total number of digits allowed, including those both to the right and the left of the decimal point.
ScaleSpecify 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.

CharacterUse
0Digit. You must enter a single digit in this position.
9Digit. 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.
LLetter. You must enter a single letter in this position.
?Letter. Single letters in this position are optional.
ALetter or digit. You must enter a single letter or digit in this position.
aLetter 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.
CAny 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.
PasswordIn 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

The examples in the following table demonstrate some of the ways you can use input masks.

This input mask Provides this type of valueNotes
(000) 000-0000(206) 555-0199In this case, you must must enter an area code because that section of the mask (000, enclosed in parentheses) uses the 0 placeholder.
(999) 000-0000!(206) 555-0199
( ) 555-0199		In this case, the area code section uses the 9 placeholder, so area codes are optional. Also, the exclamation point (!) causes the mask to fill in from left to right.
(000) AAA-AAAA(206) 555-TELEAllows you to substitute the last four digits of a U.S. style phone number with letters. Note the use of the 0 placeholder in the area code section, which makes the area code mandatory.
#999-20
2000		Any positive or negative number, no more than four characters, and with no thousands separator or decimal places.
>L????L?000L0GREENGR339M3
MAY R 452B7		A combination of mandatory (L) and optional (?) letters and mandatory numbers (0). The greater-than sign forces users to enter all letters in uppercase. To use an input mask of this type, you must set the data type for the table field to Text or Memo.
00000-999998115-
98115-3007		A mandatory postal code and an optional plus-four section.
>L<??????????????Maria
Pierre		A first or last name with the first letter automatically capitalized.
ISBN 0-&&&&&&&&&-0ISBN 1-55615-507-7A book number with the literal text, mandatory first and last digits, and any combination of letters and characters between those digits.
>LL00000-0000DB51392-0493A combination of mandatory letters and characters, all uppercase. Use this type of input mask, for example, to help users enter part numbers or other forms of inventory correctly.

 

 

 

 

Query

Query: is a database object used to request for specific type of data from a database table or combination of tables.

This is achieved through the use of query criteria

A query criteria is a rule that identifies the records that you want to include in the query result.

Use this criterionQuery result
“China”Returns records where the Value in the field is set to China.
Not “Mexico”Returns records where the Value in the field is set to any other value in the field other than Mexico.
Like A*Returns records for all fields whose values start with “A”,

 Note    When used in an expression, the asterisk (*) represents any string of characters — it is also called a wildcard character.

Like “*Korea*”Returns records for all fields that contain the string “Korea”.
K*and*I
*D*
K*or b*
<>john
*E or *I
Not Like U*Returns records for all fields whose names start with a character other than “U”.
Not Like “*Korea*”Returns records for all fields that do not contain the string “Korea”.
Like “*ina”Returns records for all fields whose names end in “ina”, such as China and Argentina.
Not Like “*ina”Returns records for all fields that do not end in “ina”, such as China and Argentina.
Is NullReturns records where there is no value in the the field.
Is Not NullReturns records where the value is not missing in the field.
“” (a pair of quotes)Returns records where the field is set to a blank (but not null) value.
Not “”Returns records where the field has a nonblank value.
“” Or Is NullReturns records where there is either no value in the field, or the field is set to a blank value.
Is Not Null And Not “”Returns records where the field has a nonblank, non-null value.
>= “Mexico”Returns records of all fields with values beginning with Mexico and continuing through the end of the alphabet.
Like “[A-D]*”Returns records for fields whose values start with the letters “A” through “D”.
“USA” Or “UK”Returns records for USA and UK.
In(“France”, “China”, “Germany”, “Japan”)Returns records for all fields with values specified in the list.
Right([Value in the], 1) = “y”Returns records for all fields where the last letter is “y”.
Len([Country/Region]) > 10Returns records for countries/regions whose name is more than 10 characters long.
Like “Chi??”Returns records for fields with values, such as China and Chile, whose names are five characters long and the first three characters are “Chi”.

Criteria for Number, Currency, and AutoNumber fields

Use this criterionQuery Result
100Returns records where the value is 100.
Not 1000Returns records where the value is not 1000.
< 100
<= 100		Returns records where the value is less than 100 (<100).

Displays records where the value is less than or equal to 100.

20 or 25Returns records where the value is either 20 or 25.
>10 and <90Returns records where the value is between (but not including) 10 and 90
Between 50 and 100Returns records where the value is between (and including) 50 and 100
<50 or >100Returns records where the value is not between 50 and 100.
In(20, 25, 30)Returns records where the value is either 20, 25, or 30.
Like “*4.99”Returns records where the value ends with “4.99”, such as 4.99, 14.99, 24.99, and so on.
Is NullReturns records where no value is entered in the field.
Is Not NullReturns records where the value is not missing in the field.

Criteria for Date/Time fields

Use this criterionQuery result
#2/2/2006#Returns records with date set to Feb 2, 2006.

Note

Remember to surround date values with the # character so that Access can distinguish between date values and text strings.

Not #2/2/2006#Returns records that took place on a day other than Feb 3, 2006.
5/*/*Displays all records on 5th of any month of any year
5/10/*
*/6/*All records with the month of june
*/*/2012
< #2/2/2006#Returns records that took place before Feb 2, 2006.
> #2/2/2006#Returns records that took place after Feb 2, 2006.
>#2/2/2006# and <#2/4/2006#Returns records took place between Feb 2, 2006 and Feb 4, 2006.

You can also use the Between operator to filter for a range of values. For example, Between #2/2/2006# and #2/4/2006# is the same as >#2/2/2006# and <#2/4/2006# .

<#2/2/2006# or >#2/4/2006#Returns records took place before Feb 2, 2006 or after Feb 4, 2006.
#2/2/2006# or #2/3/2006#Returns records that took place on either Feb 2, 2006 or Feb 3, 2006.
In (#2/1/2006#, #3/1/2006#, #4/1/2006#)Returns records that took place on Feb 1, 2006, March 1, 2006, or April 1, 2006.
DatePart(“m”, [SalesDate]) = 12Returns records that took place in December of any year.
DatePart(“q”, [SalesDate]) = 1Returns records that took place in the first quarter of any year.
Date()Returns records that took place on the current day. If today’s date is 2/2/2006, you see records where the Date field is set to Feb 2, 2006.
Date()-1Returns records that took place the day before the current day. If today’s date is 2/2/2006, you see records for Feb 1, 2006.
Date() + 1Returns records that took place the day after the current day. If today’s date is 2/2/2006, you see records for Feb 3, 2006.
< Date()Returns records that took place before today.
> Date()Returns records that will take place after today.
Is NullReturns records where the date is missing.
Is Not NullReturns records where the date is known.
DateDiff (“yyyy”, [BirthDate], Date()) > 30This criterion applies to a Date/Time field, such as BirthDate. Only records where the number of years between a person’s birthdate and today’s date is greater than 30 are included in the query result. 

 

The design process

The design process consists of the following steps:

  • Determine the purpose of your database

This helps prepare you for the remaining steps.

  • Find and organize the information required

Gather all of the types of information you might want to record in the database, such as product name and order number.

  • Divide the information into tables

Divide your information items into major entities or subjects, such as Products or Orders. Each subject then becomes a table.

  • Turn information items into columns

Decide what information you want to store in each table. Each item becomes a field, and is displayed as a column in the table. For example, an Employees table might include fields such as Last Name and Hire Date.

  • Specify primary keys

Choose each table’s primary key. The primary key is a column that is used to uniquely identify each row. An example might be Product ID or Order ID.

  • Set up the table relationships

Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.

  • Refine your design

Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design, as needed.

  • Apply the normalization rules

Apply the data normalization rules to see if your tables are structured correctly. Make adjustments to the tables, as needed.

 

Factors to consider before designing a database

  • Size and nature of the work that need to be entered into a database.
  • Number of users of the database.
  • How the database will be administered.
  • Future needs of the organization.
  • Initial cost for designing.
  • Application program to use (on the programming side).

 

DATA VALIDATION

Validation – Is the process of comparing the data entered with a set of predefined rules or values to check if the data is acceptable. OR Validation is the name for the checks that detect incorrect data, display an error message and request another input or just reject the data.

Data Validation – is the checking of input data for errors (e.g., of the correct data type) before processing.

Validation checks used in a database

  • Alphabetic check: ensures that only alphabetic characters are entered into the field
  • Numeric checks: ensures that only numeric data is entered into the field
  • Consistency check: test Checks fields to ensure data in these fields corresponds, e.g., If Title = “Mr.”, then Gender = “M”.
  • Check digits: this confirms the accuracy of a primary key value. It is usually appended to or inserted into a primary check
  • Cross-system consistency checks: Compares data in different systems to ensure it is consistent, e.g., the address for the customer with the same id is the same in both systems.
  • Data type checks: Checks the data type of the input and give an error message if the input data does not match with the chosen data type, e.g., In an input box accepting numeric data, if the letter ‘O’ was typed instead of the number zero, an error message would appear.
  • Range check: Checks that the data lie within a specified range of values, e.g., the month of a person’s date of birth should lie between 1 and 12.
  • Spelling and grammar check: Looks for spelling and grammatical errors.

Validation rules

This prevent bad data from being saved in your table. Basically, they look like criteria in a query.

Validation ruleValidation text
<>0Enter a nonzero value.
>=0Value must be zero or greater.

-or-

You must enter a positive number.

0 or >100Value must be either 0 or greater than 100.
BETWEEN 0 AND 1Enter a value with a percent sign. (For use with a field that stores number values as percentages).
<#01/01/2007#Enter a date before 2007.
>=#01/01/2007# AND <#01/01/2008#Date must occur in 2007.
<Date()Birth date cannot be in the future.
M Or FEnter M for male or F for female.

 

END

 

 

ALL NOTES FOR ALL SUBJECTS QUICK LINKS:

Computer Study/ICT Study Notes

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *