SQL Bible [Electronic resources] نسخه متنی

Real-Life Database Examples

To say that the databases are everywhere
would be an understatement. They virtually permeate our lives: Online stores,
health care providers, clubs, libraries, video stores, beauty salons, travel
agencies, phone companies, government agencies like FBI, INS, IRS, and NASA —
they all use databases. These databases can be very different in their nature
and usually have to be specifically designed to cater to some special customer
needs. Here are some examples.

Order management system
database

A typical database for a company that
sells building materials might be arranged as follows: The company must have at
least one customer. Each customer in the database is assigned one or more
addresses, one or more contact phones, and a default salesperson who is the
liaison between the customer and the company. The company sells a variety of
products. Each product has a price, a description, and some other
characteristics. Orders can be placed for one or more product at a time. Each
product logically forms an order line. When an order is complete it can be
shipped and then invoiced. Invoice number and shipment number are populated
automatically in the database and can not be changed by users. Each order has a
status assigned to it:
COMPLETE,
SHIPPED,
INVOICED, and so on. The database also
contains specific shipment information (bill of lading number, number of boxes
shipped, dates, and so on). Usually one shipment contains one order, but the
database is designed in such a way that one order can be distributed between
more than one shipment, as well as one shipment can contain more than one
order. Some constraints also exist in the database. For example, some fields
cannot be empty, and some other fields can contain only certain types of
information.

You already know that a database is a
multiuser environment by definition. It's a common practice to group users
according to the functions they perform and security levels they are entitled
to. The order management system described here could have three different user
groups: Sales department clerks' function is to enter or modify order and
customer information; shipping department employees create and update shipment
data; warehouse supervisors handle products. In addition, all three user groups
view diverse database information under different angles, using reports and
ad-hoc queries.

We'll use this database, which we'll call
ACME, throughout this book for examples and exercises. ACME database is a
simplified version of a real production database. It has only 13 tables, and
the real one would easily have over a hundred.

Health care provider database

A health
provider company has multiple offices in many different states. Many doctors
work for the company, and each doctor takes care of multiple patients. Some
doctors just work in one office, and others work in different offices on
different days. The database keeps information about each doctor, such as name,
address, contact phones, area of specialization, and so on. Each patient can be
assigned to one or more doctors. Specific patient information is also kept in
the database (name, address, phones, health record number, date of birth,
history of appointments, prescriptions, blood tests, diagnoses, etc.).
Customers can schedule and cancel appointments and order prescription drugs
either over the phone or using the company Web site. Some restrictions apply —
for example, to see a specialist, the patient needs an approval from his/her
primary physician; to order a prescription the patient should have at least one
valid refill left, and so on.

Now, what are the main database user
groups? Patients should be able to access the database using a Web browser to
order prescriptions and make appointments. This is all that patients may do in
the database. Doctors and nurses can browse information about their patients,
write and renew prescriptions, schedule blood tests and X-Rays, and so on.
Administrative staff (receptionists, pharmacy assistants) can schedule
appointments for patients, fill prescriptions, and run specific reports.

Again, in real life this database would be
far more complicated and would have many more business rules, but our main goal
now is just to give a general idea what kind of information a database could
contain.

The health provider and order management
system databases are both examples of a typical hybrid
database (though the former is probably closer to an OLTP).

Scientific database

A database for genome research and related
research areas in molecular and cellular biology can be a good example of a
scientific database. It contains gene catalogs for completely sequenced genomes
and some partial genomes, genome maps and organism information, and data about
sequence similarities among all known genes in all organisms in the database.
It also contains information on molecular interaction networks in the cell and
chemical compounds and reactions.

This database has just one user group —
all researchers have the same access to all the information. This is an example
of a data warehouse.

Nonprofit organization
database

A database of an antique automobile club
can be pretty simple. Also, such an organization would not typically have too
many members, so the database is not going to be very large. You need to store
members' personal information such as address, phone number, area of interest,
and so on. The database might also contain the information about the autos
(brand, year, color, condition, etc.). Autos are tied to their owners (members
of the club). Each member can have one or more vehicles, and a vehicle can be
owned by just one member.

The database would only have a few users —
possibly, the chairman of the club, an assistant, and a secretary.

The last two examples are not
business-critical databases and don't have to be implemented on expensive
enterprise software. The data still have to be kept safely and should not be
lost, but in case of, let's say, hardware failure it probably can wait a day or
two before the database is restored from a backup. So, the use of a free
database, like mySQL, PostgreSQL, or even nonrelational Posgres is appropriate.
Another good choice might be MS Access, which is a part of Microsoft Office
Tools; if you bought MS Office just because you want to use Word and Excel, you
should be aware that you've got a free relational database as well. (MS Access
works well with up to 15 users.)