Understanding Database Management Systems

Figure 12.1: School Information System

Think of an address book you might buy in a bookshop. In it, you will write all your important contacts; friends, family, relatives, companies, and other people in your circles. The address book contains all names, addresses, location, phone numbers of whoever you need to contact at any time.

Activity 12.1: Understanding Database Management Systems.

1. In groups, search the internet or carry out a library research about what Database is, database management software and examples?

2. Write a brief definition, examples and description and share these with other groups through a class Discussion.

3. Copy and complete the table below in your books

4. After agreeing on the meaning and examples, make corrections in your work if necessary.

5. Share your findings

FEATURES OF A DATABASE MANAGEMENT SYSTEM

Activity 12.2: Features / characteristics of a database management system

In six groups of Green, purple, orange, red, blue and Tan.

Applications of Database Management Software

Database management systems are important in businesses and organizations because they provide a highly efficient method for handling multiple types of data. Some of the data that are easily managed with this type of system include: employee records, student information, payroll, accounting, project management and inventory. These systems are built to be extremely versatile.

Figure 3: Database management systems in an organization

Activity 12.3: Applications of Database Management Software

1. In groups, search the internet or carry out a library research about applications of database management systems?

2. Write an application area, a brief description and share these with other groups through a class Discussion.

3. After agreeing on applications of database management systems, make corrections in your work if necessary.

4. Share your findings

5. Copy and complete the table below

Identifying records and fields in the database

In Access, rows and columns are referred to as records and fields. A field is more than just a column; it’s a way of organizing information by the type of data it is. Every piece of information within a field is of the same type. For example, every entry in a field called First Name would be a name, and every entry in field called Street Address would be an address.

Likewise, a record is more than just a row; it’s a unit of information. Every cell in a given row is part of that row’s record.

Fields and records are two basic components of a database, which is an organized collection of information, or data. The term “fields” refers to columns, or vertical categories of data; the term “records” refers to rows, or horizontal groupings of unique field data.

Starting Microsoft Access

Microsoft Access is a Database Management System offered by Microsoft. Microsoft Access offers the functionality of a database and the programming capabilities to create easy to navigate screens (forms). It helps you analyze large amounts of information, and manage data efficiently.

In groups

1. To start Access, click on the Start button,

2. Then the Programs menu,

3. Then move to the Microsoft Office menu

4. Finally click on the Microsoft Access menu item.

5. Choose the option to create a new, blank database

6. On bottom right, fill in File Name as Classlist.

7. Choose a location eg documents, desktop or in your specific folder

NB: By default Access stores data in documents

8. Click on the Create button to create the database

9. This screen will appear

10. Close the Table, by right clicking on Table1 above or use the icon to the right.

The Access objects

Tables, queries, forms, and reports are the framework for any database you create in Access. Understanding how each of these objects works will help you create a database that will be useful and help you retrieve the information you need.

Activity 12.4: Working With Access tables

While there are four types of database objects in Access, tables are arguably the most important. Even when you’re using forms, queries, and reports, you’re still working with tables because that’s where your data is stored. Tables are at the heart of any database, so it’s important to understand how to use them.

In groups

1. Create a database called patients.

2. Using design view, create the table below and add its information.

3. Procedure

– Use the same steps in opening access as seen above

– From create Tab choose table design

Resizing fields and rows

If your fields and rows are too small or large for the data contained with them, you can always resize them so all of the text is displayed.

To resize a field:

In groups

1. Place your cursor over the right gridline in the field title. Your mouse will become a double arrow.

3. Click and drag the gridline to the right to increase the field width or to the left to decrease the field width.

4. Release the mouse. The field width will be changed.

Alternatively one can double click after getting a double arrow mouse pointer

To resize a row:

1. Place your cursor over the bottom gridline in the gray area to the left of the row. Your mouse will become a double arrow

2. Click and drag the gridline downward to increase the row height or upward to decrease the row height.

3. Release the mouse. The row height will be changed.

Data types in Microsoft Access

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

Activity 12.5: Data types in Microsoft Access

1. In groups, search the internet or carry out a library research about different Data types used in Access?

2. Write a data type, a brief description and share these with other groups through a class Discussion.

3. After agreeing on different data types used in access, make corrections in your work if necessary.

4. Share your findings

5. Copy and complete the table below

Field Properties in Microsoft Access

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

Activity 12.6: Field Properties in Microsoft Access

