chapter 6 database updated

8/8/2019 Chapter 6 Database Updated

1/46

1

Chapter 6

Database


2/46

2

OBJECTIVES

In this chapter you will learn:

Relational database concepts.

To use Structured Query Language(SQL) to retrieve data from and

manipulate data in a database. To install and configure MySQL.

To create a MySQL database.


3/46

3

Introduction A database is an integrated collection of data. A

database management system (DBMS) providesmechanisms for storing, organizing, retrieving andmodifying data.

Todays most popular database management systemsare relational database systems.

SQL is the international standard language used

almost universally with relational database systemsto perform queries and manipulate data.

Programs connect to, and interact with, relationaldatabases systems via an interfacesoftware thatfacilitates communications between a database

management system and a program.


4/46

4

Relational Data A relational database stores data in tables. Tables are

composed of rows, and rows are composed of

columns in which values are stored. A primary key is provides unique values that cannot

be duplicated in other rows of the same table.

Each column of a table represents a differentattribute in a row of data.

The primary key can be composed of more than onecolumn.

SQL provides a rich set of language constructs thatenable you to define complex queries to retrieve datafrom a database.


5/46

5

Relational Data (Cont.)

Every column in a primary key must have a value,and the value of the primary key must be unique.This is known as the Rule of Entity Integrity.

A one-to-many relationship between tables indicatesthat a row in one table can have many related rowsin a separate table.

A foreign key is a column in a table that matches theprimary-key column in another table.

The foreign key helps maintain the Rule ofReferential Integrity: Every foreign-key value mustappear as another tables primary-key value. Foreignkeys can be used to combine information frommultiple tables. There is a one-to-many relationshipbetween a primary key and its corresponding foreign

key.


6/46

6

Employee table sample data.


7/46

7

Result of selecting distinct Department and Location

data from table Employee.


8/46

8

Relational Database Overview: A

books Database

Foreign keys also allow related data inmultiple tables to be selected from thosetables for analytic purposesthis isknown as joining the data.


9/46

9

Column Description

authorID Authors ID number in the database. In the booksdatabase, thisinteger column is defined as autoincrementedfor each row

inserted in this table, theauthorID

value is increased by 1automatically to ensure that each row has a unique authorID.This column represents the tables primary key.

firstName

Authors first name (a string).lastName Authors lastname (a string).

authors table from the books database.


10/46

10

authorID firstName lastName

1 Har ey Deitel

2 Paul Deitel

3 Andrew Goldberg

4 Da id Choffnes

Sample data from the authors table.


11/46

11

Column Description

authorID The authors I number, a oreign key to theauthorstable.

isbn The ISBNor a book, a oreign key to thetitlestable.

authorISBN table from the books database.


12/46

12

authorIDisbn authorIDisbn

1 0131869000 2 0131450913

2 0131869000 1 01318282741 0131483986 2 0131828274

2 0131483986 3 0131450913

1 0131450913 4 0131828274

Sample data from the authorISBN table ofbooks.


13/46

13

l ri ti

isbn I of the book (a string). The tables primary key.

I is an abbre iation for International tandardook umber a numbering scheme that publishersuse to gi e e ery book a unique identification number.

title Title of the book (a string).

editionNumberEdition number of the book (an integer).

copyright Copyright year of the book (a string).

titles table from the books database.


14/46

14

14

isbn title editionNumber copyright

0131869000 Visual Basic How to Program 3 2006

0131525239 Visual C# How to Program 2 2006

0132222205 Java How to Program 7 20070131857576 C++ How to Program 5 2005

0132404168 C How to Program 5 2007

0131450913 Internet and World Wide WebHow to Program

3 2004

Sample data from the titles table of the books database.


15/46

15

Relational Database Overview: A

books Database (Cont.) An entity-relationship (ER) diagram shows the

database tables and the relationships among them.

Every row must have a primary-key value, and thatvalue must be unique in the table. This is known asthe Rule of Entity Integrity.

An infinity symbol () indicates a one-to-manyrelationship, in which an entry from a table can havean arbitrary number of entries in another table.

A many-to-many relationship indicates that multipleentries can be related between tables.


16/46

16

16

Common Programming Error

Not providing a value for every column in

a primary key breaks the Rule ofEntityIntegrity and causes the DBMS to reportan error.

Providing the same value for the primary keyin multiple rows causes the DBMS to reportan error.


17/46

17

17

Table relationships in the books database.

Primary keys

Foreign keys

One-to-many

relationship

One-to-many

relationship

Many-to-many

relationship


18/46

18


Providing a foreign-key value that does not

appear as a primary-key value in another tablebreaks the Rule ofReferential Integrity andcauses the DBMS to report an error.


19/46

19

19

SQL keyword Description

SELECT Retrieves data from one or more tables.

FROM Tables involved in the query. Required inevery SELECT.

