Entity relationship diagram for bakery

HANDS ON DATABASE

by Steve Conger

© 2010

Hands ON Database

2

Introduction

Many students taking an introductory database course need hands-on experience. Typically they are

under pressure to finish quickly with a certificate or degree and get to work. They need to get actual

practice in the process of designing and developing databases that they can apply in their future

employment. They need to create tables, enter data, and run SQL queries.

This book is designed for them.

Hands on Database: an Introduction to Database Design and Development focuses on the process of

creating a database. It guides the student through the initial conception of the database. It covers

gathering of requirements and business rules, the logical and physical design and the testing of the

database. It does this through a continuous narrative that follows a student, Sharon, as she designs and

constructs a database to track the tutoring program at her school. It shows some of her missteps as well

as her successes. Students get hands-on experience by doing practices and developing scenarios that

parallel the narrative.

After completing this book students will have a good sense of what is involved in developing and

creating a database. Below is a list of the book outcomes. A student who has completed this book will be

able

 to give a general definition of a relational database

 to identify a variety of ways to gather database requirements

 to define business rules for a database

 to create an Entity design for a database

Hands ON Database

3

 to normalize a design up to third normal form

 to develop a database in a given DBMS

 to run SQL Queries against sample data to test requirements and business rules

 to define the general security context of a database and its users

 to document the process of database design and development

The Scenario Approach

The scenario approach is at the heart of the book. It informs both the narrative and the exercises. A

scenario in its essence is a story problem. It provides a context from which to work. It is much easier for

a student to understand database design if he or she sees it as a solution to a particular set of problems.

There is an emphasis on defining business rules and then testing the database design against those

rules. The scenarios also provide a sense of process. They give the student some guidance in how to go

about defining and developing a database. I would argue that even a computer science student could

benefit from this approach. It would allow them to experience how the concepts they have learned can

be applied to the actual development process.

The scenario that makes up the body of the book describes Sharon, a database student, in the process of

creating a database to manage the school’s tutoring program. She encounters several problems. The

way that tutoring sessions are scheduled is awkward and inefficient. The reports that the manager of

the program needs to make are difficult and time consuming to put together. It is also difficult, at times,

to track the tutor’s hours. Sharon sees a database as a solution to these problems and sets about

defining its requirements, designing it, and building a prototype. She enters some sample data and then

Hands ON Database

4

tests the database using SQL to enter and retrieve the information required. Finally she looks carefully at

the security issues inherent in the database.

At the end of each chapter, after the practices, there are four additional scenarios for the student to

develop. The Wild Wood Apartments scenario involves creating a database manage a chain of

apartment buildings. Vince’s Vintage Vinyl Record shop offers a scenario of a small shop owner who

needs a database to handle his inventory, sales and purchases. Grandfield College leads students

through the process of making a database to track what software the school owns, the licensing for that

software, on what machines the software is installed, and what users have access to those machines.

The WestLake Research Hospital scenario involves creating a database to track a double blind drug

study for a new antidepressant.

The scenarios are meant to be complex enough to keep the student involved, but simple enough not to

overwhelm the novice. Each scenario presents different challenges. Students could work on some or all

the scenarios or they could be broken into groups with each group assigned one of the scenarios. The

scenarios are open ended, that is, they offer room for student creativity and innovation. They and the

instructor are free to define many of the parameters and business rules as they proceed. But each

scenario, in each chapter, has specific deliverables that help keep the students on track.

Other Features

Process Driven The book models the process of developing a database from the beginning through the final stages. It

provides students with tools and techniques for discovering requirements and business rules. It also

provides them with suggestions for organizing and managing all the complex details that go into

developing a database. The book emphasizes the need to understand the data and the relationships

Hands ON Database

5

among the data. It shows them the value of carefully designing a database before actually implementing

it. Then when the database is first developed, it emphasizes the need to test it, to make sure it meets

the requirements and business rules before deploying the database. Finally it emphasizes the need to

