Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
72 Cards in this Set
- Front
- Back
|
What is a DBMS?
|
Database Management System is a software package designed to store and manage databases
|
|
|
What is a database?
|
a very large integrated permanent collection data
|
|
|
What is a data model?
|
A collection of concepts for describing data.
|
|
|
What is a schema
|
A description of a particular collection of data, using the given data model and its data definition language.
|
|
|
Why use a DBMS?
|
Data independence and efficient access.
Reduced application development time Data integrity (enforce constraints) and security. Uniform (central) data administration Concurrent access, recovery from crashes. |
|
|
What is Logical data independence?
|
Protection from changes in logical structure of data.
|
|
|
What is Physical data independence?
|
Protection from changes in physical structure of data (e.g. sorting, indexing, compressing).
|
|
|
What do you use to define schemas?
|
Data Description Language DDL
|
|
|
What do you use to modify/query data?
|
Data Manipulation Language DML
|
|
|
What problems can concurrent controls of different users cause?
|
Interweaving actions of different user programs can lead to inconsistency. Check is cleared while account balance is being computed.
|
|
|
What is a conceptual schema?
|
A schema that defines logical structure.
|
|
|
What is a physical schema?
|
A schema that describes the files and indexes used.
|
|
|
What is atomicity? Describe how DBMS ensure atomicity.
|
All or nothing. DBMS ensures atomicity even if system crashes in the middle of a transaction. Keeps a log of all actions carried out by the DBMS while executing a set of transactions.
Before a change is made to the database, the log entry is forced to a safe location (Write-ahead log, or WAL protocol) After a crash, the effects of partially executed transactions are undone using the log. |
|
|
How does the DBMS regulate the scheduling of concurrent transactions?
|
Before reading/writing an object, a transaction requests a lock on the object, and waits till the DBMS gives it the lock. All locks are released at the end of the transaction.
|
|
|
What does a typical DBMS layered architecture contain?
|
Query Optimization and Execution
Relational Operators Concurrency Control Recovery Components Files and Access Methods Buffer Management Disk Space Management |
|
|
What is an entity?
|
An entity is described in DB using a set of attributes. Real-world object distinguishable from other objects.
|
|
|
What is an Entity set?
|
A collection of similar entities. E.g. , all employees
All entities in an entity set have the same set of attributes. Each entity set has a key. Each attribute has a domain. |
|
|
What is a relationship?
|
Association among two or more entities. E.g., Attishoo works in Pharmacy department.
|
|
|
What is a relationship set?
|
Collection of similar relationships.
An n-ary relationship set R relates n entity sets E |
|
|
What is a typle?
|
a list with n elements in order.
<a1, a2, ..., an> |
|
|
What is an ordered pair?
|
A binary tuple <a, b>
|
|
|
What is a Participation Constraint?
|
Every department has a manager. The participation of Departments in Manages is said to be Total (vs. partial)
|
|
|
What is a weak entity?
|
A weak entity can be identified uniquely only by considering the primary key of another (owner) entity.
Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities). Weak entity set must have total participation in this identifying relationship set. |
|
|
Describe ISA hierarchies.
|
If A ISA B, then every A entity is also considered to be a B entity.
ie. Employee ISA Hourly_Emps or Contract_Emps Overlap Constraint: Joe can't be Hourly_Emps as well as a Contract_Emps. Covering constraints: Every employee entity has to be an Hourly_Emps or a Contract_Emps. |
|
|
What is Aggregation?
|
Allows us to treat a relationship set as an entity set for purposes of participation in other relationships.
Use "Monitors" |
|
|
What is a relational database?
|
A set of relations.
|
|
|
What is a relation?
|
Made up of 2 parts:
Instance: a table, with rows and columns #rows = cardinality #fields = degree Schema: specifies name of relation, plus name and type of each column. |
|
|
How do you destroy a relation?
|
DROP TABLE Students
|
|
|
How do you add a new field in a schema?
|
ALTER TABLE Students
ADD COLUMN firstYear: integer |
|
|
How do you insert a single tuple?
|
INSERT INTO Students(side, name, logic, age, gpa)
VALUES(12, 'Gee', 'g@ee', 18, 3.2) |
|
|
What are integrity constraints?
|
conditions that must be true for any instance of the database
ICs are specified when schema is defined. ICs are checked when relations are modified. |
|
|
What is a legal instance of a relation?
|
one that satisfies all specified Integrity Constraints.
|
|
|
When is a set of fields a candidate key for a relation?
|
No two distinct tuples can have same values in all key fields.
There could be more than 1 candidate key. Designate one of them to be the primary key. |
|
|
What is a foreign key?
|
set of fields in one relation that is used to refer to a tuple in another relation.
|
|
|
How is referential integrity achieved?
|
No dangling references. If all foreign key constraints are enforced.
|
|
|
Consider the following:
CREATE TABLE Enrolled(sid CHAR(20, cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid, cid), FOREIGN KEY(sid) REFERENCES Students) What should be done if an enrolled tuple with a non-existent student id is inserted? What should be done if a Students tuple is deleted? |
reject it.
Also delete all Enrolled tuples that refer to is. Disallow deletion of a Students tuple that is referred to. Set sid in Enrolled tuples that refer to it to a default sid. add to the end: ON DELETE CASCADE ON UPDATE SET DEFAULT |
|
|
How do you capture participation constraints (total) involving one entity set in a binary relationship?
|
Set foreign key to NOT NULL
Set foreign key to ON DELETE NO ACTION |
|
|
How do you capture weak entity sets?
|
NOT NULL
ON DELETE CASCADE |
|
|
What is a view?
|
a relation, but we store a definition rather than a set of tuples.
|
|
|
How do you create a view?
|
CREATE VIEW blah(a, b)
AS SELECT S.a, E.b FROM Students S, Enrolled E WHERE S.sid = E.sid and S.age < 21 |
|
|
How can you delete a view?
|
DROP VIEW
|
|
|
What are query languages?
|
Language that allows manipulation and retrieval of data from a database. It is applied to relation instances and the results of a query is also a relation instance.
|
|
|
Describe relational algebra and relational calculus.
|
Relational Algebra is more operational, very useful for representing execution plans. Relational Calculus lets useres describe what they want, rather than how to compute it.
|
|
|
Describe the follow relational algebra operations:
δ ᴨ X ‒ U |
δ: Selection. Selects a subset of rows from relations.
ᴨ: Projection. Deletes unwanted columns from relation. X: Cross-products. Allows us to combine two relations. ‒: Set-difference. Tuples in reln. 1, but not in reln. 2. U: Union. Tuples in reln. 1 and in reln. 2 |
|
|
Describe the follow relational algebra operation: ⋈
|
Conditional Join.
R ⋈c S = δc (R X S) |
|
|
What is Equi-join?
|
A special case of condition join where the condition c contains solely of equalities.
S ⋈sid R |
|
|
Describe Division.
|
A/B contains all x tuples such that for every y tuple in B, there is an xy tuple in A.
|
|
|
Describe relation-list, target-list, and qualification.
|
SELECT target-list
FROM relation-list WHERE qualitification |
|
|
What is DISTINCT?
|
An optional keyword indicating that the answer should not contain duplicates. Default in SQL is that duplicates are not eliminated.
|
|
|
What are the operations of "=" and "AS"?
|
Two ways to name fields in result.
age = age - 5 age AS age -5 |
|
|
What is the operation of "LIKE"?
|
used for string matching.
S.name LIKE 'B_%B' '_' stands for any one character '%' stands for 0 or many character. |
|
|
What is the operation of "EXIST"?
|
Set comparison operator, such as IN and ANY. It allows us to test whether a set is nonempty.
|
|
|
What is the operation of "ANY" in the following?
SELECT S.sid FROM Sailors S WHERE S.rating > ANY(SELECT S2.rating FROM Sailors S2 WHERE S2.sname = 'Horatio') |
S.rating is successively compared with each rate value that is an answer to the nested query. The subquery must return a row that makes the comparison true in order for S.rating > ANY to return true.
|
|
|
What is the operation of "ALL" in the following?
SELECT S.sid FROM Sailors S WHERE S.rating > ALL(SELECT S2.rating FROM Sailors S2 WHERE S2.sname = 'Horatio') |
Find the sailors whose rating is better than every sailor called Horatio. If there were no sailor called Horatio, the comparison S.rating > ALL is defined to return true.
|
|
|
What is NOT IN equivalent to?
|
<> ALL
|
|
|
How can the special attribute value NULL be interpreted as?
|
unknown, inapplicable, withheld
|
|
|
What are the rules for NULL values?
|
an arithmetic operator with at least one NULL argument always returns NULL.
The comparison of a NULL value to any second value returns a results of UNKNOWN. |
|
|
Describe the three-valued logic.
|
TRUE = 1, UNKNOWN = 1/2, FALSE = 0.
AND of two truth values: their minimum. OR of two truth values: their maximum. NOT of a truth value: 1 - the truth value |
|
|
Where can the "join" be expressed in?
|
FROM clause.
SELECT * FROM Sailors JOIN Reserves ON Sailors.sid = Reserves.sid |
|
|
What are Outer Joins?
|
join variants that do not loose any information from the input tables. Include dangling tuples that have no matching tuple in other table.
|
|
|
What are aggregate operators?
|
COUNT(*)
COUNT([DISTINCT]A) SUM([DISTINCT]A) AVG([DISTINCT]A) MAX(A) MIN(A) |
|
|
What does GROUP BY do?
|
partitions the tuples into groups by the value of attributes in grouping-list
|
|
|
What does HAVING do?
|
applies the group-qualification to eliminate some groups. Expressions in group-qualification must have a single value per group.
|
|
|
What is a Domain constraint?
|
Field values must be of right type. Always enforced.
|
|
|
What is an attributed-based CHECK?
|
Defined in the declaration of an attribute, activated on insertion to the corresponding table or update of attribute.
CREATE TABLE Sailors (sid INTEGER, rating INTEGER, PRIMARY KEY(sid), CHECK(rating >= 1 AND rating <=10) |
|
|
What is tuple-based CHECK?
|
Defined in the declaration of a table, activated on insertion to the corresponding table or update of tuple.
CREATE TABLE Reserves (sname CHAR(10), bid INTEGER, day DATE, PRIMARY KEY(bid, day), CHECK('Interlake' ,. (SELECT B.bname FROM Boats B WHERE B.bid = bid))) Interlake boats cannot be reserved. |
|
|
What is an assertion?
|
Defined independently from any table, activated on any modification of any table mentioned in the assertion.
CREATE ASSERTION Blah CHECK(10 > ALL (SELECT COUNT(*) FROM Reserves GROUP BY sid)) |
|
|
What is a Trigger?
|
procedure that starts automatically if specified changes occur to the DBMS: Event (activates the trigger), Condition (tests whether the triggers should run), Action(what happens if the trigger runs)
|
|
|
What are the activating statements of a Trigger?
|
Before, After, Instead of , Deferred
|
|
|
What are the options for the REFERENCING clause in a trigger?
|
NEW TABLE: the set of tuples newly inserted (INSERT)
OLD TABLE: the set of deleted or old version of tuples (DELETE/UPDATE) OLD ROW: the old version of the tuple(FOR EACH ROW UPDATE) NEW ROW: the new version of the tuple (FOR EACH ROW UPDATE) |
|
|
Why use Triggers over General Constraints?
|
A trigger may activate other triggers
Triggers have more applications than constraints: monitor integrity constraints, construct a log, gather database statistics. |
|
|
Why use General Constraints over Triggers?
|
Assertions react on any database modification whereas Trigger can only react to a specified event.
|
