Transcript Data Definition

Data Definition
After this lecture, you should be able to:
 Use SQL DDL Statement to define a database
structure.


Work with mysql or phpMyAdmin to access
your MySQL databases.
Complete Assignment 2 (Part II - a, b).
Data Definition
1
SQL DDL Statements







Create Table
create table s (sno char(5), ... );
Drop Table
drop table s;
Alter Table
alter table s add phone char(13);
Create View
create view SV as select ....
Drop View
drop view SV;
Create Index
create index X on s(sno);
Drop Index
drop index X;
Data Definition
2
Suppliers-Parts Database: E-R Diagram
sno
(key)
sname
S
status
Data Definition
pno
(key)
M
city
M
SP
qty
pname
P
color
city
weight
3
Supplier-Part Database
Table S
sno | sname | status | city
-----------------------------s1 | Smith | 20
| London
s2 | Jones | 10
| Paris
s3 | Blake | 30
| Paris
s4 | Clark | 20
| London
s5 | Adams | 30
| Athens
Table P
pno | pname | color | weight | city
---------------------------------p1 | nut
| red
|
12
| London
p2 | bolt | green |
17
| Paris
p3 | screw | blue |
17
| Rome
p4 | screw | red
|
14
| London
p5 | cam
| blue |
12
| Paris
p6 | cog
| red
|
19
| London
Table SP
sno | pno | qty
--------------s1 | p1 | 300
s1 | p2 | 200
s1 | p3 | 400
s1 | p4 | 200
s1 | p5 | 100
s1 | p6 | 100
s2 | p1 | 300
s2 | p2 | 400
s3 | p2 | 200
s4 | p2 | 200
s4 | p4 | 300
s4 | p5 | 400
Data Definition
4
Create Table Statements
create table s (
sno
char(5) not null,
sname char(20) not null,
status smallint,
city
char(15),
primary key (sno)
);
create table p (
pno
char(6) not null,
pname char(20) not null,
color char(6),
weight smallint,
city
char(15),
primary key (pno)
);
create table sp (
sno
char(5) not null,
pno
char(6) not null,
qty
integer not null,
primary key (sno, pno)
);
Data Definition
5
Data Types







integer
The magnitude range is -2,147,484,648 ... +2,147,484,647.
real
For fixed or floating-point numbers.
Allowable values are -3.402823466E+38 to -1.175494351E-38, 0,
and 1.175494351E-38 to 3.402823466E+38.
char
For fixed length character strings up to 255 bytes.
varchar
For variable-length character strings.
The maximum length is 65,532 bytes.
bit[n]
n indicates the number of bits, from 1 to 64. The default is 1 if n is
omitted .
date
 Year values in the range 70-99 are converted to 1970-1999.
 Year values in the range 00-69 are converted to 2000-2069.
datetime
Date and time. The format is ‘YYYY-MM-DD HH:MM:SS’.
Data Definition
6
Creating S-P-SP Database
% mysql –h mysql.cs.orst.edu –u pham –p pham
Enter password:
mysql> \.
Query OK,
Query OK,
Query OK,
create_s_p_sp.sql;
0 rows affected (0.01 sec)
0 rows affected (0.01 sec)
0 rows affected (0.01 sec)
SQL> show tables;
TABLE_in_pham
----------------p
s
sp
Data Definition
7
Looking at Table Structures
Data Definition
mysql> desc s;
Name
Null?
----------------SNO
NOT NULL
SNAME
NOT NULL
STATUS
CITY
Type
---CHAR(5)
CHAR(20)
SMALLINT(6)
CHAR(15)
mysql> desc p;
Name
Null?
----------------PNO
NOT NULL
PNAME
NOT NULL
COLOR
WEIGHT
CITY
Type
---CHAR(6)
CHAR(20)
CHAR(6)
SMALLINT(6)
CHAR(15)
8
Adding Data to a Table
insert into s
values('s1', 'Smith',
insert into s
values('s2', 'Jones',
insert into s
values('s3', 'Blake',
insert into s
values('s4', 'Clark',
insert into s
values('s5', 'Adams',
mysql> \.
Query OK,
Query OK,
Query OK,
.
Data Definition
20, 'London');
10, 'Paris');
30, 'Paris');
20, 'London');
30, 'Athens');
insert_s_p_sp.sql
0 rows affected (0.01 sec)
0 rows affected (0.01 sec)
0 rows affected (0.01 sec)
.
.
9
Showing Table Contents
SQL> select * from s;
SNO
SNAME
STATUS
---------- -------s1
Smith
20
s2
Jones
10
s3
Blake
30
s4
Clark
20
s5
Adams
30
CITY
-----London
Paris
Paris
London
Athens
SQL> select * from p;
PNO
PNAME
COLOR
----- ------- ------p1
nut
red
p2
bolt
green
p3
screw
blue
p4
screw
red
p5
cam
blue
p6
cog
red
WEIGHT
-------12
17
17
14
12
19
Data Definition
CITY
------London
Paris
Rome
London
Paris
London
10
Sailors-Boats Database:
E-R Diagram (Schema)
sid
(key)
M
Sailor
rating
Data Definition
bid
(key)
sname
age
M
Reserves
date
bname
Boat
color
11
Create Table Statements
create table sailors (
sid
integer
not null,
sname char(20) not null,
rating smallint,
age
real,
primary key (sid)
);
create table boats (
bid
integer not null,
bname char(20) not null,
color char(10),
primary key (bid)
);
create table reserves (
sid
integer not null,
bid
integer not null,
day
date
not null,
primary key (sid, bid, day)
);
Data Definition
12
Sailors-Boats Database: Tables
Sailors
sid
sname
rating
age
22
Dustin
7
45.0
33
Libber
8
55.5
Boats
bid
bname
color
2
Yuppy
Blue
3
Lubber
Red
Reserves
Data Definition
sid
bid
day
22
2
10/10/96
22
3
10/09/96
33
2
11/12/96
13