1 session 6: database best practice inet academy open source web development

1

Session 6: Database Best Practice

iNET Academy

Open Source Web Development

2

Objectives

Relational Databases Normalization and Forms of Normalization Backup and Restore Data Advanced SQL

3

Relational Databases

In relational systems, data is spread across several tables. The key is the relation between tables Decision of the primary key is very important in designing database. Foreign key is key (one or more than one field) that acts as the primary

key in one table. In other tables, they are normal columns, not the key.

4

Relationship Types

One-to-One Relationship Each item is related to one and only one other item.

5

Relationship Types (cont.)

One-to-Many Relationships: a key from one table appears multiple times in another table

6

Relationship Types (cont.)

Many-to-Many Relationships

7

Normalization

Goal: minimize the duplication of data

8

Forms of Normalization -First Normal Form Tables must not have repeating columns that contain the same kind of

data All column must containt only one value There must be a primary key that uniquely defines rows

9

First Normal Form (cont.)

The Address is splited into street, city, state, zip code

10

Second Normal Form

Tables must be in First Normal Form Columns that have repeating values across multiple rows must be

placed in separate tables and referenced by a key value in the original table

11

Second Normal Form (cont.)

12

Third Normal Form

Remove partial dependence, data that is not fully dependent on the primary key, but dependent on another value in the table.

13

Column Data Types

PHP isn’t strongly typed, but most databases are!

14

Backing Up & Restoring Data- Copying Database Files To backup, simple copy all files in the database’s directory. Shutdown

the database server before backing up To restore a database, copy all files into the database’s directory. Then,

restart the database server Note: File Backup & Restore must consider the problem of multiple

versions of database server. Backingup and Restoring on *nix systems must consider the file permissions.

15

Backing Up & Restoring Data –Using mysqldump Command Syntax: mysqldump –u user –p objects_to_backup Backingup a database: mysqldump –u root –p store >

my_backup_of_store.sql

16

Backing Up & Restoring Data –Using mysqldump Command (cont.) To backup a table

mysqldump –u root –p store authors > authors.sql To backup everything

mysqldump –u root –p - -all-databases > my_backup.sql To backup only the structure, not the data

mysqldump –u root –p - -no-data store > structure.sql To backup only the data

mysqldump –u root –p - -no-create-info store > data.sql

17

Backing Up & Restoring Data –Using mysqldump Command (cont.) Restoring a MySQL backup

Restoring a full backup

mysql –u root –p < my_backup.sql Restoring a database

mysql –u root –p –D store < my_backup.sql Working with other formats: CSV format

mysqldump –u root –p - -no-create-info - -tab=/home/jon - -fields-terminated-by=‘,’ store

Using mysqlimport command to import data from other formats

mysqlimport –u root –p - -fields-terminated-by=‘,’ store books.txt Backup best practice

Determine how often to backup based on How critical is your data How often it changes

Keep a copy of data in a separate location

18

Advanced SQL

Indexes Use to find the data as quick as possible Indexes are used when

In a WHERE clause: SELECT * FROM authors WHERE author=‘Ellen Sieve’; the author column should be indexed

In an ORDER BY clause: SELECT * FROM contacts ORDER BY author; the author column should be indexed

In MIN or MAX clauses How to create the index

CREATE UNIQUE INDEX authind ON authors (author);

19

Advanced SQL (cont.)

Multicolumn indexes: columns that are likely to be used in the WHERE clause should be indexed

LEFT JOIN Clause SELECT fields

FROM left_table

LEFT JOIN right_table ON left_table.field_id = right_table.field_id GROUP BY Clause

SELECT title, COUNT(author_id)

FROM books NATURAL JOIN authors

GROUP BY title

20


Using Database Functions String Functions:

Concatenation

Concatenation with a predefined separator

21


String functions Calculate a string length

Changing strings to upper- or lowercase

22


String Functions Trimming and padding strings

To trim spaces or tabs from a string, use LTRIM or RTRIM To trim spaces or a specified character, use TRIM function

TRIM(LEADING ‘X’ FROM string) TRIM(TRAILING ‘X’ FROM string)

23


String Functions String location and position

Cutting up strings LEFT RIGHT SUBSTRING

24


String Functions Search and Replace

25


Date and Time Functions

26



27



28



29



1 session 6: database best practice inet academy open source web development

Documents