SQL Server

SQL - Terminology

Database Terminology

Database Server

This is a computer that is holding databases, as well as a program that manages those databases.

For example Database Server – The Maximo DayOff database is held on the DayoffDb database server.

  • Access: does not have the database server concept. But you could stretch the definition to include all computers with access installed.

  • SQL Server, Oracle: has dedicated servers, the database engine is installed as a service on the server. Separate Clients are used access those databases. For example

    • Maximo to access Maximo databases.
    • SQL Server Management Studio used by programmers to access data.
    • Access – Allows you to establish a link to tables in a database server.

Database

This is a file holding data which is normally held in one or more tables, which are divided into rows that represents a unit of data, and into columns representing an attribute of that data.

For example ‘the maximo database’ is holding all the data associated with Maximo. This data is divided into many tables. An example of one of those tables is the Workorder table. Each row in that table could represent a different work-order. Each column represents different piece of data associated with that work-order.

  • Access: the .mdb or .accdb file is a database. It also holds other object types like Queries, forms, and report definitions.
  • SQL Server: has an .mdf file *** but generally this file is placed in a protected directory and is not accessible to people for casual access.

Table

This is a set of common data.

For example the workorder table, or the service request table. All of the records in these tables are generally associated with that table itself.
Generally a table consists of definitions of columns, indexes, triggers, relationships and data.

Table Name

This is … the name of the table containing data.

For example the workorder table – the table name is workorder.

  • Access: when you link to a sql server – the tablename will generally be schema_tablename (for example dbo_workorder). This is the default when you link or export in the table however it can be renamed.

  • SQL Server: generally, the name of a table will be the name of the table. However that is the name in one context. Its full table name would consist of the following components.

    servername.databasename.schemaname.tablename

For example dayoffdb.maximo.dbo.workorder.

You start using other parts of its name when you are connected to a different database, or you are operating with databases with different schema names.

  • Oracle: Generally, the name of the table will be the name of the table. However sometimes you will need to access data in other schemas. Then you specify table names like schemaname.tablename.

Column, Field

This is an attribute within a table holding a certain kind of information.

People use Column, others use Field – I believe in SQL Server and Oracle the correct term is Column.
For example in the workorder table, you will have columns like ‘Work order number’, ‘start date’, ‘status’

Data Type

Associated with each column is a data type that helps specify the kind of data that can be placed into a field. You can specify values like Character, Number, Bit, Blob, Date etc. When creating a column in a table, a data type is required.

  • Access

1 Attachment, AutoNumber, Currency, Date/Time, Hyperlink, Memo, Number, OLE Object, Text, Yes/No
2 http://msdn.microsoft.com/en-us/library/ms187752.aspx

  • Sql Server

1 BigInt, Binary, Bit, Char, Cursor, Date, DateTimeOffset, DateTime2, Decimal, Float, Hierarchyid, Image, Int, Money, nChar, nVarchar, nText, Numeric, Real, SmallDateTime, SmallInt, SmallMoney, sql_variant, table, Timestamp,TinyInt, DateTime, Time, uniqueidentifier, Varchar, VarBinay, xml, spatial Types.
2 http://msdn.microsoft.com/en-us/library/ms187752.aspx

  • Oracle

1 Binary_Float, Binary_Double, Blob, bFile, Char, Character, Clob, Date, Dec, Decimal, Double, Float, Int, Integer, interval, Long, nChar, nClob, nVarchar2, Number, Numeric, ORDAudio, ORDImage, ORDVideo, ORDDoc, ORDDicom, Raw, Real, RowID, SDO_Geometry, SDO_Topo_Geometry, SDDO_GeoRaster, SI_StillImage, SI_AverageColor, SI_PositionalColor, SI_ColorHistogram, SI_Texture, SI_FeatureList, SI_Color, SmallInt, Timestamp, URowId, URIType, Varchar, Varchar2, XML.

2 http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm

Some data types require additional information to fully specify the data type. For example a variable character data type usually has associated with it a maximum field size. Ex varchar(50). A numeric data type usually has associated with it a size, and precision. Ex numeric(8,2)

Key

