NURS 6411: Information and Knowledge Management | Week 6

In the past several weeks, you have been introduced to the database software, Microsoft Access, and have the opportunity to explore its capabilities for creating tables and establishing relationships. To this point, the focus has been on creating the database. Now, you begin to explore how you get the specific information or data you need out of the database. Reflect for a moment on the types of information that you could use to help in your role as a nurse informaticist. Perhaps you need to know how many patients are being treated for a certain condition, or you may be interested in determining which children are behind on their immunizations. Microsoft Access has the capability to generate a wide range of reports. The possibilities are exciting!

This week, you continue to explore functionalities in Access by generating queries and reports. Also, Part 2 of your Team Database Project is due this week.

Learning Objectives – NURS 6411: Week 6: Fundamentals of Relational Databases in Microsoft Access, Part 2 

Students will:

• Generate a suggestion for using Microsoft Access
• Develop a plan to design a Microsoft Access database

Learning Resources

Required Readings

Coronel, C. & Morris, S. (2017). Database systems: Design, implementation, and management (12th ed.). Boston, MA: Cengage Learning.

• Chapter 7, “Introduction to Structured Query Language (SQL)” (pp.246-305)This chapter introduces structured query language and discusses how it is instrumental in performing efficient data extraction and management.

Cox, J. & Lambert, J. (2013). Step by step: Microsoft Access 2013. Richland, WA: Microsoft Press.

• Chapter 5, “Create Simple Reports” (pp. 135–156)Chapter 5 explores the creation of simple reports, using a Microsoft Access wizard. The authors then demonstrate how to preview, modify, and print the reports.>
• Chapter 7, “Create Queries” (pp. 197–226)In this chapter, the authors explore the concept of queries, which are used to filter through information to identify relevant data. The authors examine two types of queries; select and action. They then demonstrate how to create queries, calculate and update records using queries, and finally, how to delete records.
• Chapter 8, “Create Custom Forms” (pp. 227–254)This chapter focuses on creating custom forms to meet unique needs. The authors review the three main sections for forms, and then demonstrate how to customize each of these sections.

Optional Resources

There are a wide variety of online tutorials available to assist with using Microsoft Access. Below are several you might consider, as needed:

Goodwill Community Foundation, Inc. (2010). Access 2010. Retrieved from http://www.gcflearnfree.org/office

This free resource provides a series of lessons, interactives, and video tutorials to assist in learning how to use Access 2010.

Pollock, A. (2010). Access 2010 essential training. Retrieved from http://www.lynda.com/Access-2010-tutorials/essential-training/62642-2.html

This series of videos provides detailed instruction on all aspects of Microsoft Access 2010 use. This resource requires a paid subscription.

Discussion: Microsoft Access Queries and Reports

As you have begun to explore Microsoft Access these past several weeks, you may have encountered challenges with using the software. In these cases, it can be beneficial to share your experiences with colleagues and exchange tips and tricks for using Access.

In this Discussion, you consider your work with Access so far and reflect on the areas that were difficult for you. You also consider areas where you could provide useful advice for others.

To prepare:

• Review the steps of planning and creating a database using Microsoft Access.
• Identify an area or step in Access that you have found to be confusing and determine what you could ask a colleague to help clarify the process.
• Consider an aspect of creating a database, query, or report using Microsoft Access that is easy for you, or a solution to a problem you found on your own. With this in mind, develop a tip for using Access that can assist your colleagues.

By Day 3

Post your question about using Microsoft Access. Describe your challenges and success with creating tables, designing queries, or generating reports in Access, including what you found easy and what areas were the most challenging.

Read a selection of your colleagues’ responses.

By Day 6

Respond to at least two of your colleagues on two different days addressing those questions raised to which you can provide input or assistance. NURS 6411: Week 6: Fundamentals of Relational Databases in Microsoft Access, Part 2

ADDITIONAL INFORMATION

Fundamentals of Relational Databases in Microsoft Access

Introduction

Relational databases are a popular choice for storing and managing data in most businesses. They’re also known as relational database management systems (RDBMSs), which means they’re designed to work with tables that contain both rows and columns. A row is a single record or set of information, while a column contains data stored in separate fields/columns within each row. In this tutorial you’ve learned about the fundamentals of relational databases and how to design a good database structure.

