MySQL Database Design

Access to MySQL, Structured query language (SQL)

An Brief Introduction to MySQL Database

MySQL is a free, efficient and widely used database system that implements structured query language, i.e., SQL. It supports various platforms such as Linux, MAC OS X, Windows and more. You will learn how to access MySQL database and how to write and run SQL on MySQL in this lab.

Access MySQL Database via Hercules

You can logging into MySQL database by the following commands:

a049510$ ssh hercules

hercules[1]% mysql -p your_hercules_username 
Enter password:
mysql> use database_name;
  

You must log into hercules before logging into MySQL database system. The first ssh command requires your hercules account name and password. The second mysql command requires the mysql account name and password, i.e., the one you just created. You need to select a database before run your SQL commands. You can use the following MySQL command to display all existing databases:

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| temp1              |
+--------------------+

In this lab, you use the one labeled with your MySQL-account name as your database. For example, if your account name is temp1, then you can enter use temp1;. You are able to create a database for your class projects, for example classprojects, and a database for your lab practices, for example, labassignments. The following link provides details of how to create a new database under your account.

Create database: http://www.w3schools.com/sql/sql_create_db.asp

Notes:

  • Your SQL commands should end with a semi-colon (;).
  • You do not need to create a database for the labs, since a database is already created for you to use. Your database's name is your username.

Structured Query Language (SQL)

SQL, stands for structured query language. It is a standard query language for popular relational databases such MySQL, SQL Server and Oracle. Although diffrerent relational databases implement SQL with their own extentions and properties, they share the standard on commonly used operations, namely, data definition language (DDL) and data manipulation language (DML). DML commands can be used for querying and updating data. DDL commands create or delete a table in database.

Data Definition Language (DDL)

The following introduces SQL commands: create table, drop table and describe (desc) table_name.

create table
creates a new table in your database
CREATE TABLE user (
	user_id 	    INT 		NOT NULL AUTO_INCREMENT,
	user_email 	    VARCHAR(255) 	NOT NULL,
	user_password       VARCHAR(255)        NOT NULL,
	user_name 	    VARCHAR(255),
	user_DofB	    VARCHAR(255),
	user_PicName	    VARCHAR(255),
	PRIMARY KEY     (user_id)
	);
create table post (
	post_id 	INT 			NOT NULL AUTO_INCREMENT,
	user_id 	INT 			NOT NULL,
	content 	varchar(255)	       NOT NULL,
	time 		TIMESTAMP 		NOT NULL,
	url 		VARCHAR(255),
	PRIMARY KEY 	(post_id),
	FOREIGN KEY 	(user_id) REFERENCES user (user_id)
	);

The DATE, DATETIME, and TIMESTAMP Types

drop table
destroys a table from your database
desc table_name
describes the sturture of a database table, including field names, default values and other properties

The following links provide detials of these SQL commands.

Create a table: http://www.w3schools.com/sql/sql_create_table.asp

Destroy a table: http://www.w3schools.com/sql/sql_drop.asp

Describe a table: http://dev.mysql.com/doc/refman/4.1/en/describe.html

Data Manipulation Language (DML)

The following introduces SQL commands: select, insert into, update and delete.

select
extracts data from a database
insert into
inserts new data into a database
update
updates data in a database
delete
deletes data from a database

The following links provide detials of these SQL commands.

Select: http://www.w3schools.com/sql/sql_select.asp

Insert into: http://www.w3schools.com/sql/sql_insert.asp

Update: http://www.w3schools.com/sql/sql_update.asp

Delete: http://www.w3schools.com/sql/sql_delete.asp

In Class Exercise Lab Assignment

This lab assignment contains 4 steps.

Keep all your sql commands in a log file, Yourusername.txt, link it on your index.html and upload it to URcourses by 11:55 PM.

Step1: Load the students table by using the source command

Download this SQL script file, i.e., students.sql and upload the file into your public_html directory.

Make sure the file permission for students.sql is 644.

If you are in hercules account, you can use the following command:

cp -p /net/data/ftp/pub/class/215/basic_sql/students.sql ~/public_html/

Log into your MySQL and execute the following MySQL command:


mysql> source students.sql

The source command creates the following students table as well as the data.

mysql> describe students;

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| student_ID         | varchar(30)  | NO   |	 | NULL    |	   |
| first_Name         | varchar(30)  | NO   | 	 | NULL    |	   |
| middle_Name        | varchar(30)  | YES  |	 | NULL	   | 	   |
| last_Name          | varchar(30)  | NO   | 	 | NULL    |	   |
| email              | varchar(100) | NO   |	 | NULL	   | 	   |
| course_Name        | varchar(30)  | NO   | 	 | NULL    |	   |
| course_Description | varchar(255) | NO   |	 | NULL	   |	   |
| marks              | real         | NO   | 	 | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

Step2: Fix incorrect data

The table you created may contain errors or inconsistancy information due to various user inputs, use SQL update and delete commands to fix these problems.

  1. A student, Ahmed Abbad, has a problem with his email address. Find the problem and use update command to fix it.
  2. Yupeng Xu doesn't have a middle name, but the system put his last name into the middle name column. Try to fix the problem for him.
  3. The course description of CS215 should be Web and Database Programming, but some records use a different description for CS215, find it and fix it.
  4. A student, Tao Ke, is taking a course "CS000" that does not exist. Use delete command to remove this record.

Note: keep the commands (e.g., update, delete, etc.) as part of the solution for the exercises.

Step3: Decompose the students table into three new tables

Decompose students table into three sub tables according to the following description:

  1. Create a table called Student_Info contains five attributes, student ID, first_name, middle_name, last_name and email address.
  2. Create a table called Courses contains two attributes, course_Name and course_Description.
  3. Create a third table called Marks table contains student_ID, course_Name and marks.

Step4: Extract records from students table and insert into new tables

Populate the three new tables. They are Student_Info, Courses and Marks.

Show all records of each new table.

The following SQL command is a combination of insert and select commands. You can use this command to extract data from old students table and insert into your new tables.

insert into table2 (column_name_1,column_name2,...column_name_m) select 
distinct column_name_1, column_name_2,...column_name_m from table1; 

For example:

insert into Student_Info (student_id,first_name,middle_name,last_name, email)
select distinct student_ID,first_name,middle_name, last_name,email from students;

The keyword distinct is optional and it helps to retrieve non-duplicated database records.