DB/SQL Terminology Flashcards
What is a database?
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…)
Define: Character
Various alphanumerical characters, digital, and special characters. eg. D b 4 7 *
Define: Column
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.
Define: Row
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.
Define: Table
A collection of related rows. Tables can be linked together to view (or manipulate) data as if one table.
Define: Entity
A person, place or thing, represented by a table
Define: Attribute
A descriptor of an entity, represented by column headings
Define: Data Dictionary
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
What does DDL stand for?
Data Definition Language
Give Data Definition Language (DLL) examples.
CREATE table CREATE index
What does DML stand for?
Data Manipulation Language
Give Data Manipulation Language (DML) examples.
SELECT, INSERT, UPDATE, DELETE
Define: Expressions
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()
Define: Conditions
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
What are _ and % used for in expressions?
What are the different built-in functions?
Scalar, Column, Mathematical, String, Date and Time
Give examples of scalar functions.
Number, Character, Date
Give examples of mathematical functions.
Give examples of string functions.
Give examples of date and time functions.
Give column function examples.
What is the function at groups data?
GROUP BY
What is the function to sort data?
ORDER BY
Define: Relation
Define: Tuple
A row of data (another name for a row)
Define: Attribute
A column in a table (another name for column)
Define: Primary Key
Define: Determinant
Define: Functional Dependency
Define: Candidate Key
What does the CREATE TABLE command do?
creates the table and how the data is laid out
What does the DROP TABLE command do?
Removes the table
What does the ALET TABLE command do?
changes the data layout of the table
What does the SELECT command do?
selects rows of a data from a table
What does the INSERT command do?
inserts rows of data into a table
What does the UPDATE command do?
changes rows of data in a table
What does the DELETE command do?
removes rows of data from a table
What does DCL stand for?
Data Control Language
Define: Privileges
The ability to say who will and will not be allowed to see and/or ammend the data.
Define: Data Control Language and give examples.
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
What is the Law to enforce Database Normalization?
Codd’s Law
Explain Codd’s Law
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.
- The Information rule: All information in an RDBMS is represented logically in just one way - by values in tables.
- 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.
- 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.
- 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.
- 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).
- The View Updating rule: All views of the data which are theoretically updatable must be updatable in practice by the DBMS.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
Define: Null
A column for which the value is not currently known or applicable will have no data inside of it, making it null.
Give examples of when null values are acceptable.
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.
How do you properly check for a null column?
you use WHERE item IS NULL (not = or equals)
Is null = to null?
No, just because each item is null doesnt mean they are equal, but they do not have a value at all.
Define: Entity Relationship (rule)
The column or columns used to form a primary key are not allowed to contain null values
Define: Atomic Attribute
A component of the record definition that is used to describe an entity. A field, unique meaningful names. eg. postal_code
Define: Composite Attribute
Composed of atomic attributes, each defined in the data dictionary.
eg. Address is comprised of: street_address, city, province_id, postal_code
Define: Derived Attribute
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())
Define: Multi-Valued Attribute
An attribute with multiple possible values.
eg. An employee can have more than one skill
Define: Alias
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”
Define: Domain
The set of possible values an attribute can take on
Define: Concatenated Key
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
Define: Secondary Key
Requires an index may not be unique.
eg. last_name
Define: Sort Key
Used to physically sequence a file.
eg. seniority listing of employee records
Define: Foreign Key
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
Define: Entity Occurrence
Represented by a record with actual data values in it, or a row in a table
Define: Schema
The definition of an entire databse. Although in SQL Server, schemas are used as containers to organize database objects.
What are the 3 stages in database design?
Stage1: Develop the conceptual model
Stage2: Convert the conceptual model to an internal model
Stage3: Map the internal model to a physical model
Explain Stage1: Develop Conceptual Model
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.
Explain Stage2: Convert conceptual to internal model
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)
Explain Stage3: Map the internal model to a physical model
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
Define: System Catalog
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.
How would you create a table?
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.
What are the most commonly used data types?
Define: Idex
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.
Define: Binary Search Algorithm
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
Define: Index Clustering
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
How would you create an idex?
Define: Views
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
Give some examples of creating views
Define: Subquery
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
Define: CRUD
Create, Read, Update, Delete
All databases support this
Define: SQL Script
Many SQL statements packaged together in a single file to be executed all together in sequence.
