Chapter 3 An Introduction to Relational Databases Prof

Chapter 3 An Introduction to Relational Databases Prof. Yin-Fu Huang CSIE, NYUST

3. 2 An Informal Look at the Relational Model n n Structural aspect Integrity aspect Manipulative aspect Example (See Fig. 3. 1 & 3. 2) Ø Primary key Ø Foreign key Advanced Database System 2

Example (Fig. 3. 2) n Points from Fig. 3. 2: a. the closure property of relational systems b. set-at-a-time Advanced Database System 3

Example (Fig. 3. 1) n Points from Fig. 3. 1: a. Tables are the logical structure, not the physical structure. b. The entire information content of the database is represented in one and only one way, namely as explicit values. Advanced Database System 4

3. 3 Relations and Relvars n Relation ? ⇒ a mathematical term for a table (set theory and predicate logic) n Relation model ⇒ E. F. Codd (1969 -1970) n Delete Emp Where Emp#=‘E 4’ (See Fig. 3. 3) n A relation variable vs. a relation per se Advanced Database System 5

3. 4 What Relations Mean n Two parts: the heading & the body (See Fig. 3. 4) n Thinking about relations: a. predicate b. true proposition Advanced Database System 6

3. 5 Optimization n Relational languages are often said to be nonprocedural, on the grounds that users specify what, not how. n Procedurality and nonprocedurality are not absolute. n Optimizer: automatic navigation (See Fig. 3. 5) ⇒ an efficient way Ø Example: Result: =(Emp Where Emp#=‘E 4’) {Salary}; a. a physical sequential scan of relvar Emp b. an index on the Emp# Advanced Database System 7

Automatic vs. manual navigation (Fig. 3. 5) Advanced Database System 8

3. 5 Optimization (Cont. ) n The considerations: a. Which relvars are referenced in the request b. How big those relvars currently are c. What indexes exist d. How selective those indexes are e. How the data is physically clustered on the disk f. What relational operations are involved n Automatic navigation ⇒ data independence Advanced Database System 9

3. 6 The Catalog n Users can interrogate the catalog in exactly the same way they interrogate their own data. n Example (See Fig. 3. 6) (Column Where Tabname=‘Dept’) {Colname} Advanced Database System 10

3. 7 Base Relvars and Views n Base relvars vs. derived relvars n A view can be thought of, loosely, as a derived relvar. n Example: Create View Top. Emp As (Emp Where Salary > 33 K) {Emp#, Ename, Salary}; n The view defining expression is not evaluated but is merely “remembered” by the system in some way. ⇒ a virtual relvar n A window into the base relvar n Any changes: view ⇔ base relvars Ø Example: (Top. Emp Where Salary < 42 K) {Emp#, Salary} Database System (Emp Where Salary. Advanced > 33 K And Salary < 42 K) {Emp#, Salary} 11

3. 8 Transactions n A transaction is a logical unit of work. n Operations: Begin Transaction, Commit, Rollback n Points arising: a. atomic b. durable c. isolated d. serializable Advanced Database System 12

3. 9 The Suppliers and Parts Database n Example (See Fig. 3. 8 & 3. 9) n Entity and Relationship (a special case of an entity) Advanced Database System 13

The suppliers and parts database (Fig. 3. 9) Advanced Database System 14

The End. Advanced Database System 15