When your text data type exceeds 255 characters

BY ALBERT ANTHONY D. GAVINO

A TEXT data type is a data type that establishes a field that can store text, or a combination of text and numbers (alphanumeric), but whose numbers are not used for calculations. Examples are phone numbers or e-mail addresses. When you create your tables through the Access user interface, you have two basic text types: TEXT and MEMO. But when you use SQL statements such as CREATE TABLE or ALTER TABLE to create your tables, you have many other synonyms of the TEXT and MEMO types to choose from.

In general, text fields can be up to 255 characters, while memo fields can be up to 65,535 characters, but if the memo field does not contain any binary data, then its only limit is the maximum size of the database (approximately 2.14GB or 1,070,000,000 Unicode characters). In addition, unused portions of text fields are not reserved in memory.

Personal Take:


When importing a text data type that exceeds more than 255 character, choose the MEMO type field option which has about 65, 535 characters, but this will also affect the size of your database and the speed of every query processed.

Structural Equation Modeling (SEM) Endogenous variable

ENDOGENOUS VARIABLE

a : caused by factors inside the organism or system

b : produced or synthesized within the organism or system


this one was based on research taken from 
http://www.tcrecord.org/Content.asp?ContentId=16924

DATA ANALYSIS

The proposed conceptual model was tested by using structural equation modeling (SEM) analysis. Taking into consideration measurement errors that are often inherent in survey data, SEM defines latent, multidimensional constructs (e.g., parents’ expectations, students’ motivational beliefs) and observed variables (e.g., baccalaureate expectations) while testing the theoretical links and their directions among the key variables in the study. Rooted in psychology, psychometrics, and econometrics, SEM is an appropriate approach to testing whether an a prioristructural model fits the observed data grounded in relevant literature and theory (Kaplan, 2000). By and large a confirmatory (i.e., hypothesis testing) technique, SEM consists of two parts: (1) a measurement model based on a confirmatory factor analysis that connects latent variables to observed indicator variables; and (2) a structural model that uses path analysis that accounts for relationships among endogenous, exogenous, and latent variables according to the a priori theoretical framework (Kaplan, 2000; Schreiber Stage, King, Nora, & Barlow, 2006). Figure 2 describes the SEM diagram based on the theoretical model in Figure 1.

Appending your table in MS Access

Overview

An append query selects records from one or more data sources and copies the selected records to an existing table.

For example, suppose that you acquire a database that contains a table of potential new customers, and that you already have a table in your existing database that stores that kind of data. You'd like to store the data in one place, so you decide to copy it from the new database into your existing table. To avoid entering the new data manually, you can use an append query to copy the records.

Benefits of using an append query

By using a query to copy data, you can:

• Append multiple records in one pass    If you copy data manually, you usually have to perform multiple copy/paste operations. By using a query, you select all the data at once, and then copy it.
• Review your selection before you copy it    You can view your selection in Datasheet view and can make adjustments to your selection as needed before you copy the data. This can be particularly handy if your query includes criteria or expressions, and you need several tries to get it just right. You cannot undo an append query. If you make a mistake, you must either restore your database from a backup or correct your error, either manually or by using a delete query.
• Use criteria to refine your selection    For example, you might want to only append records of customers who live in your city.
• Append records when some of the fields in the data sources don't exist in the destination table     For example, suppose that your existing customer table has eleven fields, and the new table that you want to copy from only has nine of those eleven fields. You can use an append query to copy the data from the nine fields that match and leave the other two fields blank.

Basic steps of an append query

The process of creating an append query follows these basic steps:

• Create a select query    You start by selecting the data that you want to copy. You can adjust your select query as needed, and run it as many times as you want to make sure you are selecting the data that you want to copy.
• Convert the select query to an append query    After your selection is ready, you change the query type to Append.
• Choose the destination fields for each column in the append query    In some cases, Access automatically chooses the destination fields for you. You can adjust the destination fields, or choose them if Access did not.
• Preview and run the query to append the records    Before you append the records, you can switch to Datasheet view for a preview of the appended records.

 IMPORTANT   You cannot undo an append query. Consider backing up your database or the destination table. For more information about backing up a database, see the article Back up a database.

Using the Auto Classifier in IBM Data Modeler

BY ALBERT ANTHONY D. GAVINO

The IBM Data Modeler, the Auto Classifier under the Models tab is useful when you are not sure which Predictive Model to use, IBM Data Modeler gives you this benefit to make things easier and efficient, after which you can choose a specific model such as CHAID or C&R Tree and Data Modeler will do all the work for you, take note of the Gold Icons, these are your Models created from your data sets and your data filters, each model has its own accuracy depending on the nature of your data.

Tip: Always save your stream and your data file in one folder, this will help in preventing from errors in happening such as lost file stream or lost database, Do not put your files on your desktop and your stream file in another folder as this will cause problems in the future.

Have Fun with Data Modeling!

SQL Statement Cheat Sheet

