Matthew Williams
Nicholas Good
COMP 491 / 492
Prof. Wahls
10/3/2006
Literature Review
In the modern day world, technology advancements are creating a paperless environment. Our senior project will provide our client with a database management system. That system will enable his business to produce customizable estimates as well as keep records of past and current window installation jobs. It will work without the use of paper as a primary means for recording data. The system will include the creation of a user-friendly interface that will be supported by a database. The creation of this application will be performed in Microsoft Access. Additionally, we have chosen to use SQL as the underlying language for our relational database. Visual Basic for Applications (VBA) will enhance our user interface. The customized forms in Microsoft Access will be easy for the client to use. They will meet his everyday business needs by adhering to the user specifications that he has listed for us. Furthermore, the tables that we will create will contain data fields for things such as customer demographics and window characteristics. By utilizing Microsoft Access, SQL and VBA, we will be able to produce a customizable database system that meets the needs of our client.
Why Microsoft Access, SQL and VBA
We chose to use Microsoft Access as a result of the software was available to our client. Our client informed us that he already had Microsoft Access, which made choosing a database management system (DBMS) very easy. Microsoft Access has many characteristics that would best fit the needs of our client. “The most popular database used in small businesses is Microsoft Access (Harrison, 2).” Since our client is the owner of a small construction business, Microsoft Access seems to fit the trend. If our client did not have Microsoft Access, MySQL would be our next choice for the DBMS. MySQL is open source software, which would eliminate some costs for our client (Allen, 3). Our product is intended for the use of only one user at a time. We do not have to worry about multiple users accessing the database at a certain time. Thus, Microsoft Access was the easy choice, because it was a single-user design which fulfilled the requirements of our client (Allen, 2). If we needed to worry about multiple users, MySQL would have been an excellent choice for a DBMS. MySQL allows for multiple users to access the same data at the same time (Allen, 2). This access scenario is better known as concurrency (Fernandez, Summers, Wood, 7). Along with the single-user design, the biggest characteristic of Microsoft Access was its portability. In the situation with our client he would be able to have Microsoft Access on his laptop, which can be brought to potential client interviews or job estimations. He would not have to worry about connecting to a server; he would have everything in front of him. An additional bonus of having a laptop would be to provide pictures of past jobs that have been stored on that same computer. Besides all of the previous characteristics of Microsoft Access, the most important is providing a user-friendly interface. In the case of our client adding customer and window information into the database, it is simple. All the user will have to do is click on a button to add a customer’s data. Upon clicking the button, a form will appear in which our client will be able to put the customer’s demographic and window information in. Ease of use and customization to our client’s specifications help justify why we prefer to use Microsoft Access as opposed to MySQL, which is not an end-user friendly DBMS (Harrison, 3).
The underlying programming language used in our Microsoft Access database management system is SQL. “SQL is the standard database query language,” meaning it is used in such DBMS’s like Oracle and MySQL. SQL commands are characterized into two groups. Data Definition Language (DDL) is the first group which includes such functions as creating or dropping tables. Data Manipulation Language (DML) is the grouping of commands that modify data. Such commands are insert, update and delete (Post, 146). The ISO (International Organization of Standards) is responsible for creating the standard of the SQL language (Post, 145). Because SQL is the underlying programming language of such highly regarded DBMS’s as Oracle, learning SQL is a valuable resource that we can take with us to future jobs. We have the basis to work with most DBMSs as a result of knowing SQL.
Visual Basic for Applications is built into every copy of Microsoft Access. We will utilize VBA to enhance the Microsoft Access user interface design. This event driven programming language will streamline and speed up data entry. VBA can be used to manipulate the user interface features such as menus and toolbars. It can also work with custom user forms or dialog boxes (Harkins, Gunderloy, 1-3). These features are easily achievable when utilizing VBA. In accordance to the client’s specifications, we will design forms with applicable textboxes, drop down menus and query buttons. VBA can also be used to create, import and export filters for various file formats. In our client’s case, this means he will be able to query a particular set of data and send that data to Microsoft Excel so he can print it out.
Forms/Reports
Forms are the way our client will maneuver around the database in order to add customers, find customers, add orders, etc. Our client will do all of these operations through a switchboard form, which “direct[s] the user to other forms and reports in the application (Post, 230).” These operations will be done by clicking on command buttons. For example, when adding a customer the user will click on an add customer button, which brings up a new form window. On the customer form window there will be a button to add an order to this particular customer, which will bring up another new form window. In the customer form, a single-row form will be used, which displays a single row of data “in which the designer can arrange the values in any format on the screen (Post, 231).” There is no need for a tabular form in this situation because we only need to add one customer. Tabular forms “display the columns and rows from a table or query (Post, 230).” Thus, a tabular form should be used when performing a search for customers or windows bought by certain customers. Subform forms can be a very beneficial form for our client. This is due to the fact that a subform is usually a tabular form that is embedded in a main form (Post, 232). For example, if our client wanted to display the data for a specific customer and also that customer’s window information, he could do so through a subform form. Every form that is created will maintain consistency, strive for clarity and we will receive feedback regarding changes the client would like (Post, 225-226). Clarity will provide an easy to operate interface for our client.
On every form there will be data that must be entered, and to maintain semantic integrity we will use certain types of data entry fields. Semantic integrity is “the part of data integrity concerned with the correctness of database information in the presence of user modifications (Fernandez, Summers, Wood, 7).” Advantages of Microsoft Access that add to semantic integrity are drop-down boxes (combo boxes) and list boxes (McFedries, 172). Combo boxes as well as list boxes only allow a user to choose one value from a list of items (Post, 240). In certain situations a check box would be valuable, for example when selecting an additional attribute of a window. Check boxes allow for the user to select more than one of the items that are listed (Post, 239). Data integrity is an important part in our implementation of creating forms and our overall DBMS. Another possibility in preventing errors is to “specify pop-up messages that appear when a cell is selected, as well as error messages that appear when data is entered improperly (McFedries, 208).” One aspect of data integrity we do not have to worry about when implementing our DBMS is concurrency control. Concurrency control deals with changes when there are concurrent programs running (Fernandez, Summers, Wood, 7). Our system has to worry about one user and not multiple users, especially at the same time.
Reports are a tool which, in the scope of our project and client’s vision, are vital elements in a DBMS. There are more differences than similarities when comparing reports to forms. Reports are designed to print multiple pages of output in which queries are run on the database. Two differences between forms and reports are: 1) forms collect data from a user and 2) reports summarize data that is collected on forms (Post, 247). Our client will want to be able to produce reports for finding the customers who bought certain windows. With this data, he will generate order reports for placing orders to the windows supplier. Reports will greatly decrease the amount of hand-written paperwork. In respect to searching for certain customers or anything other request by our client, reports can easily handle multiple pages of detailed and summarized data output (Post, 247). The types of reports will depend on the client’s specifications and needs. Some possible report types available are tabular, label and groups or subtotals. Tabular are the simplest reports and produce output similar to a query (Post, 250). Tabular reports can be useful to our client when a list of customers is needed. Groups or subtotal reports produce output that is grouped together by a certain attribute, such as the type of windows ordered (Post, 251). Reports are a tool that will be heavily utilized by our client.
Tables
The storage of data is a key concept that must be dissected in order for us to choose the correct storage techniques. There are three basic types of data models available to the small user. These types are hierarchical, network and relational (Elbra, 25). The hierarchical model is often chosen when the business data has a hierarchical relationship. This model would not be useful for our application, as the hierarchical model makes it difficult to search for data in the bottom of middle of the hierarchy. For example, our client would want to be able to find all customers who ordered bay windows. The database would have to search every customer, every order and every item if the hierarchical model were used (Post, 14).
The network model aims at solving the searching problem that the hierarchical model had. This model is built around the idea of a relationship between two record types. The network model is complex throughout and achieves greater complexity as the more remote branches are reached (Elbra, 33). The problem with this model is that the developer must account for every possible relationship question that the user might ask about the data (Post, 15). So, although this approach seemingly solves the hierarcical search problem, its cost is high and requested relationships may not have been anticipated by the developer.
The relational model is based on the use of simple sequential files. The data in this model is stored in tabular form (Elbra, 41). This means that data is stored in sets of data. The data tables are not physically connected, but rather data is linked between columns. In this model, the developer does not need to know the questions that the user will ask of the data. As long as the data in the relational model is carefully defined, the database can answer almost any question that the user asks of it (Post, 16). We will be utilizing the relational model for its ease of use. Insertions, updates and deletions of data can be carried out without any undue difficulty. This is because all the data is held in tables and the structure of any relation is practically the same, meaning that the search for any particular piece of data will not be greatly changed if the data is updated (Elbra, 46).
First, second and third normal forms aim at reducing the redundancy of data that occurs in tables. First normal form ensures that there are no repeating groups in a table. This means that every row in a particular table must have the same number of columns (Post, 89). In second normal form, each non-key column must depend on the entire primary key (Post, 92). All of the attributes of a table must be able to correspond to all of the primary keys in that table. Lastly, third normal form corrects the mistake of a non-key column’s dependency on another non-key column (Post, 94-97). To fix this redundancy mistake, another table must be created with all of the attributes that correspond to the non-key column that will now be the primary key in the new table.
Upon designing a database, three levels of design need to be taken into consideration. The internal level of design is concerned with how the data is actually stored. Developers must first understand the company before they can represent each element of that enterprise. The internal level deals with such items as the medium on which data is stored, the format of that data and methods used to provide access to data. Additionally, form of entries and organizational entries must be taken into account (Fidel, 9-10). When focusing on the internal level of design for our database, the organization of data will be a key factor. We will need to carefully define the data in the tables so that the relational model can be fully utilized.
The external level factors in the particular views of the data that are geared to specific purposes (Fidel, 10). This means that the developer must figure out how to retrieve and display information in a way that is useful for the user. This particular level corresponds directly with the forms and reports of Microsoft Access. Lastly, the conceptual level of design involves the representation of the entire company in the database (Fidel, 11). This means that the developer must answer the questions of what, where, why and how when considering the storage of data elements. The corresponding decisions of the conceptual level are independent of internal and external level decisions. A majority of the planning that is done at the conceptual level is provided for by the client. After the database developer understands the business practices of the company, the user specifications will take care of his unanswered questions. After taking into account and employing the correct storage techniques and the three levels of design, we can create the database tables efficiently and effectively.
Conclusion
After researching the various aspects of databases we were able to choose the qualities that best suited our needs. Microsoft Access was chosen for its ease of use, user friendly interface and mostly just because our client already owns a copy. SQL and VBA will further enhance our database management system in terms of design enhancements and data entry capabilities. We will provide our client with a relational database because that style of database will best meet his reporting needs. Further, we will provide forms and reports that will enable our client to input and export metadata. With our database management system integrated into our customer’s current business practices, not only will he be able to expedite the estimation process, but he will also eliminate the paper mess from his everyday functionality.
Works Cited
Access vs MySQL. 28 Sept. 2004. CodeWalkers – Resource for PHP and SQL Developers. 2 Oct 2006 <http://codewalkers.com/tutorials/79/1.html>.
Database Solutions for Small Business. 28 Sept. 2002. Linker Systems, Inc. 1 Oct 2006 < http://www.linkersystems.com/ArticlesAndWhitePapers/DatabaseSolutions.pdf>.
Elbra, Tony. Database for the Small Computer User. England: NCC Publications, 1982.
Fernandez, Summers, Wood. Database Security and Integrity. Philippines: Addison-Wesley, 1981.
Fidel, Raya. Database Design for Information Retrieval. Canada: John Wiley & Sons, Inc, 1987.
Hawkins, Susan, Gunderloy, Mike. Automating Microsoft Access with VBA. United States of America: Que Publishing, 2004.
McFedries, Paul. Microsoft Access 2003 Forms, Reports and Queries. United States of America: Que Publishing, 2005.
Post, Gerald V. Database Management Systems, 3rd Edition. NewYork: McGraw-Hill/Irwin, 2005.
Updated: October 3, 2006