Expert answer:Milestone Two: Database Analysis and Design
Expert answer:Submit the conceptual model, logical model, and physical database design for your solution to the selected scenario. scenario selected: Wild Wood ApartmentsFor additional details, please refer to the Milestone Two Rubric document.
milestone_two_rubric.docx
hands_on_database_text.pdf
Unformatted Attachment Preview
Milestone Two Rubric
Submit the preliminary design of the database for the final project. Devise a conceptual model that will best address the scenario you selected for the final
project. Your model should include all necessary entities, relationships, attributes, and business rules. Based on the conceptual model, illustrate a logical model
for your DBMS that accurately represents all necessary aspects of the DBMS to address the solution. Create a physical database design that builds on the
nonphysical (conceptual and logical) models you crafted. This design includes the conceptual and logical models of the database, as well as the physical design of
the database.
Specifically, the following critical elements must be addressed:
Analysis and Design
A. Conceptual Model: Based on the business problem or challenge, devise a conceptual model that would best address the problem. Your model
should include all necessary entities, relationships, attributes, and business rules.
B. Logical Model: Based on the conceptual model, illustrate a logical model for your DBMS that accurately represents all necessary aspects of the
DBMS to address the solution.
C. Physical Design: Create a physical database design that builds on the nonphysical (conceptual and logical) models you crafted.
Guidelines for Submission: Your paper must be submitted as a 6- to 12-page Microsoft Word document using appropriate modeling language and/or diagrams.
It should use double spacing, 12-point Times New Roman font, and one-inch margins. It should include at least three sources, which should be cited in APA
format.
Critical Elements
Exemplary (100%)
Proficient (90%)
Needs Improvement (70%)
Not Evident (0%)
Value
Analysis and Design: Meets “Proficient” criteria, and
Conceptual Model model evidences detailed
attention or keen insight into
conceptual design needs and skills
Devises a comprehensive
conceptual model that would
logically address the identified
problem
Devises a conceptual model that
does not attend to all necessary
entities, relationships, attributes,
and business rules or would not
logically address the identified
problem
Does not devise a conceptual
model
30
Analysis and Design: Meets “Proficient” criteria and
Logical Model
evidences keen insight into
representation, communication, or
depiction of logical model design
needs for business scenarios
Illustrates an accurate logical
model that details the necessary
aspects of the DBMS needed to
address the business needs and
identified problem
Illustrates a logical model that
lacks accuracy or necessary detail
regarding aspects of the DBMS
needed to address the business
needs and identified problem
Does not illustrate a logical model
30
Analysis and Design: Meets “Proficient” criteria and
Physical Design
exemplifies the knowledge and
skills necessary to extrapolate a
physical design from nonphysical
models for solving data challenges
Creates a physical design that
precisely builds on the nonphysical
models with incorporation of
physical aspects
Creates a physical design that
builds on the nonphysical models
with incorporation of physical
aspects, but with a lack of
precision
Does not create a physical design
that builds on the nonphysical
models with incorporation of
physical aspects
30
Submission has major errors
related to citations, grammar,
spelling, syntax, or organization
that negatively impact readability
and articulation of main ideas
Submission has critical errors
related to citations, grammar,
spelling, syntax, or organization
that prevent understanding of
ideas
10
Articulation of
Response
Submission is free of errors related Submission has no major errors
to citations, grammar, spelling,
related to citations, grammar,
syntax, and organization and is
spelling, syntax, or organization
presented in a professional and
easy to read format
Earned Total 100%
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. …
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