(Each Answer should be at least 1 or 2 paragraphs in length) (15 Points) Explain
ID: 3716945 • Letter: #
Question
(Each Answer should be at least 1 or 2 paragraphs in length)
(15 Points) Explain just what Data Integrity is and how the use of Constraints may result in better Data Integrity.
(10 Points) Explain exactly how a “Join” works. What is a Cartesian Product?
(15 Points) Briefly describe the difference and similarities between a “Join” and a ‘Correlated Sub-Query”. Discuss the advantages and disadvantages of each.
(10 Points) Describe a Transaction. What is a transaction and what keywords are used to create transactions?
(10 Points) What is the “Transaction Log”? Describe how it works and what it is used for.
(10 Points) What are Triggers and Stored Procedures and how can they be used to make a database better?
Explanation / Answer
Explain just what Data Integrity is and how the use of Constraints may result in better Data Integrity.
Answer:
Data Integrity:
Data integrity is the maintenance of, and the assurance of the accuracy and consistency of, dataover its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data.
Data integrity is the overall completeness, accuracy and consistency of data. This can be indicated by the absence of alteration between two instances or between two updates of a data record, meaning data is intact and unchanged. Data integrity is usually imposed during the database design phase through the use of standard procedures and rules. Data integrity can be maintained through the use of various error-checking methods and validation procedures.
Data integrity is enforced in both hierarchical and relational database models. The following three integrity constraints are used in a relational database structure to achieve data integrity:
The concept of data integrity ensures that all data in a database can be traced and connected to other data. This ensures that everything is recoverable and searchable. Having a single, well-defined and well-controlled data integrity system increases stability, performance, reusability and maintainability. If one of these features cannot be implemented in the database, it must be implemented through the software.
uses integrity constraints to prevent invalid data entry into the base tables of the database. You can define integrity constraints to enforce the business rules you want to associate with the information in a database.
Explain exactly how a “Join” works. What is a Cartesian Product?
Answer:
Join:
join is an SQL operation performed to establish a connection between two or more database tables based on matching columns, thereby creating a relationship between the tables. Most complex queries in an SQL database management system involve join commands.
There are different types of joins. The type of join a programmer uses determines which records the query selects. Three algorithms work behind join operations: hash join, sort-merge join and nested loop join.
Cartesian product:
The Cartesian product, also referred to as a cross-join, returns all the rows in all the tables listed in the query. Each row in the first table is paired with all the rows in the second table. This happens when there is no relationship defined between the two tables.
Answer:
A transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database. Transactions in a database environment have two main purposes:
(10 Points) Describe a Transaction. What is a transaction and what keywords are used to create transactions?
Answer:
A database transaction, by definition, must be atomic, consistent, isolated and durable.[1] Database practitioners often refer to these properties of database transactions using the acronym ACID.
Transactions provide an "all-or-nothing" proposition, stating that each work-unit performed in a database must either complete in its entirety or have no effect whatsoever. Further, the system must isolate each transaction from other transactions, results must conform to existing constraints in the database, and transactions that complete successfully must get written to durable storage.
(10 Points) What is the “Transaction Log”? Describe how it works and what it is used for.
Answer:
Transaction Log:
The transaction log is an integral part of SQL Server. Every database has a transaction log that is stored within the log file that is separate from the data file. A transaction log basically records all database modifications. When a user issues an INSERT, for example, it is logged in the transaction log. This enables the database to roll back or restore the transaction if a failure were to occur and prevents data corruption. For example, let’s say Sue is using an application and inserts 2000 rows of data. While SQL Server is processing this data let’s say someone pulls the plug on the server
a transaction log (also transaction journal, database log, binary log or audit trail) is a history of actions executed by a database management system used to guarantee ACID properties over crashes or hardware failures. Physically, a log is a file listing changes to the database, stored in a stable storage format.
If, after a start, the database is found in an inconsistent state or not been shut down properly, the database management system reviews the database logs for uncommittedtransactions and rolls back the changes made by these transactions. Additionally, all transactions that are already committed but whose changes were not yet materialized in the database are re-applied. Both are done to ensure atomicity and durability of transactions.
This term is not to be confused with other, human-readable logs that a database management system usually provides.
In database management systems, a journal is the record of data altered by a given process.
What are Triggers and Stored Procedures and how can they be used to make a database better?
Answer:
Triggers:
A database trigger is stored code that is executed immediately after a predefined event. It is used to ensure the coordinated performance of related actions. Although implementation varies, all major relational databases support triggers
For example, a human resources (HR) application requires that every employee manager receive an informational e-mail immediately after an employee’s leave request is submitted. When a record is written into a table that stores employee leave requests, a created trigger fires and invokes the e-mail-sending procedure to the manager.
Another common trigger use is to save important original data, in its unchanged state, to maintain an audit trail or ensure that the original data remains accessible in the event of accidental changes. For example, the same HR application may contain a trigger that is executed when an employee's bank details are changed. The trigger first saves the original information to another table; this allows data modification.
Stored Procedures:
A stored procedure is a subroutine available to connected relational database system applications. Stored procedures must be called or invoked, as they are sets of SQL and programming commands that perform very specific functions. Most major relational database systems (e.g., SQL Server, Oracle, MySQL, Postgres and others) provide support for stored procedures.
Stored procedures are used when an application needs to perform a complex task using relational database information. An example might be a loan loan application to determine a customer’s repayment ability and creditworthiness. To check the customer’s repayment ability, the loan officer compares the customer’s average monthly income to his monthly account withdrawal sum over a 24-month period. To verify creditworthiness, the loan officer submits the customer’s ID or social security number to a credit reporting website.
Both actions above are complex and difficult to achieve using basic SQL commands. In addition, the customer loan approval process may be performed at varying times for different customers (i.e., the same action is repeated several times), but different customer information is associated with each action.
The above situation is an ideal example of when storedprocs should be used--i.e., when performing a complex action using a combination of SQL, Procedural Language/Structured Query Language (PL/SQL) or external programming language, e.g., Java or C++. Second, the same action is performed repeatedly, and the only changes are the parameters or data to be processed.
Stored procedures usually provide a performance benefit over writing application code, for the following two reasons:
Stored procedures are stored as part of the database’s data dictionary, rather than the application that references the database. When storedprocs call other storedprocs, this is known as a setup of nested stored procedures
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.