Database Test 2 Key Terms

Candidate key

A column, or set of columns, in a table that can uniquely identify any database record without referring to any other data.

Functional dependency

A constraint between two attribute or two sets of attributes.

Correlated subquery

A correlated subquery is a subquery (a query nested inside another query) that uses values from the outer query. The subquery is evaluated once for each row processed by the outer query.

Materialized view

A database object that contains the results of a query. It may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result. Or may be a summary based on aggregations of a tables data.

Determinant

A determinant in a database table is any attribute that you can use to determine the values assigned to other attribute(s) in the same row.

Foreign key

A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables. Foreign keys need not have unique values in the referencing relation.

Partial Functional Dependency

A functional dependency in which one or more nonkey attributes are functionally dependent on part, but not all, of the primary key

Physical file

A low-level view of the physical characteristics of a file, such as its location on a disk or its physical structure, for example, whether indexed or sequential.

What is a recursive, foreign key? Explain and provide an example?

A recursive foreign key is a foreign key that represents the domain of the primary that it has the relation to. An example is a manager and his or hey employees. Primary key is employee id, foreign key is manager's id. Refers back to same relation that it is a part of.

Boyce-Codd Normal Form (BCNF)

A relation has been normalized so that all determinants are candidate keys.

When is a relation in first normal form? What criteria must be met? Explain.

A relation is in first normal form when there is no repeating groups and all of the values are atomic/no composite attributes (same thing)

4th Normal Form

A relation is in which normal form if it is in BCNF and has no multivalued dependencies?

Index

A separate table that contains organization of record for quick retrieval. Helps find things within database, find content within a specific column.

Normal form

A state of a relation that results from applying simple rules regarding functional dependencies to that relation.

RAID

A storage technology that combines multiple disk drive components into a logical unit. Data is distributed across the drives in one of several ways called "RAID levels", depending on the level of redundancy and performance required.

Record or row

A synonym for tuple in a relational database

What is a relation? Define and explain.

A table in a database. A relations contains fields(columns) and rows (tuples). Usually associated with a data point or thing, but broken down further. Tells us that the attributes it contains are interconnected.

Relation

A table in a database. Has tuples and attributes.

Surrogate key

A unique, DBMS-supplied identifier used as the primary key of a relation

What are the two parts of the internal schema? Define and explain.

ANSI-Spark 3 schema model; was on last test as well. Physical schema and logical schema.

What is a well-structured relation? Define and explain.

All the attributes are atomic. Can easily create, update, and delete records without anomalies. Is in 3rd normal form.

Equi-join

An equi-join is a specific type of comparator-based join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join.

Transitive dependency

An indirect functional dependency (x-->Z because x---Y and y-->z)

Physical database design

Specifies the physical configuration of the database on the storage media. This includes detailed specification of data elements, data types, indexing options and other parameters residing in the DBMS data dictionary.

What is the purpose of physical database design? Explain.

To fit with the infrastructure. Efficiency query, performance all become issues. trying to get a good fit between the data we want to store and the environment we want to store it in.

What is the goal of database normalization? State and explain.

Avoid redundancies so we don't get anomalies. Intended to ensure the integrity of the data. Anomalies lead to corruption of our database.

Outer join

Doesn't require each record in the two joined tables to have a matching record. The joined table retains each record - even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained (left, right, or both).

How does a hashing algorithm convert a primary key value into a physical record address? Briefly explain?

Estimate the number of records. find the prime number thats closest to the number of records and create a sequence number and divide by prime number and that is the storage

Transitive Functional Dependency

If attribute A determines attribute B, and attribute B determines attribute C, then what type of dependency exists between B and C?

What are two advantages of partitioning? Explain each.

Improve security and query performance.

What is a functional dependency? Define and explain.

In relation models have to be concerned about these when one attributes uniquely defines another attribute says that the value of one determines the value of another Example: In a table with attributes of employee name and Social security number relies there is a functional dependency between the two because social security number depends on the employee name

What is referential integrity? Define and explain.

Referential integrity a concept for insuring relationships between database tables remain consistent. Implement by using foreign keys and using constraints that say we have to have a tuple in fk field to create relationship. Can be used to control records being entered in database and when records are being deleted. (Dependencies removed from database if employees are)

Domain

Refers to all the values which a data element may contain.

