Archive for the ‘SQL’ Category

« Older Entries

Functional dependencies: How databases relate

Tuesday, December 8th, 2009

“The single most important concept in relational schema design theory is that of a functional dependency,” write Ramez Elmasri and Shamkant Navathe in “Fundamentals of Database Systems.”

But what is a functional dependency? It is the actual relationships in the relational database. It is the relations among the attributes within a table. It is a constraint between two sets of attributes. The relations are permanent and unalterable.

For instance, a table may have two attributes, or columns. If one is the primary key, we can then say we can always determine the value of the second attribute using the primary key. This means we can use the primary key as an index to look up the second attribute. It is a mathematical certainty. The primary key is the determinant and the other is the dependent, the parlance of database-speak.

(Keep in mind that this relationship does not work in reverse. You can not use a dependent value to definitively determine the primary key, chiefly because the dependent value may not be unique in a given table).

Beyond the simple connection drawn between the primary key and the dependents, a number of other inferences can be made as well, using Armstrong’s inference rules, which cover the laws of logic such as transitivity and reflexivity.

All material taken from a class I’m attending at UMUC on relational databases, as well as from the book….



…All mistakes are my own, though..
–Joab Jackson

Tags: ,
Posted in SQL, Uncategorized, database | Comments Off

PHP: Post the results of a simple MySql Query on a Web Page

Saturday, October 24th, 2009

Say you want to post the results of a simple query from a MySQL database on a Web page, using PHP. You would think that all you’d need to do is assign a variable name to the results of the SQL query, and then ask PHP to print the variable.

It doesn’t work that way. Instead of PHP printing the result, what gets printed is a mysterious message, like “Resource ID #3″

As explained here, the variable itself points to a place holder of sorts. To get the actual value, you have to use another MySQL function.

In this case that function would be mysql-fetch-row.

For example, within the PHP body of code, you do something like this:

$QueryResult = mysql_query(“select avg(Height) from Boys);

$ResultInBetweenStep = mysql_fetch_row($QueryResult);

$ResultPresent = $ResultInBetweenStep[0];

echo($ResultPresent);

In the above quote, we’re getting the result of a query from a table called Boys that is the average of all the entries in the Height column. It is assigned to the variable $QueryResult.

In order to get the actual data from the query, the function mysql_fetch_row is applied to $QueryResult, and the results are stored in another variable, $ResultInBetweenStep.

The final step is to assign a variable to the first row of $ResultInBetweenStep only (which would be the *only* row in the query, as the average function will return a single number), which, here, is called $ResultPresent.

$ResultPresent can then be printed.

There are other MySql functions that allow you to extract more complex bits of information from a MySQL query. Check the mysql_fetch_* entries here for more info.–Joab Jackson

Posted in MySql, PHP, SQL, Web, database | Comments Off

Databases: Further Defining Entities

Sunday, October 11th, 2009

In a previous post, I sketched out the basic structure of a database model. The basic elements are entities, relations and attributes. This post will describe the further definitions that can be made under this model, using the Enhanced Entity Relation Model (EER).

First, we can specify different types of entities a bit further, into subclasses and superclasses. Subclasses are groupings within a set of entities that are clustered in different roles they play, within the category of the entity. If “employee” is an entity, then “technician” and “manager” could be two different subclasses. Inversely, “employee” is a superclass of “technician” and “manager.”

Defining how subclasses is a process called specialization. The employee entity can be broken into specialization subclasses, such as “technician” or “secretary.” Some attributes can only be applied to certain subclasses (a secretary’s typing speed, for instance). These are called specific attributes or local attributes.

In some cases, inclusion into a subclass can be determined by the existence of a certain attribute. These subclasses are called predicate-defined subclasses. Membership is defined by a particular value of an attribute. (Subclasses can also be defined without any particular attribute, though they must be defined manually–Those are called user-defined subclasses).

The reverse operation of specialization–namely to summarize a set of subclasses into a master entity, is called a generalization.

All material taken from a class I’m attending at UMUC on relational databases, as well as from the book….



…All mistakes are my own, though..
–Joab Jackson

Tags: , , , ,
Posted in SQL, database | Comments Off

Databases: Entities, Relationships and the Attributes That Describe Them

Sunday, October 4th, 2009

At their most basic, relational databases are composed of three sets of elements: An entity, a relationship or an attribute. Relationships tie together different entities, and both can have attributes.

This rule-of-thumb comes from the Entity-Relationship (ER) model, which sets the formal rules for how different attributes relate to one another, at an abstract level.

