Final Exam

Question 1

How does quality of of an E-R diagram affect normalization process?

Answer 1

A data model that effectively captures the entities and the relationships between those entities will require little to no modification when transposed into relations.

If a data model is done poorly, a number of changes to the relations will be required to remove anomalies and reduce data redundancy.

Question 2

Relationship Class

Answer 2

Denotes a “connection” between entity classes.

There can be multiple relationships between entity classes.

Relationships should be named with a “verb phrase”
One phrase – grammar modified to reflect each direction EMPLOYEE has DEPARTMENT; DEPARTMENT has EMPLOYEE
EMPLOYEE mentors another EMPLOYEE; EMPLOYEE is mentored by another EMPLOYEE
Two phrases – specific for each direction

Question 3

Relationship Entity

Answer 3

Denotes a “connection” between entity instances.

Question 4

Relationships Degree

Answer 4

the degree of a relationship is the number of entity classes that participate in the relationship

Question 5

Recursive Relationship

Answer 5

A relationship among entities of the same type.

The same entity class participates more than once in different roles.

A relationship between instances of the same entity class.

A recursive relationship will always have a degree of 1.

Question 6

Binary Relationship

Answer 6

A relationship between exactly two entities.

A binary relationship will always have a degree of 2

Question 7

Maximum Cardinality

Answer 7

The maximum number of entity instances that can occur on one side of a relationship.

The maximum cardinalities from each side come together to form the cardinality ratio, which is also known as the relationship type.

One-to-One (1:1)
One-to-Many; Many-to-One (1:N; N:1)
Many-to-Many (M:N)
If a specific maximum is known, you can use the number.

To determine the maximum cardinalities, you must ask a question from each perspective.
§ Can “ENTITY_A” have more than one “ENTITY_B”?
§ Can “ENTITY_B” have more than one “ENTITY_A”?

If an answer is ‘NO, there is at most one’, then the maximum cardinality is “one” for that perspective: Denoted by a single line going into the entity that is restricted to a maximum of one.
If an answer is ‘YES, there can be more than one’, then the maximum cardinality is “many” for that
perspective: Denoted by a crow’s foot going into the entity that can have many.

Question 8

Minimum Cardinality

Answer 8

The minimum number of entity instances that have to occur on one side of a relationship.

To determine the minimum cardinalities, you must ask a question from each perspective.

Does “ENTITY_A” have to have an “ENTITY_B”?
Does “ENTITY_B” have to have an “ENTITY_A”?
If an answer is ‘NO, there doesn’t have to be one’, then the minimum cardinality is zero (optional) for
that perspective: Denoted by an oval placed before the maximum cardinality for the entity that is not required to participate in the relationship.
If an answer is ‘YES, there must be one”, then the minimum cardinality is one for that perspective: Denoted by a perpendicular line to the relationship, placed before the maximum cardinality for the
entity that is required to participate in the relationship.

Question 9

Business Rules

Answer 9

Restrictions on the model
that reflect the business
activities being modeled.
Such rules are the basis for
determining the appropriate
elements to use in the E-R
diagram.

Question 10

Has-A vs IS-A

Answer 10

‘IS-A’ is a relationship where one object is a subtype of another. A Car is a Vehicle, a Dog is an Animal; this relationship is manifested through inheritance. The ‘HAS-A’ relationship is where one object contains or uses another object

Question 11

Supertype vs subtype

Answer 11

Denotes an “IS-A” relationship
Also known as Generalization Hierarchies

Supertype – A type of entity class that contains attributes that are common
across all specializations, A generalization

Subtype – A type of entity class that “inherits” attributes of its supertype as well as containing attributes of its own - A specialization

Question 12

Strong vs Weak Entity

Answer 12

Strong entity – can exist independently of all other entities.
Supertypes. Denoted by: Square

Weak entity – can’t exist on its own; must have another entity to support it.Subtypes, Do not have their own identifier. Denoted by: round or double lines

Question 13

When is a supertype/subtype relationships necessary?

Answer 13

There are attributes that apply to some, but not all, instances of an
entity.

There is a relationship that pertains to a defined subset of instances for
an entity.

Question 14

Implications of having a relationship at supertype vs subtype level

Answer 14

Relationships at the supertype level indicate that instances of the
supertypes, including all subtypes, can participate in the relationship.

Relationships at the subtype level indicate only instances of that
subtype can participate in the relationship.

Question 15

Completeness Constraints

Answer 15

The minimum number of subtype instances that a supertype instance must belong to.

To determine the type of completeness constraint, you must ask a question.
Must every instance of the supertype belong to a subtype?
- If an answer is ‘NO, an instance of the supertype doesn’t have to belong to any of the subtype(s)’, then
there is partial specialization. Denoted by a single line from the supertype to the supertype/subtype relationship indicator.
-If an answer is ‘YES, every instance of the supertype must belong to a subtype’, then there is total
specialization. Denoted by double lines from the supertype to the supertype/subtype relationship indicator.

Question 16

Disjointedness Constraints

Answer 16

The maximum number of subtype instances that a supertype instance can belong to.

To determine the type of disjointedness constraint, you must ask a question.
- Can an instance of the supertype belong to more than one subtype?

• If an answer is ‘NO, an instance of the supertype may only belong to at most one subtype’, then the
  disjoint rule is in effect. Denoted by a ‘d’ inside the supertype/subtype relationship indicator.
• If an answer is ‘YES, an instance of the supertype can belong to more than one subtype’, then the
  overlap rule is in effect. Denoted by an ‘o’ inside the supertype/subtype relationship indicator

Question 17

Subtype discriminator

Answer 17

A discriminator is an attribute, or a group of attributes, in a supertype that will indicate any subtypes that an entity instance belongs to.

Disjoint Rule: A single attribute can be used as a discriminator in the supertype. The value for the discriminator will indicate which subtype, if any, an entity instance of the supertype belongs to.

Overlap Rule: For each subtype an attribute will need to be used as a discriminator in the supertype. The value of each attribute will indicate which subtypes an entity instance belongs to.

Question 18

Transposing Process

Answer 18

1. The name of each entity becomes the name of a “relation”.
2. Attributes of the entity become attributes of the “relation”.
3. The identifier of the entity becomes the proposed primary key of the “relation”, denoted by an underline.
4. For a relationship, create a primary key (pk)/foreign key (fk) connection based on the type of relationship.
5. Add a reference statement for the foreign key
   a. Notation: RELATION_X(foreign key) mei RELATION(primary key)
6. Repeat steps 4 and 5 until all relationships have been implemented.

Question 19

Foreign key placement for different types of relations

Answer 19

a. 1:1 – Examine the minimum cardinalities
i. If the minimum cardinalities are the same, then examine whether a simple or composite fk scenario exists, go with simple fk
ii. If the minimum cardinalities are different, go in the direction that avoids null values for the fk.

b. 1:N (N:1) – Take the pk from the relation on the “one” side and add it as a foreign key to the relation on the “many” side.

c. N:M – Create a new intersection relation.
i. The intersection relation will have a foreign key from each entity involved in the relationship.
ii. The fks will form a composite pk.

d. Supertype/Subtype
i. The pk of the supertype becomes a fk in each subtype.
ii. The fk in each subtype becomes the pk.

Question 20

Participation Constraints

Answer 20

Total participation (existence dependency) – an entity can exist only if it participates in a specific
relationship.
Weak entities always have total participation

Partial participation – an entity can exist without participating in a specific relationship.

Question 21

Identifying vs non-identifying relationship

Answer 21

Identifying Relationship
A solid relationship line that denotes that
the foreign key will need to help form a
primary key.

Non-Identifying Relationship
A dashed relationship line that denotes
that the foreign key will not be needed to
form a primary key.

Question 22

Lookup Table

Answer 22

Also known as Reference table
Used to minimize storage of repetitive values
Ensures sure that only certain values are allowed
Helpful to create drop-down list of options in front-end interface

Typical use:
Store a code in the “main” table
Store the code and full value in the lookup table

Question 23

Implementing a Foreign Key and Referential Integrity

Answer 23

Implementing a foreign key enforces referential integrity: can’t add a value to a foreign key, unless that value exists for the foreign key back in it’s “native” table.

Two step process in a CREATE TABLE statement:
Add an attribute specification for each foreign key attribute
Datatype must be consistent

Refer to E-R diagram to determine if a NOT NULL constraint is needed for the foreign key attribute(s)