there are a bunch of SQL statements upon your disposal

SQL Statement	Syntax
AND / OR	SELECT column_name(s) FROM table_name WHERE condition AND|OR condition
ALTER TABLE	ALTER TABLE table_name ADD column_name datatype or ALTER TABLE table_name DROP COLUMN column_name
AS (alias)	SELECT column_name AS column_alias FROM table_name or SELECT column_name FROM table_name  AS table_alias
BETWEEN	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE	CREATE DATABASE database_name
CREATE TABLE	CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name2 data_type, ... )
CREATE INDEX	CREATE INDEX index_name ON table_name (column_name) or CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW	CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE (used to remove one or more rows in a table)	DELETE FROM table_name WHERE some_column=some_value or DELETE FROM table_name (Note: Deletes the entire table!!) DELETE * FROM table_name (Note: Deletes the entire table!!)
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	DROP INDEX table_name.index_name (SQL Server) DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle) ALTER TABLE table_name DROP INDEX index_name (MySQL)
DROP TABLE	DROP TABLE table_name
GROUP BY	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
HAVING	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
IN	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO (used to add single or multiple rows to a table)	INSERT INTO table_name VALUES (value1, value2, value3,....) or INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,....)
INNER JOIN	SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
LEFT JOIN	SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
RIGHT JOIN	SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
FULL JOIN	SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name
LIKE	SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY	SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC]
SELECT	SELECT column_name(s) FROM table_name
SELECT *	SELECT * FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO	SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name or SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_table_name
SELECT TOP	SELECT TOP number|percent column_name(s) FROM table_name
TRUNCATE TABLE	TRUNCATE TABLE table_name
UNION	SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
UNION ALL	SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
UPDATE (used to modify a single column in one or more rows in a table)	UPDATE table_name SET column1=value, column2=value,... WHERE some_column=some_value
WHERE	SELECT column_name(s) FROM table_name WHERE column_name operator value

USE Master, Create Database, create a table

USE Master;

GO

CREATE DATABASE Sales;

GO

USE Sales;

GO

CREATE TABLE Contacts (

   ContactsID int NOT NULL,

   Lastname varchar(50) NOT NULL,

   Firstname varchar(50) NOT NULL,

   Phone varchar(30) NULL

 )

Adding data rows unto your table

INSERT INTO Contacts

         (ContactsID, Lastname, Firstname, Phone)

VALUES (101, 'Gavino', 'Albert', '5245505')
                (102, 'Amora', 'Johnny', 'NULL')
SELECT * FROM Contacts;

Creating an Instance of SQL Server

BY ALBERT ANTHONY D. GAVINO

Security Note
By default, only members of the local administrators group can start, stop, pause, r resume or restart a service. To grant non-administrators the ability to manage services,  see How to grant users rights to manage services in Windows Server 2003.  (The process is similar on other versions of Windows.)

To start the default instance of SQL Server

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, in the left pane, click SQL Server Services.
3. In the details pane, right-click SQL Server (MSSQLServer), and then click Start.
   
   A green arrow on the icon next to the server name and on the toolbar indicates that the server started successfully.
4. Click OK to close SQL Server Configuration Manager.

To start a named instance of SQL Server

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, in the left pane, click SQL Server.
3. In the details pane, right-click the named instance of SQL Server, and then click Start.
   
   A green arrow on the icon next to the server name and on the toolbar indicates that the server started successfully.
4. Click OK to close SQL Server Configuration Manager.

To start an instance of SQL Server with startup options

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, in the left pane, click SQL Server.
3. In the details pane, right-click the instance of SQL Server, and then click Properties.
4. In the SQL Server (<instancename>) Properties dialog box, click the Advanced tab, and then click Startup Parameters.
5. At the end of the original text, in the Value column, type the startup parameters you want, and then click OK. Separate parameters with a semi-colon, for example, –c;-m.
6. Stop and restart SQL Server for the parameters to take effect.

   Note
   SQL Server running on a cluster is best managed by using Cluster Administrator. For more information, see Using SQL Server Tools with Failover Clustering.

Benefits of SQL Server

Microsoft SQL Server is a robust and scalable solution all of your business application needs. Whether you're using it for data centralization for an Access front-end, a primary data source for your web-based software system, or for managing content on your business website; SQL Server is ideal for your application.

Learn more about the benefits of using SQL Server for your data storage needs:

• Server Roles
• Database Roles
• Scalability
• Centralized Data
• Data Recovery
• Security Benefits

Server Roles

Server roles provide levels of access to server operations and tasks.

Server roles are predefined and grant the permission for the whole server. These roles are not database specific and cannot be customized. The table below provides a listing and explanation for each type of server role.

sysadmin	Able to do anything in SQL Server
serveradmin	Able to modify SQL Server settings and shut down SQL Server
setupadmin	Able to install replication and control extended stored procedures
securityadmin	Able to control server logins and create database permissions
processadmin	Able to control SQL Server processes
dbcreator	Able to create and modify databases
diskadmin	Able to manage disk files
bulkadmin	Able to execute bulk insert statements