secure a database against both accidental and intentional threats.

Normalization Normalization is an important but complex issue in database development. Anyone who works with

databases is expected to have some knowledge of normalization. For this reason, I believed it important

to introduce the students to the concepts and vocabulary of normalization. But, because this is an

introductory book focused on the process of development and design, I only discussed the first three

Normal Forms. I have found that most databases that achieve at least the third normal form are

functional if not optimal in design. That being said, I do believe anyone working in databases should

become familiar with all the normal forms and principles of normalizations. In the “Things to Look Up”

segment of Chapter Four, I direct students to look up the other normal forms and pick one of them to

explain to other students. Also, in Appendix Four Common Relational Patterns, the last example shows

an ERD of a database that has been normalized beyond Third Normal Form.

SQL Chapter Seven in Hands on Database contains an extensive introduction to SQL. It covers SELECT

statements, of course, using a variety of criteria, as well as using scalar functions, especially date and

time functions, and various aggregate functions. Inner and outer joins are discussed. INSERT, UPDATE

and DELETE statements are introduced. The chapter also illustrates the use of Views and provides an

example of a stored procedure and a trigger. Chapter Eight looks at stored procedures in terms of how

they can be used to protect data integrity and security. SQL commands related to Logins and

permissions are also introduced.

Hands ON Database

6

Perhaps more importantly than the specific SQL commands presented is the context in which they are

introduced. In the text Sharon uses the SQL to test the requirements and business rules of the

TutorManagement database. In the scenarios Students use the SQL to test the requirements and

business rules of the databases they have created. In Chapter Eight they see SQL as a tool for securing a

database. By presenting it in this way, students see SQL as a vital part of database development and not

just an academic exercise.

Security Security issues are discussed at several points in the book. It is brought into consideration during the

information gathering phases in Chapters Two and Three. But it is dealt with in detail in Chapter Eight.

Chapter Eight attempts to show the student a structured approach to security. It looks at each user of

the database and creates a table that delineates exactly what permissions that user needs on each

object in the database. It applies a similar technique for analyzing threats to the database. Then it

introduces the concept of roles as collections of permission. It shows how a developer could create an

application layer of views and procedures and then assign roles permissions to those objects rather than

to the underlying tables.

Finally, the chapter discusses the importance of disaster management and of creating a set of policies

and procedures for recovering from any conceivable disaster.

Software used by the book The book uses Microsoft SQL Express 2008 R2 for the database and Microsoft Visio 2010 for the

database diagramming. The SQL Express software is offered free from Microsoft. At the time of writing

this Introduction SQL Express was available at http://www.microsoft.com/express/Database/ . This is, of

http://www.microsoft.com/express/Database/
Hands ON Database

7

course subject to change. But one can always go to the Microsoft site and type SQL Server Express in the

Bing search box. This will list the current download URL.

I selected SQL Server Express because it is readily available and because it provides a more realistic and

complete Database Management System experience than Microsoft Access which is often used in

classroom settings. SQL Server Express lets the students experience managing multiple databases in a

single management environment. The SQL Express Management Studio also contains a query analyzer

that allows students to easily run SQL queries and view the results. Unlike Access, SQL Server Express

supports Stored Procedures and Triggers. Finally, again unlike Access, SQL Express provides a rich set of

security features that are more typical of commercial database management systems. If, however, an

instructor prefers or must use Microsoft Access, Appendix one explains how to substitute it for SQL

Server. The appendix notes the variations in practices and examples in each chapter required for the

adaption.

Other database software such as MySQL or Oracle could also be adopted for use with the book.

Although the book uses SQL Server Express, its focus is on the process of developing and designing a

database. The principles of this process are applicable to any DBMS.

Microsoft Visio is readily available to students for schools that belong to the Microsoft Developers

Network Academic Alliance (MSDNAA ). It can also be purchased at a significant discount from places

