Module Two: Database concepts (Notes)
A database allows you to store information related to a specific topic in an organized way. In addition to storing data, you can also sort, extract, and summarize information related to the data. One of the software programs that allow you to do this is Microsoft Office Access 2007, which is a database creation and management program.
Some examples of commonly used databases might be:
- Address book
- Library catalogue
- Telephone directory
- Stock list
A database is not necessarily contained on a computer. A telephone directory is still a database even if it’s in the form of a huge book sitting next to your phone
Why Not Use Excel?
There are many types of data you may need to store and manage: text and numbers, for example. Depending on what you want your data to do for you, you may or may not need to use a database. You might be able to use a spreadsheet program like Microsoft Excel. How do you know which data can be adequately managed with Excel and which data really requires Access to manage it more efficiently? It depends on how much data you have to manage, and what you want your data to do for you. Let's try to answer this by looking at a bookstore scenario.
If you work for a bookstore business, you might have to keep track of your customers and their orders. You could use Microsoft Excel to store and manage this type of data; however, Excel is a spreadsheet software program that is traditionally used to manage numerical information, like totaling up all purchases by one customer. While it can do an adequate job at storing some types of text -based data like the customer's name and contact information that is not really what Excel was designed to do.
A database is a collection of related information.
Data anything that can be captured into a computer
Information these are processed data.
A table is a tool which organize data so that can be accepted by Relational database management system (RDBMS). Tables are the most important component of a database because tables are where all of your information is stored.
Tables in an access database are similar in many ways to Excel tables. Most Access databases will consist of more than one related table
Each table is made up of columns referred to as Fields and rows referred to as records.
A field is a column on a datasheet and defines a data type for a set of values in a table.
A record is a row on a datasheet and is a set of values defined by fields. In a student list table, each record would contain the data for one student as specified by the intersecting fields.
A database management system (DBMS), sometimes just called a database manager, is a program that lets one or more computer users create and access data in a database.
Is a software used to manage and query a database.
The DBMS manages user requests (and requests from other programs) so that users and other programs are free from having to understand where the data is physically located on storage media and, in a multi-user system, who else may also be accessing the data. In handling user requests, the DBMS ensures the integrity of the data (that is, making sure it continues to be accessible and is consistently organized as intended) and security (making sure only those with access privileges can access the data). The most typical DBMS is a relational database management system (RDBMS). A standard user and program interface is the Structured Query Language (SQL). A newer kind of DBMS is the object-oriented database management system (ODBMS).
On PCs, Microsoft Access is a popular example of a single- or small-group user DBMS. Microsoft's SQL Server is an example of a DBMS that serves database requests from multiple (client) users.
There are four structural types of database management systems: hierarchical, network, relational, and object - oriented.
Hierarchical Databases (DBMS), commonly used on mainframe computers, have been around for a long time. It is one of the oldest methods of organizing and storing data, and it is still used by some organizations for making travel reservations. A hierarchical database is organized in pyramid fashion, like the branches of a tree extending downwards. Related fields or records are grouped together so that there are higher-level records and lower - level records, just like the parents in a family tree sit above the subordinated children.
Based on this analogy, the parent record at the top of the pyramid is called the root record. A child record always has only one parent record to which it is linked, just like in a normal family tree. In contrast, a parent record may have more than one child record linked to it. Hierarchical databases work by moving from the top down. A record search is conducted by starting at the top of the pyramid and working down through the tree from parent to child until the appropriate child record is found. Furthermore, each child can also be a parent with children underneath it.
The advantage of hierarchical databases is that they can be accessed and updated rapidly because the tree - like structure and the relationships between records are defined in advance.
Network Databases
Network databases are similar to hierarchical databases by also having a hierarchical structure. There are a few key differences, however. Instead of looking like an upside-down tree, a network database looks more like a cobweb or interconnected network of records. In network databases, children are called members and parents are called owners. The most important difference is that each child or member can have more than one parent (or owner).
Like hierarchical databases, network databases are principally used on mainframe computers. Since more connections can be made between different types of data, network databases are considered more flexible. However, two limitations must be considered when using this kind of database. Similar to hierarchical databases, network databases must be defined in advance. There is also a limit to the number of connections that can be made between records.
Relational Database
In relational databases, Hierarchical and network databases require the user to pass down through a hierarchy in order to access needed data. Relational databases connect data in different files by using common data elements or a key field. Data in relational databases is stored in different tables, each having a key field that uniquely identifies each row.
Relational databases work on the principle that each table has a key field that uniquely identifies each row, and that these key fields can be used to connect one table of data to another. Thus, one table might have a row consisting of a customer account number as the key field along with address and telephone number. The customer account number in this table could be linked to another table of data that also includes customer account number (a key field), but in this case, contains information about product returns, including an item number (another key field). This key field can be linked to another table that contains item numbers and other product information such as production location, color, quality control person, and other data. Therefore, using this database, customer information can be linked to specific product information.
The relational database has become quite popular for two major reasons. First, relational databases can be used with little or no training. Second, database entries can be modified without redefining the entire structure.
Object - Oriented Databases (OODBMS)
Able to handle many new data types, including graphics, photographs, audio, and video, object-oriented databases - Hierarchical and network databases are all designed to handle structured data; that is, data that fits nicely into fields, rows, and columns. They are useful for handling small snippets of information such as names, addresses, zip codes, product numbers, and any kind of statistic or number you can think of.
On the other hand, an object-oriented database can be used to store data from a variety of media sources, such as photographs and text, and produce work, as output, in a multimedia format.
Object - oriented databases use small, reusable chunks of software called objects. The objects themselves are stored in the object - oriented database. Each object consists of two elements:
1) A piece of data (e.g. sound, video, text, or graphics), and
2) The instructions, or software programs called methods, for what to do with the data.
Object - oriented databases have two disadvantages. First, they are more costly to develop. Second, most organizations are reluctant to abandon or convert from those databases that they have already invested money in developing and implementing. However, the benefits to object - oriented databases are compelling.
Some of the benefits of database management system (DBMS)
- Reduced operative expenses
- Quick and accurate decisions based on historical data
- Multi user and change capabilities
- Streamlined and structured workflow capabilities
- Report capabilities
- Client relationship and business management
- Predictive and forecasting capabilities.
- Creating a new Database
You will notice that the first time you open Access the screen is rather empty and unfriendly. This is very different from other programs such as Word or Excel where you are presented with a new, ready to use document each time you start the program. This is because you can’t begin using a database without setting one up first, which takes a fair amount of effort. That is why you don’t get a blank, ready to use database when you begin the program. The first thing we will need to do is create a database to use.
The first step is to create and save a blank database file. In other programs, you usually don’t save a file until you’ve done something with it but in Access, saving the file is the first step. This is because in Access, many things are automatically saved as you work so Access needs to know where to save it all right at the start
Make sure your task pane is showing. If it isn’t, go to the View menu and select Task Pane (or press [Ctrl][F1]).
From the list of options in the task pane, click (you can also click the icon on the toolbar).
For the file location, select (or create) and appropriate folder in your student drive. Type e.g. Student List for the file name and click Create.
Databases are made up of various objects such as tables, queries and forms. The options down the left side will take you to the main sections of the database. The options at the top of the database window allow you open existing database objects , modify database objects or create new database objects .
Tables
Tables are grids that store information in a database similar to the way an Excel worksheet stores information in a workbook.
Access provides three ways to create a table for which there are icons in the Database Window. Double-click on the icons to create a table.
How to create a table
We have three methods which can be used to create a Table:
a) By Design View provides the tools for creating fields in a table.
b) By Wizard: This option presents you to a step by step process for creating a table. There are pre - defined field and data type, what you have to do is to select fields you want.
c) Worksheet view or entering data: Provide a way of entering data in data sheet; when you save it, will behave as created table
i. Click the Microsoft Office Button and then click Open
ii. In the Open dialog box, select and open the database.
iii. On the Create tab, in the Tables group, click Table
iv. A new table is inserted in the database and the table is opened in Datasheet view.
There are different types of data types:
a) Text - The default type, text type allows any combination of letters and numbers up to a maximum of 255 characters per field record.
b) Memo - A text type that stores up to 64,000 characters.
c) Number - Any number can be stored.
d) Date or Time - A date, time, or combination of both.
f) Currency - Monetary values that can be set up to automatically include a dollar sign ($) and correct decimal and comma positions.
g) AutoNumber - When a new record is created, Access will automatically assign a unique integer to the record in this field. From the General options, select Increment if the numbers should be assigned in order or random if any random number should be chosen.
h) Yes or No - Use this option for True or False, Yes or No, On or Off, or other values that must be only one of two.
i) OLE Object - An OLE (Object Linking and Embedding) object is a sound, picture, or other object such as a Word document or Excel spreadsheet that is created in another program. Use this data type to embed an OLE object or link to the object in the database
Primary Key
The primary key of a relational table is a field that uniquely identifies each record in the table.
Creating a Primary Key
At the moment the table has no primary key specified. None of the fields would contain information that would be unique to each student. For instance, the same last name could be shared by more than one student so last name could not be used to identify a specific student record.
The same is true for each other field. In cases like this where there is no field unique to each record, a new field can be created and used as a primary key
Queries
A Query is a specific set of instructions for extracting and manipulating particular data from the database.
Creating a Query
a) Click the Queries button on the left side of the Database Window.
b) Double - click the option that says Create query in Design View.
The Show Table window will appear, allowing you to select the source table or query to be used. The Query design window will be visible behind that.
Or First, you will use a wizard to create the query, based on the sample table that you just created. Then, you will enhance the query in Design view. At each step, you can review the SQL statement that is automatically generated by the steps that you take.
- On the Create tab, in the Other group, click Query Wizard.
- In the New Query dialog box, click Simple Query Wizard, and then click OK.
Under Tables or Queries, click the table that has the data that you want to use. In this case, click Table: Customers. Note that a query can also use another query as a record source.
- Under Available Fields, double-click the fields you want to use from your table. This adds them to the Selected Fields list. When you have added all four fields, click Next.
- Name the query and then click Finish. Access displays all of the records in Datasheet view.
- Close the query, and note that your query is automatically saved.
- The next key step in creating a query is to specify what kind of information you want to be included in the query results. The rules you set up for determining what will be included in the results are referred to as Criteria. If you look in the QBE grid, you will notice that there is a criteria row. To use criteria, you can type an example of your intended result in the criteria row below your intended field.
Adding Criteria to a Query
a) Click in the Criteria row in the required column.
b) Enter the one you want. It doesn’t matter if you use uppercase letter for the criteria or not. It makes no difference.
c) Click the Datasheet View icon to view the results of the current query. Your results will only show records matching the criteria.
d) Click the Design View icon to return to the Query Design view.
You will notice that Access has placed quotation marks around your criteria. Access uses quotation marks to specify that it is text criteria. When dates are used as criteria the date will be surrounded by a # on each side as you will see later. Numbers are left alone. When entering criteria you don’t need to enter these symbols yourself but be careful when editing. If you delete quotation marks on one side and not the other, you will get an error.
Using multiple criteria
You can use more than one criterion in a query to either narrow down the results or expand on the results. You can narrow down the results using “and” criteria and increase the results by using “or” criteria.
Using “And” Criteria
“And” criteria work by using more than one criterion in different fields. This has the effect of reducing the number of results. In the last page, you used criteria in the Suburb field to limit the results to only six records. If you decided to also specify that you only wanted results for female students you would reduce the results even further
Using “Or” Criteria
“Or” criterions allow you to increase the results by having more than one criterion for the same field. For example, if you wanted to see all of the records where the place was Iringa, Bedford or Morogoro, you would get more results than if you only had one of those places as the criteria. In other words, the results would show all records that are Iringa or Bedford or Morogoro. There are two ways of using or criteria. The first is to use the or operator as part of you criteria as in the following steps.
Ranges in Criteria
Instead of using specific criteria, you can specify that you want the results to find all records within a certain range.
Using an Upper or Lower Limit
Logical Operators can be used to set a logical limit on the range or results. If you want to specify a “Less than” criteria you can use the < symbol. To specify a “Greater than” criteria, use the > symbol.
Examples
Field Criteria Expected Result
Date >=1/9/89 Dates greater than or equal to 1/9/89
Mark <54 Marks less than but not including 54
Mark <=54 Marks less than and including 54
First Name >=M First names beginning with the letter M
or later in the alphabet
Keywords and Wildcards in a Query
The more commonly used wildcard is the * symbol. This can be used in place of any number of letters. The following examples show how it might be used.
Sm* All values starting with “Sm” – E.g. Smith or Smyth
*Ville All values ending with “ville” – E.g. Smallville or Townseville
*Smith* All values containing “smith” – E.g. Johnsmith, Smith or Smithson
Certain keywords can also be useful in query criteria. The “Not” keyword can be used with other criteria to specify that you want all records that don’t match the criteria. The “Null” keyword can be used to specify that you want all records that are blank in the specified field. The following examples show how these might be used. Not Bedford - All entries except ones for Bedford
Null - All entries that have nothing in the field
Not Null - All entries except ones that have nothing in the field
Performing calculations in the query
You can perform calculations in the query by writing them and running them
Examples;
Discount: 2/100*[Salary]
Result: [total] - [new salary]
After writing your calculation then click on the Run icon then it will be executed.
Data relationships
Foreign Keys
A foreign key is an attribute that completes a relationship by identifying the parent table. Foreign keys provide a method for maintaining integrity in the data (called referential integrity) and for navigating between different instances of an entity.
In simple language a Foreign Key is a field defined in parent table as primary key, but included in child table for establishing an association between the two tables.
Types of relationships
One-to-one (1:1): In this relationship, a record in the primary (parent) table has a single corresponding record in the related (child) table.
Requirement for establishing one to one relationship:
- Both tables must have primary keys with same data types
- In parent table you must have a primary key. In child table you must have foreign key
To make these relationships, drag the field from the primary table to the corresponding field in the related table.
One-to-many (1:M): In this relationship, a single record in the primary table can be linked to several tables in the related table
How to Add a One to Many Relationship
Steps:
i. Assign a Primary Key as a unique identifier in a field in each table.
ii. Click the Relationships icon on the toolbar.
iii. Click the Show Table icon on the toolbar The Add Tables or Queries box will appear.
iv. Double-click on the two tables that you want to relate.
v. Close the Add Tables or Queries dialog box.
vi. Drag the Primary Key field to from one table to the related field in the other table.
vi. When the Relationships dialog box appears, Enforce referential integrity. Check to be sure that everything is as you want it, and then click Create. The relationship is established.
Delete a Relationship
i. Close any open tables.
ii. Open the Database window
iii. Click the Query tab.
iv. From the Query window, click the Relationship button on the toolbar or select Relationships from the Tools menu.
v. A window appears. Rectangular shapes joined by lines appear. Each rectangle represents a table, and the lines represent relationships between tables.
vi. Point on relationship line; Right - click to obtain a menu. Select Delete.
Forms
Although information in a database can be entered and edited directly in a table, most people find it simpler to use a form. We use forms all the time in everyday life as a way of recording information so forms are familiar to us. The Form design tools in Access are very flexible and allow you to customize a form with many features to make it easy to use.
Reports
Reports are used in a database to present information in a neat and organized format that is ready for printing.
Post a Comment