1. (Chapter 06): Describe a relational DBMS (RDBMS), its underlying data model, data storage structures, and manner of establishing data relationships:
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
Data are stored in a collection of tables and the data relationships are represented by common values not links.
| 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.)
| Stores exact numbers with a defined precision and scale.Stores exact numbers with a predefined precision and scale of zero
| 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)
| Stores truth values: TRUE, FALSE or UNKNOWN
The relational data model assumes that you have completed the activity 'An ER Model
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?
Reduce training cost
Reduce dependence on a single vendor
3. (Chapter 07): Define each of the following key terms:
Dynamic SQL: Specific SQL code generated on the fly while an application is processing. Pg.326
Correlated subquery: Use the result of the outer query to determine the processing of the inner query. Pg. 303
Embedded SQL: Hard-coded SQL statements included in a program written in another language, such as C or Java. Pg. 323
Procedure: A collection of procedural and SQL statements that are assigned a unique name within the schema and stored in the database. Pg.323
Join: A relational operation that causes two tables with a common domain to be combined into a single table or view. Pg.290
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
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
Function: A stored subroutine that returns one value and has only input parameters. Pg323
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:
SELECT [CourseTable].CourseID, [CourseTable].CourseName
5. (Chapter 08): What are the advantages/disadvantages of two-tier...
Please join StudyMode to read the full document