Concept explainers
A.
Explanation of Solution
“Yes”, SQL server supports both locking such as optimistic and pessimistic.
Reason:
The SQL server supports the optimistic and pessimistic locks.
Optimistic lock:
- Multiple users can update the data and it gets lock after the transaction has been processed in optimistic lock. It can be used for web applications.
- There is no conflict occur during transaction. If conflict occurs then it can be repeated until no conflict.
- There is no need to lock the
database resources and it prevents database from deadlocks. - The disadvantage is that it allows the applications from selecting and modifying the same data...
B.
Explanation of Solution
Transaction Isolation Level:
The transaction isolation level gives the measure of the extent to which transaction isolation succeeds.
- This process is defined by the presence or the absence of the phenomena such as dirty read, nonrepeatable reads, and phantom read.
- These problems occur when we read data from the database.
There are four levels of transactions. They are,
- Read uncommitted isolation level
- Read committed isolation level
- Repeatable reads isolation level
- Serializable isolation level
Read uncommitted isolation level:
Read uncommitted isolation level is the lowest isolation level which allows dirty reads, nonrepeatable reads, and phantom reads.
- It makes the transaction to read any data currently on a data page irrespective of the data has been committed.
- The uncommitted read isolation level applies to read-only operations like SELECT, SELECT INTO, and FETCH.
- The uncommitted read should not be applied for the cases where the answer must be accurate.
- The uncommitted read does affect other transactions.
Read committed Isolation level:
Read committed Isolation level allows nonrepeatable reads and phantom reads but it prevents dirty read...
C.
Explanation of Solution
Cursor:
The term cursor is used to retrieve the information in row by row manner. If the records stored in the database table, which needs to be updated one row at a time is carried over by cursor.
- It plays the major role for fetching a required row from the table in the database which holds numerous records.
- Cursors are defined using SELECT statement.
- It points the set of rows which is resulted from an SQL SELECT statement.
- In general the cursor is placed in the first or last row.
- It influences the performance of the SQL Server as it uses the SQL server instances memory, decrease network bandwidth, and reduction in concurrency.
- It’s recommended to avoid the use of cursor and the cursor can be replaced with WHILE loop, temporary tables, and sub queries.
- The types of cursor are listed below:
- Forward-only cursor
- Static cursor
- Dynamic cursor
- Keyset cursor
Forward-only cursor:
Forward-only cursor is used for fastest retrieval of data from the database. It is the fastest cursor among all the cursors but with the drawback it does not support backward scrolling.
- Data deletion, updating the data can be done using the Forward-Only cursor.
- It is sensitive as any changes made in the database will affect the original data source...
D.
Explanation of Solution
Difference between Security model for SQL server and figure 6-16:
Note: Refer Figure 6-16 in textbook: “A model of DBMS security”
Security model for SQL server | Security model for database Management system |
It provides the security architecture to permit the database developer to make secure database application and counter threats. |
It provides the |
It contains the hierarchical set of entities with server. Each and every server contains multiple databases, and each and every database contains set of securable objects... |
E.
Explanation of Solution
Types of SQL server backup:
There are different types of SQL server backups. They are,
- Full backup
- Differential backup
- Transaction log backup
- Database file and Filegroup backups
Full backup:
The full backup in SQL server is simple type of backup which does not depends on recovery model.
- It backs up the any type of activity during the backup.
- It backs up any type of uncommitted transactions in transaction log file.
Differential backup:
The differential backup helps to reduce the time for restoring the modified database.
- It backs up the portion of database that have modified since final full database backup.
- It backs up any type of activity during differential backup and also any type of uncommitted transactions in transaction log file...
F.
Explanation of Solution
SQL server recovery models:
There are three different types of recovery models in SQL server. They are,
- Simple
- Full
- Bulk-logged
Simple:
Simple recovery model helps to maintains smallest quantity of information in transaction log.
- Simple recovery model restores the data from full or differential backups. It requires less administration power and easy to manage than full or bulk-logged model.
- It expense high data loss when data file damages occurs.
- The benefits allows the high-performance copy operation.
- The drawback of the simple model is changes because database or differential backup must need to be rebuild...
Want to see the full answer?
Check out a sample textbook solutionChapter 6 Solutions
Database Concepts (8th Edition)
- How is the processing of SQL DDL statements (such as CREATE TABLE) different from the processing required by DML statements?arrow_forwardHow does a SQL injection attack operate and what is it? Describe its operation and how to prevent SQL injection attacks.arrow_forwardWhat exactly is an audit trail in a database? Give a short explanation (no more than 60 words) of how Extended Events are used in SQL Server.arrow_forward
- One of the most important functions of any relational database management system (RDBMS) is to support your ability to manipulate data. To this end, you must be able to insert data in a table, modify that data, and then delete whatever data you no longer want to store. What are some of the MySQL commands that can be used to perform these tasks? Give an example of each command using an SQL statement and explain how each command works. Can you give examples that play off eachother please. Thankyouarrow_forwardWhat precisely does it mean for a database to have an audit trail? Using no more than sixty words, could you please explain how SQL Server utilises Extended Events?arrow_forwardOne of the most important functions of any relational database management system (RDBMS) is to support your ability to manipulate data. To this end, you must be able to insert data in a table, modify that data, and then delete whatever data you no longer want to store. What are some of the MySQL commands that can be used to perform these tasks? Give an example of each command using an SQL statement and explain how each command works. Can you please give a real life inventory example that uses the commands insert, update and delete (e.g. we want to insert a new item into our grocery store inventory, then we need to update the inventory, and finally delete an item that is no longer needed, etc.) Thank you Thankyouarrow_forward
- What are the general weaknesses and SQL injection enablers:arrow_forwardWhat is a database audit trail, exactly? Extended Events are used by SQL Server in the following ways.arrow_forwardWhat is the purpose of a database audit trail? Explain in no more than 60 words how Extended Events are used in SQL Server?arrow_forward
- You have been given the task of documenting the process and commands required for creating a new user and assigning privileges to that user for one or more of the tables in a DBA’s practice database layout below. This process will be documented schematically in the table below that will: - identify all SQL commands (using proper role names, table names, user names, etc. and proper syntax), - identify which user (DBA or NEW USER) should execute each command, and - detail the expected outcome based on your knowledge of Oracle data administration, roles, and granting and revoking privileges to roles. Initially, the new user will not be able to do anything, including any selects, inserts, updates, or deletes on any of the DBA’s tables. The DBA will create a role and assign the new user to that role. Then, the DBA will grant and revoke various select, insert, update or delete privileges to the role. It is your responsibility to determine the outcome. You will not be able to execute…arrow_forwardDatabase Transactions can occur concurrently by several users. This can cause issues with data consistency. To help resolve this problem, RDBMS can implement Read Consistency. Q4. What is Read Consistency? Q5. What are two problems that could occur without data/read consistency? 1. 2. Q6. What is Data Locking and how/why is it used? Q7. Please review the following SQL statements and answer the following questions. On which line does the first commit occur? Is this first commit implicit or explicit? Which SQL DML statement is rolled back when line 12 is run? Which SQL DML statement is committed on line 16? Is the commit in line 16 implicit or explicit? Assume 10 of the ‘SA_REP’ employees start with a salary of 1000.00. What is their salary when 17 is run? NOTE: For those of you who are familiar with programming, assume there is no infinite loop here. 1 select * from employees; 2 DROP TABLE Employee_Project_Assignment; 3 UPDATE employees 4 SET salary…arrow_forwardIn the context of database management, explain the concept of data binding parameters in SQL queries. How can parameterized queries help prevent SQL injection attacks?arrow_forward
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage LearningA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr