Lecture 9+10

Question 1

What are prime and non prime attributes?

Answer 1

Prime: a member of some candidate key
Nonprime: is not a member of any candidate key.

Question 2

What are the main parts of normal forms?

Answer 2

1NF: all attribute values are atomic.
2NF: 1NF and every non-key attribute is fully dependend on the primary key.
3NF: 2NF and every non-key attribute is non-transitively dependent on primary key.
BCNF: Every LHS of a functional dependency is a candidate key.
4NF: No multivalued dependencies
5NF: Every join dependency is a consequence of candidate keys.

Question 3

How do we normalise to 1NF?

Answer 3

Remove multivalued or composite attribute and create a new relation for it. Add a copy of primary key of original relation and then determine primary key of new relation.

Question 4

What is a partial functional dependency?

Answer 4

A functional dependency which can lose some attribute from the LHS can be removed from the LHS and the dependency holds.

Question 5

How do we normalise to 2NF?

Answer 5

Remove nonprime attributes that are only partially functional dependent on primary key, place them in a new relation, this relation should contain the determinants of the non prime attributes and become the primary key.

Question 6

What is transitive dependency?

Answer 6

A functional dependency is transitive if there are a set of attributes in the relation that are neither a candidate key or a subset of a key of the relation and the LHS of the functional dependency can determine these, and these attributes can determine the RHS of the functional dependency.

Question 7

How do we normalise to 3NF?

Answer 7

Remove nonprime attributes which are transitively dependent on the key attributes, and place in a new relation.
Add a copy of the determinant attributes of these attributes, which will become the primary key of this new relation.

Question 8

What is BCNF?

Answer 8

whenever a nontrivial functional dependency holds in R the LHS is a superkey of R(remove functional dependencies that go backwards).

Question 9

What is PL/SQL?

Answer 9

Adds programming capabilities to SQL, adding control structures, data abstraction, information hiding, exception handling, enforcement of complex constraints, and allowing subprograms to be stored in the database to allow for storage in executable form for better performance.

Question 10

How is PL/SQL structured?

Answer 10

Into blocks, each of which generally corresponds to a problem or subproblem.
Anonymous blocks are program units with no name, they are compiled each time they are executed.
Stored subprograms are named units, each is compiled separately and permanently in an oracle database, they can be procedures(no return value), or functions(return a value).

Question 11

What is the structure of an anonymous block?

Answer 11

[DECLARE 
... optional declaration statements ...]
BEGIN
...excutable statements
[EXCEPTION ... optional exception handler statements ...]
END;
/

Question 12

How are variables defined?

Answer 12

;
variables can have attributes, with a % sign denoting an attribute, with .%TYPE giving the datatype from the table and %ROWTYPE provides a record type representing a row in the table.

Question 13

How can we do loops in PL/SQL?

Answer 13

simple loops using LOOP and using EXIT and END LOOP at end(or EXIT WHEN ).
A while loop using WHILE LOOP and ending with END LOOP;
A FOR loop using FOR counter in lower..uppper LOOP
ending with END LOOP.
A CURSOR for loop is one actually defined by an explicit cursor.

Question 14

How do user defined exceptions work?

Answer 14

Must be delcared in declarative part of PL/SQL block, and then raised explicitly using RAISE statement.

Question 15

What is the syntax of a procedure?

Answer 15

CREATE [OR REPLACE] PROCEDURE name
IS|AS
 /* declaration section */
BEGIN
/* executable section - required */
[EXCEPTION
 /*Error handling statements
END [name];
/

Question 16

What parameter modes can be specified for procedures?

Answer 16

IN(default) passes values to procedure being called, value vannot be changed inside procedure(constant).
OUT, return values to the caller of procedure, value can be changed or referenced in any way in procedure.
IN OUT pass initial values to procedure and return updated values to called, value can be changed inside procedure.

Question 17

How are functions different from procedures?

Answer 17

Functions have a RETURN clause after the CREATE section, detailing the datatype to return.

Question 18

What is a package?

Answer 18

A package collects a group of related procedures and functions.