Database Roles

Database roles provide the assignment of a set of database-specific permissions to an individual or a group of users. Database roles can be assigned to Windows Authenticated logins or SQL Server Authenticated logins. Roles that are assigned to Windows Authenticated logins can be assigned to NT users and NT groups. Roles can also be built with a hierarchical model.

Database roles are database specific. SQL Server provides three types of roles:

1. Predefined database roles
2. User-defined database roles
3. Implicit roles

Predefined Database Roles

Predefined database roles are standard SQL Server database roles. Each database in SQL Server has these roles. Predefined database roles make it easy to delegate responsibility.

Predefined database roles are database specific and cannot be customized. The table below provides a listing and explanation for each type of database role.

db_owner	Has complete access to all objects within the database, can drop and re-create objects, and has the capability to assign object permissions to other users. It can modify database settings and perform database maintenance tasks. This role encompasses all functionality listed in the other predefined database roles.
db_accessadmin	Controls access to the database by adding or removing Windows Authentication users and SQL Server users.
db_datareader	Has complete access to SELECT data from any table in the database. This role does not grant INSERT, DELETE, or UPDATE permissions on any table in the database.
db_datawriter	Can perform INSERT, DELETE, or UDPATE statements on any table in the database. This role does not grant SELECT permission on any table in the database.
db_ddladmin	Has the capability to create, modify, and drop objects in the database.
db_securityadmin	Performs security management within the database. This role manages statement and object permissions and roles within the database.
db_backupoperator	Has the capability to back up the database.
db_denydatareader	Denies SELECT permission on all tables in the database. However, this role does allow users to modify existing table schemas. It does not allow them to create or drop existing tables.
db_denydatawriter	Denies data modification statements (INSERT, DELETE, or UPDATE) from being performed against any tables in the database.
public	Every database user is a member of the public role. A user automatically becomes part of the public role when she is permitted access to the database.

Scalability

Computer systems typically grow over time. Maybe you have a need for 30 concurrent users in your application now, but you have plans to grow and expand. SQL Server is built to be scalable and is able take your application from 30 users to thousands of users, quickly and easily.

Table size can be an issue with other database servers, but not SQL Server. The table size is only limited by the amount of space that you have on your physical server. This means that 90% of SQL Server users can store all of their data within the same instance.

This database server gives you so much flexibility that you can have multiple applications using the same SQL Server. Imagine your web applications and desktop applications using the same data, so that people in the office and employees remotely could simultaneously update data just as if they were sitting next to each other. When you choose SQL Server, you choose flexibility for your data-driven applications.

Centralized Data

Having a centralized place to store data is a great benefit to SQL Server users. Centralization is a primary SQL Server benefit and means that everyone is using the same data source. As a result there is no need to merge information together in order to receive an accurate version of a record.

With centralized data, every time that you retrieve a record, you will be confident that you have the latest information.

Data Recovery

One of the SQL Server benefits is the presence of built-in tools to manage data and database backups.  Imagine if you had a Microsoft Access database and it was deleted.  There is no good way to get your information back.  With SQL Server, you have the ability to schedule regular backups of the database.  These back-ups could be stored in a separate location for disaster recovery.

There are two kinds of backups available in Microsoft SQL Server:

• Full Backup
• Differential Backup

Full Backup

A full backup stores a complete copy of the database on any supported backup media.  You do not have to take the database offline to perform a full backup, however these backups consume a large amount of resources and users may notice that response times are a little slower.  Full backups can be scheduled for off peek times to minimize the impact that performance degradation would have on the end users.

Differential Backup

There are situations where you may not want to use as many resources as a full backup requires, but feel that it is important to have a backup at a certain time.  This is when you may consider a differential backup.  This backup uses less resources since it backs up information that has changed or been added since the last full backup.  **It is very important to note that a differential backup alone will not be enough to restore the database, since it only contains data that was modified since the last full backup.

Security Benefits

There is an old saying that states that knowledge is power.  The ability to securely store and retrieve data is one of the pillars of the IT world.  With SQL Server there are several security options to ensure that your data is stored safely.

SQL Server supports two security modes:

1. Windows Authentication
   • Uses the credentials of the person logged into the Windows machine.
2. SQL Server and Windows Authentication (Mixed Authentication)
   • Uses both Windows Authentication and SQL Server Authentication.

If you are using Mixed Authentication, please note that sa (which means system administrator) by default, does not contain passwords.  The sa username is very powerful and gives the user the right to manipulate all databases on SQL Server.  After the installation of SQL Server, please make sure that changing the sa password is at the top of your list of things to do.

If you are using Mixed Authentication it is good practice to create a username and password for applications that would restrict access to only the databases that the application uses.

There are two types of permissions in SQL Server:

• Server Roles
• Database Roles