MTA Microsoft Technical Associate (Exam 98-361) Understanding Databases
Index
- Introducing Relational Database Management Systems (RDMS)
- Understanding Query Methods
- Database Connections
1. Introducting RDMS
Database is a collection of related information, for instance, an mp3 player is a kind of database where you not only save just your favourint music tracks, you also save related information like the name of every song, the lenght, the artins. All this information is stored and is related to the same subject.
RDMS is a relational storage concept for data, where you can allocate the information in different tables and you can link those tables depending of how do you want to access the information. For instance if you want to create a new database to save all the music you have at home, first you can create a table to save all the songs, other table to save the info related with the artist, other table could store the different music styles you have...
Uses normalization, which tries to avoid fields repetition. For our music example, imagine you want to save the city of all the bands you have in your music collection. First you can probably think, ok! let's save the City in my Bands table directly. At first it has sense, but the problem comes when that table goes bigger and the amount of Cities is too big. Why we don't create another table just to save the cities with a number assigned to every city (Primary key) and link this table with our bands table. Using this process, you are not saving thousands of times the same city name. This way, we will just have a number in the band table (Foreign key) which means the city assigned to that band. You save it just one time in your cities table and link it with a number, which is less heavy than the text and very easy to link for an RDMS.
See in the following example how different tables are linked in a company database. You can see here below, table "Orders" used to save the different sales orders with a numeric primary key called "OrderID", this is linked with a third table called "LineItems" responsible of link orders and products saving the "OrderID's" and the "ProductID" numbers. This way save a lot of space and process time working with numbers instead of the Product text name.
RDMS consists of services and applications for managing data and it allows querying, updating, inserting and deleting of data. All these tools give you the ability to play with all the information you stored in your database in a very efficient and performed way.
You can find different providers for your database like: SQL Server, Oracle or MySql. All of them use their own language (TransactSQL, PL/SQL and ) to access the information throw different commands
RDMS is a relational storage concept for data, where you can allocate the information in different tables and you can link those tables depending of how do you want to access the information. For instance if you want to create a new database to save all the music you have at home, first you can create a table to save all the songs, other table to save the info related with the artist, other table could store the different music styles you have...
Uses normalization, which tries to avoid fields repetition. For our music example, imagine you want to save the city of all the bands you have in your music collection. First you can probably think, ok! let's save the City in my Bands table directly. At first it has sense, but the problem comes when that table goes bigger and the amount of Cities is too big. Why we don't create another table just to save the cities with a number assigned to every city (Primary key) and link this table with our bands table. Using this process, you are not saving thousands of times the same city name. This way, we will just have a number in the band table (Foreign key) which means the city assigned to that band. You save it just one time in your cities table and link it with a number, which is less heavy than the text and very easy to link for an RDMS.
See in the following example how different tables are linked in a company database. You can see here below, table "Orders" used to save the different sales orders with a numeric primary key called "OrderID", this is linked with a third table called "LineItems" responsible of link orders and products saving the "OrderID's" and the "ProductID" numbers. This way save a lot of space and process time working with numbers instead of the Product text name.
RDMS consists of services and applications for managing data and it allows querying, updating, inserting and deleting of data. All these tools give you the ability to play with all the information you stored in your database in a very efficient and performed way.
You can find different providers for your database like: SQL Server, Oracle or MySql. All of them use their own language (TransactSQL, PL/SQL and ) to access the information throw different commands
2. Understanding Query Methods
Queries, allow developers to get information from the database, insert, edit and delete either from a web application or a desktop application. With a few examples we will go throw all of these different queries, using the Microsoft AdventureWorks2012 (you can get it from here) database tables:
You will need to be very careful when you launch update queries to your database, because sometimes you think you are updating just the rows that you want to update but maybe there are more rows which fit with your query conditions and can apply those changes to them too. To avoid these kind of errors the best way to update fields is selecting them by using their primary key like the Id because these keys use to be unique in the tables instead of using text fields.
As you can see, one of the fields is populated with a special command called GETDATE(), this command gets the current date and time when the update statement is triggered.
I hope these examples help you to get a better understanding of how powerful is this language and all the things you are capable to do. For a better understanding you can get more info from W3.
- Selecting data: you will need to use the "Select" command from SQL, in our first example we will select all the columns throw the reserved word: *, after the SELECT word. Also we need to specify from which table we want to get the information with the FROM reserved word, in our case we are using the "Person.Address" table. Follow the following patter to get information: SELECT <column name/s> FROM <table name> WHERE (optional) <conditions>
USE AdventureWorks2012; -- Get information from the address table for people SELECT * FROM Person.Address; -- Get the state or province associated with the ID 79 SELECT * FROM Person.Address WHERE AddressID = 79;
- Updating data: to update information in your tables you need to use the UPDATE statement first, followed by the table name, then you will need to specify the values that you want to save and optionally the rows where you want to apply those values. Follow the following patter: UPDATE <table name> SET <values to save> WHERE (optional) <conditions>. In the following example we are setting the middle name 'Angela' to someone called 'Gail Erickson'.
-- Update the middle name to an initial UPDATE Person.Person SET MiddleName = 'Angela' WHERE FirstName = 'Gail' AND LastName = 'Erickson'
You will need to be very careful when you launch update queries to your database, because sometimes you think you are updating just the rows that you want to update but maybe there are more rows which fit with your query conditions and can apply those changes to them too. To avoid these kind of errors the best way to update fields is selecting them by using their primary key like the Id because these keys use to be unique in the tables instead of using text fields.
- Inserting data: for this example we need to use the INSERT statement with the following query pattern: INSERT INTO <table name> VALUE <different values>. First take a look at that table to see how is built and which data contains. The idea is to add a new language for the people from Canada in the cultures table.
-- Return all information from the culture table
SELECT *
FROM Production.Culture;
-- Insert a new value into the culture table
INSERT INTO Production.Culture
VALUES ('ca-fr', 'Canadian French', GETDATE());
As you can see, one of the fields is populated with a special command called GETDATE(), this command gets the current date and time when the update statement is triggered.
- Deleting data: you will need to be very careful with this command, rememberer you are applying changes directly in the database and there is no roll back. The following pattern explains how this command works: DELETE FROM <table name> WHERE (optional) <conditions>. See the WHERE optional parameter, if we don't specify this parameter in our delete query, we are actually saying to SQL Server that we want to delete all the rows within the table we have specified. In the following example we will delete our previous insert.
-- Delete an item from the culture table DELETE FROM Production.Culture WHERE CultureID = 'ca-fr';
3. Database Connections
In our future applications we'll need to specify how to connect to our database server, here is where we need to provide a database connection, which provides the different user credentials and server information that we need to be able to link our app with our RDMS. The connections can be pooled, which means, open a connection to a database and allow a different set of sockets that can be used by different clients to access the database. This is used because open a database connection takes a lot of time, so we just want to open it once by our connection handler and the user can ask him for a connection when it is available. See the image bellow to get a better understanding:
This way we have just one place to handle the connection with the database and we allow access to those we want.
Another important point is to open and close the connection. If a database connection is not closed could produce security issues in your applications specially if your applications is web.
Here below I include an example of a connection string with different parameters that are required to make our connection successful:
m_sConnStr = "Provider=SQLOLEDB;Data Source=MySqlServer;Initial Catalog=Northwind;Integrated Security=True".
Provider is the component responsible of the communication and information handling. DataSource is the name of the server where we want to connect. Initial catalog is the name of the database within that database server. Integrated security equals true means, if I am connected to windows with my domain user and password, use those credentials to login into the database server. This is just an example but you can find thousands of different connections strings to use in your applications.
That's all for now and I hope to see you in the next module!
This way we have just one place to handle the connection with the database and we allow access to those we want.
Another important point is to open and close the connection. If a database connection is not closed could produce security issues in your applications specially if your applications is web.
Here below I include an example of a connection string with different parameters that are required to make our connection successful:
m_sConnStr = "Provider=SQLOLEDB;Data Source=MySqlServer;Initial Catalog=Northwind;Integrated Security=True".
Provider is the component responsible of the communication and information handling. DataSource is the name of the server where we want to connect. Initial catalog is the name of the database within that database server. Integrated security equals true means, if I am connected to windows with my domain user and password, use those credentials to login into the database server. This is just an example but you can find thousands of different connections strings to use in your applications.
That's all for now and I hope to see you in the next module!
