SQL Database Files

SQL Server database management systems typically make use of three different files. There is one .mdf file, or primary data file for each database. Views, tables and other objects must be stored in the .mdf file. While a database can function with only a .mdf files it is common for secondary data to be stored in a .ndf file. If a database exceeds the maximum for a single Windows file, additional data can be stored in a .ndf file. Relativity stores full text indexes in .ndf files.

There can be multiple .ldf, or log files for each database. Log files record the time of transactions in the SQL database and can be use for recovery when there is a database.

kCura recommends that these files be kept on separate disks.

SQL - Managing Data

Here's a continuation of my review of Getting Started with SQL, an O'Reilly guide by Thomas Nield, last blogged about on January 26, 2017 .

Chapter 10 is entitled Managing Data.

In this chapter we see how to insert data directly into a table. So a command like this one:

INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME) VALUES ('Stefani','Germanotta')

Specifies a table, fields in that table, and then on the next line the values to be added.

Not that in this example the VIP field for which the command did not specify a value, has a zero because that is the default value of the field specified when the table was created.

We can also use a command in the following format to insert data from one table into another, but keep in mind the data must be transferred between fields of the same type, and any fields in the receiving table that are designated 'NOT NULL' must be accounted for.

INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME, PHONE, EMAIL) SELECT FIRST_NAME, LAST_NAME, PHONE, EMAIL FROM SOME_OTHER_TABLE

If you want to delete data from a table along specified parameters, you can use this command:

DELETE FROM STATION_DATA WHERE YEAR IS 2002 AND MONTH IS 12

The SQL command to change the case of a field is as follows:

UPDATE ATTENDEE SET LAST_NAME = UPPER(LAST_NAME)

To remove a table from a database simply use the DROP command this format:

DROP TABLE ROOM

SQL Database Design

Here's a continuation of my review of Getting Started with SQL, an O'Reilly guide by Thomas Nield, last blogged about on December 23, 2016.

Chapter 9 on Database Design begins with some background information. It notes that SQL Injection is a way to use a SELECT statement to query private data in a web site that has inadequate security measures.

The primary focus of this section of the guide is primary keys and foreign keys. Primary keys are unique identifiers in a parent table, but foreign keys are identifiers in a child table that point back to the primary key. So the foreign key has to be a value in the primary key field.

In order to create your own database simply go to the Database menu and select . . . Add a Database. Save the new database with the extension '.db'.

Double-clicking on the new database will show that it is set-up with two sections - Tables and Views. Right click on the tables inside the new database and select 'Create a table'.

To add a new column for the table click on the icon on the toolbar to the right of the X. As you can you have the option to set a field as a primary key in the check boxes below where you enter the name. Click on configure and you'll get the option to make the field auto increment.

You also have the option to set a new column as VARCHAR - which allows you to enter a set maximum length for the column. Check of 'Not Null' forces a value to be entered in this field.

After you have entered the needed columns, when you click on the green check box on the toolbar the table is saved, and a query statement is generated. If you copy this code and paste into the SQL Editor in the Tools menu, SQLiteStudio will automatically generate a copy of the table.

In order to designate a column as a foreign key double-click on it and then select the check box for 'Foreign Key'.

. . . when you select Configure to the right of the 'Foreign Key' a new dialog box will appear and lets you select the table and column that has the primary key that you refer back to.

A column in one table is a foreign key when it references back to the primary key in another table, and can only use values entered in that column in the parent table. After this is done, you need to the click the green check box on the toolbar to commit the changes to the structure of the table.

You can use query statements to create saved views in SQLiteStudio. Just right click on View and select 'Create a view'. Paste in the query code

. . . and you'll be able to bring up a view that displays specific columns in a table.