An entity can be anything, or, to be more precise, any thing. An entity can be a person, or a type of motorbike, or a particular motorbike. An entity’s attributes are a set of properties used to describe the entity. An person may have a first name, last name, birthdate—entities all.

Attributes can be single-valued or have multiple values (the names of the person’s siblings, for instance). An attribute can be a composite of multiple values: A person’s address itself an attribute. It could be composed of an address, city name, zip code, and other elements.

Attributes may also be derived, meaning that they don’t actually reside anywhere on the database, but can be calculated when needed, i.e. how many people live within a certain zip code.

Also, in many cases, one of the attributes will what is known as a key attribute. This attribute must be a unique value, at least against for each and every other entity kept in the database. The key attribute allows a query to pick out an individual entry, among all the entries. A person’s social security number, for instance, can work as a primary key, because every person’s SSN is different.

Relationships are used to tie together different entities, explaining the relation between the two. A PERSON (one entity) WORKS FOR (a relationship) a COMPANY (another entity).

Relationships can have different levels of cardinality, meaning that one entity can have one or more than one relationships with other entities. A PERSON can work for more than one COMPANY. And a COMPANY can employ more than one person. Such rules are usually defined in the database model.

Relationships, like entities, can have attributes. In the example above, the WORKS FOR relationship can have an attribute such as DATE STARTED.

All material taken from a class I’m attending at UMUC on relational databases, as well as from the book….



…All mistakes are my own, though..
–Joab Jackson

Tags:
Posted in SQL, database | Comments Off

Databases: Understanding the relational model

Wednesday, September 23rd, 2009

A relational database is different from a normal collection of stuff written down in a computer file insofar as a good database tries to map out the relationships among all the elements.

IBM computer scientist E. F. Codd first developed the relational model. While the Structured Query Language (SQL) is an implementation of the relational model, it is sometimes helpful to understand the underlying relational model itself.

Thinking abstractly, a relational database can be thought of in terms of three interlocking parts: The object, the integrity and the operators. The object is how the data is structured. The integrity restrains the data to fit the appropriate formats outlined in the Object, as well as keep everything tidy and, well, relational. Finally, the operators are the ways in which that data is allowed to be manipulated.

The Object

A database requires that a set of data to be entered in a uniform fashion. Each set of data is called a relation. A database can have multiple sets of relations, perhaps joined by foreign keys (more on that later).

A relation defines a set of columns in which each column, called an attribute, is devoted to a predefined subset of the data. Each entry into this relation, called a tuple, should have a data for each attribute (though null values can be permitted, depending on the database’s predicate, or definition of possible contents for each tuple).

