Week 11

Question 1

Steps to Create the Relational Schema within Database Management System. (Metadata)

Answer 1

Create the database
B. Create the table(s)
i. Order matters – start with tables that do not have any foreign keys

Question 2

Lookup Table

Answer 2

Also known as Reference table
§ Used to minimize storage of repetitive values
§ Ensures sure that only certain values are allowed
§ Helpful to create drop-down list of options in front-end interface

Typical use:
§ `Store a code in the “main” table
§ Store the code and full value in the

Question 3

Additional Data Types

Answer 3

TINYINT
BOOLEAN - Not directly implemented in MySQL, instead TINYINT(1); 0=False; 1=True;

ENUM: Allows a fixed number of pre-defined values to be specified

Question 4

Implementing a foreign key

Answer 4

Enforces referential integrity: can’t add a value to a foreign key, unless that value exists for the foreign key back in it’s “native” table.

Two step process in a CREATE TABLE statement:
Add an attribute specification for each foreign key attribute
§ Datatype must be consistent
§ Refer to E-R diagram to determine if a NOT NULL constraint is needed for the foreign key attribute(s)

Add a foreign key constraint for each foreign key

Syntax:
CONSTRAINT table_attr(s)_fk FOREIGN KEY (attr(s)) REFERENCES native_table(attr(s))

For 1:1 or 1:N (N:1) recursive relationships: Foreign key constraint will reference the same table’s primary key

For supertype/subtype relationships – Foreign key constraint in subtype will reference supertype

Question 5

Properties of a Transaction

Answer 5

Atomicity
Consistency
Isolation
Durability

Question 6

Transaction in MySQL

Answer 6

By default, each statement is a transaction, can be temporarily overridden by START TRANSACTION.

START TRANSACTION;
UPDATE savings
SET balance = balance - 100
WHERE account = ‘S123’;
UPDATE checking
SET balance = balance + 100
WHERE account = ‘C123’;
COMMIT;

Question 7

Commit

Answer 7

The COMMIT statement defines the end of the transaction and writes the changes to the
database

In MySQL, an implicit commit is performed when a DDL statement is executed with an
in-process transaction

Question 9

Atomic

Answer 9

either all or none of the operations are executed and applied to the database. Partial or incomplete results are rolled back, and the database returns to its state prior to execution of the transaction.

Question 10

Consistent

Answer 10

all rules governing data are valid when the transaction is committed. Completed transactions that violate any rules are rolled back.

Question 11

Isolated

Answer 11

processed without interference from other transactions. Isolated transactions behave as if each transaction were executed one at a time, or serially, when in fact the transactions are processed concurrently.