DB C4 Advanced SQL

SQL Data Types and Schemas

ref P153-164

There are additional built-in data types and basic user-defined types supported by SQL.

Date and Time Types

• date: A calendar date containing a (four-digit) year, month, and day of the month.

• time: The time of day, in hours, minutes, and seconds. A variant, time§, can be used to specify the number of fractional digits for seconds (the default being 0). It is also possible to store time-zone information along with the time by specifying time with timezone.

• timestamp: A combination of date and time.Avariant, timestamp§, can be used to specify the number of fractional digits for seconds (the default here being 6). Time-zone information is also stored if with timezone is specified.

  date '2018-04-25' 
  time '09:30:00'
  timestamp '2018-04-25 10:29:01.45'

Large-object types

Large objects (e.g., photos, videos, CAD files, etc.) are stored as a large object.

• blob: binary large object, a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system)
• clob: character large object , a large collection of character data

When a query returns a large object, a pointer is returned rather than the large object itself.

User-Defined Types

• Structured data types

• Distinct types

domain

c.f domain vs. type

Domain is

• Constraints
• not strongly typed

Integrity Constraints

Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency.

实体完整性、参照完整性和用户定义的完整性

• Constraints on single relation : Not null, Primary key, Unique, Check (P) where P is a predicate

Domain Constraints

• The check clause in SQL-92 permits domains to be restricted.

• The clause constraint value-test is optional; useful to indicate which constraint an update violated.

Referential Integrity

Let $r_1(R_1)$ and $r_2(R_2)$ be the relations with primary keys $K_1$ and $K_2$ respectively.

• The subset $\alpha$ of $R_2$ is a foreign key referencing $K_1$ in relation $r_1$ if for every $t_2$ in $r_2$ there must be a tuple $t_1$ in $r_1$ such that

  $$t_1[K_1] = t_2[\alpha].$$

• Referential integrity constraint also called subset dependency, since its can be written as

  $$\Pi_{\alpha}(r_2) \sube \Pi_{K_1}(r_1)$$

Insert

If a tuple t2 is inserted into r2, the system must ensure that there is a tuple t1 in r1 such that t1[K] = t2[a]

Delete

If a tuple t1 is deleted from r1, the system must compute the set of tuples in r2 that reference t1:

If this set is not empty, then

• Either the delete command is rejected as an error,

• or the tuples in t2 that references t1 must themselves be deleted (cascading deletions are possible).

Update

Similar to Insert and Delete, requires check.

Primary, candidate keys, and foreign keys can be specified as part of the SQL create table statement:

The primary key clause lists attributes that comprise the primary key.

The unique key clause lists attributes that comprise a candidate key.

The foreign key clause lists the attributes that comprise the foreign key, and the name of the relation referenced by the foreign key.

By default, a foreign key references the primary key attributes of the referenced table:

Create table account 
	(account-number		char(10), 
	 branch-name		char(15), 
	 balance			integer, 
	 primary key (account-number), 
	 foreign key (branch-name) references branch); 

Create table depositor 
	(customer-name		char(20), 
	 account-number		char(10), 
	 primary key (customer-name, account-number), 
	 foreign key (account-number) references account, 
	 foreign key (customer-name) references customer(customer-name)); 

Cascading Actions in SQL

Create table account ( 
    ... 
	foreign key (branch-name) references branch 
		[ on delete cascade] 
		[ on update cascade ] 
		... ); 

• If there is a chain of foreign-key dependencies across multiple relations, with on delete cascade specified for each dependency, a deletion or update at one end of the chain can propagate across the entire chain.
• But, if a cascading update or delete causes a constraint violation that cannot be handled by a further cascading operation, the system aborts the transaction. All the changes caused by the transaction and its cascading actions are undone.
• Referential integrity is only checked at the end of a transaction !! Intermediate steps are allowed to violate referential integrity provided later steps remove the violation by the end of the transaction.

Assertions

An assertion is a predicate expressing a condition that we wish the database always to satisfy for complex check condition on several relations!

CREATE ASSERTION <assertion-name> 
			  CHECK <predicate>; 

When an assertion is made, the system tests it for validity on every update that may violate the assertion: when the predicate is true, it is Ok, otherwise report error.

May introduce a significant amount of overhead! hence assertions should be used with great care.

In SQL, using non exists to express the predicate.

CREATE ASSERTION sum-constraint CHECK 
	     		(not exists (select * 
                              from branch B 
                              where (select sum(amount) from loan where loan.branch-name = B.branch-name)
                           > (select sum(balance) from account where account.branch-name = B.branch-name))) 

Triggers

A trigger is a statement that is executed automatically by the system as a side-effect of a modification to the database.

To design a trigger mechanism, we must:

• Specify the conditions under which the trigger is to be executed.
• Specify the actions to be taken when the trigger executes.

A kind of stored procedures .

CREATE TRIGGER overdraftTrigger after update on account 
	referencing new row as nrow for each row 
	when nrow.balance < 0 
		begin atomic 
		insert into borrower 
			(select customer-name, account-number from depositor 
	 		 where nrow.account-number = depositor.account-number) 
	    insert into loan values 
			(nrow.account-number, nrow.branch-name, – nrow.balance) 
	    update account set balance = 0 
			where account.account-number = nrow.account-number 
	    end 

• Triggering event: can be insert, delete or update. Triggers on update can be restricted to specific attributes,
  e.g

  Create trigger overdraftTrigger
  after update of balance on account

• Values of attributes: before and after an update can be referenced,

  referencing old row as #for deletes and updates 
  referencing new row as #for inserts and updates 

Statement Level Triggers

Authorization

Security: protection from malicious attempts to steal or modify data.

Security Level

Database system level

• Authentication and authorization mechanisms allow specific users access only to required data.
• We concentrate on authorization in the rest of this chapter.

Operating system level

• Operating system super-users can do anything they want to the database! Good operating system level security is required.

Network level: must use encryption to prevent

• Eavesdropping (unauthorized reading of messages)
• Masquerading (pretending to be an authorized user or sending messages supposedly from authorized users)

Physical level

• Physical access to computers allows destruction of data by intruders; traditional lock-and-key security is needed

• Computers must also be protected from floods, fire, etc. – (Recovery)

Human level

• Users must be screened to ensure that an authorized users do not give access to intruders

• Users should be trained on password selection and secrecy Embedded SQL

Views

Dynamic SQL

ODBC and JDBC