Transcript Chapter 3

Chapter 3
Requirements and Business Rules
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.1
Client Server Relations
• Much of software can be divided into one
of two types:
– Servers
– Clients
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.2
Servers
• A server is software that offers “services”
to other software.
• For instance a Web server provides Web
pages that are requested by a browser.
• Databases usually behave as servers.
• (Some machines are optimized to host
Server software. They are also commonly
referred to as servers).
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.3
Clients
• Clients are software that request services.
• A browser, for instance, requests a Web
page to load and view.
• An application client can request data
from a database.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.4
Client Server Example
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.5
Review of the Issues
• Reviewing the issues with the current data
management system is a good place to
start.
• Several of the requirements of the new
database will be to resolve those issues.
• Reviewing the issues also helps you
refocus on the “problem domain.”
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.6
Problem Domain
• The problem domain represents the business
problems a database is meant to solve.
• For a retail sale database, for instance, the
problem domain is the sale, and all that is
involved with the sale.
• For a science database dealing with
earthquakes, the domain would be the
locations, sizes and depths of earthquakes.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapte3.7
Requirements
• It is important to identify all the
requirements of the database.
• A requirement represents something the
database must store or do.
• There are several types of requirements:
– Data Requirements
– Report Requirements
– Security requirements
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.8
Data Requirements
• Data Requirements refer to the attributes
the database must store in order to meet
the information needs of an organization.
• It is important to identify these data
requirements as completely as possible.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.9
Report Requirements
• Report requirements refer to the reports
the database will have to generate.
• For example, the Tutor database will have
to report on tutor’s hours, the numbers of
unduplicated student sessions and the
demographics of the students using the
tutoring services, among others.
• The data required to generate those
reports must be in the database.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.10
Security Requirements
• Security requirements refer to the limits that
must be placed on database access in order
to protect the integrity and privacy of the
data in the database.
• Typically different users of the database will
have different levels of access depending on
their needs.
• A user should have access to the data and
resources they need to do their work, but no
more.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.11
Actors and Requirements
• It is useful to think about requirements in
terms of each of the actors who will be
using the database.
• What does that particular actor need to
do with the data in order to do their job?
• What are the limits on what each actor
should be able to do?
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.12
Access Requirements
• Access is usually defined in terms of what
kinds of things a user can do in the
database.
• For instance, a user can be given
permission to SELECT some data – that is
to view it–but not other data.
• They may be given permission to edit
certain data, but not all data.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.13
Types of Security Requirements
Type of Access
Description
Create
The permission to make new database objects such as tables or views
Alter
The permission to modify database objects
Drop
The permission to remove database object
Select
The permission to see data in a table or view
Update
The permission to modify data in a table
Insert
The permission to add data rows to a table
Delete
The permission to remove data rows from a table
Execute
The permission to run database executables such as stored procedures
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.14
Business Rules
• A business rule is a rule about how data is
collected, stored or processed.
• Examples of Business rules:
– All quarter grades must be between 0 and 4.
– No patron can have more than 20 items
checked out at a time.
– Payments must be made within 30 days or a
25 dollar late fee will be added.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.15
Enforcing Business Rules
• Some business rules can be enforced in
the database itself by placing constraints
on the data. (The quarter grade must be
between 0 and 4).
• Other business rules must be enforced
through other means such as “Triggers.”
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.16
Triggers
• A trigger is a block of SQL code that is
triggered by an event such as an INSERT,
UPDATE or DELETE.
• Triggers can be used to enforce things such
as “No patron can check out more that 20
items.”
• When the database inserts a new item the
trigger fires and totals the number of
unreturned items. If it is greater than 20 it
can notify the librarian or the patron.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.17
Reviewing Requirements and
Business Rules
• When you have listed all the requirements
and business rules you can discover, you
should always review them with the chief
stakeholders.
• Use the review to make sure you have a
complete list of requirements.
• Also make sure you have understood the
business rules and processes.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.18
A Little Bit of Grammar
• This is a technique to help identify the
data requirements for a database.
• Review all the documents, interviews and
questionnaires and list all the major
nouns.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.19
Grouping Around Themes
• The next step is to sort the nouns into
broad themes or groups.
• These themes may become entities in
your database design.
• The other nouns that belong to the those
themes will become attributes.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.20
Entities
• Entities are things that the database is
concerned with.
• In the tutoring database, for instance
major themes are student, class, tutor,
session and request.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.21
Attributes
• Attributes represent data that describe
entities.
• Attributes of student, for instance,
include:
– Student ID
– Student Name
– Student Address
– Student phone, etc.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.22
Keys
• When you have your preliminary entities
and attributes defined you can start
thinking about keys.
• There are several types of keys:
– Primary Keys
– Candidate Keys
– Natural Keys
– Composite Keys
– Surrogate Keys
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.23
Primary Keys
• A primary key uniquely identifies a row of
data.
• A primary key must be unique for every
row (that is it can never repeat in the
table that will result from the entity).
• For instance a student ID can uniquely
identify an individual student and the data
associated with him or her.
• Every entity should have a primary key.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.24
Candidate Keys
• A candidate key is an attribute or
attributes of an entity that have the
potential to become a primary key.
• Candidate keys are not actual keys, but
are a list of attributes that should be
considered when choosing the primary
key.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.25
Natural Keys
• There are basically two ways of making
keys: natural and surrogate.
• Natural keys are keys formed by using an
attribute that “naturally” belongs to the
entity, such as a student ID or a phone
number.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.26
Composite Keys
• Composite keys are keys composed of
more than one attribute.
• For example to get a unique designation
of a course section, it is necessary to
combine the quarter, the year, and the
item number.
• Composite keys are one key made out of
many parts.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapte3.27
Surrogate Keys
• Surrogate keys are keys that have no
business meaning.
• Often they are just integers incremented
row by row.
• They can also be things such as time
stamps of auto generated GUIDs.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.28
Comparison Between Natural and
Surrogate Keys: Advantages
Natural Keys
• Protect better against
accidently repeating the
same information.
• Belong to the entity and
tend to make foreign keys
more understandable.
Surrogate Keys
• Are always unique.
• Do not contain any business
logic and are therefore not
subject to changes in
business rules.
• Are easier to define and
use.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.29
Comparison Between Natural and
Surrogate Keys: Disadvantages
Natural Key(s)
• Uniqueness is hard to
guarantee.
• May require clumsy
composite keys
consisting of several
attributes to be unique.
• Are subject to changes
in business rules (think
of Social Security
numbers, for instance).
Surrogate Keys
• Automatically grant
uniqueness to a row
making it easier to
accidentally insert the
same information twice.
• Have no relation to the
data making database
relations less readable.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.30
Documentation
• All requirements and business rules
should be documented clearly and stored.
• Even preliminary definitions of entities,
attributes and keys should be kept for
later reference.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter3.31
All rights reserved. No part of this publication may be reproduced, stored in a
retrieval system, or transmitted, in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise, without the prior written
permission of the publisher. Printed in the United States of America.
Copyright © 2012 Pearson Education, Inc.
Publishing as Prentice Hall