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