WHERE

Criteria for selection that determine the rowsto be retrieved, deleted or updated. Optionalin a SQL query or a SQL statement.

GROUP BY Criteria for grouping rows. Optional in aSELECT query.

ORDER BY Criteria for ordering rows. Optional in aSELECT query.

INNER JOIN Combine rows from multiple tables.

INSERT Insert rows into a specified table.

UPDATE Update rows in a specified table.

DELETE

Delete rows from a specified table.

SQL query keywords.


20/46

20

Basic SELECT Query

The basic form of a query is

SELECT * FROM tableName

where the asterisk (*) indicates that all columnsfrom tableName should be selected, andtableName specifies the table in the database from

which rows will be retrieved.

To retrieve specific columns from a table, replacethe asterisk (*) with a comma-separated list of

column names.


21/46

21

Software Engineering ObservationFor most queries, the asterisk (*) should not be used to

specify column names. In general, you process results byknowing in advance the order of the columns in the resultfor example, selecting authorID and lastName from tableauthors ensures that the columns will appear in the resultwith authorID as the first column and lastName as the

second column. Programs typically process result columns by

specifying the column numberin the result (starting from number 1 for the first column).Selecting columns by name also avoids returning unneededcolumns and protects against changes in the actual order ofthe columns in the table(s).


22/46

22

22

Common Programming ErrorIf you assume that the columns are alwaysreturned in the same order from a query thatuses the asterisk (*), the program may

process the results incorrectly.

If the column order in the table(s) changes

or if additional columns are added at a latertime, the order of the columns in the resultchanges accordingly.


23/46

23

authorID lastName

1 Deitel

2 Deitel

3 Goldberg

4 Choffnes

Sample authorID and lastName data from the authors table.

SELECTauthorID,lastName FROMauthors


24/46

24

WH

ERE

Clause

The optional WHERE clause in a query specifies the selection criteriafor the query. The basic form of a query with selection criteria is

SELECTcolumnName1,columnName2, FROM

tableName WHEREcriteria

The WHERE clause can contain operators , =, =, andLIKE. Operator LIKE is used for string pattern matching withwildcard characters percent (%) and underscore (_).

A percent character (%) in a pattern indicates that a string matchingthe pattern can have zero or more characters at the percentcharacters location in the pattern.

An underscore (_) in the pattern string indicates a single characterat that position in the pattern.


25/46

25

title editionNumber copyright

Visual C# How to Program 2 2006

Visual Basic 2005 How to Program 3 2006

Java How to Program 7 2007

C How to Program 5 2007

Sampling of titles with copyrights after2005 from table titles.

SELECTtitle,editionNumber,copyright

FROMtitles

WHEREcopyright > '2005'


26/46

26

26


1 Harvey Deitel

2 Paul Deitel

Authors whose last name starts with D from the authors table.

SELECT authorID, firstName, lastName

FROM authors

WHERE lastName LIKE 'D%'


27/46

27

27


3 Andrew Goldberg

The only author from the authors table whose last namecontains o as the second letter.

SELECTauthorID, firstName,lastName

FROMauthorsWHERElastNameLIKE '_o%'


28/46

28

ORDER BY Clause The result of a query can be sorted in ascending or descending order

using the optional ORDER BY clause. The simplest form of an ORDER BYclause is

SELECTcolumnName1,columnName2, FROMtableName ORDER BY column ASCSELECTcolumnName1,columnName2, FROMtableName ORDER BY column DESC

where ASC specifies ascending order, DESC specifies descending orderand column specifies the column on which the sort is based. The defaultsorting order is ascending, so ASC is optional.

Multiple columns can be used for ordering purposes with an ORDER BYclause of the form

ORDER BY column1 sortingOrder, column2sortingOrder,

The WHERE and ORDER BY clauses can be combined in one query. Ifused, ORDER BY must be the last clause in the query.


29/46

29


4 David Choffnes

1 Harvey Deitel

2 Paul Deitel3 Andrew Goldberg

Fig. 22.15 | authors sample data in ascending orderby lastName.


FROMauthors

ORDERBYlastName ASC


30/46

30


3 Andrew Goldberg

1 Harvey Deitel

2 Paul Deitel

4 David Choffnes

authors sample data in descending orderby lastName.


FROMauthors

ORDERBYlastNameDESC


31/46

31


4 David Choffnes

1 Harvey Deitel

2 Paul Deitel3 Andrew Goldberg

authors sample data in ascending orderby lastName and firstName.


FROMauthors

ORDERBYlastName, firstName


32/46

32

32

isbn title editionNumber copyright

0132404168 C How to Program 5 20070131857576 C++ How to Program 5 2005

0131450913 Internet and World Wide WebHow to Program

3 2004

0132222205 Java How to Program 7 2007

0131869000 Visual Basic 2005 How to

