Question

You are a data analyst with strong backgrounds in database design and management. In fact, you have learned from education, mentors, and experience the art of collecting data and transforming data into business intelligence and your experience in database design and management complements your abilities to analyze data. Your hypothetical employer, Park University, is in the process planning a new employee payroll database and has asked you for assistance. The database will be standalone but will need to have ability to communicate with other ODBC and SQL Server databases. The overall purpose of the database will be to input employee data for 100-150 employees. The database will need to input time and process data needed to document payroll and to create payroll checks. Park University at this point needs to understand and review options so that cost to develop and maintain this payroll database are kept at a minimum but without compromising security. Park University has requested information and has asked you to address the following questions: Would a full-scale Database Management System (DBMS) or Relational Database Management Systems (RDBMS) be required in this case? Discuss and defend your answer in scholarly detail!! Could Microsoft Access be a good option in this case? Discuss and defend your answer in scholarly detail!! Could even Microsoft Excel be used in this case maybe as a secondary database support application for further data analysis and statistical models? Discuss and defend your answer in scholarly detail!! What Systems Development Life Cycle methodology would you suggest in this case for the overall planning, design, implementation, and maintenance of this database? Discuss and defend your answer in scholarly detail!! What else might you need to cover to help Park University determine what type of database to consider for the new payroll database? Include any other important conclusions or content you see fit to support this assignment.

Answer 1

Explanation :

DBMS represents a Database Management System. It is used to create/update/delete and maintain a database and it provides controlled access to data. RDBMS, Relational Database Management System, is an enhanced version of DBMS.

DBMS :

DBMS stands for Database management system. DBMS is a system software for creating and managing databases. It can also be described as a software package designed to define, manipulate, retrieve and manage data in a database. A Database Management system (DBMS), generally manipulates the data itself, the data format, field names, record structure and file structure. It also defines rules to validate and manipulate this data.

The DBMS manages three important aspects: The data, the database schema which defines the database’s logical structure and the database engine that allows data to be accessed, locked and modified.

Essentially, the DBMS serves as an interface between the database and end users or application programs, ensuring that data is consistently organized and remains easily accessible.

RDBMS:

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL and Microsoft Access. An RDBMS is a type of database management system with row-based table structure that connects related data elements and includes functions that maintain the accuracy, security, consistency and integrity of data.

While a relational database describes the type of database an RDBMS manages, the RDBMS refers to the database program itself. It is the software that executes queries on the data, including adding, updating and searching for values. Some RDBMS programs allow you to create forms that can streamline entering, editing and deleting data. Also, the relational structure makes it possible to run queries across multiple tables at once.

In the light of the above discussions, the solution to the given set of questions are as follows:

The Solution :

Q. 1. Would a full-scale Database Management System (DBMS) or Relational Database Management Systems (RDBMS) be required in this case?

In order to decide upon whether to use a DBMS or a RDBMS in the given scenario, firstly an analysis is required to understand the differences between the two in the given context. The following table outlines the major differences between a DBMS and a RDBMS :

DBMS RDBMS

DBMS stores data as file.	RDBMS stores data in tabular form.
Data elements need to access individually.	Multiple data elements can be accessed at the same time.
No relationship between data.	Data is stored in the form of tables which are related to each other.
Normalization is not present.	Normalization is present.
DBMS does not support distributed database.	RDBMS supports distributed database.
It stores data in either a navigational or hierarchical form.	It uses a tabular structure where the headers are the column names, and the rows contain corresponding values.
It deals with small quantity of data.	It deals with large amount of data.
Data redundancy is common in this model.	Keys and indexes do not allow Data redundancy.
It is used for small organization and deal with small data.	It is used to handle large amount of data.
It supports single user.	It supports multiple users.
Data fetching is slower for the large amount of data.	Data fetching is fast because of relational approach.
The data in a DBMS is subject to low security levels with regards to data manipulation.	There exists multiple levels of data security in a RDBMS.
Low software and hardware necessities.	Higher software and hardware necessities.
Examples: XML, Microsoft Access, etc.	Examples: MySQL, PostgreSQL, SQL Server, Oracle, etc.

In the light of the above differences, the major requirements of the database of Park University are outlined below:

