4620 Exam 2

VIEWS

"Virtual" table that is derived from other tables Allows for limited update operations Allows full query operations Once defined, SQL queries can use the View relation in the FROM clause Always up-to-date, responsibility of the DBMS and not the user Can be used to hide certain attributes or tuples from unauthorized users

True

(T/F) Domains make it easier to change the data type for a domain that is used by numerous attributes.

Three most common (specifying attribute constraints)

- Default <value> - NOT NULL - CHECK (Number > 0 AND Number < 21);

Separation of concern

- Have one utility class that actually interacts with the database - Make it's methods static - Other classes call the utility class methods

SQL Schema

- Identified by a schema name - includes an authorization identifier and descriptors for each element.

Domain

- Name used with the attribute specification - Makes it easier to change the data type for a domain that is used by numerous attributes - Improves schema readability Ex. CREATE DOMAIN SSN_TYPE AS CHAR(9);

referential triggered action

- SET NULL, CASCADE, and SET DEFAULT

Foreign key errors:

- circular references - or because they refer to a table that has not yet been created.

Nested queries

- complete the select-from-where blocks within WHERE clause of another query. - Outer query and nested subqueries

Statement interface

- provided by the JDBC library - queries we want to run - create a string with our SQL Query and wrap it in our Statement object

?

- represents a statement parameter - determined at runtime

Alter table actions include:

1. Adding or dropping a column (attribute) 2. Changing a column definition 3. Adding or dropping table constraints

Explicit (enumerated) set of values in the WHERE clause

EX: SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1,2,3,);

semicolon

Each statement in SQL ends with a ______________ .

LEFT OUTER JOIN

Every tuple in left table must appear in result

RIGHT OUTER JOIN

Every tuple in right table must appear in result

FOREIGN KEY clause

FOREIGN KEY (<attribute_name>) REFERENCES <table_name>(<attribute_name>)

CREATE VIEW

Give table name, list of attribute names, and a query to specify the contents of the view

one

Have _____ utility class that actually interacts with the database. - easier to adapt to change - easier to enforce security protocols.

ResultSet object

Holds the result of query

Commands used to modify database

Insert, Update, Delete

Base Constraints

Key, Entity Integrity, Referential integrity

Naming a constraint

Keyword "CONSTRAINT" - Useful for later altering the constraint - it is not required by SQL to name your constraints

Decimal (m, d)

M is the total number of digits D is number or digits after the decimal point

NATURAL JOIN

No join condition specified

does not

SQL _______________ automatically eliminate duplicate tuples in query results

authorization identifier; descriptors

SQL schema includes an ________________________________ and ___________________ for each element.

Three-valued logic

TRUE, FALSE, UNKNOWN

Table constraints

can be added to the table itself, occur after the attribute list. - Normally where primary and foreign keys are specified. - Some DBMS allow them to be specified in the attribute itself - Allows for naming the constraints

Impedance Mismatch

differences between database model and programming language model.

Embedded SQL

embedding database commands in a general-purpose programming language

Binding

for each host programming language, specifies for each attribute type the compatible programming language types.

Schema elements

includes: Tables, constraints, views, domains, and other constructs

ResultSet

is another class provided by our JDBC library to help us handle the impedance mismatch - It is java's representation of a table that a SQL statement returns - Each row is a tuple - Each column is an attribute

Delete

may also update a number of tuples (rows) in a relation (table) that satisfy the condition.

Update

may update a number of tuples (rows) in a relation (table) that satisfy the condition.

Stored procedures

program modules stored by the DBMS at the database server. - Can be called by functions or procedures

Persistent Stored Modules

stored persistently by the DBMS - including general-purpose programming constructs in SQL Useful: - When database program is needed by several applications - Reduce data transfer and communication cost between client and server in certain situations - To enhance modeling power provided by views.

Insert

typically inserts a tuple (row) in a relation (table).

PreparedStatement

use parameter binding, which is more secure that creating a query string ourselves - creates a separation between the query, and the data Can create the statement with the query and without the data, then bind the data to the query. - use ? to show places in the query where data will be placed.

_ (LIKE comparison operator)

used for string pattern matching replaces a single character

% (LIKE comparison operator)

used for string pattern matching used to match an arbitrary number of zero or more characters.

setString(int pos, String data)

used to replace your ? with data

Key constraint

A primary key value cannot be duplicated