Add a foreign key constraint for each foreign key
Syntax:
CONSTRAINT table_attr(s)_fk FOREIGN KEY (attr(s)) REFERENCES native_table(attr(s))

For 1:1 or 1:N (N:1) recursive relationships
Foreign key constraint will reference the same table’s primary key

For supertype/subtype relationships
Foreign key constraint in subtype will reference supertype

Question 24

Transactions

Answer 24

SQL provides us with the concept of a “transaction”.

A transaction is a logical unit of work.
Can be composed of one or more statements

Statements can be
SQL
§ Data Definition Language (DDL)
§ Data Manipulation Language (DML)
§ Programming elements – if supported by DBMS

Processed by Transaction Control Language (TCL) statements
§ COMMIT = save
§ ROLLBACK = undo/cancel

Question 25

COMMIT

Answer 25

The COMMIT statement defines the end of the transaction and writes the changes to the database

In MySQL, an implicit commit is performed when a DDL statement is executed with an in-process transaction

The ACID properties guarantee that either all the changes between the “BEGIN” and “COMMIT” statements get done, or they don’t get done at all

Transaction Control Language

The database is automatically restored to its prior state if there is a problem

No code or other intervention by programmer

Question 26

Rollback

Answer 26

End/Cancel

Transaction Control Language

Question 27

Properties of a Transaction

Answer 27

Atomic
Consistency
Isolation
Durability

Question 28

Atomicity

Answer 28

Transaction is considered an atomic unit. A transaction is resolved by either all changes being made or none of the changes being made, a partial commit is not allowed.

If a failure occurs in the middle of a transaction, all partial results must be undone.

Question 29

Consistency

Answer 29

A transaction must leave the database in a consistent state.

A transaction must not violate database integrity constraints

Question 30

Isolation

Answer 30

Concurrent transactions will not negatively impact one another.

Record locking – can restrict access to records that are part of an in-process transaction.

An in-process transaction will not reveal results to others until it is committed or rolled-back

Question 31

Durability

Answer 31

Committed data is permanently recorded.

If a failure occurs, DBMS, needs to be able to restore committed data.

Question 32

Relational Algebra

Answer 32

Similar to sets
Some operations are similar to corresponding set operations
Can be represented by Venn diagrams
Relations can be manipulated using operations
Procedural
Query languages are based on relational algebra (SELECT statement)

Consists of variables – representing relations.

Operators – represent the actions we can take with these relations.

The output must meet the characteristics of a relation.

Question 33

Theoretical Relational Algebra Operators

Answer 33

Three Classes:

Operations to remove parts of a relation:
Selection – only tuples that meet stated criteria appear in the resulting relation
Projection – only specified attributes appear in the resulting relation

Set based: Union, Intersection, Difference

Operations that join tuples in two relations:
Product Joins

Question 34

Projection

Answer 34

The projection of a relation produces a new relation with selected columns from the original relation

Limits the attributes that will appear in the resulting relation to those that are specified
Can be used to rearrange the order of columns in the resulting relation

Duplicate tuples (rows) from result set are removed

Notation variations:
RELATION[attr1, attr2, attr4]

Select cause of a SELECT statement

Question 35

Selection

Answer 35

Can be used to specify condition(s) that must be met in order for a tuple to appear in the result set

Condition can include: =, <, >, ≤, ≥, Negation: ¬, OR: ∨, AND:

Notation variations:
RELATION WHERE condition_to_be_met
Example: EMP WHERE dept# = 3

Where Cause of a select statement

Question 36

SQL and Relational Algebra

Answer 36

When attempting a relational algebra using SQL, the DBMS may not produce a relation as duplicate rows can be in the result set.

Can use the DISTINCT key word as part of SELECT clause of SELECT statement to remove duplicate rows from a result set.

Not needed for every SELECT statement written!

Only those where you are asked to produce a relational algebra operation using SQL.
Format: SELECT DISTINCT .

Question 37

Union

Answer 37

The union of two relations produces a resulting relation with the all of the tuples from each relation.

Relations must be Union Compatible.

Remember, the resulting relation is ALWAYS a relation and must meet the characterics of a relation (i.e. duplicate tuples are removed)

Notation variations:
§ A ∪ B
§ A + B
§ A UNION B

Is commutative

Question 38

Union Compatible

Answer 38

Each relation has the same number of attributes

