Database Final Exam

1. (Chapter 06): Describe a relational DBMS (RDBMS), its underlying data model, data storage structures, and manner of establishing data relationships: a. A relational DBMS (or RDMBS) is a data management system that implements a relational data model, one where data are stored in a collection of tables and the data relationships are represented by common valves, not links. Pg. 247 b. Data are stored in a collection of tables and the data relationships are represented by common values not links.

String| CARACTER (CHAR)CHARACTER VARYING (VARCHAR or VARCHAR2)BINARY LARGE OBJECT (BLOB)| Stores string values containing any character in a character set. CHAR is defined to be a fixed length. Stores string values containing any characters in a character set but of definable variable length. Stores binary string values in hexadecimal format. BLOB is defined to be a variable length. (Oracle also has CLOB and NCLOB, as well as BFILE for storing unstructured data outside the database. )| Number| NUMERICINTERGER(INT)| Stores exact numbers with a defined precision and scale.

Stores exact numbers with a predefined precision and scale of zero| Temporal| TIMESTAMPTIMESTAMP WITH LOCAL TIME ZON| Stores a moment an event occurs, using a definable fraction-of-a-second precision. Value adjusted to the user’s session time zone (available in Oracle and MySQL)| Boolean| BOOLEAN| Stores truth values: TRUE, FALSE or UNKNOWN| c. The relational data model assumes that you have completed the activity ‘An ER Model d. The power of the RDBMS is realized through…. The relationship existing between the tables. The relationships are established by including common column or columns in every table where a relationship is needed. . (Chapter 06): What are six potential benefits of achieving an SQL standard? Pg. 245-246 a. Reduce training cost b. Productivity c. Application portability d. Application longevity e. Reduce dependence on a single vendor f. Cross-system communication 3. (Chapter 07): Define each of the following key terms: a. Dynamic SQL: Specific SQL code generated on the fly while an application is processing. Pg. 326 b. Correlated subquery: Use the result of the outer query to determine the processing of the inner query. Pg. 303 c. Embedded SQL: Hard-coded SQL statements included in a program written in another language, such as C or Java.

Pg. 323 d. Procedure: A collection of procedural and SQL statements that are assigned a unique name within the schema and stored in the database. Pg. 323 e. Join: A relational operation that causes two tables with a common domain to be combined into a single table or view. Pg. 290 f. Equi-join: A join in which the joining condition is based on equality between vales in the common columns. Common columns appear (redundantly) in the result table. P 291 g. Self-join: There are times when a join requires matching rows in a table with other rows in that same table – that is, joining table with itself. Pg. 297 . Outer join: A join in which rows that do not have matching values in common columns are nevertheless included in the result table. Pg. 293 i. Function: A stored subroutine that returns one value and has only input parameters. Pg323 j. Persistent Stored Modules (SQL/PSM): Extensions defined in SQL:1999 that include the capability to create and drop modules of code stored in the database schema across user sessions. Pg. 319 4. (Chapter 07): Write the SQL Query needed to: Display CourseID and CourseName for all courses in the Course Table where the CourseID has an ‘ISM’ prefix: Query: SELECT [CourseTable].

CourseID, [CourseTable]. CourseName FROM CourseTable WHERE((([CourseTable]. CourseID)=”ISM”)); 5. (Chapter 08): What are the advantages/disadvantages of two-tier architectures? Pg. 339 An advantage of two-tier architecture The advantage of the two-tier design is its simplicity. The TopLink database session that builds the two-tier architecture provides all the TopLink features in a single session type, thereby making the two-tier architecture simple to build and use. A disadvantage of the two-tier architecture is The most important limitation of the two-tier architecture is that it is not scalable, ecause each client requires its own database session. * 6. (Chapter 08): What are six common steps to access databases? Pg. 340 * a. Identify and register a database driver b. Open a connection to a database c. Execute a query against the database d. Process the results of a query e. Repeat step 3-4 as necessary f. Close the connection to the database * * * 7. (Chapter 09): What are the three major components of Data Warehouse architecture? Pg. 389 a. Operational data are stored in the various operational systems of record throughout the organization (and sometimes in external systems). . Reconciled data are the type of data stored in the enterprise data warehouse and an operational data stored. c. Derived data are the type of data stored in each of the data marts. * * 8. (Chapter 09): What are the four characteristics of a data warehouse? a. Subject Orientation: Data organized by subject b. Integration: Consistency of defining parameters c. Non-volatility: Stable data storage medium d. Time-variance: Timeliness of data and access terms * 9. (Chapter 09): What are the five claimed limitations of independent data marts? Pg. 384 . A separate ETL process is developed for each data mart, which can yield costly redundant data and processing efforts. b. Data marts may not be consistent with one another because they are often developed with different technologies, and thus they may not provide a clear enterprise-wide view of data concerning important subjects such as customers, suppliers, and products. c. There is no capability to drill down into greater detail or into related facts in other data marts or a shared data repository, so analysis is limited, or at best very difficult. . Scaling costs are excessive because every new application that creates a separate data mart repeats all the extract and load steps. e. if there is an attempt to make the separate data marts consistent, the cost to do so is quite high. * 10. (Chapter 09): What are the three types of operations that can be easily performed with OLAP tools? Pg. 214-215 a. Relational OLAP (ROLAP) –Star Schema based b. Multidimensional OLAP (MOLAP) –Cube based c. Hybrid OLAP (HOLAP) * 11. (Chapter 10): What are the four key components of a data governance program? Pg. 435 . Sponsorship from both senior management and business units b. A data steward manager to support, train, and coordinate the data stewards c. Data stewards for different business units, data subjects, source systems, or combinations of these elements d. A governance committee, headed by one person, but composed of data steward managers, executives and senior vice presidents, IT leadership and others business leaders, to set strategic goals, coordinate activities, and provide guidelines and standards for all data management activities. * * 12. Chapter 10): What are the four ways that data capture processes can be improved to improve data quality? According to Inmon (2004), there are several actions that can be taken at the original data capture step: Pg. 441 a. Enter as much of the data as possible via automatic, not human, means (e. g. , from data stored in a smart card or pulled from a database, such as retrieving current values for addresses, account numbers, and other personal characteristics). b. Where data must be entered manually, ensure that it is selected from preset options (e. . , drop-down menus of selections pulled from the database), if possible. c. Use trained operators when possible (help systems and good prompts/examples can assist end users in proper data entry). d. Follow good user interface design principles that create consistent screen layouts, easy to follow navigation paths, clear data entry masks and formats (which can be defined in DDL), minimal use of obscure codes can be looked up and displayed from the database, not in the application programs), etc. . Immediately check entered data for quality against data in the database, so use triggers and user-defined procedures liberally to make sure that only high-quality data enter the database; wen questionable data are entered (e. g. , “T for gender), immediate and understandable feedback should be given to the operator, questioning the validity of the data.