Final Take 2.1 Flashcards

1
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Relationship Class

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Relationship Entity

A

Denotes a “connection” between entity instances.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Relationships Degree

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Recursive Relationship

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Binary Relationship

A

A relationship between exactly two entities.

A binary relationship will always have a degree of 2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Maximum Cardinality

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Minimum Cardinality

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Business Rules

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Has-A vs IS-A

A

‘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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Supertype vs subtype

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Strong vs Weak Entity

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

When is a supertype/subtype relationships necessary?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Implications of having a relationship at supertype vs subtype level

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Completeness Constraints

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Disjointedness Constraints

A

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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Subtype discriminator

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Transposing Process

A
  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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Foreign key placement for different types of relations

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Participation Constraints

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Identifying vs non-identifying relationship

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Lookup Table

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Implementing a Foreign Key and Referential Integrity

A

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

24
Q

Transactions

A

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

25
Q

COMMIT

A

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

26
Q

Rollback

A

End/Cancel

Transaction Control Language

27
Q

Properties of a Transaction

A

Atomic
Consistency
Isolation
Durability

28
Q

Atomicity

A

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.

29
Q

Consistency

A

A transaction must leave the database in a consistent state.

A transaction must not violate database integrity constraints

30
Q

Isolation

A

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

31
Q

Durability

A

Committed data is permanently recorded.

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

32
Q

Relational Algebra

A

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.

33
Q

Theoretical Relational Algebra Operators

A

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

34
Q

Projection

A

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

35
Q

Selection

A

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

36
Q

SQL and Relational Algebra

A

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 .

37
Q

Union

A

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

38
Q

Union Compatible

A

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

39
Q

Commutative

A

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

Note that not all relational algebra operations are commutative

40
Q

Difference

A

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

41
Q

Subqueries

A

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

42
Q

Intersection

A

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

43
Q

Product

A

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

44
Q

Algebra Joins

A

Equijoin
Natural join -Also includes a projection

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

45
Q

Equijoin

A

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)

46
Q

Natural Join

A

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)

47
Q

Left Outer Join

A

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

48
Q

Right Outer Join

A

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.

49
Q

Full Outer Join

A

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

50
Q

Having Clause

A

§ 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

51
Q

SELECT CLAUSE order and which are optional

A

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

52
Q

Highly Abstract Tables

A

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

53
Q

Highly Abstract Tables Advantages and Disadvantages

A

Benefit:
§ Very flexible
§ Easy to handle unforeseen information

Downside
§ Queries get difficult

54
Q

Union compatible

A

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

55
Q

Join combo

A

Product selection projection

56
Q

Union compatible

A

Union, difference, intersection