Subtitles of the Movie

The schema is a very important documentation process of our database. It allows us to get a visualization of all the tables and the overlap between different departments and different components. In front of you is AdventureWorks schema. Now, AdventureWorks is a database that's provided by Microsoft. It is a free download and I have included the download on this actual video. When we work in AdventureWorks, you are able to see all the different components that we can do with the database itself. The schema is part of the AdventureWorks database. The idea here is that I can very easily see the flow of all the components of the database. Notice that its color coded into the individual departments so that I can see sales versus purchasing versus production. When I want to, I can now zoom in to individual areas and as I'm zooming in I can start seeing more and more details on the individual tables itself. Now, the schema is produced after I've finished the conceptual, the logical and the physical modeling stages. In other words, I have a good idea of exactly how my tables are going to be designed within the database. Notice that first I can see the overlap between the actual departments. I can see now clearly what securities and type of access that I need to give people for the tables themselves. If I do not have the correct access, I may lock people out of places that they really need to be actually working. This gives me the feel of seeing the connection of the tables within the departments. Notice also that I can clearly see the primary keys, the foreign keys and even unique fields within the database itself. The idea is that I can clearly see the layout of all the key variables. Now, keep in mind this is when it's really important to use the proper nomenclature. Let's look at customer within sales. The primary key is the customer ID. Now, because I've used the universal naming convention, when I go down to the table called Individual and I see the customer ID, I know right away that that information is coming from the customer table. Same thing with territory ID. I know that there is a territory table because of the actual naming itself. This is an important aspect of the database itself. It allows me to quickly see do all the tables have primary keys in them? Are they utilizing foreign keys? Is there a unique record that I could use for another kind of component within the database itself? This documentation of course is very tedious and time consuming. However, it's an absolutely essential component of our database and allows us to see what we can and cannot change later on in the database. It's very easy for me to now know that if something is listed as a foreign key, I must be careful about naming and changing. Same thing with the primary keys. I must be careful about this information because there are connections via the relationships. Now, when we're working with the schema, we want to get more and more in detail as the information itself is concerned. For instance, we have an overview of the tables at this point but what about more descriptions of the fields? This is where our backup documentation comes into play. Let's go ahead and switch over to an example of some of the actual documentation and I'll go into full screen on the slideshow. Notice first that the customer table is part of the documentation. Here's where we're actually defining what are the column names, what type of data is including, do they allow nulls and what is a full description of that actual table? If I'm looking to put a new report together later on, this makes it very easy for me to determine where my data needs to come from. We'll also take some time to actually define the user functions. If functions have been created specifically for this database, it's nice to have a list of them. Again, if I'm creating a new query, a new report it's nice to know that these functions already exist. And then there are stored procedures. If we're writing coded procedures such as stored procedures, once again we want to list the name, the description of what it does and what is expected from us in input parameters. This documentation can help someone that's new to the company quickly catch on to your database and it's a historical record of exactly what's going on within the database itself.