数据库课件总结：Database-Chapter-Four-Outline.docx

Database Chapter Four Outline
Chapter 4: Advanced SQLSQL Data Types and Schemas
EXACT(f7e/d from d) CAST e AS tcreate type Dollars as numeric (12,2) final
create domain person_name char(20) not null
Types and domains are similar. Domains can have constraints, such as not null, specified on them. Domains are not strongly typed.
Domain Constraints
Domain constraints are the most elementary form of integrity constraint. They test values inserted in the database, and test queries to ensure that the comparisons make sense.
Large-Object Types
Large objects (photos, videos, CAD files, etc.) are stored as a large object:
blob: binary large object - object is a large collection of uninterpreted binary data (whose interpretation is left toan application outside of the database system)
clob: character large object - object is a large collection of character data
When a query returns a large object, a pointer is returned rather than the large object itself.
Constraints on a Single Relationnot null
primary keyunique
check (P), where P is a predicate
Candidate keys are permitted to be null unless they have explicitly been declared to be not null (in contrast to primary keys).
create table depositor [customer_name char(20), account_number char(lO), primary key (customer_name, account_number), foreign key (account_number) references account, foreign key (customer_name) references customer)Assertions
create assertion <assertion-name> check <predicate>Forms of authorization on parts of the database:
Read - allows reading, but not modification of data.
Insert - allows insertion of new data, but not modification of existing data.
Update - allows modification, but not deletion of data.
Delete - allows deletion of data.
Authorization Specification in SQLThe grant statement is used to confer authorization
grant <privilege list>on <relation name or view name> to <user list>
grant select on branch to Ui with grant optionRevoking Authorization in SQL
revoke select on branch from Ui, U2, U3Trigger Example in SQL:1999 ECA(Event-Condition-Action model)
Create trigger o verdraft-trigger after update on accountnrow

nrow
referencingnewrowasfor each row
when nrow.balance < 0referencing old row as : for deletes and updates
referencing new row as : for inserts and updatesSQL Functions
create function account_count (customer_name varchar(20))returns integer
begindeclare account integer;
select count (*) into accountfrom depositor
where depositor.customer_name = customer_namereturn account;
endcreate function accounts_of (customer_name char(20)
returns table ( account_number char(lO),branch_name charflS), balance numeric(12,2))
SQL Procedurescreate procedure account count oroc (in title varchar(20), out a count integer)
begin
select count(author) into a count
from depositor
where deoositor.customer name = account count oroc. customer name
end declare account integer;call account_count_proc( "Smith", account);
■ Specify the query in SQL and declare a cursor for itEXEC SQL
declare ccursorforselect customer name, customer city from depositor, customer, account
where depositor.customer name = customer.customer nameWVW^WVWXAA/W\AAAAAAAAAAAAAAAAAA/WWSAAAAAA/SAAAA/WW VWXAA/SAAAAAAAAAAAAAAAAAA/WSAAA/SAAA/WW'v4AAAAAAAAAAAAA/'v
and depositor account number= accountaccount numberIXAAAAAAAA/VSAAAAAAAAAAAAAAAAAAA/S/V\A/\A/ W\AAAAAAAAAAAAAA/WSZvW\AAAAAAAAAAAAA>yWW\AAAAAAAAAA^
an d account.balance > :amountEND_EXEC
ODBCOpen DataBase Connectivity(ODBC) standard
standard for application program to communicate with a database server.
application program interface (API) to► open a connection with a database,
► send queries and updates,get back results.
Applications such as GUI, spreadsheets, etc. can use ODBCJDBC
JDBC is a Java API for communicating with database systems supporting SQL
JDBC supports a variety of features for querying and updating data, and for retrieving query results
JDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributesModel for communicating with the database:
Open a connectionCreate a “statement" object
Execute queries using the Statement object to send queries and fetch resultsException mechanism to handle errors
public static void JDBCexamDle(String dbid, String userid, String passwdjtry(
Class.forName (lvH)；Connection conn = DriverManager.aetConnection(
,vidbc:oracle:thin:@aura.bell-labs :2000:bankdbv\ userid, passwd):
Statement stmt = conn .create S tatsm ent();... Do Actual Work ....
stmt.close();connxIpseO；
)catch (SQLException sale) {
System.out.printlnf'SQLException : H + sale);)
■ Execute query and fetch and print resultsResultSet rset = stmt.executeQuery( "select branch_name, avg( balance)
from accountgroup by branch-name,1);
while (rset.next()){System .out.println(
rsetgetStringCbranch-name") +11 M + rset.getFloat(2));