¶ … 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

(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…