Database Standardization, Optimization, and Security
1) What is Standardization? Standardization is structuring tables and attributes to eliminate redundancies and avoid data insertion, deletion, and update problems.
2) Summon two main goals of Standardization? To facilitate database handling and ensure data independence.
3) What does Data Independence Standardization bound? The relational database is concerned only with storing data logically, not physically.
4) When is minimized redundancies which tried to avoid bound Standardization? Minimizing redundancies avoids inconsistencies by eliminating redundant data and preventing repetition across tables.
5) What does facilitate database maintenance linked to Standards? Organizing tables allows DBAs to make changes easily.
6) What is the professional who does maintenance usually structural objects in the database? An ADB (administrator database).
7) What are the professionals who normally deal with data tables (relations)? Development analysts.
8) How many stages can be used in standardization? Five stages.
9) How many stages are used in more standardization in most companies? Three stages.
10) What are the possible stages of standardization? 1st Normal Form (1NF), 2nd Normal Form (2NF), 3rd Normal Form (3NF), 4th Normal Form (4NF), and 5th Normal Form (5NF).
11) What are the stages of standardization used in most businesses? 1NF, 2NF, and 3NF.
12) What does the stages of normalization are cumulative? To reach 3NF, a database must meet the prerequisites of 1NF and 2NF.
13) Set the 1st normal form. A table is in 1NF if all its attributes are atomic, meaning fields contain single values, not sets of values.
14) On the phrase “in 1st Normal Form multivalued attributes are being considered or not in the same column. True or False? Explain. True – although attributes are in the same column, if they are multivalued, it violates 1NF.
15) When applying the 1st normal form in a table it can provide redundant information. Yes or No? Yes.
16) What problems can arise if applied to only 1 normal form? Update, insertion, and deletion problems.
17) What would be the “update problem” by applying the 1st Normal Form? Changing a student’s address requires updating multiple rows, risking database inconsistencies.
18) What would be the “problem of integration” to apply the 1st Normal Form? A student is necessarily bound to a discipline, which isn’t always true, as students may change schedules.
19) What would be the “problem of elimination” to apply the 1st Normal Form? Canceling a student’s registration may delete other information like their number, name, and address.
20) Set the 2nd normal form. a) If the first normal form (1st NF) b) If all the attributes that do not belong to the key depend on the key through a basic functional dependence, ie depend on the entire key and not one of its attributes or subsets alone.
21) Set the 3rd Normal Form. All its attributes are atomic (1NF), it has a simple key (2NF), and non-key attributes do not depend on other non-key attributes.
22) How can we define the 4th Normal Form? If there is an N-to-N relationship between the table’s attributes, independent entities cannot be stored in the same table.
23) How can we define the 5th normal form? It creates a single table to represent data between interrelated tables, often used for large schedule data with functional multidependency cycles between at least three columns.
UNIT II – PROCESSING AND OPTIMIZATION OF QUERIES
2.1 routines to access query processing
24) For the phrase “A query typically has several possible implementation strategies.” True or False? Explain. True – choosing the best strategy is query optimization.
25) What is query optimization? The process of choosing the best implementation strategy for a query.
26) What is the name given to the user chooses the best execution plan to be executed among the possible? Strategy implementation.
27) Define implementation plan? It indicates the sequence of algorithms to apply to the query.
28) What can be done to assist in the implementation plan? Properly create tables, adjust indices, update statistics (e.g., ANALYZE in Oracle, RUNSTATS in DB2, UPDATE STATISTICS in SQL Server), adjust database parameters, and design queries to return only necessary data.
29) Quote of the operations that usually take longer to complete? Cartesian product and Merge (Join).
30) To quote a possible synonym for Implementation Plan? (ANALYZE in Oracle, RUNSTATS in DB2, UPDATE STATISTICS in SQL Server).
31) What is a more expensive access plan with few records or many records? Why? With many records, because more data must be searched.
32) When we run a query to the DBMS. The DBMS executes the query in the same way? Explain. No – The DBMS executes each operation in the implementation plan to reach the final result.
33) What is the access method? Algorithms and data structures used to retrieve data directly from tables.
34) To have the fastest response to a query is better than the data is in RAM or on disk? Why? In RAM, because research is more agile.
35) Briefly Optimization and Execution Query which involve steps (are two). a) Rewrite the query b) Determine the best access plan.
36) For submitting a query to the database, after being chosen the best access plan to be executed, what is the next step? The DBMS executes each operation in the plan and returns the result to the user.
37) Quote 1 note that the optimization process uses. Relational theory.
38) What are the steps for submitting a query? a) Parse Query b) Check Semantics c) Query Rewrite d) Optimize Access Plan e) Code Generation.
39) For the step ‘Parse Query’ submission of a query to define it. This step checks query syntax. If correct, the DBMS proceeds to semantic checks.
40) For while ‘Check in Semantics’ submission of a query to define it. The DBMS checks for tables, views, columns, attribute types, and data used in the query.
41) For while ‘Query Rewrite’ submission of a query to define it. The database rewrites the SQL into a simpler or more efficient internal representation (often derived from relational algebra).
42) For while ‘Tuning Access Plan’ submission of a query to define it. The DBMS uses transformation rules to handle the query’s internal representation and find the most efficient implementation strategy.
43) For the step ‘Code Generation’ submission of a query to define it. With the optimal access plan, the DBMS calls basic routines.
44) What is the purpose of analyzing an access plan? Improve query routines until reaching basic DBMS routines.
UNIT II – PROCESSING AND OPTIMIZATION see Section 2.2 Optimization heuristics (rule)
45) Who chooses the access plan in the most appropriate optimization heuristics (rule)? Query optimizer 46) What is optimization by rule (or heuristic)? Set of heuristic rules to transform the query into an access plan. 47) The indices are used in the database at the conceptual level or at the practical level? conceptual 48) How to optimize a rule (or heuristic)? the optimizer could always choose to use an index that is available on a column 49) What are clustered indexes? (stored in a pre-ordered) it easy to search later-type range. 50) What are nonclustered indexes? (not stored pre-ordered) to most difficult type of search range. 51) Which of these indices is commanded clustered or nonclustered? Clustered 52) The phrase “The heuristic approaches can induce the process of optimizing the drastic mistakes” is true or false? Explain. True. The use of heuristics simplifies the execution of queries by the optimizer, since it is not necessary detailed information about data sources. 53) The implementation of the optimization heuristic (a rule) to the DBMS is simple or complex? Explain. Simple. The use of heuristics simplifies the execution of queries by the optimizer, since it is not necessary detailed information about data sources. 54) The optimization heuristic produces good results. True or false? Explain. Fake. Based only on heuristics to generate execution plans can lead to inefficient plans 55) Quote 1 characteristic of sequential scan. Usually called full table scan 56) Quote 1 Indexed scan feature. Usually called the indexed table scan 57) Which factor is key to optimizing the cost to be effective? a lower cost plan in the space of all possible execution plans. 58) What do the commands update statistics for a table? Includes information on number of rows in a table, the number of pages, the number of distinct values of an indexed column, and so on. 59) Quote 1 sample statistic that can be considered by the optimizer cost? What has the best performance 60) The optimizer for cost is considered more complex than the optimizer to rule? Explain. No – it is more efficient
61) Today, most database management systems market using what kind of optimization? For cost 62) is more efficient to create a query or some queries to check the best execution plan? Some querys break it possible to compare who has the best performance 63) The access plan (costs) for a table with 10, 10.000 or 10,000,000 records is equal? Explain. No. It is possible that with 10 one is better, now with 100 other is better, just testing. UNIT II – PROCESSING AND OPTIMIZATION OF QUERIES 2.4 Optimization Semantics 64) The concept of semantics could be linked to data mining? Explain. Yes – a type of information mining d 65) The semantics is focused on what, how the query is executed or the result of the query. Because the query and runs 66) The optimization semantics can be linked to that concept of relational database? 67) Quote of the steps in which the optimization is based semantics to execute a query. 68) The semantic analysis has link with Web pages? Yes or no. Explain. Yes – applies to semantic analysis to data that are in HTML. 69) The optimization attempts to resolve semantic concepts of the 2 relational databases that are not yet well executed. Quote of 1. 70) What kind of model database that could bind to a semantic optimization (name 2)? UNIT II – PROCESSING AND OPTIMIZATION OF QUERIES 71 Firefox 2.5) summon a type of operation of the database using sort. union 72) What Sort Merge Join? It is a method of joining tables without requiring the use of indexes. 73) What is Nested Loops Join? It is a join algorithm in which usually involves an index of at least one of the tables. 74) What is Hash Join? In this type of junction, a hash table is built for the larger of the two tables. The lower table is then scanned and the hash table is used to find the rows that match the larger table. 75) In which case the Hash Join works well? if the hash table can remain in memory (otherwise, temporary tables have to be allocated). 76) What is Hint? They are statements that may be included in SQL to instruct or guide the optimizer hints using the User can specify the join order, type the path to access data, the index to be used and further instructions. 77) Among the 3 concepts: Sort Merge Join Nested Loops Join and Hash Join, 2 are considered the same family, what are they? sort merge join and hash join 78) Nested Loop Join typically offers better response time and better throughput? Better response time 79) Usually Sort Merge Join gives better response time and better throughput? Best throughput 80) within a single command line to execute a query can be used several hints? yes 83) Usually transactions are implemented in single-user or multiuser systems? multiuser 84) summon a synonym for the transaction. operations 85) How can we describe transaction linking reading and writing. It is a set of operations of reading and writing data 89) What is commit? Ends the transaction (requests realization of their actions). 90) What is rollback? Requests that the actions of the transaction are undone 92) There are 3 ways to access linked to the SET TRANSACTION, would like 2 of these. READ (read only); WRITE (only update); 93) There are 4 levels of isolation for a transaction, would like 2 of these. SERIALIZABLE (1 transaction executed with complete isolation), READ UNCOMMITTED (1 transaction can read data that were not already commit). 94) What is the SERIALIZABLE isolation level? Transaction 1 performed with complete isolation 97) What is the isolation level READ UNCOMMITTED? Transaction 1 can read data that were not already commit 98) What is the level of isolation more restrictive? serializable 99) What is the level of isolation less restrictive? Read uncommitted 103) What does Durability for the acronym ACID? You should ensure that the modifications made by a transaction that has successfully persist in the database. 106) On the recovery of data which can happen if the index is corrupted? is possible that search results can return unwanted or duplicate key inserts happen.107) In order to have data persistence. The data should be written as: a) CPU b) RAM c) Disc. C – disco 108) relative to fault tolerance in the database (Concepts of Recovery) cite a feature. a mechanism is not 100% safe, 109) Set Undo? Transaction not completed its operations, the changes made by this transaction in the database are undone. Undo an update in the database. 110) Set Redo? Transaction completed its operations, it has commit, but their actions may not have been reflected in the DBMS, so the changes made are redone to the DBMS. Redo an update in the database. 111) to name the 3 types of failures that can occur in a DBMS. Transaction failure, system and storage DEMEI 115) Quote 2 causes of transaction failure. deadlock, cancel User-116) Quote 2 causes of system failure. power outage, failure to SO 117) Quote 2 causes of failure of the storage medium. bad sectors on the disk failure in the head read / write the disk. 118) Link between possible failures (transaction, system and storage medium) which has the lowest, average or most likely to happen. Larger transaction, the rating system, smaller storage 119) Link between possible failures (transaction, system and storage medium) which has a recovery time medium, small and large. Transaction system small-medium – large storage 121) for the checkpoint information is written from where to where? Memory to disk 122) What is sensitization Schedules? Make a more detailed analysis of competition between transactions. 123) How is the transaction when the DBMS is based on a recovery REDO? Transactions already completed before the failure 124) How is the transaction when the DBMS is a recovery based on UNDO? Transactions initiated put unfinished 125) The checkpoint records are stored in what area of the DBMS? Log 127) What is the most widely used technique to deal with the competition? block 128) What are the types of locks (lock) are there? Shared and exclusive 129) What type of problem can occur when we use the lock (lock)? may have cases occurring deadlock 130) What is a serial execution? is one in which transactions are executed one at a time, in any sequence. 131) SQL does not allow a way of setting explicit lock (lock). How the DBMS implements the lock (lock)? SERIALIZABLE, REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED 134) Quote 1 type of technique pessimistic concurrency control. block 136) all or part of the table table (linked to the data) has a lock status. What are these stats? Released – block shared and exclusive lock 137) What kind of operation takes place in a shared lock? reading a number of transactions using this block. 138) What types of transactions take place in a exclusive lock? reading + update, a transaction uses this block. 139) How many transactions are linked to a shared lock? several 140) How many transactions are linked to an exclusive lock? 1 141) The technique is validated by 3 phases, what are these phases? Reading, writing and validation 142) summon 2 level lock that can be found in the DBMS. Registration and page 143) The concept of recovery based on log name 2 techniques that may be used. Immediate change in the db, modification of the delayed bd 144) What is the type of technique used for recovery based on shadow pages? Technical NO-UNDO/NO-REDO (failure and transaction system 145) Relating to Buffer Management would like a synonym for block. page 146) Quote 2 types of buffers that the DBMS can manage. Data management for transaction processing and “proc.Log 150) for Buffer Management which means NOT FORCE? Block maintains data updated by a transaction that are not immediately written to the DBMS when this transaction suffers commit. 151) What does the DBMS security? means protecting data from unauthorized users 152) Security must be maintained in which tables? the table of system catalog 153) Why does the DBMS security today is stronger? because the Internet and e-commerce 154) Quote 3 security problems. Operational problems, political issues of the company, physical controls 155) summon the 2 types of approach to data security. Control discriminatory control mandatory 156) What is Discriminatory checks? The User shall have the right (privilege) to access each object. 157) What is Mandatory Control? Each data object is marked with a certain level of classification and each User receives a certain level of release. 158) usually occurs as the authentication of a User to access the DBMS? occurs via an ID and a password. 159) When we use the audit in a DBMS? can be used if you suspect that are occurring unauthorized access to the database, because there can be inaccurate. 160) Quote 3 information that can be audited. Image before, after image, date and time of operation. 161) Typically encryption is used that way in DBMS? an encryption key to accomplish the encryption of data at the time of recording and when the reading is used the same key to decrypt the data. 162) can be used as a view (vision)? Can be used as a form of security by hiding certain columns or records determined to n users. 165) What is an integrity constraint? is a Boolean expression that is associated with a database and must be evaluated at all times to TRUE. 167) What will happen if the integrity constraint is violated? will be shown an error message 168) What is trigger? is a procedure that is triggered to be a DML operation (Insert, Delete, Update), in which one can check the integrity of certain columns in certain tables. 169) The integrity constraints are stored in which tables? are made to the data (in columns) 170) The integrity constraints can be implemented in other models of the database? Yes / No. If the answer is Yes cite 2 models database. Yes, multimedia egeografico.