HANDS ON DATABASE
by Steve Conger
© 2010
Hands ON Database
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
2
Hands ON Database
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
3
Hands ON Database
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
4
Hands ON Database
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.
5
Hands ON Database
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
6
Hands ON Database
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.
7
Hands ON Database
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.
8
Hands ON Database
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.
9
Hands ON Database
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
10
Hands ON Database
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
11
Hands ON Database
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
12
Hands ON Database
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
13
Hands ON Database
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
14
Hands ON Database
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.
15
Hands ON Database
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
16
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
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?”
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.
“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
17
Hands ON Database
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
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
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
Data Integrity—refers to the
accuracy and the correctness of the
data in the database
integrity and they often contain many redundant elements.
Spreadsheets, such as Excel, can also be used as flat file
Redundancy—refers to storing the
same data in more than one place in
the database
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
18
Hands ON Database
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
19
Hands ON Database
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.
20
Hands ON Database
C:\Users\ITStudent\Documents\myfile.txt
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?
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
21
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
Hands ON Database
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
22
Hands ON Database
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)
C41098X3
CV1099B1
D345XU24
TransactionID
10002345
10002346
10002347
LastName
Carson
Madison
Brown
FirstName
Lewis
Sarah
Lisa
TransactionType
Deposit
Deposit
Withdrawel
Address
121 Center Street
1324 Broadway
2201 Second Ave
TransactionDate
2009-2-12 10:25:06
2009-2-12 10:27:13
2009-2-13-14:45:57
City
Seattle
Seattle
Seattle
CustomerID(FK)
C41098X3
CV1099B1
C41098X3
State
WA
WA
WA
Amount
1245.76
500.00
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.
23
Hands ON Database
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.”
24
Hands ON Database
“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.”
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.
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.”
25
Hands ON Database
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
ORACLE
SQL Server
DB2
MySQL
Comments
The first commercial
RDMS and the biggest.
Powers many of the
world’s largest companies
Microsoft’s RDMS
product. Ships in many
versions designed for
different company needs.
Also powers many large
enterprises
IBMs RDBMS
URL
http://www.Oracle.com
The most popular Open
Source RDBMS currently
http://www.MySql.com
26
http://www.microsoft.com/sql/default.mspx
http://www306.ibm.com/software/data/db2/9/
Hands ON Database
ACCESS
owned by SUN
Microsoft’s Desktop
Database
http://office.microsoft.com/enus/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.”
27
Hands ON Database
“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
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
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
28
Hands ON Database
statement of work, you can guarantee that any changes or additions are a matter of discussion and not
just assumed by one of the parties.
The following table delineates a few of the elements that can appear in a statement of work.
Table 2: Statement of Work Elements
Element
History
Scope
Objectives
Tasks and
Deliverables
Description
Describes the reason for the project, usually a problem with the current system or
an opportunity to provide new services. May describe the various steps and efforts
that led to the current state of the project.
Provides a general statement of the requirements and expectations of the project.
It states only the high level requirements and does not get into specifics. It does
not go into detail about how things are to be done. It may include some general
constraints such as time or budget limits.
The things the project is intended to achieve. Objectives aren’t about creating
specific elements of the database, for instance, but about what the database is
supposed to achieve, why the client wants the database in the first place.
Breaks the project into discrete tasks. Each task should have an estimated duration
and concrete deliverables.
The First Interview
The next day Sharon sits in Terry’s office. She has brought a notebook to take notes. She has written
down some of the key questions she knows she will need to ask. Sharon knows it is important to be
prepared and focused for any interview. She has also brought a diagram of a database she created for a
non-profit to show Terry as an example of database work she has done.
Terry says, “Thanks for coming in. You have no idea how long and how much we’ve wanted a database
for the tutoring program. We have to generate several reports each term to justify our funding. It has
gotten so that it takes most of my time. It keeps us from doing things to improve the program. We also
really need to be able to track what works and what doesn’t better.”
29
Hands ON Database
Sharon nods, “I really hope I can help. I brought an example of a database I made for Capital Charities to
show that I do have some experience creating databases. We did this as part of a project for a Database
class”
30
Hands ON Database
Figure 5: Sample Entity Diagram for a Non-Profit
Terry looks at the diagram as Sharon explains it.
31
Hands ON Database
“Capital Charities provides funds for basic utilities, food and occasional repairs for poor families on a one
time, emergency basis. They needed to be able to track
their contributors and their contributions. That was one
part of the database. That data is stored in the contributor
and contribution tables. That line between them indicates a
one-to-many relationship. It uses what is called “crows
Crows Feet Notation: A type of
Entity Relation Diagram where the
relationships are depicted using
lines and 0’s. These are more
descriptive of relationships than the
diagrams using simple arrows.
feet” notation. It shows that each contributor has
contributed at least once and may have contributed many times. The crows foot, those three lines,
points to the many side of the relationship. The other part of the database tracks the types and amounts
of assistance given to each client. The client information is entered into the Client table.”
She points to the ClientNotes entity, “There can 0 or many notes about any client. Each client receives
assistance at least once. That was a business rule of the charity. They only wanted to list as clients those
they had actually given assistance to. Each act of assistance is associated with a particular councilor and
can involve several different types of assistance. That is the reason for the AssistanceDetail table.”
“It looks complex.”
“It is a little. But I also built some forms and reports that made it so the Capital Charities staff didn’t have
to navigate the database directly. It made it a lot easier to use.”
“Well it certainly looks like you should be capable of doing this for us. What do you need from me?”
You have already started suggesting some of the things I want to talk about today—things you want the
database to do. What I need to get from you today is a clear sense of what you want the database to do
for you. I don’t need specifics yet, just general statements of what you want to see and what the
database needs to do to be useful to you.”
32
Hands ON Database
Terry hesitates, “Ok…Where do I start.”
“You already suggested a couple of things. You need to track what works and what doesn’t. How would
you determine that something is working or not working?”
Things you should know
You should always go to an interview prepared. In this initial interview, you should be prepared to help
them get started on the right track and have questions that help focus them on the important aspects of
the database. But you don’t want to guide them toward some preconceived notion of what the
database should be. Rather, your questions should help them guide you to a clearer understanding of
what they need out of a database
“Well, part of it is how many students are using the tutoring services. What courses are they taking
tutoring for and how the tutoring they receive helps them succeed in their courses. Do they get better
grades? Does tutoring stop them from dropping the class? I know these are a bit vague and difficult to
track.”
“That’s OK. What about scheduling tutors and students. What do you need to track to do that?”
“Well, we need to track tutors, of course, and what classes they can tutor for. We need to track the
tutor’s schedules so we know what times they are available. We need to know which students sign up
for each session and ideally we should be able to check that they are actually taking the course for which
they are getting tutoring.”
“Do you need to track demographic information for students?”
33
Hands ON Database
“If we could, that would be great. It would make our reporting much easier. Several of our grants are
targeted at particular groups of students. We would have to guarantee that such information would
remain private.”
“What other reports do you need to make.”
“I need to know how many hours each tutor worked in a pay period. I need to know how many students
each tutor saw. I also need to know how many unduplicated students were seen each term.”
“Unduplicated?”
“Yes, individual students. A single student could get several sessions of tutoring. For some reports we
need to know how many individual students we are serving—not just how many sessions we have
scheduled.”
“Can you think of anything else?”
“We really need to know if a student actually got the tutoring they signed up for. Sometimes a student
will sign up and then not show for the actual session. It might also be good to know what courses
students want tutoring in where we are not offering it. Maybe you could provide a way for students to
request tutoring for courses or subjects.”
“Anything else?”
“Nothing I can think of right now.”
“OK. What I am going to do is to take this and write up a statement of work describing the database, the
objectives and a tentative time line. Then we can look at it and see if it really describes the database you
need. If it doesn’t we can adjust it. When it does, we can use it to refer back to keep us on track so that
we don’t get lost in the details later.”
34
Hands ON Database
“Thanks,” Terry stands up. “I actually think we can do this. You really seem to know what you are doing.
I am looking forward to it.”
Sharon smiles, though she doesn’t feel nearly as confident in her abilities. “I am looking forward to it
too.”
Identifying the Big topics
Sharon goes to the school cafeteria and gets a cup of coffee. She sits down to go over her notes. She
knows it is important to review them while the interview is still fresh in her mind. The first thing she
needs to do is to identify the big topics. What is the database about? What are the major components
going to be? “Well, tutoring,” she says to herself, “that is the big topic.” But what does tutoring include.
She takes out a pencil and starts a list, “Tutors, of course, and students and the tutoring schedule.” She
writes them in the list:
tutors
students
tutoring schedule
“Is there anything else? Anything I am missing?” She frowns as she concentrates for a moment.
“Courses! Tutors tutor for specific courses and students are supposed to be registered in those courses
in order to get tutoring.” She adds it to the list. Students also should be able to request tutoring for
specific courses. She adds Requests to the list.
tutors
students
tutoring schedule
courses
requests
35
Hands ON Database
She thinks a bit longer. “We need to track whether students attended the sessions they scheduled. That
is important, but is it a new topic? It could be part of scheduling.” Terry wanted one more thing, she
remembers. She wanted to track student success. To Sharon that seems like a different topic entirely.
She recalls that Bill Collins in his class always insisted that a good database like a good table should be
focused on a single topic. She decides to leave the list as it is.
Things you should know
Identifying the major topics of a database is an important
Entities—an entity is something
that the database is concerned
with, about which data can be
stored, and which can have
relationships with other entities.
Attributes—are things that define
entities (The entity customer has
attributes like name and address)
exercise. It helps provide a clearer sense of just what the
database is about. It also is the first step toward identifying the
“entities” that will be used in the database design.
One way to begin identifying the major themes is to look at
the nouns in your notes. See if they cluster together around
certain themes. These themes are most likely the major topics
of your database. We will look at this technique more closely later when we talk about defining entities
and attributes.
It is important to note that a database may contain several themes, but all those themes should relate
to a single overarching topic like tutoring. If there is more than one overarching topic, it may indicate
that that you should develop additional databases.
Writing the Statement of Work
36
Hands ON Database
Now that she has the big topics in mind, she begins to compose the Statement of Work. She begins with
the History. The history is a statement of the problem. It can narrate how the current situation came to
be the way it is. Sharon thinks about the things she saw and the things that Terry told her.
For a long time the tutoring program has used a paper schedule to sign students up for tutoring.
Tutors identify their schedule for a two week period and then a schedule is printed and placed in
the computer lab. Students look through the schedule for sessions that match courses they are
taking and the times they have available. This system has worked and continues to work, but it
has several significant problems. For one, it can be difficult for students to find appropriate
tutoring sessions. The paper forms are difficult to navigate and understand. Additionally, it is
very difficult for the tutoring program to track the students using the tutoring. It is difficult or
impossible to track demographic information. It is also difficult to assure that students are
enrolled in the courses they receive tutoring in. Even tracking tutors’ hours can be difficult.
A database with a client application could significantly improve the situation, by providing a
flexible, searchable schedule for students, better tracking of demographics and eligibility, and
better tracking of hours tutored.
She pauses. That was hard to get going, but once she got started it flowed pretty well.
The tutoring database will be designed to manage the tutoring program at the college.
She isn’t real happy with that as an opening sentence. She modifies it a little and forges ahead. It proves
to be a lot harder than she imagined. The statement has to include all the general points but still be
concise enough to give a clear indication of the purpose and functions of the database. After a lot of
effort she had this preliminary statement:
37
Hands ON Database
The tutoring database will manage data for the tutoring program at the college. It will track
available tutors and the courses they can tutor. It will also track each tutor’s tutoring schedule.
The database will store demographic
information for students who register for
tutoring. This information will be private and
used only generate general reports which
include no personal information. Students, who
Constraints ore limits on what the
database will do. Later we will see
that you can also set constraints on
the types and range of data that
can be entered into a column in a
table
have registered, will be able to sign up for
available tutoring sessions for courses in which they are enrolled. The database will track
whether students attended their scheduled sessions.
Sharon looks it over carefully. What about the data about student success? Should that be a part of this
database, or should that be a separate project? She decides to set it aside until she has talked with
Terry.
She also wonders if she should state some of things the database won’t do. Things like:
The database can be used to get the hours worked for each tutor, but it will not process pay or
payroll information.
The database will not validate student information against the school’s registration database.
For the moment, she can’t think of any other constraints.
She consults an example her instructor gave her to look at. The next step is to set out the objectives for
the database. She spends some time thinking about this. Most of the objectives are spelled out in the
scope. She pulls out some of the main points and makes a list.
38
Hands ON Database
Streamline the process by which the tutors enter their schedules and students sign up for them
Improve tracking of demographic data of students using the tutoring program
Improve tracking of tutor’s hours and students use of tutoring sessions
Next she needs to add tasks and a timeline. She jots down some notes on a paper. The first thing she will
have to do is to gather information. She needs to know all the relevant data and processes. How long
will that take? She makes a rough guess of two to three weeks. Then she will have to evaluate all the
information she has gathered and use it to start developing a list of business rules and first rough model
of the data. That could take another couple weeks. Next she will have to refine and normalize the
model. Sharon thinks she can do this in two or three days. Then she needs to actually make the
database. That won’t take long. She can probably do that part in a couple of hours. What then? Sharon
muses for awhile. The last part may take a fair amount of time. She will need to test the database and
make sure that it meets all Terry’s needs. She will also have to test for security issues and privacy. That
could take two or more weeks of intense work. Where does that put her? Sharon calculates and taking
the longer times in each case comes up with nine or ten weeks. None of this is counting the fact that it
will take a completely different development project to create a client application for Terry, the tutors
and students to interact with the database. But, Sharon says to herself, one project at a time.
39
Hands ON Database
Sharon almost has everything she needs for the statement of work,
Things to Think About:
Estimating Times
One of the most difficult things for
anyone who is new to developing
databases is estimating the time it
will take to complete the various
tasks. Experience will help, but
before you have enough experience
how to do you even begin to guess
an appropriate time?
There are some techniques that
can help. One is to make a
weighted average. To do this write
down your most optimistic time—if
everything goes perfect--your best
guess at the probable time it will
take, and your most pessimistic
time estimate—if everything that
can goes wrong. Add them all
together, but multiply your most
probable estimate by 3, then divide
the sum by five.
(0 + Pt x 3 + p)/5
What other ways can you think of
to help your time estimates be
more accurate?
but there is still something missing. After a while it occurs to her:
every task should also have a deliverable, something concrete she
can show Terry to let her know that the database is on track.
Sharon spends the next couple of hours completing her statement
of work.
Reviewing the Statement of Work
The following afternoon she returns to Terry’s office and shows her
the statement. As Terry looks it over Sharon says, “It is important
that we both are clear about what we are working on. I don’t want
to go off and make a database and then find out it is not what you
had in mind at all.”
“No, I can see that is a really good idea.” She sets the paper down.
“What about the surveys of student success?”
“I thought about that, and I am not sure. Sometimes I think that does belong in this project, and other
times I think that it is a separate project on its own. I am not sure how we could get objective data on
their success but we could include evaluations by students or a quarterly survey. If we build the
database as I have described it we should be able to add the success tracking features later or we could
look at adding a second database devoted to tracking student success.”
“Ok, I can live with that. It would be nice if you could validate student information.”
40
Hands ON Database
“Yes, but I don’t really know how to do that. I also think it unlikely that I would be granted the
permissions I would need on the School’s registration database. You might be able to get the school’s
developers to look at that piece later.”
“Fair enough. One other thing you don’t have here, and I am not sure we talked about it, but it would be
nice if students could request tutoring in courses that we don’t currently have tutors for. It would help
us know where the need is and where we need to try to recruit new tutors.”
“That shouldn’t be a problem. I can add that.”
“Good. What do you need to proceed?”
“Well, let’s go over the tasks and timeline. First, I am going to need to gather some information. I am
going to need to see how you have been doing things. I will need to talk to some tutors, and maybe
some students, and I probably need to see the reports you make to make sure that the database
contains all the information you require. Then I will need to analyze all the information I get and begin
to make a data model. After all that I can actually make the database and test it.”
Terry studies the timeline. “This is very clear and well done. How realistic do you think this timeline is.”
Sharon smiled. “It represents my very best guess. It could go faster if everything works out well, but it
could also go slower if I encounter problems. I tried to be very conservative on the times, so I think there
is a good chance it can be completed on schedule.”
“Good, it would be ideal if the database could be in place by the beginning of next term.”
Sharon warns, “There is another piece to all this. There will need to be client application developed so
you, the students and tutors can interact safely and easily with the database. But that is really a
separate project.”
41
Hands ON Database
Terry smiles. “You’re right. We can tackle that when we have finished with the database.”
“Tell you what, I will come by tomorrow with a revised version of this statement, and I will give you a
preliminary plan of where we go next.”
Terry stood up and put out her hand to shake. “Sounds good. I look forward to working with you on
this.”
The Statement of Work
Home, later. Sharon revised the statement of work to include student requests. Here is her completed
statement of work:
STATEMENT OF WORK: TUTORING DATABASE PROJECT
HISTORY
For a long time the tutoring program has used a paper schedule to sign students up for tutoring. Tutors
identify their schedule for a two week period and then a schedule is printed and placed in the computer
lab. Students look through the schedule for sessions that match courses they are taking and the times
they have available. This system has worked and continues to work, but it has several significant
problems. For one, it can be difficult for students to find appropriate tutoring sessions. The paper forms
are difficult to navigate and understand. Additionally, it is very difficult for the tutoring program to track
the students using the tutoring. It is difficult or impossible to track demographic information. It is also
difficult to assure that students are enrolled in the courses they receive tutoring in. Even tracking tutors’
hours can be difficult.
A database with a client application could significantly improve the situation, by providing a flexible,
searchable schedule for students, better tracking of demographics and eligibility, and better tracking of
hours tutored.
SCOPE
The tutoring database will manage data for the tutoring program at the college. It will track available
tutors and the courses they can tutor. It will also track each tutor’s tutoring schedule. The database will
store demographic information for students who register for tutoring. This information will be private
and used only generate general reports which include no personal information. Students, who have
registered, will be able to sign up for available tutoring sessions for courses in which they are enrolled.
The database will track whether students attended their scheduled sessions. It will also track student
requests for tutoring in additional course and subjects.
42
Hands ON Database
Constraints
The database can be used to get the hours worked for each tutor, but it will not process pay or payroll
information. The database will not validate student information against the school’s registration
database.
OBJECTIVES
Streamline the process by which the tutors enter their schedules and students sign up for them
Improve tracking of demographic data of students using the tutoring program
Improve tracking of tutor’s hours and students use of tutoring sessions
Track Student requests for additional tutoring
TASKS AND TIMELINE
1. Gathering Data: This task will consist in a number of interviews, questionnaires and
observations. Time allotted 3 weeks.
Deliverable: A list of scheduled interviews and observations, text of the questionnaires.
2. Analyzing Data: The data gathered will be analyzed to determine business rules and preliminary
data modeling. Time allotted 2 weeks.
Deliverable: List of business rules to be reviewed, basic entities and attributes.
3. Normalization: the data model will be completed with entities and relationships normalized.
Time allotted 1 week.
Deliverables: Entity Relation Diagram for Review.
4. Building the physical database: The data model will be translated to the Relational Database
Management system. Tables, Columns with specific data types and Relational and other
constraints created. Time allotted 3 days.
Deliverables: The Schema of the database for review.
5. Testing and security; Sample data will be entered and each of the business rules and
requirements will be tested. General database security and security related to business rules will
also be tested. Time allotted 3 weeks.
Deliverables: Documented test results.
6. Database Completion and installation: final changes and corrections are made. Sample data will
be removed and the database installed on a server. Final testing for server access and
connections. Time allotted 2 weeks.
Deliverables: The working database.
Total time between beginning of project and end: 11 weeks, 3 days.
43
Hands ON Database
Things we have done
In this chapter we
identified a situation in which a database could prove valuable
reviewed briefly the history of databases
identified some of the components of relational databases such as entities and key fields
observed an interview to gather general information about a database
broke the general information into major topics
used the major topics to develop a statement of work for the database
Vocabulary
Match the definitions to the vocabulary words
1. Attribute
2. Foreign Key
3. Statement of work
4. Primary Key
5. Data integrity
6. Redundancy
7. Delimited files
8. Relational Database
9. Entity
10. Relational Design
11. SQL
12. Constraints
a. A type of database that uses “relations,” tables, to store and relate tables.
44
Hands ON Database
b. The process of organizing data into tables or entities and then determining the relations among
them
c. The language Relational Databases Use to create their objects and to modify and retrieve data.
d. These 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.
e. Files where the length in characters of each column is the same.
f.
Refers to the accuracy and the correctness of the data in the database.
g. Refers to storing the same data in more than one place in the database.
h. This key uniquely identifies each row in the table.
i.
This key is the primary key repeated in another table to create a link between the tables.
j.
A short statement of one or more paragraphs that says in clear, but general, terms what the
project will do.
k. Something that the database is concerned with, about which data can be stored,
l.
Things that define aspects of entities
m. Limits on what the database will do.
n. A document including the scope, objectives and timeline for a given project
Things to look up
1. Look up Codd’s twelve rules. Choose one of the rules to explain to your fellow students.
2. Look up the history of SQL. How many revisions of the standard have there been? What was
added in the most recent one?
3. Use the internet to look up Database related jobs. Make a brief report summarizing what you
find.
45
Hands ON Database
4. A recent trend for major commercial Database developers is to offer free “Express” versions of
their databases. Microsoft has Sql Express, Oracle has Oracle Express and DB2 has DB2 Express.
Go to the company web sites and look up these Express editions. What features does each one
have? What limits do they have? How do they compare to each other?
5. For some time there have been attempts to move beyond Relational databases, to find some
new data model. One direction has been to move toward Object Oriented Databases. Another
area of research is into XML based databases. Choose one of these to look up and write a brief
summary of what the model entails and what is the current status of the model.
6. Look up Statements of Work. What are some additional elements that can be included?
Practices
1. Think about keeping a home budget. Would it be better to keep the budget in spreadsheets or
to create a budget database? Write a couple of paragraphs that describe your choice and at
least three reasons to justify it.
2. An Entity is something the database is concerned with. For instance, a movie rental business
would probably have an entity called DVD. Attributes are things that describe the entity. Make a
list of possible attributes for a DVD entity.
3. You are going to interview a small business owner about creating a database for his sandwich
shop and bakery. Make a list of questions for this initial interview. Remember at this point you
just want the big picture and major requirements. Don’t get too deep into the details.
4. Look around the school or think of some businesses or non profits with whom you are familiar.
Identify at least one situation in which a database could be a help.
a. Describe why a database would improve the situation.
b. Describe what the major topics of this database would be
46
Hands ON Database
c. Write a statement of work for this database
5. An instructor has been keeping all his grade books in Excel for years. He has a separate
spreadsheet for every course. In the spreadsheet he tracks the scores for every assignment and
test and then assigns term grades based on the overall averages. Whenever a former student
contacts him requesting a letter of recommendation or whenever the administration requests
information concerning a student in a previous term he has to open and search several
spreadsheets to get the student’s information.
a. What are some of the advantages a database would have over the current system for
this instructor?
b. What would be some of the major topics for the database?
c. Write a statement of work for the database above.
Scenarios
These scenarios are designed to give you the opportunity to experience database development from
beginning to end. Each has its own unique challenges. They can be pursued individually or in small
groups. I would suggest choosing one scenario that interests you to follow throughout the term. Later, if
you are so inclined you can return and work through some of the others.
Wild Wood Apartments owns 20 different apartment complexes in Washington, Oregon, California, and
Idaho. Each apartment complex contains anywhere from 10 to 60 separate apartments, of varying sizes.
All apartments are leased with a six month or yearlong lease.
47
Hands ON Database
It is the company’s practice to hire one of the tenants to manage each apartment complex. As managers
they need to admit new tenants to the building, collect rents from existing tenants, and close out leases.
The manager also needs to maintain the apartments conducting any repairs, replacements, or
renovations. These can be billed back to the parent company. For acting as manager, the tenant gets
free rent and a stipend. The stipend varies depending on the size of the apartment building.
Each manager is expected to send a report to the Wild Wood Apartments company headquarters in San
Francisco every quarter. This report summarizes the occupancy rate, the total revenues in rent, the total
expenses in maintenance and repairs, etc. Currently managers fill out a paper form and mail it back to
headquarters. Many apartment managers have complained that preparing this report is a very difficult
and time consuming process. Also, the managers at corporate headquarters have expressed concerns
about the accuracy and verifiability of the reports.
To allay these concerns and to improve the ease and efficiency with which the apartment managers
conduct their daily business, the company is proposing to development a centralized database that can
be used by the managers to track the daily business of their apartment building and to prepare their
reports.
To do
1. List the major topics for this database
2. Write a draft statement of Work. Include a brief history, a statement of scope, objectives and a
preliminary timeline.
48
Hands ON Database
Vince Roberts runs a vintage record shop in the University district. His shop sells 45’s, LPs and even old
76 RPM records. Most of his stock is used—he buys used vinyl from customers or finds them at yard
sales and discount stores--but he does sell new albums that are released on vinyl. For a couple of years
he has kept most of his inventory either in his head or in a spiral notebook he keeps behind the sale
counter. But his inventory and his business have grown to where that is far from sufficient.
Vince is looking for someone to make him a database. He knows he needs to get a better handle on
several aspects of his business: he needs to know the extent and condition of his inventory. He needs to
know the relative value of his inventory—some records are worth a fortune; some are nearly worthless.
He also needs to track where, from whom and for how much he purchased his stock. He needs to track
his sales. He often is not entirely sure how much money he has spent or how much money he has
earned.
In addition he would like to allow customers to make specific requests and notify them if a requested
item comes in. More generally he would like to make an email list of interested customers in order to let
them know about new items of interest.
Someday, he would like to expand his business on-line. But he knows he needs to have everything under
control before then.
To do
1. List the major topics for this database
2. Write a draft statement of work. . Include a brief history, a statement of scope, objectives and a
preliminary timeline.
49
Hands ON Database
The law requires that any business, including a school track its software. It is important to know what
software the school owns, in what versions, and what the license agreement for that software is. There
are several different licensing schemes. The least restrictive is a "site" license which allows an institution
to have a copy of the software on any machine on the business property. Other licenses specify a certain
number of active copies for an institution but don't worry about which machine or user has the copy.
The more restrictive licenses do specify one copy per a specific machine or user.
Whatever the license agreement for particular software, it is essential for the institution to know which
software is installed on which machine, where that machine is located, and what users have access to
that machine. It is also important to track when the software is uninstalled from a machine, and when a
machine is retired.
An additional useful feature of any software tracking database would be to track software requests from
users to determine 1) if a copy of the software is available, and 2) if it something that should be
purchased. All installations are reviewed and must be approved.
For now the school just wants the database to track faculty and staff computers and software. Software
for student machines is a separate and complex issue and will be treated as a separate project at a later
time.
To do
1. List the major topics for this database
50
Hands ON Database
2. Write a draft statement of work. Include a brief history, a statement of scope, objectives and a
preliminary timeline.
A hospital is conducting a double blind test of a new depression drug. It will involve about 20 doctors
and about 400 patients. Half of the patients will get the new drug and half will get traditional Prozac.
Neither the doctors nor the patients will know who is getting which drug. Only two test supervisors will
know who is getting what. The test will last about 18 months. Each doctor will see 20 patients initially,
though it is expected some patients will drop out over time. Each patient will coming in twice a month
for a checkup and interviews with their doctor. The drugs will be dispersed in a generic bottle by the two
supervisors one of whom is a pharmacist.
To track this study the hospital will need a database. It will need to track patient information from their
first screening through each of their interviews. In particular they are looking at whether the patient
seems more depressed or less, what their appetite is like, are they sleeping, what kind of activities they
are engaged in if any. Also it will be looking for specific physical side effects such as rashes, high blood
pressure, irregular heart rhythms or liver or kidney problems.
51
Hands ON Database
Doctors need to be able to see their own patient’s information, but not other doctor’s patients. They
also need to be able to enter blood pressures, blood test results etc, the depression indicators and their
own notes for each session
Patients should be able to see their own medical profile, the doctor’s notes, and nothing else.
Only the two researchers should be able to see everything. All patient information, all doctors notes and
which drug each patient is being given.
There is always some danger of spying by other companies interested in similar drugs, so in addition to
the security of the blind test, the database needs to be secured against outside intrusion as well.
To do
1. List the major topics for this database
2. Write a draft statement of work. . Include a brief history, a statement of scope, objectives and a
preliminary timeline.
52
Hands ON Database
Chapter Two: Gathering Information
(Interviews, Observations and Reviewing Documents)
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 sheets 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 and a couple of students.
As a follow up she creates a questionnaire for students who use the 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
Looking at the Documents
Sharon has arranged to meet with Terry early in the morning. She arrives on time and Terry greets her.
“Let’s go look at how students sign up for tutoring now.”
53
Hands ON Database
Sharon follows Terry to the lab. On the counter of service station at the front of the lab there is a
clipboard with sign in sheets for tutoring. Each sheet is for one week. Across the top are the days of the
week. Down the left margin are times. Tutors mark the times they are available and what topics they are
tutoring by listing their name and the class they are tutoring for in a time slot. Students sign up for a
time slot.
Figure 6: Morning Tutoring Appointments
Tutoring for the Week of 4/12 to 4-16 2009
Monday
Tuesday
9:00 AM
TT:
TT:
CL:
CL:
ST:
ST:
------------------------------TT:
TT:
CL:
CL:
ST:
ST:
------------------------------TT:
TT:
CL:
CL:
ST:
ST:
Wednesday
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
Thursday
TT:Aimes
CL:(Math 290)
ST:Laura Jones
---------------TT:Carson
CL: (ITC 110)
ST:
--------------Johnson
(ITC 224)
Shanna Taylor
Friday
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
9:30 Am
TT:Johnson
CL:(ITC224)
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:Carson
CL: (ITC 110)
ST:Peter Laws
_________
TT:Johnson
CL: (ITC 224)
ST:Sara Lewis
---------------TT:
CL:
ST:
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:Johnson
CL:(ITC 224)
ST:Bob Green
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
10:00 AM
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
TT:Stevens
C:(Math 100)
ST:homas Seth
---------------TT:
CL:
ST:
---------------TT:
54
Hands ON Database
CL:
ST:
CL:
ST:
CL:
ST:
CL:
ST:
CL:
ST:
10:30 AM
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:Mary L
CL:(ENG 101)
ST:Ly Poon
___________
TT:Sanderson
CL:(ITC 110)
ST: Anderson
---------------TT:
CL:
ST:
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:Mary L
CL:(ENG 101)
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:Stevens
CL:(Math 100)
ST:Thomas
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
11:00 AM
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:Mary L
CL:(ENG 101)
ST: Snodgrass
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:Mary L
CL:(ENG 101)
ST:Martin Yang
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:Stevens
CL:(Math 100)
ST:Brown
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
11:30 AM
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:Mary L
CL:(ENG 101)
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:Mary L
CL:(ENG 101)
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
TT:
CL:
ST:
---------------TT:
CL:
ST:
---------------TT:
CL:
ST:
Sharon looks at the sheets. “I presume TT: stands for tutor and CL: for class and ST: for student. Is that
correct?
55
Hands ON Database
Tracy nods, “Yes that is correct.”
“Is this all the information you have about the tutoring sessions? How do you know if the student
showed up or not?”
Things You Should Know
Gathering Information
Before you can actually begin designing a database, you must understand what data the database needs
to store and how that data will be used. It is tempting to think you understand the gist of what is going
on and start sketching out tables and columns, but it is always better to wait. Gather information. Make
sure that you understand exactly what the customer needs to store in the database and why.
Gathering information is complex task. Most projects have many facets that need to be accounted for. It
can be quite daunting, but there are some basic steps to help you proceed.
initial interviews with the chief stakeholders (the managers or executives that are initiating the
database project
Review of business documents to identify data elements
interviews with stakeholders
Questionnaires
Work shadowing (observing the flow of
Work Shadowing: following and
observing person as they go through
their work routine
information)
The initial interview should provide the overview of the
Domain: the focus of the database.
If the database is about the tutoring
program, its domain is “tutoring”
database. In it you define the domain of the database, that is
what business tasks and information the database is meant to
56
Hands ON Database
handle. You may get a few specific requirements in
this initial interview, but the primary goal should be
to get a clear picture of why the database is needed
Requirement: Something the
database must do. For instance, It
must keep track of tutors and the
classes they can tutor for.
and what, in general, it is meant to do.
One of the first tasks should be to review any business documents. Business documents consist of forms
and reports related to the data, but can also include things like memos, organizational charts, mission
statements, company goals, plans etc. Reviewing documents allows you to begin to make a list of what
kind of content your database will have. It is important to ask about any abbreviation or item you don’t
understand.
Next you should identify the chief stakeholders. A stakeholder is anyone who will interact with the
database directly or indirectly. A stakeholder is anyone who has a “stake” in the results. Stakeholders
include the managers and the employees who will work with database. They probably also include IT
staff who will develop, maintain and support the database. They may also include direct customers and
business partners.
Once you have identified stakeholders, you should arrange
interviews with each group or possibly with all the
stakeholders together. The purpose of the interviews is to
Stakeholder: Anyone with a “stake”
in the final product. Anyone who
will use or be affected by the
database
get each stakeholders perspective on what data the
database needs to store for their use and how they will need to process that data.
Questionnaires may be more efficient to gather some types of information. You can often get responses
from more people than in an interview. Questionnaires are best for technical information and close
ended questions that require simple straight forward answers.
57
Hands ON Database
Finally, it is extremely valuable to watch how people work with the system they currently have. You can
observe the “flow” of the data, how it is captured, how it is used. It is also a valuable way to discover
exceptions to the rule. “Oh, we always give Mr. Johnson a
discount, he has been such a good customer.” or
“Sometimes we wave the fee. It is up to the clerk.” If your
Exception: A variation in how things
are done or recorded, an alternate
process
database doesn’t allow for common exceptions it may
prove too rigid to be useful.
“I use these sheets but I also have the tutor’s reports. Each tutor is supposed to fill out short little report
form for each session time they sign up for. In fact, the reports are my primary source of data. The
signup sheets are just a check to make sure that I have all the report forms. Some tutors are a little lax
about turning them in.”
“Do you have any of those forms that I could look at?”
Terry smiles, “Of Course. “ She walks behind the desk. “We keep the forms here for the tutors.”
Sharon takes the form and looks at it briefly. “It seems simple enough.”
Terry nods. “It is quite simple. We wanted the tutors to focus on tutoring not on paper work.”
Figure 7: Tutor session Report Form
Tutor Session Report Form
Tutor Name
Session Date
Session Time
StudentID
Student Name (NA if no
student signed up)
Materials covered (NS if
no show)
58
Hands ON Database
Sharon asks, “Does it give you the information you need to make your reports.”
Things to Watch Out For
Make sure you understand all the terms and abbreviations on the forms and reports you review.
Terry smiles wryly. “That’s difficult to say. I use them, but it’s certainly not easy to make my reports from
them.”
Sharon says, “Maybe you can show me some of the reports
Form: a document, paper or
electronic that is used to gather
data
Report: a document paper or
electronic used to display
summarized or formatted data
you need to make and explain what you have to do to
complete them.”
“No problem, let’s go back to my office.
Things You Should Know
Reviewing Business Documents
The forms and reports a business uses to gather and disseminate information are an invaluable source
for understanding several aspects of a business’ data needs. For one thing, they provide clear insights
into the daily business processes. They show how information is gathered about various transactions,
and then how that information is passed to other people and departments. Studying business
documents can reveal not only what information is needed, but when and in what sequence. Secondly,
carefully scanning the forms and reports will reveal many, perhaps most, of the individual pieces of data
the database will need to contain. Business documents can reveal how the data will be used, how it will
be summarized, analyzed and presented.
59
Hands ON Database
There are several kinds of basic business documents that can be relevant. Two of the most important
documents are forms and reports. Forms are documents, either on paper or on the computer that
businesses use to capture data. They are used to “input,” things like new customer information, sale
details or an employee’s hours. Reports are documents that present “output” from the system. They
summarize and analyze the data that was collected through forms and other means, or the current
status of inventory.
Several other types of documents can also be useful when trying to get a picture of the data a database
need to process. Manuals and procedures can give you a
sense of how things are processed, or, at least, how they
are supposed to be processed. Memos and letters can
provide some insight into issues that can arise in the
Procedures: documents that
describe the approved steps for
completing some business process.
A “How to” document.
system and also provide a sense of how the information
moves through an organization and who is responsible for what parts of the information. Annual reports
offer insights into the state of the organization and into what function the proposed database might
serve within the broader business context. Even mission statements and goals can be useful. A database
should be supportive of the mission and contribute to one or more of the stated goals.
In her office Terry logs into her computer and brings up Excel. She opens a spreadsheet. “Here is an
example of a simple time sheet.”
60
Hands ON Database
Tutor Pay
For weeks beginning
Tutor
Aimes, Tabatha
Carson, Karen
Johnson, Luke
Lewis, Mary
Sanderson, Nathan
Stevens, Robert
Totals
4/6/2009 and 4/16/2009
Week1
Week2
Total Hours
0.5
2
2.5
8
10
18
3
4.5
7.5
1
3.5
4.5
3
3
6
4
5.5
9.5
19.5
28.5
48
Wage
$ 10.50
$ 10.50
$ 10.50
$ 10.50
$ 10.50
$ 10.50
Gross Pay
$ 26.25
$ 189.00
$ 78.75
$ 47.25
$ 63.00
$ 99.75
$ 504.00
Figure 8: Tutor Pay Spreadsheet
Sharon looks over the spreadsheet. “You get the hours for each tutor by going over those signup sheets
and the Report forms?”
“Yes.”
“I imagine that can be labor intensive and error prone.”
“You can only imagine. I used to assign this task to a work study student. But, no matter how good they
were or how much I trusted them, I never felt confident until I had rechecked all the materials. So now I
just do the payroll report myself.”
“I think we can make this task a lot easier with a database and a lot more accurate. What other reports
do you have to make?”
“Well, one important report is total student Usage. For this I report the total of all sessions attended by
students in a term and then the unduplicated count of students”
“Unduplicated means you only count each individual student once. Is that correct?”
“Yes. We need to know how many total tutoring sessions are attended, but we also need to know how
many individual students are taking advantage of the tutoring.”
61
Hands ON Database
Tutorial Usage Term 1 2009
2345
2500
2000
1735
1500
1000
500
0
Total
Unduplicated
Figure 9: Total and Unduplicated Students
“Here are two other important reports. The first two charts cover demographics and the second for
what tutoring topics are most sought after.”
Unduplicated Student Count by
Gender
F
54%
M
46%
Figure 10: Gender Report
62
Hands ON Database
Unduplicated Student Count By
Ethnicity
NAmer
2%
PacIs
7%
other
5%
Asian
18%
white
50%
AfrAm
18%
Figure 11: Ethnicity Report
63
Hands ON Database
Unduplicated Students by Subject
Area
SCI
14%
BUS
7%
ENG
20%
HIS
8%
ACC
8%
ITC
8%
MAT
35%
Figure 12: Subject are Usage
Sharon looks at the charts carefully for a moment and then asks a question: “How do you get the
demographic information?”
Terry sighs, “It’s not always easy. As long as the tutor
Things to think about:
remembers to put in the studentID number, I can locate the
Are there other forms you would
have asked to look at, if you were
Sharon?
student on the School’s enrollment database. I can get their
What other kinds of forms could be
relevant to the tutor database?
gender and ethnicity information there. If there is no student
number for a particular student on any of the forms turned
in, I can usually locate a student on the School’s enrollment
database by searching for their last name and comparing that
with the classes they are enrolled in and what topics they are seeking tutoring in. The hardest part is
actually the unduplicated counts. I have to manually eliminate duplicates.”
64
Hands ON Database
“That sound like way too much work.”
“Believe me it is. But many of our grants depend on ethnicity reports. We must show that we are serving
a diverse population. Here is the actual spreadsheet I use to create the charts.”
Report Statistics
Fall Term 2010
Students
Total Usage
Unduplicated Usage
Difference
2345
1735
610
Workforce
retraining
Unduplicated Demographics
Male
Female
Total
937
798
1735
Ethnicity
White
AfAm
Asian
PacIs
Namer
Other
Total
868
312
312
121
35
87
1735
By Subject Area
ACC
BUS
ENG
HIS
ITC
MAT
SCI
Total
139
121
347
139
139
607
243
1735
65
247
Hands ON Database
Sharon looked over the spreadsheet. “You have to gather all that information by hand? I have just a
couple of questions about some of the abbreviations. Does “PacIs” mean “Pacific Islanders?”
“Yes.”
“Also what does “Workforce Retraining” refer to?”
“Several students receive are identified as workforce retraining. Usually they are students who have lost
their jobs and have been given government grants to return to school. Workforce retraining will pay for
tutoring for those students.”
“How are other students covered?”
“We get some money from different federal grants. Often these are tied to the diversity of the students
we serve. Some are paid from funds at the college.”
“Does the database need to track which students qualify for which funding?”
“No, I can handle that. If I can just get the basic counts and statistics easily, it will make my life a
hundred percent better.”
Transaction Database—a database
that is optimized to keep track of
transactions such as sales or
purchases in real time
Things You Should Know
Types of database
Relational databases can serve different needs for different
users. These different needs can require different setups
and structures.
66
Hands ON Database
One common usage of a relational database is as a Transaction database. A transaction database, as its
name suggests, records the data from immediate transactions such as sales or orders in real time, as
they happen. These databases can be attached to a point of sale at a cash register or they can be behind
web forms such as at Amazon.com or Ebay. Transaction databases are optimized for speed and
efficiency. Nobody wants a long wait while his or her order is being processed. Also, given the global
nature of business, it is essential that many of these databases be as available as possible, preferably 24
hours a day seven days a week.
Another common use for a database is as a Management Information System(MIS). The purpose of a
MIS is to use the data to provide data managers need to manage an organization. A management
information system focuses on data analysis. It is used to query data to return reports on things like
total monthly sales, number of products sold, total shipping
costs, etc.
The MIS bases its reports on the data gathered by the
Management Information
System—a database optimized for
queries that return summary
information about transactions
Transaction database. In a simple situation like the tutoring
program where the number of users is relatively small, the Transaction database and the MIS database
may be the same physical database. But in enterprise organizations they are usually separated. They
types of queries that a MIS runs to retrieve the data for reports would slow down the performance of
the transaction database more than is acceptable. So,
Business Intelligence—a set of tools
for analyzing trends and patterns in
business data
typically, the data is periodically copied or shipped from the
transaction database to the Management Information
system.
Increasingly, DBMS software is including tools for Business Intelligence. Business Intelligence moves
beyond management systems. Business intelligence systems mine data for patterns and trends that
67
Hands ON Database
might help a business improve its offerings or services. A
Things to Think About
Why do you think the idea of data
warehouses and data mining hold
such an attraction to
organizations? What are some of
the advantages an organization
could get from using Business
Intellegence tools?
company, for instance, might analyze its customer data to find
the ages and incomes of the customers who buy a particular
product. They might look to see what other products those
customers have purchased in a six month period before or
after the purchase of a particular product. They might look to
see if they can spot a trend related to current advertising or a
current event.
Data Mining, exploring data sets looking for useful trends,
is related to the idea of Data Warehouses. The concept of
a data warehouse is to bring together all the data that an
organization generates, not just the transactions that are
Data Mining—using business
intelligence techniques on a variety
of data sources brought together in
a Data Warehouse
recorded in formal databases, but also the memos, letters,
minutes from meetings and other documents any organization generates. The data warehouse brings
them all together in a way and a place where data can be extracted and compared.
The concept of the data warehouse is obviously very attractive, but it has proven very difficult to bring
about in practice. New RDBMS’s have included tools to incorporate more heterogeneous data such as
documents, but it is still difficult to compare data from the different sources. One development that
holds the promise of making data warehousing a reality is
XML—marked up Unicode text that
follows a few strict rules—
increasingly used as a file format for
documents and data transferal
xml. XML is a set of technologies based on Unicode. XML
is marked up text that follows a few simple rules. Ideally,
an xml document is self describing, that is the markup
tags tell a user what the text between consists of.
68
Hands ON Database
Increasingly business documents are saves as XML. (It is now the default format for Microsoft
documents.) Tools have been developed for querying XML, allowing a user to extract and compare
pieces of documents. RDBMS systems have also added data types and tools to store and manipulate
XML documents. These developments may make data warehousing a fully realized business tool. XML
will be dealt with more thoroughly in Chapter 10.
Sharon stands up. “Thank you. Looking at these reports will help
me a lot. They give me a much better idea of what kind of data
Things to think about
The confidentiality of data in a
database is a major issue for many
companies. The database may
contain private information about
employees or clients, or it may
contain data that could
competitors could use to gain a
competitive edge. Can you think of
some ways that you could assure a
client that you will keep all their
data confidential?
the database needs to track and store. Do you think I could get
some copies to look at? I think I would also like to see examples
of reports you have to make to your funding sources.”
Terry hesitates for a moment, “I think I can do that—but some
samples might have confidential information on them.”
“I understand. You can give me blank ones, or you can black out
private information. I promise not to divulge any information that
could even remotely be considered private. I’ll even sign
something to that effect if you want.”
“That shouldn’t be necessary. I will get copies of the things I showed you and the other reports and get
them to you tomorrow. What’s next?”
“Thanks, The next thing I really need to do is interview some of the tutors and, if possible, a student or
two to get their perspective. It is a good idea to have some representation of all the stakeholders. Is
there a good time to do that?”
69
Hands ON Database
Terry thinks for a minute. “We have a tutor’s meeting once a month. The next one is the day after
tomorrow at 9:00 AM in Room 301. Would that work?”
“Yes that would work just fine, ...
Purchase answer to see full
attachment