Essay Example: Comparative Analysis of Access and SQL Databases

SQL is better suited for an entire organization. There are several reasons for this; SQL can be used to retrieve large amounts of records in a database in a quick and efficient manner. In an organization, there are going to be large amounts of data that need to be retrieved quickly and in an efficient manner. Secondly, SQL has standards that are established and there is no need to write a large amount of code. This is different from non-SQL databases many of which are not standardized. This means that many applications required in a large organization can be supported using SQL databases.

Is your time best spent reading someone else’s essay? Get a 100% original essay FROM A CERTIFIED WRITER!

Order now

Access on the other hand is designed for individuals and small to medium sized organizations. It is a desktop based database system that is sold as part of the Microsoft office software suite. However, Access has a limit and can only hold up to 2GB of data. Its multi-user support is also limited to 10 users meaning that it is not suitable for an entire organization. Functional dependencies in a relational database can be described as the constraints in a relationship between two attributes. These functional dependencies are where one attribute will be dependent on anther attribute (Information Resources Management Association, 2015). For example, every patient will have a patient id in a hospital database. The patient can either be male or female. This means that for each unique patient id, there can be two genders assigned to it. In the relationship between the patient id and gender, each gender must have a patient id. In this case, the gender is a functional dependency of patient id.

Atomicity is a key concept in any database transaction. Atomicity means that a transaction acts as a whole. Many transactions will have many permutations contained within them. However, atomicity treats the transaction as a whole. If one part of the transaction fails, then the whole transaction fails. Consistency means that whenever initiation of a transaction occurs, it moves the database from one valid state to another valid state. Each database has constraints which are rules that define the function the database. For the database to be in a valid state, it must fulfill all the rules. Isolation means hiding the activities of one transaction from transaction acting on the same records in the same database. This means that the result of two transactions should be the same whether the two transactions occur concurrently or serially. Durability means that once a transaction is completed the results should be stored permanently. This means that the new state is recorded and future transactions will act on the updated database.

In dimensional modeling, the business process, which has the most interest within the organization in this case, would be customer identification, and related tasks. Identifying a customer in a hospital environment would require the patient id, gender of patient, age of patient, whether insurance, and the type of visit, patient, cover the patients physician, and amount payable. Consistency is very important in a database (SQL:Acess to SQL server, 2002). Data should remain consistent between different customers for the database to remain valid. If data is inconsistent among customers, this means that the same transaction can result in two different end states of the database. This would bring about an error, where the database would not be able to support its business functions.

References:

In Information Resources Management Association,. (2015). Business intelligence: Concepts, methodologies, tools, and applications. IGI Global

SQL: Access to SQL Server. (2002). Berkeley, CA: Apress.