Frequently Asked Questions (FAQ) & Advanced Topics
| 1. | Why can't I just ask for the first three rows in a table? |
| Because in relational databases, rows are inserted in no particular order, that is, the system inserts them in an arbitrary order; so, you can only request rows using valid SQL features, like ORDER BY, etc. |
| 2. | What is this DDL and DML I hear about? |
| DDL (Data Definition Language) refers to (in SQL) the Create Table statement...DML (Data Manipulation Language) refers to the Select, Update, Insert, and Delete statements. Also, QML, referring to Select statements, stands for Query Manipulation Language. |
| 3. | Aren't database tables just files? |
| Well, DBMS's store data in files declared by system managers before new tables are created (on large systems), but the system stores the data in a special format, and may spread data from one table over several files. In the database world, a set of files created for a database is called a tablespace. In general, on small systems, everything about a database (definitions and all table data) is kept in one file. |
| 4. | (Related question) Aren't database tables just like spreadsheets? |
| No, for two reasons. First, spreadsheets can have data in a cell, but a cell is more than just a row-column-intersection. Depending on your spreadsheet software, a cell might also contain formulas and formatting, which database tables cannot have (currently). Secondly, spreadsheet cells are often dependent on the data in other cells. In databases, "cells" are independent, except that columns are logically related (hopefully; together a row of columns describe an entity), and, other than primary key and foreign key constraints, each row in a table is independent from one another. |
| 5. | How do I import a text file of data into a database? |
| Well, you can't do it directly...you must use a utility, such as Filopto Import/Export Manager, or write a program to load the data into the database. A program to do this would simply go through each record of a text file, break it up into columns, and do an Insert into the database. |
| 6. | What is a schema? |
| A schema is a logical set of tables, such as the Antiques database above...usually, it is thought of as simply "the database", but a database can hold more than one schema. For example, a star schema is a set of tables where one large, central table holds all of the important information, and is linked, via foreign keys, to dimension tables which hold detail information, and can be used in a join to create detailed reports. |
| 7. | What are some general tips you would give to make my SQL queries and databases better and faster (optimized)? |
| o | You should try, if you can, to avoid expressions in Selects, such as SELECT ColumnA + ColumnB, etc. The query optimizer of the database, the portion of the DBMS that determines the best way to get the required data out of the database itself, handles expressions in such a way that would normally require more time to retrieve the data than if columns were normally selected, and the expression itself handled programmatically. |
| o | Minimize the number of columns included in a Group By clause. |
| o | If you are using a join, try to have the columns joined on (from both tables) indexed. |
| o | When in doubt, index. |
| o | Unless doing multiple counts or a complex query, use COUNT(*) (the number of rows generated by the query) rather than COUNT(Column_Name). |
| 8. | What is a Cartesian product? |
| Simply, it is a join without a Where clause. It gives you every row in the first table, joined with every row in the second table. This is best shown by example: |
SELECT *
FROM AntiqueOwners, Orders;
This gives:
AntiqueOwners. AntiqueOwners. AntiqueOwners. Orders. Orders.
OwnerID OwnerLastName OwnerFirstName OwnerID ItemDesired
01 Jones Bill 02 Table
01 Jones Bill 02 Desk
01 Jones Bill 21 Chair
01 Jones Bill 15 Mirror
02 Smith Bob 02 Table
02 Smith Bob 02 Desk
02 Smith Bob 21 Chair
02 Smith Bob 15 Mirror
15 Lawson Patricia 02 Table
15 Lawson Patricia 02 Desk
15 Lawson Patricia 21 Chair
15 Lawson Patricia 15 Mirror
21 Akins Jane 02 Table
21 Akins Jane 02 Desk
21 Akins Jane 21 Chair
21 Akins Jane 15 Mirror
50 Fowler Sam 02 Table
50 Fowler Sam 02 Desk
50 Fowler Sam 21 Chair
50 Fowler Sam 15 Mirror
The number of rows in the result has the number of rows in the first table times the number of rows in the second table, and is sometimes called a Cross-Join.
If you think about it, you can see how joins work. Look at the Cartesian product results, then look for rows where the OwnerID's are equal, and the result is what you would get on an equijoin.
Of course, this is not how DBMS's actually perform joins because loading this result can take too much memory; instead, comparisons are performed in nested loops, or by comparing values in indexes, and then loading result rows.
| 9. | What are relationships? |
| Another design question...the term "relationships" (often termed "relation") usually refers to the relationships among primary and foreign keys between tables. This concept is important because when the tables of a relational database are designed, these relationships must be defined because they determine which columns are or are not primary or foreign keys. You may have heard of an Entity-Relationship Diagram, which is a graphical view of tables in a database schema, with lines connecting related columns across tables. See the sample diagram at the end of this section or some of the sites below in regard to this topic, as there are many different ways of drawing E-R diagrams. But first, let's look at each kind of relationship... |
A One-to-one relationship means that you have a primary key column that is related to a foreign key column, and that for every primary key value, there is one foreign key value. For example, in the first example, the EmployeeAddressTable, we add an EmployeeIDNo column. Then, the EmployeeAddressTable is related to the EmployeeStatisticsTable (second example table) by means of that EmployeeIDNo. Specifically, each employee in the EmployeeAddressTable has statistics (one row of data) in the EmployeeStatisticsTable. Even though this is a contrived example, this is a "1-1" relationship. Also notice the "has" in bold...when expressing a relationship, it is important to describe the relationship with a verb.
The other two kinds of relationships may or may not use logical primary key and foreign key constraints...it is strictly a call of the designer. The first of these is the one-to-many relationship ("1-M"). This means that for every column value in one table, there is one or more related values in another table. Key constraints may be added to the design, or possibly just the use of some sort of identifier column may be used to establish the relationship. An example would be that for every OwnerID in the AntiqueOwners table, there are one or more (zero is permissible too) Items bought in the Antiques table (verb: buy).
Finally, the many-to-many relationship ("M-M") does not involve keys generally, and usually involves identifying columns. The unusual occurrence of a "M-M" means that one column in one table is related to another column in another table, and for every value of one of these two columns, there are one or more related values in the corresponding column in the other table (and vice-versa), or more a common possibility, two tables have a 1-M relationship to each other (two relationships, one 1-M going each way). A [bad] example of the more common situation would be if you had a job assignment database, where one table held one row for each employee and a job assignment, and another table held one row for each job with one of the assigned employees. Here, you would have multiple rows for each employee in the first table, one for each job assignment, and multiple rows for each job in the second table, one for each employee assigned to the project. These tables have a M-M: each employee in the first table has many job assignments from the second table, and each job has many employees assigned to it from the first table. This is the tip of the iceberg on this topic...see the links below for more information and see the diagram below for a simplified example of an E-R diagram.