• The database will be standalone but will need to have ability to communicate with other ODBC and SQL Server databases.
• The overall purpose of the database will be to input employee data for 100-150 employees.
• Park University at this point needs to understand and review options so that cost to develop and maintain this payroll database are kept at a minimum but without compromising security.

Moreover, the RDBMS has the advantage of scalability, ie being capable to be upgraded for handling larger data at the future if so required by Park University.

Therefore, considering all the points as above, a RDBMS is required in this given scenario.

Q. 2. Could Microsoft Access be a good option in this case?

While there are many advantages to choosing Microsoft Access as the database management system, there are also a few disadvantages that needs to be considered which are as follows:

• Concurrent Users : Even though Microsoft has stated that Access is able to support 255 concurrent users, it is a more practical choice to select Access when the database will be used by only 15 to 20 simultaneous users. If more than 20 users try to gain access to the database, performance and response time may begin to suffer.
• Multiple Windows Operating Systems : Whenever various Windows operating systems like Windows 98, Windows ME, Windows 2000, Windows XP, or Windows Vista are used to access the same database, the database may experience corruption issues. It is recommended that all Access users are operating with the identical operating system.
• Novice User – Design and Development Limitations : A user who is new to software development may find that they are a bit overwhelmed when using Microsoft Access. In order to maximize the potential of Access, a thorough understanding of relational database design along with VBA coding is highly recommended. As with any software development tool, the ease of screen use, database performance, automation of data input, and report quality relies on the skill level and experience of the developer.
• Sensitive Data Needs : If you have sensitive data needs like storing health and medical records, financial information, academic records or employment data, for example, you will require more extensive database security than Access can offer. One suggestion would be to use SQL Server as the database engine with an Access front-end. We can help. Please contact us to discuss your needs. We have a solution that will work for you.
• Internet Limitations : Microsoft Access does have a few limitations. Access was not designed to be used with applications that are web based since its forms and reports are only functional in a Windows environment and are not compatible with an internet browser like Internet Explorer. The Access Jet database that is used by Microsoft Access is a file based system and does not contain the server features that are available in SQL Server, Oracle or other ODBC compliant databases. Access databases are more suited for web based solutions when the number of users is small or when the users are just viewing data rather than editing or deleting.
• Remote Access :In the past, record locking activities in Access caused an increase in response time which resulted in slow performance on a VPN (Virtual Private Network) or WAN (Wide Area Network) when accessing the Access Jet database. However, this problem has been eliminated as a result of quicker broadband connections. It is important to note that performance can also be enhanced if a continuous connection is maintained to the back end database throughout the session rather than opening and closing the sessions every time a table is requested. If you find that Access database performance over a VPN or WAN connection continues to suffer, then perhaps Microsoft Terminal Services is an effective alternative. We have found that Access databases linked to a SQL Server or Access Data Projects work quite well over VPN and WAN connection. Please contact us and let us know how we can help.
• Access on a Mac :Unfortunately, Microsoft Access is not designed to operate on Apple Macintosh computers. Since Access is commonly used in many businesses that use PC’s, Microsoft does not have any future plans to expand the database to be functional on a Mac.

Therefore, considering the above, Microsoft Access does not seem to be a good option in this case.

Q. 3. Could even Microsoft Excel be used in this case maybe as a secondary database support application for further data analysis and statistical models?

The Data Model feature lets creation of a basic relational database structure within Excel and so Microsoft Excel may be used in this case maybe as a secondary database support application. However, with the RDBMS used in this scenario data can bepresented in any desired format and structure for further data analysis and statistical models. Therefore, the requirement to use Microsoft Excel as a secondary database support application for further data analysis and statistical models might not be required at all.

Thus, keeping in view the objective of Park University to minimise the cost, Microsoft Excel need not be used as a secondary database support application as all the functionalities can be provided by the RDBMS itself.

Q. 4. What Systems Development Life Cycle methodology would you suggest in this case for the overall planning, design, implementation, and maintenance of this database?

There are several Systems Development Life Cycle methodology available for database design and among them the suggested Systems Development Life Cycle methodology for the current scenario can be outlined as follows:

Database System Development Lifecycle:

• Database planning
• System definition
• Requirements collection and analysis
• Database design (conceptual, logical, physical)
• DBMS selection (optional)
• Application design
• Prototyping (optional)
• Implementation
• Data conversion and loading
• Testing
• Operational maintenance

