SQL midterm

concatenation

String concatenation means to append one string to the end of another string. SQL allows us to concatenate strings but the syntax varies according to which database system you are using. Concatenation can be used to join strings from different sources including column values, literal strings, output from user defined functions or scalar sub queries etc.

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.

field

A field is an area in a fixed or known location in a unit of data such as a record, message header, or computer instruction that has a purpose and usually a fixed size. In some contexts, a field can be subdivided into smaller fields

functionally dependent

A functional dependency occurs when one attribute in a relation uniquely determines another attribute. This can be written A -> B which would be the same as stating "B is functionally dependent upon A."

what is a primary key?

A primary key, also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver license number, telephone number (including area code), or vehicle identification number (VIN). A relational database must always have one and only one primary key.

what is a repeating group?

A repeating group is a domain or set of domains, directly relating to the key, that repeat data across tuples in order to cater for other domains where the data is different for each tuple.

attribute

A single data item related to a database object. The database schema associates one or more attributes with each database entity.

that is a relational database?

Amazingly, "relation" in "relational" databases does not refer to the foreign key relationship of one table to another. "A relation is a data structure which consists of a heading and an unordered set of tuples which share the same type

what is a relation?

Amazingly, "relation" in "relational" databases does not refer to the foreign key relationship of one table to another. "A relation is a data structure which consists of a heading and an unordered set of tuples which share the same type

update anomaly

An Update Anomaly exists when one or more instances of duplicated data is updated, but not all.

define the first normal form?

An atomic value is a value that cannot be divided. For example, in the table shown below, the values in the [Color] column in the first row can be divided into "red" and "green", hence [TABLE_PRODUCT] is not in 1NF. A repeating group means that a table contains two or more columns that are closely related.

second normal form (2NF)

As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column depends only on one part of the concatenated key, then the table fails Second normal form.

what is an entity

Entity SQL is a SQL-like language that enables you to query conceptual models in the Entity Framework. Conceptual models represent data as entities and relationships, and Entity SQL allows you to query those entities and relationships in a format that is familiar to those who have used SQL.

how do you qualify a name for a field and when do you need to do this?

Geodatabases that reside in a relational database management system (RDBMS), such as Oracle, SQL Server, or IBM DB2, use standard database naming conventions for identifying tables for specific users. A RDBMS can contain thousands of tables from many different users; therefore, users of an enterprise geodatabase must understand how to correctly qualify the name of an object so the correct feature class, relationship class, feature dataset, or table is used during an operation.

relation

In SQL, CREATE TABLE syntax is used to define base tables. The following is an example. CREATE TABLE List_of_people ( ID INTEGER, Name CHAR(40), Address CHAR(200), PRIMARY KEY (ID) ) The Data Definition Language (DDL) is also used to define derived relation variables

record

In computer data processing, a record is a collection of data items arranged for processing by a program. Multiple records are contained in a file or data set. The organization of data in the record is usually prescribed by the programming language that defines the record's organization and/or by the application that processes it. Typically, records can be of fixed-length or be of variable length with the length information contained within the record.

that is a relationship? what is a one-to-many relationship?

In relational databases, a one-to-many relationship occurs when a parent record in one table can potentially reference several child records in another table. In a one-to-many relationship, the parent is not required to have child records; therefore, the one-to-many relationship allows zero child records, a single child record or multiple child records. The important thing is that the child cannot have more than one parent record. The opposite of a one-to-many relationship is a many-to-many relationship, in which a child record can link back to several parent records.

repeating groups

The term "repeating group" originally meant the concept in CODASYL and COBOL based languages where a single field could contain an array of repeating values. When E.F.Codd described his First Normal Form that was what he meant by a repeating group. The concept does not exist in any modern relational or SQL-based DBMS.

third normal form (3NF)

Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. For example, consider a table with following fields

what is a attribute

This attribute must be applied to all assemblies that are deployed to a Microsoft SQL Server 2005 database. This attribute has no parameters. It is added to the AssemblyInfo file when you create a SQL Server project.

one-to-many relationship

This is the most commonly used type of relationship. Consider an e-commerce website, with the following:Customers can make many orders. Orders can contain many items. Items can have descriptions in many languages.In these cases we would need to create "One to Many" relationships.

Boyce-Codd normal form (bcnf)

When a relation has more than one candidate key, anomalies may result even though the relation is in 3NF. 3NF does not deal satisfactorily with the case of a relation with overlapping candidate keys. i.e. composite candidate keys with at least one attribute in common.

first normal form (1NF)

is a property of a relation in a relational database. A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.

entity-relationship (E-R) diagram

is a way of graphically representing the logical relationships of entities (or objects) in order to create a database.

nonkey column

is the attribute or a set of attributes in an entity whose value(s) guarantee only one tuple (row) exists for each value. A unique key must consist of attributes that cannot be collectively duplicated by any other row.

entity

it is a noun that is put in to a system

database design

the processes of determine the particular tables and columns that will comprise a database

relational database

the way the tables relate to one another

candidate key

A candidate key is a single field or the least combination of fields that uniquely identifies each record in the table.

quality

Data quality is a perception or an assessment of data's fitness to serve its purpose in a given context.

redundancy

Data redundancy occurs in database systems which have a field that is repeated in two or more tables. For instance, when customer data are duplicated and attached with each product bought, then redundancy of data is a known source of inconsistency since customer might appear with different values for given attribute.

normalization

Database normalization (or normalisation) is the process of organizing the columns (attributes) and tables (relations) of a relational database to minimize data redundancy.

tuple

Most of the answers here are on the right track. However, a row is not a tuple. Tuples* are unordered sets of known values with names. Thus, the following tuples are the same thing (I'm using an imaginary tuple syntax since a relational tuple is largely a theoretical construct):

normal form

are defining characteristics of relational databases. SQL forms get classified according to the types of modification anomalies they're subject to. First, second, and third normal forms (1NF, 2NF, 3NF) serve as remedies to the three main sources of modification anomalies.

relationship

are the established associations between two or more tables. Relationships are based on common fields from more than one table, often involving primary and foreign keys. A primary key is the field (or fields) that is used to uniquely identify each record in a table.

primary key

constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key.