LIS2780 Database Concepts Final Exam

Subquery

-A query that is embedded(nested) inside another query -Also known as a nested query or an inner query.

Primary Key

-Attribute or combination of attributes used to ensure that each row in a table is uniquely identified -A candidate key selected to uniquely identify all other attribute values in any given row. i. Cannot contain null values

Chen Model vs. Crow's Foot Model

-The left side of the ER diagram shows the Chen notation, based on Peter Chen's landmark paper. In this notation, the connectivity's are written next to each entity box. Relationships are represented by a diamond connected to the related entities through a relationship line. The relationship name is written inside the diamond. -The right side of Figure 2.3 illustrates the Crow's Foot notation. The name "Crow's Foot" is derived from the three-pronged symbol used to represent the "many" side of the relationship. As you examine the basic Crow's Foot ERD in Figure 2.3, note that the connectivity's are represented by symbols. For example, the "1" is represented by a short line segment, and the "M" is represented by the three-pronged "crow's foot." In this example, the relationship name is written above the relationship line.

Ternary Relationship

A ___ relationship exists when three entities are associated. Although higher degrees exist, they are rare and are not specifically named.

Binary relationship

A ___ relationship exists when two entities are associated. i. Most common

Unary relationship

A ____ relationship exists when an association is maintained within a single entity. i. Association within an entity (course is a pre-rec to another course)

Weak entity:

A _____ entity is one that meets two conditions: 1. The entity is existence-dependent; that is, it cannot exist without the entity with which it has a relationship. 2. The entity has a primary key that is partially or totally derived from the parent entity in the relationship.

Transitive Dependency

A condition in which an attribute is dependent on another attribute that is not part of the primary key i. More difficult to identify among a set of data

a. Data redundancy

A condition that exists when a data environment contains redundant (unnecessarily duplicated) data. i. Produces data integrity problems

ERD (Entity Relationship Diagram)

A diagram that depicts an entity relationship model's entities, attributes, and relations. b. Diagramming techniques and conventions (i.e., how relationships are illustrated)

INSERT

Adds records i. Inserts rows into a table

First Normal Form (1NF)

All the key attributes are defined (no repeating groups in the table) and when all remaining attributes are dependent on the primary key.

Second Normal Form (2NF)

All the key attributes are defined (no repeating groups in the table), all remaining attributes are dependent on the primary key, and it includes no partial dependencies.

Non-overlapping subtypes

Also known as disjoint subtypes, ____ are subtypes that contain a unique subset of the supertype entity set; in other words, each entity instance of the supertype can appear in only one of the subtypes. For example, in figure 5.2 (pg. 150), an employee (supertype) who is a pilot (subtype) can appear only in the PILOT subtype, not in any of the other subtypes. In Visio, such disjoint subtypes are indicated by the letter "d" inside the category shape.

Supertype (entity set)

An entity (set) that contains the general (commonly shared) characteristics of an entity subtype. If the entity set can include characteristics that are not common to all entities within the set, the ____ becomes the parent to one or more subtypes in a generalization hierarchy.

Subtype (entity set)

An entity (set) that contains the unique characteristics (attributes) of an entity whose general characteristics are found in another, more broadly defined entity known as a supertype. In a generalization hierarchy, it is any entity that is found below a parent entity. Example: The ____ PILOT of the supertype EMPLOYEE.

Determinant

Any attribute in a specific row whose value directly determines other values in that row. If you have three different transitive dependencies, you will have three different determinants. As with the conversion to 2NF, it is important that the determinant remain in the original table to serve as a foreign key. i. In a BCNF every ____ is a candidate key.

Integer (INT)

Are (whole) counting numbers, so they cannot be used if you want to store numbers that require decimal places.

b. Foreign Key

Attribute or combination of attributes in one table whose values must match the primary key in another (parent) table or whose values must be null.

Nonprime attribute

Attribute that is not part of a key

Total Completeness

Each instance of a supertype may appear in more than one subtype i. - Overlapping subtype

a. Existence-independent

Entity that can exist apart from one or more related entities. i. Created first when referencing an existence dependent table for it.

Boyce-Codd normal form (BCNF)

Every determinant in the table is a candidate key.

Character: CHAR(L)

Fixed-length character data for up to 255 characters.

Specialization

Grouping of UNIQUE attributes into a SUBtype entity b. Generalization: grouping of COMMON attributes into a SUPERtype entity

