ISDS 402 Final Exam questions well answered to pass, Exams of Nursing

Download ISDS 402 Final Exam questions well answered to pass and more Exams Nursing in PDF only on Docsity!

ISDS 402 Final Exam

DDL - correct answer ✔✔Data Definition Language CREATE, ALTER, DROP DML - correct answer ✔✔Data manipulation language refers to the four basic operations that can and must be performed on data stored in any DBMS: data retrieval, data update, insertion of new records, and deletion of existing records. When you drop a table in SQL - correct answer ✔✔you also drop all of its data when removing tables always start with - correct answer ✔✔many side, due to referential integrity SQL ALTER TABLE statement - correct answer ✔✔The SQL command used to change the structure of a database table -DDL Uses of DML - correct answer ✔✔query database and modify data in the tables Method 1 of inserting data - correct answer ✔✔Use both names and values of columns Method 2 of inserting data - correct answer ✔✔use only columns values but not column names SELECT clause - correct answer ✔✔specifies which columns are to be listed in the query results FROM clause - correct answer ✔✔Specifies the table containing the column listed in the select clause WHERE clause - correct answer ✔✔specifies which rows are to be listed in the query results

symbol for all - correct answer ✔✔* star SELECT DISTINCT - correct answer ✔✔specifies that the statement is going to be a query that returns unique values in the specified column(s). ORDER BY clause - correct answer ✔✔- It simply takes the result of a SQL query and orders them by one or more specified attributes.

• automatically orders by ASC, can specify DESC 3 options for SQL WHERE clause - correct answer ✔✔-compound clauses with logical operators (AND, OR, NOT) -Ranges using IN or NOT IN or BETWEEN -Wildcards SQL UPDATE statement - correct answer ✔✔used to make changes to existing records within a table UPDATE usedcars SET price=29995. WHERE year='2014' SQL DELETE statement - correct answer ✔✔used to remove records from a table DELETE FROM usedcars WHERE year='2014' SQL Wildcard underscore (___) - correct answer ✔✔Represents single unspecified character (including a space) in a specific position in the character string SQL Wildcard Percent sign (%) - correct answer ✔✔represents any sequence of contiguous unspecified characters (including spaces) in a specific position in character string TRIM Function - correct answer ✔✔removes leading and trailing spaces in a text string, but spaces between words are not removed

To include or exclude rows that contain NULL values: - correct answer ✔✔use IS NULL or IS NOT NULL after WHERE GROUP BY clause - correct answer ✔✔groups rows by common values HAVING clause - correct answer ✔✔to filter the results grouped by a GROUP BY clause. Can only reference aggregate functions or column that are also specified in the GROUP BY clause. Two techniques for querying data from multiple tables - correct answer ✔✔SQL Subquery SQL Join Subquery - correct answer ✔✔An SQL SELECT statement that is inside another select or action query. -nested query -only columns of top level table from outermost query can be displayed in the query results -columns of other tables from inner queries cannot be displayed SQL Joins - correct answer ✔✔Used to combine rows from two or more tables, based on a common field between them Implicit Join - correct answer ✔✔the SQL JOIN operator is not used as part of the SQL statement Explicit Join - correct answer ✔✔the SQL JOIN operator is used as part of the SQL statement JOIN ON - correct answer ✔✔If you wish to create an inner join, but the two tables do not have a commonly named attribute Difference between Join and Subquery - correct answer ✔✔-Subquery can only be used to retrieve data from top table -A join can be used to obtain data from any tables, including top table of subquery

Three types of outer joins - correct answer ✔✔Left, Right, Full Left/Right Join - correct answer ✔✔Designate the table whose unmatched rows should be kept in the returned results in addition to the matched rows FULL OUTER JOIN - correct answer ✔✔A join in which all rows from both tables will be included regardless of whether they match rows from the other table CREATE VIEW - correct answer ✔✔A SQL command that creates a logical, "virtual" table. The view can be treated as a real table. -CANNOT include ORDER BY clause Business Intelligence Systems - correct answer ✔✔information systems that process operational, social, and other data to identify patterns, relationships, and trends for use by business professionals and other knowledge workers Reporting - correct answer ✔✔Filter, sorting, grouping and making simple calculations -can be performed using standard SQL -Summarize current status of Business Activities Data Mining - correct answer ✔✔the process of analyzing data to extract information not offered by the raw data alone 3 sources of data BI - correct answer ✔✔-Operational databases -From extracts from the operational database -Data purchased from data vendors Why operational databases are not good for BI - correct answer ✔✔-querying operational database places burden on DBMS -Creation and maintenance require programs, facilities and expertise not available from operations- focused databases

