for Microsoft Access
Create complex MS Access databases without being an expert in relational database design! Designer for Microsoft Access asks you plain-language questions about what you want to manage with your database, and creates the tables and relationships automatically. Free trial available
When you need to build a database, there is a temptation to immediately sit down at the computer, fire up your RDBMS, and start creating tables. Well, don’t. There is a process you need to follow to develop a well-designed relational database and, at the start, you’re a long way from actually setting up the tables in the database application. Not necessarily a long way in time, but certainly in thought.
A systematic approach to the design will save you, the designer, a lot of time and work and makes it much more likely that the “client” for the database will get something that fits the need. In this topic, you’ll look at the steps of a design process that you will follow.
When you get to the point of drafting your tables and fields, you’re going to use a very low tech approach to the design process—pencil and paper. You’ll find lots of blank spaces in this manual to work in. When you start building databases on your own, if you’re the kind of person who just cannot think unless you’re looking at a computer screen, there are software tools available for modeling a database. These CASE (computer-aided software engineering) tools can be used to create diagrams and some will create documentation of the design; they can be particularly useful when a team is working on the design of a database. Additionally, some CASE products can generate commands that will actually create the tables in the RDBMS.
The idea is to draw a picture of your tables and fields and how the data in the tables is related. These are generally called entity-relationship, ER, or E/R diagrams. There are various formal systems for creating these diagrams using a specific set of symbols to represent certain objects and types of relationships. At this point in your design career, you should probably use whatever works for you. Again, a formal system becomes more useful when a group of people are working on the same design. Also, using a recognized method is helpful for documenting your design for those who come after you. For additional information on ER diagrams, you may want to read Entity-Relationship Approach to Information Modeling by P. Chen.
Following a design process merely ensures that you have the information you need to create the database and that it complies with the principles of a relational database. In this topic, you're going to use this process to get the point of having well-designed tables and relationships and understand how you can extract data from the tables. After that, you, as the designer, may also have to create additional objects for the application, such as the queries, forms, reports, and application control objects. Most of those tasks are application-specific and are beyond the scope of this topic.
In this topic, you'll review an outline of the process. You'll go through the first few steps of identifying the purpose of the database and, in subsequent topics, will design the tables and relationships. You're the database designer and the information contained represents the client (the person(s) who has expressed the need for a database).
If you wish,you can work on a database of your own where you can be both the client and the designer. Then you have nobody to blame but yourself if it doesn't come out right.
You will rarely be handed a detailed specification for the database. The desire for a database is usually initially expressed as things the client wants it to do. Things like:
The early stages of database design are a people-intensive phase, and clear and explicit communication is essential. The process is not isolated steps but is, to a point, iterative. That is, you'll have to keep going back to people for clarification and additional information as you get further along in the process. As your design progresses, you'll also need to get confirmation that you're on the right track and that all needed data is accounted for.
If you don't have it at the beginning of the design process, along the way you'll also need to develop an understanding of the way the business operates and of the data itself. You need to care about business operations because they involve business rules. These business rules result in constraints that you, as the database designer, need to place on the data. Examples include what the allowable range of values is for a field, whether a certain field of data is required, whether values in a field will be numbers or characters, and will numbers ever have leading zeros. Business rules can also determine the structure of and relationship between tables. Also, it will be difficult for you to determine what values are unique and how the data in different tables relates if you don't understand the meaning of the data. The reasons will be clearer when you actually get to those points in the process.
Your goal is to collect as much information as you can about the desired products of the database and to reverse engineer that information into tables and fields.
Each field should be atomic; this means each should hold the smallest meaningful value and, therefore, should not contain multiple values. The most common disregard of this rule is to store a person's first name and last name in the same field.
Do not include fields to hold data that can be calculated from other fields. For example, if you had fields holding an employee's hourly pay rate and weekly hours, you would not include a gross pay field.To be Continued…
The rest of the steps in the design process will be addressed in subsequent topics. They are:
As you think about the design of a database and work through the process, keep Motto #4 in mind. If five different database designers were given the same information, they would most likely come up with five different database designs. But that is all the more reason for following a process and complying with the design principles. The resultant design will be a valid design.
Create database applications using Microsoft Access. This electronic book introduces the reader to databases with an emphasis on how to create database applications using Microsoft Access. The lessons are presented in detailed step-by-step instructions that assume no prior knowledge of databases from the reader. This is a technique that was experimented and proven on its original web site (http://www.functionx.com/access). Anybody who reads this ebook and follows its instructions will surely get a hands-on experience about the capabilities of Microsoft Access.
Download the ebook now and begin creating database applications using Microsoft Access - Microsoft Access Fundamentals