Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Study > COSC265 > Flashcards

COSC265 Flashcards

1
Q

Parametric end user

A

uses existing queries - don’t create their own

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Casual end user

A

not particularly knowledgeable, will use query builders and UI tools

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

sophisticated end user

A

is able to make complex SQL queries

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

stand-alone end user

A

Uses their own database for their own means

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

structural component

A

the relations, the cardinality, the degree, the keys

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

integrity component

A

applying correct use of primary and foreign keys, as well as the implementation of constraints in order to ensure that data can only be entered if it is valid and correct given the context of the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

operational component

A

CRUD operations, SQL queries, implementing transactions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

DDL

A

Data definition language

Defines and manages objects

CREATE, ALTER, DROP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

VDL

A

View definition language

not a formal language

Is just used to create views

uses both DDL and DML components

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

DML

A

Data manipulation language

manages the data itself

SELECT, INSERT, UPDATE, DELETE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

phases of database design

A

requirements collection and analysis

conceptual design

logical design

physical design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

phases of ER modelling

A

Identify entities and attributes

identify relationships and attributes thereof

examine for problems and repeat

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

degree

A

the number of attributes in a relation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

cardinality

A

the number of tuples in a relation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

domain constraint

A

part of integrity component of RDM

Defining the valid data for an attribute. Type and size. eg: varchar(20)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

key constraint

A

part of integrity component of RDM

applies uniqueness rules to keys, preventing integrity errors. Also requires keys be irreducible

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

entity integrity constraint

A

part of integrity component of RDM

ensures each table has exactly one primary key, of which meets key constraints whilst also being not null

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

referential integrity constraint

A

part of integrity component of RDM

ensures there are no foreign key values that do not match a value in the referenced table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

business constraints

A

part of integrity component of RDM

business logic such as salary rules for managers and employees

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Division

A

smaller table must be proper subset
attribute names must be the same.

get all tuples (excluding the attributes from the subset) of which the ID has instances for each item in the subset.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

union compatible operations

A

UNION is where you combine the values of two relations to make a single big relation, so therefore union compatibility requires both relations to have the same degree, data types, and order

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

When can updating a view update the underlying database

A

When the view is based on a simple
select X,Y,Z from table where condition;

You can also update attributes in the underlying table that aren’t in the view, so long as the selection is made based on the attribute in the view

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

EER -> RM

A

ER:
make tables for all,
weak entities = 1:m,
Binary - total participation,
1:m - m
m:n - new
multivalue = 1:m
Nary - new

EER:
one table with flags,
TP + D can be multiple tables
multiple inheritance - choose one, flag other
union: 1:m

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

DOMAIN

A

CREATE DOMAIN AgeType AS INTEGER CHECK (VALUE >= 0 AND VALUE <= 120);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

CONSTRAINT

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

ALTER

A

ALTER TABLE Employees
ADD COLUMN PhoneNumber VARCHAR(15); - uses all DDL

MODIFY PhoneNumber char(15); - changes types

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

DEFAULT

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

scalar functions

A

Operate on a single value and return a single value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

LIKE

A

_ single
% any number including 0

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

correlated/non-correlated nested queries

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

INSERT

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

UPDATE

A

UPDATE Employees
SET Age = 26
WHERE EmployeeID = 1;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

ASSERTION

A

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

34
Q

row-level triggers

A

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.

35
Q

statement level triggers

A

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

36
Q

trigger creation

A

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;
/

37
Q

CIA triad

A

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

38
Q

control measures

A

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

39
Q

informal normalisation guidelines

A

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.

40
Q

Update anomaly

A

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

41
Q

insertion anomaly

A

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

42
Q

delete anomaly

A

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

43
Q

lossless join

A

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

44
Q

functional dependency

A

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

45
Q

reflexive

A

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

46
Q

augmentation

A

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

47
Q

transitive

A

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

48
Q

decomposition

A

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

49
Q

union inference rule

A

given
x->y
x->z

                xz -> yz (augmentation, l1)
                xy-> yz (augmentation, l2)
                x -> xy (augmentation, l1)
                x -> xy -> yz
                x -> yz (transitive)
50
Q

pseudotransitivity

A

given
x->y
wy->z

wx->wy (augmentation)
wx->z (transitive)

51
Q

minimal cover

A

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.

52
Q

minimal set

A

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

53
Q

1NF

A

Atomic, indivisible, unique names for attributes

54
Q

2NF

A

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

55
Q

3NF

A

Transitive functional dependency.

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

56
Q

BCNF

A

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

57
Q

RAID 0

A

1357 2468

58
Q

RAID 1

A

1234 1234

59
Q

RAID 5

A

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

60
Q

RAID 6

A

qpcd aqpd abqp pbcq

2 drives can die - distributed

61
Q

RAID 10

A

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

62
Q

clustered and non-clustered indexes

A

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.

63
Q

B+ tree order formula

A

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

64
Q

physical and logical units in oracle

A

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

65
Q

lost update

A

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

66
Q

dirty read/temporary update

A

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.

67
Q

incorrect summary

A

aggregate function is applied with incorrect or incomplete data

68
Q

ACID

A

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

69
Q

transaction

A

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

70
Q

canonical tree

A

the tree that corresponds to the SQL query before optimisation

71
Q

phantom read

A

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

72
Q

CRUD

A

Create, Read, Update, Delete

73
Q

RAID 4

A

abcd abcd abcd and a parity drive

74
Q

Transaction states

A

Active, failed, partially committed, committed, terminated

75
Q

system log operations

A

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

76
Q

selectivity

A

ratio of number of tuples that satisfy relation to the total number of tuples in the relation

more specific, more efficient

77
Q

Costs of query execution

A

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

78
Q

order of B+ tree leaf

A

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)

79
Q

closure

A

denoted by +. all attributes determined by the attribute

80
Q

how do you find candidate keys?

A

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

Study (3 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions