IT314 Final

The flashcards below were created by user esmenikmati on FreezingBlue Flashcards.

  1. Attribute
  2. Database
    Organizational data in a central location
  3. DBA
    Installs, Administers, Maintains DB's
  4. Database Apps
    Interface that allows users to interact wtih the DB
  5. DBMS
    Handles all routine operations
  6. Entity
    Object about which you want to store data
  7. 1st Normal Form (1NF)
    Data organized to have a primary key and no repeating groups
  8. Instance
    A row/record
  9. Key Fields
    Establish relationships among records in different tables
  10. N:M Relationships
    An instance can occur multiple times in each entity
  11. 1:M Relationship
    Instance can appear once in one entity, and multiple times in another
  12. 1:1 Relationship
    Instance can occur once in each entity
  13. Personal DB
    DBMS and apps run on the same workstation and appear as a single application
  14. Primary Key
    Column that must be unique for each row, identifies that row
  15. Relational Database
    Stores data in a tabular format
  16. Relationship
    Link that shows how different records are related
  17. Second Normal Form (2NF)
    1NF + No partial Dependencies
  18. Sequence
    Lists of numbers the DB automatically generates, guarantees each PK is unique
  19. Surrogate Key
    Artifical PK, made just to be a PK
  20. Third Normal Form (3NF)
    2NF + No Transitive Dependencies
  21. CHAR
    fixed-length up to 2,000 characters
  22. Constraint
    Rules that restrict data values that you can enter into a column
  23. Data Definition Language (DDL)
    • Create new objects
    • Modify/Delete existing objects
  24. Data Manipulation Language (DML)
    Insert/Update/Delete/View Database data
  25. DATE
    Stores dates from 12/31/4712 BC to 12/31/4712 AD
  26. Fixed-Point Number
    • Contains specific number of decimal places
    • (Floating point contains variable number of decimal places)
  27. Integer
    whole number, no digits
  28. Integrity Constraint
    Define Primary, Foreign keys
  29. NUMBER
    • stores negative/positive/fixed/floating numbers between
    • 10^-130 and 10^125
    • Precision up to 38 decimal places
  30. Restricted Action
    can only modify the table in certain situations
  31. SQL
    Query language for relational databases
    stores fractional seconds, century, year, month, day, hour, minute, second
  33. User Schema
    • User's area of the DB
    • Stores all user-created objects
  34. Value constraints
    Specific to the column
  35. VARCHAR2
    Stores variable-length characters up to 4,000 characters
  36. View
    • Object based on an actual table
    • Enables DBMS to present the table in different formats
  37. Action Query
    • DML command
    • inserts/updates/deletes data
  38. Alias
    alternate name for a column
  39. Complex Search Condition
    combines AND, OR, NOT
  40. Group Function
    Performs operation on a group of rows and returns single result
  41. Inner Join
    Joins two tables based on values in one table being equal to those of another
  42. Join
    Combines data from multiple tables using foreign key references
  43. Linesize
    • Specifies how many characters appear on display line
    • Property of SQL*Plus
  44. Nested Query
    Main query and one or more subqueries
  45. Outer Join
    Returns all the rows from one table, plus matching rows from a second table
  46. Pagesize
    How many lines appear on an SQL*Plus page
  47. PseudoColumn
    acts like a column, is actually a command
  48. Query
    DML command that allows users to view data
  49. Savepoint
    • Designates beginning of an individual section of a transaction
    • is a bookmark
  50. Single-Row Functions
    Built-In functions that return a single result for each row of data retrieved 
  51. Table alias
    Alternate name that you assign within the FROM clause
  52. Transaction
    Series of actions that represent a logical unit of work
  53. Truncate
    Removes all of a table's data without saving any rollback info
  54. Composite Variable
    References a data structure that contains multiple scalar variables
  55. Concatenating
    joins two separate strings
  56. Cursor
    Points to a memory location on the database server that the DBMS uses to process a query
  57. Data Structure
    Object containing multiple elements
  58. Declaration section
    begins with DECLARE
  59. Exception
    an unwanted event
  60. Execution Section
    Begins with BEGIN
  61. Explicit Cursor
    Retriesves/Displays data in PL/SQL programs for a query that might receive multiple records or none at all
  62. Function
    • Receives one or more parameters
    • Returns single output value
  63. Implicit Cursor
    points to a context area and parsed representation of the query
  64. Logic Error
    Doesn't stop program, but produces incorrect result
  65. Output Buffer
    Area on the server that stores output values before they're displayed to the user
  66. Parse
    • Separate a single string into two separate strings
    • Using commas or spaces
  67. Posttest Loop
    • Executes one or more program commands
    • THEN evaluates exit condition
  68. Predefined Exception
    most common errors
  69. Procedural Programming Language
    uses detailed, sequential instructions
  70. Scalar Variables
    reference a single value
  71. Reference Variables
    Directly reference a specific column/row and assume its data type
  72. Strongly-Typed Language
    Language in which each variable must be declared before it can be used
  73. Syntax error
    Occurs when command does not follow the guidelines of the programming language
  74. Undefined Exceptions
    • Less common
    • Do not have predefined names
  75. Anonymous PLSQL programs
    do not interact with other program units
  76. Composite Index
    • table that contains multiple sorted columns to identify row location
    • contains up to 16 columns
  77. DB Objects node
    • Top-level node within object navigator
    • not associated with a specific form
  78. DB Table Index
    Contains data values along with corresponding column that specifies physical locations of records
  79. Triggers
    Program units that execute in response to insert/update/delete events
  80. Package
    Code library containing related program units and variables
  81. Package body
    contains commands to create program units that are declared
  82. Package Specification
    Declares package objects: variables, cursors, procedures, functions
  83. Parameter Mode
    Specifies how the program unit can change the parameter value (IN OUT)
  84. PL/SQL Library
    Operating System file that contains code for multiple related procedures and functions
  85. Procedure
    Program unit that can receive multiple parameters and return many or no values
  86. Public Variables
    visible to many programs
  87. Row-Level triggers
    Fire once for each row affected by the triggering statement
  88. ROWID
    specifies internal location of the record in the database
  89. Server-side program units
    stored on server, execute on server
  90. Statement-level trigger
    • Fires once
    • either before or after trigger statement executes
  91. Stored program units
    • other PL/SQL programs can reference them
    • other database users can execute
  92. Trigger Level
    Whether trigger fires once for each row or for the statement
  93. Trigger Statement
    • Defines type of SQL statement that causes a trigger to fire
    • can be on INSERT, UPDATE, or DELETE
  94. Trigger Timing
    Defines whether trigger fires before or after SQL statement executes
  95. Column constraints can be called
    • a. during each column declaration
    • b. at the end of the CREATE table command
  96. Column Constraint declaration

    or at end of create table:

    • CONSTRAINT constraint1 PRIMARY KEY (column1)
    • PRIMARY KEY (column 1, column2)
  97. Foreign Key declaration
    FOREIGN KEY (column1) references table (constraint)
  98. DROP TABLE syntax
  99. RENAME TO syntax
    RENAME table1 TO table2;
  100. Add Columns syntax
    • ALTER TABLE table1 
    • ADD (column NUMBER PRIMARY KEY);
  101. Rename Columns syntax
    • ALTER TABLE table1
    • RENAME COLUMN column1 TO column2;
  102. Change column data declaration syntax
    • ALTER TABLE table1
    • MODIFY (column1 CHAR);
  103. Delete Column
    • ALTER TABLE table1
    • DELETE column column1;
  104. Add or remove constraints syntax
    • ALTER TABLE table1
    • ADD CONSTRAINT name;
    • [DROP CONSTRAINT name];
  105. CURSOR declaration
    • CURSOR cursor1 IS 
    • SELECT *
    • FROM
    • ;
  106. Function/Procedure Declaration
    • (parameter1 IN varchar2)
    • RETURN number
    • IS
    • cnumber number;
    • BEGIN
    • RETURN;
    • END;
  107. Using cursors
    • Open cursor1;
    • Fetch cursor1 into variable1;
    • If cursor1%NOTFOUND then exit;
    • endif;
    • close cursor1;
  108. Creating a view
    • CREATE VIEW view1 AS 
    • SELECT *
    • FROM table;

    DROP VIEW view1;
  109. You can not use triggers on views
  110. Creating Triggers
    • CREATE OR REPLACE TRIGGER orders_before_insert
    • ON table
  111. Dropping a trigger
    DROP TRIGGER trigger1;
  112. Disabling/Enabling a trigger
    • ALTER TRIGGER trigger1 ENABLE;
  113. Enabling all triggers for a table
  114. GROUP BY
    cannot use WHERE, must use HAVING
  115. Creating an index
    CREATE INDEX index1 ON table1 (column1);
Card Set
IT314 Final
IT314 Final
Show Answers