1 / 56

BCIS 4620 INTRODUCTION TO DATABASE PROCESSING Class 2

BCIS 4620 INTRODUCTION TO DATABASE PROCESSING Class 2. Spring 2010 Dr. Jack D. Becker. CLASS 2 – Components of DB Processing Systems, DB Devel. Process, & NX SQL Overview. Announcements & Class Rolls -- 2nd Call NX SQL Tips DB Development Process Kroenke 5 Component DB Model

sheng
Download Presentation

BCIS 4620 INTRODUCTION TO DATABASE PROCESSING Class 2

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 PROCESSINGClass 2 Spring 2010 Dr. Jack D. Becker

  2. CLASS 2 – Components of DB Processing Systems, DB Devel. Process, & NX SQL Overview • Announcements & Class Rolls -- 2nd Call • NX SQL Tips • DB Development Process • Kroenke 5 Component DB Model • DBMS Definition • Homework #1 SQL Tutorial

  3. Tutors BCIS 4620 DRAFT All 4620 Tutors in Room 333

  4. CLASS02 DOC B1 • CLASS #2 -- DATABASE PROCESSING • Roll Call • Class Announcements (CISO) • CISO meetings • Tutor Schedules (also on course website) • Cabinets on 3rd Floor BCIS • A - Z Top Shelf

  5. NX SQL Tips • MicroFocus NX SQL Product 5.0 UE • HELP/Getting Started & Getting Started – Additional Topics • Create and Use SQL DB2 (XDB) • “Sqldemo” Project Folder in NX Help Chapter 11 tutorial • SQL DB2 Server/Start/Stop feature • PRINTING • Use COBA lab printers for first assignments • EMAIL policies • becker@unt.edu for personal email • WebCT for Group-email or Eagle mail (used sparingly)

  6. Start Here!

  7. Integrated Development Environment (IDE)

  8. New Project Folder When you install Net Express, Setup creates, within the system folders containing Net Express, a folder called by default Net Express\Base\Workarea. This folder is intended as your work area, and we suggest that you put all your project folders within it.

  9. NX SQL Tips (continued) • SQL Wizard • SYSTEM/SYSXDB Locations • TUTORIAL Location • SQL Results options • Table Results/Form Results • XDB Mode • This implementation is a superset of DB2, with a number of extensions and some differences in handling of character values. • ANSI Mode • Supports Level 2 of the ANSI standard for SQL • DB2 Mode • Supports IBM's DB2 Version 6.

  10. SQL Wizard Mode

  11. SQL Wizard

  12. Tutorial Tables • Most of the example results in the SQL Tutorial are based upon the sample tables contained in your TUTORIAL location (see XDB Server TUTORIAL Location). The TUTORIAL location (along with the sample tables) is generally created automatically (the default) when XDB Server software is installed. • You may find it useful to execute the example commands as you work through the tutorial (all commands are executed in XDB mode). Unless otherwise noted, the results printed in this reference reflect the result obtained if the command is applied to the unchanged tables as shown in the XDB Server TUTORIAL Location. • To execute the examples shown in this reference, you must set your location and Current SQLID as follows [Note: Locations.SQLID is like a DB2 subdirectory/database space]: • SET CURRENT LOCATION = “TUTORIAL” • SET CURRENT SQLID = "TUTORIAL"

  13. 2 Helps -- SQL Wizard

  14. Location & SQLID settings Naming Convention: Location.SQLID.tablename

  15. CLASS 2Introduction to DB Development • DB Development w/DB Processing System • Kroenke’s 5 component model: • Consists of hardware, programs, data, procedures, and people that encompass the entire database • SDLC Model (Analysis, Design, Development, and Operation) • RAD -- Rapid Application Development • DBMS -- Database Management Systems exist to support the DB Applications

  16. Kroenke’s 5 Component Model for DB Processing System • Hardware • Programs • Data (The Bridge) • People • Procedure SDLC -- • System Development Life Cycle Model

  17. SDLC with 5-Component Model

  18. 1. Hardware RequirementsDBPS • Additional Memory Requirements • 1:10 ratio [User raw data:Total DB storage] • Database Machines • Tb --Terabyte databases • 1x1012 = 1,000,000,000,000 bytes!!! • Client/Server systems

  19. 2. Software/Programs in DBPS • Simple Personal DB Applications • SQL • Program Generators (QMF) • More Complicated DB Applications • Host Language Interfaces (HLI; CALLs) • COBOL, Visual Basic, etc. • Very Complex DB Applications • Distributed database applications; Internet • Multi-database queries.

  20. 3. Data Elements in a DB Processing System[4 Data Elements] • Integrated User Data • Only DBMS knows the physical aspects of the data • Metadata -- Overhead data that describes the data • System data tables, names for columns, types of data, size of tables, internal formats, domain specifications, etc.

  21. 3. Four Data Elements (continued) • Indexes -- Overhead data • Inverted lists, linked lists, etc. • Application Metadata • Data about the structure of applications • Constructs such as (e.g., ACCESS): • Menus Layouts • Screens Layouts • Report Layouts • Form layouts

  22. 4. People in DBPS • Customers or clientele • Users • Operators • System Developers/Analysts • Database Programmers • Database Managers (DBM) • Database Administrators (DBA)

  23. 5. Procedures in DBPS Documentation • Manuals • Books and CD ROM • On-line (See IBM Manuals ONLINE; Bookread.exe) • Normal Operating Procedures for Users and Operators • A.k.a., SOPs, Standard Operating Procedures • Failure/Recovery procedures • Data Administration/Control Procedures

  24. Database Processing System Components (DBPS) A DBPS consists of 4 components: • Database (Raw Data, Indexes, & Metadata) • DBMS (3 components)* • Application Programs (APs) • People: Users & Developers

  25. DBPS Components e.g. ACCESS

  26. Database Management System (DBMS) Definition • Three (3) Key DBMS Components (Figure 2-1): • A. Design Tools Subsystems • B. Run-time Subsystems • C. DBMS Engine

  27. A. Design Tools Subsystemin DBMS • Table Creation & Definition Tools • NX SQL Wizard; Schema and Subschema (Views) • Form Creation Tool & Report Creation Tool • NX SQL Wizard; SQL Editor • Query Creation Tool • NX SQL Wizard; SQL Editor • Procedural Language Compilers • COBOL Pre-compilers • Query Language Interface

  28. B. Run-Time Subsystemsin DBMS • Form Processor • SQL DB2 • Query Processor • SQL DB2 • Report Writer • SQL DB2 • Procedure Language Run Time Executors • COBOL/SQL

  29. C. DBSM Engine • Processes Physical I/O requests between the DBMS and the Disk O/S • Intermediary between the Design tools, the Run-time subsystems, and the Data • Transaction Management: • Locking & Concurrent Processing requests • Backup/Recovery

  30. Types of Database Systems(recall) • 3-Tier Architecture: • Personal Databases (PC) • Workgroup databases (Servers) • Organizational databases (EWIM) • Internet Databases • Multimedia (ODBMS)

  31. Types of Database Systems(recall)

  32. PAUSE?

  33. XDB Server – Main Menu Tools/SQL for DB2/Start Server

  34. COBOL Example TEST1.CBL

  35. SQL Wizard Window

  36. Run a Query

  37. Relational DatabaseStructured Query Lang.. (SQL) • SQL, a.k.a. SEQUEL • Key concepts: • Tables (Rows, Columns) • Normalization (Good Vs. Bad tables) • ANSI & ISO standards attempted since 1980 • “Inter-galactic data speak,” M. Stonebraker

  38. SQL Basic Relational Operations • SQL SELECT statement • 4 Basic Relational operations: • Selection (rows, WHERE condition) • Projection (columns, list) • Join (Product; Inner and Outer Joins on Keys) • Union (Union Compatible)

  39. Design View vs. SQL View

  40. Design View vs. SQL View

  41. After you RUN Query Result Table vs. Result Form

  42. Result Table vs. Result Form

  43. Selected SQL Statements • 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

  44. SELECT Statement • Simple General Form: • 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: SELECT * FROM EMP;

  45. Homework #1Building an SQL Project Folder in NX SQL • NX SQL Introduction • NX Tutorials – Getting Started • Chapters 1 - 4 • SQL Tutorials – Getting Started Additional Topics • Part 1 (Chaps. 1-3)

  46. MF NX Tutorial Map—Getting Starting in MF NX Chapters 1-4

  47. Hwk #1: SQL Options Tutorials Part 1: Chapters 1-3

  48. DO NOT SET UP MAINFRAME LINK [in Chap 12 DB2 Applications] Set up Access to Mainframe Through XDB Link • To connect to the mainframe directly, without using the XDB server: • Use the Gateway Profile utility to log information about the mainframe DB2 location, as follows: • Click Options > SQL for DB2 > XDB LINK. • Login to local XDB server as user INSTALL with no password • Click REGISTER • Consult mainframe DBA and HELP information to fill out relevant fields.

More Related