The attributes must be in the same order in the two relations, which means that corresponding attributes must come from the same physical and logical domain

Question 39

Commutative

Answer 39

The order that the relations are specified will not impact the resulting relation

Note that not all relational algebra operations are commutative

Question 40

Difference

Answer 40

Result is all of the tuples from the first relation that do not appear in the second relation.

Relations must be union compatible
Is NOT cummutative
§ Order matters

§ Notation:
A – B
A DIFFERENCE B

Question 41

Subqueries

Answer 41

Combining Queries

You can have many nested subqueries

The subquery should be in parentheses

The subquery can be part of the WHERE clause of the parent query

The datatype being returned by the subquery must match the field referenced in the WHERE clause

Any projection of attributes to the user can only include attributes from the outermost query

Question 42

Intersection

Answer 42

The intersection of two relations is a relation that contains only the tuples that appear in both relations.

Relations must be union compatible.

Is commutative
A INTERSECT B = B INTERSECT A

Notation variations:
§ A ∩ B
§ A INTERSECT B

Question 43

Product

Answer 43

The set of tuples formed by combining each tuple in one relation with each of the tuples in another relation.

Also called:
§ Cartesian product
§ Cross product

Notation variations:
§ A x B
§ A PRODUCT B
§ By itself, not very useful, but is used as part of other operators

Question 44

Algebra Joins

Answer 44

Equijoin
Natural join -Also includes a projection

Outer join (also known as a directional join)
§ Left outer join
§ Right outer join
§ Full outer join

Question 45

Equijoin

Answer 45

In an equijoin, specified attributes are matched using an equality test. All attributes will appear in the resulting relation

selects tuples where the “ON”
attribute(s) has non-null values in both relations (matches only)

Question 46

Natural Join

Answer 46

In a natural join, specified attributes are matched using an equality test.
Only one of the “joining” attributes appears in the final result

selects tuples where the “ON”
attribute(s) has non-null values in both relations (matches only)

Question 47

Left Outer Join

Answer 47

LEFT OUTER JOIN selects tuples where the “ON” attribute(s) has non-null values in the first relation (left side)

Gets all tuples from the relation that appears on the left-hand side of the join and any matching rows from the relation on the right side.
In case there are no matching tuples from the right side, NULL will appear for “right side” attributes

Question 48

Right Outer Join

Answer 48

Selects tuples where the “ON” attribute(s) has non-null values in the second relation (right side)

Right Outer Join: Gets all tuples from the relation on the right side of the statement and any matching tuples from the relation on the left side.

In case there are no matching tuples on the left side, attribute values for “left side” are
NULL.

Question 49

Full Outer Join

Answer 49

FULL OUTER JOIN selects tuples where the “ON” attribute has non-null values in either relation

You see all tuples from both relations
§ Where the tuples match based on the join criteria, the values from both relations will appear.
§ NULL values will appear for attributes from either relation where the join conditions were not
met

Question 50

Having Clause

Answer 50

§ Instead of using the WHERE clause, SQL provides us with the HAVING clause
§ Use the HAVING clause to place restrictions according to the results of aggregate functions

It’s not a case of one or the other – a WHERE clause and a HAVING clause can be in the same statement
§ The WHERE clause applies to the raw, ungrouped data and is evaluated before a GROUP BY clause executes
§ The HAVING clause applies to the grouped data and is applied after a GROUP BY clause executes

Question 51

SELECT CLAUSE order and which are optional

Answer 51

SELECT attribute(s)
FROM tableName(s)
WHERE condition(s) <- optional
GROUP BY groupList <- optional
HAVING haveCondition <- optional
ORDER BY orderList; <- optional

Question 52

Highly Abstract Tables

Answer 52

Highly abstract tables collapse common fields into a tag/value relationship
n Tempo, Mood, and Genre are all “attributes”
n Create another weak entity that has an “attribute” field and a value field
n Similar in concept to key/value pair

Question 53

Highly Abstract Tables Advantages and Disadvantages

Answer 53

Benefit:
§ Very flexible
§ Easy to handle unforeseen information

Downside
§ Queries get difficult

Question 54

Union compatible

Answer 54

both output compatible data types for each column, return the same number of columns,
and the order of the columns match up

Question 55

Join combo

Answer 55

Product selection projection

Question 56

Union compatible

Answer 56

Union, difference, intersection