Database Final Exam

Only available on StudyMode
  • Download(s) : 265
  • Published : April 15, 2013
Open Document
Text Preview
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.

2. (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

h. 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...
tracking img