Transcript Application Development Concepts

MD807: Relational Database
Management Systems
• Introduction
– Course Goals & Schedule
– Logistics
– Syllabus Review
• RDBMS Basics
– RDBMS Role in Applications
– RDBMS Functions
Course Goals
• Database Management Concepts
– Design, Capabilities, Limitations
– Development Process
• Detailed knowledge of SQL “API”
– Managing Data
– Manipulating Data
– Strong Focus on Reporting
• Database Application Development
– Focus on procedures
– Database-oriented web development
Logistics
•
•
•
•
Webpage: www2.bc.edu/~spang
Reference Material
Syllabus / Schedule
Access to the Oracle Software
– Isqlplus.bc.edu
• bcacad3
• Project
• Grading
• Labs
Syllabus / Sequence
•
•
•
•
•
•
•
•
•
Purpose / Need
Logical design
Physical design
Data Management
SQL Reporting
Procedure development
Data/Logic Security
Data Integrity and Protection
Web Application Environment
BC’s 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
The DBMS in the
Application Environment
Web
Server
Client
(Browser)
Disk
Storage
browser
“Jolt”
Application
Server
“OS”
SQLNet,
ODBC,…
DBMS
Server
Application Development
Environment
Network
Servers
Operating System/Subsystems
Common System Utilities
Common Business Code
Application Specific Code
DBMS Application Services
•
•
•
•
•
Physical Storage of Data
Metadata Repository
API/Language to manage
API/Language to /Use
Utilities and Services
– Recovery, Performance
• Usage Management
– Sharing
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
The DBMS Server layers
DBMS Storage
DBMS Server
Database
Server
Tablespace
Instance
Table
Schema
Tablespace
Instance
Schema
Table
Schema
Table
“Mounts”
DBMS Architecture
• Disk Storage / File System
• DBMS Application
– Server
– Instance
– Schema
• Logical Data Constructs
– Tablespaces
– Tables
– Views
• Clients
– SQLNet Heavy Clients
– Web Clients
DBMS Operations
•
•
•
•
Starting the Instance
Mounting the database
Opening the Database
Quiescing / Closing the Database
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
Locking Scenario
•
•
•
•
•
•
User A requests record 5
User B requests record 5
User A changes record 5
User A commits changes
User B changes record 5
User B commits changes
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
SQL API
• Defines command syntax
– Data Definition
– Data Manipulation
– Session Management
• Open Standard
• Extensions for each vendor
Utilities
• Import / Export
– SQLLOADER
•
•
•
•
Backup / Recovery
Administrative tools for DDL/DML
Operations Management
Gateways
Terminology Review
•
•
•
•
•
•
•
•
•
•
Database Server
Database Instance
Schema
Table-space
Table
View
Column, Row
Data type
Procedure
Set Processing
Preview the Sample
Database
Login
Finding Data
Running a request