1. In groups, search the internet or carry out a library research about different field properties used in Access?

2. Write a field property, a brief description and share these with other groups through a class Discussion.

3. After agreeing on different field properties used in access, make corrections in your work if necessary.

4. Share your findings

5. Copy and complete the table below

In groups

1. Open your database called hospital

2. Open your table in design view

3. Change the following field properties

– Field size for PID to 3

– Date of birth to medium date

– Change Fnames to upper case

– Add a surfix UGX to Treatment Fee

4. Procedure

– Field size for PID to 3

o While in design view, click in PID field Name

o Move down to Field Properties and click under Field size

o Change it from 255 to 3

Validation rules, validation text and input mask

Validation rule is a field property used to specify and define conditions that limit values that can be entered in a particular field. For example, suppose you have a Date field, and you enter >=#01/01/2010# in the Validation Rule property of that field.

Your rule now requires users to enter dates on or after January 1, 2010. If you enter a date earlier than 2010 and then try to place the focus on another field, Access prevents you from leaving the current field until you fix the problem.

Validation text is a message that is displayed when data entered in that field does not conform to the validation rule or it is violated.

Input masks allow you to specify exactly how data should be entered into the database. It’s an expression that specifies certain rules about how the data should be formatted as it is entered into the system.   You can use an input mask to validate data by forcing users to enter values in a specific way. For example, an input mask can force users to enter dates in a European format, such as 2007.04.14.

You can use these methods of validating data alone or in combination with each other. Data types are not optional, and provide the most basic type of data validation.

Activity 12.8: Adding Validation Rules and Validation text

In Groups;

1. Open Hospital Database

2. Open your table in design view

3. Enter a rule that will limit Treatment Fee not to exceed more than 150,000

6. Save changes and you will get this message

1. Click yes and open your table in Datasheet view.

2. Now you can try entering 180,000 instead of 10,000 for the first record of Omondi

3. Click in the next record or cell, you will get such a message as a validation text.

Activity 12.9: Adding an input Mask

In groups

1. Open Your table in design view

2. Click on Date Of Birth Field and the under field properties click on Input Mask

3. Note that the Date of Birth field is selected and that the Input Mask property on the General tab is empty.

4. Click in the Input Mask property and a Build button appears at the right.

5. Click on the Build button and the Input Mask Wizard appears.

6. Then click in the Try It: box and you will see the Input Mask.  It indicates where you should type and what the pattern is.

7. You can see that the input area is divided into three parts by two “/” characters.  These correspond to the separators in the Medium Date format, so you would enter the Day number before the first “/”, the Month text between the “/” characters and the year after the second “/”.

8. If you try to type anything other than a text in the second “/” in this field it will not be accepted.

2. If you try to enter an invalid date, such as a month number of 13, you see an error message like this one.

10. When you’re happy that you have the correct Input Mask, click Next and you will see the second screen in the wizard.

11. Here you have the option of further customizing the Input Mask.  For example, you could change the placeholder character (the one used to indicate where the numeric digits go) from an underscore to something else.

12. If you do decide to make a change, there is another Try It: box at the bottom for you to test your new Input Mask.

13. When you’ve made any changes you want to, click Next.

14. All you have to do now is click on Finish.

The Input Mask appears as a property of the field.

Using Forms for Data Entry

Forms are used for entering, modifying, and viewing records. You probably have had to fill out forms on many occasions, like when visiting a doctor’s office, applying for a job, or registering for school. The reason forms are used so often is that they’re an easy way to guide people into entering data correctly. When you enter information into a form in Access, the data goes exactly where the database designer wants it to go: into one or more related tables.

Activity 12.10: Using forms to enter Data

In groups;

1. Create a database called Hospital 2.

2. Using design view design a table called Patientswith the following Fields

3. Assign appropriate data types and a primary key.

4. Save and close your table.

NB: Do not Enter the Data in the table directly

5. Go to create tab, choose Form Wizard

9. After Writing the Forms Name, Click Finish

10. From this layout start entering data from the given table.

11. When done with entering Data save changes and close your Form.

Creating a Form in Design View

In groups

1. After you have created a table, go to create tab
2. Click Form Design

3. Under the design Tab, Click on add Existing Fields

4. On the Right Window that appears click on Show all tables

5. Double Click on your Table “Patients”

6. Double Click Each of those Fields ie PID, Fname, Date Of Birth, Diagnosis and Treatment Fee as they appear on the left side on the form details.

