Database Key Issues Assignment

01-06-16 Rochio Varela 2 comments

Introduction

Database development is fundamental in the area of computing and ICT within organizational contexts. Database Management Systems (DBMS) provide the systems, tools and interfaces by which the organization can manage their information and use it to assist in the effective running of the organization. Databases offer many links to other areas such as programming, systems analysis, HCI, as well as embracing issues of compatibility and end-user interfacing. This unit explores database architecture, DBMS and the use of databases in an organizational context. Database design techniques are investigated and successful learners will be able to apply theoretical understanding to design, create and document a database system.

This document includes the report on the Tasks of Database Management Topics and Case Study implementation of Structured Systems Analysis and Design Method (SSADM).

Task 1 – Database and Data Management Systems

P1.1 Database Key Issues Assignment

Data Integrity

Data integrity is a byword for the quality and the reliability of the data of a database system. In a broader sense data integrity includes also the protection of the database from unauthorised access (confidentiality) and unauthorised changes. Data reflect facts of the real world database.

Data Integrity Database Key Issues Assignment

Types of integrity constraints

Data integrity is normally enforced in a database system by a series of integrity constraints or rules. Three types of integrity constraints are an inherent part of the relational data model: entity integrity, referential integrity and domain integrity as shown in above diagram:

Entity integrity concerns the concept of a primary key. Entity integrity is an integrity rule which states that every table must have a primary key and that the column or columns chosen to be the primary key should be unique and not null.

Referential integrity concerns the concept of a foreign key. The referential integrity rule states that any foreign-key value can only be in one of two states. The usual state of affairs is that the foreign key value refers to a primary key value of some table in the database. Occasionally, and this will depend on the rules of the data owner, a foreign-key value can be null. In this case we are explicitly saying that either there is no relationship between the objects represented in the database or that this relationship is unknown.

Example

Referential integrity

Domain integrity specifies that all columns in relational database must be declared upon a defined domain. The primary unit of data in the relational data model is the data item. Such data items are said to be non-decomposable or atomic. A domain is a set of values of the same type. Domains are therefore pools of values from which actual values appearing in the columns of a table are drawn.

Example

Domain integrity

Techniques for Guaranteeing Data Integrity

When designing a database application, developers have various options for guaranteeing the integrity of data stored in the database. These options include:

  • Enforcing business rules with triggered stored database procedures, as described in “Overview of Triggers”
  • Using stored procedures to completely control access to data, as described in “Introduction to Server-Side Programming”
  • Enforcing business rules in the code of a database application
  • Using Oracle Database integrity constraints, which are rules defined at the column or object level that restrict values in the database

Types of Integrity Constraints

 Types of Constraints

Constraint Type Description
NOT NULL Allows or disallows inserts or updates of rows containing a null in a specified column.
Unique key Prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
Primary key Combines a NOT NULL constraint and a unique constraint. It prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
Foreign key Designates a column as the foreign key and establishes a relationship between the foreign key and a primary or unique key, called the referenced key.
Check Requires a database value to obey a specified condition.
REF Dictates types of data manipulation allowed on values in a REF column and how these actions affect dependent values. In an object-relational database, a built-in data type called a REF encapsulates a reference to a row object of a specified object type. Referential integrity constraints on REF columns ensure that there is a row object for the REF.

Real Application of Integrity Checking

Security

Data-integrity assurance techniques go a long way in making a computer system secure.

  • Intrusion Detection
  • Non-Repudiation and Self-Certification
  • Trusting Untrusted Networks

Performance

The design of a certain class of integrity assurance mechanisms takes advantage of already existing redundant information to improve system performance.

  • Duplicate Elimination
  • Indexing
  • Detecting Failures

Example of Data Integrity

Integrity checks on raw data can be used to identify disk failures.

Security

Database Security is the mechanisms that protect the database against intentional or accidental threats.

Application Data Secuirty Life Cycle Database Key Issues Assignment

Database security is concerned with avoiding the following situations:

  • Theft and fraud
  • Loss of confidentiality
  • Loss of privacy
  • Loss of integrity
  • Loss of availability

Computer-based security controls for multi-user environment

  • Authorization
  • Views
  • Backup and recovery
  • Integrity
  • Encryption
  • RAID technology

Database Security can be used as follows

Default security provider

You use the default security provider to set which groups and users are authorized to perform specific changes to the data when a transaction is run. For example, you can use this security provider to set which users have the authority to make changes to specified data, and then to ensure that a user has permission to perform changes to the data when a transaction is run.

