 |
EGR
199: Fundamentals of Engineering |
Databases |
| Prabhaker Mateti | ||
|
EGR
199: Fundamentals of Engineering |
Databases |
| Prabhaker Mateti | ||
Background Information Appendix A: Acronyms
|
|
|
Executive Summary |
This article is an introduction to distance collaboration. Among the many tools available, we chose to illustrate such collaboration, in our specific lab setting, with Net Meeting that is bundled with MS Internet Explorer 5. It assumes that the student is already familiar with Web, and HTML authoring.
A database is a collection of data organized so that the stored information can be retrieved fast. You can store your standard little black book of addresses as a database. Both the content and the structure of how the data is organized are essential parts of a database. In its simple form, a database is a register. Thus, some databases have their parallel in traditional paper registers (like, for instance, parish registers and other personal records) and Cardex files. Others have a structure too complex to be compared to any kind of records in the traditional paper world.
Most databases that individuals maintain for their own use are rather tiny, and exactly how the information is organized on a hard disk is not very critical. Typical sizes of real life databases are
| A trusted source of real-time and archival information for 26 years, the LEXIS-NEXIS Group makes its premium, branded information products available in any medium, via the Web, dial-up online, CD-ROM and books. Purchasing LEXIS-NEXIS brands carries with it the guarantee of access to information from authoritative sources, enriched with valuable enhancements -- such as indexing, linkages and segmentation -- from the company that invented electronic information research. | |
| The Famous LEXIS®-NEXIS® headquartered
in Dayton, Ohio Database
|
|
A database is logically structured as a table with rows and columns. Modern databases consist of a number of interrelated tables, and there are references from information in one table to corresponding information in another. For example, in an employee database there will typically be one table with basic information on each employee, one table describing each position an employee may hold, one table which identifies each department a position can be connected to, etc. Then, for each person in the employee table there will be a reference to his or her position in the position table, and from there a reference to the department (in the department table) to which the position belongs.
A DataBase Management System (DBMS) is software that handles all operations
(such as adding, modifying, deleting, and retrieving) on a database
through a standard interface. Many DBMS’s today implement relational
databases, where all information is stored in tables, which are much like
spreadsheets.
Each row of a table represents a record.
Each column of a table represents a field.
Operations performed on tables generate new tables from existing tables.
Foundations of Relational DBMS’s
Based on formal mathematical definitions of relations.
A relation is a set of tuples. In a relational database, a tuple is equivalent
to a record (row) from a table.
A record with n fields is said to be an n-tuple.
It is useful to think of the fields in a record as relating a name to a value.
A DBMS Interface
We will use a subset of SQL called mSQL, for “mini-SQL”
A product of Hughes Technologies, an Australian company (www.hughes.com.au)
We will interface mSQL with our Java applications using an API provided as
freeware by another Aussie, Darryl Collins (www.minmet.uq.oz.au/msqljava)
Data Definition
Creating a table:
CREATE TABLE table_name
(field_name field_type [not null], …)
Example:
CREATE TABLE course
(code uint, dept char(3) not null, number uint not null,
title char(60))
mSQL Data Types
Primary Keys
Most tables have a field identified as the primary key field for that table.
No two records having the same primary key value can exist in the same table.
Use of a primary key insures there will be no duplicate records in a table.
In mSQL primary keys are defined via:
CREATE UNIQUE INDEX index_name
ON table_name (field_name)
Example:
CREATE UNIQUE INDEX idx1
ON course (code)
Removing a Table
Of course, sometimes there is a need to delete an entire table from an mSQL
database.
General:
DROP TABLE table_name
Example:
DROP TABLE course
Terminology
persistent data
database
data redundancy
data integrity
DBMS
data definition language
data manipulation language
Terminology (cont.)
Relational databases
record
field
relation
tuple (n-tuple)
SQL (mSQL)
Know how to...
Create an mSQL table using:
the “CREATE TABLE” command
the “CREATE UNIQUE INDEX” command
the mSQL data types
table and field names of your choosing
Delete an mSQL table using the “DROP TABLE” command.
In order to create and use a relational database, a DBMS needs to provide us
with a way to:
Define the form our database tables will take.
Insert, delete and modify information in our tables.
Extract useful information from our tables.
The first of these is provided by a data definition language, the remainder by a
data manipulation language.
Structured Query Language
The most popular way to access relational databases today is through the
Structured Query Language, or SQL.
SQL is both a data definition and data manipulation language.
hhhh
"
MySQL is a true multi-user, multi-threaded SQL database
server. SQL (Structured Query Language) is the most popular and standardized
database language in the world. MySQL is a client/server
implementation that consists of a server daemon mysqld and many
different client programs and libraries.
SQL is a standardized language that makes it easy to store, update and access information. For example, you can use SQL to retrieve product information and store customer information for a web site. MySQL is also fast and flexible enough to allow you to store logs and pictures in it.
The main goals of MySQL are speed, robustness and ease of use. MySQL was originally developed because we needed a SQL server that could handle very large databases an order of magnitude faster than what any database vendor could offer to us on inexpensive hardware. We have now been using MySQL since 1996 in an environment with more than 40 databases containing 10,000 tables, of which more than 500 have more than 7 million rows. This is about 100 gigabytes of mission-critical data.
The base upon which MySQL is built is a set of routines that have been used in a highly demanding production environment for many years. Although MySQL is still under development, it already offers a rich and highly useful function set.
The official way to pronounce MySQL is ``My Ess Que Ell'' (Not MY-SEQUEL).
MySQL is a very fast, multi-threaded, multi-user and robust SQL (Structured Query Language) database server.
For Unix and OS/2 platforms, MySQL is basically free; for Microsoft platforms you must get a MySQL license after a trial time of 30 days. See section MySQL licensing and support.
"
| WinMySQL is a native client of Windows 32-bits for MySQL remote databases. It allows to send SQL queries to MySQL databases directly on its TCP port 3306 (since release 1.02 you can choose it) and then to display outputs. WinMySQL doesn't need any additional layer (such as MyODBC) to work. You only need a Personal Computer running on Windows95, 98 or NT with TCP/IP (and a MySQL remote database, of course !). |
WinMySQL is a "public domain" software, you can copy it, use it, distribute it freely but without any warranty
If you use it, the author's responsability will never be engaged either if WinMySQL would be the direct or indirect cause of any damage.
If you want, you can use WinMySQL on our training database with the following parameters (note that TCP port 3306 can be closed on router by sysop without warning, so in this case, please retry later) :
MySQL host : nuage.stdenis.univ-reunion.fr TCP port : 3306 Database : cours User : scott Password : tigerThis database is a simulation of the Oracle's training database, with the following tables :
bonus : salary bonuses customer : customers dept : company departments dummy : fake table to operate non databases functions emp : employees item : orders items mgr : managers ord : orders price : prices product : items to sell sales : sales salgrade : salary ranksWinMySQL allows to use all SQL statements (sub-queries excepted) with select, insert, delete, update, create, drop, alter, rename, but for trivial security reasons, our training database MySQL only allows the select statement.
If you want to obtain a table description, you can use DESC statement or perform this fake query :
select * from table_name where 1=2This kind of query will never output any record (because 1=2 will never be true) but outputs the name of every fields of the table.
Note that WinMySQL doesn't require trailing semicolon, in opposition with the Unix client program supplied with MySQL or Oracle's SQL+.
Oracle Corporation was founded in 1977 in Redwood, California. They introduced the first Relational Database Management System based on the IBM System/R model and the first database management system utilizing IBM's Structured Query Language (SQL) technology.
Today, the Oracle DBMS is supported on over 80 different operating environments, ranging from IBM mainframes, DEC VAX minicomputers, UNIX-based minicomputers, Windows NT and several proprietary hardware-operating system platforms, and is clearly the world's largest RDBMS vendor.
Oracle employs more than 42,000 professionals in 93 countries around the world. Their expenditure for research and development is approximately 13% of their revenues.
------------------------
There have been some comments from people who cannot imagine that a large database might be valid. That simply shows a lack of imagination. Here are examples. Even the NYS DMV database is over 4GB. It has 20M records of drivers and vehicles, each of over 200B. That DB is online, accessible by every state trooper. Mike Stonebreaker tells us to learn the term 'petabyte'. You probably didn't think that that term was useful. However: The Landsat tape lib in Sioux Falls SD has 50-100TB, i.e., 0.1PB. That's on tape, but for serious datamining, to detect things like ozone holes, online would be better if it were possible. In fact, Stonebreaker claims that the storage and processing of large raster databases is the most important problem in database design. He's trying to do it, in Postgres. Finally, using synthetic aperture radar, it's now possible to digitize the world at about 1m resolution. How do you store the data? ------------------------ Wm. Randolph Franklin, wrf@ecse.rpi.edu, (518) 276-6077; Fax: -6261 ECSE Dept., 6026 JEC, Rensselaer Polytechnic Inst, Troy NY, 12180 USA
BACKGROUND CHECK USA http://www.backgroundcheckusa.com/
U.S. Geological Survey http://www.usgs.gov/
http://www.terraserver.microsoft.com/ Explore Dayton
http://www.terraserver.microsoft.com/image.asp?T=2&S=14&X=231&Y=1375&Z=16&W=1
|
|
|
Achievement Test |
| pmateti@cs.wright.edu |