Databases 3-5 test

view

any such relation that is not part of the logical model, but is made visible to a user as a virtual relation (because a user may not need to see the entire database)

cursor

contains information on a select statement and the rows of data accessed by it. It's almost like a pointer but this work area is used to store data from a database and manipulate the data without going back to the table.

sytanx for creating a table

create table *table name* ( );

Embedded SQL and dynamic SQL

define how SQL statements can be embedded within general-purpose programming languages, such as C, C++, and Java.

syntax for deleting rows from a table

delete from *table name*;

inner join

equivalent to natural join and returns tuple where there is at least one match in both tables

syntax for inserting data into a table

insert into *table name* values (valueHere, valueHere, valueHere);

To design a trigger, what two requirements must be met?

1.) specify when a trigger is to be executed. 2.) specify the action to be taken when the trigger executes.

Different types of triggers

BEFORE - before (update, insert, delete) happens this can prevent invalid updates, deletes or inserts used when validation needs to take place before a change. INSTEAD OF - before (update, insert, delete) happens. We can have instead of delete do something else... replace the operation intended. If we want to update something instead of runs something else if we declare it to.. AFTER - trigger fires after sql server completes execution. Will only work if preceding sql execution happens with no errors. • usually when needed to update a table somewhere else.

5 basic aggregate functions

COUNT - gets the number of rows in the requested query's criteria MIN - returns the minimum MAX - returns max SUM - returns the total amount of rows/data AVG - finds avg (in numbers) of a group of rows that matches the query criteria

7 parts of SQL

Data-definition language - (provides commands for defining relation schemas, deleting relations, and modifying relation schemas) Data-manipulation language - (the SQL DML provides the ability to query information from the database and insert tuples into, delete tuples from, and modify tuples in the database) Integrity - (SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.) View definition - (SQL DDL includes commands for defining views.) Transation control - (SQL includes commands for specifying the beginning and ending of transactions.) Embedded SQL and dynamic SQL - (define how SQL statements can be embedded within general-purpose programming languages, such as C, C++, and Java.) Authorization - (the SQL DDl includes commands for specifying access rights to relations and views.)

full outer join

join that preserves all tuples in the relation

right outer join

join that preserves tuples only in the relation named after the right outer join operation

Left outer join

join that preserves tuples only in the relation named before the left outer join operation

How do you make a series of commands atomic?

o Begin a transaction and end, and anything in between executes atomically. o Begin atomic and end making sure all transactions are occurring separately in a single transaction

trigger

o a statement system executes automatically as a side effect of a modification of the database o Initiated when an event side effect such as (INSERT, UPDATE, DELETE) occurs

Three types of integrity constraints

primary key - required to be non null and unique and used to uniquely identify a row from a special relation. UNIQUELY IDENTIFIES EACH ROW OR RECORD IN THE DATABASE. foreign key - for each foreign key in relation S must be found as a primary key in another relation R. USED for referential integrity. not null - null value not allowed in certain attribute

Data-definition language (DDL)

provides commands for defining relation schemas, deleting relations, and modifying relation schemas

Authorization

the SQL DDl includes commands for specifying access rights to relations and views.

Data manipulation language (DML)

the SQL DML provides the ability to query information from the database and insert tuples into, delete tuples from, and modify tuples in the database

What makes a view non-updatable?

If the view does not contain a primary key, it is not updatable.

Where can you use a subquery?

Nested within the where clause. Commonly used to test for set membership, make set comparisons, and determine set cardinality.

What integrity constraints are given in Chapter 4?

Not null constraint (prohibits the insertion of null values for the attribute) Unique (says that attributes, or colums form a candidate key. No two tuples, or rows, in the relation can be equal on every attribute, or column) Check (ensures that attribute values satisfy specific conditions)

View definition

SQL DDL includes commands for defining views.

Integrity

SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.

Transaction control

SQL includes commands for specifying the beginning and ending of transactions.