Q. 5. What else might you need to cover to help Park University determine what type of database to consider for the new payroll database? Include any other important conclusions or content you see fit to support this assignment.

Considering and analysing the given scenario of Park University, the Oracle RDBMS with PL/SQL can be considered to be the ideal for the new payroll system due to its several advantages which are outlined as follows:

• Portability : Oracle is ported to more platforms than any of its competition, running on more than 100 hardware platforms and 20 networking protocols. This makes writing an Oracle application fairly safe from changes of direction in hardware and operating system, and therefore a safe bet. One caveat, however, is that applications using some constructs (such as field level triggers) may have to be reworked when porting them to a block mode environment. You can also develop a fairly fully featured application with little        knowledge of the underlying OS. Personally, I have developed applications on OS systems barely knowing how to copy and edit text files.
• Market Presence : Oracle is by far the largest RDBMS Vendor, and spends more on R&D than most of its competitors earn in total revenue. Oracle has the largest independent RDBMS market share in VMS, UNIX and OS/2 Server fields. This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces supported and also, proficient staff are relatively easy to get.
• Version Changes : Oracle seem very good at informing you in detail as to what is not going to be supported in the next major release and usually have some knob you can twiddle for good backward compatibility, or simply leave it working, but with "don't use this, use xxxx instead" warnings in the manual. Backward compatibility is very good meaning you will not be in for an application re-write when you upgrade the DBMS. [Compare this with the Ingres v5->6 OSQL upgrade from hell.] I've worked with Oracle since V4 Beta and have never been in for nasty surprises as far as syntax goes. However, see "Version Changes" under disadvantages.
• Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerance to disk failure. You can also do point-in-time recovery. Of course, you need the archive mechanisms and storage space to do this, but Oracle supports continuous archiving to tape devices spanning multiple volumes.
• Performance : Speed of a *tuned* Oracle database and application is quite good, even with large databases. Oracle refer to >100 GB databases and have personal experience administering 10 GB databases. The performance is not only "raw", but includes consideration of performance with locking and transaction control.
• Cursor Support : Oracle, like Ingres, but unlike Sybase (until Release 10 I think),supports cursors which ease programming when performance is needed. A cursor basically lets you do row-by-row processing. Oracle supports multiple cursors per Oracle connection in line with ANSI standards.
• SQL Dialect : The dialect of SQL offered by Oracle is in my opinion superior to the others in the extensions it offers over ANSI-2, which is very much a lowest common denominator. Constructs such as the absolute function and decode keyword are very powerful Oracle additions to the standard SQL.
• Multiple Database Support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol. This is best implemented in V7. You can fairly easily move where data is actually stored from node to node in a network and have data mirroring, making it easy to optimise the location of the data from time to time. This is not so easily done with offerings from other vendors or earlier versions of Oracle, where you were not able to update more than one database in the one transaction with any reliability. This meant that you could not move data around without recoding your programs. With V7, your DBA can optimise the location without pre-planning by programmers or re-examination of the code prior to the move.
• PL/SQL : PL/SQL, the procedural extensions, is a draft ANSI standard for procedural DBMS languages. See main discussion on PL/SQL.
• Declarative Integrity : Oracle V7 onwards supports declarative database integrity (the current ANSI standard) and V6 permits you to enter the declarations. With V6, you can get the toolset (such as SQL*Forms 3) to read the declarations and automatically generate the required code. With V7 onwards, not even this is required, as the database engine automatically enforces the integrity. This means that you can open up your database to end-users through simple third party interfaces as they simply cannot break your business rules even if they try. It makes it easy to administer changes in business and data rules as there is only one spot where the change needs to be made. This lowers the cost of required modification to the system because you do not have to edit all applications code that works with the table.

While this RDBMS is known for its inherent stringent security norms which is desired by Park University, it can also cater to the requirement of the ability to communicate with other ODBC and SQL Server databases very easily.

Moreover, the RDBMS has the advantage of scalability, ie being capable to be upgraded for handling larger data at the future if so required by Park University such as including the entire employee module into the RDBMS with increased number of employees in the future or incorporate the stusent module or course module into the same RDBMS as well quite conveniently.

This concludes the answer to all parts of the question along with the necessary explanations.

Please do not forget to like the answer if it helps you. Thank you.