MI807: Database Systems for Managers

1
MI807 Database Systems for Managers

• Introduction
• Course Goals Schedule
• Logistics
• Syllabus Review
• Relational DBMS Basics
• RDBMS Role in Applications
• RDBMS Functions

2
Course Goals

• Database Management Concepts
• Design, Capabilities, Limitations
• Development Process
• Business constraints
• Detailed knowledge of SQL API
• Managing Data
• Manipulating Data
• Strong Focus on Reporting
• Database Application Development
• Focus on procedures
• Database-oriented web development

3
Logistics

• Webpage www2.bc.edu/spang
• Reference Material
• Syllabus / Schedule
• Access to the Oracle Software
• Isqlplus.bc.edu
• bcacad1 database instance
• Project
• Grading
• Labs

4
Syllabus / Sequence

• Purpose / Need
• BC Environment Tools
• Logical design
• Physical design
• Data Management
• SQL Reporting
• Procedure development
• Data/Logic Security
• Data Integrity and Protection
• Web Application Environment

5
The DBMS in the Application Environment
Web Server
Disk Storage
Client (Browser)
browser
Jolt
OS
DBMS Server
Application Server
SQLNet, ODBC,
6
BCs Oracle Software

• Host Database
• Oracle Release 9i tools
• BCACAD1 Instance (Rel 8.1)
• Userids/passwords
• iSQLPlus on the web
• SQLPlus Thick Client
• Universal Oracle client
• Available on BC network
• SQLLoader
• HTMLDB
• Oracle 10 App Development tool

7
Application Development Environment
Network
Servers
Operating System/Subsystems
Common System Utilities
Common Business Code
Application Specific Code
8
DBMS Application Advantages

• Single, consistent source of rules for accessing
  data
• Shared access for users, processes
• Insulation from data storage mechanics
• Increased granularity of access
• Integrity/recovery functions

9
The DBMS Server layers
Application Heavy
Client sqlplus App Server Web Server isqlplus
DBMS Storage
DBMS Server
DBMS Client
Database Tablespace Table
Tablespace Table Table
Server Instance Schema
Instance Schema Schema
Connects
Mounts
10
DBMS Architecture

• Disk Storage / File System
• DBMS Application
• Server
• Instance
• Schema
• Logical Data Constructs
• Tablespaces
• Tables
• Views
• Clients
• SQLNet Heavy Clients
• Web Clients

11
Data Integrity

• Locking
• Allows simultaneous use
• Different levels available
• Logging
• Prevent partial updates
• Recovery from failures
• Constraints
• Limits on acceptable entries
• Discussed in detail later
• Process

12
Data Security

• Granularity
• Filter records
• Filter columns
• Type of Access
• E.g. read-only
• Multiple levels
• Database
• Table
• Function
• Roles
• Groups of privileges
• Assignable to individuals

13
SQL API

• Defines command syntax
• Data Definition
• Data Manipulation
• Session Management
• Open Standard
• Extensions for each vendor

14
Terminology Review

• Database Server
• Database Instance
• Schema
• Table-space
• Table
• View
• Column, Row
• Data type
• SQL
• Constraint

15
Preview the Sample Database

• Login
• Finding Data
• Running a request