3+4

Question 1

What were the previous database models? What is the current one?

Answer 1

Hierarchical (tree structure, bad because adding anything required a parent). Network. Currently used model is relational model.

Question 2

What are the core components in the relational model?

How does it work?

Answer 2

Objects (or relations), which form the structure.
Integrity, the enforcing of constraints and rules.
Operators, for data manipulation.
Each relation is a table of values: A row is a tuple, a collection of related data values. A column is an attribute.

Question 3

What is a domain?

Answer 3

a set of atomic values, with a data type or format.

Question 4

What is a relation schema made up of? What is a degree?

Answer 4

a relation name, and a set of attributes. The degree of a relation is the number of attributes in the schema.

Question 5

What does NULL mean?

Answer 5

Can mean many things, e.g that the value is unknown, or that it does not apply.

Question 6

What are restraints and what types are there?

Answer 6

Restrictions on the values in the database, these can be:

• domain contraints
• key constraints and constraints on NULL
• Integrity constraints
• Semantic Integrity constraints
• Functional dependency constraints.

Question 7

What is a domain constraint?

Answer 7

Specify the domain of an attribute.

Question 8

What is a superkey, how does this apply to key constraints?

Answer 8

firstly: no two tuples can have all the same values.
A superkey is a set of attributes such that for any two distinct tuples the superkeys are not identical.
A key is a minimal superkey, meaning no more attributes can be taken away. There can be more than one possible key, each of which is named a candidate key. One of these candidate keys is known as the primary key.

Question 9

What is an integrity constraint? What are two important examples?

Answer 9

specified on database schema, holds every valid database state of the schema, e.g domain, key, NOT NULL.
An entity integrity constraint means no primary key values can be NULL.
A referential integrity constraint ensures if another entity is referenced, it must exist.

Question 10

What is a foreign key?

Answer 10

A set of attributes which references another relation, must satisfy two conditions:
1. Same domain as primary key attributes of second relation.
2. The values specified must be either NULL or reference an existing entity in the second relation.
The first relation is the referencing, the second is the referenced.

Question 11

What are semantic integrity constraints and functional dependency constraints?

Answer 11

Semantic: salary of employee should not exceed salary of supervisor.
Functional dependency: establish functional relationship among two sets of attributes X and Y.

Question 12

What are the steps in mapping an ER diagram to relational database model?

Answer 12

1: Map regular entity types
- Create a relation that has all simple attributes. Include simple components for composite.
- Choose one key attribute of E as primary key

2. Map weak entity types
   - Create a relation that includes all simple attributes and components.
   - Primary key of owner entity becomes foreign key of weak entity relation.
   - Primary key is primary key of owner + partial key.
3. Map binary 1:1 relationship types
   - Choose one participating relation, include primary key of second relation as foreign key in first. Should choose one with total participation.
   - Add all simple attributes of relationship.
4. Map binary 1:N relationship types
   - Include foreign key in the N side entity of the relationship.
   - Include simple attributes of relationship.
5. Map binary M:N relationship types
   - Create new relationship, include foreign keys of both parties in the relationship. Combination forms primary key.
   - include simple attributes of relationship.
6. Map multivalued attributes
   - Create new relation
   - has an attribute corresponding with multi value attribute, primary key is foreign key of original entity + attribute.
7. Map N-ary relationship types
   - create new relation.
   - primary key is foreign key of each participating entity.
   - include simple attributes.

Question 13

What are the operators in the relational database model?

Answer 13

Special relational operators: Select(or ‘restrict’), project, join, divide
Set operators: Union, intersection, difference, cartesian product.

Question 14

What does the select operator do?

Answer 14

Works on a single relation ans selects a subset based on selection_condition. It is commutative(order of selects doesn’t change result) and a cascade can be combined into a single SELECT.

Question 15

What does the project operator do?

Answer 15

Works on single relation, defines a relation containing a vertical subset of the original relation. Eliminates duplicates. Is not guaranteed to be commutative.

Question 16

What do the set operators do?

Answer 16

Union: includes all tuples in either R or S or both, duplicates eliminated
Intersection: Includes tuples in both R and S.
Set Difference(-): includes all tuples in first relation, which arn’t also in the second.
Union and intersection are associative, set difference is not commutative.
For these to work the relations must be union compatible or type compatible(same degree and domain for all attributes).