SQL and My SQL Relational database and SQL

SQL and My. SQL Relational database and SQL My. SQL LAMP SQL queries

Assignment 2 SQL and a My. SQL Tutorial • Web database applications (Assignment 1, part II -- 5 reviews) • Three-Tier Architectures – Client tier: web browser software that interact with the applications – Middle tier: application logic and communicate with db tier and client tier – Database tier: database management system supports your data operation

Assignment 2: Using My. SQL to issue SQL Queries • Relational database • SQL queries: language to communicate with DBMS • A My. SQL Tutorial – create db and tables – insert data – delete data – select data

Relational Database and SQL • A particular kind of DBMS that is very good at relating information stored in one table to another –> to pull data from those tables and to join info from related tables to produce answers to the questions that can’t be answered from individual tables alone. • SQL (Structured Query Language) standard database language.

My. SQL • Relational database system • Client/server architecture (101 simultaneous connections) • SQL (table size for Linux = 4 GB) • Programming languages: C, C++, Java, Perl, PHP, Python, and Tcl • Platform independence, security, and speed

LAMP • Linux (operating system) • Apache (web server system) • My. SQL (database system) • Perl/PHP (programming language – PHP for us) LAMP is an open source dream team LAMP is supported here by: athena

PHP • Pure HTML static web site • PHP enable you to build dynamic web site • A server-side scripting language designed specifically for the web • Conceived in 1994 by Rasmus Lerdorf as oneman project then adapted by many • PHP (Personal Hypertext Preprocessor)

SQL Queries • When you use My. SQL, you are using 2 programs(client/server): – Database server is a program located on the machine where your data are stored. It listens for client requests coming from network – Clients are programs that connect to the database server and issue queries to tell it what info they want

Benefits of Client/Server • The server provides concurrency control so that two users cannot modify the same record at the same time • You don’t have to be logged in on the machine where your database is located

A My. SQL Tutorial • An excellent My. SQL Tutorial in Chapter 1 of the following book: “My. SQL™: The definitive guide to using, programming, and administering My. SQL 4. 1 and 5. 0”, Third Edition by Paul Du. Bois (Safari Tech Books Online ) • Establishing and terminating connection and issue SQL queries

Applications Examples of My. SQL • UCSC Human Genome Project Working Draft • LOCal: A Flexible Web-Based Microscope Reservation System – Univ. of Wisconsin, Madison • You are going to find out more fine examples ….

Assignment 2: change password for My. SQL • Following the instruction in Assignment 2, change your password in your My. SQL account: – The assigned Athena user name 122 xxx is for both web server (Apatche) and database server (My. SQL) – The assigned password needs to be changed separately, once for web and once for db – you may use different passwords or keep them same – Do it now …

Speedy My. SQL Connection • Create a option file named. my. cnf in your home directory with content – this will save your time to log into My. SQL every time: • [client] • User = your-mysql-username • Password = your-password – After creating the above file, chmod 600. my. cnf • Familiar yourself with My. SQL by using a tutorial and you are ready to go

Database Building Basics Create a table in My. SQL: Method 1 – create it in My. SQL – easy to make mistakes Method 2 – create/update in your favorite editor (linux or widow) first and then run mysql in Batch mode. Example: Edit mysql command to create a table “student” and save it as a file, “create_student. sql”. At linux prompt type the follow will create a table student in database webdb. mysql webdb < create_student. sql

Database Design Issues • Understanding the process and relations • Objects tables, attributes columns, data types, relations index, primary keys • Our database: 1 table • student

The First Normal Form • Columns with similar content must be eliminated • A table must be created for each group of associated data • Each data must be identifiable by means of a primary key (unique index)

Second Normal Form • Whenever the contents of columns repeat themselves, this means that the table must be divided into several subtables • These table must be linked by foreign keys (cross reference)

Third Normal Form • Columns that are not directly related to the primary key must be eliminated A good example of normalizing a database