لطفا منتظر باشید ...
5.1 Database Basics
The field of databases has its own terminology. Terms such as
database, table, attribute, row, primary key, and relational model
have specific meanings and are used throughout this chapter. In this
section, we present an example of a simple database to introduce the
basic components of relational databases, and we list and define
selected terms used in the chapter. We then show you our
winestore database that we use throughout our
examples in this chapter, and as the basis of our sample application
in Chapter 16 through Chapter 20. More detail on the database can be found in
Appendix E.
5.1.1 Introducing Relational Databases
A simple example
relational database is shown in Figure 5-1. This database stores data about wineries and
the wine regions they are located in. A relational database is
organized into tables, and there are two tables
in this example: a winery table that stores
information about wineries, and a region table
that has information about wine regions. Tables collect together
information that is about one object.
Figure 5-1. An example relational database containing two related tables
system
(DBMS) or
database server
. A database server supports a database
language to create and delete databases and to manage and search
data. The database language used by almost all database servers is
SQL
,
a set of statements that define and manipulate data. After creating a
database, the most common SQL statements used are
INSERT, UPDATE,
DELETE, and SELECT, which add,
change, remove, and search data in a database, respectively.In this book, we use the
MySQL
database server to manage databases. MySQL runs as a server (daemon)
process or service, like Apache or IIS, and supports several
different clients including a command-line interpreter (that we use
in this chapter) and a PHP function library (that we use throughout
later chapters). One MySQL server can manage multiple databases for
you for multiple applications, and each can store different data
organized in different ways.A database table may have multiple
attributes
,
each of which has a name. For example, the
winery table in Figure 5-1 has
four attributes, winery ID,
winery name,
address, and region
ID. A table contains the data as
rows
,
and a row contains values for each attribute that together represent
one related object. (Attributes are also known as
fields
or
columns
,
while rows are also known as
records
.
We use attribute and row throughout this book.)Consider an example. The winery table has five
rows, one for each winery, and each row has a value for each
attribute. For example, in the first winery row, the attribute
winery ID has a value of 1, the
winery name attribute has a
value of Moss Brothers, the attribute address has
a value of Smith Rd., and the region
ID attribute has a value of 3. There is a row for
region 3 in the region table and it corresponds
to Margaret River in Western Australia. Together this data forms the
information about an object, the Moss Brothers Winery in Western
Australia.In our example, the relationship between wineries and regions is
maintained by assigning a region
ID to each winery row. The region
ID value for each region is unique, and this allows you to
unambiguously discover which region each winery is located in.
Managing relationships using unique values is fundamental to
relational databases. Indeed, good database design requires that you
can make the right choice of which objects are represented as tables
and which relationships exist between the tables. We discuss good
database design in Appendix E.In our example of the relationship between wineries and regions,
there's a one-to-many mapping between regions and
wineries: more than one winery can be situated in a region (three
wineries in the example are situated in the Barossa Valley) but a
winery can be situated in only one region. It's also
possible to have two other types of relationship between tables: a
one-to-one relationship where, for example, each bottle of wine has
one label design, and a many-to-many relationship where, for example,
many wines are delivered by many couriers. As we show you later,
unique values or primary
keys
allow these relationships to be managed
and they're essential to relational databases.Attributes have data
types
. For example, in the
winery table, the winery
ID is an integer, the winery
name and address are strings,
and the region ID is an
integer. Data types are assigned when a database is designed.Tables usually have a primary key, which is
formed by one or more values that uniquely identify each row in a
table. The primary key of the winery table is
the winery ID, and the primary
key of the region table is the
region ID. The values of these
attributes aren't usually meaningful to the user,
they're just unique ordinal numbers that are used to
uniquely identify a row of data and to maintain relationships.Figure 5-2 shows our example database modeled using
entity-relationship (ER)
modeling
. An ER model is a standard method
for visualizing a database and for understanding the relationships
between the tables. It's particularly useful for
more complex databases where relationships of different types exist
and you need to understand how to keep these up-to-date and use them
in querying. As we show you later, our winestore
database needs a moderately complex ER model.In the ER model in Figure 5-2, the
winery and region tables or
entities
are shown as rectangles. An entity is often a real-world object and
each one has
attributes
,
where those that are part of the primary key are shown underlined.
The relationship between the tables is shown as a diamond that
connects the two tables, and in this example the relationship is
annotated with an M at the winery-end of the
relationship. The M indicates that there are potentially many winery
rows associated with each region. Because the relationship
isn't annotated at the other end, this means that
there is only one region associated with each winery. We discuss ER
modeling in more detail in Appendix E.
Figure 5-2. An example relational model of the winery database
5.1.2 Database Terminology
Database
A
repository to store data. For example, a database might store all of
the data associated with finance in a large company, information
about your CD and DVD collection, or the records of an online store.
Table
A part of a database
that stores data related to an object, thing, or activity. For
example, a table might store data about customers. A table has
columns, fields, or attributes. The data is stored as rows or
records.
Attributes
The columns in a
table. All rows in a table have the same attributes. For example, a
customer table might have the attributes
name, address, and
city. Each attribute has a data type such as
string, integer, or date.
Rows
The data entries stored
in a table. Rows contain values for each attribute. For example, a
row in a customer table might contain the values
"Matthew Richardson,"
"Punt Road," and
"Richmond." Rows are also known as
records.
Relational model
A formal
model that uses database, tables, and attributes to store data and
manages the relationship between tables.
(Relational) database management system (DBMS)
A software application that
manages data in a database and is based on the relational model. Also
known as a database server.
SQL
A
standard query language that interacts with a database server. SQL is
a set of statements to manage databases, tables, and data. Despite
popular belief, SQL does not stand for Structured Query Language and
isn't pronounced Sequel: it's
pronounced as the three-letter acronym S-Q-L and it
doesn't stand for anything.
Constraints
Restrictions or
limitations on tables and attributes. A database typically has many
constraints: for example, a wine can be produced only by one winery,
an order can't exist if it isn't
associated with a customer, and having a name attribute is mandatory
for a customer.
Primary key
One or more
attributes that contain values that uniquely identify each row. For
example, a customer table might have the primary
key named cust ID. The cust
ID attribute is then assigned a unique value for
each customer. A primary key is a constraint of most tables.
Index
A data structure
used for fast access to rows in a table. An index is usually built
for the primary key of each table and can then be used to quickly
find a particular row. Indexes are also defined and built for other
attributes when those attributes are frequently used in queries.
Entity-relationship (ER) modeling
A technique used
to describe the real-world data in terms of entities, attributes, and
relationships. This is discussed in Appendix E.
Normalized database
A correctly
designed database that is created from an ER model. There are
different types or levels of normalization, and a
third-normal form database is generally regarded
as being an acceptably designed relational database. We discuss
normalization in Appendix E.
5.1.3 The Winestore Database
This section is a summary of the
entity-relationship model of the winestore
database. It's included for easy reference, and
you'll find it useful to have at hand as you work
through this chapter.
5.1.3.1 The winestore entity-relationship model
Figure 5-3 shows the complete entity-relationship
model for our example winestore database; this
model is derived from the system requirements listed in Chapter 16, and is derived
following the process described in Appendix E.
Appendix E also includes a description of the
meaning of each shape and line type used in the figure.
Figure 5-3. The winestore ER model
wines by placing one or more orders.Each customer has exactly one set of
user details.Each customer has a title (such as "Mr" or
"Dr") and lives in a country.Each order contains one or more
items.Each item is a specific
quantity of wine at a
specific price.A wine is of a type such as
"Red,"
"White," or
"Sparkling."A wine has a vintage year; if
the same wine has two or more vintages from different years, these
are treated as two or more distinct wines.Each wine is made by one
winery.Each winery is located in one
region.Each wine has one or more
grape_variety entries. For example, a wine of
wine_name
"Archibald" might be made of the
grape_variety entries
"Sauvignon" and
"Cabernet." The order of the
entries is important. For example, a "Cabernet
Sauvignon" is different from a
"Sauvignon Cabernet."Each inventory for a wine
represents the on-hand stock of a wine. If a wine is available at two
prices, there are two inventories. Similarly, if the stock arrived at
the warehouse at two different times, there are two inventories.Each wine may have one or more
inventories.