This is a column (sometimes it could be multiple columns) which is used to identify the entire record. A couple of qualities of the key is that it makes sense to be a unique value, and it must be included with each record.

For example in the work order table the Work Order Number field could be the key. It should make sense that in the workorder table when you give a work-order number it be referring to one work-order. In addition it should make sense that if there is a work-order – it will have a number associated with it.

Most tables have 1 column to represent a key (a unique value – ex ssn – which is enforced and supposedly guaranteed to be unique and with a value). But some tables have 2 or more columns which are combined to make a key (Maximo – site id/job plan)

Some tables do not have a key. This is kind of rare as it causes performance problems, but you could find some ‘log’ tables without keys.

Internal Key

This is a subcategory of the key. When this term is used, it generally describes the existence of a key that is hidden in the database and not generally seen by users of the database.

External Key

this is a subcategory of the key. When this term is used, it generally descriptions the existence of a key that is used by users to identity the record.

For example if you look at the Asset Table in Maximo there are 2 keys; AssetNum is an external key, AssetUid is an internal key

Foreign Key

This is a column in a table which refers to the primary key in another table.

A common example would be a workorder table and a labor transaction table. The workorder table could have a primary key of WONO, the labor transaction table may have a foreign key of WONO which is a pointer to a workorder being referenced.

1 to 1, 1 to Many, Many to Many

(aka 1:1, 1:many, many:many )

These are terms that help some people understand how one table is connected or associated with another table. They do not necessarily imply a programmed relationship – but they represent good places to place them.

  • 1 to Many Example: consider a work-order table, and a hypothetical priority table. A record in the workorder table will point to 1 record in the priority table. A record in the priority table could point to many records in the workorder table. This is an example of a 1 to many relationship.

  • 1 to 1 Example: consider the service request and the overflow record in the 2013 version of Maximo.

Generally for the service request – there may be 1 associated overflow record (never more), for a record in the overflow table there will only be one associated service request.

This is a fair, but not great, example of a 1 to 1 relationship.

  • Many to Many: generally speaking, it is hard to find real examples of Many to Many relationships, but suppose a workorder allowed you to define multiple assets. If this were the case, then a record in the workorder table could point to multiple asset records, and a record in the asset table could point to multiple workorder records.

Note: The SQL Language does not really support a Many:Many association. But there are common workarounds (ex an intermediate table)

Relationship

A relationship is a programmed tie between 2 tables.

For example in a workorder table there is a relationship between the workorder table and the asset table. Supposedly getting the associated asset of a workorder is a fast operation.

Some database engines can take advantage of relationships to run better.

For example in access the query tool seems to know which field to join when you attempt to connect up 2 tables.

DBA’s will look for these different table associations (1:1, 1:many, many:many) because they can modify the database structure to perform better when database selections are submitted to the database engine.

View

This is a SQL Statement that has been saved into the database for later re-use. It has a couple of advantages

  • Some databases can run these in an optimized manner.
  • Their use, sometimes, simplifies things. Especially when you start reusing it in different places.

Access: Calls these Queries.

Index

An Index is a small table consisting of a column and a pointer. The rows of the table usually match another table.

The advantage of an index is a program can search an index faster than it can search a main table.

Most SQL servers use these indexes to speed up conditions placed on the sql statements. They also use these indexes to speed up table joins.

Trigger

This is a program that is setup and is executed when a certain action is performed on a table.

For example you can write a trigger to run a program when a record is added to the workorder table. You could also write another trigger to run a program when a record is removed from the workorder table.
Access: Does not support triggers.

Replication

Database Failure is a big problem. If the database server crashes – people usually want it fixed right away and they do not want to lose any data placed in that database. DBA’s place a lot of effort into minimizing database downtime. One strategy is to make a copy of the database. Once a copy or an active copy is in place then additional strategies are developed to take advantage of the copy. Replication can be setup numerous ways – each has their own pro’s and con’s.

Code Table

This is a certain type of table which is usually holding codes and description of codes. For example a list of priority codes may have a priority code and a description field.

Maximo calls these domains, and usually bundles all domains into a few tables.

Granite bundles all the code lists into a single table.