Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Database Theory > DB/SQL Terminology > Flashcards

DB/SQL Terminology Flashcards

1
Q

What is a database?

A

A shared collection of logically related data, designed to meet the information needs of multiple users. A collection of related tables and other objects (views, triggers, procedures…)

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

Define: Character

A

Various alphanumerical characters, digital, and special characters. eg. D b 4 7 *

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

Define: Column

A

Sometimes referred to as a field, the vertical columns of a database that contains actual value consisting of one or more characters. Each column contains the same type of information for every record in the table. Common columns relate data in multiple tables.

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

Define: Row

A

Sometimes referred to as a record, the horizontal rows of a database. Each row contains one or more columns, and each row in a table contains the same columns.

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

Define: Table

A

A collection of related rows. Tables can be linked together to view (or manipulate) data as if one table.

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

Define: Entity

A

A person, place or thing, represented by a table

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

Define: Attribute

A

A descriptor of an entity, represented by column headings

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

Define: Data Dictionary

A

The repository of all data definitions for all objects within the scope of the database. It contains meta-data. eg. table name, column name, data constraints, primary and foreign keys, index

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

What does DDL stand for?

A

Data Definition Language

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

Give Data Definition Language (DLL) examples.

A

CREATE table CREATE index

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

What does DML stand for?

A

Data Manipulation Language

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

Give Data Manipulation Language (DML) examples.

A

SELECT, INSERT, UPDATE, DELETE

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

Define: Expressions

A

An expression can be: - A column name - Text - ‘this is a text string’, 10, last_name + ‘, ‘ + first_name - A function - LEN(‘Blake’), SQRT(144) + 5, STR(SQRT(99), 5, 3), GETDATE()

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

Define: Conditions

A

Purpose: To specify a combination of one or more expressions and logical operators that evaluates to either TRUE, FALSE or unknown Comparison operators =, !=, >, =, <=, [NOT] IN, [NOT] BETWEEN x AND y, [NOT] LIKE, EXISTS, IS [NOT] NULL

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

What are _ and % used for in expressions?

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

What are the different built-in functions?

A

Scalar, Column, Mathematical, String, Date and Time

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

Give examples of scalar functions.

A

Number, Character, Date

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

Give examples of mathematical functions.

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

Give examples of string functions.

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

Give examples of date and time functions.

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

Give column function examples.

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

What is the function at groups data?

A

GROUP BY

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

What is the function to sort data?

A

ORDER BY

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

Define: Relation

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

Define: Tuple

A

A row of data (another name for a row)

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

Define: Attribute

A

A column in a table (another name for column)

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

Define: Primary Key

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

Define: Determinant

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

Define: Functional Dependency

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

Define: Candidate Key

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

What does the CREATE TABLE command do?

A

creates the table and how the data is laid out

32
Q

What does the DROP TABLE command do?

A

Removes the table

33
Q

What does the ALET TABLE command do?

A

changes the data layout of the table

34
Q

What does the SELECT command do?

A

selects rows of a data from a table

35
Q

What does the INSERT command do?

A

inserts rows of data into a table

36
Q

What does the UPDATE command do?

A

changes rows of data in a table

37
Q

What does the DELETE command do?

A

removes rows of data from a table

38
Q

What does DCL stand for?

A

Data Control Language

39
Q

Define: Privileges

A

The ability to say who will and will not be allowed to see and/or ammend the data.

40
Q

Define: Data Control Language and give examples.

A

You can say who will and will not be allowed to see and/or edit the data. These are called privileges. The commands are:

GRANT: to give privileges to a user

REVOKE: to remove privileges from a user

41
Q

What is the Law to enforce Database Normalization?

A

Codd’s Law

42
Q

Explain Codd’s Law

A

The following are Codd’s original 13 rules:

