1 / 54

BCIS 4620 INTRODUCTION TO DATABASE PROCESSING

BCIS 4620 INTRODUCTION TO DATABASE PROCESSING. Spring 2010 Dr. Jack D. Becker. CLASS 1-Overview of File Organization & Database Processing. Welcome Class Rolls Review Syllabus Miscellaneous Course Announcements CDIT Student Night (Fall usually) Kroenke & Auer 11 th Edition

tudor
Download Presentation

BCIS 4620 INTRODUCTION TO DATABASE PROCESSING

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. BCIS 4620INTRODUCTION TO DATABASE PROCESSING Spring 2010 Dr. Jack D. Becker

  2. CLASS 1-Overview of File Organization & Database Processing • Welcome • Class Rolls • Review Syllabus • Miscellaneous Course Announcements • CDIT Student Night (Fall usually) • Kroenke & Auer 11th Edition • Class Notes will be distributed as needed (FREE) • MicroFocus NetExpress 5.0 & DB2 Option [IBM DB2] • MicroFocus NetExpress 5.1 w/.NET Option-Vista 2007 • http://microfocus.com/Resources/Communities/Academic/shop/index.asp • Course Overview and Objectives

  3. MicroFocus Net Express 5.0/5.1 UE General Information: HINTS & TIPS • Self-Install Instructions in Start file (or Run) x:\setup.exe /r (where x is the CD drive) • Must have MS Internet Explorer 6.0 to install software. Must have Adobe Acrobat 5.0 or higher to read documentation • MS IE 6.0 • Adobe Acrobat 5.0 • If necessary paste the following address in IE: File///X:/autorun/html/start.htm (where X is CD drive) • Do not install 5.1 .NET option, unless you have Visual Studio installed already and MS Vista/2007 O/S • Install all options, especially SQL/DB2 • Except UNIX & .NET

  4. Database Modeling Software TableDesigner™ • Free version at the following website: www.pearsonhighered.com/kroenke/ • Also, downloadable on course website • Go to textbook website for TableDesigner download info. • TableDesigner is a Semantic Object database modeling tool • alternative to ER Diagrams [ERDs]

  5. Spring 2010 [DRAFT]BCIS 4620 Lab Tutor Spring 2010 Note: Tutor in Room 333 or 151 were noted

  6. Chapter 1 KroenkeINTRODUCTION TO DATABASE PROCESSING • Mainframes to Micros (IMS, TOTAL, ORACLE, SQL/DS, DB2, ACCESS) • Client/Server & Distributed Processing • File Processing, Hierarchical DB, Relational DB • SQL (Structured Query Language) Standard • Legacy Systems – “YR2K” remembered • MICROSOFT ACCESS a NEW “standard”

  7. CHAPTER 1Introduction to DB Processing • Information (reports) vs. Data • Hierarchy of Data Elements [next slide] • File Processing (COBOL) vs. • Database Processing (Access, SQL/DS, etc.)

  8. Hierarchy of Data Elements Traditional DB Relational DBMS Database Tables Rows Columns Characters or bytes Bits

  9. File Processing Systems • Better than manual, but have limitations • Data is separate and isolated: • Programs (COBOL) which must process data in multiple files are complex (JCL) • Data redundancy common; Leads to data integrity problems • Application programs (APs) are difficult to maintain and data dependent -- • See Music Instrumental Rental Co. Example

  10. File Processing System

  11. Key Characteristics of a Database Processing System [DBPS] • Integrated Data • Only the Database Management System (DBMS) needs to know the physical aspects of the data • Reduced Data Duplication • Program/Data Independence • Easier Representation of the User’s View of the Data or System • See DB Bank System Example

  12. Database Systems [Figs. 1.4 & 1.6]

  13. Database Definition • First note the three (3) Key DB Components: • Data in the Database • DBMS software • Especially the Data Dictionary • (Meta-data => Data about data) • DB Application Programs

  14. Database Definition continued Kroenke defines a DB as: A self-describing collection of integrated records (data dictionary or meta-data); which provides a representation (the users’ model, including applications) of an organization (business system).

  15. Figure 1.9 Typical Metadata Tables Data about Tables Data about Data

  16. Your Evolution of Database Systems

  17. Fig 1.3 Example Database Uses

  18. Types of Database Systems • 3-Tier Architecture (Client/Server): • Tier 1: Organizational databases (EWIM) • Tier 2: Workgroup databases (Servers) • Tier 3: Personal Databases (PCs) • Internet databases (WWW; XML; SQL Server) • Multimedia (ODBMS)

  19. Database Systems --A Brief History (See also Fig 1-25) • 1960’s -- Big Transactional Processing Systems; File Processing systems (IBM, Burroughs, UNIVAC, RCA, Sperry, Amdahl); • COBOL reign begins • 1970’s -- First Commercial Mainframe Database Systems: Cincon’s TOTAL, IBMs IMS, PIC O/S • Conference on Database Systems Language – DBTG (Database Task Group) • A.k.a., CODASYL Standards • 1970 -- Relational Model Proposed (Codd & Date, at IBM). Note: After CODASYL model proposed

  20. Database Systems --A Brief History (continued) • 1970’s -- First Commercial Relational DBMS: ORACLE (1978), DB2 (SQL/DS 1981), ADABASE • 1980’s -- PC Databases (dBase II (1979); RBase, Paradox, Lotus 1-2-3, …) • 1990’s – Access Released (1991); Object-oriented DBMS emerge (multimedia databases) • Late1990’s – Internet/Web-oriented databases; and distributed databases; XML applied to DB; JAVA apps. • 2000’s – Multi-platform, distributed, enterprise databases (mixed database types); Wireless utilities; WWWW; ASP; Search for standards …?

  21. Brief History of Database Processing Systems(Figure 1-25;Kroenke & Auer)

  22. Systems Analyst Openings In TXCareerBuilder.Com What is the Story?

  23. Job Openings on CareerBuilder.ComTexas Job Openings [BI] • What is the Story? • Leading Indicator?

  24. Relational Databases—Think Tables! • 1970 E.F. Codd proposed the relational model • Relations  TABLES! • Key concepts: • Normalization (Good Vs. Bad tables) • Split or Divide Tables!! • SQL Programming Language • Initial Resistance to the Model • Commercial aspects; IBM slow to market • Performance aspects; additional resources • Tables (Rows, Columns) = RELATIONS(tuples, attributes)

  25. Then Along Came ERDsEnity-Relationship Diagrams A.k.a., Data Structure Diagrams STUDENT CLASS 1:N Relationships = GOOD!! and NORMAL, too GRADES

  26. Fig. 1.1: RDBMs – It’s All About TABLES, Relationships & DataStructures Primary Keys (PK) & Foreign Keys (FK) PS: If one table is good… then many tables are better!!

  27. Relational DatabaseStructured Query Lang. (SQL) • SQL, a.k.a. SEQUEL • ANSI & ISO standards attempted since 1980 • Mathematically rigorous and complete • “Inter-galactic data speak.” • M. Stonebraker, 1994

  28. Selected SQL Statements [7-11] • Database Definition Language (DDL) • CREATE (TABLE, INDEX, VIEW, etc.) • ALTER, DROP • Database Manipulation Language (DML) • SELECT • UPDATE, DELETE, INSERT • Database Control Language (DCL) • GRANT, REVOKE • COMMIT, ROLLBACK

  29. SELECT Statement -- 4 Basic Operations [30,000 ft view] SELECTlist-of-columns or expressions FROMlist-of-tables or views WHERElist-of-row conditions [ORDER BYlist-of-columns[ASC/DESC]] [UNION SELECT table2]; • Projection operation • Selects columns: • Selection operation: • Selects rows: • Join operation: • Connects/Appends rows 2 or more tables • Union operation • Merges 2 or more tables’ rows by columns

  30. SELECT Statement Example #1 [DB2/ISQL] • Simple General Form: SELECTlist-of-columns or expressions FROMlist-of-tables or views [WHERElist-of-row conditions [ORDER BYlist-of-columns[ASC/DESC] ] ][;] • EXAMPLE #1 [projection: DB2/ISQL in VM CMS]: SELECT * FROM employee;

  31. IBM CMS DB2 Interactive “Memorex” Example

  32. SELECT Statement -- Basic Operation 1: Projection Op. SELECTlist-of-columns or expressions FROMlist-of-tables or views WHERElist-of-row conditions [ORDER BYlist-of-columns[ASC/DESC]] [UNION SELECT table2]; • Projection operation “Selects/Specifies” columns: SELECT column1, column2, …, etc. FROM table1; SELECT empno, lastname, …[or *] FROM employee;

  33. SELECT StatementExample #2 [DB2/ISQL] SELECTlist-of-columns or expressions FROMlist-of-tables or views WHERElist-of-row conditions [ORDER BYlist-of-columns[ASC/DESC]]; • EXAMPLE #2 [projection, selection, [order by]]: SELECT empno, lastname, phoneno, workdept FROM employee WHERE workdept = ‘D11’ [ORDER BY lastname DESC];

  34. SELECT empno, lastname, phoneno, workdept FROM employee WHERE workdept= ‘D11’ ORDER BY lastname DESC;

  35. SELECT Statement -- Basic Operation 2: Selection Op. SELECTlist-of-columns or expressions FROMlist-of-tables or views WHERElist-of-row conditions [ORDER BYlist-of-columns[ASC/DESC]] [UNION SELECT table2]; • Selection operation • Selects rows: WHERE column1 = value SELECT empno, lastname, …. FROM employee WHERE workdept = ‘D11’;

  36. Example: Employee Database:[Along Came -- 2 Entities/Tables]Data Structure Diagrams DEPARTMENT 1 N 1:N Relationships = GOOD! and NORMAL, too! EMPLOYEE

  37. Null Character

  38. SELECT StatementExample #3 [DB2/ISQL] SELECT list-of-columns or expressions FROM list-of-tables or views WHERE list-of-row conditions ORDER BY list-of-columns [ASC/DESC]; • EXAMPLE #3 [Join]: SELECT e.empno, e.lastname, e.phoneno, d.deptname FROM employee e, department d WHERE e.workdept=d.deptno [AND e.workdept= ‘D11’];

  39. SELECT e.empno, e.lastname, e.phoneno, d.deptname • FROM employee e, department d • WHERE e.workdept=d.deptno [AND e.workdept= ‘D11’];

  40. SELECT Statement -- Basic Operation 3: Join Op. SELECTlist-of-columns or expressions FROMlist-of-tables or views WHERElist-of-row conditions [ORDER BYlist-of-columns[ASC/DESC]] [UNION SELECT table2]; • Join operation • Connects/Appends rows 2 or more tables: • WHERE T1.column1 = T2.column2 SELECT e.empno, e.lastname, e.phoneno, d.deptname FROM employee e, department d WHERE e.workdept=d.deptno [AND e.workdept= ‘D11’];

  41. SELECT StatementExample #4 [MF NX/SQL] SELECTlist-of-columns or expressions FROMlist-of-tables or views WHERElist-of-row conditions ORDER BYlist-of-columns [ASC/DESC]; • EXAMPLE #4 [projection, selection]: /* L4-3 List customers with balances over $2,000 */ SELECT company, state, balance -- columns FROM customer -- customer table WHERE balance > 2000; -- balance over $2000

  42. Example #4 [NX SQL]3 Windows Open in SQL Wizard • 1. Catalog Browser • 2. SQL Query • 3. Result

  43. SELECT StatementExample #5 [MF NX/SQL] • Use QBE window for new query • Generated SQL • EXAMPLE #5 [Join with Order By]: SELECT E1.e_no, E1.fname, E1.lname, E1.dept, D1.mgr, E1.payrate, E1.zip, D1.dname FROM TUTORIAL.TUTORIAL.EMPLOYEE E1, TUTORIAL.TUTORIAL.DEPT D1 WHERE (D1.dept = E1.dept) ORDER BY E1.lname, E1.fname;

  44. Example #5 [NX SQL]3 Windows Open in SQL Wizard 1. QBE Browser 2. Result 3. Order By …

  45. SELECT Statement -- Basic Operation 4: UNION Op. SELECTlist-of-columns or expressions FROMlist-of-tables or views WHERElist-of-row conditions [ORDER BYlist-of-columns[ASC/DESC]] [UNION SELECT table2]; • Union operation • Merges 2 or more tables’ rows by columns: UNION operator SELECT * FROM department UNION SELECT * FROM department2; department department2

  46. Simplistic View of Relational Normalization • SQL manipulates rows more efficiently than columns (programming ease) • Thus: Design Tables with fewer Columns, and many Rows • HOW: Divide “big”(many column) tables into multiple smaller tables • Avoids Anomalies • NORMALIZATION => DIVIDE TABLE!!

  47. Course Overview of DBD Approaches:Three Types of Database Design (DBD)

  48. Summary • Course will use MicroFocus NetExpress (MF NX) and with SQL option [a.k.a., DB2] • Course will use Relational Database Methodology & Several Data Modeling Approaches (ERD & SOM, UML) • SQL is “THE” database language standard • Multi-user, multi-application, and multi-tier databases will be studied END

More Related