-Operational data have accuracy and consistency problems that limit their use Problems with operational data - correct answer ✔✔dirty data, missing values, inconsistent data, data not integrated, wrong granularity, too much data Data warehouse - correct answer ✔✔A logical collection of information - gathered from many different operational databases - that supports business analysis activities and decision-making tasks ETL - correct answer ✔✔Extract, Transform, Load Data warehouse attributes - correct answer ✔✔-usually denormalized -data never inserted, updated or deleted by user (only admin) -can vary by scales and scope Components of a Data Warehouse - correct answer ✔✔User Data warehouse programs Data warehouse DBMS Data data mart - correct answer ✔✔a data collection, smaller than the data warehouse, that addresses the needs of a particular department or functional area of the business -subset of data warehouse info dimensional database - correct answer ✔✔(1) Used for unstructured analytical data processing (2) Current and historical data are used (3) Data are loaded and updated systematically, not by users. Star Schema - correct answer ✔✔Has a fact table at center of star and dimension tables radiating out from center

fact table - correct answer ✔✔keep track of measures (quantitative/factual columns) that can be examined from dimensions dimension tables - correct answer ✔✔stores multiple dimensions to examine the measures in the fact table -MUST have time dimensions Dimension database is also - correct answer ✔✔a relational database Fact table is also - correct answer ✔✔an association between the dimension tables with additional measures in it -relationship between dimension table and fact table is 1:M -PK of fact table is composite PK -purpose of dimension database is to facilitate queries and analytics Extended Star Schema - correct answer ✔✔multiple star schemas used in a dimensional database, many fact tables RFM analysis - correct answer ✔✔a way of analyzing and ranking customers according to their purchasing patterns Recency, Frequency, Money (dollar amount of orders) data administration - correct answer ✔✔refers to an organization-wide function that is in charge of developing data policies and enforcing data standards database administration - correct answer ✔✔Refers to the more technical and operational aspects of managing data, including physical database design and maintenance. database administrator - correct answer ✔✔refers to the person in charge of a database and facilitates the development and use of it

Concurrency Control - correct answer ✔✔A method of dealing with a situation in which two or more users or applications need to access the same record at the same time. -needs locking database at various levels high levels of concurrency control - correct answer ✔✔technically easier and consume less resource, but slow down throughput Low levels of concurrency control - correct answer ✔✔technically more difficult and consume more resource but they increase database throughput atomic transaction - correct answer ✔✔a series of actions are taken on a database such that all of them are performed successfully or none of them are performed at all (ACID) successful transaction changes database from - correct answer ✔✔one consistent state to another -all data integrity constraints satisfied -any database transaction must change affected data only in allowed ways database transactions formed by - correct answer ✔✔two ore more database requests -equivalent of a single SQL statement in an application program or transaction Concurrent Transactions - correct answer ✔✔when two transactions are being processed against a database at the same time

• it is possible that one update may overwrite the other Lost updates problem: - correct answer ✔✔user B's update overwrites A's update before A's transaction is finished Inconsistent Read Problem - correct answer ✔✔An unrepeatable read, one that occurs when one user reads data that have been partially updated by another user. resource locking - correct answer ✔✔prevents concurrent processing problems by disallowing sharing by locking data that are retrieved for update

