TDC597 Linuxbased Network Management Lecture Nine mySQL

1
TDC597Linux-based Network ManagementLecture
Nine (mySQL)

• James T. Yu, Ph.D.
• jyu_at_cs.depaul.edu
• School of CTI
• DePaul University

2
Todays Outline

• Database Primer
• Introduction to MySQL
• Linux Environment for mySQL
• mySQL query commands
• Perl API for mySQL

3
Database Fundamentals

• Database
• Relational Database
• Tables
• Rows and Columns
• Structured Query Language (SQL)

4
Database Fundamentals

• Create and delete databases
• Create and delete user accounts
• Update user accounts
• Create and delete tables
• Update table attributes
• Create and delete data records
• Update data records

5
Application Development Process for
Telecommunications
Parsers
perl
Database components
CGI program
User interaction
6
Database and Database management system

• Database is simply a collection of data. In
  relational database, data is organized into
  tables (rows and columns)
• Rows data records
• Columns data attributes
• Database Management System (DBMS) is software to
  maintain and utilize the collections of data
  (Oracle, DB2, MySQL )

7
Objects of Database
Operations
Objects
Database
Creation
Tables
Columns (fields)
Deletion
Rows (records)
Modification
8
MySQL Introduction

• MySQL is a database management system.
• SQL stands for the Structured Query Language. It
  defines how to insert, retrieve, modify and
  delete data.
• Free from www.mysql.com and already included in
  major linux distributions.
• An tutorial of MySQL for Bioinfomatics
• http//www.bioinformatics.iastate.edu/BBSI/lectur
  e_notes_2006/BBSI06-B.Dong.ppt
• This lecture reuses many slides from this
  tutorial.

9
How MySQL Stores Data

• A MySQL server can store several databases
• Databases are stored as directories
• In linux distributions, it is usually at
  /var/lib/mysql
• Tables are stored as files inside each database
  (directory)
• For each table, it has three files
• table.FRM file containing information about the
  table structure effectively, an internal
  representation of the CREATE TABLE statement.
• table.MYD file containing the row data
• table.MYI containing any indexes belonging with
  this table, as well as some statistics about the
  table.

10
Access to MySQL

• Login the MySQL server installed on your machine.
  
• Syntax mysql database u ltusernamegt p
  password
• mysql tdc597s30 -u tdc597s30 -p
• Enter password passowrd
• Welcome to the MySQL monitor. Commands end with
  or \g. Your MySQL connection id is 23 to server
  version 3.23.41.
• Type 'help' or '\h' for help. Type '\c' to clear
  the buffer.
• mysqlgt This is the mysql prompt and you enter
  query here!

11
Access MySQL
12
Look around in MySQL
Create a database (make a directory) mysqlgt
create database ltdbNamegt Select database to use
(change your working directory) mysqlgt use
ltdbNamegt Database changed What tables are
currently stored in the MyDB database? mysqlgt
show tables
13
Creating Tables

• Syntax CREATE TABLE Table_Name (column
  specifications)

Create Table Student ( Serial integer
auto_increment primary key, / Serial Number
/ Account text NOT NULL,
/ Account / Password text
NOT NULL, / Used for
password / Course_ID text NOT NULL,
Course_Name text NOT NULL )
14
Display Tables
15
Adding New Fields into a Table
16
Insert Records

• Syntax INSERT INTO table_name SET
  col_name1value1, col_name2value2,
  col_name3value3,

Insert Into Student ( Serial, Account, Password,
Course_ID, Course_Name) values ( '1001',
'tdc597030', '123456789', 'tdc597', 'Advanced
Hands-on Data Networking')
17
Retrieve Record (MySQL Query)

• Syntax SELECT what columns to select
• FROM table or tables
• WHERE condition that data must satisfy

18
Update Records

• Syntax UPDATE table_name
• SET which columns to change
• WHERE which records to
  update

update table_name Set Password '123123123'
where Account 'tdc597002'
19
Delete Record

• Syntax DELETE FROM table_name WHERE which
  records to delete

delete from Student where Account'tdc597030'
20
Drop table (delete a table)

• Syntax DROP TABLE table_name
• mysqlgt drop table student
• Query OK, 0 rows affected (0.00 sec)
• Exit MySQL
• mysqgt quit

21
Perl API for MySQL
use DBI dbh DBI-gtconnect(connectionInfo,
userid, passwd) sql define the SQL
string sth dbh-gtprepare(sql) sth-gtexecut
e _at_row sth-gtfetchrow_array for SQL
query only dbh-gtdisconnect
22
MySQL API for Perl

• Connecting to MySQL

use DBI Database information my
db"tdc597s30" my host"localhost" my
userid"tdc597s30" my passwd"tdc597secret"
my connectionInfo"dbimysqldbhost"
Make a connection to database my dbh
DBI-gtconnect(connectionInfo, userid, passwd)
23
MySQL API for Perl (cont.)

• Execute a query (one one item)

sql "select from DBTable where issueID
iid sth dbh-gtprepare(sql) sth-gtexecute
_at_row sth-gtfetchrow_array originator
row1 origEmail row2 owner
row3 dbh-gtdisconnect
24
SQL Query (multiple items)
sql "select from DBTable" sth
dbh-gtprepare(sql) sth-gtexecute while (_at_row
sth-gtfetchrow_array) iid
row0
25
Data Insertion to a Table
sql "INSERT INTO Action ( issueID ,
originator , origEmail , owner ,
ownerEmail , description ,
type , URL ) VALUES (
NULL , '".FORM'originator'
."', '".FORM'origEmail' ."',
'".FORM'owner' ."',
'".FORM'ownerEmail' ."',
'".FORM'description'."',
'".FORM'type' ."', '".FORM'URL'
."')"
26
Data Modification
sql "UPDATE Action SET
originator '".FORM'originator'."',
origEmail '".FORM'origEmail'."',
owner '".FORM'owner'."',
ownerEmail '".FORM'ownerEmail'.
"', description
'".FORM'description'."',
type '".FORM'type'."',
URL
'".FORM'URL'."' WHERE issueID
".FORM'iid'
27
Data Deletion
sql "DELETE from Action where issueID
".FORM'iid'
SQL Execution
sth dbh-gtprepare(sql) sth-gtexecute dbh-gtd
isconnect
28
Case Study Lab Action Item

• Tracking Lab Action Item
• http//140.192.40.48008/jyu/labAction/ShowData.c
  gi
• Create tables (createTable.pl)
• Know how to delete tables before you create them
  (dropTable.pl)
• Reports for showing the data (ShowData.cgi)
• Forms for data entry and modification
  (DataForm.cgi)
• Database Action Insertion, Deletion, and
  Modification
• DataAction.cgi

29
More

• Database Design
• Multiple tables
• Keep data in a single table or multiple tables?
• What is the rule? Have you heard the Data
  Normal Forms?
• Join
• What is a join?
• Should I do join in MySQL or in Perl?
• How do I do join in Perl?
• Aggregation
• What is data aggregation?
• Should I do data aggregation in MySQL or in Perl?
• No right or wrong answer
• What is your preference?

30
Extra Credit (in Final Project)

• Enhance hw04 - user registration component
• Save the data in mySQL (instead of in a text
  file)
• Design a web form to allow users to change
  submitted information.
• Only the user himself/herself can change his/her
  own data.
• Design a report for the instructor (instructor
  password instructor) to see submitted
  information
• No duplication data (same e-mail) is allowed in
  the mySQL database.
• Each student has his/her own database
• Database name tdc597sxx
• where xx is your own account ID, same as your
  Linux account
• Your database account tdc597sxx
• Your password tdc597-ddddd
• Where ddddd is the last five digits of your
  student ID
• Do not change the password.
• Five extra points for the final project

31
Final Exam

• Take Home Exam
• posted on DLWeb at 1159pm 08/17 (Friday)
• Due on 1159pm, 08/19 (Sunday)
• Submit the final exam in a SINGLE WORD (or PDF)
  file to the DLWeb site
• 20 penalty for ZIP file or any other format
• Penalty for late submission
• 20 penalty for late by one second
• 50 penalty for late submission after 1200noon,
  08/20
• No acceptance after 24 hours
• Violation of academic integrity
• Minimum penalty (for very minor offense) 0 for
  the final exam
• Other offense automatic F for the course
• A formal report submitted to the deans office
• It applies to both parties the person who
  offers the answer and the person who copies the
  answer receive exactly the same penalty.

32
Final Exam Notes

• No answers to ANY questions during the exam
  period
• If you see any error, correct it.
• It the question is not clear, make your own
  assumption.
• Exam questions
• Linux command and syntax (10)
• Program Trace (20-35)
• Program Debug (20-25)
• Syntax error
• Logic error
• Program Development (30-40)
• You will need to use your Linux04 account to some
  questions.
• You can contact me if the machine (linux04) is
  not accessible.

33
Final Exam Part I

• Linux command
• Provide a short description for a given linux
  command.
• Give an example of its syntax
• Question ls
• Description list directory contents
• Syntax example ls -l

34
II. Program Trace

• What is the difference between
• date "HMS vs date HmS

What is the out when a user enter End ?
print "What is this? "x ltSTDINgtif (x
"end") print "small ending\n" elsif (x
"END") print "BIG ENDING\n" else
print "no ending\n"
35
III Correct Syntax Error
n ltSTDINgt get input (a number) from
terminal for (i1 iltn i) sum
i print "for-loop sum of 1 to n sum \n"
36
IV Correct Logic Error
Input Data namestudentIDgrade open(DATA, lt
student.data) while (linebuf ltDATAgt)
chomp(linebuf) _at_myArr split(//,
linebud) name myArr1 id
myArr2 grade myArr3 close (DATA)
37
V. Program Development

• Write a Perl program to check the validity of an
  IP address
• Write a Perl program to check the validity of a
  MAC address
• Write a Perl program to check the validity of
  date
• mm/dd/yy m/d/y m/d/yyyy mm/dd/yyyy

38
Course Summary
39
My Schedules

• Attending a conference from 08/18 to 08/27
• While I am at the conference, I will check my
  e-mail once or twice a day.
• Final project due 1159pm, 08/26
• No extension will be considered for ANY reason.
• Final grade will be posted by noon, 08/28