Brainscape's Knowledge GenomeTM

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


See full index

Relational Databases > Databases > Flashcards

Databases Flashcards

1
Q

What is a cyclic relationship type and what is the part played by roles in the relationship type?

A

A cyclic relationship type is a relationship type between two occurrences of the same entity type. With each entity type in a cyclic relationship type we associate a role, represented by labels on lines in an ERD. For example, roles are specified by “husband” and “wife” labels.

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

What does it mean to say that r satisfies the functional dependency (FD) X –>Y

A

When two rows in r have the same X-value they also have the same Y -value.

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

what does it mean to say that X –>Y holds on R?

A

When every allowable (legal) relation r over R satisfies X –> Y. Essentially it must apply to real life not just the provided data.

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

What is embedded SQL statements?

A

EXEC SQL BEGIN DECLARE SECTION;
char theBar[21], theBeer[21];
float thePrice;
EXEC SQL END DECLARE SECTION;

/* obtain values for theBar and theBeer */

EXEC SQL SELECT price INTO :thePrice
FROM Sells
WHERE bar = :theBar
AND beer = :theBeer;

/* do something with thePrice */

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

Why are database operations group into ACID Transactions?

A

ACID is a set of properties that guarantee that database transactions are processed reliably. Refers to multiple users querying at any one time or multiple similar queries on the same data at any one time or if software/hardware failures leave data inconstancies.

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

What are the ACID tests?

A

Atomicity - transactions should execute completely or appear not to have run at all.
Consistency - the constraints specified in the database must be preserved.
Isolation - each transactions should appear to execute as if there are no others.
Durability - once a transaction completes, it effect should never be lost.

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

What is Serialisable transactions?

A

Transactions must behave as if they had been run serially (one after another - no overlap). Must be specified by programmer and transactions grouped into one.

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

What causes Anomalies in a Relational DB and how are they avoided?

A

Anomalies are problems that arise due to bad database design. They can be resolved through Database Normalisation.

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

Explain what are the different Anomalies?

A

1 - Update Anomalies - exists when one or more instances of duplicated data is updated, but not all.
2 - Delete Anomalies - exists when certain attributes are lost because of the deletion of other attributes.
3 - Insert Anomalies - occurs when certain attributes cannot be inserted into the database without the presence of other attributes.
4 - Redundancy problems - no unnecessary information redundancy.

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

What are the 3 levels of DB abstraction?

A

1) Views - what the users see.
2) Logical - based on data model.
3) Physical - how data is stored.

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

What are the 3 parts of the Data Model?

A

1) Structural part (tables) - relations
2) Integrity part (constraints) - keys (entity integrity) & foreign keys (referential integrity)
3) Manipulative part (access data) - SQL or Relational Algebra

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

What is an entity? (ERM & ERD)

A

A “thing” that can be uniquely identified (object).

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

What is an entity type and how do attributes relate to it? (ERM & ERD)

A

an entity type is a collection of similar entities (class) and attributes are the properties of the entity type.

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

What is a Domain in relation to entity types? (ERM & ERD)

A

A data type.

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

What is a relationship type and a relationship? (ERM & ERD)

A

A relationship type is an association between two or more entity types whereas a relationship is an instance of.

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

What is a cyclic relationship type? (ERM & ERD)

A

A relationship type between 2 occurrences of the same entity type.

17
Q

What is a weak entity type? (ERM & ERD)

A

An entity type that does not have sufficient attributes to form a primary key. Requires an owner to form a primary key (i.e. NI of parent, for Child).

18
Q

What are the three types of multiplicity constraints? (ERM & ERD)

A

Many-to-one, one-to-one, and many-to-many relationship.

19
Q

What are the 2 participation constraints? (ERM & ERD)

A

Optional (default) and mandatory relationship.

20
Q

What is a ISA relationship? (ERM & ERD)

A

Allows for modelling of generalisation or specialisation hierarchies. For example, one can state that student ISA person. Attributes are inherited down the hierarchy.

21
Q

What is a super key?

A

A superkey is a combination of attributes that can be uniquely used to identify a database record.

22
Q

What is a key?

A

A super key which is minimal (simple: CNUM, composite: {FNAME, LNAME}).

23
Q

What is a primary key?

A

A key that has been designed as such by the designer that we uniquely identify a row.

24
Q

What is a Natural Join?

A

Combines records from two or more tables in a database where all pairs of attributes having the same name are paired, one of each is projected. If no match for a tuple was found the tuple is lost. Outer join however will return null instead of omitting them from projection.

25
Q

What is a Full Outer join?

A

Combines records from two or more tables in a database by including tuples from each relation, padded with null values, where no matching tuples exist.

26
Q

What is a Left Outer Join?

A

Combines records from two or more tables in a database by including tuples from the left relation padded with null values, where no matching tuples exist. Refers to the location left of the join.

27
Q

What is a Right Outer Join?

A

Combines records from two or more tables in a database by including tuples from the right relation padded with null values where no matching tuples exist. Refers to the location right of the join.

28
Q

What is a View?

A

Decomposes the high-level logical view into access rights views. Views are defined by queries and are not precomputed. Views cannot be updated as they are considered ambiguous (i.e. tables are unknown) but may if certain conditions apply.

29
Q

What are the 3 transaction commands?

A

1) START TRANSACTION - used to mark the start of a group of transactions.
2) COMMIT - causes the transactions to be made permanent.
3) ROLLBACK - aborts and reverts.

30
Q

What is a Dirty Read?

A

Data that is written but not yet committed and then read by another query.

31
Q

What are the 3 isolation levels defined by SQL?

A

1) Serialisable - this is the highest isolation level.
2) Repeatable reads
3) Read committed
4) Read uncommitted - dirty reads allowed

32
Q

What are integrity constraints?

A

Integrity constraints are logical statements that restrict the set of allowable relations in a BD.

33
Q

What are the 4 types of Integrity Constraints, and what are they?

A

1) Inclusion Dependencies - Values must exist. Foreign keys are a special case.
2) Data and Domain Dependencies - Restricting to an equality or inequality.
3) Cardinality Dependencies - Limits the number of occurrences.
4) Functional Dependencies - X functionally determines Y (X -> Y). Each X has one Y. Usually reflects real world constraints. Note: Keys are a special case of FD.

34
Q

What does the Closure(X, F) Algorithm?

A

Closure(X, F) is the set Y of all attributes such that X functionally determines Y.

35
Q

Why would a non-BCNF decomposition not be considered a good decomposition?

A

The decomposition would be lossless, i.e., the

join of R1 and R2 might return tuples not in the original R from which R1 and R3 were decomposed.

36
Q

What is the difference between Outer join, Right, Left and Full Outer join?

A

The OUTER JOIN operator in SQL allows rows from one table that would be excluded in a normal join because they don’t match rows in the other table to be included in the join, padded with null values. The LEFT OUTER JOIN includes rows from the left table that do not join, the RIGHT OUTER JOIN includes those from the right table, and the FULL OUTER JOIN includes rows from both that do not
join.

37
Q

What is the 1NF?

A

First normal form is the requirement that all values in relations be atomic as far as queries are concerned.

38
Q

An SQL view is updatable if the query defining the view satisfies the following?

A

1 - The from clause has only one database relation.
2 -The select clause contains only attribute names of the relation (no expressions, aggregates, or distinct).
3 - Any attribute not listed in the select clause can be set to null.
4 - There is no group by or having clause.

Relational Databases (2 decks)

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