Relational Databases COMP2400

In the relational data model, the order of tuples in a relation is important but the order of the attributes in a relation is not important.

False

The term 'attribute' is used to refer to a column in the relational data model.

True

Every candidate key must be a superkey in the same relation schema.

True

Consider the relation schemas where we have made bold the primary keys:
STUDENT(StudentID, Name, DoB, Email)
ENROL(StudentID, CourseNo, Semester, Status, EnrolDate)
The foreign key StudentID in ENROL references StudentID in STUDENT.
Which of the following statements is incorrect?

a.
The StudentID values in ENROL must be distinct.
b.
StudentID must be a superkey in STUDENT.
c.
There may exist StudentID values in STUDENT that do not appear in ENROL.

a. The StudentID values in ENROL must be distinct

Which of the following statements is correct for SQL's DDL (Data Definition Language)?
a.
The ALTER TABLE statement is used to remove an existing relation schema from a database schema.
b.
The DROP TABLE statement is used to drop an attribute from an existing relation.
c.
The FOREIGN KEY constraint is used to enforce referential integrity between two tables.

c. The FOREIGN KEY constraint is used to enforce referential integrity between two tables.

What is a domain?

The set of all possible values for attributes

What are the four main types of constraints in the relational data model?

Domain constraints
Key constraints
Entity integrity constraints
Referential integrity constraints

What is the definition of a domain constraint?

Every value in a tuple must be from the domain of its attribute

What is a key constraint?

Primary key constraints e.g. a student can only have one student id etc.

What is the entity integrity constraint?

No primary key value can be NULL

What is a referential integrity constraint?

It specifies a reference between two relations

What does ALTER TABLE do in SQL?

It changes something about the table, like changing its name, adding/dropping an attribute, etc

What does DROP TABLE do in SQL?

It removes a table from the database schema

What is a minimal superkey?

A superkey is minimal if there is no other superkey on relation R

What is another term for a minimal superkey?

Candidate key

Which of the following is a correct SQL query to select all the employees from "Employee" table where the value of column "age" is below 30 and the value of column "salary" is above 60000.
a.
SELECT * FROM Employee WHERE age < 30 AND salary > 60000;
b.
SELECT age < 30 AND salary > 60000 FROM Employee;
c.
SELECT Employee FROM age < 30 AND salary > 60000;

a. SELECT * FROM Employee WHERE age < 30 AND salary > 60000;

What are the four phases of database design?

Requirements Collection/Analysis
Conceptual Design
Logical Design
Physical Design

How to denote a key attribute in an ER model?

Like a regular attribute, but underlined inside

What is an identifying relationship in ER modeling?

It is a weak entity (double rectangle) whose existence depends on the existence of an identifying entity type. Relationship between them is the identifying relationship (double diamond)

What is the relationship between subclass and superclass described as in EER modeling?

ISA relationship. Ex. a secretary ISA employee

What is specialization in EER modeling?

Process of defining a set of subclasses of an entity type (top-down)

What is generalization in EER modeling?

Opposite of specialization, bottom-up

Logical design is the process of implementing the logical data model in a specific database management system (DBMS).

False

When converting a binary M:N relationship from an ER model into a relation schema, the M:N relationship will become a separate table with the primary key as the combination of all the attributes of the participating entity types.

False

How to identify functional dependencies?

A FD must hold at all times. We can analyze data requirements or analyze sample data

A subset of the attributes of a relation schema R is a _______ if it uniquely determines all attributes of R

Superkey

A superkey K is called a ___________ if no proper subset of K is a superkey

candidate key

How is a primary key chosen?

It is chosen from the candidate keys (aka keys)

What is a prime attribute in functional dependencies?

An attribute occurring in a key

Assume we have a FD A→B. If two tuples have the same values for B, then they must have the same value for A.

False

If a proper subset of X is a superkey, then X cannot be a key.

True

Consider a relation schema R = {A, B} and a set of functional dependencies Σ = {A → B}, AB must be a key of R.

False

What is the closure of a superkey?

The whole set

If an attribute never appears in the dependent of any FD, this attribute must __________

be part of each key

If an attribute never appears in the determinant of any FD but appears in the dependent of any FD, this attribute must __________

not be part of each key

What is minimal cover?

A set of FDs that can't be reduced further.
-1 attribute per dependent
-No duplicate or redundant FDs
-Determinant can't be further reduced

What is normalization?

Decomposing a relation into smaller relations in a certain normal form? e.g. BCNF or 3NF

What is the basic idea of BCNF?

Whenever a non-trivial FD X → A holds in R, then X is a superkey. All redundancy based on functional dependency are removed

What is lossless join?

Capturing the same data

What is dependency preservation?

Capturing the same metadata; ensuring each FD can be inferred from FDs after decomposition

What is the idea behind 3NF

A relation schema R is in 3NF if whenever a non-trivial FD X → A holds in R, then X is a superkey or A is a prime attribute

Describe the dependent in a minimal cover

Each FD only has a single attribute on its right hand side

Describe the determinant in a minimal cover

Each FD has as few attributes on the left hand side as possible

Is the minimal cover of a set of FDs unique?

Not necessarily

The property known as dependency preservation ensures that each of the original functional dependencies can be inferred from the functional dependencies after decomposition (normalisation).
Question 1

True

There exists only one possible 3NF decomposition for any relation schema and any set of functional dependencies.

False

What is a procedural language?

The programmer writes instructions that tell the computer what to accomplish and how to do it.

What is a declarative language?

A language used to define the problem to be solved rather than the solution
- SQL falls into this category

In relational algebra, what is selection and how is it denoted?

It chooses certain rows, and it denoted σϕ(R). ex. σSemester=S2(Course)

In relational algebra, what is projection and how is it denoted?

It chooses certain columns and is denoted πA1,...,An (R). ex. πhome,away(soccer)

In relational algebra, what is renaming and how is it denoted?

It renames attributes or relation names ρR' (A1,...,An)(R)

In relational algebra, what is union and how is it denoted?

It is the union of two sets and is used the same as in SQL. Same with intersection and difference

In relational algebra, what is the cartesian product?

R1 X R2, combines tuples from two relations in a combinatorial fashion.

In relational algebra what is a join?

It is a join just like in SQL, and uses the double triangle symbol. Can be a regular join or a natural join

Is selection commutative?

Yes

Is projection commutative?

No

What is the complete set of operators in relational algebra?

selection, projection, renaming, union, difference, cartesian product