COSC265 Flashcards

Question

CONSTRAINT

Answer 1

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Age INT, CONSTRAINT chk_age CHECK (Age >= 18) );

Answer 2

ALTER TABLE Employees ADD COLUMN PhoneNumber VARCHAR(15); - uses all DDL MODIFY PhoneNumber char(15); - changes types

Answer 3

CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATE DEFAULT CURRENT_DATE );

Answer 4

Operate on a single value and return a single value

Answer 5

_ single % any number including 0

Answer 6

non-correlated means the query can be executed stand-alone as it does not rely on values from the outside query Correlated utilises aliases to allow operations within the nested query that involve relations of the outer query

Answer 7

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Email) VALUES (1, 'John', 'Doe', 25, 'john.doe@example.com');

Answer 8

UPDATE Employees SET Age = 26 WHERE EmployeeID = 1;

Answer 9

CREATE ASSERTION CheckAge CHECK (NOT EXISTS (SELECT * FROM Employees WHERE Age < 18));

Answer 10

has access to each row with the new and old keywords. An example of use is to add Employee-identifiable values to an audit table for each specific event. uses FOR EACH ROW.

Answer 11

does not have access to the data changed. Useful for calculated values like updating salary averages.

Answer 12

CREATE (or REPLACE) trigger name BEFORE/AFTER/INSTEAD OF DELETE/INSERT/UPDATE ON table/view **for each row/statement** **when (condition)** begin PL/SQL block end; /

Answer 13

Confidentiality Someone sees something they shouldn't Integrity threats that result in the manipulation of data Availability makes the database unusable due to it being too slow (eg DDoS), or because it has broken

Answer 14

access control controlling who can see what inference control reducing someone's ability to infer information about a specific person from data available to them - eg: data aggregation in scientific databases flow control controlling what gets in and out of the database such as to prevent the individual from seeing more than what they are supposed to see as well as preventing injection attacks encryption prevents man in the middle

Answer 15

1 - semantics of attributes Each tuple represents attributes relevant to only one entity. 2 - no update anomalies no redundant data or double-ups insertion, update, delete anomalies 3 - null values minimise. Put in other relations 4 - spurious tuples Make sure tables have at least one superkey, and that join conditions are made on a foreign key that is a superkey for its respective table.

Answer 16

when in order to maintain consistency, each (or more than one) tuple has to be found and updated too

Answer 17

Where you want to add an instance of an entity, but the requirement of the tuple dictates you must have another unrelated entity entered - which may not exist

Answer 18

when deleting one entity necessarily deletes values of another entity which you don't want to have deleted

Answer 19

you can separate a table based on a superkey, add new values, delete some values from either table, and they can still come together as a functional table with no spurious tuples

Answer 20

Just like MATH120 X -> Y means each value of X maps to exactly 1 value of Y, but one value for Y can still match with multiple values of X eg: studentID -> studentName studentName -> studentID

Answer 21

t1[x] = t2[x] y ( x t1[y] = t2[y] x->y

Answer 22

x->y t1[x] = t2[x] t1[y] = t2[y] t1[xz] = t2[xz] contradiciton: t1[yz] =/= t2[yz] t1[z] = t2[z] y->z t1[yz] = t2[yz] - contradicts the contradiction

Answer 23

x->y t1[x] = t2[x] t1[y] = t2[y] y -> z t1[y] = t2[y] t1[z] = t2[z] therefore x -> z

Answer 24

x->yz yz->y (reflexive, l1) yz->z (reflexive, l1) x -> y (transitive, l1, l2) x -> z (transitive, l1, l3)

Answer 25

given x->y x->z xz -> yz (augmentation, l1) xy-> yz (augmentation, l2) x -> xy (augmentation, l1) x -> xy -> yz x -> yz (transitive)

Answer 26

given x->y wy->z wx->wy (augmentation) wx->z (transitive)

Answer 27

A set of FDs that has been reduced to remove all equivalent sets. Start with reflexivity. For FDs with multiple attr. on LHS, find closure without attr's one by one. Next check for LHS closures when whole FDs are hidden.

Answer 28

The minimal set of relations built from a minimal cover. minimal meaning combining relations with the same primary key

Answer 29

Atomic, indivisible, unique names for attributes

Answer 30

full functional dependency. Each candidate key as a whole must determine each non-prime attribute. No attributes can be determined by just part of a candidate key

Answer 31

Transitive functional dependency. remove all transitive functional dependencies where LHS is not a candidate key and the RHS is a non-prime attribute.

Answer 32

LHS is always a superkey. Weeds out cases where 3NF was ok because while the LHS was non-prime, the RHS was a prime attribute

Answer 33

pbcd apcd abpd abcp distributed parity - it doesn't matter if the parity block dies on the drive because there is none of that data on that drive. It doesnt matter if the data dies, as it can all be reconstructed with the parity blocks from the other drives

Answer 34

qpcd aqpd abqp pbcq 2 drives can die - distributed

Answer 35

RAID 1 (1234,1234) of RAID 0 (1357, 2468)

Answer 36

Clustered indexes are based on the order the data is stored in - meaning there can only be one. There can be as many non-clustered indexes as you can make from the rest of the attributes note: the attribute used for the order of data can make insertions slower due to having to shift.

Answer 37

order gives the number of children an internal index node can have Pp+V(p-1) <= Block where P is the size of the pointer, V is the size of the index, and p is the number of pointers in the block eg: 6p+9(p-1) <= 512 15p <= 521 p ~= 34

Answer 38

Tablespace (SYSTEM/SYSAUX) datafiles segments (based on tables) extents (collections of blocks)

Answer 39

Where the second transaction updates a value before the first has written it, so the previous value is kept. The update has been lost

Answer 40

one of the transactions reads the value written by another transaction, but that transaction rolls back, unable to correct the transaction that has begun using incorrect data.

Answer 41

aggregate function is applied with incorrect or incomplete data

Answer 42

Atomicity transaction all done in one go Consistency doesn't break any constraints Isolation Other transactions aren't affected by, nor can influence, nor reliant on for simultaneous operation Durability operation gets stored somewhere safe such that can be recreated after a failure

Answer 43

multiple operations executed upon the database as a single logical unit of work

Answer 44

the tree that corresponds to the SQL query before optimisation

Answer 45

a read occurs before rows are added or removed by another transaction, that then give different results when the read occurs again

Answer 46

Create, Read, Update, Delete

Answer 47

abcd abcd abcd and a parity drive

Answer 48

Active, failed, partially committed, committed, terminated

Answer 49

record all read/write operations store on disk undo to backup and redo to current to restart database

Answer 50

ratio of number of tuples that satisfy relation to the total number of tuples in the relation more specific, more efficient

Answer 51

secondary storage access cost storage cost computation cost memory usage cost communication cost

Answer 52

pleaf * (Pr + Vattr) + P <= B Consider the structure - each index has a pointer, and the block has an extra pointer that points down to the next leaf (for aggregate functions, etc)

Answer 53

denoted by +. all attributes determined by the attribute

Answer 54

start with all attr. as superkey remove based on dependencies when smallest, see if prime attributes are determined by other FDs swap and repeat until all found

COSC265 Flashcards

(80 cards)