Midterm Flashcards

Question

Transposing Process

Answer 1

1. The name of the entity becomes the name of the "relation" 2. Attributes of the entity become attributes of the "relation" (Except for any composite attributes (shows a logical connection) and derived attributes (not physically stored)) Relational Schema Notation (one-dimensional view) § RELATION(attr1, attr2, attr3, ...) § Example: A(2, 3, 4, 5) 3. The identifier of the entity becomes the "proposed" primary key of the "relation", denoted by underlining

Answer 2

A two-dimensional structure that holds data pertaining to an object of interest. Columns are attributes, rows are tuples of information or an entity instance.

Answer 3

A Surrogate primary key exists only to create a unique identifier.

Answer 4

A column, or group of columns, used to identify a row. Column values, when grouped must be unique May not contain null must be minimal

Answer 5

Data Definition Language (DDL) Deals primarily with metadata CREATE... DROP... ALTER Data Manipulation Language (DML) Deals primarily with user data INSERT SELECT UPDATE DELETE

Answer 6

Shows user data in a table SELECT * | attributes_to_include FROM tableName;

Answer 7

Means unknown or nothing § Does NOT mean an empty string('') or a string of a space (' ')

Answer 8

Representation level. Normalization: decomposing relations to avoid anomalies when inserting, updating or deleting data. Also serves to reduce redundancy of the data

Answer 9

Used to show a relationship between attributes within a relation Functional Dependency: If an attribute, (A), determines the value of another attribute, (B), then A functionally determines the value of B. Notation: A->B Therefore if we know the value of A, the value of B can be determined

Answer 10

A candidate key functionally determines all other attributes in the relation Since each tuple in a relation must be unique, there is a unique candidate key for each tuple A relation could have several possible unique candidate keys Each determinant (simple or composite) that is eligible to serve in the role of primary key are called candidate keys. You decide to use one of them as the primary key

Answer 11

The primary key is an attribute, or group of attributes, that will uniquely identify a tuple from all other tuples in a relation The primary key functionally determines all other attributes in the relation There can only be one primary key for a relation That primary key can be composed of more than one attribute (Composite Primary Key) Before a “proposed primary key” (Step #2) can be confirmed, any candidate keys must be determined through an examination of functional dependencies for the relation (Step 3.B) If there is only one candidate key, it will become the primary key  If there are multiple candidate keys, then one must be selected to become the primary key  Sometimes one candidate key will be “better” than the others, other times, candidates may be “equivalent”  A primary key is AWALYS a candidate key. A candidate key may, or may not, become a primary key.

Answer 12

A database anomaly is a fault in a database that can occur when data is stored in a flat database or due to poor planning. Anomalies can occur when data is stored multiple times unnecessarily, or when all data is stored in a single table

Answer 13

Insertion anomaly When it is not possible to insert new data into a table due to missing information. For example, if you try to insert a supplier who has no order. Deletion anomaly When deleting data unintentionally results in the loss of other important data. For example, if you delete the last single order of a supplier, the data of the supplier will also be lost. Update anomaly When updating a single data value requires multiple rows to be modified. For example, to update a supplier, you need to update in each and every purchase order of the supplier.

Answer 14

Determinant – the attribute(s) on the left side of a functional dependency

Answer 15

Composite Candidate Key: A candidate key made up of more than one attribute. Composite Primary Key: A primary key made up of more than one attribute.

Answer 16

A relation is in second normal form if it is in first normal form, and all of its non-key attributes are dependent on all of the primary key. Violation: partial dependency – when you have a non-key attribute that is dependent on only part of a composite pk (primary key).

Answer 17

Establishes a connection, or relationship, between relations. When a primary key of one relation appears in any other relation, it is a foreign key. Denoted by italics (when typing) or dashed underline (when handwriting) CONTACT_INFO(contactID) must exist in CONTACT(contactID)

Answer 18

A reference statement enforces referential integrity. Referential Integrity – the set of values that can be used as values for a foreign key value MUST be in the set of values from the respective primary key. Example: CONTACT_INFO(contactID) must exist in CONTACT(contactID

Answer 19

A relation is in third normal form if it is in 2NF and is free from transitive dependencies. Violation: The 2nd half of a Transitive Dependency: a non-key attribute is functionally dependent on another non-key attribute A -> B -> C where A is the primary key, and B and C are not.

Answer 20

A relation is in BCNF if, and only if, all determinants are a candidate key (which would include primary key). Determinant: the attribute(s) on the left side of a functional dependency. Candidate Key: attribute(s) that could serve as a primary key, but for some reason was not selected to serve as the primary key