LDAP security provider

Use the LDAP security provider to set and check which users can perform specific transactions. For example, you can use the LDAP Security Provider to set which users have the authority to run specified transactions, and then to ensure that a user has permission to perform those transactions when the transaction is run.

Example of Data Security

Example of Data Security Database Key Issues Assignment

Above diagram illustrates how data Security is done using access control and encryption work.

Recovery

The process of restoring the database to a correct state in the event of a failure

The storage of data generally includes four different types of media with an increasing degree of reliability:

  • Main memory
  • Magnetic disk
  • Magnetic tape
  • Optical disk

Recovery Technique

  • Maintain a log file containing transactions records that identify the start/end of transactions and the before-and after-images of the write operation.
  • Using deferred update, writes are done initially to the log only and log records are used to perform actual updates to the database.
  • If the system fails, it examines the log to determine which transactions it needs to redo, but there is no need to undo any writes.
  • Using immediate updates, an update may be made to the database itself any time after a long record is written. The log can be used to undo and redo transactions in the event of failure. [ Read about: Features of Database Management System Assignment ]

Recovery Application:  Diagram shows the designed to recover data.

Recovery Application Database Key Issues Assignment

Concurrency

Concurrency in terms of databases means allowing multiple users to access the data contained within a database at the same time. If concurrent access is not managed by the Database Management System (DBMS) so that simultaneous operations don’t interfere with one another problems can occur when various transactions interleave, resulting in an inconsistent database.

Concurrency is achieved by the DBMS, which interleaves actions (reads/writes of DB objects) of various transactions. Each transaction must leave the database in a consistent state if the DB is consistent when the transaction begins.

Example: cheque is cleared while account balance is being computed. DBMS ensures such problems don’t arise: users can pretend they are using a single-user system.

Concurrency

Concurrent Execution

You know there are good reasons for allowing concurrency:-

  1. Improved throughput and resource utilization.

(THROUGHPUT = Number of Transactions executed per unit of time.)

The CPU and the Disk can operate in parallel. When a Transaction

Read/Write the Disk another Transaction can be running in the CPU.

The CPU and Disk utilization also increases.

  1. Reduced waiting time.

In a serial processing a short Transaction may have to wait for a long transaction to complete. Concurrent execution reduces the average response time; the average time for a Transaction to be completed.

What is concurrency control?

Concurrency control is needed to handle problems that can occur when transactions execute concurrently. The following are the concurrency issues:-

Lost Update: an update to an object by some transaction is overwritten by another interleaved transaction without knowledge of the initial update.

Lost Update Example:-

Lost Update Example

Transaction A’s update is lost

Uncommitted Dependency: a transaction reads an object updated by another transaction that later falls.

Uncommitted Dependency Example:-

Uncommitted Dependency Example

Transaction B reads an uncommitted value for R

Inconsistent Analysis: a transaction calculating an aggregate function uses some but not all updated objects of another transaction.

Inconsistent Analysis Example:-

Inconsistent Analysis

The value in SUM will be inconsistent

Main goals of Database Concurrency Control

  • To point out problem areas in earlier performance analyses
  • To introduce queuing network models to evaluate the baseline performance of transaction processing systems
  • To provide insights into the relative performance of transaction processing systems
  • To illustrate the application of basic analytic methods to the performance analysis of various concurrency control methods
  • To review transaction models which are intended to relieve the effect of lock contention
  • To provide guidelines for improving the performance of transaction processing systems due to concurrency control; and to point out areas for further investigation.

Concurrency Control

Concurrency control in database management systems permits many users (assumed to be interactive) to access a database in a multi programmed environment while preserving the illusion that each user has sole access to the system. Control is needed to coordinate concurrent accesses to a DBMS so that the overall correctness of the database is maintained. For example, users A and B both may wish to read and update the same record in the database at about the same time. The relative timing of the two transactions may have an impact on the state of the database at the end of the transactions. The end result may be an inconsistent database.

Multiple update problems

In this problem, the data written by one transaction (an update operation) is being overwritten by another update transaction. This can be illustrated using our banking example. Consider our account CA2090 that has Rs. 50000 balance in it. Suppose a transaction T1 is withdrawing RS. 10000 fro the account while another transaction T2 is depositing RS. 20000 to the account. If these transactions were executed serially (one after another), the final balance would be Rs. 60000, irrespective of the order in which the transactions are performed. In other words, if the transactions were performed serially, then the result would be the sameif T1 is performed first or T2 is performed first- order is not important. But idf the transactions are performed concurrently, then depending on how the transactions are executed the results will vary. Consider the execution of the transactions given below

