SYS-ED Database Training Services - Classroom and Web-based SYS-ED Experience

Submit Database Questions


Submit Database Questions

Technology Driven IT Training

Knowledge Base

Submit Database Questions to SYS-ED Advanced search

Interrelated Information Technology
Database Questions - General and Open Source Microsoft Databases - Questions
Proprietary Database (Other) - Questions PostgreSQL - Questions

SAP: Crystal Reports Questions IBM DB2 Questions
IBM IMS Transaction Server Questions Oracle Database Questions

The SYS-ED knowledge base is a service for answering questions, inclusive of the research and validation of the accuracy of information in the public domain. Citation of source documentation and examples are used to provide answers to the questions. Utilization and reliance on the answers, information, or other materials received through this website is done at your own risk.

Database Questions - General and Open Source


I am a new database administrator and the database that I manage appears to have a problem with random bottlenecks. In order to address this issue, I need to become aware of the underlying causes of the locking and blocking.

A The typical underlying causes of the bottleneck are:
  • Insufficient resources, which require additional or upgraded components.
  • Resources of the same type among which workloads are not distributed evenly. e.g. hard disks
  • Incorrectly configured resources.
  • A load may be preventing direct and efficient access to a component thereby increasing the time required for completing the load.
  • Network congestion may be degrading the speed of client requests.


What does the term ACID refer to?


The acronym ACID stands for atomicity, consistency, isolation, and durability.



Atomicity Either all the tasks in a transaction must be performed or none of them. The transaction must be completed, or else it must be rolled back.
Consistency Every transaction must preserve the integrity constraints of the database. It cannot place the data in a contradictory state.
Isolation Two simultaneous transactions cannot interfere with one another. Intermediate results within a transaction are not visible to other transactions.
Durability Completed transactions cannot be aborted later or their results discarded. They must persist through restarts of the DBMS.


What are the ANSI SQL extensions?


This table includes the most widely extensions.

Extension - Acronym Full Name Explanation
SQL/CLI Call-Level Interface

Defined in ISO/IEC 9075-3:2003.

Defines common interfacing components that can be used to execute SQL statements from applications written in other programming languages.

SQL/MED Management of External Data

Defined by ISO/IEC 9075-9:2003.

Provides extensions to SQL that define foreign-data wrappers and datalink types which allow SQL to manage external data.

SQL/OLB Object Language Bindings

Defined by ISO/IEC 9075-10:2003.

Defines the syntax and semantics of SQLJ, which is SQL embedded in Java.


SQL Routines and Types for the Java Programming Language

Defined by ISO/IEC 9075-13:2003.

Specifies the ability to invoke static Java methods as routines from within SQL applications.


XML-Related Specifications

Defined by ISO/IEC 9075-14:2003.

Specifies SQL-based extensions for using XML in conjunction with SQL.


Persistent Stored Modules

Defined by ISO/IEC 9075-4:2003.

Standardizes procedural extensions for SQL.

Formalizes declaration and maintenance of persistent database language routines.


Is it necessary to develop a conceptual data model? My project team is already developing logical data models.


A conceptual data model is a business model; from a data perspective it is not a solution model and is application and technology neutral. A conceptual data model is typically developed in the form of an entity relationship diagram. Its purpose and value is to describe and capture business knowledge from a data perspective, rather than a process. As a business model, it should be reviewed by the business for confirmation or correction.


Unless developing an enterprise logical data model, the logical data model is probably being developed as an implicit solution to the application that is being designed: data warehouse, data mart, ODS: Operational Data Store, transaction processing or other type of application.


Why is it necessary to build a logical data model?



Good database design starts with a complete picture of the business requirements and should be used for determining an optimal approach for implementation. The most important reason for building a logical data model is to confirm that the users and analysts understand the business requirements and assure that the system to be developed accurately fits the business model. Data elements need to be well organized and structured properly and tables and files will need to be based on screen and report layouts.

Failure to build a logical data model, will in all likelihood result in having the design of a new system concentrate on processes and activities with omissions in the data requirements. Designing a model based on physical workflow could result in a model that does not accurately represent the business requirements. The technology of the workflow must not be forced into the physical model. This would result in a database that is missing critical data and which would need to be changed after implementation.


A logical data model is a foundation for designing a database that supports the business requirements. Logical data modeling provides the analyst with a tool and technique to conduct analysis.

Microsoft Databases - Questions


I ran across a road block in working with FoxPro. Iím trying to insert pictures into a database so that I can point to them from a report. How did you upload those images that you had shown us from the other database?

Right now Iíve made a separate database with a column designated to photographs. Iíve made this column type a BLOB. When I go into the mode to edit the database (the rest of the information I had uploaded) the photo column says that itís read-only. Iíve tried to copy and paste into the box as well as upload an image from the toolbar. Nothing so far has worked.

Could you let me know what I can do to resolve this?


It will be easier for you to create pictures using the General column type. In order to use the BLOB, it would require some programming.