7. Save your form as Patients Form by pressing ctrl + s or click file then save.

8. Click down arrow on view and choose Form View

9. Finally you have a form created in Design View.

10. You can add more records with in your Form by pressing the Tab Key or

NB: Use the Same steps while creating a report in Design View

Queries

Queries are a way of searching for and compiling data from one or more tables. Running a query is like asking a detailed question of your database. When you build a query in Access, you are defining specific search conditions to find exactly the data you want.

Queries are far more powerful than the simple searches you might carry out within a table. While a search would be able to help you find the name of one customer at your business, you could run a query to find the name and phone number of every customer who’s made a purchase within the past week. A well-designed query can give information you might not be able to find out just by looking through the data in your tables.

Creating Queries

In groups (Design a query to extract those that have names beginning with letter N)

1. Select the Create tab on the Ribbon, and locate the Queries group.
2. Select the Query Design command.

3. Access will switch to Query Design view. In the Show Table dialog box that appears, select the table you want to run a query on. Click Add, then click Close. We are running a query about Patients, so we’ll add the Patients table.

4. The selected table will appear as a small window in the Object Relationship pane. In the table window, double-click the field names you want to include in your query. They will be added to the design grid in the bottom part of the screen.

5. In our example, we want to look at those patients who have Fnames beginning with letter “N”. Save it as N-Names (as the query).

6. Set the search criteria by clicking the cell in the Criteria: row of each field you want to filter. Typing criteria into more than one field in the Criteria: row will set your query to include only results that meet all of the criteria. If you want to set multiple criteria but don’t need the records shown in your results to meet them all, type the first criteria in the Criteria: row and additional criteria in the Or: row and the rows beneath it. E.g. Like “N*” under Fname column criteria.

7. After you have set your criteria, run the query by clicking the Run command on the Query Tools Design tab.

8. The query results will be displayed in the query’s Datasheet view, which looks like a table. If you want, save your query by clicking the Save command in the Quick Access toolbar. When prompted to name it, type the desired name, then click OK.

Activity 12.11: Creating Queries

In groups

Create the following Queries to return

1. Names that end with letter a. Save it as A-Names
2. Those patients that have names ending with letter A and were diagnosed with Tuberculosis. Save it as A-Tubsis.
3. Those patients that paid treatment Fee greater than 90,000. Save it as Amount.

Creating Query criteria’s using Expression Builder

Sometimes, writing an expression can be daunting. But the Expression Builder makes it a lot easier. Expressions have many components or “moving parts”: functions, operators, constants, identifiers, and values. Use the Expression Builder to quickly look up these components and insert them accurately.

There are two ways to use the Expression Builder: use the Expression Builder box, which may be all you need, or use the expanded Expression Builder, when your expression is more elaborate.

In groups

Create a query that will return patients born between 1988 and 1991, save it as Yearly.

1. Create a query in design view
2. Add all fields to the design grid
3. Then click in the criteria under Date Of Birth
4. Click the Builder option for the Expression Builder to appear
5. Or Right-click the box where you want to insert your expression, and then choose Build.

6. Type any of these Expressions in your Expression Builder Box

7. >12/31/1988 And <1/1/1991 or >=1/1/1989 And <=31/12/1990

8. Click Ok and run your query.

9. Then Save it.

Activity 12.11: Calculating Age Queries

Example: Add a new field Called Age after Treatment Fee to calculate patients’ Actual Age. Save your Query as Actual Age.

In groups,

1. Create a query in design view
2. Add all fields on design grid
3. Click in a new field after Treatment Fee

4. Click on Builder or right click and choose build

5. In the expression builder Box

• Type DateDiff(“yyyy”,
• On the left side on Expression builder, under Expression elements double click your database Name (hospital), then double click tables, click your Table Name (Patients).
• This should display Fields in the middle column of Expression Categories and then choose Date of Birth Field by Double Clicking on it.

• Your function should be looking like this

DateDiff(“yyyy”, [Patients]![Date of Birth] Add a comma then

• Under Expression Elements scroll down and click on common expressions
• This should give you Expression categories, double click Current Date

Close brackets and your function should look this one below

DateDiff(“yyyy”, [Patients]![Date of Birth], Date())

• Click Ok

• Replace the word Epr1 with Age

NB: Don’t remove the colons:

• Run your query
•  
• 6. Now you should be having Age Calculated in a new field.

7. Save your query as Actual Age