For example, a video store may keep a database of customers. This relation may consist of attributes such as “customer ID,” “last name,” “first name,” “zip code,” and so on. In this case, a tuple is an individual entry describing one person (#42, “John” “Smith” “20707”).

Thinking in terms of attributes, the set of all the implied possible values for an attribute is called a domain. For instance, the Domain for Social Security numbers would be all the possible sets of numbers that fit in the NNN-NN-NNNN format (though an entry with a letter or letters inserted in place of a number would not be part of the domain).

For more defining characteristics of relational databases, see Dodd’s 12 Rules.

Wrapping up, the entire set of relations is called the schema. The collected values at any one point of time is called its state.

Keep in mind that the terminology for the relational model does not always map exactly to terminology in SQL. For the most part they are pretty close. In SQL, term relation is equivalent to table, tuple means row, and attribute is a column.

The Integrity

Integrity constraints are the rules that ensure the relation keeps order of its data.

At the tuple level, identifying each tuple with some unique characteristic is important. A superkey is a combination of attributes that uniquely defines a tuple. Alternately, one of the keys can be designated as a primary key. A primary key must be completely different for each tuple. If a new entry is added for each new date, with no two entries ever falling on the same date, then the date could be the primary key. Or, in a sales environment, an “order number” could serve as a primary key, since each order number is unique. Obviously, no primary key can have a null value.

Alternately, the could be as simple as an incremented number assigned to each new entry, by the database (This entry, generated solely for the purpose of creating a primary key, is called a surrogate key). If more than one entry is used is to fashion a unique identifier, this approach is called a composite primary key.

Foreign keys are the way to link different tuples. A e-commerce store may have a regular customer buy a new item. In the customer relation, the customer ID is the primary key, while in the transactions relation, the order number is the primary key. However, the order tuple includes the customer ID. In this tuple, the customer ID is a foreign key.

It should be noted that these two relations must maintain what is called referential integrity, meaning that any customer ID in the transactions relation must be in the customer relation.

In addition to these internal constraints, the database operator may apply some own rules, derived from the operations being recorded in the database. These rules can be enforced through the CHECK constraints in SQL.

The Operations

Now that we’ve described the database structure (the “object”), as well as how the pieces are related to one another (the “integrity”), lastly we get to the operations, or what can be done with the database.

Basically, you can do one of four things with a database data:

*You can retrieve existing data
*You can insert new data
*You can update existing data
*You can delete existing data

Of course, there are a lot of different things you can do within these four broad categories. All of these options are contained within what is generically called SQL’s data manipulation language, or DML.

All material taken from a class I’m attending at UMUC on databases, as well as from the book….



…All mistakes are my own, though..
–Joab Jackson

Tags:
Posted in SQL, database | Comments Off

SQL: Adding a New Column to a Database Table

Sunday, May 24th, 2009

When you have already created a database, and a table, and you need to add another column to the table This is what you add:

ALTER TABLE [TableName] ADD [ColumnName DataType]

To see what you’ve done, use the SHOW COLUMNS command:

SHOW COLUMNS FROM [name of database table]

Taken from >SQL-Tutorial.com and the MySQL Reference Manual. MySQL 5.1 was used here.
–Joab Jackson

Posted in MySql, SQL, database | Comments Off

SQL: Fetching & Parsing with SELECT

Wednesday, May 20th, 2009

The SELECT statement is used in SQL to select data from the database.

Some background first: SQL stands for structured query language. It is the language used for working with a relational database.

SQL commands can be split into two groups. One is for working with the data within the database (called the The Data Manipulation Language, or DML). The other works designing the tables, as well as defining the relationship among them (The Data Definition Language, or DDL).

All of these commands can be used from the command line prompt, once you’ve gained admittance to the Relational Database Management System (that is the MySQL software itself, or whatever RDMS you are using).

SELECT Falls under DML. It is the basic command for pulling data from the database, either directly from the command line, or as part of a Java or PHP connector.

The basic format for SELECT is:

SELECT [column(s)] FROM [table]

(Note for all these blog pages, you fill your own values in the brackets “[ ]“).

So, if you wanted to see all the info in the database “test,” you would write:

SELECT * from test;

(“*” is a wildcard operator. MySQL requires a “;” at the end of each statement, not shown here. Multiple columns can be selected to show, separated by a comma.)

If you just wanted to see one column of data, say the column of numbers called “measure” you would write:

SELECT measure FROM test;

So SELECT is pretty easy, yes? But it is also rather coarse-grained, returning a tree when what you want is a branch. You need to do some more whittlin’!

You build a more precise query from SELECT using additional qualifiers.

The main way of doing this is through the WHERE keyword, which come after the FROM statement that specifies the table. To further qualify you can also append, in various mixes, the BETWEEN, LIKE, IN, AND or OR keywords, along with selected values to filter by.

Finally, you can order the results by ORDER BY and filter them by DISTINCT, niether of which require WHERE, but could be used in conjunction with WHERE.

What follows are the details:

* * *

With WHERE you can pull only those records from a table that meet some “specific criteria,” as the W3C puts it. WHERE is part of a SELECT statement.

SELECT [column] FROM [table] WHERE [column]=[value you seek]

i.e.,

SELECT measure FROM test WHERE measure=0;

In this example, I used the “=” But other conditionals include…

<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal

Now, for some other keywords:

* * *

BETWEEN: BETWEEN returns a range of data specified between two values.

The format for this is:

SELECT [column(s)] FROM [table] WHERE [column]
BETWEEN [lower value] AND [upper value]

The values can be numbers, date or even text! Text is ranked, ascending or descending, by alphabetical order. Here is an example:

SELECT * FROM Bike WHERE Date
BETWEEN 20090501 AND 20090515

…Will return all the data in all the columns in the table Bike that have a date between May 1 and May 15 2009.

* * *

IN: IN specifies specific entries. When you are looking for values within known entries, then IN is what you’d use.

SELECT [column] FROM [table] WHERE [column] IN ([value(s)])

Commas separate multiple values in the IN set. And, as always if the values are text, they should be in single quotes. Example:

SELECT * FROM Bike WHERE RideTime IN [30, 60]

..Returns all the rows in the Bike table where the RideTime column equals 30 or 60.

* * *

LIKE and NOT LIKE: LIKE is a pattern-matcher. You can structure a query with the elements that you know are in a value that you are looking for, with wild card elements both before and after the known-nugget of info.

The format:

SELECT [column(s)] FROM [table] WHERE [column] LIKE [pattern]

For these operations the percentage sign, %, is the wildcard operator. In a hypothetical database, “Turkey%” will return “TurkeyShoot” and “TurkeyBreast” and “%Turkey” will return “unTurkey” and “%Turkey%” will return all three i.e.

NOT LIKE is the same but returns all the results that DON’T match the criteria you select.

SELECT * FROM BirdWords WHERE Fowl LIKE ‘%Turkey%’

NOTE: Single quotes are used also for fetching numerical responses, dig?

* * *

DISTINCT: DISTINCT is actually a precursor to WHERE–It comes before WHERE in the SQL statement and, in fact, can be used without WHERE. It picks out all of the original values in a column, eliminating any duplicates.

SELECT DISTINCT [column] FROM [Table]

* * *

AND/OR An AND or OR statement may be placed after WHERE for further distinction:

SELECT [column(s)] FROM table WHERE [column]=[value1] AND [column]=[value2]

SELECT [column(s)] FROM table WHERE [column]=[value1] OR [column]=[value2]

SELECT [column(s)] FROM [table] WHERE [column]=[value1] AND ([column]=[value2] OR [column]=[value3])

Note, operators other than the “=” could be used. See above. In the final example AND and OR can be combined, with a parenthesis to clarify order of evaluation with the machine. Play around with these for greater nuances, i.e.

SELECT * from Run WHERE Time=60 OR Time=50 AND (Date=20090101 OR Date=20090201);

…Will return all those entries with a date of Jan 1 2009 or Feb 1 2009 in which the Run Time was 50 or 60.

* * *

ORDER BY The ORDER BY specifies how the results of a query should be returned. They results can be returned either in a numerically ascending (or alphabetical), or descending (reverse alphabetical) for whatever column you choose. Default is ascending.

Again, ORDER BY can be done without WHERE. It is placed at the end of the SQL statement:

SELECT [column(s)] FROM [table] ORDER BY [column] [ASC or DESC]

Example:

SELECT * FROM Run ORDER BY Time DESC

…Will return all the entries from the Run table ranked by time, longest time first.

Taken from the W3C School’s tutorial on SQL, as well as from SQLCourse, a helpful site I found on the Web.

–Joab Jackson

Posted in MySql, SQL, Unix, database | Comments Off

PHP: Entering data into MySql with PHP

Tuesday, May 12th, 2009

For some reason, there is very little direct instruction on the Web about how to enter data into a MySQL database from a Web page, using PHP. This dearth of info is surprising insofar as this procedure is one of the most commonly executed actions on the Web.

There are plenty of online tutorials, but for some reason, they tend to be made more complicated than they need to be.

Anyway, forthwith, here is the very basic mechanism used for entering data into a database over the Web.

First gather the ingredients: This tutorial uses Linux for the Web server (Ubuntu 8.04), with Apache running the PHP module, with the MySQL package. Also, MySQL (5.1 in this example) is already set up and running, with all the permissions worked out.

In this example, we will use a new database to be run inside MySQL, called “test.” It will have a single table called “sample” with one column of data text data. What it is called is not relevant. You will also require a username and password for the database.

For this action to happen, we will need TWO web pages, an HTML landing page where the user enters the data, and a PHP page that the HTML page will call that will do the actual work of entering the data into the database. (These can be combined into one page with the “form action = self” html declaration, which I’ll figure out another day).

First, you create an HTML page, where the user can enter data. Instructions on how all this is done are here.

In the code on this page, the user has a box in which some data can be entered, which the page tags as the variable “somedata” when the submit button is pushed. Also, when the submit button is pushed, this page will call up a PHP page in the same directory, called “InsertingIntoDatabase.php” (which we will create in a moment).

<FORM ACTION=”InsertingIntoDatabase.php” METHOD=”get”>
<p>
Enter Data: <INPUT TYPE=”text” NAME=”somedata” />
</p>
<INPUT TYPE=SUBMIT VALUE=”GO” />
</FORM>

Note: For the full working code, click here, and save page, and replace the .txt extension in the file name with a .html extension.

For this setup, I used the HTML “get” method. I could have also used “post.” The differences between the two are explained here.

Now onto the PHP page. Basics on creating a PHP page are here. The PHP code block will have a number of discrete steps:

1. Connect to the database server:

$open = @mysql_connect(“localhost”,”[USERNAME]“, “[PASSWORD]“);
if (!$open) {
echo( “

Can’t connect to database!

” );
exit();
}

In this chunk, you are opening the database.

The @mysql_connect PHP command is the command that does the actual work of opening the database. It requires three things: Type of connection, user name and password. Since the Web page is on the same server as the database, the connection will be “localhost”. You need to enter a username and password for an account that has write privileges to the database you will be using.

You save this command and its arguments as a variable, called $open, in this case. $open is run when the Web page is called. If the connection does not work, (signified by “!$open”) either because the database is not found, or your username or password doesn’t work, then you will get an error message (“Can’t connect to database!”).

2. Gather the data and specify database to be used.

$somedata = $_GET["somedata"];
$db = (“test”);

In this step, you grab the data from the server that was saved by the HTML page under the variable “somedata”. In PHP, it will also be called somedata, as $somedata. You also specify the name of the database within MySQL that you will open. Here it is called “test” but you set the name when you created the database.

3. Prepare the SQL query:

$sql = “INSERT into sample VALUE(‘”.$somedata.”‘) “;

Here is the the actual query you will submit to the database. It is formatted exactly like any other MySQL query. (Note: in SQL speak, even inserting data is called a “query,” evidently).

For this query, we are sending the contents of $somedata to a one-column table called “sample.” (Obviously you need to replace “sample” with the name of your own table, the one that you created when you created your database within Mysql). Note all the query is in quotes, except for the PHP variable $somedata.

In PHP, variables can not be called from within a quoted stream of text. So we are concatenating a string together by breaking the query into two strings, one before the variable (which is placed where it is needed in the statement) and the other after. Concatenating elements together in PHP is done with the period, “.”

Also, MySQL itself (at least the 5.1 version) requires that text data must be entered with SINGLE, not double quotes, hence the single quotes above.

This query is saved in php as a variable, called $sql. It looks complicated, but what PHP is storing is a straight-forward MySQL data insert query.

4. Run the query

Here is the code for doing this..

$doit = mysql_db_query($db, $sql);

The database is already open, so all we need to do is enter the query. The PHP “mysql_db_query” command runs the actual query. It requires two bits of information: First element is the name of the database (captured as a variable called “$db” here). The second element is the SQL-formatted query (“$sql”).

The entire command is captured as the variable “$doit” which, again, runs when the page is called.

5. Report results on the page:

If the query for some reason does not take, we need to alert the user. Likewise, if it works, then we should also report that. This mightbe best handled by the PHP “If … Else” statement:

if (!$doit) {
echo(“It didn’t work because of this: ” .mysql_error());
}
else
echo (“Data entered!”);

Here, if the data was not inserted due to some error (“!$doit”) then the PHP returns a statement saying so (“It didn’t work because of this: “) along with the error message returned by MySQL itself (“mysql_error()”) concatenated onto the end.

If the transaction did work (“else”),a message of success is returned.

6. Close database

mysql_close($open);

And that is how you enter data into a MySQL database using PHP. There are lots more you can do it terms of security, handling of errors, input-checking and so on, but this is the bare-bones approach.

Note: For the full working code for this PHP page, click here, and save page, but replace the .txt extension in the file name with a .php extension. Also replace the [USERNAME] [PASSWORD] and [DATABASENAME] with your own variables.

–Joab Jackson

And now a word from our sponsor:

Posted in MySql, PHP, SQL, Ubuntu, Unix, Web | Comments Off

MySQL: Entering and viewing data

Sunday, January 11th, 2009

Inserting data into a table:
mysql> INSERT Into Weight VALUES (20090101, 240);

(Note: It appears that you must enter the data in the order in which the columns were first entered. (Also please note that in MySQL 5.1 the only date format that gets ingested properly is in the format yyyymmdd, with no dashes, periods, etc.)

This will show everything in the database:
mysql> SELECT * FROM Weight;

–Joab Jackson

Posted in MySql, SQL, database | Comments Off

MySQL: Average and summing data

Friday, January 9th, 2009

To find an average of numbers in a database:

mysql>SELECT AVG ([column]) from [Table];

i.e.

mysql>SELECT AVG(HWeight) FROM Weight;

To sum of numbers in a database:

mysql>SELECT SUM ([column]) from [Table];

i.e.

mysql>select SUM(Weight)from Weight;

From here

–Joab Jackson

Posted in MySql, SQL, database | Comments Off

« Older Entries