The following roles may be assigned to different people on a development team: requirements analyst, designer/architect, developers/coders, tester/QA, DBA, IT dept., project manager. Some of these may overlap a bit where individuals are responsible for multiple roles in the development process. In larger environments, there may be entire teams delegated to be responsible for different roles (Developer.com, 2006).
With regard to the duties for each role, the requirements analyst is responsible for analyzing business requirements and needs, which aid in providing solutions to problems (The Data Warehousing Institute, 2006). The designer or architect uses the information gathered by the requirements analyst to design the database structure. The developers are responsible for implementing the actual design by coding the application and creating the database. The testers test the application and database functionality once the application is in its final stages of development. Essentially, a DBA, or database administrator would be responsible for managing and administering the database however, in smaller environments, or when using smaller database applications, the IT department is typically responsible for hardware and system support once the database application has been deployed (FMS Technical Papers, 2006). The project manager is responsible for overseeing and management all aspects and phases throughout the design and development process (The New York Times, 2006).
Rapid application development and deployment is an advantage to using Access in a team environment. Database applications can be implemented quickly, which saves time, money and other resources. The low cost element is a major benefit of using Access. It is considerably cheaper than other solutions (GI Business Data Solutions, 2006). It is also bundled with the Microsoft Office suite of applications. Many companies already have user licenses for Office products. Therefore, there is no added cost in this case. Another advantage to using Access is its Office integration capabilities (Access Programmers, 2006).
Access can be used to connect to a variety of backend databases, including MS SQL Server databases by using linked tables. It is also possible to use an Access application interface to connect to multiple databases. In a team environment, users as well as developers only need to be trained on Microsoft Access. Access is easier to learn than other more advanced database systems, such as Oracle or MS SQL Server (Microsoft, 2006). Another advantage to using Access to develop a database is its ease of implementation and overall simplicity (Adobe, 2006).
Although the maximum number of concurrent users is 255, the recommended number of simultaneous is only 25-50 (MS Access 2000 Maximum Capacities, 2006). This can be considered a disadvantage if many developers must be connected to the same database at the same time. In addition, as databases grow, they can be increasingly difficult to support. The IT department frequently becomes responsible for supporting smaller database systems developed in Access, which they are usually not too fond of doing because of Access’ challenges and limitations (FMS Technical Papers, 2006). Another disadvantage to developing an Access database in a team environment is that all developers must have Access installed (Microsoft, 2006).
Assuming the challenges of developing an Access database on a team are obstacles, which can be worked around in some way; manageability and data integrity could become an issue when using a team to develop an Access database. Having multiple users modifying a database at the same time always runs the risk of compromising data integrity. One way of dealing with this issue would be to work on separate files for the interface development and making frequent backups of the actual data. Although, this may be considered a limitation being that a database cannot be backed up while users are connected to it. Infrequent backups and difficulty restoring data can become an issue if a team of developers is working on the same database.
Assuming limitations of using Access in a team development environment are considered obstacles, which cannot be worked around; one major limitation would be the restriction of database size. Having multiple developers working on one database might require a significant amount of space for data, even if it is only temporary. Access only supports databases up to 2 gigabytes in size. If more space is needed, the database should be upgraded to a more robust system such as MS SQL Server. Some argue that Access is unstable in multi-user environments (Adobe, 2006).
References:
Developer.com. (2006). Retrieved May 24, 2006 from http://www.developer.com/java/ent/article.php/3529081
The Data Warehousing Institute. (2006). Retrieved May 24, 2006 from http://download.101com.com/pub/TDWI/Images/Building%20a%20BI%20Career.pdf
FMS Technical Papers. (2006). Retrieved May 21, 2006 from http://www.fmsinc.com/tpapers/genaccess/DBOD.asp#challenges
The New York Times. (2006). Retrieved May 24, 2006 from http://jobs.nytimes.com/texis/jobsearch/details.html?id=446c92d36d6ea0&pp=25&pbt=nytcategoryinformation&cbt=Statistics&view=2&page=1
GI Business Data Solutions. (2006). Retrieved May 24, 2006 http://www.galleryimage.com.au/Why-Access-Database.htm
Access Programmers. (2006). Retrieved May 24, 2006 from http://www.access-programmers.co.uk/services2/why_access.htm
Microsoft. (2006). Retrieved May 24, 2006 from http://download.microsoft.com/download/5/d/0/5d026b60-e4be-42fc-a250-2d75c49172bc/Access_Whats_Right.doc
Adobe. (2006). Retrieved May 24, 2006 from http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_17034
MS Access 2000 Maximum Capacities. (2006). Retrieved May 24, 2006 from http://www.micronetservices.com/Access2kCapacities.htm
Microsoft SQL Server is a high performance, scalable and secure database management system available for Microsoft Windows systems. The most recent release of MS SQL Server is available in four different versions. The Express Edition, which is essentially a newer release of the Microsoft SQL Server Database Engine (MSDE), is free to use and redistribute as an extremely lightweight version of Microsoft SQL Server. The Express Edition is easy to install and offers enhanced security. As far as scalability and performance is concerned, the Express Edition only supports one CPU and one gigabyte of RAM. In addition, databases are limited to four gigabytes in size (Microsoft – SQL Server Express Edition Features, 2006).
If high performance and scalability are desired, it may be best to go with the Workgroup Edition, the Standard Edition, or the Enterprise Edition, depending on actual requirements. Each of these versions has no limit with regard to database size. The Workgroup Edition supports up to two CPUs. The Standard Edition supports up to four CPUs. The Enterprise Edition has no limit to the number of CPUs that it supports. The Workgroup Edition supports up to three gigabytes of RAM. Both the Standard and Enterprise Editions support as much RAM as the operating system can handle. The Enterprise Edition also supports features such as partitioning and indexed views, which are not available in other versions. All versions, including the Express Edition offer the same security options (Microsoft – SQL Server 2005 Features Comparison, 2006).
The pricing for MS SQL Server is broken down by license type and version. Each version is priced differently according to overall features and there are two types of licensing available for each version. The two types of licensing are Processor Licensing and Client Access Licensing. Processor licensing is broken down by how many processors the server will contain. Client access licensing is broken down by the number of clients who will access the server. The only exception is with the Express Server version, which is free. Client Access Licensing can be further broken down by either users or devices. Either way, the pricing is the same (Microsoft – SQL Server: How to Buy, 2006).
Here is a sample breakdown of the prices for different versions:
|
Version |
Processor Licensing (Retail) |
Client Access Licensing (Retail) |
|
Workgroup |
$3,899 |
$739 with 5 Workgroup CALs |
|
Standard |
$5,999 |
$1,849 with 5 CALs |
|
Enterprise |
$24,999 |
$13,969 with 25 CALs |
Resources:
Microsoft – SQL Server Express Edition Features. (2006). Retrieved May 14, 2006 from http://www.microsoft.com/sql/editions/express/features.mspx
Microsoft – SQL Server 2005 Features Comparison. (2006). Retrieved May 14, 2006 from http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Microsoft – SQL Server: How to Buy. (2006). Retrieved May 14, 2006 from http://www.microsoft.com/sql/howtobuy/default.mspx
One of the appeals to using Microsoft Access is its versatility with regard to its integration capabilities. It is possible to import and export a variety of different data types and files. For instance, the data within an Access database can be easily exported to XML data. XML has many benefits but is particularly useful when creating web services (GotDotNet, 2006).
Microsoft Access also integrates very well with the Office suite of applications. For example, Access data can be directly exported to Excel spreadsheets (Adamski & Finnegan, 2006, pp. AC414 – AC422). From within Microsoft Word, the data in an Access database can be used to create mail merge documents. The mail merge function is used to create customized documents using a template in Word (Microsoft – Help and Support, 2006).
As with all Microsoft Office applications, Access can be customized using the Visual Basic for Applications (VBA) language. It is possible to create modules that contain procedures and functions, which can be called from within an Access database. These modules can also be used in other applications. This functionality provides further integration with other Office applications (Adamski & Finnegan, 2006, pp. AC554 – AC590).
One of the most powerful integration features of Microsoft Access is the ability to connect to databases from outside sources. For instance, it is possible to create a custom application that connects to an Access database to retrieve and manipulate data. These custom applications can be written in just about any programming language including Visual Basic, Java, and C (Visual Basic Explorer, 2006).
Although Access databases are not optimized for multi-user access, they can be very useful when creating portable stand-alone applications that require a powerful backend database. Access databases can also be easily upgraded to higher end database systems, such as SQL Server (Enflow Information Systems, 2006).
Another powerful integration feature is the ability to generate SQL code using the expression builder (FMS Technical Papers, 2006). There are times when I have had to write complex SQL statements for applications, but rather than write them from scratch, I was able to use the query wizard to help build the SQL statement for me. I was then able to use the SQL statement that was generated by Access in my Visual Basic code. I have found it useful at times as well to create temporary databases, which allow me to test out my SQL code before actually running it against a production database.
Finally, Microsoft Access also offers built-in web integration features such as the ability to create web forms that allow data viewing and manipulation (Adamski & Finnegan, 2006, pp. AC369 – AC414).
Reference:
Adamski, J. J. & Finnegan, K. T. (2006). New Perspective on Microsoft Office Access 2003, Second Ed. Boston: Course Technology
GotDotNet. (2006). Retrieved May 14, 2006 from http://www.gotdotnet.com/team/XMLWebServices/
Microsoft – Help and Support. (2006). Retrieved May 14, 2006 from http://support.microsoft.com/kb/q208933/
Visual Basic Explorer. (2006). Retrieved May 14, 2006 from http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/Database_Beginner_ADO_DAO.asp
Enflow Information Systems. (2006). Retrieved May 14, 2006 from http://www.enflow.com/WhyUseAccess.htm
FMS Technical Papers. (2006). Retrieved May 14, 2006 from http://www.fmsinc.com/tpapers/queries/
Filed under: Databases
From a broad perspective, business rules are a set of defined guidelines that help a business achieve its goals. These rules may include operational guidelines, policies, regulations, and common practices. Businesses often hire consultants to help them gather information to define their own set of business rules. There are software packages available as well to aid in the process of defining business rules (Wikipedia – Business Rules, 2006).
With regard to database design, business rules are specifications used in the design process. They are determined during the interviewing phase. During the interviewing phase, the users and managers should be asked questions that will help the database designer determine requirements and limitations of the database. These requirements pertain to the actual data that will be stored, the data structures, as well as the relationships between entities and entity sets. Business rules should be documented and used as guidelines throughout the database design process. Without business rules, a database designer would have little to go on when designing the database. Business rules should be kept up to date according to current requirements to ensure data integrity consistency (DBMS Magazine, 2006).
To enforce business rules during the database design process, different types of data integrity should be used. For example, entity integrity should be used to enforce rules that pertain to the types of data that can be stored and referential integrity should be used to define how data is related (Jose’s Database Programming Corner, 2006).
To aid in communicating business rules during the database design process, graphical depictions of data, data structures and relationships are often used. These are called Entity Relationship Diagrams, or ERDs (About – Entity-Relationship Diagram, 2006). There are tools available to help create these diagrams. For example, CASE tools can be used to analyze requirements, model processes and develop data models, such as ERDs (Wikipedia – Case Tool, 2006).
Reference:
DBMS Magazine. (2006). Retrieved May 13, 2006 from http://www.dbmsmag.com/hernandz.html
Jose’s Database Programming Corner. (2006). Retrieved May 13, 2006 from http://b62.tripod.com/doc/dbbase.htm
Wikipedia – Business Rules. (2006). Retrieved May 13, 2006 from http://en.wikipedia.org/wiki/Business_rules
Wikipedia – CASE Tool. (2006). Retrieved May 13, 2006 from http://en.wikipedia.org/wiki/CASE_tool
About – Entity-Relationship Diagram. (2006). Retrieved May 6, 2006 from http://databases.about.com/cs/specificproducts/g/er.htm
Filed under: Databases
There are certain guidelines that should be taken into consideration when designing the logical structure of a relational database regardless of its physical implementation. One of the most important benefits of using a relational model versus a flat-file model when designing a database is the reduction of data redundancy. When a database consists of several unrelated flat data files, many times the tables will contain redundant attributes, which hold duplicate data. One of the main purposes of relational databases is to reduce or eliminate data redundancy by using relationships between tables, references to data that reside in separate entity sets and normalization (Washington.edu, 2006).
Before actually creating a database, careful thought should be put into the design process. The table structures, including attributes, entities and data types should be known at least to the extent possible. The relationships between tables and their types should also be determined and mapped out. This requires identifying primary and foreign keys within tables and understanding how the data will be referenced by other tables (Adamski & Finnegan, 2006, pp. AC A1 – AC A6). When creating table structures, ideally the table will contain a primary key, no calculated fields, and no redundant data (Databasedev.co.uk, 2006). To aid in the design process, entity-relationship models can be used. These help to identify relationships between tables and their entities. A visual diagram can be used as a graphical representation of an entity relationship model. The diagram describes conceptual relationships between tables (Rob & Coronel, 2004, p. 45).
One of the most important aspects of database design is the concept and use of normalization. Normalizing a database is accomplished by analyzing table structures and making modifications as necessary. When appropriately implemented, normalization helps to reduce data redundancy and anomalies, but there are times when it may be necessary to de-normalize a database such as when increased performance is desired (Adamski & Finnegan, 2006, p. AC A115).
Another major consideration when designing a database is data access time. Although hardware and software have a significant impact on the speed at which data can be accessed and manipulated, just as important is how the data is organized within a database. In a sense, the better organized a database is, the faster data can be accessed. However, it is important to keep in mind that if a database is over-normalized, it may actually result in decreased performance (Rob & Coronel, 2004, p. 184).
Regardless of which software or database management system is used, the concepts and guidelines pertaining to the design of a relational database are essentially the same.
Reference:
Rob, P. & Coronel, C. (2004). Database Systems: Design, Implementation, & Management. Boston: Course Technology
Adamski, J. J. & Finnegan, K. T. (2006). New Perspective on Microsoft Office Access 2003, Second Ed. Boston: Course Technology
Databasedev.co.uk – Relational Database Design Guidelines. (2006). Retrieved May 6, 2006 from http://www.databasedev.co.uk/database_design_guidelines.html
Washington.edu – Benefits of Normalization. (2006). Retrieved May 6, 2006 from http://faculty.washington.edu/ocarroll/infrmatc/database/design/sld029.htm