Implicit locks vs explicit locks - correct answer ✔✔-implicit is placed by DBMS -explicit locks placed by command exclusive lock - correct answer ✔✔Locks an item from access of any type. No other transaction can read or change the data shared lock - correct answer ✔✔locks the item from change but not from read; -other transactions can read the item as long as they do not attempt to alter it. database-level lock - correct answer ✔✔entire database is locked table-level lock - correct answer ✔✔Entire table is locked page-level lock - correct answer ✔✔DBMS locks an entire diskpage -most popular row-level lock - correct answer ✔✔Allows concurrent transactions to access different rows of same table or even same page field level lock - correct answer ✔✔allows concurrent transactions to access the same row as long as they require the use of different fields (attributes) within that row. lock granularity tradeoff - correct answer ✔✔more strict controls reduce conflicts but slow down throughput or performance Serializable Transactions - correct answer ✔✔Refer to two transactions that run concurrently and generate results that are consistent with the results that would have occurred if they had run separately -achieved through the two-phase locking protocol

Two-Phase Locking - correct answer ✔✔An approach to locking that is used to manage concurrent update in which there are two phases: a growing phase and a shrinking phase Growing phase - correct answer ✔✔when the transaction continues to request additional locks shrinking phase - correct answer ✔✔when the transaction begins to release the locks deadlocks - correct answer ✔✔occurs when two transactions wait indefinitely for each other to unlock data Preventing deadlock - correct answer ✔✔-Allow users to issue all lock requests at one time. -Require all application programs to lock resources in the same order. Breaking Deadlock - correct answer ✔✔- Almost every DBMS has algorithms for detecting deadlock

• When deadlock occurs, DBMS aborts one of the transactions and rolls back partially completed work Optimistic Locking - correct answer ✔✔The assumption is made that no conflict will occur. -checks whether conflict occured -if no conflict, locks issued, transaction finished -if conflict transaction is repeated until no conflict pessimistic locking - correct answer ✔✔-Assumes that conflict will occur -Locks are issued before transaction is processed, and then locks are released -does not check ACID Transactions - correct answer ✔✔Atomic, Consistent, Isolated, Durable atomic - correct answer ✔✔all of the database actions occur or none of them do

consistent - correct answer ✔✔any given database transaction must change affected data only in allowed ways isolation - correct answer ✔✔multiple transactions can occur concurrently and independently without interference with each other durable - correct answer ✔✔all committed changes permanent database security - correct answer ✔✔ensures that only authorized users can perform authorized activities at authorized times Processing Rights and Responsibilities - correct answer ✔✔create user groups or roles -assign permissions to groups -put individuals into groups -individuals are identified by username and password reprocessing - correct answer ✔✔all activities since the backup was performed are redone -means redo all business events exactly as first time -rarely used for database recovery transaction log - correct answer ✔✔A feature used by the DBMS to keep track of all transaction operations that update the database. The information stored in this log is used by the DBMS for recovery purposes. transaction log stores - correct answer ✔✔-beginning of transaction (START) -type of operation (update, delete, insert) -names of objects being affected -before and after values/images -pointers to previous and next transaction log entries

-ending (COMMIT) Before image - correct answer ✔✔A copy of a record (or page of memory) before it has been modified. after image - correct answer ✔✔A copy of a record (or page of memory) after it has been modified. both images (before and after) refer to copies of database values in the - correct answer ✔✔log file, not physical storage deferred-write technique - correct answer ✔✔- transaction operations do not immediately update the physical database

• only the transaction log is updated
• database is physically updated only with committed transactions Write-Through Method - correct answer ✔✔update log file and database at the same time -when log file committed, physical storage updated too Rollforward - correct answer ✔✔restore database using saved data and valid transactions since the last save -under deferred write method Rollback - correct answer ✔✔undo the erroneous changes made to the database and reprocess valid transactions -under write through method distributed database - correct answer ✔✔A logically related database that is stored in two or more physically independent sites. Types of Distribution - correct answer ✔✔-partitioned (split database into smaller subsets and store on multiple computers) -replicated (storing copies of same database on multiple computers)

horizontal partitioning - correct answer ✔✔Distribution of relation into subsets of rows Vertical Partitioning - correct answer ✔✔division of a relation into subsets of columns mutual consistency rule - correct answer ✔✔A data replication rule that requires all copies of data fragments to be identical. fully replicated database - correct answer ✔✔Stores multiple copies of each database fragment at multiple sites partially replicated database - correct answer ✔✔A distributed database in which copies of only some database fragments are stored at multiple sites. unreplicated database - correct answer ✔✔Stores each database fragment at a single site -no duplicate database fragments