INTRODUCTION TO DATABASES

Database is a collection of logically related data with descriptions designed to meet the information needs of an organisation. Data_bank is an enormous/large collection of two or more databases for several users within and outside an organisation.

Database management system (DBMS) is software system that allows multiple users to define, create, store, maintain and control access to the database.

Examples of DBMSs include;

• Microsoft Access,
• Oracle,
• Microsoft SQL Server,
• Dbase,
• Fox Pro,
• Sybase

INTRODUCTION TO MICROSOFT ACCESS

MS-Access is a RDBMS (Relational Database Management System) application developed by Microsoft Inc. that runs on Windows operating System. The extension of MS-Access data file is MDB or .accdb

TYPES OF DATABASES

1. Flat databases. These consist of one table
2. Relational databases. These consist of two or more tables and manipulate data by relating the tables.

Download and install the following softwares that we shall be using while studying this course.

1. Microsoft Office 2010 https://drive.google.com/file/d/18p9ZHoxhRRFy01BfmLemjccMiFhRowbd/view?usp=sharing
2. Support Files https://drive.google.com/drive/folders/1iU5k9hy4E-mEp1hp-dOjOAOoVd03Dz6z?usp=sharing
3. For zipping and unzipping use this software (Winrar) https://drive.google.com/drive/folders/10QCneHc39W-xZJwyK6NvmwbcY62rajNS?usp=sharing

FUNCTIONS OF A DATABASE MANAGEMENT SYSTEM

1. Takes care of storage, retrieval and management of large data sets in a database
2. Used to creates a database structure to accommodate data that may be text, numbers, objects, video, sound
3. It lets you easily add new records, delete out-dated records, update records
4. Allows one to organises records in different ways, i.e. sorted and indexed order
5. Helps to locate specific records, i.e. search, find and replace
6. Eliminates duplicate data say by editing, e.g. deleting and retyping
7. Used to create relationships between tables
8. You can ask questions about your data and get answers using queries
9. Used to create data entry forms
10. Used to create professional good-looking reports
11. Used to change appearance of information, i.e. perform some formatting, etc.

ADVANTAGES AND DISADVANTAGES OF DATABASE MANAGEMENT SYSTEMS

ADVANTAGES

1. Sharing of data. Data is easily shared among different users and applications
2. Data persistence. Data exists beyond the scope of the process that it was created for.
3. Data security. Data is protected from unauthorised access using passwords. It also provides protection of databases through security, control and recovery facilities
4. Data validity, integrity & correctness. Data should be correct with respect to the real entity that they represent. Auditing or error check and correction are easily done
5. Consistency of data. The system always produces consistent values with respect to the relationships
6. Data integrity. Refers to both correctness and consistency of data. Correctness is being free from errors while consistence is having no conflicts among related data items
7. Large data storage. It is capable of storing enormous data amounts for personal and organisational use
8. Non–redundancy. Eliminates or decreases duplication of data in the same container. No two data items in a database should represent the same real-world entity.
9. Data independence. Both the data and the user program can be altered independently of each other.

DISADVANTAGES OF DATABASE MANAGEMENT SYSTEMS

1. Complexity. The systems are complex, costly, and take much time to develop, e.g. they include sophisticated software programs that may require special hardware.
2. Need for substantial conversion effort. Changing from a traditional file oriented system to a computerized database system can involve large-scale reorganization of data and programs. This can create user resistance
3. Organisation security may be compromised since a database is used by many people, departments or personnel who may cause havoc by leaking out vital secrets
4. They are difficult to thoroughly test and audit errors
5. Initial expense. Because of their complexity and efficiency, they include sophisticated database systems which can be expensive to setup
6. Requires special skills to handle. Being complex and enormous, databases require skilled personnel to develop, establish and maintain
7. Vulnerability. Data in the database may be exposed to software and hardware failures, sabotage, theft, destruction, virus attacks, etc.
8. Routine back-up. Requires back-up systems, which are inconveniencing, complex, tedious and expensive