GROUP BY

Groups the selected rows based on one or more attributes.

Data Integrity

In a relational database, refers to a condition in which the data in the database is in compliance with all entity and referential integrity constraints.

Partial Dependency

In normalization, a condition in which an attribute is dependent on only apportion (subset) of the primary key. i. Is a functional dependence in which the determinant is only part of the primary key ii. Usually straight forward and easy to identify

Composite Key

Keep in mind that it might take more than a single attribute to define functional dependence; that is, a key may be composed of more than one attribute. Such a multi-attribute key is known as a _____ ___. i. Useful for: 1. Composite entities (resolving M:N relationship) 2. Weak entities with strong relationships

Prime Attribute

Key attribute that is part of a key or is the whole key. i. Helps form a primary key

SMALLINT

Like INTEGER but limited to integer values up to six digits. 1. If value is small, use SMALLINT instead of INT.

DECIMAL(L,D)

Like the NUMBER specification, but the storage length is a minimum specification. That is, greater lengths are acceptable, but smaller ones are not. 1. DECIMAL(9,2), DECIMAL (9), and DECIMAL are all acceptable.

UPDATE

Makes changes to data tables i. Modifies an attribute's values in one or more table's rows

Optionality

Means that one entity occurrence does not REQUIRE a corresponding entity occurrence in a particular relationship i. Has one or more optional relationships ii. Represented by an O

Weak Relationship

Non-identifying relationship. i. Exist when the PK of the related entity does not contain a PK component of the parent entity.

1:1

One instance of an entity (A) is associated with one other instance of another entity (B). For example, in a database of employees, each employee name (A) is associated with only one social security number (B).

M:N

One instance of an entity (A) is associated with one, zero or many instances of another entity (B), and one instance of entity B is associated with one, zero or many instances of entity A. For example, for a company in which all of its employees work on multiple projects, each instance of an employee (A) is associated with many instances of a project (B), and at the same time, each instance of a project (B) has multiple employees (A) associated with it.

1:M

One instance of an entity (A) is associated with zero, one or many instances of another entity (B), but for one instance of entity B there is only one instance of entity A. For example, for a company with all employees working in one building, the building name (A) is associated with many different employees (B), but those employees all share the same singular association with entity A.

ORDER BY

Orders the selected rows based on one or more attributes. i. Organizes data

Existence-dependent

Property of an entity whose existence depends on one or more other entities. i. In implementation terms, an entity is ______ if it has a mandatory foreign key—that is, a foreign key attribute that cannot be null.

FROM

Pulls data from specified tables

DELETE

Removes records (deletes one or more rows from a table)

Strong Relationship

Requires weak or existent-dependent entities that have primary keys partially or fully made up of the parent entity's primary key. i. In a Crow's Foot ERD, a _____ _____ is depicted with a solid line connecting the two entities.

HAVING

Restricts the selection of grouped rows based on a condition.

WHERE

Restricts the selection of rows based on a conditional expression. i. Pulls data based on at least one attribute's specific value

SELECT

Returns information from the server i. Selects attributes from rows in one or more tables or views

MAX

Returns the maximum attribute value found in a given column.

MIN

Returns the minimum attribute value found in a given column.

COUNT

Returns the number of rows with non-null values for a given column.

SUM

Returns the sum of all values for a given column.

COMMIT SQL command

SQL command that permanently saves data changes to a database.

ROLLBACK SQL command

SQL command that restores the database table content to its last condition, to the condition that existed after the last commit statement

DATE

Stores dates in the Julian date format.

Overlapping Subtypes

Subtypes that contain non-unique subsets of the supertype entity set; that is, each entity instance of the supertype may appear in more than on subtype. For example, in a university environment, a person may be an employee or a student or both. In turn, an employee may be a professor as well as an administrator. Because an employee may also be a student, STUDENT and EMPLOYEE are overlapping subtypes of the supertype PERSON, just as PROFESSOR and ADMINISTRATOR are overlapping subtypes of the supertype EMPLOYEE. Figure 5.4 (pg. 152) illustrates ______ subtypes with the use of the letter "o" inside the category shape.

Connectivity

Symbols i. Describes the classification of the relationship between entities.

Numeric SQL Data Type: NUMBER(L,D):