Create a database column with the General datatype and startup the append mode. Double click on the picture (General) field; a window will appear. Select Edit, Insert Object from the menu. Click on Create from File. Select the picture file that you want in the database and press OPEN. Microsoft's stated strategic direction with MS Visual FoxPro is that there will be no new versions of FoxPro after version 9. Microsoft has indicated that it will be supporting Visual FoxPro until 2015.


I am an experienced database administrator, however, this is the first time I am working with MS SQL Server. Is it possible to create a resource pool for the Resource Governor? What is Database Backup Compression?


It will not be necessary to create a resource pool. Both an internal and default resource pool are created when SQL Server 2008 is installed. However, the Resource Governor does also support user-defined resource pools.

A2 Database Backup Compression provides for the compression of a SQL Server 2008 database backups natively rather than by taking uncompressed native SQL Server database backups or using a third-party tool. By default, database backup compression is turned off in the SQL Server 2008. This feature is only available in the Enterprise Edition of SQL Server 2008.


What is SDS: SQL Data Services?


SDS: SQL Data Services, which originally was known as SSDS: SQL Server Data Services, provides scalable, on-demand data storage and query processing utility services. Released in the fall of 2008, the latest version of SDS includes additional query support.



Joins Join query support allows entities to be retrieved from a container based on a join condition involving properties on different kinds of entities.
OfKind Serves to simplify the join Syntax. The OfKind function can be specified on a query From clause to distinguish between multiple entity Kinds within a container.
Order By The query syntax now supports an Order By clause in its query syntax. This optional clause provides the capability to have query results returned ordered by one or more properties in either ascending or descending order.
Take The query language now supports a Take function. This new function can be used to restrict the number of entities returned in a given query.




FILESTREAM is a new datatype in SQL Server 2008. In order to use FILESTREAM, a database needs to contain a FILESTREAM filegroup and a table which contains a varbinary(max) column with the FILESTREAM attribute set. This causes the Database Engine to store all data for that column in the file system, not in the database file.

A FILESTREAM filegroup is a special folder that contains file system directories known as data containers. These data containers are the interface between database engine storage and file system storage. The files in the data containers are maintained by Database Engine.


Are there new date and time datatypes in SQL Server 2008?


Yes. The new date and time datatypes are:



date Stores only the date component without the time component, ranging from 1st January 0001 to 31st December 9999, with an accuracy of 1 day.
time Stores only the time component, without the date component, ranging from 00:00:00.000000 to 23:59:59.9999999 with an accuracy of 100 nanoseconds.
datetime2 Stores both date and time components, ranging from 1st January 0001 to 31st December 9999, with an accuracy of 100 nanoseconds.
datetimeoffset Stores both date and time components and the time zone offset, ranging from 1st January 0001 to 31st December 9999, with an accuracy of 100 nanoseconds.

The 100 nanospaces is 7 decimal places.


What is the difference between ANSI SQL and Microsoft ACCESS SQL?



There are differences and distinctions in the syntax of SQL: Structured Query Language.

  • ANSI SQL, is the traditional Jet SQL syntax; ANSI-92 SQL has new and different reserved words, syntax rules, and wildcard characters. It is ANSI -89 Level 1 compliant.

  • ANSI-89 SQL, which also is known as Microsoft Jet SQL.


MS Access database engine SQL is a database query and programming language for accessing, querying, updating, and managing data in relational database systems. MS Access SQL includes reserved words and features not supported in ANSI SQL. It does not support the DISTINCT aggregate function references in ANSI SQL.

Significant Differences

Reserved words and datatypes Microsoft OLE DB Provider provides additional reserved words.
Rules applied to the Between...And construct


expr1 [NOT] Between value1 And value2

In MS Access SQL, value1 can be greater than value2; in ANSI SQL, value1 must be equal to or less than value2.

Additional Features

TRANSFORM statement Provides support for crosstab queries.
SQL Aggregate Functions MS Access SQL provides StDev and VarP.
PARAMETERS declaration for defining parameter queries MS Access SQL provides a query in which a user interactively specifies one or more criteria values. A parameter query is not a separate kind of query; rather, it extends the flexibility of a query.

Proprietary Databases (Other) - Questions


What is the difference between the logical data model and physical data model in the CA Erwin software.



Entity sets are equivalent to tables.

Attributes are columns of tables

Relationship sets attributes are mapped to columns of tables.


The LDM: Logical Data Model is derived from the CMD: Conceptual Data Model. The CDM consists of the major entity sets and the relationship sets, and does not state anything about the attributes of the entity sets. The LDM consists of the entity sets, their attributes, the relationship sets, the cardinality, type of relationship, etc.

The PDM: Physical Data Model consists of the entity, their attributes, the relationship sets, datatype of the columns, the various integrity constraints, etc.

Erwin categorizes the conversion / transformation of LDM => PDM as Forward Engineering which is the foundation for generating the code and the conversion of code => PDM => LDM as Reverse Engineering!

For all practical purposes, this explanation is independent of the data modeling tool.