Databases Flashcards
(38 cards)
What is a cyclic relationship type and what is the part played by roles in the relationship type?
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.
What does it mean to say that r satisfies the functional dependency (FD) X –>Y
When two rows in r have the same X-value they also have the same Y -value.
what does it mean to say that X –>Y holds on R?
When every allowable (legal) relation r over R satisfies X –> Y. Essentially it must apply to real life not just the provided data.
What is embedded SQL statements?
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 */
Why are database operations group into ACID Transactions?
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.
What are the ACID tests?
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.
What is Serialisable transactions?
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.
What causes Anomalies in a Relational DB and how are they avoided?
Anomalies are problems that arise due to bad database design. They can be resolved through Database Normalisation.
Explain what are the different Anomalies?
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.
What are the 3 levels of DB abstraction?
1) Views - what the users see.
2) Logical - based on data model.
3) Physical - how data is stored.
What are the 3 parts of the Data Model?
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
What is an entity? (ERM & ERD)
A “thing” that can be uniquely identified (object).
What is an entity type and how do attributes relate to it? (ERM & ERD)
an entity type is a collection of similar entities (class) and attributes are the properties of the entity type.
What is a Domain in relation to entity types? (ERM & ERD)
A data type.
What is a relationship type and a relationship? (ERM & ERD)
A relationship type is an association between two or more entity types whereas a relationship is an instance of.
What is a cyclic relationship type? (ERM & ERD)
A relationship type between 2 occurrences of the same entity type.
What is a weak entity type? (ERM & ERD)
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).
What are the three types of multiplicity constraints? (ERM & ERD)
Many-to-one, one-to-one, and many-to-many relationship.
What are the 2 participation constraints? (ERM & ERD)
Optional (default) and mandatory relationship.
What is a ISA relationship? (ERM & ERD)
Allows for modelling of generalisation or specialisation hierarchies. For example, one can state that student ISA person. Attributes are inherited down the hierarchy.
What is a super key?
A superkey is a combination of attributes that can be uniquely used to identify a database record.
What is a key?
A super key which is minimal (simple: CNUM, composite: {FNAME, LNAME}).
What is a primary key?
A key that has been designed as such by the designer that we uniquely identify a row.
What is a Natural Join?
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.
