Introduction to Linux

Download Report

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
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
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:
MySQL Workbench (GUI tools):
MySQL database connectors (future):
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,
Run queries to view data, sort data, etc.
Add primary keys to the tables (student2.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)