0
Creating Databases, Tables and Fields using SQL code
For the sake of these tutorials, I will be using SQL Server Management Studio on a SQL Server 2012 Server.
The first lesson will be to create Databases, Tables and Fields using SQL code.
On clicking "Run", the database will be created and displayed within the Object Explorer panel (commonly found on the left hand side of SQL Server Management Studio)
Next , create a table called Customer:
In order to identify between the records, a primary key column will be required. This table will typically store customer details, therefore customer FirstName, LastName, Address, Postcode, ContactNo and EmailAddress would be good places to start when thinking of potential field names. It is also a good idea to think about what data types these fields would take. For a unique key, either an integer (int) or a globally unique identifier (guid) would be ideal. For this tutorial - I suggest using an integer despite it being possible to use both. There will be a later blog post to explain the differences between them so you can decide which best suits your situation.
Now you will need to consider how much space will need to be allocated to each field. For example, a FirstName shouldn't really contain more than 35 characters. Therefore, 35 will be placed in brackets next to the data type in order to signify this. Note that some data types may not allow you to specify an amount of space (such as integer values) as they already have a set amount of space to use. It will also allow you to set the size as "MAX" in the possibility that -- In the case of primary keys, they will need to be set as Identity fields so that they automatically generate a an ID number. You will notice in the code sample below that it is made up similarly to a co-ordinate (1,1). The first number declares the seed (the number that the Primary Key will start at) and the second number declares the increment (the amount in between each primary key as it's inserted into the table). By default, fields are set to allow NULLs if no data has been entered by the user. You would not want this for all of your fields so be careful and think about what fields you want as null able. To declare a field as NOT NULL, all you have to do is place the NOT NULL at the end of the declaration. Note that Primary Keys must always be NOT NULL.
The code below creates the basic field types and declares the CustomerId as a Primary Key:
And that code will create your first database table! Now, create an Item table in exactly the same way (see code below):
We will need to consider relationships between these two tables. You can have different relationships between tables as depicted in the table below:
So looking at our scenario, can a customer buy many items? Yes... Could many customers buy the same item?... Yes! Therefore, this will require a many to many relationship.
In order to create many to many relationship tables in SQL Server Management Studio, a bridge table will need to be made in order to link the 2 by their ID's. This will normally consist of its own unique Primary Key, along with the 2 Primary Key fields from the 2 tables you have just created. These will be declared as Foreign Keys as they do already exist, we are just pulling the values from the other tables. Don't forget to reference the foreign keys so you know the exact table and exact field they are coming from. See the example code below:
Once created, it should look like this:
The first lesson will be to create Databases, Tables and Fields using SQL code.
Create a Database
It is possible to create Databases using the user interface and then adding a database name. However, you can quite easily select "New Query" from the toolbar and type in the following:On clicking "Run", the database will be created and displayed within the Object Explorer panel (commonly found on the left hand side of SQL Server Management Studio)
Creating Tables and Fields
Next, we will want to create some tables to go inside our newly created database. Remember before we do this, always ensure that you put "USE databaseName" above the CREATE statements so that SQL Server Management Studio knows where to put the new tables (see code below):
Next , create a table called Customer:
In order to identify between the records, a primary key column will be required. This table will typically store customer details, therefore customer FirstName, LastName, Address, Postcode, ContactNo and EmailAddress would be good places to start when thinking of potential field names. It is also a good idea to think about what data types these fields would take. For a unique key, either an integer (int) or a globally unique identifier (guid) would be ideal. For this tutorial - I suggest using an integer despite it being possible to use both. There will be a later blog post to explain the differences between them so you can decide which best suits your situation.
Now you will need to consider how much space will need to be allocated to each field. For example, a FirstName shouldn't really contain more than 35 characters. Therefore, 35 will be placed in brackets next to the data type in order to signify this. Note that some data types may not allow you to specify an amount of space (such as integer values) as they already have a set amount of space to use. It will also allow you to set the size as "MAX" in the possibility that -- In the case of primary keys, they will need to be set as Identity fields so that they automatically generate a an ID number. You will notice in the code sample below that it is made up similarly to a co-ordinate (1,1). The first number declares the seed (the number that the Primary Key will start at) and the second number declares the increment (the amount in between each primary key as it's inserted into the table). By default, fields are set to allow NULLs if no data has been entered by the user. You would not want this for all of your fields so be careful and think about what fields you want as null able. To declare a field as NOT NULL, all you have to do is place the NOT NULL at the end of the declaration. Note that Primary Keys must always be NOT NULL.
The code below creates the basic field types and declares the CustomerId as a Primary Key:
Creating Relationships
We will need to consider relationships between these two tables. You can have different relationships between tables as depicted in the table below:
So looking at our scenario, can a customer buy many items? Yes... Could many customers buy the same item?... Yes! Therefore, this will require a many to many relationship.
In order to create many to many relationship tables in SQL Server Management Studio, a bridge table will need to be made in order to link the 2 by their ID's. This will normally consist of its own unique Primary Key, along with the 2 Primary Key fields from the 2 tables you have just created. These will be declared as Foreign Keys as they do already exist, we are just pulling the values from the other tables. Don't forget to reference the foreign keys so you know the exact table and exact field they are coming from. See the example code below:
Let's take a look at what we've done!
In order to see what has been created, click onto "Diagrams" and create a database diagram adding the 3 newly created tables into it. Note at this point that you may need to refresh the popup in order for it to display your tables!Once created, it should look like this:
Post a Comment
Please post any feedback or comments here...