Learn: DBMS Midterm 2 Exam

Left Outer Join

Shows join result + non-matching tuples from the left table, with NULL values for attributes of the right table.

Right Outer Join

Shows join result + non-matching tuples from the right table, with NULL values for attributes of the left table.

Full Outer Join

Shows join result + non-matching tuples from the left table + non-matching tuples from the right table.

NULLs in a Left Outer Join

Used for right-table attributes when a tuple from the left table has no match.

NULLs in a Right Outer Join

Used for left-table attributes when a tuple from the right table has no match.

NULLs in a Full Outer Join

Used for attributes from the opposite table when an unmatched tuple is included.

Entity-Relationship Model (ER Model)

Helps with conceptual design of the database; software/DBMS independent.

Entity

A real-world object about which data is/will be stored in a database.

Entity Type

A group of entities having the same attributes.

Entity Type Notation

Rectangle.

Relationship

Denotes an association between entity types.

Relationship Notation

Diamond.

Attribute

Each entity type has a set of attributes.

Attribute Notation

Oval connected to the corresponding entity type.

Key Attribute

Its value is unique for each entity in the entity type.

Key Attribute Notation

Underlined attribute.

Simple Attribute

An attribute that cannot be meaningfully divided into smaller attributes.

Composite Attribute

An attribute that can be further subdivided

Single-Valued Attribute

Each entity can have AT MOST ONE VALUE for the attribute

Multivalued Attribute

An entity MAY HAVE multiple values from the attribute

Stored Attribute

The attribute value is stored in the database.

Derived Attribute

The attribute value can be computed from other stored values.

Weak Entity

The entity is existent dependent on another entity.

Strong Entity

The entity is not existent dependent on any entity

Unary Relationship (Recursive)

Connects to the same entity type

Binary Relationship

Connects two entity types.

Ternary Relationship

Connects three different entity types.

Relationship Constraint

Describes restrictions on how entities participate in a relationship.

One-to-One (1:1)

One entity of one type will have at most one corresponding entity on the entity type and vice versa

One-to-Many (1:M)

One entity on one side may have multiple related entities on the other side. However, consider that it is only one-to-one on one side, while one-to-many on the other side

Many-to-Many (M:N)

Many entities on either side can be related to many entities on the other side.

Participation Constraint

Specifies whether entities of an entity type must participate in a relationship.

Total Participation

Entities from all of the corresponding entity type participate in the relationship.

Partial Participation

Some entities may not participate through the relationship.

Total Participation Notation

Double line between the entity type and relationship.

Partial Participation Notation

Single line between the entity type and relationship.

Normalization

A process that helps in determining proper attributes for tables.

Purpose of Normalization

Reduces data redundancy and creates more tables.

Normalization Order

1NF → 2NF → 3NF → BCNF → 4NF → 5NF.

Database Anomalies

Insertion anomaly, deletion anomaly, and modification anomaly.

Insertion Anomaly

A problem caused when inserting data into a poorly designed relation. (Primary key CANNOT be null. We also cannot just make up a key)

Deletion Anomaly

A problem where deleting data causes other desired information to be lost.

Modification Anomaly

The data is not properly changed for all data points

Data Redundancy

Repeated or duplicated data in a database.

First Normal Form (1NF)

No composite attributes and no multivalued attributes.

1NF Attribute Requirement

Attributes should contain single values.

Functional Dependency

An attribute B is functionally dependent on A iff for each unique value of A, there must be a unique value for B (A → B)

A → B

For each unique value of A, there exists no more than one value of B.

Determinant

The attribute on the left side of a functional dependency.

Full Functional Dependency

A is fully functional dependent of a group attributes (B, C, …). if dropping any one attribute from the determinant f.d. go away

Second Normal Form (2NF)

Every non-key attribute must be f.d. on key attributes

part of a key -/> non-key

2NF Requirement

Every non-key attribute must be fully functionally dependent on the key attributes.

Partial Dependency

Occurs when a non-key attribute depends on only part of a composite key.

Third Normal Form (3NF)

Schema must be in 2NF and no non-key should be transitively func. dependent on a key attribute

In 2NF Non-key -/> nonkey

Transitive Func. Dependency

If B is f.d. on A, C is f.d. on B, then C is transitively f.d. on A.

A —> B & B —> C, so A—> C

Boyce-Codd Normal Form (BCNF)

Every determinant must be a key, the whole key, nothing but the key.

Physical Database Design

Concerned with how data is physically stored.

File

A collection of records.

Record

A collection of fields stored together.

Fixed-Length Record

Each record has the same size.

Variable-Length Record

Each record could be of different size

Page

A unit used to store records in the database.

Page Size

The number of bytes available in a page.

Record Size

The number of bytes occupied by one record.

Blocking Factor

The number of records per page.

Blocking Factor Formula

Page size ÷ record size; use the whole-number result.

Spanned Records

The last records on a page spans to the next page

Unspanned Records

A record does not span across pages.

Pages Needed

Determine how many records fit per page, then determine how many pages are required.

Heap File

Records are not sorted. New records are added to the end of the last page.

Heap File Insertion

New records are added to the end of the last page.

Heap File Advantage

Insertion is faster.

Heap File Disadvantage

Searching is slower.

Heap File Search

May require searching records/pages until the desired record is found.

Sorted File

Records are stored in sorted order based on a search/key value.

Sorted File Search

Can use binary search based on the ID value.

Sorted File Advantage

Searching is faster.

Sorted File Disadvantage

Insertion is slower because we need to move records down every page

Binary Search

Search method used with sorted files to repeatedly reduce the search space.

Binary Search Pages Examined

Approximately log₂(number of pages).