Transcript Introduction to Linux

Phil Brewster
[email protected]

One of the first steps – identify the proper data types

Decide how data (in columns) should be stored and used
 character, numeric, date-time, or other data type (binary, enum, etc.)

Data types determine how you will be able to use data in
queries and in application program coding (edit routines)




Do you need to perform calculations on the data?
Do you need the data to be ‘constant’ or ‘variable’?
Must the values be unique (often required for primary keys)?
Can there be ‘missing values’ (allow NULL)?
▪ ‘Missing’ -- NOT to be confused with zeroes or spaces or initial default values!
Set of rules against which to test a
relational system (E.F. Codd, 1970)
1. All data represented as tables
2. Logical representation independent
from physical storage
3. Single high-level language to build
tables, insert/update/delete data
4. Support relational operations (select,
project, join) and set operations
(union, intersect, difference, division)
5. Support views (alternate ways of
looking at tables)
6. Differentiate between unknown (null)
and zero
7. Mechanisms to support security and
authorization
8. Protect data integrity through
transactions and recovery procedures
[summary slides for Codd’s Rules: courtesy Dr K. Sward]
 SQL lets you interact with a relational database system
 ISO standards – most commands are the same
 Yet, also different ‘dialects’ for major database systems
▪ Oracle, Microsoft SQL Server, DB2, MySQL
 Need to compare syntax rules across systems you use
 Administrative commands to create logical databases,
tables, grant user permissions
 Application/user commands to load data, insert and
update rows, select and order data

One-to-many data relationships: primary  foreign key

Many-to-many data relationships: junction tables

First Normal Form (1NF)
 Eliminate repeating groups in individual tables
 Create a separate table for each set of related data
 Identify each set of related data with a primary key

Second Normal Form (2NF)
 Create separate tables for sets of values that apply to multiple records
 Relate these tables with a foreign key

Third Normal Form (3NF) – frequently this is ‘overkill’…
 Eliminate fields that do not depend on the key
http://support.microsoft.com/kb/283878

Can be shared by many applications, many
users (clients) at once



Database environment
Database security
Database administration

For application programs (APIs) to access
server, need database connectors (drivers)
 Remember JDBC for Java? – needs custom drivers
 ODBC for almost everything else


The Community Edition is free (no tech
support unless you buy the licensed version)
The product is stable (since version 4)
 Scalable, good performance

An alternative to other databases (Microsoft,
Oracle) but without as many utilities
 Oracle PL/SQL
▪ a powerful way to integrate database logic with
application logic (adds procedural programming
constructs like if-else to ‘pure’ SQL)
 Microsoft SQL Server
▪ proprietary ‘hooks’ integrating with MS applications
Pick the installer for your favorite OS



MySQL Community Server:
http://dev.mysql.com/downloads/mysql/
MySQL Workbench (GUI tools):
http://dev.mysql.com/downloads/workbench/
5.2.html
MySQL database connectors (future):
http://dev.mysql.com/downloads/connector/



Initial access to the MySQL server as root
Create a standard user id + password
Grant permissions
 then log back in as user and do not use root!




Create a database
Create tables in the database
Load data into the tables (student.txt,
course.txt)
Run queries to view data, sort data, etc.

Add primary keys to the tables (student2.txt,
course2.txt)



Create a junction table
Use foreign key relationships to link tables
Insert rows

Run more complex queries using table joins

Use MySQL Workbench to complete these
tasks with GUI tools (if time allows)