Database Final Exam

1. (Chapter 06) Describe a relative DBMS (RDBMS), its underlying entropy model, data storage structures, and appearance of establishing data alliances a. A relational DBMS (or RDMBS) is a data management system that implements a relational data model, one where data be stored in a collection of tables and the data relationships atomic number 18 correspond by common valves, not links. Pg. 247 b. Data ar stored in a collection of tables and the data relationships be represented by common values not links.String CARACTER (CHAR)CHARACTER VARYING (VARCHAR or VARCHAR2)BINARY LARGE OBJECT (BLOB) Stores mountain chain values containing any character in a character solidifying. CHAR is defined to be a fixed length. Stores string values containing any characters in a character set but of determinable 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 take in numbers with a defined precision and scale.Stores exact numbers with a predefined precision and scale of zip fastener Temporal TIMESTAMPTIMESTAMP WITH LOCAL TIME ZON Stores a moment an event occurs, using a definable fraction-of-a-second precision. think of adjusted to the users 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 blameless the activity An ER Model d. The power of the RDBMS is realized through. The relationship existing between the tables. The relationships atomic number 18 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. finishing portability d. Application longevity e. Reduce dependence on a single vend or f. Cross-system communication 3. (Chapter 07) Define distributively of the following key terms a. Dynamic SQL Specific SQL code generated on the fly while an use is processing. Pg. 326 b. Correlated sub interrogative sentence Use the result of the outer query to determine the processing of the inner query. Pg. 303 c. Embedded SQL Hard-coded SQL statements include in a program written in some other language, such as C or Java.Pg. 323 d. Procedure A collection of procedural and SQL statements that are assigned a unique name inside the schema and stored in the database. Pg. 323 e. Join A relational operation that causes two tables with a common mankind to be combined into a single table or view. Pg. 290 f. Equi-join A join in which the connexion condition is establish 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 s ame 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 solitary(prenominal) input parameters. Pg323 j. Persistent Stored Modules (SQL/PSM) Extensions defined in SQL1999 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 var.ID 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 profits/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 ar chitecture provides all the TopLink features in a single session type, thereby qualification 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. severalize 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 several(a) 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. Deriv ed 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 shelter data storage medium d. Time-variance Timeliness of data and access terms * 9. (Chapter 09) What are the five claimed limitations of self-reliant data marts? Pg. 384 . A separate ETL process is developed for each data mart, which can yield pricey 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 light enterprise-wide view of data concerning important subjects such as customers, suppliers, and products. c. There is no capability to drill smooth into greater detail or into related facts in other data marts or a divided up 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 natural selection and load steps. e. if there is an attempt to make the separate data marts consistent, the cost to do so is rather 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 brass instrument program? Pg. 435 . Sponsorship from both senior management and business units b. A data steward manager to support, train, and direct 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 s et 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. write in code 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 darling prompts/examples can assist end users in proper data entranceway). d. Follow good user interface design principles that create consistent screen layouts, easy to follow n avigation 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 funny data are entered (e. g. , T for gender), immediate and understandable feedback should be given to the operator, questioning the validity of the data.