Large Joins in Large Databases -- How to find Keys?

[WWGD]

Hi,
I am playing around with the large(ish) database Adventureworks2014. A couple of issues , please:

1) How do we go about finding the keys when joining , say, 5 tables at a time? Does one do a database diagram using those tables?

2) I am having trying to set up the diagrams for these joins. Problem is I am having trouble finding the tables I need when designing the diagrams; tables are not listed in the dialog box used to choose for the diagram to make ( please see dialog box in center) . How do I load missing tables into the list, so I can use them for diagrams? I guess this is more of an OLAP issue than an OLTP Thanks.:

 

[Buzz Bloom]

database Adventureworks2014

How do we go about finding the keys when joining , say, 5 tables at a time? Does one do a database diagram using those tables?

Hi WWGD:

My database experience is a bit rusty since I am retired. I would like to help you, but I do not fully understand the nature of your problem.

1. I do not know this data base. Can you link to a diagram and/or a description of its tables?
2. Can you write an example of a 5 join query to illustrate what your problem is like?
3. I always found it very helpful to have a diagram of all of, or at least that part of a database I was to prepare queries about. So, if you provide an example of a query, it would be helpful to see a diagram showing the 1-to-many relationships among the relevant tables, as well as the keys that support these relationships.

Regards,
Buzz

 

[newjerseyrunner]

Yeah, I'm confused my what's being asked. Are you asking how to know which keys to compare in what order when doing large JOINs, or are you asking for help with a particular tool? Do you have access to any kind of terminal? Most database people aren't terribly familiar with GUIs. Showing you tables should be as easy as typing "SHOW TABLES;" then showing your keys is "DESC <table>;"

5 tables isn't a terribly bad join. It'll definitely require some temp table space though, so optimizing it would be beneficial. Then again, I don't know if your database has 50 million rows or 50 billion.

 

[anorlunda]

How do we go about finding the keys when joining

Are you asking how to find the database shema? Do you know what schema means in this context?

 

[WWGD]

Are you asking how to find the database shema? Do you know what schema means in this context?

Yes, I believe. A Schema is a container for data objects related to a given role , right?. But , don't we sometimes join tables within different schemas? I am just curious as to how one would go about figuring out the keys to use. And, just the technical point of how to import all needed tables into the to be able to create a full diagram.

 

[WWGD]

Hi WWGD:

My database experience is a bit rusty since I am retired. I would like to help you, but I do not fully understand the nature of your problem.

1. I do not know this data base. Can you link to a diagram and/or a description of its tables?
2. Can you write an example of a 5 join query to illustrate what your problem is like?
3. I always found it very helpful to have a diagram of all of, or at least that part of a database I was to prepare queries about. So, if you provide an example of a query, it would be helpful to see a diagram showing the 1-to-many relationships among the relevant tables, as well as the keys that support these relationships.

Regards,
Buzz

Hi Buzz, thanks for your reply. The thing is we are going in circles here. The dialog box in my post contains some of the tables in the database, but not all. What I am trying to do is to import the remaining tables into this list so I can create a full diagram of the database. Once I have the diagram I can find the keys, but the dialog box is not including all the tables. How do I import the ones that are missing?

 

[WWGD]

Yeah, I'm confused my what's being asked. Are you asking how to know which keys to compare in what order when doing large JOINs, or are you asking for help with a particular tool? Do you have access to any kind of terminal? Most database people aren't terribly familiar with GUIs. Showing you tables should be as easy as typing "SHOW TABLES;" then showing your keys is "DESC <table>;"

5 tables isn't a terribly bad join. It'll definitely require some temp table space though, so optimizing it would be beneficial. Then again, I don't know if your database has 50 million rows or 50 billion.

OK, sorry for the confusion. Let me slow myself down to make myself more clear
1) I am trying to create a full diagram of my database ( AdventureWorks2014, BTW ), meaning a diagram that contains all the tables in the database.
2) I then use the ' New Database Diagram' feature to create said diagram
3) In the process of working on 2), a dialog box containing _some of the tables_ is displayed. I can add any of the tables listed in the dialog box to create a diagram
BUT
4) The list of tables in the dialog box is not exhaustive: it does not contain all the tables belonging to AdventureWorks2014. Some of the tables in AdventureWorks2014 are missing from the list.
5) I would like to have _all_ the tables in AdventureWorks2014 listed in the dialog box, so I can add them to the diagram. How can I import the missing tables into the list in the dialog box displayed? This last is what I want to do.
Hope I made myself more understandable.

 