Program

3 2006

0131525239 Visual C# How to Program 2 2006

Sampling ofbooks from table titles whose titles end with

How to Program inascending order

by title.

SELECT isbn, title, editionNumber,

copyright

FROM titles

WHERE title LIKE '%How to Program'

ORDER BY title ASC


33/46

33

Combining Data from Multiple

Tables: INNER JOIN An INNER JOIN combines rows from two tables by matching

values in columns that are common to the tables. The basic formfor the INNER JOIN operator is:

SELECTcolumnName1,columnName2, FROM table1

INNER JOIN table2

ON table1.columnName = table2.columnName

The ON clause specifies a condition that determines which rowsare joined. This condition often compares columns from eachtable IfaSQL statement uses columns with the same name frommultiple tables, the column names must be fully qualified byprefixing them with their table names and a dot (.).


34/46

34

Software Engineering Observation

If a SQL statement includes columns

with the same name from multipletables, the statement must precedethose column names with their table

names and a dot(e.g.,authors.authorID).


35/46

35


Failure to qualify names for columns that

have the same name in two or more tablesas an error.


36/46


37/46

37

INSERT StatementAn INSERT statement inserts a new row into a table. The

basic form of this statement is

INSERTINTO tableName (columnName1,columnName2, ,columnNameN)

VALUES(value1,value2, ,valueN)

where tableName is the table in which to insert the row.The tableName is followed by a comma-separated list of

column names in parentheses. The list of column names isfollowed by the SQL keyword VALUES and a comma-separated list ofvalues in parentheses.

SQL uses single quotes (') as the delimiter for strings. Tospecify a string containing a single quote in SQL, the single

quote must be escaped with another single quote.


38/46

38

Good Programming Practice

Always explicitly list the columns wheninserting rows. If the tables column orderchanges or a new column is added,omitting the columns list may cause anerror.

It is normally an error to specify a value foran autoincrement column.


39/46

39


SQL uses the single-quote (') character as a

delimiter for strings. To specify a string containing a

single quote (e.g., OMalley) in a SQL statement, thestring must have two single quotes in the positionwherethe single-quote character appears in the string

(e.g., 'O''Malley'). The first of the two single-quote characters acts as an escape character for thesecond. Not escaping single-quote characters in astring that is part of a SQL statement is a SQL

syntax error.


40/46

40


1 Harvey Deitel

2 Paul Deitel

3 Andrew Goldberg

4 David Choffnes

5 Sue Smith

Sample data from tableAuthors afteran INSERT operation.

INSERTINTO authors( firstName, lastName )

VALUES( 'Sue', 'Smith' )


41/46

41

UPDATE Statement

An UPDATE statement modifies data in a table. The basicform of an UPDATE statement is

UPDATEtableName

SETcolumnName1 =value1,columnName2 =value2, ,

columnNameN=valueN

WHEREcriteria

where tableName is the table in which to update data. ThetableName is followed by keyword SET and a comma-separated list of column name/value pairs in the formatcolumnName =value. The optional WHERE clause criteriadetermines which rows to update.


42/46

42


1 H ar ey D eitel

2 Pau l D eitel

3 Andrew Goldberg

4 D a id C hoffnes5 ue Jones

Sample data from table authors afteran UPDATE operation.

UPDATEauthors

SETlastName = 'Jones'

WHERElastName = 'Smith' AND firstName = 'Sue'


43/46

43

DELETE Statement

A DELETE statement removes rows from a table.The simplest form for a DELETE statement is

DELETE FROM tableName WHEREcriteria

where tableName is the table from which to deletea row (or rows). The optional WHERE criteria

determines which rows to delete. If this clause isomitted, all the tables rows are deleted.


44/46

44


1 Harvey Deitel

2 Paul Deitel

3 Andrew Goldberg

4 David Choffnes

Sample data from table authors afteraDELETE operation.

DELETE FROMauthors

WHERElastName = 'Jones' ANDfirstName = 'Sue'


45/46

45

MySQL MySQL (pronounced my sequel) is a robust and scalable

relational database management system (RDBMS) that wascreated by the Swedish consulting firm TcX in 1994.

MySQL is a multiuser, multithreaded RDBMS server that usesSQL to interact with and manipulate data.

Multithreading capabilities enable MySQL database toperform multiple tasks concurrently, allowing the server to

process client requests efficiently.

Implementations ofMySQL are available for Windows, MacOS X, Linux and UNIX.


46/46

46

Benefits of Using MySQL Scalability: Embed in an application or use it in

massive data warehousing environments

Performance: Optimize performance based on thepurpose of the database in an application.

Support for many programming languages : Accessa MySQL database from PHP and Ruby on Rails.

Implementation ofMySQL for windows , MAC OS X,Linux, UNIX. Handling large databases.

chapter 6 database updated

Documents