Relational Database MySQL

1
Relational Database - MySQL

• Our relational database management software is
  MySQL
• Name of our database ct366f08
• Entity-Relationship (ER) diagram represents the
  business, the organization, ..
• Tables are derived from the ER diagram

2
Relational Database - MySQL

• Each table has attributes (columns)
• The rows are the records (data)
• Each table has a primary key
• The value of the primary key cannot be null
• The value of the primary key uniquely determines
  the row/record

3
Relational Database - MySQL

• The primary key can be simple (one column) or
  composite (2 or more columns)
• Every attribute of the key is non-redundant you
  cannot take away an attribute of the key and
  still have a key

4
Relational Database - MySQL

• There can be connections between tables
• These connections are made through common
  columns/attributes, NOT through pointers or links
• Typically a foreign key/column in one table
  references a primary key/column in another
  table
• There can also be other constraints in a table
  (business/operational constraints)

5
Relational Database - MySQL

• Language is SQL
• Structured Query Language
• Software vendors include Oracle, Informix,
  DBase, SyBase, MySQL, SQLServer, ..

6
SQL

• Typical operations
• Operations on the structure of the database
  Create a table, drop a table, alter a table
• Operations on the data of one or more tables
  insert data, delete data, update data, read
  (select) data

7
SQL create a table

• create table login ( id varchar(10) not null,
  password varchar(10), age int, constraint
  login_pk primary key(id) )
• ? 3 columns id, password, age
• ? id and password are strings, max 10 characters,
  age is int
• ? id is the primary key

8
SQL delete, alter, desc

• desc login
• ? returns a description of the login table
• drop table login
• ? deletes table login from database
• alter table login add( address varchar( 30 ))
• ? adds a column named address, a string of 30
  characters max

9
SQL insert data

• Insert data into the login table
• insert into login values( mike, open, 30 )
• insert into login values( jane, sesame, 22 )
• insert into login (id) values( sarah )
• ? password and age for that record are null

10
SQL delete data

• delete data from the login table
• delete from login
• ? all the data in login is deleted table login
  still exists (is empty)
• delete from login where password sesame
• ? deletes all records whose password is sesame
• delete from login where id mike and password
  sesame
• ? can use compound condition in where clause

11
SQL update data

• update data of the login table
• update login set password open
• ? sets all the password fields in login to open
• update login set password sesame where id
  mike
• For the record whose id is mike (if there is
  one), the password field is changed to sesame

12
SQL Read data

• select data from the login table
• select from login
• ? selects all the rows and columns from login
• The result of a query is a table ? it can be
  reused in another sql query ? sql queries can be
  nested
• select id from login
• ? selects column id, all the rows, from login
• select id from login where password open
• ? selects the id column, rows whose password is
  open from login

13
SQL Multiple table queries

• We can query several tables within the same sql
  query
• Use nested queries
• Or
• Use joined tables

14
SQL Multiple table queries

• Nested queries
• Assuming table1 and table2 both have a column
  named column1
• select from table1 where column1 in (select
  column_name from table2 where )

15
SQL Multiple table queries

• Joined table queries
• Assuming table1 and table2 both have a column
  named column1
• select from table1, table2 where table1.column1
  table2.column1 and

16
SQL Multiple table queries

• Nested queries are more efficient than joined
  table queries