1 / 21

Foundations of Relational Implementation (1)

Foundations of Relational Implementation (1). IS 240 – Database Management Lecture #13 – 2004-04-01 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu. Topics. Review of Relational Terminology Define DB Using DDL Allocate Disk Space Plan for Maintenance Loading the DB

tori
Download Presentation

Foundations of Relational Implementation (1)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Foundations of Relational Implementation (1) IS 240 – Database Management Lecture #13 – 2004-04-01Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu

  2. Topics • Review of Relational Terminology • Define DB Using DDL • Allocate Disk Space • Plan for Maintenance • Loading the DB • DML Interfaces

  3. Review of Relational Terminology • Relation / table / file / dataset • Tuple / row / record / entry • Attribute / column / field • Domain • Uniqueness

  4. Key – Two Meanings • Design: unique identifier(s) of row • Implementation: performance tool • Logical key: unique identifier • Physical key: column with an index for faster performance

  5. Indexes • Kroenke reserves word “key” for logical key • Uses word “index” for physical key • Indexes are useful • Find rows fast • Retrieve rows in a sorted order • Enforce uniqueness

  6. Example of Indexes in a Database * 1 block • Compare # I/Os required to read all the order #9890 • Without index • With index on OrdNo • With packing* on index

  7. Implementing RDB • Having designed a DB using the relational model, there’s no problem in defining a DB using today’s DBMS products • Use the Data Definition Language (DDL) of the DBMS • Formalized language for describing what we need • Varies a bit from DBMS to DBMS • Easy to learn once you’re mastered any one of them

  8. Define DB Using DDL • Text-file (schema) DDL • Specific syntax • Name all the elements • Name all the tables • Name all the indexes and links • Graphical systems • MS-Access uses tables and also graphics • Easy to define details using text • Structures can be linked using graphics • Menus provide guidance, limits

  9. Allocate Disk Space • PC-based products generally allocate space dynamically • DB grows as needed • Don’t have to worry about defining maximum size • Mainframe / server DBs generally require size definitions • Need to reserve space in advance • Performance issues become important • Multiple disk drives or controllers • Try to avoid thrashing of disk heads

  10. DB Performance Issues • Computer system performance always depends on 5 factors • Access to & speed of CPU • Access to & speed of RAM • Amount & speed of disk I/O • Communications speed • Application design • DB performance often affected by • Presence / absence of indexes • Good / bad packing of records • Locking strategies (coming in later lecture)

  11. Plan for Maintenance • Databases often central components of production systems • Plan for • Backups • Diagnostics to find corrupted data • Child records without parents • Record counters that are incorrect • Pointers to non-existent records • Data that violate business rules • Archiving inactive records • Compaction of data files

  12. Loading the DB • Have to get data somewhere • Most DBs get historical data • Older DBs • Special programs written to read old DB, write into new DB with correct format • Often get exceptions – write to exception file • May have large-scale data entry from paper records – need careful verification • Some critical systems use dual data entry • Compare records

  13. Types of Data Manipulation Languages (DMLs) • Relational algebra • Relational calculus • Transform-oriented languages • Graphical interfaces

  14. Relational Algebra • Operators function on records • Union / intersection etc. • Much like set theory • Procedural language • Step-by-step changes in collections of data • Not used in commercial DBs • Useful to understand as preparation for SQL • Will study in next lesson

  15. Relational Calculus • Non-procedural theoretical framework for dealing with relations • E.g., De Morgan's law, “The complement of a union is equal to the union of the complements." • Learned in mathematics and advanced theory of programming and data structures • Not used in commercial DB processing • Not part of this course

  16. Transform-Oriented Languages • Non-procedural • Change relations into a single relation • Thus define conditions for selecting records and end up with a set of records that satisfy the conditions • SQL is most important example of this kind of language • Will study in detail in this course

  17. Graphical Interfaces (1) • Query-by-Example (QBE) • User sees place to enter specific values or ranges • Generates SQL without bothering user

  18. Graphical Interfaces (2) • Query-by-Form (QBF) • Allows more complex queries • Still generates underlying SQL

  19. DML Interfaces to the DBMS • Forms (e.g., in MS-Access) • Query languages (e.g., SQL) • Stored procedures (incl. triggers) • API = application program interface

  20. Homework • For Thu 8 April 2004: REQUIRED • Reread chapter 8, pp. 211-221 thoroughly using Read-Recite-Review phases of SQ3R • Do exercises 8.1-8.17 for 34 points • For next Tuesday: • Read onwards in chapter 8 from page 221 through 231 • Continue working on MS-Access lab work

  21. DISCUSSION

More Related