Views

Views are virtual tables. They basically store queries:

CREATE VIEW name AS query

CREATE VIEW can_visit as (select salpers_name, cust_name from salesperson, customer where salesperson.office = customer.city)

Later we can ask, can any salespersons visit Jefferson?

SELECT salpers_name FROM can_visit WHERE cust_name = ‘Jefferson’

This does not create a table, it simply stores the query.

Because a view is not a table: Can we do this?

INSERT INTO can_visit VALUES (‘Weninger’, ‘Jefferson’) 

There is a lot of research trying to make this happen. Called “updatable views”. IE – what does this logically imply? But in general its not a good idea. It may not work as expected even if its allowed.

Joins

There are lots of different types of joins

SELECT column_name(s)
FROM table1
x JOIN table2
ON table1.column_name=table2.column_name;

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

Constraints

A constraint is a relationship among data elements that the DBMS is required to enforce.

Example: key constraints.

Triggers are only executed when a specified condition occurs, e.g., insertion of a tuple.

Easier to implement than many constraints.

Contraints

Keys.
Foreign-key, or referential-integrity.
Value-based constraints.
                Constrain values of a particular attribute.
Tuple-based constraints.
                Relationship among components.
Assertions: any SQL boolean expression.

Foreign Key

Consider Relation SALE

We might expect that a PROD_ID is a real product — something appearing in PRODUCT.PROD_ID.

This is called a foreign-key constraint.

Use the keyword REFERENCES, either:

Within the declaration of an attribute, when only one attribute is involved.
As an element of the schema, as:

FOREIGN KEY ( <list of attributes> )
		REFERENCES <relation> ( <attributes> )

Referenced attributes must be declared PRIMARY KEY or UNIQUE.

As attribute:

CREATE TABLE PRODUCT (
	PROD_ID	 INT PRIMARY KEY,
	PROD_DESC   VARCHAR(20) 
	… );
CREATE TABLE SALE(
	SALE_ID	INT PRIMARY KEY,
	…
	PROD_ID	INT REFERENCES PRODUCT(PROD_ID),
	…);

As element:

As element
CREATE TABLE PRODUCT (
	PROD_ID	 INT PRIMARY KEY,
	PROD_DESC   VARCHAR(20) 
	… );
CREATE TABLE SALE(
	SALE_ID	INT PRIMARY KEY,
	PROD_ID	INT 
	…
	FOREIGN KEY(PROD_ID) REFERENCES 	PRODUCT(PROD_ID) );

Foreign Key Constraints

If there is a foreign-key constraint from attributes of relation $R$ to the primary key of relation $S$, two violations are possible:

                An insert or update to $R$ introduces values not found in $S$.

                A deletion or update to $S$ causes some tuples of $R$ to “dangle.”

Possible Actions

An insert or update to SALE that introduces a nonexistent PRODUCT must be rejected.

A deletion or update to PRODCUT that removes a PROD_ID value found in some tuples of SALE can be handled in three ways:

Default : Reject the modification.
Cascade : Make the same changes in SALE.
                Deleted product: delete SALE tuple(s).
                Updated product: change value in SALE.
Set NULL : Change the PROD_ID to NULL.

Example cascade:

Suppose we delete the Sweater tuple from PRODUCT.
                Then delete all tuples from SALE that have PROD_ID = ’1035’.

Suppose we update the Sweater tuple by changing ’1035’ to ‘1036’.
                Then change all Sale tuples with prod_id = ’1035’ so that prod_id = ’1036’.

Example set NULL:

Suppose we delete the Sweater tuple from Product.
                Change all tuples of SALE that have prod_ID= ’1035’ to have prod_ID= NULL.

Suppose we update the Sweater tuple by changing ’1035’ to ‘1036’.
                Change all tuples of SALE that have prod_ID= ’1035’ to have prod_ID= NULL. (Same as delete)

Which action to choose?

When we declare a foreign key, we may choose policies SET NULL or CASCADE independently for deletions and updates.

Follow the foreign-key declaration by:

ON [UPDATE, DELETE][SET NULL CASCADE]

Otherwise, the default (reject) is used.

CREATE TABLE SALE(
	SALE_ID	INT PRIMARY KEY,
	PROD_ID	VARCHAR(20),	
	…
	FOREIGN KEY(PROD_ID)
		REFERENCES PRODUCT(PROD_ID)
		ON DELETE SET NULL
		ON UPDATE CASCADE );

In the Company database determine the foreign keys and what their actions ought to be.

Customer – None
Salesperson – salesperson.manager_id REF salesperson.salpers_id on update cascade on delete set null
Product – product.manf_id REF manufacturer.manf_id on update cascade on delete reject
Manufacturer – None
Sale – sale.prod_id REF product.prod_id on update cascade
sale.cust_id REF customer.cust_id
sale.salesper_id REF salesperson.salpers_id