Term
| (3.1) What is a relation? |
|
Definition
| The main construct for representing data in the relational model. |
|
|
Term
| (3.1) Differentiate between a relation schema and a relation instance. |
|
Definition
| The relation instance is a table and the relation schema describes the column headings for the table. They both comprise a relation. |
|
|
Term
| (3.1) Give an example of a relation schema. |
|
Definition
| Students(sid: string, name: string, login: string, age: integer, gpa: real) |
|
|
Term
|
Definition
| It is a single record or row in a relation. |
|
|
Term
| (3.1) Give an example of a relation instance. |
|
Definition
50000 Dave dave@cs 19 3.3
53666 Jones jones@cs 18 3.4
53688 Smith smith@ee 18 3.2
53650 Smith smith@mu 19 3.8
53831 Madayan maday@mu 11 1.8
53832 Culdu culdu@ma 12 2.0 |
|
|
Term
| (3.1) If an instance of a relation has 17 rows with 5 attributes, what is the degree (or arity) of this relation instance? |
|
Definition
| the degree of the relation is 5 |
|
|
Term
| (3.1) If an instance of a relation has 17 rows with 5 attributes, what is the cardinality of this relation instance? |
|
Definition
|
|
Term
| 3.1) What is a relational database? |
|
Definition
| It is a collection of relations with distinct relation names. |
|
|
Term
| (3.1) What are domain constraints? |
|
Definition
| It is the requirement that each tuple in a relation instance must satisy the domain constraints of its relation schema. (i.e. an integer attribute must contain an integer) |
|
|
Term
| (3.1.1) What SQL construct enables the definition of a relation? |
|
Definition
| the CREATE TABLE statement |
|
|
Term
| (3.1.1) What SQL constructs allow modification of relation instances? |
|
Definition
| the INSERT/INTO/VALUES, DELETE/FROM/WHERE and UPDATE/SET/WHERE statements |
|
|
Term
| (3.2) What are integrity constraints? |
|
Definition
| A condition specified on a database schema and restricts the data that can be stored in an instance of the database. |
|
|
Term
| (3.2) What is a legal instance? Can a DBMS database have an illegal instance? |
|
Definition
| A database instance that satisfies all the integrity constraints specified on the database schema. No, since a DBMS enforces integrity constraints thereby only legal instances my be stored in one of it's databases. |
|
|
Term
| (3.2.1) What is a key constraint? |
|
Definition
| It is a statement that a certain minimal subset of the fields of a relation is a unique identifier for a tuple. |
|
|
Term
| (3.2.1) What is a candidate key? |
|
Definition
| The candidate key is a minimum set of fields that uniquely identify a tuple. The candidate key is often abbreviated to just key. |
|
|
Term
| (3.2.1) What is the relationship between a candidate key and a primary key? |
|
Definition
| Since a relation may have only one primary key, only one of any possible candidate keys is assigned as the primary key. |
|
|
Term
| (3.2.1) What is a superkey? |
|
Definition
| Very simply stated, it is a set of fields that contains a key. So, a tuple itself is a superkey. |
|
|
Term
| (3.2.1) What are the two ways to specify a key constraint in SQL? |
|
Definition
1) PRMARY KEY (sid)
2) UNIQUE (name, address)
A DBMS requires that every table must have one primary key defined, although SQL does not require it. The UNIQUE constraint is used to define another type of key that can be thought of as a secondary key. It allows for other candidate keys to be assigned as a constraint. |
|
|
Term
| (3.2.1) Define the term "primary key constraint". |
|
Definition
| It is the constraint that only one candidate key may be used as the primary key for a table. |
|
|
Term
| (3.2.1) For what is the SQL keyword "CONSTRAINT" used? |
|
Definition
| It is used to name a key constraint. This can aid in identifying errors when a key constraint is violated. |
|
|
Term
| (3.2.2) What is a foreign key constraint? |
|
Definition
| It is a key constraint defined for the purpose of requiring that information stored in a relation is linked to information stored in another relation. |
|
|
Term
| (3.2.2) How is a foreign key constraint specified in SQL? |
|
Definition
FOREIGN KEY (studentID) REFERENCES Students
This foreign key constraint states that every studentID value in this table must also appear as a value in the primary key field in the Students table. |
|
|
Term
| (3.2.3) Other than domain and key constraints, what other kinds of constraints can we express in SQL? |
|
Definition
| There are table constraints and assertions, that are considered general constraints. |
|
|
Term
| (3.2.3) Define table constraint. |
|
Definition
| Table constraints are associated with a single table and checked whenever that table is modified. |
|
|
Term
| (3.2.3) Define an assertion. |
|
Definition
| Assertions are basically table constraints among multiple tables. An assertion is checked whenever any table involved in the assertion, is modified. |
|
|
Term
| (3.2.2) Give a descriptive example of a general table constraint. |
|
Definition
| All students in the Students relation must have an age within a certain range of values. |
|
|
Term
| (3.3) What does the DBMS do when constraints are violated? |
|
Definition
| the SQL transaction is rejected |
|
|
Term
| (3.3) What is referential integrity? |
|
Definition
| Referential integrity is the result of the DBMS rejecting transactions that violate the referential relationships between tables. |
|
|
Term
| (3.3) What options does SQL give application programmers for dealing with violations of referential integrity? |
|
Definition
NO ACTION - action is to be rejected
CASCADE - the action should propagate through to maintain referential integrity
SET DEFAULT - give default value in order to maintain referential integrity (must be carefult that doing this makes sense for table usage)
SET NULL - give null value to allow a transaction (i.e. ON DELETE SET NULL would allow for the deletion of a tuple that is used as a foreign key in another table without that table's tuple from being deleted (and instead would have it's foreign key field changed to null) |
|
|
Term
| (3.3.1) How can an application programmer control the time that constraint violations are checked during transaction execution? |
|
Definition
| SET CONSTRAINT abcdef DEFERRED - defers the constraint checking until a complete sequence of related transactions can be executed. |
|
|
Term
| (3.3.1) What is the default for when integrity constraints are enforced by a DBMS? |
|
Definition
| A constraint is checked at the end of every SQL statement that could lead to a violation. |
|
|
Term
| (3.4) What is a relational database query? |
|
Definition
| (query, for short) it is a question about the data |
|
|
Term
| (3.5) How can we translate an ER diagram into SQL statements to create tables? |
|
Definition
| Using the ER diagram as a map, you can derive the SQL statements accordingly. The SQL statements can only approximate the ER design. |
|
|
Term
| (3.5.1) How are entity sets mapped into relations? |
|
Definition
| Each attribute in the ER design becomes an attribute of the table. The domain would be specified according to the type of data that the fields would contain. The primary key underlined in the ER design would be the primary key of the table. (see figure 3.8 and corresponding SQL statement on p.75) |
|
|
Term
| (3.5.2) How are relationship sets mapped into relations? |
|
Definition
| The attributes of the relation include the primary key attributes of each participating entity set as foreign key fields, and the descriptive attributes of the relationship set. See SQL translations of ER diagrams in figures 3.10 and 3.11 on pages 76 and 77. |
|
|
Term
| (3.5.3) How are key constraints translated from the ER diagram? |
|
Definition
| If a relationship involves entity sets that are linked with arrows, then the key of one of these entity sets must be the primary key of the relation. (see figure 3.12 and SQL statement on page 78) |
|
|
Term
| (3.5.3) How can a relationship set in an ER diagram be assimilated into a non-relationship entity? |
|
Definition
| Augment the entity set that has an arrow with attributes from the relationship set and keys from other entity sets. Add the key fields of all the other entity sets to the augmenting entity set. Add the attributes of the relationship set to this entity set. (see figure 3.12 and SQL statement on page 79 - This method has performance trade-offs that are mentioned in the first paragraph on p.79) |
|
|
Term
| (3.5.4) How are participation constraints translated from the ER diagram? |
|
Definition
| By specifying NOT NULL on key fields, or by table constraints and assertions (more on these in ch.5). If you take the Dept_Mgr example on p. 79, by specifying NOT NULL on foreign key field ssn in the Dept_Mgr table requires that every dept. have a manager. Although not all participation constraints can be handled this way. |
|
|
Term
| (3.5.5) How are weak entity sets translated from the ER diagram? |
|
Definition
| Since weak entity sets must use a key field from another entity to be unique, the example on p82 translates similar to the second approach in 3.5.3 (see p79) except that the primary key references 2 fields one of which is a foreign key to Employees. |
|
|
Term
| (3.5.6) How are class hierarchies (is/a relationships) translated from the ER diagram? |
|
Definition
| There are 2 methods. 1) The child is defined with its attributes and the key attributes of its parent which serve as the primary key and the foreign key referencing the parent. 2) The parent only serves as a proxy for the common fields of the children, thereby each child is defined with its own attributes and all of the parent's attributes, since the parent would not actually exist as a relation. This only works if each child row only occurs once, since data would be duplicated otherwise. |
|
|
Term
| (3.5.5) How is aggregation translated from the ER diagram? |
|
Definition
| The relationship set that is connected to the aggregation is defined with the following attributes: the key fields from the entity set connected to it directly, the key fields from the relationship set within the aggregation rectangle, and any descriptive attributes associated with itself. |
|
|
Term
|
Definition
| A table whose rows are not explicitly stored in the database but are computed as needed from a view definition. Views can be used just like explicitly stored tables, in defining new queries or views. |
|
|
Term
| (3.6) What is a base table? |
|
Definition
| A base table is any explicitly stored table. |
|
|
Term
| (3.6.1) How do views support logical data independence? |
|
Definition
| If conceptual database design change is implemented, a view can be defined using the old conceptual design, thereby making the conceptual design change undetectable to applications using the old design. |
|
|
Term
| (3.6.1) How are views used for security? |
|
Definition
| Views can be used to define subsets of tables and then give selected users access to the view. This enables these users to see some table data that is less sensitive than other data. |
|
|
Term
| (3.6.2) How are queries on views evaluated? |
|
Definition
| They are evaluated the same as base table queries. There is no difference in the way a base table query is evaluated from the way a view query is evaluated. |
|
|
Term
| (3.6.2) Why does SQL restrict the class of views that can be updated? |
|
Definition
| Some views may not contain all the fields necessary to keep a base table's constraints, therefore an update could result in some null fields when a null would not be allowed. Also if multiple base tables are part of a view definition, then there could be some confusion over which base table should get updated. |
|
|
Term
| (3.7) What are the SQL constructs to modify the structure of tables and destroy tables and views? |
|
Definition
| The ALTER TABLE, DROP TABLE and DROP VIEW commands. |
|
|
Term
| (3.7) Describe what happens when a view is destroyed. |
|
Definition
| All the rows within the view are deleted from the base table and the view definition is deleted from the database. |
|
|