The declaration NUMBER(7,2) indicates numbers that will be stored with two decimal places and may be up to seven digits long, including the sign and the decimal place. 1. Ex: 12.32, -134.99

Normalization

The process for evaluating and correcting table structures to, - minimize data redundancies - reduce likelihood of data anomalies - minimize and/or eliminate data integrity problems

Normalization

The process for evaluating and correcting table structures to, - minimize data redundancies - reduce likelihood of data anomalies - minimize and/or eliminate data integrity problems -1NF→ 2NF→3NF

iVARCHAR(L) or VARCHAR2(L):

Variable-length character data. The designation VARCHAR2(25) will let you store characters up to 25 characters long. However, VARCHAR will not leave unused spaces. Oracle automatically converts VARCHAR to VARCHAR2.

Recursive Relationship

When an entity is related to itself

Third Normal Form (3NF)

When it includes no partial dependencies and it contains no transitive dependencies

Fourth Normal Form (4NF)

Whereas the second, third, and Boyce-Codd normal forms are concerned with functional dependencies, ___ is concerned with a more general type of dependency known as a multivalued dependency. A Table is in 4NF if and only if, for every one of its non-trivial multivalued dependencies X -->> Y, X is a superkey—that is, X is either a candidate key or a superset thereof.

Cardinality

____ expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity. In the ERD,____ is indicated by placing the appropriate numbers beside the entities, using the format (x,y).

Natural Identifier

a. A real-world, generally accepted identifier, used to identify real world objects. Familiar to end users and forms part of their day-to-day vocabulary. b. If one exists (and there are no security issues), a data modeler may use a ____ _____ as the primary key of the entity being modeled.

Dependency Diagram

a. A representation of all data dependencies (PK, Partial or Transitive) within a table i. - Arrows on top are good dependencies ii. - Arrows on bottom are bad (partial & transitive) iii. - Very helpful in getting a bird's-eye view of all of the relationships among a table's attributes, and their use makes it less likely that you will overlook an important dependency.

Repeating Groups

a. Characteristic describing a group of multiple entities of the same type that exist for a single key attribute occurrence. i. ____ ____ = multivalued attribute ii. Ex: A car can have multiple colors (top, interior, bottom, trim, and so on)

18. Specialization hierarchy (e.g. subtype/supertype relationship)

a. Depicts the arrangement of higher-level entity supertypes (parent entities) and lower-level entity subtypes (child entities). b. A hierarchy that is based on the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype. _____ is based on grouping unique characteristics and relationships of the subtypes.

Inheritence

a. Enables an entity subtype to inherit the attributes and relationships of the supertype. b. One important _____ characteristic is that all entity subtypes inherit their primary key attribute from their supertype. c. Note: in figure 5.2 (pg. 150) that the EMP_NUM attribute is the primary key for each of the subtypes.

Subtype discriminator

a. The attribute in the supertype entity that is used to determine to which entity subtype the supertype occurrence is related.

Strong Entity

an entity can exist apart from all of its related entities (it is existence-independent), then that entity is referred to as a ___ entity or regular entity.

Associative Entity

i. An entity designed to transform an M:N relationship into two 1:M relationships. ii. convey information about their attributes and their connections. They are considered an entity because they have attributes, and they are considered a relationship because they link entities together. These kinds of entities very frequently have many relationships and connections because they are attributable, but they can have independent meaning from other entities. It is also good practice in a relational model for the ____ ____ to have, at the very least, one attribute that is distinguishable from the identifier entity. ______ ____ can also be participants in relationships separate from the associated entity relationships.

Candidate Key

minimal (irreducible) superkey. i. A superkey that does not contain a subset of attributes that is itself a superkey.

Partial Completeness

not every supertype occurrence is a member of a subtype i. - A disjointed subtype/non-overlapping subtype

DE-Normalization

process which a table is changed from a higher level normal form to a lower normal form i. - Usually done to increase processing speed ii. - Potentially yields data anomalies iii. -3NF→2NF→1NF

Domain Key Normal Form

requires that the database contains no constraints other than domain constraints and key constraints. A domain constraint specifies the permissible values for a given attribute, while a key constraint specifies the attributes that uniquely identify a row in a given table. The _____ is achieved when every constraint on the relation is a logical consequence of the definition of keys and domains, and enforcing key and domain restraints and conditions causes all constraints to be met. Thus, it avoids all non-temporal anomalies.