DISADVANTAGES OF PAPER/MANUAL/FILE-BASED/FLAT DATABASES

Before computerized databases management systems and even now data may be kept and managed manually on paper files and filing cabinets. This system has the following drawbacks or deficiencies

1. Data redundancy. Data are often repeated in more than one file.
2. Updating difficulties. Keeping all files up-to-date can be problematic
3. Data dispersion. Scattered data are difficult for programs and people to share
4. Under-utilization of data. Dispersed data cannot usually be used to full advantage
5. Not durable. Data on manual papers does not last for long
6. Exposed to risks. Data can be easily lost due to fire, rot, termites, rats, etc.
7. Data dependence. Programs may be dependent on the data formats and file organisation.

DATABASE OBJECTS

1. Table. Is a collection of data arranged and stored in rows and columns. It is the basic/primary object where all other objects derive data from.
2. Query. Is used to ask questions on table data and find qualifying answers.
3. Form. Is a tool for displaying data from data tables easily and for entering & editing data in the data tables
4. Report. Is a summarised and good-looking display of data from tables and queries. It is for output only.
5. Macros A macro is an object in MS-Access that is used to execute one or more database commands automatically. Macros are useful in tasks such as printing month-end reports, adding new record to a table, printing letters to customers periodically.
6. Modules: A module object in Access is a program written using VBA (Visual Basic for Application) to automate and customize database function.

DEFINITION OF TERMINOLOGIES

1. File. Refers to the entire collection of data in the database.
2. Field. This is the entire column that contains similar data items
3. Field name. This is the name/title of a particular field
4. Field type. This refers to how particular data items are stored in a table
5. Field properties. This refers to specific characteristics of particular fields
6. Record. This refers to particulars within a file, or a set of entire data items in a row
7. Macro. This is an automated procedure of action in a computer
8. Attribute. This refers to a group of fields or columns in a table
9. Primary key. This is a unique record identifier in the table. It is used to ensure that there are no duplicate fields in the table. It is also used to create relationships among tables.
10. A foreign key, is a copy of the primary key in another table
11. A view. Is a virtual table that does not necessarily exist in its own right but may be dynamically derived from one or more base tables
12. Relationships. This refers to how two or more entities/tables share information in the database structure. That is, how data in one table are related to data in another table. Relationships are of three types; one-to-one (1:1), one-to-many (1:M) and many-to-many (M:M)
13. Datasheet view. This is a table view which allows you to update, edit, format and delete information from the table.
14. Design view is a table view which provides tools for creating fields in a table, i.e. specify field names, data types, field properties and descriptions (a view for creating the table)

CHARACTERISTICS OF DATABASE APPLICATIONS

1. Data is organised in rows and columns
2. Each column has a distinct name and represents an attribute of table entities
3. All values in a column must conform to the same data format or data type.
4. Each row represents a single entity occurrence (entity instance)
5. It contains tools known as database objects such as; forms, queries and reports

COMPONENTS OF A DATABASE MANAGEMENT SYSTEM

1. Data dictionary. This is an automated or manual tool used to store and organise the data in the database and defines each data field that will be contained in the database file including characteristics of each item
2. Data definition language (DDL). It helps one to create and maintain the data dictionary and define the structure of files in a database. It defines each term as it appears in a database, e.g. delete, create, browse, zap, index, sort, etc.
3. Data manipulation language. This is used to manipulate data in the database. That is, it helps one to; add, change, select, and delete data in the database and mine it for valuable information

DATA TYPES, FIELD PROPERTIES, VALIDATION CHECKS AND ERRORS

DATA TYPES

Data type specifies and determines the kind/category of values or information entered in the field containers. There are various data types applied in Microsoft Access and these include;

1. Text. Are alphabetic letters or numbers that cannot be calculated. Examples of such fields are; names, addresses, subject names, course names, telephone numbers, etc. it can contain up to 255 characters.
2. Number. Refers to numerical data you can calculate but not relating to money, e.g. age, height, weight, course duration, score, number of items in stock. It can be whole number or fraction.
3. Currency. Are numerical monetary values that can be calculated and may have a currency symbol or not such as £56000.05, 59000.89, $5362, €4563, etc. It is suitable for field like; salary, gross pay, net pay, PAYE, school fees, amount paid, etc.
4. Memo. It is for lengthy descriptive text and numbers usually several sentences or paragraphs. It can contain a maximum of 32,000 characters. It is suitable for fields like; remarks, comments, particulars, descriptions.
5. Date/Time. For months, date and time values that are in the form; dd/mm/yy or dd-mm-yy, i.e. date/month/year for dates and Hr:Min:sec, i.e. Hour:Minutes:Seconds for time values. It is suitable for fields like; date of birth, date of joining, on/off set date/time, date/time of departure/arrival, etc.
6. AutoNumber. A number that automatically increments for each record you enter. It stores sequential numbers entered automatically by Microsoft Access starting with one. They are unique and can make a good primary key. It is suitable for fields like; registration number, ID number, membership number, etc.
7. Yes/No. Here you can enter and store only one value or answer out of the available two options but not both. It is suitable for fields like; true/false, on/off, smoker/non-smoker, Ugandan/Non-Ugandan, in/out, etc.
8. Object linking and embedding (OLE object). For object data and other binary information such as; sounds, symbols, graphics/pictures such as; signatures, thumbprints, company logos, one’s photo, etc.
9. Hyperlink. Stores data in form of hyperlinks, which are the blue-coloured hotspots or connections that can be clicked to open other pages or documents, e.g. e-mail address, website, bookmarks, etc.
10. Lookup wizard. Refers to a list of items in form of a list-box from which you can choose the desired item during data entry, especially if that data exists in another table or form. It is suitable for repetitive data such as marital status; single, married, separated, divorced, widowed, etc.
11. Calculated data type. This new data type lets you create a field that is based on a calculation of other fields in the same table. For example, you might create a Line Total field that contains the product of a Quantity field and a Unit Price field. Then, if you update the Quantity or Unit Price field, the Line Total is updated automatically
12. Attachment. This is the preferred data type for storing digital images and any type of binary file, like; Pictures, Images, Office files

 FIELD PROPERTIES

These are traits or characteristics defining data entered in particular fields. Common properties include

1. Field size. This specifies the maximum length of a field. That is, the maximum number of characters to be stored in the field. e.g. if you specify field size as 5, only 5 or less characters will be allowed in the column.
2. Format. Specifies the way that the field appears by default when displayed or printed.
3. Decimal Places. It is used to specify the number of decimal places to use when displaying numbers
4. Input Mask. Specifies the pattern or format for data to be entered in that field, e.g. (–/–/–) for date.
5. Caption. Used to set the text displayed by default in labels for forms, reports, and queries.
6. Default Value. A value that appears in the field automatically even before you enter there anything.
7. Validation Rule. An expression that must be true whenever you add or change the value in a given field. e.g. >=10 for age, “married” or “single” for marital status, etc.
8. Validation Text. A message displayed when a value violates the expression in the Validation Rule e.g. “please, marital status is either single or married”
9. Required. Specifies whether or not an entry must be entered in that field. That is, if yes, you must type an entry, but if no, you may proceed without entering anything.
10. Allow Zero Length. A provision for a field to be left blank in case of unavailable data to be entered later even if the setting for required is yes. Nulls indicate that data may exist but it is unknown. To enter a null, leave the required property as no and leave the field blank, e.g. a company without a fax number
11. Indexed. It specifies whether or not duplicates in the field should be allowed in order to speed up the data search, sort, filter, etc.
12. Text Align. Specifies the default alignment of text within a control.
13. New Values. Specifies whether an AutoNumber field is incremented or assigned a random value when a new record is added
14. Unicode Compression. Compresses text stored in this field when a small amount of text is stored (< 4,096 characters).
15. IME Mode. Controls conversion of characters in an East Asian version of Windows.
16. IME Sentence Mode. Controls conversion of sentences in an East Asian version of Windows.
17. Smart Tags. Attaches a smart tag(smart tags: Data recognized and labeled as a particular type. For example, a person’s name or the name of a recent Microsoft Outlook e-mail message recipient is a type of data that can be recognized and labeled with a smart tag.) to this field.
18. Append Only. Tracks the history of field values (by setting the property’s value to Yes).
19. Text Format. Choose the property’s Rich Text value to store text as HTML and allow rich formatting. Choose the property’s Plain Text value to store only unformatted text.

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. 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. Common data validation checks include;

