Test 2 objective sheet

 

 

Refer to your syllabus for chapter references.

 

Integrity /Constraints/triggers/stored procedures/cursors

v       What is referential integrity?

It is also stated as foreign key constraint: Defines a relationship between two tables in which the domain (set of allowable values) in a column of one table is defined by the set of values contained in the primary key column or columns of another table. For example, Oracle will not allow a value in the customer ID column of a customer payments table unless that customer ID exists in the customer table. Null values may be allowed if desired.

 

v       What are constraints and why are they important.

Constraints define the rules that insure the integrity of data in the database.

Because constraints are part of the database definition they reduce the amount of validation that must be done at the application level and provide an integrity defense against poorly written application programs and inconsistent data entry.

 

v       What are the five types of Oracle constraints?

Primary Key constraints (PK)Foreign Key constraints (FK)Unique Key constraints (UK)Check Constraints (CC)Not Null Constraints (NN)

 

v       Know the syntax for creating oracle PK, FK, and CC constraints including those involving composite keys.

(1) CREATE TABLE STUDENT

(SID CHAR(6),

LASTNAME VARCHAR2(20),

FIRSTNAME VARCHAR2(20),

CONSTRAINT Student_SID_PK PRIMARY KEY(SID));

(2) CREATE TABLE STUDENT

(SID CHAR(6),

SMAJOR VARCHAR2(4),

LASTNAME VARCHAR2(20),

FIRSTNAME VARCHAR2(20),

CONSTRAINT Student_Smajor_FK FOREIGN KEY(SMAJOR)

REFERENCES MAJOR(MAJORID));

(3) CREATE TABLE STUDENT