What is a relational database?

A relational database is a collection of related data that can be accessed by means of a set of SQL queries. The data is stored in tables, which are connected by relationships. Each table contains columns and rows that summarize each piece of information in the dataset, while other columns may act as attributes associated with those records.

The most common way to visualize this structure is with an example: imagine you have created two tables called “People” and “Employment.” In People, you would have columns such as Name, Address, Phone Number and Birth Date (columns). Then there might be another column called Relationship Age where each person’s age could be represented as an integer value between 0-100 (for example), which would indicate whether someone was younger than 18 or older than 90 years old; this way we can see how relationships work between different elements within our database!

Why use a relational database?

A relational database is a type of database that can store, retrieve and manipulate data efficiently. They are considered the most advanced form of databases because they allow you to store large amounts of data in one place. This makes it easier to manage your business’s information, which means fewer mistakes will occur when it comes time for you to put together reports or perform analysis on the data.

Relational databases also provide security features such as encryption and authentication methods so that confidential information doesn’t fall into the wrong hands—which can be especially important if the system is used by employees who work remotely or outside normal business hours (such as when customers call).

What is a table?

A table is a collection of data organized in columns and rows. Each row has a number, called its primary key, which uniquely identifies the record. The columns contain field names that can be used to store values for each record.

Each record contains one or more fields: for example, if you have three fields named first_name, last_name and age then we call this type of data an attribute set because it describes attributes like “first name” or “last name”.

What are primary keys and foreign keys?

Primary keys and foreign keys are the most important concepts in relational databases. A primary key is used to uniquely identify a row in a table, while a foreign key is used to link tables together. In Microsoft Access, these two types of relationships are typically auto-incrementing (meaning that they’re incremented automatically).

What is normalization? 1NF, 2NF, 3NF and BCNF in databases

Relational databases are collections of related data. A table is a collection of data and each row or record in the table represents one item. For example, if you want to track the number of people who have downloaded your app, then you would create an employee table that contains columns for employee name, employee ID (or some other unique identifier), department they work in and their salary level.

The primary key is usually a combination of columns that uniquely identifies each row in a table so that when someone searches for data based on its primary key value they can quickly find everything related to it (e.g., if I search “John Smith” my query will return all rows where John Smith has been hired).

How to design a good database structure

When you design a database, it’s important to have a good structure. This will help your users find information faster and more easily.

What is normalization?

Normalization is a process that reduces data redundancy in databases by organizing tables based on their attributes (such as name and address) instead of using separate tables for each attribute. The result is that there are fewer connections between related tables within the same database—and therefore less overhead when accessing them.[1] It also makes queries faster because they don’t have to be written twice: once for each table involved in an operation.[2]

What are primary keys and foreign keys?

Primary keys are unique identifiers for each record within a table; foreign keys link one or more records from another table together so that only one record can exist at any given time in those two places combined.[3]

What are indexes in databases? Clustered vs nonclustered index

An index is a structure that contains the data in sorted order. This enables you to find specific information quickly, because it’s easy to look up what you’re looking for based on its position in the list.

Indexes can be used for two main purposes:

• To speed up retrieval of rows from your database table(s). Indexes allow you to retrieve only those rows from your tables which match some criteria (e.g., name or salary).

• To restrict access to just one subset of all possible values when creating new records or editing existing ones

In this tutorial you’ve learned about the fundamentals of relational databases and how to design a good database structure.

In this tutorial you’ve learned about the fundamentals of relational databases and how to design a good database structure. You also learned about normalization, indexes, and foreign keys.

The first thing that you need to do when designing your tables is determine what type of data will be stored in each one. For example, if you’re storing customers’ names, addresses and phone numbers then it would make sense to create three separate tables: Customers table (for storing customer information), Address table (for storing address information) and PhoneNumbers table (for storing phone number information). Every thing else can be placed into another table called Orders where each record represents an order placed by one customer with different items ordered at different times during their order process through Microsoft Office 365 or other software applications like Salesforce CRM Online .

Conclusion

I’ve only scratched the surface of what relational databases are and how they work. You can learn a lot more by reading books on the topic, but hopefully this tutorial was helpful in getting you started with building your own database.