Multiple update problems

Both transactions start nearly at the same time and both read the account balance of 50000. Both transactions perform the operations that they are supposed to perform-T1 will reduce the amount by 10000and will write the result to the data base; T2 will increase the amount by 20000 and will write the amount to the database overwriting the previous update. Thus the account balance will gain additional 10000 producing a wrong result. If T2 were to start execution first, the result would have been 40000 and the result would have been wrong again.

This situation could be avoided by preventing T2 from reading the value of the account balance until the update by T1 has been completed.

Incorrect Analysis Problem

Problems could arise even when a transaction is not updating the database. Transactions that read the database can also produce wrong result, if they are allowed to read the database when the database is in an inconsistent state. This problem is often referred to as dirty read or unrepeatable data. The problem of dirty read occurs when a transaction reads several values from the data base while another transactions are updating the values.

Consider the case of the transaction that reads the account balances from all accounts to find the total amount in various account. Suppose that there are other transactions, which are updating the account balances-either reducing the amount (withdrawals) or increasing the amount (deposits). So when the first transaction reads the account balances and finds the totals, it will be wrong, as it might have read the account balances before the update in the case of some accounts and after the updates in other accounts. This problem is solved by preventing the first transaction (the one that reads the balances) from reading the account balances until all the transactions that update the accounts are completed. 

Inconsistent Retrievals

Consider two users A and B accessing a department database simultaneously. The user A is updating the database to give all employees a 5% salary raise while user B wants to know the total salary bill of a department. The two transactions interfere since the total salary bill would be changing as the first user updates the employee records. The total salary retrieved by the second user may be a sum of some salaries before the raise and others after the raise. Such a sum could not be considered an acceptable value of the total salary (the value before the raise or after the raise would be).

Inconsistent Retrievals

The problem illustrated in the last example is called the inconsistent retrieval anomaly. During the execution of a transaction therefore, changes made by another transaction that has not yet committed should not be visible since that data may not be consistent. 

Uncommitted Dependency

Consider the following situation: 

Uncommitted Dependency

Transaction A reads the value of Q that was updated by transaction B but was never committed. The result of Transaction A writing Q therefore will lead to an inconsistent state of the database. Also if the transaction A doesn’t write Q but only reads it, it would be using a value of Q which never really existed! Yet another situation would occur if the roll back happens after Q is written by transaction A. The roll back would restore the old value of Q and therefore lead to the loss of updated Q by transaction A. This is called the uncommitted dependency anomaly.

Atomicity

Atomicity is part of ACID property of transaction

  • Atomic

Process all of a transaction or none of it; transaction cannot be further subdivided (like an atom)

  • Consistent

Data on all systems reflects the same state

  • Isolated

Transactions do not interact/interfere with one another; transactions act as if they are independent

  • Durable

Effects of a completed transaction are persistent

We will consider the banking example to gain a better understanding of the acid properties and why are they important. We will consider a banking system that contains several accounts and a set of transactions that accesses and updates accounts. Access to a database is accomplished by two operations given below:-

  1. Read(x)-This operation transfers the data item x from the database to a local buffer belonging to the transaction that executed the read operation
  1. Write(x)-the write operation transfers the data item x from the local buffer of the transaction that executed the write operation to the database.

Now suppose that Ti is a transaction that transfers RS. 2000/- from account CA2090 to SB2359. This transaction is defined as follows:-

Ti:

Read(CA2090);

CA2090:=CA2090-2000;

Write (CA2090);

Read (SB2359);

SB2359:=SB2359+2000;

Write (SB2359);

Implementing Atomicity on above

Let’s assume that before the transaction take place the balances in the account is Rs. 50000/- and that in the account SB2359 is Rs. 35000/-. Now suppose that during the execution of the transaction a failure(for example, a power failure) occurred that prevented the successful completion of the transaction. The failure occurred after the Write(CA2090); operation was executed, but before the execution of Write(SB2359); in this case the value of the accounts CA2090 and SB2359 are reflected in the database are Rs. 48,000/- and Rs. 35000/- respectively. The Rs. 200/- that we have taken from the account is lost. Thus the failure has created a problem. The state of the database no longer reflects a real state of the world that the database is supposed to capture. Such a state is called an inconsistent state.