• presence/existence or completeness check,
• range check,
• limit check,
• data type check or character check or alphanumeric check,
• format check,
• consistency check,
• control total check, and
• hash total check.

ERRORS

An error is a fault or an issue that arises unexpectedly causing the program not to function properly and to close. Common types of errors include; transcription errors and transposition errors. Transpositions errors include; error of omission, error of addition, random error, overflows error, rounding up error, and truncation errors.

WORKING WITH EXAMPLES PRACTICALLY

PRACTICAL QUESTION ONE

The table below shows medical records of a certain clinic

• Create a database called Medical Details.
• Design a table with appropriate data types in design view called Patients.
• Enter the given data in the table.
• Assign a UGX symbol to the treatment fee field and change date to medium format.
• Create a query to display all the details of patients who come from either Jinja or Busia. Save it as Eastern Patients.
• Create a query to display all the details that were not diagnosed with Malaria. Save it as No Malaria.
• Create a form from the table and save it as Main form
• If the Government pays 40% of treatment fee for all patients, create a query to calculate discounted fee. Put only Fname, DoB, Diagnosis and Treatment Fee on the display. Save it as Discounted fee.
• Create a report to display the information in the discounted fee query. Save it as Discounted Report.
  Create a tabular form from discounted fee query showing all details and save it as Patient’s form.
• Print your work.

PRACTICAL QUESTION TWO

Using any data base management system load file called 20xxxCoMpany. Mdb and use it to answer the following questions. Any changes made should be saved.

• Open up table  called suplaya in design view and adjust the data type appropriately
• Change the default primary key field to the most appropriate field
• Change the date format in the table properties to medium date
• The currency field should be formatted to indicate SHS as units
• Create a tabular form that includes all the fields that can be used to add more information in the table. Save it as entry
• If all suppliers offered a general discount of 0.2% use a form above to determine how much discount the company enjoyed from each supplier
• Change the background color of your form to a brighter color
• Insert a header on the page of the form
• Create a query with only the field of SUPNAME and PRODUCAT to filter those who supplied meals, stationary or text books and save it as power
• Create another query that will filter suppliers who supplied items during 1998 and after 2014. Save it as yearly
• Create on other query with a field of supname and cost to display those suppliers whose items were not valued and names start with letter K. Save it as valueless
• Create a general report with supplier name sorted in ascending order with a header of your name and index number. Save it as
• Print the genrepo on a landscape page orientation.

PRACTICAL QUESTION THREE

The table below represents Kagali Secondary School student’s data.

• Create a database and save it as Kagali database.                     
• Create a table to capture the given information with appropriate data types. Save it as Kagali
• Enter the data in the table below.
• Change district names into uppercase
• Generate a form to return the records of the following fields: STUDENT NUMBER, NAME, AGE and HOME DISTRICT. Save it as Student’s Form.
• In the form,
  (i) Insert any clip art picture from the library to appears as the logo.

(ii) Include the title as KAGALI SECONDARY SCHOOL with font size 20.
(iii) Indicate the date and time it has been created.                              

• Use a query to filter out student(s);
  (i) from Masaka district. Save it as Masaka.

(ii) with age above 18. Save it as Age.

• Generate a report from the table. Save it as Student’s report.
• Add your name and personal number as footer on your report.
• Print your work.

