February 3, 2012 at 2:53 am
Hello,
As a newcomer to SQL I have found that often an article or tutorial aimed at beginners uses jargon that is never explained. I have even read articles for newcomers and intermediates alike on this site that use words that are not explained.
Here's a list off the top of my head that I've come across:
* Cardinality
* Schema
* Dimension
Is there a source that someone could point me to that actually explains in plain English what these terms mean? I say "plain English" because it also appears that when I've wanted to find out what they mean, I am often directed to articles or sites that give a technically-oriented or confusing explanation for the novice.
I think that often the problem with those that write articles designed for the beginner is that because they are so familiar and experienced with SQL that they find it difficult to explain things in terms other than those they are already used to and this can leave the reader confused and none the wiser.
Book online is great as a general reference guide and serves those who have a fundemental understanding of SQL well, but can also be unclear and inadequate for new users. References are great for those who already understand, but not that useful for those that don't.
So, any pointers to anything you think helpful to a dummy like me would be appreciated.
Regards
Steve
February 3, 2012 at 3:28 am
Steve
I think those are very good points, and I agree that for the beginner, SQL Server database administration can be a nightmare. However, for definitions of those terms, you would have to look a good deal further than a simple define:x on Google to understand them.
Take 'dimension' as an example. Relating to SQL Server Analysis Services, dimensions are based on fact tables. Dimension tables can have more than 3 dimensions. Think a 64-dimensional hypercube. Now consider complicating factors such as integration with a normal OLTP relational database, cube slicing, etc. An explanation of a dimension in one paragraph would not give you sufficient context.
I am trying not to come across as obstructive, but I would really recommend that you dig into a number of SQL Server books where these terms will become apparent *in context* - e.g. a decent RDBMS (Relational Database Management System) book for the basic terms - the Microsoft offering for course 70-432, ISBN 978-0-7356-2605-8, isn't too bad.
I have been (previously) an Oracle DBA, an IBM Technical Lead, and now I'm a SQL Server DBA. I have a degree and specialised in the subject of databases - and I've been doing it for about 12 years now. Yet I still constantly refer to BOL and a large number of reference manuals (sitting on my desk as we speak) whenever I get stuck. You really cannot underestimate the value of reading about this subject, constantly, and keeping abreast of new developments.
Sorry if this wasn't the answer you were hoping for.
Derek.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
February 3, 2012 at 3:46 am
Source: http://www.dictionary.com
Cardinality: (of a set) the cardinal number indicating the number of elements in the set.
[http://dictionary.reference.com/browse/cardinality]
Schema: 1. • a diagram, plan, or scheme.
• an underlying organizational pattern or structure; conceptual framework: A schema provides the basis by which someone relates to the events he or she experiences.
[http://dictionary.reference.com/browse/schema]
2. It is also the owner, the parent object, of standard database objects. In this example, the table myTable in the database myDB has the schema dbo: myDBServer.myDB.dbo.myTable.
from BooksOnline:
A schema is a container that holds tables, views, procedures, and so on. It is inside a database, which is inside a server. These entities fit together like nested boxes. The server is the outermost box, and the schema is the innermost box.
[ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/54aaa7cd-ed12-4787-ae2b-4024032a7cb5.htm]
Dimension: It depends on the context. For cubes and datamarts, it has a very specific meaning. I translate dimension roughly as variable, or measurable variable at any rate. The more variables that are at play in a query, for example, the more dimensions there are.
One final note: Wikipedia is probably also good for simple descriptions.
February 3, 2012 at 3:56 am
Hello Derek,
Please do not apologise for your answer. You were kind enough to respond and that's much appreciated.
I do understand what you're saying and realize that perhaps certain books might be the answer here. However, I do feel that although in the example of dimensions a paragraph isn't enough - it's a start. I come across these sorts of terms and they are inserted into tutorials for beginners which I fail to understand. Anyway, you've highlighted one of my issues with explanations - not being able to express an answer without mentioning other unexplained terms.
In your example of an explanation of dimensions, you also included the phrase "fact table". Now, I have heard of these, but yet again, I do not understand what they are.
I think what I'm saying is that there's no "stepping-stones" here. An initial paragraph introducing the concept of dimensions would be what you've said : I.e. a dimension is an Analysis Services concept. Ok, that's a start and one that gets me on the way to grasping what it is.
I also agree with you that BOL is an excellent resource, but one for those who already have good understanding.
It is difficult starting out with SQL Server as there is just so much to understand and I think I will never be able to get enough of it into my head to ever be a DBA. I am not new to I.T. I was a COBOL/UNIX developer for 23 years before I lost my job and decided to try and grab hold of SQL. While much of T-SQL is not a problem for me to grasp. I find the peripheral stuff more difficult and the fact that there is just so much to know.
Anyway, I'm rambling again, but thanks again for your input, it's much appreciated.
Regards
Steve
February 3, 2012 at 4:15 am
Thanks for the descriptions.
The schema one was particularly helpful.
I habve used schemas in my own little test DB's for learning purposes, but I think I need to have someone either confirm or deny - or even elaborate on, how they should be used.
For example.
I have two schemas setup in one of my test databases. One called "Master" and the other called "Archive". I have used them - rightly or wrongly - to help group or categorize by function or type certain tables.
Example:
Master.Boat_Categories
February 3, 2012 at 4:21 am
Thanks for the descriptions.
The schema one was particularly helpful.
I habve used schemas in my own little test DB's for learning purposes, but I think I need to have someone either confirm or deny - or even elaborate on, how they should be used.
For example.
I have two schemas setup in one of my test databases. One called "Master" and the other called "Archive". I have used them - rightly or wrongly - to help group or categorize by function or type certain tables.
Example:
Master.Boat_Categories
Master.COuntry_Codes
And ...
Archive.Results
Archive.Entrants
I have in the above examples used the "Master" schema name to identify what in the old days used to be called "Master" files. i.e. tables containing definitions that are used throughout the database and "Archive" to identify those tables that are considered to contain historic or archival information.
So, have I got this right? Or, have I missed the point entirely?
Any advice or clarification would be most helpful.
February 3, 2012 at 4:25 am
Cardinality (in SQL) is used with regards to a query operator in a query. It it simply the measure of how many rows that query operator processes.
So if an index seek processes 10 rows, that index seek has a cardinality of 10.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2012 at 4:27 am
No, you've got the point of them - but a couple of notes.
I would steer clear of using the word 'master' since it may be confused with the 'master' database, which holds things like schema definitions.
The default schema in SQL Server is 'dbo'.
I find schemas useful only in very large databases and even then I administrate many where they are not used at all. There is a school of thought arguing that they represent an unnecessary layer of hierarchy.
For your clarification:
Instance (installation of SQL Server)
---> Database
--------> Schema
--------------> Object (i.e. Table/Stored Procedure)
-------------------> Parameters (e.g. columns, constraints).
You can see from the above how the schema can be effectively removed from scope without major impact (i.e. disregarded). Many developers do this to simplify matters.
Confusingly, many non-technicians mangle their database jargon too, you'll find that in meetings 'database' is used where 'instance' is meant, 'schema' is met with a blank look, etc.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
February 3, 2012 at 4:37 am
As Derek has already suggested, it's just something you need to read up on.
Many of the terms can have more than one meaning depending on the context, so I could give you a definition and send you off in completely the wrong direction.
Cardinality... If someone mentions cardinality in the context of a table, they may be referring to the uniqueness of the values in a data column e.g. an Email Address should be unique, and therefore has high cardinality, but Gender can only have a few different values so its cardinality is low... on the other hand they may be referring to the cardinality of its relationship to another table (1 to 1, 1 to many etc) e.g. an Order Header will have 1 to many cardinality with the Order Lines table.
Schema... Somebody could be referring to the schema as being the structure of the whole database e.g. all it's tables, keys, relationships, indexes etc... on the other hand, they may be referring to the "container" used to group a lot of objects e.g. there could be an Person table in the "Customer" schema (referenced as customer.person) as well as a Person table in the "Employee" schema (referenced as employee.person). Both the Customer and Employee schemas can exist in the same database.
February 3, 2012 at 4:59 am
Yes I agree its hard for beginners.
But these jargons come with the job. You ill need to learn it anyway.
When you find any strange word, take a time to learn about it.
In many cases it contains basic (= very important) concepts relative to the topic you are studing.
A good start to search for these terms are the wikipedia disambiguation pages.
February 3, 2012 at 5:07 am
GilaMonster (2/3/2012)
Cardinality (in SQL) is used with regards to a query operator in a query. It it simply the measure of how many rows that query operator processes.So if an index seek processes 10 rows, that index seek has a cardinality of 10.
Thanks! 🙂
It's just the sort of plain-speaking, example-prividing answer I like! 🙂
February 3, 2012 at 5:31 am
Ian Scarlett (2/3/2012)
If someone mentions cardinality in the context of a table, they may be referring to the uniqueness of the values in a data column e.g. an Email Address should be unique, and therefore has high cardinality, but Gender can only have a few different values so its cardinality is low...
That's not cardinality.
Density - measure of how unique a column or set of columns is. Low density = very unique, high density = not very unique. It's calculated as 1/(number of unique values in a column). Indexes or columns have density. This is expressed as a number > 0 and <= 1 (1 meaning that the row has only one value)
Selectivity - % of rows in the table that are affected by a specific predicate. If the predicate affects a small portion of the table it has high selectivity. If a predicate affects a large portion of the table it has a low selectivity. Predicates have selectivity (and it is possible for a predicate on a low density column (very unique) to have low selectivity (affects a lot of the table)). Selectivity is expressed as a %
Cardinality - Number of rows that a operator processes. If an operator processes a few rows, it has low cardinality. This is usually an estimate based on statistics, so it appears as 'cardinality estimate'. Query operators have cardinality. This is expressed as a number >= 0.
From http://technet.microsoft.com/en-us/library/cc966419.aspx
We define the following terms related to SQL Server 2005 statistics:
Predicate: A condition that evaluates to true or false. Predicates appear in a WHERE or JOIN clause in a database query.
Selectivity: The fraction of rows from the input set of the predicate that satisfy the predicate. More sophisticated selectivity measures are also used to estimate the number of rows produced by joins, DISTINCT, and other operators. For example, SQL Server 2005 estimates the selectivity of the predicate "Sales.SalesOrderHeader.OrderID = 43659" in the AdventureWorks database as 1/31465 = 0.00003178.
Cardinality estimate: An estimate of the size of a result set. For example, if a table T has 100,000 rows and a query contains a selection predicate of the form T.a=10, and a histogram shows that the selectivity of T.a=10 is 10%, then the cardinality estimate for the fraction of rows of T that must be considered by the query is 10% * 100,000 = 10,000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2012 at 5:43 am
I see what you mean with regards to using "Master".
Am I correct in thinking that using schemas can be useful because you can grant users access at the schema level and thus you could deny users access to the "Master" schema-based tables without having to individually specify tables?
What confuses me as a person trying to learn all this is the apparent contradictions I come across. For example, a Microsoft online course I did (terrible course by the way - riddled with errors!) stated that use of schemas is to be encouraged. The AdventureWorks database sample provided by Microsoft illustrates this. But, experienced users like you good self are less enamoured by them.
However, I tend to listen more to the voices of experience rather than idealistic doctrine when it comes to such contradictions.
Thanks once again Derek. 🙂
Regards
Steve
February 3, 2012 at 5:46 am
GilaMonster (2/3/2012)
Ian Scarlett (2/3/2012)
If someone mentions cardinality in the context of a table, they may be referring to the uniqueness of the values in a data column e.g. an Email Address should be unique, and therefore has high cardinality, but Gender can only have a few different values so its cardinality is low...That's not cardinality.
That has demonstrated my point precisely... there is a Wiki definition that says it is... http://en.wikipedia.org/wiki/Cardinality_(SQL_statements)
That article is defining the cardinality of "columnar sets", but other people will take your definition of cardinality, and other people will take the definition of a relationship, so you can't give a single definition of cardinality without some context.
February 3, 2012 at 6:09 am
At the wikipedia disambiguation page there are TWO entries relative to DB:
Cardinality of a set, a measure of the "number of elements" of a set in mathematics
Cardinality of a musical set, the number of pitch classes
Cardinality (data modeling), a term in database design
Cardinality (SQL statements), a term used in SQL statements
Cardinal utility, in contrast with ordinal utility, in economics
http://en.wikipedia.org/wiki/Cardinality_(disambiguation)
😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply