chapter 1: 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 2: 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 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 an additional interview
with the supervisor, and 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
xiv Preface
chapter 3: 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—interacts 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
chapter 4: 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.
outcomes
• 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 5: 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
chapter 6: Physical design
narrative Sharon builds a prototype of the database, creating all the tables and setting
up the relationships. 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 7: 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.
Preface xv
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
chapter 8: 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 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
a: using microsoft access with the book 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.
b: sql server express An overview of how to use the SQL Server Management Studio
to create and access databases in SQL Server Express.
c: visio An overview of the Visio environment, with a special focus on the database
templates.
D: 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, and so on.
glossary of terms Glossary of all vocabulary terms.
