Databases part 2

Question 1

Syntax to create a user defined type & inheritance? (OO SQL)

Answer 1

CREATE TYPE NameType AS (
firstName varchar(20),
lastName varchar(20)
) NOT FINAL; // Allows for sub typing

CREATE TYPE PersonType UNDER NameType (
gender varchar(20),
age int
) FINAL;

Question 2

Syntax to create a Table & inheritance? (OO SQL)

Answer 2

CREATE TABLE person OF PersonType;

CREATE TABLE student OF UNDER person;

Question 3

Syntax an array? (OO SQL)

Answer 3

authors varchar(20)[10]

Question 4

Syntax an multiset? (OO SQL)

Answer 4

keywords varchar(20) multiset

Question 5

What are the 3 ways to combine SQL with programs?

Answer 5

Stored procedures, interface libraries and embedded SQL.

Question 6

Syntax for dynamic SQL in embedded SQL?

Answer 6

EXEC SQL PREPARE queryName FROM ;

EXEC SQL EXECUTE queryName;

// or you can execute in one statement:
EXEC SQL EXECUTE IMMEDIATELY ;

Question 7

Syntax for PHP/PDO query?

Answer 7

$db = new PDO($host, $user, $pw);
$result = $db->query("query text");
$index1 = $result->fetchColumn(0);
$index2 = $result->fetchColumn(1);

// to iterate over the results use:
while($row = $result->fetch()){
      echo '';
      for($row as $cell){
             echo " $cell ";
      }
      echo '';
}

Question 8

Syntax for PHP/PDO Anonymous Placeholder query?

Answer 8

$query = "SELECT * FROM Sells WHERE pub=? AND beer=?"
$stmt = $bd->prepare($query);
$pub = $_GET('pub'); $beer = "Coors";
$stmt->execute(array($pub, $beer));
$rows = $stmt->fetch();

Question 9

Syntax for PHP/PDO Named Placeholder query?

Answer 9

$query = "SELECT * FROM Sells WHERE :pub AND :beer"
$stmt = $bd->prepare($query);
$pub = $_GET('pub'); $beer = "Coors";
$stmt->bindParam(:pub, $pub);
$stmt-> bindParam(:beer, $beer);
$rows = $stmt->fetch();

Question 10

3NF rules?

Answer 10

1 - Is the left hand side a superkey?
2 -If not a super key, is right hand side prime ( prime = attribute is part of key (not super key).

If not, violates 3NF.

Question 11

BCNF rule?

Answer 11

R is in BCNF wrt F if for every FD X -> Y, X is a super key.