[anorlunda]

Yes, I believe. A Schema is a container for data objects related to a given role , right?. But , don't we sometimes join tables within different schemas? I am just curious as to how one would go about figuring out the keys to use. And, just the technical point of how to import all needed tables into the to be able to create a full diagram.

No, see below:

The term "schema" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases).

It seems that your question is how to find out the schema for your database; correct?

I found this https://www.sqldatadictionary.com/AdventureWorks2014.pdf

 

[BvU]

A nice 149 pages fat dissection...

I remember an impressive poster from the thing. (link from http://www.wilsonmar.com/sql_adventureworks.htm )

 

[WWGD]

No, see below:It seems that your question is how to find out the schema for your database; correct?

I found this https://www.sqldatadictionary.com/AdventureWorks2014.pdf

Thanks. I am just curious, I am pretty sure there was some concept named for what I was referring to: a collection of data objects assigned to a database role. e.g., there would be a container for data objects related to HR . This container would include ,would contain, among other things, all tables HumanResources. xxx.xxx. Can anyone think of the name for this, or if maybe I am remembering incorrectly?

 

[anorlunda]

I think you have your answers @WWGD. I gave you the link to the data dictionary text in #8 and @BvU gave a link to the pictorial form in #9.

If these answers are not enough, please try to be more specific and more clear about what you are asking.

 

[Buzz Bloom]

How do I import the ones that are missing?

Hi WWGD:

Sorry, I cannot help you. I don't know your database, and the techniques for accessing database diagrams and definitions vary from one to another. Good luck.

Regards,
Buzz

 

[Buzz Bloom]

Hi WWGD:

I looked at the poster linked in BvU's post #9 and the schema text linked in aorlunda's post #8. Have you looked at these? If so, what are the specific problems you have in understanding the information there?

Regards,
Buzz

 

[WWGD]

Hi, thanks for the links. My issue was about importing missing tables into the general schema so I can create a full database diagram . It is just a general technical issue and not just about issues specific to adventureworks database. With a full diagram I can determine , for a general database, the keys I can use to make required joins.

 

[anorlunda]

Hi, thanks for the links. My issue was about importing missing tables into the general schema so I can create a full database diagram . It is just a general technical issue and not just about issues specific to adventureworks database. With a full diagram I can determine , for a general database, the keys I can use to make required joins.

As @Buzz Bloom said, the specific procedures vary with each DBMS and with each user interface. For example:

Oracle show Schema
How to list all available schemas

• DB2. select schemaname from syscat.schemata.
• MySQL. You can leave the schema blank since for MySQL, physically a schema is synonymous with a database.
• Oracle. select USERNAME from SYS.ALL_USERS.
• PostgreSQL. select nspname from pg_catalog.pg_namespace.
• Presto. SHOW SCHEMAS [ FROM catalog ]
• Snowflake. ...
• SQL Server. ...

You still have deep misconceptions about database schemas. Please read the whole Wikipedia article https://en.wikipedia.org/wiki/Database_schema

 

[WWGD]

My apologies for not reading that post carefully enough. Still I think I do understand what a schema is and there is a related concept , at least within Sql server of a schema as a container for data objects assigned to a sspecific role, e.g, there is an hr schema containing tables and other objects related to the hr role. The use is that access to these tables is restricted to those assigned to the hr role. Roles are assigned in this way.

 

[anorlunda]

My apologies for not reading that post carefully enough. Still I think I do understand what a schema is and there is a related concept , at least within Sql server of a schema as a container for data objects assigned to a sspecific role, e.g, there is an hr schema containing tables and other objects related to the hr role. The use is that access to these tables is restricted to those assigned to the hr role. Roles are assigned in this way.

So, is your question specific to SQL Server? It sounds like you want to circumvent the security in SQL server.

You are still being very unclear, so PF members don't know how to answer your questions.

I'm going to lock this thread. Please PM me with better descriptions of your goal if you want it reopened.

Thread locked.