Foundations of Relational SQL Flashcards
(34 cards)
Why write SQL in a standard way whenever possible?
- It’s more portable between platforms
- Your knowledge is more portable”
SQL Server 70-461 01-01
What mathematical model is standard SQL based on?
The Relational Model
SQL Server 70-461 01-01
How is a relation, from the Relational Model, represented in SQL?
SQL is not able to perfectly represent a relation. However, its best attempt to represent a relation is with a table.
SQL Server 70-461 01-01
What two components is does a relation have?
A heading and a body
SQL Server 70-461 01-01
What does a heading consist of?
A heading consists of a set of attributes, which SQL attempts to represent with columns.
SQL Server 70-461 01-01
What two components should each attribute have to be relational?
- A name that is unique. To be relational no two attributes should have the same name
- An assigned type.
SQL Server 70-461 01-01
What order are the attributes in?
The attributes are not required to be in any specific order
SQL Server 70-461 01-01
What order will the attributes be in if you use a SELECT * statement?
They will be ordered based on the order in which they were defined when initially creating the table.
SQL Server 70-461 01-01
What does a body consist of?
A body consists of a set of tuples, which SQL attempts to represent with rows.
SQL Server 70-461 01-01
What two branches of mathematics are the foundation for the relational model?
- Set Theory
- Predicate Logic
SQL Server 70-461 01-01
What is a set?
A collection of distinct objects, called the elements of the set.
SQL Server 70-461 01-01
What is a proposition?
It is a declarative sentence that is true or false. Example, Paul lives in Minneapolis.
SQL Server 70-461 01-01
What is a declarative sentence?
A declarative sentence makes a statement and ends with a period.
SQL Server 70-461 01-01
What is a predicate?
Something having the form of a declarative sentence with parameters that are not yet known, so it cannot be decided whether it is true or false. Example, Paul lives in x.
SQL Server 70-461 01-01
How do you make a predicate into a proposition?
Supply values for the unknown parameters. So Paul lives in x becomes Paul lives in Minneapolis. Now you can decide whether the statement is true or not.
SQL Server 70-461 01-01
What are predicates used for in the relational model?
- To enforce data integrity
- To filter data
- To define the data model
SQL Server 70-461 01-01
Does SQL Server allow duplicates in a table?
Yes, it does not perfectly follow relational theory in this way. SQL Server allows you to create a table without a key. In strict terms, this is not relational.
SQL Server 70-461 01-01
How could you follow relational theory with regard to not allowing duplicates when creating a table in SQL Server?
By using a primary key or unique constraint.
SQL Server 70-461 01-01
Does SQL Server allow duplicates in a query?
Yes. For example, you could do SELECT Country FROM HR.Employees and you could get duplicate countries if they exist in the data.
SQL Server 70-461 01-01
How could you follow relational theory with regards to a query returning duplicates?
You could use the DISTINCT operator.
SQL Server 70-461 01-01
By default, what order is data from a query returned in?
The query returns a relational result, which by definition has no prescribed order. The data may appear to be in a certain order due to how the engine decided to execute the query, but the only way you can count on the data being in a specific order every time is to have an ORDER BY clause in the SQL statement.
SQL Server 70-461 01-01
Once you add an ORDER BY clause to a SQL statement, it is no longer relational. What kind of operation is it?
It is a cursor
Note: the use of the term cursor here is conceptual. T–SQL also supports anobject called a cursor that is defined based on a result of a query, and that allows fetchingrows one at a time in a specified order.
SQL Server 70-461 01-01
Should you use ordinal position or attribute name in the ORDER BY clause?
Always use the attribute name. The ordinal position is the order in which the column is listed in the select statement. The query would come back in the wrong order if you added a column to the SELECT list but didn’t update the ordinal position of the attribute in the ORDER BY clause to reflect this.
SQL Server 70-461 01-01
Using an ORDER BY clause is not relational. On top of that, which method of listing attributes in the ORDER BY clause is not relational?
Referencing the attributes by ordinal position is not relational, refer to them by name instead.
SQL Server 70-461 01-01