DataBases
MySQL databases
DataBase course notes `9-2
all databases store chunks of information
most efficient way of storing those chunks
is to group them into similar chunks that
can all be stored in a similar way
DataBase course notes `9-2
Data Types
many data types in programming
languages, probably will only need a
cursory examination
numeric
character
date
other types
DataBase course notes `10-2
Numeric Types
used to store numbers
specific functions, arithmetic, that you can
perform on numbers and on most numeric
data types
DataBase course notes `10-2
Integers
exact whole number
columnname INT
TINYINT
128 +127, 1 byte
SMALLINT
32 thousand +32 thousand, 2 bytes
MEDIUMINT
8.3 million ... +8.3 million,3 bytes
INT
2 billion (1012) +2 billion, 4 bytes
BIGINT
9 trillion (1018) +9 trillion, 8 bytes
DataBase course notes `10-2
UNSIGNED
will only store numbers as positive integers
range then goes from zero through to double the
values
columnname INT UNSIGNED
fine for using integers as unique IDs for rows
but if you start doing arithmetic with signed and
unsigned data types it can lead to problems
DataBase course notes `10-2
BOOL
one that can have two states, true or false, on or off, 0 or
1
actually is the same as using TINYINT(1) data type
it is best just to use TINYINT, and then within your code
determine what number you need to store for on or off, 0
or 1
if you are transferring your MySQL tables to another
system that implements BOOL (other data types) in a
different way, the scripts that you need to write to export
the data will need to create the actions necessary to
reflect that difference
Representation: 1 byte; considered as being synonim
with tinyint
7
DECIMAL
used for storing numbers that are not whole numbers,
where the numbers after the decimal point are important,
especially currency
columnname DECIMAL(precision, decimals)
precision
number of digits that are needed to store the complete number
if you are storing negative numbers the minus sign is included in
these digits
decimals
number of digits to store after the decimal point
decimal number stored can be different from inserted
the internal representation varies as a function of the
MySQL version
8
Floating Point Numbers
data types that have previously been
mentioned are for storing exact values
floating point numbers enable you to store
a very wide range of numbers but with
some levels of inaccuracy
the bigger the number gets, the less detail is
stored about it
FLOAT
columnname FLOAT(precision)
precision
number of digits that are to be stored
columnname FLOAT(magnitude, decimals)
magnitude
number of digits used to store the number
decimals
number of decimal digits to be stored
forced to be of the single precision type
Representation: 4 bytes
10
DOUBLE
works in exactly the same way as the
FLOAT declaration
columnname DOUBLE (magnitude, decimals)
number stored will obviously be of the
double precision type
Representation: 8 bytes
11
CHAR
columnname CHAR(length)
length
width of the string in characters
can be between 0 and 253
once you declare a column with CHAR
value of fixed length, all of the strings that
are stored in that column become that
length
12
VARCHAR
columnname VARCHAR(length)
Length
maximum width of the string in characters
can be between 0 and 253
13
Fixed or Variable Length?
variable length data types make it easier for storing and
searching
if you are searching for the word Hello! in a CHAR(10)
column, nothing would be found, instead you have to
search for Hello!
fixed length types have the advantage that they are
faster to search
Phone number (characters no arithmetic performed)
CHAR they are fixed length
Names VARCHAR they are variable length
14
TEXT
storing things like comments, reviews, or other large
blocks of text
columnname TEXT
TINYTEXT
maximum length of string in characters is 255
TEXT
maximum length of string in characters is 65,535
MEDIUMTEXT
maximum length of string in characters is 16,777,215
LONGTEXT
maximum length of string in characters is 4,294,967,295
15
BLOB
Binary Large OBject used for storing data
could store jpeg image within a BLOB column
conform to same size constraints as TEXT columns
mentioned previously but are called TINYBLOB, BLOB,
MEDIUMBLOB and LONGBLOB
when you are searching or ordering it BLOB will be case
sensitive and TEXT will not be
both BLOB and TEXT objects are really pointers to areas of storage
on server, and so are not physically stored within table
explains why operations on this type of column are
slower than when searching normal column types
16
DATETIME
allows you to store the date and the time in one column
columnname DATETIME
details how to insert values into this column type ?
when you retrieve DATETIME value, MySQL returns
string of format YYYYMM-DD HH:MM:SS
if you only insert date part the HH:MM:SS are set by
default to [Link]
if you insert date that is invalid stores this date as 000000-00 [Link] to show that there has been an error
can store values for all dates and times between the
year 1000 and the year 9999
17
TIMESTAMP
works in a similar way to DATETIME
columnname TIMESTAMP(length)
has added functionality that it will automatically update
itself under certain conditions
whenever you create new row, or change contents of row, and
do not explicitly change contents of the first TIMESTAMP column
in that row, this column will be automatically updated with the
date and time of the change
if, however, you explicitly set the value of the column when you
are altering or creating row, the TIMESTAMP column will not
auto-update because you have specified what you want to go in
it manually
18
DATE
TIME
store date but do not
need to store time
columnname DATE
store time value or
period
columnname TIME
19
Other Types
SET
A SET - a string object that can have zero or more values, each
of which must be chosen from a list of permitted values specified
when the table is created.
SET column values that consist of multiple set members specified with members separated by commas (,) => SET
member values should not themselves contain commas.
For example, a column specified as SET('one', 'two') NOT NULL can have
any of these values: 'one' 'two' 'one,two'
ENUM
ENUM is a string object with a value chosen from a list of
permitted values that are enumerated explicitly in the column
specification at table creation time.
20
An enumeration value must be a quoted string literal; it may not be
an expression, even one that evaluates to a string value. For
example, you can create a table with an ENUM column like this:
CREATE TABLE sizes ( name ENUM('small', 'medium', 'large') );
DataBase course notes 2
21
most difficult data type: date @ time
different database - different functions
implemented to deal with date & time
other database can have no SET or
ENUM data types
22
Data Types
standard numbers, characters, text, blob
pretty much the same in all databases
date & time
exist in all databases
different representations, different functions to
deal with difficult to export - import
non standard
difficult or impossible to export - import
23
Designing and Creating
Tables
24
Redundant Data
database server can only work well if it is
processing efficiently organized data
well-designed tables that are quick to process
improve database performance by eliminating
redundant data
data that is repeated unnecessarily within a
table
data is classed as redundant if it can be
removed from table without loss of information
25
website log
26
website log
every column has data in it that is repeated
webpage column has only three unique pages listed in it,
and the [Link] page is repeated in this column four
times
browser column, not only is the Mozilla entry repeated
three times it is also storing a lot of text for each row
although lots of data is repeated, it is not necessarily all
redundant
27
to stop repeatedly storing redundant data,
we need to move the text description of
the webpage into another table, and find
some way of relating the two entries to
each other
28
WebPage table
29
Website log with reduced
redundant data
30
Reducing Redundant Data
you may think that with the size and speed
of modern hard drives there is little point
worrying about storing too much text for
each row
to an extent that is right, however it is not
just the size of the database that may
become an issue
31
Good Business
Redundant data
Closer data
Clients (Client, Country, Discount,
Clients (Client, Country ID, )
Countries (Country)
Discounts (Discount, Country ID)
Discount = f(Country)
big command from new
client from country
loose some (small) time
to find discount
OK loose some time,
cannot loose command
Discount = f(Country)
big command from new
client from country
cannot find consistent
discount
Danger can gain some
time but can loose
command
32
Primary Key
table which stores information about webpage
contains the name of the page, as well as the
title, but it also contains a row of unique
numbers, the ID column, known as the primary
key
each row in the webpage table can be identified
by using its (primary) key
website log table changed the first webpage
column
33
Foreign Keys
instead of inserting a string to determine the
webpage, we have inserted the primary key from
the webpage table
known as foreign key
by using foreign key to look up row in webpage
table, we can get back more information about
the row we are examining in the website table
able to recreate all information, without storing
anything unnecessarily
34
Counties
have naturally short key,
abbreviation
CA California
CJ Cluj
Countries
Natural key, name, but
long
prefer to insert a
surrogate short key ID
integer
01 USA
40 Romania
35
still has redundant data
cookie table to store host information that
we get from the site visitor
36
Cookie table
37
Weblog with even less
redundant data
38
Redundant or Not?
in cookie column, the first line and the third line contain the cookieID
of 1
If we were to remove this value, as it is repeated, we would lose the
link to the cookie table, and thus lose some information about the
page view
when foreign key is repeated within a column, it usually does not
signify redundant data
there is no redundant data within the cookieID column
still has some redundant data in it, as there are still repetitions in the
referring page column
if no other information about the referring page needs to be stored,
we will leave this data as it is and not store it in another table
39
1 year math teacher
told you that you cannot
add apples and pears
2 year database teacher
do not mix apples and
pears in the same table
40
Referential Integrity
remove redundant data
relationships between tables using primary and
foreign keys
alter or delete records in related tables you can
come across problems with referential integrity
if we were to remove row 2 from weblog table,
this would be allowable as no other table in our
example makes reference to this row; referential
integrity of data is maintained
41
Referential Integrity
if we were to remove row 2 from cookie table we have a
problem; lose some information about Mozilla client
row 2 of weblog table still refers to row 2 of cookie table
which we have just removed
if we tried to join the two tables together to get back all of
the information stored between them, the system would
not be able to find the reference to the Mozilla cookie, so
the join would fail
removal of row 2 from cookie table has compromised the
referential integrity of the rest of the database
only way that we could remove row 2 of cookie table is if we removed
every row that contained a reference to it in all related tables
42
Referential Integrity
most DBMS will try to maintain and enforce
referential integrity
but this will only work if you have correctly told
the system where the relationships between
tables are
possible to enforce referential integrity in your
code, and not with the database
will only work if your code works
much better to let the DBMS enforce the rules
43
MySQL will only enforce referential
integrity if you are using InnoDB tables
default table on most installations will be of
the MyISAM type
this type does not enforce referential integrity
ISAM : Index Sequential Access Method =>
fast retrieval
44
NULL
placed in column when there is no value stored in that
column
special symbol which signifies that no value has been
inserted yet
is not like empty string or 0, because empty string is a
string with no contents
NULL is the absence of content
cannot match NULL in comparisons
three value logic: true, false, NULL
same NULL on all data types
45
NULL
when creating table, you can specify that
column cannot contain a NULL by using
NOT NULL
if you are specifying column that is foreign
key, NOT NULL implies that row must
have relationship with row in another table
primary key column would normally also
be specified as NOT NULL, as each value
in this column must be unique
46
CREATE DATABASE; USE
database is the container in which we store the tables
CREATE DATABASE databasename
once you have created the database you have to
indicate to MySQL which database you will be using
USE databasename
creating the database with graphical client (click, enter name) is
exactly the same as issuing CREATE DATABASE command
selecting database from dropdown list in graphical client is exactly
the same as issuing USE database command
47
CREATE TABLE
CREATE TABLE tablename (columnname
type options,
columnname type options,
otheroptions)
tablename, columnname are self-evident
type is the data type of the column as we
described
48
CREATE TABLE options
NOT NULL
prohibits the use of NULL within the column
DEFAULT
followed by a value allows to set default value
that column will be created with if not
specified when inserting data
49
CREATE TABLE options
AUTO_INCREMENT (MySQL only other
database have others syntax, others
(similar) mechanism to implement same
function)
will add value to value of this column in the
previously added row, and insert new value
into this row automatically
normally defaults to an increment of 1, and is
useful for automatically creating new, unique
primary key value
50
CREATE TABLE webpage
CREATE TABLE webpage (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
Content TEXT,
Title VARCHAR(50),
PRIMARY KEY (id));
running query above creates table, but does not give you
any other feedback unless you have typed something
wrong
to check that table is correctly created, run following:
DESCRIBE webpage
51
CREATE TABLE website log
CREATE TABLE Website_Log (
ID MEDIUMINT NOT NULL AUTO_INCREMENT
PRIMARY KEY,
CookieID MEDIUMINT,
WebpageID MEDIUMINT,
Browser VARCHAR(50),
DateCreated DATETIME,
IPNumber VARCHAR(15),
ReferringPage VARCHAR(255) )
52
Identifying Foreign Keys
insert following into our create table definition:
FOREIGN KEY (column) REFERENCES parenttable
(primarycolumn)
column
name of column that contains the foreign key in this table
primarycolumn
name of primary key column that this foreign key references
parenttable
name of table where the column resides
53
CREATE TABLE website log
CREATE TABLE Website_Log (
ID MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
CookieID MEDIUMINT,
WebpageID MEDIUMINT,
Browser VARCHAR(50),
DateCreated DATETIME,
IPNumber VARCHAR(15),
ReferringPage VARCHAR(255),
FOREIGN KEY (WebpageID ) REFERENCES Webpage (ID),
FOREIGN KEY (CookieID ) REFERENCES Cookies (CookieID)
)
TYPE = InnoDB
54
Create from Select Statement
CREATE TABLE tablename AS
selectstatement
SELECT statement allows you to select
some or all of column and rows in a table
CREATE TABLE Log2 AS
SELECT ID, CookieID, WebpageID,
DateCreated, Referringpage FROM
Website_log
55
CREATE TABLE LIKE
CREATE TABLE destination LIKE source
will create new destination table that is
exactly based on source tables structure
CREATE TABLE log3 LIKE log
56