like the Academic Superstore and other academic outlets. Visio offers a range of tools and templates

that help make diagramming and modifying diagrams easy and enjoyable for students. Appendix Three

offers additional instruction in how to use the Database Model template in Visio 2010. Of course, other

modeling software could be easily substituted, or students could be asked to simply draw their models

on graph paper. What is important are the concepts, not the particular tools.

Hands ON Database

8

Chapter Conventions

Each chapter contains several elements other than the narrative about Sharon. These elements are

meant to provide greater depth and to provoke the student to think about some of the broader

implications of the material.

Things You Should Know These extended sections provide background and descriptions of various aspects of database

development and design. In many ways they function like the more traditional textbook. They provide

definitions, explanations and examples that provide a deeper, more comprehensive context to the

things that Sharon is doing in the narrative.

Things to Think About These are sidebars that invite the student to consider questions about the processes or topics under

discussion. The questions in these sections do not have definite answers. They are meant to encourage

thought and discussion.

Cautions Cautions are found in the margins of the text. Their purpose is to warn the students about potential

mistakes or common errors.

Documentation These sections are found at the end of each chapter. They provide a summary of how a student would

go about documenting the activities conducted during the chapter.

Things to Look up These are also found at the ends of the chapters. They guide students to other resources and topics not

fully covered in the book.

Hands ON Database

9

Vocabulary Vocabulary is an important part of any discipline. Anyone who wants to work in the database field will

be expected to know and understand certain terms.

Vocabulary words are highlighted in margins and are repeated in a exercise at the end of each chapter

where the student is asked to match the word with the definition. SQL terms are listed in Tables at the

ends of Chapters Six and Eight. The terms are also defined in a Glossary at the end of the book.

Practices Practices are at the end of each chapter. They are designed to give each student hands on experience

with the materials of the chapter. Most practices are self-contained but some do build on each other. In

particular the practices for Chapter Five and Chapter Six are related. In Chapter Five the students build a

Pizza database and in Chapter Six they query that database with SQL.

Scenarios As mentioned earlier, Scenarios are the life of the book. There are four scenarios which students build

on throughout the book. Their purpose is to provide students with the full experience of developing a

database from identifying the initial concept to testing the fully built database. For students, the most

effective use of these scenarios would be to follow one or more of the scenarios throughout the entire

term.

Outline:

The book contains Eight Chapters, Four Appendixes and a glossary. It is meant to be just long enough to

be covered fully in a single term. Below is an outline of the book with a summary of the chapter

narrative and a list of the outcomes for that chapter.

Hands ON Database

10

Chapter One: Who Needs a Database?

Narrative: Sharon, a student at a community college, applies to become a tutor for Database related

subjects at the school. She discovers they use spiral notebooks and spreadsheets to manage the tutoring

information. She suggests to the supervisor that they could benefit from a database and offers to build

it. The supervisor agrees to the project. Sharon interviews her and gets a sense of what the overall

database will entail and drafts a statement of scope. She and the supervisor discuss the statement and

make some modifications.

Outcomes:

 Define relational databases

 Understand the position of Relational Databases in the history of Databases

 Identify major Relational Database Management Systems

 Identify main characteristics of Relational databases

 Understand SQL’s role in relational database

 Recognize some indications of where a database could be useful

 Define a statement of scope for a given database scenario

Chapter Two: Gathering Information

Narrative: Now that she has the scope of the database, Sharon begins to gather information about the

data the database will need to capture and process. First she looks at the spiral notebooks that have

been used to schedule tutoring sessions. She looks also looks at the spreadsheets the supervisor

develops for reports and other related documents. Then she arranges an interview with several of the

tutors, an additional interview with the supervisor, and creates a questionnaire for students who use the

Hands ON Database

11

tutoring services. Finally she spends an afternoon in the computer lab, observing how students schedule

tutoring and how the actual tutoring sessions go.

