Database Systems ClientServer Databases and the Oracle Relational

Database Systems Client/Server Databases and the Oracle Relational Database 1

File-Based Data Processing § Each application has data files that correspond to only that application Checking Account Data Files Checking Account Programs Savings Account Data Files Auto Loan Programs Savings Account Programs 2

Problems with File-Based Processing § Each application must have duplicate programs for filehandling operations § Duplicate data exists in each application’s data files § Duplicate data can become inconsistent 3

Database Processing n All applications interface with the same data in a central database Database Management System Auto Loan Programs Checking Account Programs Savings Account Programs 4

Database Processing n Database Management System (DBMS) provides central set of common functions to manage data: n n n Insert Update Retrieve View Oracle is a relational, client/server DBMS 5

Types of Database Systems n Hierarchical: data relationships are maintained using pointers n n Pointers create links using physical hardware locations Relational: data relationships are maintained using shared data values called key fields 6

Hierarchical Databases Parent Data Pointer Child Data 7

Relational Databases n Data is organized in tables n n Columns (fields) represent different data categories Rows (records) contain actual data values Fields PRODUCT_ID DESCRIPTION QUANTITY_ON _HAND 1 Plain Cheesecake 8 2 Cherry Cheesecake 10 Records 8

Relational Table Keys n Primary key: uniquely identifies a record n n Must be unique for each record Cannot be NULL (undefined) Candidate key: field that could be used as a primary key Surrogate key: numeric field created for the purpose of being the primary key 9

Relational Table Keys n Foreign key n n n Field in a table that is a primary key in another table Creates a relationship between the 2 tables Foreign key values must exactly match 10

Table Relationships Primary Key PRODUCT_ID DESCRIPTION QUANTITY_ON _HAND 1 Plain Cheesecake 8 2 Cherry Cheesecake 10 Foreign Key ORDER_ID PRODUCT_ID ORDER_QUANTITY 100 1 2 100 2 2 200 1 1 11

Composite Key n Primary key made of the combination of two or more fields Composite Key ORDER_ID PRODUCT_ID ORDER_QUANTITY 100 1 2 100 2 2 200 1 1 12

Database System Architectures n Mainframe databases n n n Personal databases n n Both DBMS and applications run on mainframe computer Users access database and applications using terminals Both DBMS and applications run on user’s workstation Client/server databases n n DBMS runs on central server Applications run on user’s (client) workstation 13

Advantages of Client/Server Databases § § § Handling server and client failures Processing transactions Handling high data volumes Providing security Servicing multiple simultaneous users 14

The Oracle Environment (Server Side) § Enterprise Edition (on central DB server) Or § Oracle Personal Edition § Personal database that acts just like a client/server database 15

The Oracle Environment: Client Side § SQL*Plus & PL/SQL § Developer § § § Procedure Builder Form Builder Report Builder Graphics Builder Project Builder 16

What do you need for this class? n n Oracle Express Edition (any version) Works on Linux or Windows, NO OSX compatible version yet. 17