The database system should ensure that such inconsistencies are not visible in a database system. It should be noted that even during the successful execution of a transaction there exists points at which the system is in an inconsistent state. But the difference in the case of a successful transaction is that the period for which the database is in an inconsistent state is very short and once the transaction is over the system will be brought back to a consistent state. So if a transaction never started or is completed successfully, the inconsistent states would not be visible except during the execution of the transaction.

This is the reason for the atomicity requirement. If the atomicity property provided all actions of the transaction are reflected in the database of none are. The mechanism of maintaining atomicity is as follows The DBMS keeps tracks of the old values of any data on which a transaction performs a Write and if the transaction does not complete its execution, old values are restored o make it appear as though the transaction never took place. The transaction management component of the DBMS ensures the atomicity of each transaction.

Relational Model Constraints

Integrity Constraints

Each relational schema must satisfy the following four types of constraints. 

  1. Domain constraints

Each attribute Ai must be an atomic value from dom( Ai) for that attribute.

The attribute, Name in the example is a BAD DESIGN (because sometimes we may want to search a person by only using their last name. 

  1. Key Constraints

Superkey of R: A set of attributes, SK, of R such that no two tuples in any valid relational instance, r( R), will have the same value for SK. Therefore, for any two distinct tuples, t1 and t2 in r( R),

t1[ SK] != t2[SK]. 

Key of R: A minimal superkey. That is, a superkey, K, of R such that the removal of ANY attribute from K will result in a set of attributes that are not a superkey. 

Example CAR( State, LicensePlateNo, VehicleID, Model, Year, Manufacturer)

This schema has two keys:

K1 = { State, LicensePlateNo}

K2 = { VehicleID }

Both K1 and K2 are superkeys.

K3 = { VehicleID, Manufacturer} is a superkey, but not a key (Why?).

If a relation has more than one keys, we can select any one (arbitrarily) to be the primary key. Primary Key attributes are underlined in the schema:

CAR(State, LicensePlateNo, VehicleID, Model, Year, Manufacturer) 

  1. Entity Integrity Constraints

The primary key attribute, PK, of any relational schema R in a database cannot have null values in any tuple. In other words, for each table in a DB, there must be a key; for each key, every row in the table must have non-null values. This is because PK is used to identify the individual tuples.

Mathematically, t[PK] != NULL for any tuple t € r( R). 

  1. Referential Integrity Constraints

Referential integrity constraints are used to specify the relationships between two relations in a database.

Consider a referencing relation, R1, and a referenced relation, R2. Tuples in the referencing relation, R1, have attributed FK (called foreign key attributes) that reference the primary key attributes of the referenced relation, R2. A tuple, t1, in R1 is said to reference a tuple, t2, in R2 if t1[FK] = t2[PK].

Conclusion

It is concluded that all the key issue of data management systems and Relational Model Concept (domain, attribute, etc) and Relation Keys like (super key, candidate key, primary key, Relationship between identity keys and foreign keys is covered in this task.

Reference List 

[1] Date, C, J, Introduction to Database Systems, 7th edition 9

[2] Silbershatz, Korth, Sudarshan, Database System Concepts 4th Edition.

[3] Trends in Data Quality – Lou Agosta

http://www.dmreview.com/issues/20050201/1018111-1.html

[4] Gartner ranks data quality management software, reveals trends – Hannah Smalltree

http://searchdatamanagement.techtarget.com/news/article/0,289142,sid91_gci1263861,00.html

[5] Data Warehousing Special Report: Data quality and the bottom line -Wayne W. Eckerson

http://www.adtmag.com/article.aspx?id=6321&page=

[6] The Importance of Quality Control: How Good Is Your Data? – Andrew Greenyer

http://www.customerthink.com/article/importance_quality_control_how_good_data

Read all other Database Assignment here.

Programming Assignment Help UK is committed to plagiarism free assignments. This free Database Key Issues Assignment is complete solution based on case study.



Discussion

  • Database Key Issues Assignment | Programming Ba...

    […] Programming Assignment Help brings Database Key Issues Assignment which discuss HCI, issues of compatibility, end-user interfacing, integrity constraints  […]

    01-06-16
  • Database Design Concept Assignment | Programming Assignments Help

    […] estimates, accounting data and invoices etc. This database management system is required to control database  key issue, maintain data consistency, and avail data sharing facility, avail data integration facility which […]

    01-06-16
  • Comments are closed.