DB1 & DB2

atomicity of updates

Failures may leave database in an inconsistent state with partial updates carried out (transfer of funds from one account to another should either complete or not happen at all)

entering data into table

INSERT INTO account(columnName, column2Name) VALUES (12, 23);

establishing a connection

connection to database server is made as follows: string url = "protocol://host database" con = DriverManager.getConnection(url, "userID", "pass"); the protocol identifies the specific database server type

database applications

banking; airlines; universities; sales; manufacturing; human resources

concurrent access by multiple users

concurrent access needed for performance; uncontrolled concurrent accesses can lead to inconsistencies (two people reading a balance and updating at the same time)

data dictionary

contains metadata

DDL eg

create table account(account-number char(10), balance integer)

sales

customers, products, purchases

drawbacks of using file systems to store data

data redundancy and inconsistency; difficulty in accessing data; data isolation-multiple files and formats; integrity problems; atomicity of updates; concurrent access by multiple users; security problems

JDBC

part of java app programming interface(API); protocol used by database servers is unique to that server; JDBC provides platform independent programming interface that can be used to develop database client software

level of abstraction

physical; logical; view

manufacturing

production, inventory, orders, supply chain

database server

program that listens on a predefined port for database commands (generally sql statements); then it executes the command and returns either a status response or resulting selected data (in case of select statements)

database client

program that sends command to a database server and processes the results; example: a client may permit the user to enter sql commands which are then sent to server and display the result

integrity problems

integrity constraints (like account balance > 0) become part of program code; hard to add new constraints or change existing ones

JDBC lib structure

java app --> JDBC api <--> JDBC driver manager <--> JDBC driver <--> Database

join

join is a subset of the cartesian product of two tables; several types; syntax: SELECT columns FROM table INNER JOIN tabl2 ON condition; join types : INNER, CROSS, LEFT OUTER, RIGHT OUTER

data manipulation language

language for accessing and manipulating the data organized by the appropriate data model; user specifies what data is required without specifying how to get those data (query language); SQL is the most widely used query lang

data redundancy and inconsistency

multiple file formats; duplication of info in different files

difficulty in accessing data

need to write new program to carry out each new task

Entities example

objects; student, courses

view level

app programs hide details of data types; can also hide information (Salary) for security purposes

sql creating a table

CREATE TABLE account( columnName columnType, column2Name ..... )

entity relationship model

E-R model of real world; entities (objects - customers, accounts, bank branch); relationships between entities ( account 101 is held by customer johnson; relationship associates customers with accounts);

retrieving data from table

SELECT column FROM source WHERE criteria; creates a virtual tempo table consisting only of the selected columns and rows which satisfy the criteria; source is either a table, a join, or another select

query

a declarative DML

ResultSet

a table of data representing a database result set which is usually generated by executing statement that queries the database; ResultSet object maintains a cursor pointing to current row of data; next method moves cursor to next row and because it returns false when there are no more rows in ResultSet object, it can be used to iterate through result set; data is retrieved using getXXX method where XXX is java data type

banking

all transactions

logical level

describes data stored in database and relationships among the data; like: type customer = record name:string; street:string; city:integer; end

physical level

describes how a record (customer) is stored

purpose of database system

early days, database apps were built on top of file systems; database systems offer solutions to all the drawbacks of using file system

human resources

employee records, salaries, tax deductions

retrieving data

executeQuery of Statement interface returns the results of sql query as a ResultSet. ResultSet executeQuery(string statmt)

adding or modifying data

executeUpdate method of Statement interface is used to INSERT, UPDATE, or DELETE sql command; executeUpdate(string stmt); return val is the row count for UPDATE, INSERT, or DELETE or 0 for sql commands that have no ret val

universities

registration, grades

relationships example

relationship between entities; madonna is taking cis2166

database

represents some aspect of real world; a logically coherent collection of data with some inherit meaning; is not a random assortment of data

airlines

reservations, schedules

data definition language

specification notation for defining the database schema; DDL compiler generates a set of tables stored in a data dictionary;

SQL

structured query lang; widely used non procedural lang; to manage, enter data into, and retrieve data from relational database;

instance

the actual content of the database at a particular point in time (analogous to the value of a variable)

schemas

the logical structure of the database (analogous to type of info of a variable in a program); for example: the database consists of info about a set of customers and accounts and the relationship between them

creating a statement object

used to send sql commands to database server; Statement stmt = con.createStatement();

DBMS

very large integrated collection of data; models real world (entities, relationships); full form: database management system; DBMS is a software package designed to store and manage databases