Using Access for Databases

¶ … Database Using Access

Jorge Martinez Guzman

Information and Knowledge Management

Charles Beverley

Introduction to Databases

Relational and Entity Relational Databases

Cardinalities and Roles in Database Design

Business Rules and their Reflection in the Entity Relationship Diagrams

Reviewing the Conceptual Model (Figure Q4.5)

Creating a Database Using Access

Best Practices

Database Maintenance

Database Normalization

Entity-Relationship-Diagram

Entity representation

Attributes Representation

Primary Key Representation

Relationship Representation

Using Microsoft Access

Computing technology has greatly aided human development and has presented new and more efficient methods to meet various objectives to be accomplished by users and their respective organizations. However, technology is most effective when it is properly aligned organizational outcomes, and thus it is important to understand how it can be implemented into operations in order to acquire maximum benefit out of these tools.

The aim of this research is to provide a detailed overview of the technological advantages and benefits that databases can offer. First an overview of databases will be provided followed by a specific example of planning and creating a database using Microsoft Access. The research will examine the individual steps required in the process, but ultimately suggest that the planning stage is the most significant of the entire approach. The essay will conclude by providing an overview of database maintenance techniques.

Introduction to Databases

Relational and Entity Relational Databases

A database model is described as a conceptual process of designing a relational database. ER Model (Entity relationship model) is a data model for the conceptual model of the database. ER model also describes the database in the abstract form (Fleming & Halle, 1989). However, relational database stores data in tables. The ER models have two concepts:

Entities refer to the real world objects used to create a database. For example, NAME, ADDRESS, and TELEPHONE are entities.

Relationships are interactions or associations among entities.

Attributes describe the number of properties of each entity.

Entities are data objects such as STUDENTS, COURSES, and INSTRUCTORS. Examples of relationships among entities are:

STUDENTS take COURSES,

INSTRUCTORS teach STUDENTS.

ER diagram (Entity relationship diagram) is the figure used to develop entities and their relationships. Typically, a pictorial ER diagram represents a database schema that can assist in mapping ER diagram into a relational schema.

Following are the steps in designing the entity-relationship schema:

Identify entities to be used for database design,

Identify entities relationship,

Determining the attributes of each entity,

Determining primary keys for the attributes

Associate cardinality ratios with relationships

Design specialization and generalization hierarchy constraints.

The form of relational database employs both relational model and ER model. The database design derives benefits from the effortless use of both entity relationship model and design of the relational model. Thus, the normalization of ER model assists in forming ER assembles into the relational database. (Teorey, Yang, & Fry, 1986).

Cardinalities and Roles in Database Design

Cardinalities are the basic principles of database design. Cardinalities describe maximum or a minimum number of individual relationships within the database design. Typically, cardinalities show the amount of time an entity can participate in a given relationships.

Cardinality's constraints disclose a certain set of constraints within a relationship. The cardinalities show the precise and right relationships among entities. In a relational database design, there is a one-to-one relationship, many-to-one relationship, and many-to-many relationships.

An Entity-Relation model also defines certain constraints that the database contents must conform. Maximum cardinalities are the foremost constraints that indicate numbers of entities by which another entity can be associated or build relationships. For example, a database to keep a track of patient's records could have many tables such as:

A doctor table containing physicians' information;

A patient chart undergoing treatment, and A department table is having an entry for each hospital.

Cardinalities signify one-to-one, many-to-one and many-to-many relationships in the database design. For example, the customer order is a one-to-one relationship as being revealed following:

The cardinality of the relationship is also presented below:

RELATIONSHIP

CARDINALITY

PARENT

CHILD

TYPE

MAX

MIN

CUSTOMER

SUBSCRIPTION

Strong

1:N

M-O

Cardinalities role in database design is used to display the relationship of entities and explain semantics in the database design. Zero or one could represent cardinalities.

RELATIONSHIP

CARDINALITY

[Blue = Inferable]

PARENT

CHILD

TYPE

MAX

MIN

DRIVER

CORRECTION_NOTICE

Strong

1: N

M-O

OFFICER

CORRECTION_NOTICE

Strong

1: N

M-O

VEHICLE

CORRECTION_NOTICE

Strong

1: N

M-O

CORRECTION_NOTICE

VIOLATION

ID-Dependent

Multi-valued

1: N

M-O

RELATIONSHIP

CARDINALITY

[Blue = Inferable]

PARENT

CHILD

TYPE

MAX

MIN

INDEX

STOCK_QUOTE_SYMBOL

ID-Dependent

1:N

M-M

COMPANY

STOCK_QUOTE_SYMBOL

ID-Dependent

1: N

M-O

STOCK_QUOTE_SYMBOL

STOCK_QUOTE

ID-Dependent

1:N

M-M

As being revealed in the database presented above, cardinality represented by zero is optional while cardinality represented by one is required. (Date, 2005).

Business Rules and their Reflection in the Entity Relationship Diagrams

Business rules are the explicit statements that could be derived from organizational business operations. Precisely, business rules specify one or more modeling components as revealed below:

Entities

Relationships

Attributes

Connectivities

Cardinalities

Constraints. (Halle, & Goldberg, 2009).

Business rules are extremely essential in data modeling, and their exact statements are extremely beneficial in database design. Much of the success of database design depends on the accuracy of classification of business operations. In the entity relationship diagrams, there are three types of business rules:

Derivation -- This is a rule that could be derived from other knowledge

Structural assertion- a requirement is revealing the static structure of the organization.

Action assertion -- law is expressing control or constraints of organizational actions.

In the database design, the designer must understand business rules to improve the accuracy of the database. Typically, the quality of database design depends on the precision of the requirements of the database. (Genero, Jimenez, & Piattini, 2000).

The diagram in fig 1 shows the business rules among the relationships of the following entities:

STUDENTS.

COURSE.

FACULTY.

SECTION.

Fig 1: Business Rules

Business rules in Fig 1 are as follows:

A faculty member could only be allowed to teach a section in the course unless the faculty member is competent to teach the course.

A faculty member must not be appointed more than three courses at a time.

If a faculty member is already assigned three sections in a class, an attempt to add another element from the course will be rejected.

Reviewing the Conceptual Model (Figure Q4.5)

The conceptual model of the Figure Q4.5 shows the entities:

CUSTOMER

CAR

MAINTENANCE

PART

Fig 2: Conceptual Model of (Figure Q4.5)

The conceptual model of the Figure Q4.5 also shows the entity relationship between:

CUSTOMER and CAR,

CAR and MAINTENANCE, and MAINTENANCE and PART.

Business rules of the Figure Q4.5 reveal that a customer can own multiple cars. Moreover, the car can get a service record more than once. Maintenance procedures could consist of several parts. The cardinalities in fig 2 are one-to-one, many-to-one and many-to-many relationships.

The concepts of the database illustration in figure 2 can be mirrored in the database design of a bookstore. In a bookstore database design, the entities are CUSTOMER, BOOK, and BOOKSTORE. There could be relationships among entities. For example, relationships between entities are:

CUSTOMER, order BOOK,

BOOKSTORE store BOOK

The relationships will also follow the business rules. For example, a customer can order several products. The customer must pay for the books ordered before products are shipped. The cardinalities of the design could also be a one-to-one relationship or many-to-one relationship

Creating a Database Using Access

The most pivotal and important aspect of designing a database is to determine the ultimate purpose of the database itself. It is mostly a mental process that incorporates the ideas behind the design. It is the commencing point of the project, and the plan must be clear and precise. Access is a powerful tool and to maximize its practicality, a concise objective must be materialized for this software to be of any use.

Knight (2011) suggested that this process is gradual and begins with the mental process associated with brainstorming. She wrote "the first method for preparing for a database is simply to brainstorming, on paper or otherwise, as to what the database will necessitate to store, and what the site will want out of it. Make an attempt not to think of the particular fields or tables that will be needed at this point - all that detail planning can take place later. The goal is to start with a broad and complete view, and narrow down. It can often be more problematic to add in items afterward, rather than get it right the first time." It is during this crucial step that that the beginnings and ultimate purpose of the database are born and will dictate the ways in which it will be used and applied.

Microsoft also agrees with this notion of planning as being the most important and primary task needed to be accomplished in order for success. The company's help web site suggested "The first step in designing a new database is to write down its intent. In this case, you ought to enter and manage your corporation's asset data. However, don't stop there. Ask yourself who will utilize the database and how they'll use it. For example, a technician who repairs a defective machine has not the same information requirements than an accountant who tracks costs. Make sure your purpose statement focus on all of those different needs and uses."

The rest of the progression of the database can be squarely built of the purpose statement. From this point, a simple recipe of steps may be followed that include:

1. Listing the data you want to store.

2. Grouping data by subject

3. Form Groups and fields

4. Plan Data Types

5. Plan Primary Keys

6. Plan Foreign Keys

7. Design tables for SharePoint.

These stages are simple and can be easily applied by simply following the step-by-step instructions located on Microsoft's literature or web support.

Best Practices