PRACTICAL QUESTION EIGHT

Open a database file called DANDAMAN.mdb that is in your support folder

1. Import the data from a file called MAJANA.xls that is in the support folder into the database
2. In the imported table,

• Rename the table as Mothers
• Change the format of units for doctors’ fees to UGX
• Set the date format to medium date
• Format the Name field to automatically store names in upper case
• For purposes of validation, enter a rule that will limit Doctors fees to Not more than shs 900,000
• Use a function to determine the average Doctors fee

1. Create a relationship between the two tables.
2. Create a form  that  can  be used  to  enter  new  data  in  the mothers  table  and  save it  as Entry form.

• On your form calculate 15% discount on doctors’ fees
• Given that Doctors whose fee is 200000 and above are categorized as “Form” otherwise “substance” use an IIF function to work out this in a new field named Category.
• Add date and time you created your form
• Create a footer of your names and reg no
• Create queries
• In table 1 to select girl children born with complications, save query as Complicated
• In the mothers table  to  determine  mothers whose  date  of  birth occurred in  December  and  save  it  as December                                        
• Make a report for complicated girl children with the following information
• Arrange in descending order of names
• A label of your name as a header
• Print your work

PRACTICAL QUESTION THREE

You have been approached by the Computer Club president in your school to design for them a database. Study the information below and answer the questions that follow.

• Create a database called Computer Club and create a table called Students Table with the appropriate datatypes and enter the data above.
• Create the following queries whose members have;
• Names between A-G. Name this query A-G query.
• Code between1-10 and joined the club between 2004 and 2007. Save this query as M001-10 query.
• Create a form from M001-10 query. Name it M001-10 Form.
• In the form above add date and time and a header as “The Wizards Club 2020” in font size 24 and a footer of your names and reg no.
• Create a report with the names in ascending order, tabular layout. Name it Computer Report.
• Given that complete membership Fee is 20,000, create a query to display their balances in a new field BALANCE. Save it as Balance.
• Create a query to filter out all those members from S6. Name it S6 ICT Query.
• Create another query that will return their years spent in the club in a new field named YEARS. Name it Actual Years Spent.

PRACTICAL QUESTION FOUR

Create a database file called MYDATABASE – REG NO in the folder you created.

1. Create a table in design view and assign appropriate data types. Save it as Employee Table.

• For purposes of validation, enter a rule that will limit Amount fee to not exceed more than 91,000 and change date format to medium date.

1. Create a form called “Employee Entry Form” and Insert the following records
2. Create a query to show all female employees who are programmers and name it “Programmers”
3. If the employees pay a tax of 15% of the amount, create a query to compute for tax in a new field named TAX. Save this query as Revenue Tax.
4. Create another query from the Revenue Tax Query that will return employees net pay in a new field NET PAY. Save it Net Pay. Note net pay is (Amount – Tax).
5. Create a form from the above query. Include date and time with a footer of your names and Reg no. on your form. Save it as Net pay Form. Add at least two command buttons.
6. Create a query to display those with no Amount values. Save it as Valueless.
7. Create query to display a post of Engineer, Finance or Technician. Save it as POST.
8. Create a query returning all the post excluding the technician. Save it as Exclusion.
9. Create another query that will filter members born during 1999 and after 2004. Yearly.
10. Create a parameter query basing on the Post. Save it as Parameter.
11. Create a query to calculate the age of all employees who earn a salary less than 50,000/= Save it as Age with <50,000.
12. Create a report showing all records with Post in ascending order, tabular layout in portrait. Include your name as footer and a heading of your choice in the header. Save it as “Employee Report”
13. Using the above report add a field ACTUAL AGE and determine their actual ages.
14. Given that the total amount to be paid is 91,000/=, calculate the balances for the members in a new field named BALANCE use a query. Save it as Balance.
15. Create a query to filter out those members with a post ending with letters er. Name it End er.

https://youtu.be/mBVNvoJYmEk