Outcomes:

 Review documents to discover relevant entities and attributes for database

 Prepare interview questions and follow up

 Prepare questionnaires

 Observe work flow for process and exceptions

Chapter Three: Requirements And Business Rules

Narrative: Having gathered all this information, Sharon must figure out what to do with it. She searches

through her notes for nouns and lists them. Then she looks at the lists to see if there are additional

topics, or subjects. Then she groups which nouns go with which topics. For each topic area, Sharon

identifies some candidate keys. Next she looks through her notes to determine what the business rules

of the tutoring program are. She lists the rules and makes notes for further questions. The rules seem

complex and Sharon remembers something from a systems analysis class about UML diagrams called

Use Case Diagrams. She uses these diagrams to graphically show how each actor—tutor, student, and

supervisor—interact with the database.

Outcomes:

 Use nouns from notes and observations to discover database elements

 Group elements into entities and attributes

 Define business rules

 Develop Use Case diagrams to model requirements

Hands ON Database

12

Chapter Four: Database Design

Narrative: Sharon is ready to design the database. She looks at her topics lists and diagrams an initial

set of Entities, using Visio. She analyses the relationships among the entities adding linking tables

wherever she finds a many-to-many relation. Then she adds the other items from her list to the

appropriate Entities as attributes. For each attribute she assigns a data type. She reviews the design to

ensure that she has captured all the data and the business rules.

 Use the database modeling template in Microsoft Visio

 Create Entities and add attributes

 Determine the appropriate relationship between entities

 Resolve many to many relationships with a linking table

Chapter Five: Normalization and Design Review

Narrative: Now, with the help of an instructor, Sharon checks to make sure the database conforms to

the rules of normalization. She reviews the database thus far with her supervisor.

Outcomes:

 Evaluate entities against first three normal forms

 Adjust the relational diagram to reflect normalization

Hands ON Database

13

Chapter Six: Physical

Narrative: Sharon builds a prototype of the database, creating all the tables and setting up the

relationships. (SQL Server Express –though I could other DBMS’s also.) When she has it set up she enters

5 or 10 rows of sample data so she can test the database.

Outcomes:

 Implement a physical design of the database based on the logical ERDs.

 Choose appropriate data types for columns

 Enter sample data into tables

Chapter Seven: SQL

Narrative: Sharon writes some SQL queries to see if she can get the needed information out of the

database. She tests for database requirements

Outcomes:

 Name the main events in the development of SQL

 Run SELECT queries with a variety of criteria

 Join two or more tables in a query

 Use the Aggregate Functions COUNT, AVG, SUM, MIN, and MAX

 INSERT, UPDATE and DELETE records

 Use SQL to test business rules

Hands ON Database

14

Chapter Eight: Is it Secure?

Narrative: In this chapter Sharon looks at the security needs of the database. It is important to give

everyone the access that they require to do the things they need to do. But it is also important to

protect the database objects and data from either accidental or intentional damage. Sharon discovers

that security is a complex and requires careful planning.

Outcomes

 Analyze security needs and restrictions for users of the database

 Analyze threats to database integrity

 Understand the concepts of authentication and authorization

 Create logins and users

 Create roles

Appendixes

Microsoft Access

A quick overview of using Microsoft Access instead of SQL Server with the book It looks at each chapter

and shows how you would use Access and what adjustments you will need to make to the practices and

scenarios

Visio

An overview of the Visio environment with a special focus on the database templates

Hands ON Database

15

SQL Server Express

An overview of how to use the SQL Server Management Studio to create and access databases in Sql

Server Express

Common Relational Patterns

A review of some of the most common relational patterns students will encounter in database design

such as the Master /Detail relation, weak entities, linking tables, etc..

Glossary of Terms

Glossary of all vocabulary terms

Conclusion There are many good database textbooks, but they tend to be more theoretical than hands-on. Their

audience is the computer science student who needs to understand the deeper, mathematical subtleties

of entity relationships, who needs to understand the ways that various database management systems

process physical files or how they optimize queries. These are valuable skills, but these books contain

far too much information for a student to absorb in a term, and too little hands-on practice for the

student who is looking for a practical introduction to database. Hands On Database is designed to be

that practical introduction.

Hands ON Database

16

Chapter One: Who Needs a Database

Overview of Relational Databases and their Uses

In this chapter we meet Sharon, a college student who is working toward a degree in Database

Development and Administration. She signs up to become a tutor and realizes that the tutoring program

is in desperate need of a database to track tutoring sessions. She volunteers to develop it and after

some discussions defines a statement of work for the database.

Chapter Outcomes  Define relational databases

 Understand the position of Relational Databases in the history of Databases

 Identify major Relational Database Management Systems

 Identify main characteristics of Relational databases

 Understand SQL’s role in relational database

 Recognize some indications of where a database could be useful

 Define a statement of work for a given database scenario

The Situation

Sharon is a student taking database classes. She is near

the end of her program and has done quite well. Like

any student, she could really use some extra money

and has decided to inquire about tutoring. She has

Relational Database—a type of

database that uses “relations,”

tables, to store and relate tables.

See “Things You Need to Know 1”

Hands ON Database

17

noticed that many students seem to struggle with relational database concepts, particularly in the early

classes, and she is fairly sure there would be a demand for her services.

The administrator of the tutoring program at the college is named Terry Lee. Terry invites Sharon into

her office and offers her a seat. She smiles.

“So you want to tutor?”

“Yes. I think I would be good at it.”

“What subjects do you think you could tutor?”

“I was thinking especially of database related topics. I can

do relational design and SQL. I think I could tutor Microsoft

Access, Sql Server and even other Database Management

Systems. I can also do some database programming.”

Terry nods. “That’s good. We do have some requests for tutoring in those areas but so far no one to

provide the tutoring. Before you can begin, you will need to get recommendations from two instructors

who teach in the area you want to tutor. Also you will need to do a short training session.”

Sharon smiles, “That’s no problem.”

“Good.” Terry rises from her seat. “Let me show you how things work.”

Things You Should Know

Databases

Relational Design involves

organizing data into tables or

entities and then determining the

relations among them. SQL is the

language Relational Databases Use

to create their objects and to

modify and retrieve data.

Hands ON Database

18

A database is, at its simplest level, a collection of related data. It doesn’t have to be electronic. The card

catalogs that libraries used to have were certainly databases. A scientist’s spiral notebook where he or

she keeps notes and observations could be considered a database, so too could a phone or address

book. When we say “database,” though, we usually mean electronic databases, databases that run on

computers.

Flat File Databases

The simplest form of database is the flat file database. Flat

files usually consist of a file which store data in a structured

way. A common format for flat file databases is the

delimited file. In a delimited file, each piece of data is

separated from the next piece by some “delimiter,” often a

comma or a tab. The end of a row is marked by the new

line character (usually invisible). It is important, if the file is

to be read correctly, that each row contain the same

number of delimiters. Another kind of flat data file is the fixed width data file. In these all the columns

share a fixed width in characters. These flat files can be

read by a computer program and manipulated in various

ways, but they have almost no protections for data

integrity and they often contain many redundant elements.

Spreadsheets, such as Excel, can also be used as flat file

databases. Spreadsheets offer a great deal more

functionality than simple delimited files. Cells can be given a

data type such as “numeric” or “date time.” This helps

ensure that all the entries in a given column are of the same type. You can also define valid ranges for

Redundancy—refers to storing the

same data in more than one place in

the database

Data Integrity—refers to the

accuracy and the correctness of the

data in the database

Delimited files have some sort of

character separating columns of

data. The delimiter is often a

comma or tab, but can be any non

alphanumeric character. in Fixed

Length files the length in

characters of each column is the

same

Hands ON Database

19

data (For instance, you can stipulate that a valid term grade is between the numbers 0 and 4)

Spreadsheets usually contain data tools that make it possible to sort and group data. Most spreadsheets

also contain functions that allow the user to query the data. But despite these enhancements

spreadsheets still share many of the redundancy and data integrity problems of other flat file formats.

Figure 1: Delimited Text Example

Hands ON Database

20

Figure 2: Excel Spreadsheet

Hierarchical Databases

The most common database model before the relational model was the hierarchical database.

Hierarchical databases are organized in a tree like structure. In it one parent table can have many child

tables, but no child table can have more than one parent. This sounds abstract, and it is. One way to

visualize it is to think of the Windows (or, for that matter, the Mac or Linux) file system. The file system

has a hierarchical structure. You have a directory, under which there can be sub directories and in those

subdirectories can be other subdirectories or files. You navigate through them by following a path.

Hands ON Database

21

C:\Users\ITStudent\Documents\myfile.txt

This tree like organization is very logical and easy to navigate but

it does present some of the same problems of redundancy, data

Integrity and comparability of data. It is not uncommon for the

same data to be repeated in more than one place in the tree.

Any time data is repeated there is a risk of error and

inconsistency. It also can be very difficult to compare a piece of

data from one branch of the database with a piece from an

entirely different branch of the database.

Relational Databases

By far, the most popular type of database for at least the last 30

years is the relational database. The idea for relational

Databases came from a man named Edgar F. Codd in 1970. He

worked for IBM and he wrote a paper on, at that time, a new

theoretical design for databases. This design would be based on

the mathematics of set theory and predicate logic. He

formulated the

basics of the relational design in 12 rules (Actually there

are 13 rules. Like any good computer engineer, Codd

begins his numbering with 0.) Briefly, in the relational

model data would be organized into relations or tables and

these relations would define the relationships among

themselves by means of repeating an attribute or column

Keys—in relational databases each

table usually has one column

designated as a primary key. This

key uniquely identifies each row in

the table. This primary key

becomes a foreign key when it is

repeated in an another table to

create a link between the tables

Things to think about

Hierarchical databases are still in

use in many institutions. This is

especially true of large institutions

such as banks and insurance

companies that adopted database

technologies early.

These institutions invested heavily

in the development of these

databases and have committed

decades of data to their files.

Although database technologies

have improved, they are reluctant

to commit the time and money,

and to incur the risk of

redeveloping their databases and

of translating their vast stores of

existing data into new formats.

The basic philosophy is, if it still

works, let well enough alone. Most

companies are conservative about

their databases, for

understandable reasons.

What do you think companies like

Microsoft or Oracle have to do to

convince companies to upgrade to

their newest database products?

Hands ON Database

22

from one table in another table. These repeating columns would be called “Keys.” He also specified that

the logical design of a database should be separate and independent of physical design considerations

such as file types and disk writing and reading functions. He specified that there should be a “data

sublanguage” that can perform all data related tasks. SQL has evolved into this language. We will discuss

it more thoroughly in a later chapter. For a discussion of Codd’s 12 rules see Wikipedia

http://en.wikipedia.org/wiki/Codd's_12_rules

Figure 3: SQL Server Relational Database Manager showing an Entity diagram for a DVD Rental database

This may sound complex, and it certainly can be, but it solved many of the problems that plagued the

databases of the day. One of those problems was data redundancy. Redundancy refers to the need to

store the same data in more than one place in the database. In a banking database, for instance, you

would store the customer’s name and address along with an associated savings account. But you might

http://en.wikipedia.org/wiki/Codd's_12_rules
Hands ON Database

23

have to repeat this same customer information for a checking account. The more times you have to

enter the same information, the more likely it is that one of the entries will contain an error. Also, if you

have to change the information, an address or phone number, for example, the greater the likelihood

that one of the entries could miss being updated. This kind of update error is why bills or notices

sometimes continue to an old address even after you have submitted your new address to a company. It

was changed it in some places but not others.

In a relational database the redundancy is minimized. A bank would enter the customer’s data only

once, in one place. Any changes would be made only in one place. The only redundancy that is allowed

is the repetition of a key column (or columns) that is used to create relationships among the tables. This

significantly reduces the chances of error and protects the integrity of the data in the database.

Figure 4: Primary key Foreign Key Relations between a Customer table and a Transaction table

CustomerID(PK) LastName FirstName Address City State

C41098X3 Carson Lewis 121 Center Street Seattle WA

CV1099B1 Madison Sarah 1324 Broadway Seattle WA

D345XU24 Brown Lisa 2201 Second Ave Seattle WA

TransactionID TransactionType TransactionDate CustomerID(FK) Amount

10002345 Deposit 2009-2-12 10:25:06 C41098X3 1245.76

10002346 Deposit 2009-2-12 10:27:13 CV1099B1 500.00

10002347 Withdrawel 2009-2-13-14:45:57 C41098X3 200.00

Another problem the relational design helped solve was the problem of relating data from different

parts of the database. In many of the previous database designs, a programmer had to write a routine in

a language like Fortran or Cobol to extract the data from various parts of the database and compare

them. In a well designed relational database every piece of data can be compared or joined with any

other piece of data. The relational design was a huge step forward in flexibility.

Hands ON Database

24

The chief drawback of relational database is the inherent complexity of the design. It is fairly easy to

design a bad database that will not do what a client needs it to do. The chief advantages, for a well

designed relational database are data integrity and flexibility. These two advantages have made it the

most commonly used database model for the past 30 years or so.

The Opportunity

They walk from Terry’s office down the hall to the computer lab. Terry stops at the front desk. “The

computer lab is one of our designated tutoring areas, and I suspect the one where most of your sessions

would be scheduled.” She picks up a clipboard containing several pieces of paper. “We have 2 pages for

each week an AM one and a PM one. At the beginning of the month, each tutor enters their availability

for each day, what times they are available that day, and what courses they can tutor for. “Students sign

up for particular sessions. Tutoring is free for the students as long as they are enrolled in the class for

which they are getting tutored.”

“How do you check that?”

“Right now it is mostly a matter of trust.”

“How long is each tutoring session?”

“Tutoring sessions are for 30 minutes each, and a tutor can only do 30 sessions or 15 hours a week.”

“What if you set up a time slot and nobody signs up?”

“As long as you show up when scheduled, we will pay you for the time. The pay, by the way, is $10.50 an

hour.”

Hands ON Database

25

“Thanks.” Sharon looks over the notebook. “Just out of curiosity, what do you do with the schedules at

the end of the month?”

“Actually, I take them back to my office ever two weeks and type it into various spreadsheets to make

reports to the people who pay for the tutoring, and to determine the pay for the tutors themselves.”

Sharon turns to Terry and says, “You know, you could really use a database. It would make it much

simpler to track schedules and availability and it could make

doing your reports much easier.”

Terry sighs. “I’ve known that for some time, but we just can’t find

anyone willing to take on the task. The school’s database

administrator is much too busy and no one else feels competent

or has the time to take on the task.”

Sharon hesitates a little, then says, “I might be able to put a

database together.”

Terry looks hopeful. “Really? That would be wonderful. We even

have some money in our budget so we could pay you something

for your work.”

“I am still learning database,” Sharon cautions, “but I am pretty

sure I could make you something that would meet most of your

needs.”

“Good, why don’t you come by tomorrow about this time and we will talk about it.”

“I will be there.”

Things to think about

There are many situations that

could be improved with the

addition of a database. Whenever

there is a large amount of complex

data to handle, a database is likely

to provide the best solution.

There are times, however, when the

data involved is more modest in

scope and complexity, that a

relational database may be overkill

Relational database s are complex

to develop and maintain.

The benefits when dealing with

large amounts of data are worth

the costs in development time and

maintenance. But sometimes, the

best solution is simply a