Entity Integrity Constraint

A primary key value cannot be null.

Selection condition

Boolean condition that must be true for any retrieved tuple

not available

Boolean data type is _______________ in all DBMS

Built-in aggregate functions

COUNT, SUM, MAX, MIN, AVG

True

Can the same name be used for two (or more) attributes in different relations (TRUE/FALSE)

Virtual relation(views)

Created through the CREATE VIEW statement. Do not correspond to any physical file.

cast

DATE, TIME, Timestamp, INTERVAL data types can be ______ or converted to string formats for comparison.

False, They do have ways

DBA's do not have ways to stop referential integrity enforcement to get around foreign key issues (True/False)

special prefix

Database statements are identified by a _________________

Aliases (tuple variables)

Declare alternative relation names E and S to refer to the EMPLOYEE relation twice in a query. EX. SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn=S.Ssn

all tuples

a missing WHERE-clause specifies that _________________ in the relation are to be deleted; the table then becomes an empty table.

CallableStatements

are for running Stored Procedures, not queries

Projection attributes (SELECT, FROM)

attributes whose values are to be retrieved.

EXISTS and NOT EXISTS function

Checks whether the result of a correlated nested query is empty or not Boolean functions that return TRUE or FALSE results

Comparison operator IN

Compares value c with a set/multiset of values V Evaluates to TRUE if v is one of the elements in V

IN (Comparison operator)

Compares value v with a set (or multiset) of values V

Nested queries

Complete select-from-where blocks within WHERE clause of another query. Outer query and nested subqueries

GROUP BY clause

Partition relation in subsets of tuples based on grouping attribute(s) Apply function to each group independently Specifies grouping attributes

Joined table

Permits users to specify a table resulting from a join operation in the FROM clause of a query

HAVING clause

Provides a condition to select or reject an entire group

Base tables (Base relations)

Relation and its tuples are actually related and stored as a file by the DBMS

= ANY comparison

Returns TRUE if the value is equal to some value in the set Equivalent to IN ANY can also be used with >, >=, <, <=, and <>

UNIQUE

Returns TRUE if there are no duplicate tuples in the result of a query

EX: Retrieve the name of each employee who has a dependent with the same first name as the employee

SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS A WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN = E.SSN AND E.FNAME = DEPENDENT_NAME);

EX: Retrieve the name and address of all employees who work for the 'Research' department

SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME = 'Research');

several different

Single Java program can connect to ____________ ____________ databases - called data source accessed by the Java program

Defining tables

Single table derived from other tables called the _____________

Unique Clause

Specifies alternate (secondary) keys (called CANDIDATE keys in the relational model). - UNIQUE (Dname)

Primary Key Clause

Specifies one or more attributes that make up primary key of a relation PRIMARY KEY(Dnumber), PRIMARY KEY(Dnumber, ExtNumba)

CREATE ASSERTION

Specify additional types of constraints outside scope of built-in relational model constraints

CREATE TRIGGER

Specify automatic actions that database system will perform when certain events and conditions occur Used to monitor the database Has three components: Event, condition, action

Create Table Command

Specify: - Name of Table - Attributes, their types, and initial constraints

SELECT

Standard arithmetic operators can be included as a part of ____________ .

ALL comparison

Value must exceed all values from nested query

Referential integrity constraints

The "foreign key" must have a value that is already present as a primary key, or may be null.

six

Timestamp data type includes the DATE and TIME fields with a minimum of ______ positions for decimal fractions of seconds

one table

Tuple are deleted from only _____________ at a time (unless CASCADE is specified on a referential integrity constraint)

INNER JOIN

Tuple is included in the result only if a matching tuple exists in the other relation

Set operations

UNIT, EXCEPT(difference) , INTERSECT

same

UPDATE command modifies tuples in the ____________ relation

clearParameter()

Used to remove any data from the PreparedStatement. - done for safety and sanity

Correlated nested queries

When a condition in the WHERE clause of a nested query references an attribute of a relation declared in the outer query

types; initial constraints

When specifying a new relation, you must specify attributes, their __________ and ____________________.

Cartesian product

When we pull from multiple tables we end up with the ________________________ .

True

With Aliasing, you can also rename attributes (TRUE/FALSE)

Precompiler;preprocessor

________________ or ________________ scans the source program code. - Identify database statements and extract them for processing by the DBMS