Database design scenario example

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.