(SID CHAR(6),

LASTNAME VARCHAR2(20),

FIRSTNAME VARCHAR2(20),

CONSTRAINT Student_Status_CC CHECK(STATUS=’PT’ or STATUS=’FT’or STATUS=’NE’);

 

v       Related to the above, know the syntax for creating and dropping tables.

DROP TABLE tablename;

 

v       Match constraint types to their definitions or give a brief description of a constraint type.

(1) Primary Key constraints (PK): Defines the primary key of a table. Oracle will not allow a primary key value to be null and will not allow two rows in the table to have the same primary key value. For example, no two students would be allowed to have the same student ID number. A table can have only one primary key constraint.

(2) Unique Key constraints (UK): Primarily used to enforce uniqueness in a candidate key. A candidate key is a column or columns that could have been used as the primary key but was not, but were uniqueness should still be enforced. For example, RU uses a six digit student ID as the primary key. If only a primary key constraint were defined than the database would fail to reject duplicate entries for social security number. By defining a Unique Key constraint on social security number no students would be allowed to have the same social security number. Null values may be allowed if desired. Unique key constraints are often used to enforce uniqueness in natural composite keys when a token key is being used as the Primary Key to avoid the natural composite key.

(3) Check Constraints (CC): A check constraint allows an entered value to be “checked” against a set of defined conditions. For example we may check to see that a student grade point average is between 0 and 4.0, or that the hours worked in a week by an employee is between 0 and 60, or that the answer to a question is either yes or no. Check constraints may involve more than one column. Null values may be allowed if desired.

(4) Not Null Constraints (NN): When a not null constraint is defined on a column Oracle requires the entry of some value into that attribute of the row. The Not Null Constraint is automatically defined for primary keys.

 

v       Given a business rule indicate which type of constraint would be appropriate to enforce the rule

See HW 3

 

v       What makes constraints superior to embedding business rules in application code?

Because constraints are part of the database definition they reduce the amount of validation that must be done at the application level and provide an integrity defense against poorly written application programs and inconsistent data entry.

 

v       What is a stored procedure?

procedures and functions can be stored in the database server for other application programs (in addition to PL/SQL programs) to use.

 

v       What is a trigger and why are they important?

Trigger is a SQL mechanism that automatically executes a specified PL/SQL block(called trigger action) when a database event such as Insert, Delete or Update occurs on a table. Triggers are useful for maintaining integrity constraints that is not able to be done by 5 constraints (PK,FK,UK,NN,CK) and for creating auditing information such as recording the username who updates the grade field of the Students table by maintaining 2 global variables :new and :old. (:new is used to store the new row when using update or insert - use :new.fieldname to access each field data that you intend to insert or update- and :old is used to store the old row when using update or delete.)

 

v       What is the difference between a stored procedure and a trigger?

Trigger fired automatically when DML events happen. Stored procedures needs to be called by application program.

 

v       Where are triggers and stored procedures stored?

Databse server

 

v       Where are PL/SQL scripts stored?

User’s workstation

 

v       How are triggers and constraints similar?

Both constraints and triggers are stored in the database tables,  they reduce the amount of validation that must be done at the application level and provide an integrity defense against poorly written application programs and inconsistent data entry.

business rules too complex for these constraints must be enforced via Triggers or Application Code.

 

v       What does the FOR EACH ROW option of the trigger syntax do?

It applies to every row in the table

 

v       What does :NEW and :OLD: refer to within the code of an update trigger?

:new is the newly inserted or updated row

:old is the old row that is deleted or updated

 

v       What makes triggers a superior location for certain functions than application code?

Used for logging the malicious attempt on database table and guard against the database integrity

 

v       What causes a trigger to fire?

DML events

 

v       what is a sequence?

User can create a sequence object to represent a surragate key (or token key) object and store it in the database to use.

 

v       How is a sequence different than an autonum field in Access?

Sequence is an object which includes a lot of methods

 

v       Where are sequences frequently used

Used for token key

 

v       What is a cursor and why are they used.

Cursor: a pointer that points to the memory location that contains the SQL command's context area(# of rows processed,parsed command statement and set of data rows returned by the query). You may think it as a pointer to the record set resulting from querying a database and used for processing one or more rows.

•          A cursor is a private set of records

•          An Oracle Cursor = VB recordset = JDBC ResultSet

 

v       What are the four steps needed to implement a cursor and what does each step do.

Declare the cursor: defines memory to be used

Open the cursor: open the connection to database server and execute the SQL statement  and store the result in cursor

Fetch a row: get one row from the cursor

Close the cursor: close connection to database server

 

v       What are the four parts of a PL/SQL block?

(1) PROCEDURE or Function definition

(2) DECLARE

(3) BEGIN

        -statements

 

END

 (4) EXCEPTION

 

v       Be able to read and comment on a PL/SQL program, trigger, or stored procedure

CREATE OR REPLACE TRIGGER SalaryTrig

  BEFORE INSERT ON Employees

  FOR EACH ROW

DECLARE

  v_upper_sal_limit NUMBER(10,2);

  v_lower_sal_limit NUMBER(10,2);

BEGIN

  SELECT MIN(salary)*10 INTO v_upper_sal_limit

    FROM employees;

  SELECT MAX(salary)/10 INTO v_lower_sal_limit

    FROM employees;

  IF :new.salary NOT BETWEEN v_lower_sal_limit AND v_upper_sal_limit THEN

    RAISE_APPLICATION_ERROR(-20001,'salary out of allowed range');

  END IF;

END SalaryTrig;

/

 

 

 

DESIGN

v       Given a set of tables and data identify one-to-one, one-to-many, and many-to-many relationships

v       Give examples of or describe unary relationships

v       Identify and/or describe primary keys

v       Identify and/or describe foreign keys

v       Identify and/or describe unique keys

v       Distinguish between primary keys, candidate keys, and unique keys

v       Distinguish between natural and token keys

v       List advantages and disadvantages of using a token key

v       Distinguish between simple and composite keys

v       Indicate the minimum number of tables necessary to implement a given ERD

v       Sketch a set of RDBMS tables for a given ER diagram

v       Draw an ER diagram given a brief narrative

v       Be able to apply or identify generalization (subtypes and supertypes) and be able to answer related questions on disjointness and completeness

v       Describe/Sketch implementation choices for an ERD with subtypes and supertypes

HW 5

Tables Form

 

Normalization

v       Identify violations of 1st, 2nd, 3rd, BCNF, and 4th normal forms

v       Correct a normal form violation through lossless decomposition

v       Describe the update, insertion, and deletion anomalies that occur in relations which are not normalized

v       Discuss the purpose of normalization

v       Discuss the limits of normalization

 

HW 6

Nomalization Example