Everand Logo

Welcome to Everand!

  • Discover millions of ebooks, audiobooks, and so much more with a free trial

    Only $11.99/month after trial. Cancel anytime.

    Access 365 Project Book: Hands-On Database Creation
    Access 365 Project Book: Hands-On Database Creation
    Access 365 Project Book: Hands-On Database Creation
    Ebook774 pages3 hours

    Access 365 Project Book: Hands-On Database Creation

    By Mercury Learning and Information and Julitta Korol

    ()

    Read preview

    About this ebook

    This book is a practical introduction to building and managing Access desktop databases. Instead of merely explaining Microsoft Access, it helps you complete tasks related to creating, using, and managing a database. The hands-on approach focuses on creating a traditional Access desktop database and emphasizes splitting the database for optimized multi-user access. The primary project, the Employee Training Database (ETD), offers practical, real-world experience.
    The book guides you through database creation and management. It starts with planning and designing a database, followed by creating and setting up Access tables. It then covers primary keys, indexes, and table relationships, and populating tables with data. The middle sections include working with queries and designing and customizing forms.
    The latter part focuses on designing and using reports and customizing them. It concludes with compacting, splitting, and securing the database for optimized performance and security. These concepts enable efficient database management and enhance multi-user collaboration. This book transitions you from a novice to a proficient user, equipped with practical skills and comprehensive knowledge. Companion files with hands-on examples enrich your learning.

    LanguageEnglish
    PublisherPackt Publishing
    Release dateJul 22, 2024
    ISBN9781836647225
    Access 365 Project Book: Hands-On Database Creation

    Read more from Mercury Learning And Information

    Related to Access 365 Project Book

    Related ebooks

    Databases For You

    View More
    View More

    Related podcast episodes

    Related articles

    • Article

      7 Better Ways to Make Money Off the FAANGs

      Nov 10, 2021

      Five of the largest and best-known companies in the world are the FAANG stocks.  Or the FAAMNGs. Or the FANTAMANs. Whatever the acronym of the day is. The original "FANGs" – Facebook, Apple, Netflix and Google – was a clever way to refer to four mega

      1 min read

    • Article

      How Fixed Deferred Annuities Can Complete Your Retirement Income Strategy

      Aug 31, 2021

      Most fixed annuities are designed to provide safety and secure your assets and income for retirement. When financial markets are tanking, whether in the short or long term, these annuities provide guaranteed stability along with tax advantages. Annui

      5 min read

    • Article

      The Housebuilding Bust

      Mar 10, 2023

      Shares in Persimmon, one of the country’s top housebuilders, tumbled by 12% last week after it said it was “slashing” construction, says Archie Mitchell in the Daily Mail. The group warned that if “headwinds” such as higher mortgage rates and the cos

      2 min read

    • Article

      Answers

      Jun 28, 2022

      gives your Tux a total tune up to keep it ticking along. Q Small screen VM I’ve been running Ubuntu and Linux Mint happily in VirtualBox for years. A couple of weeks ago, I purchased a PowerSpec G902 gaming PC. I haven’t been able to run Ubuntu 20.04

      8 min read

    • Article

      Seakeeper Ride Test and Review

      Aug 23, 2022

      Seakeeper Ride Test and Review
      6 min read

    • Article

      I Wish I Knew What Alpha And Beta Were, But I’m Too Embarrassed To Ask

      Aug 12, 2022

      Alpha is the amount of value an investor adds or takes away from an investment portfolio. In other words, it measures how their stock- or asset-picking skills affect the portfolio, above and beyond any returns that are simply down to what you’d expec

      1 min read

    • Article

      Seeing The Light

      Jun 28, 2022

      Seeing The Light
      7 min read

    • Article

      The Ultimate Raw Quiz

      Aug 10, 2017

      The Ultimate Raw Quiz
      16 min read

    • Article

      Three Low-code Options

      Nov 12, 2020

      Counting Intel, Vodafone and VW among its customers, OutSystems helps businesses create cloudbased, on-premises and hybrid applications for mobile and web. Its development environment is predominantly drag-and-drop, with views for processes, data and

      3 min read

    • Article

      Browser Tips

      Feb 15, 2023

      Browser Tips
      3 min read

    • Article

      Stop Using Windows Tools

      Apr 26, 2023

      Stop Using Windows Tools
      16 min read

    • Article

      Awesome Apps For Less

      Aug 23, 2022

      Awesome Apps For Less
      3 min read

    • Article

      Making You Smarter

      Sep 17, 2024

      Making You Smarter
      4 min read

    • Article

      Browser Tips

      Jan 3, 2024

      Browser Tips
      4 min read

    • Article

      Code An Admin Back-end In Django

      Dec 13, 2022

      Credit: www.djangoproject.com OUR EXPERT Matt Holder has been a fan of the open source methodology for over two decades and uses Linux and other tools where possible. More featurepacked source code for this project can be downloaded from https://

      6 min read

    • Article

      Stop Using Windows Tools

      Jun 19, 2023

      Windows offers pretty good value for money - especially when you can upgrade for free from the previous version. As well as the main operating system, you get plenty of extra apps and tools for performing everyday tasks, so you don’t need to waste ti

      16 min read

    • Article

      Awesome Apps For Less

      Oct 11, 2022

      Awesome Apps For Less
      3 min read

    • Article

      Switch To Private Versions Of Software

      Jan 18, 2023

      The code for open-source software is made freely available to other developers, so they can help improve programs by identifying bugs and suggesting new features. This collaboration often involves ‘forking’, which allows you to modify somebody else’s

      5 min read

    • Article

      Browser Tips

      Mar 4, 2024

      Browser Tips
      4 min read

    • Article

      PC Matic for Mac

      Jan 12, 2024

      PC Matic for Mac
      3 min read

    • Article

      Buying The Tool

      Apr 1, 2021

      Buying The Tool
      3 min read

    • Article

      Best Free Downloads For 2023

      Dec 21, 2022

      • Remove hidden PC junk using Microsoft’s new clean-up tool • Back up files and drives without paying for premium options • Try Thunderbird’s new Android app before anyone else • Legally download thousands of classic books for free • Download high-qu

      16 min read

    Related categories

    Reviews for Access 365 Project Book

    0 ratings

    0 ratings0 reviews

    What did you think?

    Tap to rate

    Review must be at least 10 words

      Book preview

      Access 365 Project Book - Mercury Learning and Information

      Plan, Design, and

      Create a Database

      P a r t   I

      Part I introduces you to database concepts. Access databases are created by using Access objects: tables, queries, forms, reports, macros, and modules. You must understand these and numerous other terms before you can create meaningful databases for yourself and others. In this part of the book, we determine the purpose and scope of our database project and examine the database planning, designing and data normalization processes. We also begin setting up the database you’ll be working with throughout this book project.

      Chapter 1   Planning and Designing a Database

      Chapter 2   Creating a Desktop Database

      Planning and

      Designing a

      Database

      C h a p t e r   1

      In general terms, a database is a collection of information concerning a certain topic and is composed of one or more tables. While it is considerably easy to get started with a Word document or an Excel spreadsheet, creating a database requires a lot of preliminary thinking and information gathering. Even if you are already familiar with the concept of database tables, please do not skip this chapter. The initial steps which you must take prior to designing your database will determine whether your efforts will meet with a success or failure. This chapter gives you some background knowledge about Access, including the planning and design tasks that you need to complete outside of Access before you are ready to mold the information you have gathered into a working and user-friendly database application.

      ABOUT ACCESS DATABASES

      Microsoft Access stores data in related tables, hence it’s called a relational database. Most databases include many related tables. By using multiple tables, you can decrease the input of redundant data and simplify data entry. We will expand on this concept known as normalization as we progress through this chapter.

      In a relational database data in one table is related to data in another table by a common field. A classic example are Customers and Orders tables, where the Customers table stores information about customers such as their ID, Name, Address, Phone and so on, and the Orders table contains information about each order placed by a customer. Each row (record) in the Orders table must include a column (field) that identifies the customer who placed the order. The customer information is stored only once in the Customers table and by including the CustomerID field in the Orders table we can easily retrieve Customer data when needed without entering it again. Instead of storing customer data together with the order data like you would do in a spreadsheet, in a relational database you will split data into multiple tables. This approach will greatly simplify data entry and reporting and prevent duplication of data. Figure 1.1 illustrates table relationships using the sample Northwind.mdb file shipped with earlier versions of Microsoft Access.

      Figure 1.1.

        Relating Access tables by a common field in a sample Northwind database.

      Each box in the above diagram represents a single Access table. The data in each table is stored in fields A field is like a column in an Excel spreadsheet. A field can hold a specific type of data such as text, number, logical value (true or false) and so on. Some fields are designated as primary key fields. These fields uniquely identify each record in a table. In database terms, we will refer to rows as records. In the above relationship diagram, the CustomerID field in the Customers table has a primary key symbol next to it. Each CustomerID is unique; there are no two customers with the same ID. In the related Orders table, the CustomerID key is known as a foreign key. Primary and foreign keys are used for joining tables, so that you can extract related data. A customer can have many orders. Each order has one customer. This type of a relationship is known as one-to-many and is depicted by 1 and the infinity symbol at the end of the joining line as you can observe in Figure 1.1. You will learn the details of fields and relationship types as you begin the process of designing your database.

      ABOUT ACCESS OBJECTS

      Microsoft Access databases are built, maintained, and automated using Access objects. An object is a thing that is specifically designed to enable a specific feature that you will need to create and work with in your database. The six main Access objects and their short descriptions are listed in Table 1.1.

      Table 1.1

      Microsoft Access Objects

      You will get many chances of working with Access objects listed in Table 1.1 as you design, work, automate, and maintain your database. Just by looking at the objects listed in Table 1.1, it is easy to notice that Microsoft Access provides a complete programming environment that, in addition to creation of database tables, allows you to build your data entry screens and reports, and includes multiple ways of accessing and automating your database via queries, macros and programming code written in modules.

      THE EMPLOYEE TRAINING DATABASE (ETD)

      The database you will be designing, creating, and working with in this book is called the Employee Training Database (ETD). This database will aim at addressing the needs of any company or organization that is looking for a simple and reliable system of scheduling and keeping track of employee training. The training management is poorly executed in many companies. Frequently, the employee training data is stored in spreadsheets, on various scraps of paper, or in other systems that were not designed to handle the training needs. As employee training is ongoing, it makes sense to spend time and design a dedicated database system that will eliminate wasted time, resources and money lost in attempting to keep track of this process. Think of how much frustration can be avoided daily if the employee training data has its own system that is easy to work with and maintain.

      The ETD database will be a welcome solution to someone like Frank, who was seeking a nice customizable template to track employee training but found none. When he tried to create one himself in Microsoft Access, he realized that he was lost in the myriad of features that Access offers out of the box. Which features should he use first? How will he connect them into a meaningful system of storing and presenting data? Will it be usable when he’s done?

      By following easy, step-by-step instructions in this book, you can avoid many of the pitfalls that Frank had encountered and learn how to go about the process of building a simple and maintainable Training Management System.

      PLANNING YOUR DATABASE

      A primary purpose of a database is effective data storage and easy information retrieval. The database planning phase plays a crucial role in ensuring that the database you design will serve its intended purpose. Without a solid plan, your database project may prove to be nothing else but a costly waste of time and other resources that were provided. So, where do you start? The steps you need to take will depend on the size of the database project. Complex databases will require a great number of elements to consider and thus, will take longer to plan. However, no matter how big your project is, it is a good idea to start by setting up a meeting with the stakeholders—people that are most interested in bringing the database project to its successful completion. These individuals should be your starting point of reference. Find out what is expected of the new system, how it will benefit its users, and what is the expected delivery time.

      Some Database Design Questions to Ask

      Is there currently any other system in use that serves a similar need?

      If some of the information you need is already available in another system (documents, spreadsheets, or other databases), find out who you can contact to obtain samples of the data and discuss how that data is being used. It is beneficial to get the names and types of columns (fields) used, as well as any diagrams presenting the flow of data. Ask if you can link directly to other databases to provide data lookups in your database forms.

      Make a list of all the resources you were able to identify; state their format (i.e., text file, Excel spreadsheet, SQL, MySQL, or other database table, another Access database, diagram, JSON, or XML/HTML file). Don’t forget to include information that may have been shared with you via email, text, or Zoom.

      List the names of identified fields/columns.

      Make a list of proposed names for the database.

      Make a list of proposed tables.

      Ask for examples of the use cases for the database.

      Identify groups of users who will use the database on a regular basis. Ask what type of information they will need to store and retrieve. How would they like their data entry forms to look like? And what about the reports? How many reports they need and what kind of details they want to include? Will the reports be run manually or must be scheduled to run automatically at specific time intervals?

      Ask various people to prepare mock-up forms and reports on paper or in a computer program they are comfortable with. The idea is to get as much end-user input as possible in this initial planning stage. Be sure to take a careful note of the specific wants of those individuals so you can include their requests early in the database design.

      Schedule regular follow-up meetings with stakeholders and end-users to keep them informed about your progress and listen to their feedback. Your goal is to have a well-planned and useful database, and that requires a well-coordinated team effort.

      Business Rules for the Database

      It is important to determine what kind of logic you will need to implement in the database. Business rules are policies, procedures, or standards that a company has adopted which need to be enforced by the database system you design.

      The following are some examples of business rules you could implement in your database:

      An employee can only register for one course in one quarter.

      An employee cannot take the same course more than once in a year.

      An employee must fill out a feedback form after each course.

      A course cannot be given in the same room in the same period while another course is taking place.

      Each course may have one or more prerequisite courses.

      Business rules restrict the allowable data values for a database object in one way or another. They are often implemented via constraints which are rules that are placed on a database object such as a table or a column. Later in this book you will learn about types of constraints, including referential constraints, that enforce relationships between database tables; NOT NULL constraints, that specify whether null values are permitted for the field; and CHECK constraints, that use logical statements to validate whether a value is permitted in a field.

      Creating a Database Diagram

      After you have gathered substantial amount of information about the requirements for your database, decide what tables you will need and what data will go into each table. Because your tables will be in various relationships with one another, you will need to give a lot of thought to the primary key fields that uniquely identify each record in a table. When you perform a database search by this key, you should only get one record in return. Sometimes one key field is not enough to ensure the uniqueness of the data. You may need to combine two or more fields to meet this condition. We will talk about the selection of keys in Chapter 2.

      It is important to make a preliminary diagram of a database showing the tables and listing the fields that they should have. Highlight the proposed relationships between the tables by drawing the lines between them. This diagram will be very helpful to you in the design phase.

      Figure 1.2 depicts a table relationship diagram for the ETD database you will be building in this book.

      Figure 1.2.

      Table Relationships in the Employee Training Database (ETD)

      Action Item 1.1

      The Companion files include an Excel workbook named etd_Fields.xlsx. You will find there the Fields worksheet with a listing of the fields as well as field data types of the data collected during the planning stage. Add a new worksheet to this workbook for each table depicted in Figure 1.2 and assign each field from the etd_Fields worksheet to an appropriate table. When done, check your work against the etd_Tables.xlsx file in the Companion files.

      DESIGNING THE DATABASE

      The first law of database design is to accurately define the requirements. You must conduct a thorough analysis of what you want to do and how you want to do it. In our Employee Training database all employees can sign up for offered training sessions but only some employees should be allowed to administer the database. These admin users in addition to reading the data; could insert, update, and delete data. In database terms, they can perform Create, Read, Update, Delete (CRUD) operations. The database should be able to keep track of all the training offerings and employees who enroll in them. It should contain forms that allow for easy data entry as well as reports that provide detailed summaries of completed training for the company management needs.

      With the database planning phase completed, you are now ready to call on Microsoft Access to help you implement your database design. Access offers many built-in tools, known as Wizards, to make this process as much enjoyable and efficient as possible. The design process you are embarking on will continue throughout the book as you create tables, define relationships between them, set up primary and foreign keys and indexes, include validation and business rules, design your forms and reports, create statements to retrieve, insert, update, and delete data. You will enhance your design skills by learning how to automate certain database tasks via programming code. The skillset required to create even a simple database is enormous and the only way to get the hang of it is simply by doing. As you are working in Access it will be easier to find out any mistakes made in your initial mockup design. Corrections are also easier to make when the only user is the database designer. So, where do you start?

      You cannot have a database unless you’ve got at least one table, so let’s create one.

      Guidelines for Creating Tables

      Decide which table you are going to create first. Look at the diagram shown earlier and pick a table that seems central to your database design. As the ETD database will track employee training, it is wise to start by creating a table for employees. In your real-life database project, you may decide to create a link to the Employees table that already exists in your Human Resources database instead of creating a new table. However, to keep this database entirely standalone, we’ll build our own Employees table from scratch. In later chapters you will fill in this table with the existing HR data.

      When creating tables pull out the spreadsheet from Action Item 1.1 earlier in this chapter and look over the fields that were identified for the database. Are all those fields needed? Cross out the ones that you are not planning to include in your forms, reports, or calculations. Make sure that each field you keep goes into one table. Each piece of information in a database should be kept only in one place so that the process of updating data is quick and efficient. The data that may be needed for calculations can be stored in a database field, but you must avoid storing any data that can be calculated (or derived) from existing fields. Take for example the age of an employee. If you are already storing the birth date, skip the age field. Access has many built-in math functions that can get you the answers you need for your forms and reports, or any decision-making process you need to implement.

      Most databases rely on so called lookup tables. These tables usually contain a code and a description. For example, States table can have two fields: one with a state code and the other with the full name of the state. Lookup tables are very helpful as they prevent errors that may result from erroneous data entry in multiple places. The code description can be easily looked up simply by storing only a code field in the tables you need.

      Understanding Table Relationships

      When you need to display data from more than one table you will need to understand how to connect tables with one another so data can be easily retrieved. This process called joining is essential to understanding the relational databases. There are three basic types of relationships between tables:

      one-to-one

      one-to-many

      many-to-many

      The one-to-one relationship indicates that for every record in the first table only one record exists in the second table. Notice that in our ETD database (see the diagram in Figure 1.2), there is no one-to-one relationship. These types of relationships are very rare. Often time there are used in situations when you simply don’t want to store certain information in the same table for security reasons. For example, the knowledge of some personal data may be confidential and restricted to a small group of authorized individuals, so keeping that data in a separate table would absolutely make sense. Take the Employees table. To keep the Social Security number of each employee restricted to only authorized persons, you could create an ESSN table that is joined to the Employees table by the EmployeeID field. The ESSN table would only have two fields: EmployeeID and SSN. Only authorized people should be able to query the ESSN table. The relationship between the Employees table and the ESSN table would be a one-to-one.

      While the one-to-one relationships are rare, the one-to-many relationships are the most common type of the relationships in database systems. In our ETD database there are several such relationships. For example, the Employees table is related to the Registration table as a one-to-many relationship. This tells Access that for each Employee in the Employee table there can be many registration entries in the Registration table as any employee can register in multiple training sessions. In other words, many registration records can be associated with a single employee.

      The third type of a relationship, many-to-many, serves all those situations where each record in both tables can be related to none, one, or multiple records in the other table. This type of relationship requires a separate join table. The join table, also called a junction table or an intermediate table, will have at least two fields: a primary key field from one table and a primary key field from another table. To better visualize this scenario, look at the OrderDetails table depicted in Figure 1.1. Notice that the OrderDetails table has both the OrderID field from the Orders table and ProductID field from the Products table. Without the join table it would be impossible to connect Orders and Products tables to get the details of each order placed. As there are many products and there are many orders, it is said that these tables are in a many-to-many relationship.

      In our ETD database, you will create a join table named SessionInstructor. This table will join the Instructor and TrainingSession tables as depicted in Figure 1.2. These tables are in a many-to-many relationship as there are many instructors and many training sessions. By creating a join table SessionInstructor, you will be able to determine which sessions are taught by which instructor and vice versa..

      About Primary Keys

      As mentioned earlier, a primary key is a field that uniquely identifies each record in a table. To find that key, ask yourself a question: If I choose this field as a primary key, can I be certain that this field is different for every record in a table? For example, if you are creating a bookstore database, the International Standard Book Number (ISBN) printed on the back of the book cover can be used as a primary key in the Books table because each book has a unique ISBN number. How about the Social Security number for the Employees table? Although SSNs are unique, for privacy reasons, it is not recommended that you use them as primary keys. When deciding on a primary key keep in mind the following:

      A primary key must be unique; you cannot have a duplicate value in this key.

      A primary key cannot contain a Null value; the value for that field must exist when the record is created.

      Once created, the primary key cannot be changed.

      Default order of data displayed in an Access table is by primary key, so selection of your key is important.

      The keys play fundamental role in establishing relationships between tables. Unless you find a unique key, you will not be able to relate (or connect) your tables.

      Sometimes finding a unique key will seem like a daunting task. If one field cannot guarantee the uniqueness of data, try to identify two or more fields that together have values that are different for any given record. These types of primary keys are known as composite primary keys. If none of these methods work, you can assign each record in a table a unique number. This is easily done with Microsoft Access autonumbering feature. Access will number the record as you enter it, thus making it unique.

      Primary key fields speed up searching for records as Access automatically creates an index for each primary key field. Indexes are discussed in Chapter 4. Keep in mind that Access will allow you to have a table without a primary key, but you will not be able to set up relationship with other tables unless you add that key.

      About Foreign Keys and Null Values

      When you include the primary key from one table as a field in a second table to form a relationship between the two tables, the key in the second table is called a foreign key. While primary keys are used to ensure the integrity of the records within a table by guaranteeing their uniqueness, foreign keys are used to protect the integrity of data spread over multiple tables. Each value in a foreign key field must exist as values in the primary key field of the table being referred to. This concept is called a referential integrity rule. This rule prevents us from entering a child record when there is no parent record in the first table. A foreign key can be used as a check constraint by limiting a column to the defined set of values in the parent table. Foreign key columns almost always contain repeating values. Unlike primary key columns, it is possible for a foreign key column to contain Null values. In database terms, Null refers to data that’s either missing or is unknown at the time of data entry. Keep in mind however that if the foreign key contains a Null value, you won’t be able to relate the foreign key records to the records in the parent table uniquely identified with a primary key. To protect the integrity of your data, you should require a value in foreign key fields. You will learn how to check for Null values and how to use Null later in your database project when you start working with Access expressions and functions.

      You will work with foreign keys while creating table relationships in Access.

      What Is Database Normalization?

      Normalization is a process of organizing the data in a database. The Normalization process includes creating tables and establishing relationships between those tables. During the normalization process you apply a set of rules to your unnormalized data. These rules help eliminate data redundancy and protect data integrity. The normalization rules were first proposed in 1971 by Edgar F. Codd, an English computer scientist, as part of his relational model (RM) for database management. While studying various relations (tables), Codd discovered that unnormalized relations present some problems when attempts are made to update, insert, or delete data from a relation. He called these problems anomalies.

      Figure 1.3 illustrates an update, insertion, and delete anomaly as presented in Wikipedia: https://en.wikipedia.org/wiki/Database_normalization.

      Figure 1.3.

      Database Normalization – Wikipedia, The Free Encyclopedia¹,²,³

      ¹. Nabav, Example of a relational database table that suffers from an update anomaly, 2012-02-17, accessed March 23, 2023, https://en.wikipedia.org/wiki/Database_normalization#/media/ File:Update_anomaly.svg

      ². Nabav,Example of an insertion anomaly in a relational database, 2007-08-07, 2008-02-13, accessed March 23, 2023, https://en.wikipedia.org/wiki/Database_normalization#/media/ File:Insertion_anomaly.svg

      ³. Nabav, Example of a relational database table that suffers from a deletion anomaly, 2008-08-08, 2008-01-23, accessed March 23, 2023, https://en.wikipedia.org/wiki/Database_normalization#/media/ File:Deletion_anomaly.svg

      The Update anomaly refers to a situation where an update of a single data value requires updates of multiple rows of data. As Figure 1.3 illustrates, to update employee’s address we may need to change one or more records. Update anomalies often result in inconsistent data as it is easy to miss some of the data that should have been included in the update but weren’t. To avoid update anomaly, it is important to store only one copy of the data and refer to it wherever required instead of duplicating it.

      The Insert anomaly refers to a situation where a row of data cannot be inserted into a table because information that should be kept in two separate tables is kept in one. As Figure 1.3 illustrates, because we don’t know the course code for Dr. Newsome, his personal information cannot be inserted. To avoid insert anomaly it is important to keep data related to different categories in separate tables. The faculty information should not be embedded in the Courses table.

      The Delete anomaly is the opposite of the insert anomaly. Because Faculty and Courses data are kept in one table, deleting a row of data for Dr. Giddens (see Figure 1.3) will result in loss of his personal information as well as the information about courses he was assigned to teach.

      Data Normalization Rules

      The normalization rules are formally known as normal forms. Each rule is called a normal form. Each normal form has rules that must be satisfied before you can go to the next form. How far you should normalize depends on whether you want to optimize your database storage and updates or get more efficient querying capabilities. Too much normalization increases the complexity of your queries as multiple tables must be accessed to retrieve data. To achieve the highest level of normalization, start with unnormalized data and organize your data into tables in such a way that it complies to first normal form, then second normal form, until the data satisfies at least the third normal form. This is a progressive process in which each step must be completed before higher level of database normalization can be achieved. The Action Item 1.2 at the end of this chapter provides a link to a 5-minute overview and examples of normal forms. The three normal forms will cover most cases encountered while designing today’s database information systems.

      Benefits of Data Normalization

      By following specific normalization rules, it is possible to determine the most efficient way to divide the information into tables so that each piece of data is stored only once in the database and all related information can be easily retrieved. As Codd stated, by normalizing the database you can avoid undesirable insertion, update, and deletion dependencies and reduce the need to restructure the database as new types of data are introduced. Changes made to a fully normalized database should only minimally affect the applications that interact with that database. A properly normalized database saves time and money, while the unnormalized database will lead to maintenance headaches in the future.

      What Is Database Denormalization?

      Normalization leads to creating more tables and joins. Sometimes you will need to denormalize the database. Suppose your database users report serious performance issues while running complex queries that retrieve company’s asset data. You have tried to eliminate these issues by tuning your database, and even upgraded your computer hardware, but the issues seem to persist. At this point, denormalization may be the solution you need to consider. You may improve the performance of your database by storing some redundant and summarized data in tables and combining some tables that were split during the normalization process. Keep in mind that denormalization should be your last resort. By denormalizing data, you will reintroduce data anomalies that you tried to eliminate in the process of normalization, thus you will end up with a database application that is faster but less accurate. You need to decide which outcome will be most beneficial to you.

      Action Item 1.2

      Get familiar with the normalization concepts before proceeding to the next chapter. Spend the next five minutes reading Microsoft documentation at: https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description

      When you are done reading, don’t miss the opportunity to download the Office Products Troubleshooting PDF file that will serve you as a go to reference on Microsoft Access and other Microsoft 365 products. You should see the download PDF link in the left scroll area. And, in case you missed it, use this link:

      https://docs.microsoft.com/en-us/office/troubleshoot/opbuildpdf/clienttoc/toc.pdf?branch=live

      SUMMARY

      In this chapter, you began your journey into the exciting world of Access database development. As you have seen, properly designing a database, even a small one, is a big job. To get good results you must have a clear understanding of the underlying data and its various relationships. You were introduced to the required database terminology and basic database concepts that should help you working with the remaining chapters of this book. If some of these concepts still feel foreign to you, don’t panic. You will have plenty of time to assimilate this new knowledge as you progress through this book and learn how these concepts are implemented piece by piece in Access. If you picked up this book because of its hands-on approach, get ready for the next chapter where we begin building the Employee Training Database.

      Creating a

      Desktop Database

      C h a p t e r   2

      Now that you are familiar with the basic database terms and you’ve gathered your company’s requirements for the training database, it’s time to begin the implementation phase by building an Access desktop database.

      ABOUT ACCESS DATABASE FILE FORMATS

      Depending on your needs, Access databases can be created in various file formats. In this book you will use the ACCDB format to create the Employee Training database. The ACCDB file format, which is recognized by its .ACCDB extension in the file name, was first introduced in Access 2007 and has been the default file format for desktop databases since then. In earlier versions of Access, the MDB file format was used. For a complete listing of Access file formats and their differences, please see Appendix A.

      CREATING AN ACCESS DESKTOP DATABASE

      When you launch Microsoft Access, you are given options for creating a new database or for opening an existing Access database (see Figure 2.1). On this screen you will also find various predefined templates that Microsoft includes with Access free of charge. These templates provide examples of prebuilt Access applications that you can use as a starting point in your database development or as a handy learning tool. The search box available on this screen enables you to search for additional templates that are available online. This book’s focus is on creating a database from scratch, so templates are not covered. However, it is recommended that you take time to download and explore some of these templates to get familiar with the structure of various databases.

      NOTE

      Files for the Hands-On project may be found in the Companion files.

       Hands-On 2.1   Creating a Blank Database

      Launch Microsoft Access and choose New (blank database) as shown in Figure 2.1.

      Figure 2.1.

      Creating a desktop database (Step 1).

      Access displays the blank database dialog box (see Figure 2.2) where you can specify the location and database name. The default database name is shown automatically in the File Name and so is the default file location.

      Click the file folder image next to the File Name box in Figure 2.2.

      Figure 2.2.

      Creating a desktop database (Step 2).

      Access displays the File New Database dialog box (see Figure 2.3).

      Select the folder where you would like to save the database and enter ETD as the database name as shown in Figure 2.3. Notice that you can create a new folder for your database by clicking the

      Enjoying the preview?
      Page 1 of 1