Referential integrity constraint

Requires every value of one attribute of a relation to exist as a value of another attribute in a different (or the same) relation.

Indexed file organization

Rows in the file are stored in sequence according to a primary key value. Updating and adding records may require rewriting the file. Deleting records results in wasted space.

What are the 3 primary Relational Algebra operators? List and explain.

selection- is like select, from, where in SQL. will filter results Projection- shows columns you want, specifies the subset of comumn join- set all of the combinations of tuples in both tables that are equal on their common attribute names.

What is the purpose for building a logical data model? List and explain.

to help us get more detail and communicate with database professional and project team make sure what we have makes sense and has business names/terms most of the time a logical model is normalized

Subtype entity

type of entity has its relationship to another entity determined by an attribute in that other entity called a discriminator

How does horizontal partitioning differ from vertical partitioning? Define and explain.

Horizontal deals with rows and vertical partitions on columns. If we have a relation with 10000 rows or tuples horizontal says we can put 50,000 in one and 50,000 in the other. Vertical partition says that we divide and take out some of the columns in a different space because of the information it contains (credit card etc)

Weak Entity

Which type of entity cannot exist in the database unless another type of entity also exists in the database, but does not require that the identifier of that other entity be included as part of its own identifier?

Dynamic SQL

Can be built at run time and placed in a string host variable. Not hard coded into the program like static SQL.

What are two major criticisms of SQL?

Can be large differences between different vendors offerings Base SQL is very incomplete

Well-structured relation

Contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies.

What operations are classified as Data Manipulation Language (DML)? List.

Create(Insert Into), Read (select), Update, Delete.

Database design

Database design is the process of producing a detailed data model of a database. This logical data model contains all the needed logical and physical design choices and physical storage parameters needed to generate a design in a data definition language, which can then be used to create a database.

What type of language is Structured Query Language (SQL)? Explain.

Declarative. Non-procedural. Set-oriented language. Query language. ANSI-standard. A database language. You don't tell the language how to get it, just what you want it to get hence declarative.

Composite key

Is a combination of two or more columns in a table that can be used to uniquely identify each row in the table.

Partition

Is a division of a logical database or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance or availability reasons. Common criteria for partitioning are: range, list, hash, and composite. Partitioning method include horizontal and vertical.

Data Manipulation Language (DML)

Is a family of syntax elements similar to a computer programming language used for selecting, inserting, deleting and updating data in a database. Performing read-only queries of data is sometimes also considered a component of DML.

Data Definition Language (DDL)

Is a syntax similar to a computer programming language for defining data structures, especially database schemas.

Denormalization

Is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.

Enterprise Key

Primary keys that are unique in the whole database, not just within a single relation.

Anomaly

Problem with the information in a database. There are three types of anomalies: update, deletion and insertion.

What is RAID? Define and explain.

Raid is the way of storing information and involves multiple servers and storage places, all networked together. More of a storage array involving parity and striping, meant for transaction processing of important data. Most common raid is #5.

Sequential file organization

Records are stored in sequential order, based on the value of the search key of each record.

What are two benefits of a standardized relational language? List and explain.

Reduce training costs improve productivity

Entity integrity rule

States that every table must have a primary key and that the column or columns chosen to be the primary key should be unique and not NULL.

Dynamic view

Supports the creation and use of freely entered SQL statements

Hashed file organization

The address for each row is determined using an algorithm.

Recursive foreign key

The child and parent table may, in fact, be the same table. Is known as a self-referencing foreign key.

Normalization

The process of organizing the fields and tables of a relational database to minimize redundancy and dependency

External Key

Type of key is UPC (Universal Product Code) in the OrderLines and Products tables

Primary key

Uniquely specifies a tuple within a table. In order for an attribute to be a good primary key it must not repeat. Surrogate keys are often used as primary keys since it can uniquely identify a tuple. Composite keys are also used as primary keys.

What anomalies does normalization to third normal form help avoid? List and explain.

Update, insertion and deletion. Make sure that we update something in every place in the database, make sure that we eliminate redundancy so update doesn't affect everything. In third normal form

Hashing algorithm

Usually division remainder to determine record position. Dividing each primary key value by a suitable prime number and then using the remainder of the division as the relative storage location. Records with same storage location are grouped in lists.