Database Final Exam

1. (Chapter 06) Describe a comparative entropybase management system (RDBMS), its underlying selective information model, selective information 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, unmatchable where data argon stored in a collection of control boards and the data relationships argon represented by common valves, not links. Pg. 247 b. Data atomic number 18 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 inured. CHAR is defined to be a dogged 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 o verly has CLOB and NCLOB, as well as BFILE for storing unstructured data outside the database. ) repress NUMERICINTERGER(INT) Stores exact total with a defined precision and scale.Stores exact numbers with a predefined precision and scale of zero Temporal sequenceSTAMPTIMESTAMP WITH LOCAL TIME ZON Stores a moment an event occurs, using a definable fraction-of-a-second precision. time value adjusted to the users session time z hotshot (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 natural action An ER Model d. The power of the RDBMS is realized through. The relationship real 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 sixsome potential benefits of achieving an SQL standard? Pg. 245-246 a. Reduce training cost b. Productivity c. practise program portab ility d. Application longevity e. Reduce dependence on a case-by-case vendor f. Cross-system communication 3. (Chapter 07) Define each of the following key basis a. Dynamic SQL Specific SQL code generated on the fly while an application is touch on. Pg. 326 b. Correlated subquery determination the result of the outer query to determine the processing of the inner query. Pg. 303 c. Embedded SQL Hard-coded SQL statements accommodated in a program compose in an opposite language, such as C or Java.Pg. 323 d. map A collection of procedural and SQL statements that are assigned a curious 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 unite 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 thither are times when a join req uires 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 besides included in the result table. Pg. 293 i. Function A stored subroutine that returns one value and has nevertheless 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 CourseID and CourseName for all courses in the Course Table where the CourseID has an philosophical system 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 objective is it s simplicity. The TopLink database session that builds the two-tier architecture provides all the TopLink features in a single session type, thereby fashioning the two-tier architecture simple to build and use. A disadvantage of the two-tier architecture is The to the highest degree distinguished limitation of the two-tier architecture is that it is not scalable, ecause each client requires its take in database session. * 6. (Chapter 08) What are six common tempos to access databases? Pg. 340 * a. Identify and chronicle a database driver b. Open a connection to a database c. operate a query against the database d. Process the results of a query e. Repeat musical note 3-4 as necessary f. Close the connection to the database * * * 7. (Chapter 09) What are the tercet study 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 d ata 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 marketplaces. * * 8. (Chapter 09) What are the 4 characteristics of a data warehouse? a. Subject Orientation Data unionized 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 discern ETL process is certain for each data mart, which place 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 solve enterprise-wide view of data concerning important subjects such as customers, suppliers, and products. c. There is no capability to drill mass into greater detail or into related fac ts in other data marts or a shared data repository, so analysis is limited, or at best very difficult. . Scaling costs are luxuriant 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 synopsis based b. Multidimensional OLAP (MOLAP) Cube based c. Hybrid OLAP (HOLAP) * 11. (Chapter 10) What are the quatern key components of a data governance program? Pg. 435 . Sponsorship from both quaternaryth-year management and business units b. A data steward manager to support, train, and form 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 st eward 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 changed 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 i nterface 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.