IT314 Final

Column

Organizational data in a central location

Installs, Administers, Maintains DB's

Interface that allows users to interact wtih the DB

Handles all routine operations

Object about which you want to store data

Data organized to have a primary key and no repeating groups

A row/record

Establish relationships among records in different tables

An instance can occur multiple times in each entity

Instance can appear once in one entity, and multiple times in another

Instance can occur once in each entity

DBMS and apps run on the same workstation and appear as a single application

Column that must be unique for each row, identifies that row

Stores data in a tabular format

Link that shows how different records are related

1NF + No partial Dependencies

Lists of numbers the DB automatically generates, guarantees each PK is unique

Artifical PK, made just to be a PK

2NF + No Transitive Dependencies

fixed-length up to 2,000 characters

Rules that restrict data values that you can enter into a column

• Create new objects
• Modify/Delete existing objects

Insert/Update/Delete/View Database data

Stores dates from 12/31/4712 BC to 12/31/4712 AD

• Contains specific number of decimal places
• (Floating point contains variable number of decimal places)

whole number, no digits

Define Primary, Foreign keys

• stores negative/positive/fixed/floating numbers between
• 10^-130 and 10^125
• Precision up to 38 decimal places

can only modify the table in certain situations

Query language for relational databases

stores fractional seconds, century, year, month, day, hour, minute, second

• User's area of the DB
• Stores all user-created objects

Specific to the column

Stores variable-length characters up to 4,000 characters

• Object based on an actual table
• Enables DBMS to present the table in different formats

• DML command
• inserts/updates/deletes data

alternate name for a column

combines AND, OR, NOT

Performs operation on a group of rows and returns single result

Joins two tables based on values in one table being equal to those of another

Combines data from multiple tables using foreign key references

• Specifies how many characters appear on display line
• Property of SQL*Plus

Main query and one or more subqueries

Returns all the rows from one table, plus matching rows from a second table

How many lines appear on an SQL*Plus page

acts like a column, is actually a command

DML command that allows users to view data

• Designates beginning of an individual section of a transaction
• is a bookmark

Built-In functions that return a single result for each row of data retrieved 

Alternate name that you assign within the FROM clause

Series of actions that represent a logical unit of work

Removes all of a table's data without saving any rollback info

References a data structure that contains multiple scalar variables

joins two separate strings

Points to a memory location on the database server that the DBMS uses to process a query

Object containing multiple elements

begins with DECLARE

an unwanted event

Begins with BEGIN

Retriesves/Displays data in PL/SQL programs for a query that might receive multiple records or none at all

• Receives one or more parameters
• Returns single output value

points to a context area and parsed representation of the query

Doesn't stop program, but produces incorrect result

Area on the server that stores output values before they're displayed to the user

• Separate a single string into two separate strings
• Using commas or spaces

• Executes one or more program commands
• THEN evaluates exit condition

most common errors

uses detailed, sequential instructions

reference a single value

Directly reference a specific column/row and assume its data type

Language in which each variable must be declared before it can be used

Occurs when command does not follow the guidelines of the programming language

• Less common
• Do not have predefined names

do not interact with other program units

• table that contains multiple sorted columns to identify row location
• contains up to 16 columns

• Top-level node within object navigator
• not associated with a specific form

Contains data values along with corresponding column that specifies physical locations of records

Program units that execute in response to insert/update/delete events

Code library containing related program units and variables

contains commands to create program units that are declared

Declares package objects: variables, cursors, procedures, functions

Specifies how the program unit can change the parameter value (IN OUT)

Operating System file that contains code for multiple related procedures and functions

Program unit that can receive multiple parameters and return many or no values

visible to many programs

Fire once for each row affected by the triggering statement

specifies internal location of the record in the database

stored on server, execute on server

• Fires once
• either before or after trigger statement executes

• other PL/SQL programs can reference them
• other database users can execute

Whether trigger fires once for each row or for the statement

• Defines type of SQL statement that causes a trigger to fire
• can be on INSERT, UPDATE, or DELETE

Defines whether trigger fires before or after SQL statement executes

• a. during each column declaration
• b. at the end of the CREATE table command

CONSTRAINT constraint1 PRIMARY KEY

or at end of create table:

• CONSTRAINT constraint1 PRIMARY KEY (column1)
• PRIMARY KEY (column 1, column2)

FOREIGN KEY (column1) references table (constraint)

DROP TABLE table1 CASCADE CONSTRAINTS;

RENAME table1 TO table2;

• ALTER TABLE table1 
• ADD (column NUMBER PRIMARY KEY);

• ALTER TABLE table1
• RENAME COLUMN column1 TO column2;

• ALTER TABLE table1
• MODIFY (column1 CHAR);

• ALTER TABLE table1
• DELETE column column1;

• ALTER TABLE table1
• ADD CONSTRAINT name;
• [DROP CONSTRAINT name];

• CURSOR cursor1 IS 
• SELECT *
• FROM
• ;

• CREATE OR REPLACE FUNCTION[PROCEDURE]
• (parameter1 IN varchar2)
• RETURN number
• IS
• cnumber number;
• BEGIN
• RETURN;
• END;

• Open cursor1;
• Fetch cursor1 into variable1;
• If cursor1%NOTFOUND then exit;
• endif;
• close cursor1;

• CREATE VIEW view1 AS 
• SELECT *
• FROM table;

DROP VIEW view1;

true

• CREATE OR REPLACE TRIGGER orders_before_insert
• BEFORE INSERT
• ON table
• FOR EACH ROW

DROP TRIGGER trigger1;

• ALTER TRIGGER trigger1 DISABLE;
• ALTER TRIGGER trigger1 ENABLE;

ALTER TABLE table1 ENABLE ALL TRIGGERS;

cannot use WHERE, must use HAVING

CREATE INDEX index1 ON table1 (column1);