Often referred to as rule 0, this rule states that all subsequent rules are based on the notion that in order for a database to be considered relational, it must use its relational facilities exclusively to manage the database.

  1. The Information rule: All information in an RDBMS is represented logically in just one way - by values in tables.
  2. The Guaranteed Access rule: Each item of data in an RDBMS is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name.
  3. The Systematic Treatment of Null Values rule: Null values (distinct from an empty character string or a string of blank characters and distinct from zero or any other number) are supported in a fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of the data type.
  4. The Dynamic Online Catalog Based on the Relational Model rule: The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.
  5. The Comprehensive Data Sublanguage rule: A relational system may support several languages and various modes of terminal use (for example, the fill-in-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and whose ability to support all of the following is comprehensible: data definition, view definition, data manipulation (interactive and by program), integrity constraints, and transaction boundaries (begin, commit, and rollback).
  6. The View Updating rule: All views of the data which are theoretically updatable must be updatable in practice by the DBMS.
  7. The High-level Insert, Update, and Delete rule: The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update, and deletion of data.
  8. The Physical Data Independence rule: Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods.
  9. The Logical Data Independence rule: Application programs and terminal activities remain logically unimpaired when information preserving changes of any kind that theoretically permit unimpairment are made to the base tables.
  10. The Integrity Independence rule: Integrity constraints must be definable in the RDBMS sub-language and stored in the system catalogue and not within individual application programs.
  11. The Distribution Independence rule: An RDBMS has distribution independence. Distribution independence implies that users should not have to be aware of whether a database is distributed.
  12. The Nonsubversion rule: If the database has any means of handling a single record at a time, that low-level language must not be able to subvert or avoid the integrity rules which are expressed in a higher-level language that handles multiple records at a time.
43
Q

Define: Null

A

A column for which the value is not currently known or applicable will have no data inside of it, making it null.

44
Q

Give examples of when null values are acceptable.

A

When the value of the data item is not yet known, or when the value for that data item is yet to be entered into the system.

45
Q

How do you properly check for a null column?

A

you use WHERE item IS NULL (not = or equals)

46
Q

Is null = to null?

A

No, just because each item is null doesnt mean they are equal, but they do not have a value at all.

47
Q

Define: Entity Relationship (rule)

A

The column or columns used to form a primary key are not allowed to contain null values

48
Q

Define: Atomic Attribute

A

A component of the record definition that is used to describe an entity. A field, unique meaningful names. eg. postal_code

49
Q

Define: Composite Attribute

A

Composed of atomic attributes, each defined in the data dictionary.

eg. Address is comprised of: street_address, city, province_id, postal_code

50
Q

Define: Derived Attribute

A

An attribute whose value is obtained by applying a formula to other data elements.

eg. sales commission = sales * percentage

age = DATEDIF(YEAR, birth_date, GETDATE())

51
Q

Define: Multi-Valued Attribute

A

An attribute with multiple possible values.

eg. An employee can have more than one skill

52
Q

Define: Alias

A

Synonym, A different name for a data element with the same meaning.

eg. SELECT COUNT(*) AS total FROM patients

the alias for the count being “total”

53
Q

Define: Domain

A

The set of possible values an attribute can take on

54
Q

Define: Concatenated Key

A

Also known as Composite Key or Compound key, when two or more attributes taken together to uniquely identify a record.

eg. - patient_id, admission_date in admissions table
- purchase_order_id, line_num in purchase_order_lines table

55
Q

Define: Secondary Key

A

Requires an index may not be unique.

eg. last_name

56
Q

Define: Sort Key

A

Used to physically sequence a file.

eg. seniority listing of employee records

57
Q

Define: Foreign Key

A

A field (or multiple fields) in a table that uniquely identifies a row of another table (or the same table).

eg. nursing_unit_id in admissions table

58
Q

Define: Entity Occurrence

A

Represented by a record with actual data values in it, or a row in a table

59
Q

Define: Schema

A

The definition of an entire databse. Although in SQL Server, schemas are used as containers to organize database objects.

60
Q

What are the 3 stages in database design?

A

Stage1: Develop the conceptual model

Stage2: Convert the conceptual model to an internal model

Stage3: Map the internal model to a physical model

61
Q

Explain Stage1: Develop Conceptual Model

A

In this model we describe each entity completely (identify all its attributes).

For each entity we identify the key attribute and the dependent attributes.

Between pairs of entities we identify relationships.

62
Q

Explain Stage2: Convert conceptual to internal model

A

Here we set up an internal model that is compatible with the DBMS we have selected. The internal model is also known as a schema. In the internal model, the relationships between entities fit the structure of a hierarchy or a network or exist as relations between tables. The internal model is required to satisfy each user view (external model)

63
Q

Explain Stage3: Map the internal model to a physical model

A

The physical model specifies what devices the data is storedd, how the data is stored, what indexing techniques are used for direct access to records and what file organizations are used

64
Q

Define: System Catalog

A

A detailed system data dictionary that describes all objects in the database.

Also referred to as Metadata, Data dictionary, System Tables (SQL Server) and System dictionary.

65
Q

How would you create a table?

A

When creating tables, constraints (primary key, check, foreign key) can be specified inline or at the end of the statement. Or tables can be created interactively by rightclicking on Tables and selecting Table… Fill in the data from there.

66
Q

What are the most commonly used data types?

A
67
Q

Define: Idex

A

A separate structure pointing to the table data, Multiple indices allowed on one table, Consists of one or more columns, Use is transparent once created

Advantage:

Used to reduce access time

Disadvantage:

Requires storage space, Requires work space, Needs occasional maintenance (rebuilding), Slows down INSERT, DELETE and possibly UPDATE processing.

68
Q

Define: Binary Search Algorithm

A

Only works with ordered (sorted) list, Check the middle value, if that is the target, done, If target is lower, eliminate all higher values, If target is higher, eliminate all lower values, Loop until done

69
Q

Define: Index Clustering

A

Physically sorts the data according to the clustered index, Only 1 clustered index allowed per table, Frequently, the primary key is clustered. However, based on usage patterns it may make sense to cluster a different index

70
Q

How would you create an idex?

A
71
Q

Define: Views

A

A virtual table based on one or more tables

The View result set is not permanently stored in the database, but generated each time it is used

Shows up in INFORMATION_SCHEMA.VIEWS and INFORMATION_SCHEMA.TABLES

Why do we use Views?

Provide a level of security

Provide a mechanism to customize appearance

Simplify frequently used complex SQL

72
Q

Give some examples of creating views

A
73
Q

Define: Subquery

A

Sometimes it is necessary to use data from one table to access data in another table. A query within a query that must return only one column. May return a single row or multiple rows, depending how it is used and can be neste

74
Q

Define: CRUD

A

Create, Read, Update, Delete

All databases support this

75
Q

Define: SQL Script

A

Many SQL statements packaged together in a single file to be executed all together in sequence.

Database Theory (7 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions