Submit Database Questions to Computer Education Techniques Knowledge Base CETi


*Submit Database Questions*	Home

			Advanced search
Submit Database Questions	Knowledge Base	Database Courseware


Database Technology Updates

Crystal Reports Technology Update	DB2 Technology Update	IMS Technology Update	MS SQL Server Technology Update	Oracle Technology Update

Database Questions - General and Open Source	Microsoft Databases - Questions
Proprietary Databases (Other) - Questions

Interrelated Information Technology
Crystal Reports Questions	DB2 Questions
IMS Questions	Oracle Questions

The Computer Education Techniques 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 of the information of this service and reliance on the answers, information or other materials received through this web site 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.

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.

Term	Explanation
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/JRT	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.
SQL/XML	XML-Related Specifications	Defined by ISO/IEC 9075-14:2003. Specifies SQL-based extensions for using XML in conjunction with SQL.
SQL/PSM	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.

Explanation

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?

Explanation

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, would require some programming.

Create a database column with the General data type 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. Micrsoft'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.

Q	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?
A1	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.

Function/Clause	Explanation
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.

What is FILESTREAM?

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 data types in SQL Server 2008?

Yes. The new date and time data types are:

Data Type	Explanation
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 (7 decimal places).
datetime2	Stores both date and time components, ranging from 1st January 0001 to 31st December 9999, with an accuracy of 100 nanoseconds (7 decimal places).
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 (7 decimal places).

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

Background

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 is also 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 data types

Microsoft OLE DB Provider provides additional reserved words.

Rules applied to the Between...And construct

Syntax:

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.

Assumptions

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.

Schedule