Since the planning stage of the database design is for the most part important aspect in determining the success of the project, it is helpful to discuss some useful tips to consider when designing a database using Access. Involving the creative potencies of the mind will serve the designer well in this initial stage of the planning process. A useful way to help in formulating ideas for the design is to use a pen and paper to create an action plan that will summarize all the required and necessary reports that satisfies the objectives of the project. This written-out blueprint of the plan allows a fluid and correctable means of sorting out the fields, data types and tables and hash out their connection to each other. The simple act of putting an idea on to paper serves the designer in many ways.

The database is an integrated computer design that stores end-user data and metadata. The database is defined as a set of logically related records. Core concepts and parameters are especially essential to the understanding of database design. Relational and substance relational data are the fundamental concepts of database design.

Database Maintenance

Database Normalization

As one enters data into the inadequately designed database over time, it is probable that there will be substantial errors and inconsistencies, which will accumulate in the system. Also, it will result in false and misleading information, or cause substantial inconsistencies requiring a cleanup before migrating the information into other databases, or linked to other datasets. A user that shows carefulness, working with a dataset is capable of maintaining clean data; however, if multiple users continue working with such a database, errors and inconsistencies will accumulate in the poorly designed system. Therefore, thinking about database design is useful to create effective database systems and to comprehend some challenges, which might exist in healthcare data, and in those entered into older database systems (Wesley, 2000).

On the other hand, understanding the concepts to store in a database is the strategy behind a good database design. The theoretical design stage of the database design phase should result in the data model. This information design, comprise of a written documentation of what to store in the database, their connection to each other, and a diagram, which represents them and their relationships. Notably, many information models exist for the informatics field. The models define the entities and relate the entities to each other. Moreover, it is possible to put entities and attributes on tables and fields (Slyke and Day, 2004). Most importantly, when designing databases, designers should see real things in the world as objects, which have attributes that the objects can do things.

In addition to giving the objects such attributes, the designers should map the ideas in an object model, which is implementable using an object-oriented language, for instance, Java. In the case of designers programming interfaces to a relational database in such a language, one will need to think of how the stored concepts in the database relate to the objects. Therefore, an entity-relationship model can generate the crucial documentation required understanding the concepts that a particular relational database stores (Roman, 1999). In the field of nursing informatics, nurses should have this vital skill of understanding and model information in a healthcare setting. In so doing, the nurses will have proven their ability to use a variety of tools and techniques to understand the patient's information.

Entity-Relationship-Diagram

To acquire the proficiency of modeling data, informatics nurses require skills in entity relationship modeling. This proficiency is one approach to semantic modeling. Therefore, when informatics nurses attempt to understand and represent meaning, the nurses are engaged in semantic modeling, which can assist in creating systematic databases. Prior studies suggest that there are many approaches to semantic modeling. Nevertheless, the ER-modeling approach has many activities that help informatics nurses to comprehend the objects the healthcare setting wants to store information about, the essential features of the objects and the relationships among the objects (Wesley, 2000).

The result of ER modeling is the ER diagram, which is a graphical illustration of the structure of a given database. An Entity Relationship Diagram (ERD) serves several aims. First, the informatics nurse will gain an understanding of the information to be contained in the database via the process of structuring the diagram. Secondly, the diagram serves as a documentation tool, and finally, the ERD communicates the logical fabric of the database to users. Therefore, the ERD purposes to pass through information concerning the logic of the database to the users.

Entity representation

The entity relationship modeling process identifies three primary aspects, which include entities, attributes, and relationships. In this context, an entity refers to a thing, which stands with a distinct characteristic for identification. In database design, entities refer to the "things" about which the database stores the given information. They can include, but not limited to tangible items, concepts, people, events, and places. Also, entity type indicates to some linked items, while an entity instance refers to a single event of the former. Furthermore, the term entity is an entity type, and in most cases, entity function is more preferred, when compared to an entity instance. In the ER diagram, rectangles represent entities (Slyke and Day, 2004).

Patient

Building

Hearing

Respiratory

Blood Borne Pathogen

Attributes Representation

An attribute refers to a single data value, which describes the characteristic of an entity. Also, other terms including data item and field, describe similar significant notion. Every entity comprises of particular characteristics, which show the information concerning the entity, which the organization shows interest. In this context, the hospital may wish to know the name, SSN no, contact number of the patient. In the database context, the patient is the entity, and name, SSN no; contact refers to the attributes of interest (Roman, 1999).

Primary Key Representation

Primary key refers to an attribute or combination, which uniquely identifies the instance of a given entity. In simple words, it is not probable for two instances of an entity to have similar values for the primary keys. Sometimes, it is useful to use many attributes to make the primary key. In the case where an entity has many attributes, it becomes a composite key. However, when dealing with composite primary keys, it is essential to understand that combination of values for all the attributes must be exceptional.