spreadsheet such as Excel.

Hands ON Database

26

Things you should know

RDBMS

A Relational Database Management System (RDBMS) is, as its name suggests, a system for managing

relational databases. As a minimum an RDBMS needs to allow a user to create one or more databases

and the objects associated with that database such as tables, relationships, views and queries. It also

needs to support basic maintenance such as backing up the database and restoring it from a back up

file. It also needs to support security making sure that users and groups have access only to the

databases and data that they are authorized to use.

Most commercial RDBMSs offer many features beyond these basic ones. Most include tools for

monitoring and optimizing the performance of their databases. Many include reporting services to

format and present the results of queries. Some even include complex Business Intelligence Packages for

analyzing business trends and patterns. Below is a table of the most common RDMSs with a link to their

home web sites.

Table 1

RDBMS Comments URL

ORACLE The first commercial RDMS and the biggest. Powers many of the world’s largest companies

http://www.Oracle.com

SQL Server Microsoft’s RDMS product. Ships in many versions designed for different company needs. Also powers many large enterprises

http://www.microsoft.com/sql/default.mspx

DB2 IBMs RDBMS http://www306.ibm.com/software/data/db2/9/

MySQL The most popular Open Source RDBMS currently

http://www.MySql.com

http://www.oracle.com/
http://www.microsoft.com/sql/default.mspx
http://www306.ibm.com/software/data/db2/9/
http://www.mysql.com/
Hands ON Database

27

owned by SUN ACCESS Microsoft’s Desktop

Database http://office.microsoft.com/en- us/access/default.aspx?ofcresset=1

Getting the Scope

After Sharon leaves Terry, she goes to one of the instructors, A professor named Bill Collins from whom

she hopes to get a recommendation. He is setting in his office and smiles when he opens the door for

her. “Come on in. How can I help you today?” She tells about her plan to tutor and asks for a

recommendation. He says he will be happy to provide one. Then Sharon tells him about the possibility of

making a database.

She says, “I’ve got a thousand ideas about how the database should look and what should be in it.”

Things to watch out for

It is easy to get carried away with your own excitement about a database project. You may be able to

see several possible solutions and want to start designing right away. But it is critically important that

you delay designing until you have a clear idea of what client wants and needs. Patience and the ability

to listen are among the most important skills of a database developer.

Bill cautions her, “Be careful not to get ahead of yourself. You need to remember you are not making

this database for you. You are making it for a client. You need to listen carefully to what Terry and the

other people who will use the database say about what they need and not get trapped by preconceived

notions. The first thing you need to do is get as clear an idea about what the database is intended to do

as possible.”

http://office.microsoft.com/en-us/access/default.aspx?ofcresset=1
http://office.microsoft.com/en-us/access/default.aspx?ofcresset=1
Hands ON Database

28

“A statement of scope?”

“Yes, that would be a good place to start, but I would

go farther and make a complete statement of work.

That would include the scope, but it would also

contain some discussion of the background, the

objectives of the project and a tentative timeline. I

have some samples I can share with you. Listen, if you

need any help or advice on this project, feel free to

ask me.”

“Thank you. Thank you very much.”

Things You Should Know

Statement of Work A statement of work is a preliminary document that describes, in general, the work that needs to be

done on a project. Often this is prepared by the people who want the work to be done and offered to

contractors to for bids. But sometimes, as in this case, it can be used as an initial clarification of task at

hand.

It is important to have something like a statement of work for any major project so that everyone knows

what is expected. Without it, people often find, sometimes late in the process, that different individuals

have very different expectations about what the project should contain. A statement of work is also a

good reference throughout the project to keep everyone on track and focused. The statement is

preliminary and can be altered as the needs of the project change or grow. But, by referring to the

A statement of scope is a short

statement of one or more

paragraphs that says in clear, but

general, terms what the project will

do. A Statement of work is a more

complete statement about the

objectives and timeline of the

project

Hands ON Database