Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
26 Cards in this Set
- Front
- Back
|
What are the mathematical foundations of T-SQL
|
It is based on standard SQL, which is based on the "relational model", which is based on "set theory" & "predictate theory"
|
|
|
1) What is relational model?
2) What is it in terms of SQL? |
1) A mathematical model for data management and manipulation
2) A "table" |
|
|
A relation, "table" has two components:
|
A table has a Heading and a body.
|
|
|
What is a "heading?"
|
A heading is a set of attributes, each of a given type (what SQL attempts to represent with columns).
|
|
|
What is a "body"?
|
A "body" is a set of "tuples", which is an ordered set of elements. (SQL attempts to represent as rows)
|
|
|
What is a set:
|
A set is a colletion of elements into a whole of definite, distict objects.
|
|
|
A set has no ......
|
Order
|
|
|
What is a sequence?
|
An ordered set
|
|
|
A set is "distinct", meaning:
|
It has no duplicates. If something is true, it can't be more true
|
|
|
A set should be considered as a "whole", meaning?
|
You do not interact with the individual elements of the set, but rather the set as a whole.
|
|
|
What is a "predicate"?
|
A predicate is an expression that when attributed to some object, make a proposition either true or false.
It is a parameterized proposition. Eg. "salary greater than R5k" It can enforce data integrity |
|
|
A relation (SQL attempts to represent with a table) has a heading and a body, what are they?
|
A "heading" is a set of attributes (SQL attempts to represent as a column), each of a given type.
The "body" is a set of tuples. Tuples are an ordered set of elements. |
|
|
What are the mathematical branches that the relational model are based on?
|
Set theory & predicate logic.
|
|
|
What is the difference between SQL & T-SQL?
|
SQL is standard
T-SQL is the dialect & exoension of SQL which Microsoft use in its RDBMS - SQL Server |
|
|
To follow relational theory, you need to enforce ....
|
Uniqueness.
I.e. your table needs a "key" |
|
|
T-SQL is based not so much on set theory (uniqueness), but on ....
|
A "multiset" (also known as a "bag" or a "superset".
Allows duplicates, eg many KZN in a query |
|
|
To remove duplicates from a query: SELECT .....
|
SELECT DISTINCT country
|
|
|
If you want to order the rows, you need to add .....
|
ORDER BY
SELECT empid, lastname FROM HR.Employees ORDER BY empid; |
|
|
What is a "cursor"
|
What SQL calls the order of rows where the output is guarenteed by the heading attribute
The column is ordered |
|
|
Does SQL allow "Null" values?
|
Yes.
You can assign a Null value |
|
|
Name two aspects in which T-SQL deviates from the Relational model
|
1) A relation has a body with a distinct set of tuples (doesn't allow buplicates), where as a table doesn't have to have a key.
2) T-SQL allows referring to ordinal positions of columns with ORDER BY (you can order) |
|
|
To make sure a table is relational, you need to ....
|
define a "key"
|
|
|
What are the correct T-SQL definitions of a field & a record
|
T-SQL calls a field a "column" & a record a "row"
|
|
|
Can you refer to a "NULL value"?
|
No!
A NULL is a mark for a missing value, not a value its self |
|
|
Why are the terms "field" and "record" incorrect when referring to a column & a row?
|
A field and a record describe physical things, where are columns and rows are logical elements of a table.
|
|
|
Why is it important to use the standard SQL code where possible?
|
Standard code is more portable between platforms
|
