Planning and creating databases using Microsoft Access

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

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.

Patient

Primary Key

SSN No.

Patient Last Name

Patient First Name

Building (Foreign Key)

Contact number

Relationship Representation

A relationship refers to the association among entities. In the entity relationship diagram, an ellipse connected by lines indicates the relationship in the entities. Most importantly, different relationships exist, and the extent of a correlation refers to the amount of entities involved in a given relationship. However, it is important to comprehend the known relationship degrees that include unary, binary and ternary. A unary relationship involves a single entity, and a binary one refers to the relationship between two entities. On the other hand, a relationship involving three entities is a ternary relationship (Roman, 1999). Moreover, relationships involving more than three entities are n-ary relationships. In this situation, n denotes the number of entities participating in a relationship.

Using Microsoft Access

In the database designing, the first thing is gathering the information, relevant to the user, and there is a consideration of how the new system can speed up and simplify the data, and how the database will be valuable to the organization. Afterward, the informatics nurse can begin creating a conceptual schema of the database, following a description of the hospital setting. This will involve looking at the paperwork of the hospital, including the patient information and use all the emerging aspects into one conceptual schema. Once the nurse has developed a conceptual system, the entity classes (tables), along their attributes (fields) will require defining (Roman, 1999).

Also, a small number 1 next to the key suggests that the table is on the one side of a relationship. On the other hand, an infinity symbol suggests that the table is on the many sides of a relationship. To ensure that each record of an entity class is not repetitive, it must have a distinctive identifier, termed as the primary key, which turns into the foreign key, after it is the one used as a field in a table it links. Most importantly, if one has the privilege to create a new primary key, it is advisable to let the system create an automated numbering field for every record, to allow for uniqueness (Roman, 1999).

In the process, the system progress there will be the addition of entities and information to the ER diagram until completion of the conceptual database. Notably, there are various methods and formats for developing entity-relationship diagrams. Since this paper follows the Microsoft Access approach to building a database, it is advisable to adopt ER diagrams as shown in the Microsoft Accesses' table relationship format (Wesley, 2000). The related tables are shown through lines that connect the core fields together, and subsequently display the type of relationship exhibited by the entities.

Conclusion

Maximizing the use of technology is dependent upon a wide range of variables. Design is one of the critical success factors to information management. Ultimately to master Microsoft Access, one must have a full grasp of how the tables in this application are created and manipulated. The fractioning of this data and its ability to be transferred in other parts of the database are the lifeblood of the program and designing these tables so that they meet the of the plan will help sustain a successful working relationship with the program and at the same time maximize resources and create and effective and efficient solution to the problem that Access is trying to solve.