Expert answer:Database Data Model
Expert answer:Submit the enterprise data model, operating rules, and rule reflection for the selected group or department. selected scenario: wild wood apartments.
hands_on_database_text.pdf
guidelines_for_submission……docx
Unformatted Attachment Preview
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 databas …
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades