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 database templates 14 Hands ON Database SQL Server Express An overview of how to use the SQL Server Management Studio to create and access databases in Sql Server Express 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, etc.. Glossary of Terms Glossary of all vocabulary terms Conclusion There are many good database textbooks, but they tend to be more theoretical than hands-on. Their audience is the computer science student who needs to understand the deeper, mathematical subtleties of entity relationships, who needs to understand the ways that various database management systems process physical files or how they optimize queries. These are valuable skills, but these books contain far too much information for a student to absorb in a term, and too little hands-on practice for the student who is looking for a practical introduction to database. Hands On Database is designed to be that practical introduction. 15 Hands ON Database Chapter One: Who Needs a Database Overview of Relational Databases and their Uses In this chapter we meet Sharon, a college student who is working toward a degree in Database Development and Administration. She signs up to become a tutor and realizes that the tutoring program is in desperate need of a database to track tutoring sessions. She volunteers to develop it and after some discussions defines a statement of work for the database. Chapter 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 work for a given database scenario The Situation Sharon is a student taking database classes. She is near the end of her program and has done quite well. Like any student, she could really use some extra money and has decided to inquire about tutoring. She has 16 Relational Database—a type of database that uses “relations,” tables, to store and relate tables. See “Things You Need to Know 1” Hands ON Database noticed that many students seem to struggle with relational database concepts, particularly in the early classes, and she is fairly sure there would be a demand for her services. The administrator of the tutoring program at the college is named Terry Lee. Terry invites Sharon into her office and offers her a seat. She smiles. “So you want to tutor?” Relational Design involves organizing data into tables or entities and then determining the relations among them. SQL is the language Relational Databases Use to create their objects and to modify and retrieve data. “Yes. I think I would be good at it.” “What subjects do you think you could tutor?” “I was thinking especially of database related topics. I can do relational design and SQL. I think I could tutor Microsoft Access, Sql Server and even other Database Management Systems. I can also do some database programming.” Terry nods. “That’s good. We do have some requests for tutoring in those areas but so far no one to provide the tutoring. Before you can begin, you will need to get recommendations from two instructors who teach in the area you want to tutor. Also you will need to do a short training session.” Sharon smiles, “That’s no problem.” “Good.” Terry rises from her seat. “Let me show you how things work.” Things You Should Know Databases 17 Hands ON Database A database is, at its simplest level, a collection of related data. It doesn’t have to be electronic. The card catalogs that libraries used to have were certainly databases. A scientist’s spiral notebook where he or she keeps notes and observations could be considered a database, so too could a phone or address book. When we say “database,” though, we usually mean electronic databases, databases that run on computers. Flat File Databases The simplest form of database is the flat file database. Flat Delimited files have some sort of character separating columns of data. The delimiter is often a comma or tab, but can be any non alphanumeric character. in Fixed Length files the length in characters of each column is the same files usually consist of a file which store data in a structured way. A common format for flat file databases is the delimited file. In a delimited file, each piece of data is separated from the next piece by some “delimiter,” often a comma or a tab. The end of a row is marked by the new line character (usually invisible). It is important, if the file is to be read correctly, that each row contain the same number of delimiters. Another kind of flat data file is the fixed width data file. In these all the columns share a fixed width in characters. These flat files can be read by a computer program and manipulated in various ways, but they have almost no protections for data Data Integrity—refers to the accuracy and the correctness of the data in the database integrity and they often contain many redundant elements. Spreadsheets, such as Excel, can also be used as flat file Redundancy—refers to storing the same data in more than one place in the database databases. Spreadsheets offer a great deal more functionality than simple delimited files. Cells can be given a data type such as “numeric” or “date time.” This helps ensure that all the entries in a given column are of the same type. You can also define valid ranges for 18 Hands ON Database data (For instance, you can stipulate that a valid term grade is between the numbers 0 and 4) Spreadsheets usually contain data tools that make it possible to sort and group data. Most spreadsheets also contain functions that allow the user to query the data. But despite these enhancements spreadsheets still share many of the redundancy and data integrity problems of other flat file formats. Figure 1: Delimited Text Example 19 Hands ON Database Figure 2: Excel Spreadsheet Hierarchical Databases The most common database model before the relational model was the hierarchical database. Hierarchical databases are organized in a tree like structure. In it one parent table can have many child tables, but no child table can have more than one parent. This sounds abstract, and it is. One way to visualize it is to think of the Windows (or, for that matter, the Mac or Linux) file system. The file system has a hierarchical structure. You have a directory, under which there can be sub directories and in those subdirectories can be other subdirectories or files. You navigate through them by following a path. 20 Hands ON Database C:\Users\ITStudent\Documents\myfile.txt Things to think about Hierarchical databases are still in use in many institutions. This is especially true of large institutions such as banks and insurance companies that adopted database technologies early. These institutions invested heavily in the development of these databases and have committed decades of data to their files. Although database technologies have improved, they are reluctant to commit the time and money, and to incur the risk of redeveloping their databases and of translating their vast stores of existing data into new formats. The basic philosophy is, if it still works, let well enough alone. Most companies are conservative about their databases, for understandable reasons. What do you think companies like Microsoft or Oracle have to do to convince companies to upgrade to their newest database products? This tree like organization is very logical and easy to navigate but it does present some of the same problems of redundancy, data Integrity and comparability of data. It is not uncommon for the same data to be repeated in more than one place in the tree. Any time data is repeated there is a risk of error and inconsistency. It also can be very difficult to compare a piece of data from one branch of the database with a piece from an entirely different branch of the database. Relational Databases By far, the most popular type of database for at least the last 30 years is the relational database. The idea for relational Databases came from a man named Edgar F. Codd in 1970. He worked for IBM and he wrote a paper on, at that time, a new theoretical design for databases. This design would be based on the mathematics of set theory and predicate logic. He formulated the basics of the relational design in 12 rules (Actually there are 13 rules. Like any good computer engineer, Codd begins his numbering with 0.) Briefly, in the relational model data would be organized into relations or tables and these relations would define the relationships among themselves by means of repeating an attribute or column 21 Keys—in relational databases each table usually has one column designated as a primary key. This key uniquely identifies each row in the table. This primary key becomes a foreign key when it is repeated in an another table to create a link between the tables Hands ON Database from one table in another table. These repeating columns would be called “Keys.” He also specified that the logical design of a database should be separate and independent of physical design considerations such as file types and disk writing and reading functions. He specified that there should be a “data sublanguage” that can perform all data related tasks. SQL has evolved into this language. We will discuss it more thoroughly in a later chapter. For a discussion of Codd’s 12 rules see Wikipedia http://en.wikipedia.org/wiki/Codd's_12_rules Figure 3: SQL Server Relational Database Manager showing an Entity diagram for a DVD Rental database This may sound complex, and it certainly can be, but it solved many of the problems that plagued the databases of the day. One of those problems was data redundancy. Redundancy refers to the need to store the same data in more than one place in the database. In a banking database, for instance, you would store the customer’s name and address along with an associated savings account. But you might 22 Hands ON Database have to repeat this same customer information for a checking account. The more times you have to enter the same information, the more likely it is that one of the entries will contain an error. Also, if you have to change the information, an address or phone number, for example, the greater the likelihood that one of the entries could miss being updated. This kind of update error is why bills or notices sometimes continue to an old address even after you have submitted your new address to a company. It was changed it in some places but not others. In a relational database the redundancy is minimized. A bank would enter the customer’s data only once, in one place. Any changes would be made only in one place. The only redundancy that is allowed is the repetition of a key column (or columns) that is used to create relationships among the tables. This significantly reduces the chances of error and protects the integrity of the data in the database. Figure 4: Primary key Foreign Key Relations between a Customer table and a Transaction table CustomerID(PK) C41098X3 CV1099B1 D345XU24 TransactionID 10002345 10002346 10002347 LastName Carson Madison Brown FirstName Lewis Sarah Lisa TransactionType Deposit Deposit Withdrawel Address 121 Center Street 1324 Broadway 2201 Second Ave TransactionDate 2009-2-12 10:25:06 2009-2-12 10:27:13 2009-2-13-14:45:57 City Seattle Seattle Seattle CustomerID(FK) C41098X3 CV1099B1 C41098X3 State WA WA WA Amount 1245.76 500.00 200.00 Another problem the relational design helped solve was the problem of relating data from different parts of the database. In many of the previous database designs, a programmer had to write a routine in a language like Fortran or Cobol to extract the data from various parts of the database and compare them. In a well designed relational database every piece of data can be compared or joined with any other piece of data. The relational design was a huge step forward in flexibility. 23 Hands ON Database The chief drawback of relational database is the inherent complexity of the design. It is fairly easy to design a bad database that will not do what a client needs it to do. The chief advantages, for a well designed relational database are data integrity and flexibility. These two advantages have made it the most commonly used database model for the past 30 years or so. The Opportunity They walk from Terry’s office down the hall to the computer lab. Terry stops at the front desk. “The computer lab is one of our designated tutoring areas, and I suspect the one where most of your sessions would be scheduled.” She picks up a clipboard containing several pieces of paper. “We have 2 pages for each week an AM one and a PM one. At the beginning of the month, each tutor enters their availability for each day, what times they are available that day, and what courses they can tutor for. “Students sign up for particular sessions. Tutoring is free for the students as long as they are enrolled in the class for which they are getting tutored.” “How do you check that?” “Right now it is mostly a matter of trust.” “How long is each tutoring session?” “Tutoring sessions are for 30 minutes each, and a tutor can only do 30 sessions or 15 hours a week.” “What if you set up a time slot and nobody signs up?” “As long as you show up when scheduled, we will pay you for the time. The pay, by the way, is $10.50 an hour.” 24 Hands ON Database “Thanks.” Sharon looks over the notebook. “Just out of curiosity, what do you do with the schedules at the end of the month?” “Actually, I take them back to my office ever two weeks and type it into various spreadsheets to make reports to the people who pay for the tutoring, and to determine the pay for the tutors themselves.” Sharon turns to Terry and says, “You know, you could really use a database. It would make it much simpler to track schedules and availability and it could make doing your reports much easier.” Things to think about There are many situations that could be improved with the addition of a database. Whenever there is a large amount of complex data to handle, a database is likely to provide the best solution. There are times, however, when the data involved is more modest in scope and complexity, that a relational database may be overkill Relational database s are complex to develop and maintain. The benefits when dealing with large amounts of data are worth the costs in development time and maintenance. But sometimes, the best solution is simply a spreadsheet such as Excel. Terry sighs. “I’ve known that for some time, but we just can’t find anyone willing to take on the task. The school’s database administrator is much too busy and no one else feels competent or has the time to take on the task.” Sharon hesitates a little, then says, “I might be able to put a database together.” Terry looks hopeful. “Really? That would be wonderful. We even have some money in our budget so we could pay you something for your work.” “I am still learning database,” Sharon cautions, “but I am pretty sure I could make you something that would meet most of your needs.” “Good, why don’t you come by tomorrow about this time and we will talk about it.” “I will be there.” 25 Hands ON Database Things you should know RDBMS A Relational Database Management System (RDBMS) is, as its name suggests, a system for managing relational databases. As a minimum an RDBMS needs to allow a user to create one or more databases and the objects associated with that database such as tables, relationships, views and queries. It also needs to support basic maintenance such as backing up the database and restoring it from a back up file. It also needs to support security making sure that users and groups have access only to the databases and data that they are authorized to use. Most commercial RDBMSs offer many features beyond these basic ones. Most include tools for monitoring and optimizing the performance of their databases. Many include reporting services to format and present the results of queries. Some even include complex Business Intelligence Packages for analyzing business trends and patterns. Below is a table of the most common RDMSs with a link to their home web sites. Table 1 RDBMS ORACLE SQL Server DB2 MySQL Comments The first commercial RDMS and the biggest. Powers many of the world’s largest companies Microsoft’s RDMS product. Ships in many versions designed for different company needs. Also powers many large enterprises IBMs RDBMS URL http://www.Oracle.com The most popular Open Source RDBMS currently http://www.MySql.com 26 http://www.microsoft.com/sql/default.mspx http://www306.ibm.com/software/data/db2/9/ Hands ON Database ACCESS owned by SUN Microsoft’s Desktop Database http://office.microsoft.com/enus/access/default.aspx?ofcresset=1 Getting the Scope After Sharon leaves Terry, she goes to one of the instructors, A professor named Bill Collins from whom she hopes to get a recommendation. He is setting in his office and smiles when he opens the door for her. “Come on in. How can I help you today?” She tells about her plan to tutor and asks for a recommendation. He says he will be happy to provide one. Then Sharon tells him about the possibility of making a database. She says, “I’ve got a thousand ideas about how the database should look and what should be in it.” Things to watch out for It is easy to get carried away with your own excitement about a database project. You may be able to see several possible solutions and want to start designing right away. But it is critically important that you delay designing until you have a clear idea of what client wants and needs. Patience and the ability to listen are among the most important skills of a database developer. Bill cautions her, “Be careful not to get ahead of yourself. You need to remember you are not making this database for you. You are making it for a client. You need to listen carefully to what Terry and the other people who will use the database say about what they need and not get trapped by preconceived notions. The first thing you need to do is get as clear an idea about what the database is intended to do as possible.” 27 Hands ON Database “A statement of scope?” “Yes, that would be a good place to start, but I would go farther and make a complete statement of work. That would include the scope, but it would also contain some discussion of the background, the objectives of the project and a tentative timeline. I have some samples I can share with you. Listen, if you A statement of scope is a short statement of one or more paragraphs that says in clear, but general, terms what the project will do. A Statement of work is a more complete statement about the objectives and timeline of the project need any help or advice on this project, feel free to ask me.” “Thank you. Thank you very much.” Things You Should Know Statement of Work A statement of work is a preliminary document that describes, in general, the work that needs to be done on a project. Often this is prepared by the people who want the work to be done and offered to contractors to for bids. But sometimes, as in this case, it can be used as an initial clarification of task at hand. It is important to have something like a statement of work for any major project so that everyone knows what is expected. Without it, people often find, sometimes late in the process, that different individuals have very different expectations about what the project should contain. A statement of work is also a good reference throughout the project to keep everyone on track and focused. The statement is preliminary and can be altered as the needs of the project change or grow. But, by referring to the 28 Hands ON Database statement of work, you can guarantee that any changes or additions are a matter of discussion and not just assumed by one of the parties. The following table delineates a few of the elements that can appear in a statement of work. Table 2: Statement of Work Elements Element History Scope Objectives Tasks and Deliverables Description Describes the reason for the project, usually a problem with the current system or an opportunity to provide new services. May describe the various steps and efforts that led to the current state of the project. Provides a general statement of the requirements and expectations of the project. It states only the high level requirements and does not get into specifics. It does not go into detail about how things are to be done. It may include some general constraints such as time or budget limits. The things the project is intended to achieve. Objectives aren’t about creating specific elements of the database, for instance, but about what the database is supposed to achieve, why the client wants the database in the first place. Breaks the project into discrete tasks. Each task should have an estimated duration and concrete deliverables. The First Interview The next day Sharon sits in Terry’s office. She has brought a notebook to take notes. She has written down some of the key questions she knows she will need to ask. Sharon knows it is important to be prepared and focused for any interview. She has also brought a diagram of a database she created for a non-profit to show Terry as an example of database work she has done. Terry says, “Thanks for coming in. You have no idea how long and how much we’ve wanted a database for the tutoring program. We have to generate several reports each term to justify our funding. It has gotten so that it takes most of my time. It keeps us from doing things to improve the program. We also really need to be able to track what works and what doesn’t better.” 29 Hands ON Database Sharon nods, “I really hope I can help. I brought an example of a database I made for Capital Charities to show that I do have some experience creating databases. We did this as part of a project for a Database class” 30 Hands ON Database Figure 5: Sample Entity Diagram for a Non-Profit Terry looks at the diagram as Sharon explains it. 31 Hands ON Database “Capital Charities provides funds for basic utilities, food and occasional repairs for poor families on a one time, emergency basis. They needed to be able to track their contributors and their contributions. That was one part of the database. That data is stored in the contributor and contribution tables. That line between them indicates a one-to-many relationship. It uses what is called “crows Crows Feet Notation: A type of Entity Relation Diagram where the relationships are depicted using lines and 0’s. These are more descriptive of relationships than the diagrams using simple arrows. feet” notation. It shows that each contributor has contributed at least once and may have contributed many times. The crows foot, those three lines, points to the many side of the relationship. The other part of the database tracks the types and amounts of assistance given to each client. The client information is entered into the Client table.” She points to the ClientNotes entity, “There can 0 or many notes about any client. Each client receives assistance at least once. That was a business rule of the charity. They only wanted to list as clients those they had actually given assistance to. Each act of assistance is associated with a particular councilor and can involve several different types of assistance. That is the reason for the AssistanceDetail table.” “It looks complex.” “It is a little. But I also built some forms and reports that made it so the Capital Charities staff didn’t have to navigate the database directly. It made it a lot easier to use.” “Well it certainly looks like you should be capable of doing this for us. What do you need from me?” You have already started suggesting some of the things I want to talk about today—things you want the database to do. What I need to get from you today is a clear sense of what you want the database to do for you. I don’t need specifics yet, just general statements of what you want to see and what the database needs to do to be useful to you.” 32 Hands ON Database Terry hesitates, “Ok…Where do I start.” “You already suggested a couple of things. You need to track what works and what doesn’t. How would you determine that something is working or not working?” Things you should know You should always go to an interview prepared. In this initial interview, you should be prepared to help them get started on the right track and have questions that help focus them on the important aspects of the database. But you don’t want to guide them toward some preconceived notion of what the database should be. Rather, your questions should help them guide you to a clearer understanding of what they need out of a database “Well, part of it is how many students are using the tutoring services. What courses are they taking tutoring for and how the tutoring they receive helps them succeed in their courses. Do they get better grades? Does tutoring stop them from dropping the class? I know these are a bit vague and difficult to track.” “That’s OK. What about scheduling tutors and students. What do you need to track to do that?” “Well, we need to track tutors, of course, and what classes they can tutor for. We need to track the tutor’s schedules so we know what times they are available. We need to know which students sign up for each session and ideally we should be able to check that they are actually taking the course for which they are getting tutoring.” “Do you need to track demographic information for students?” 33 Hands ON Database “If we could, that would be great. It would make our reporting much easier. Several of our grants are targeted at particular groups of students. We would have to guarantee that such information would remain private.” “What other reports do you need to make.” “I need to know how many hours each tutor worked in a pay period. I need to know how many students each tutor saw. I also need to know how many unduplicated students were seen each term.” “Unduplicated?” “Yes, individual students. A single student could get several sessions of tutoring. For some reports we need to know how many individual students we are serving—not just how many sessions we have scheduled.” “Can you think of anything else?” “We really need to know if a student actually got the tutoring they signed up for. Sometimes a student will sign up and then not show for the actual session. It might also be good to know what courses students want tutoring in where we are not offering it. Maybe you could provide a way for students to request tutoring for courses or subjects.” “Anything else?” “Nothing I can think of right now.” “OK. What I am going to do is to take this and write up a statement of work describing the database, the objectives and a tentative time line. Then we can look at it and see if it really describes the database you need. If it doesn’t we can adjust it. When it does, we can use it to refer back to keep us on track so that we don’t get lost in the details later.” 34 Hands ON Database “Thanks,” Terry stands up. “I actually think we can do this. You really seem to know what you are doing. I am looking forward to it.” Sharon smiles, though she doesn’t feel nearly as confident in her abilities. “I am looking forward to it too.” Identifying the Big topics Sharon goes to the school cafeteria and gets a cup of coffee. She sits down to go over her notes. She knows it is important to review them while the interview is still fresh in her mind. The first thing she needs to do is to identify the big topics. What is the database about? What are the major components going to be? “Well, tutoring,” she says to herself, “that is the big topic.” But what does tutoring include. She takes out a pencil and starts a list, “Tutors, of course, and students and the tutoring schedule.” She writes them in the list: tutors students tutoring schedule “Is there anything else? Anything I am missing?” She frowns as she concentrates for a moment. “Courses! Tutors tutor for specific courses and students are supposed to be registered in those courses in order to get tutoring.” She adds it to the list. Students also should be able to request tutoring for specific courses. She adds Requests to the list. tutors students tutoring schedule courses requests 35 Hands ON Database She thinks a bit longer. “We need to track whether students attended the sessions they scheduled. That is important, but is it a new topic? It could be part of scheduling.” Terry wanted one more thing, she remembers. She wanted to track student success. To Sharon that seems like a different topic entirely. She recalls that Bill Collins in his class always insisted that a good database like a good table should be focused on a single topic. She decides to leave the list as it is. Things you should know Identifying the major topics of a database is an important Entities—an entity is something that the database is concerned with, about which data can be stored, and which can have relationships with other entities. Attributes—are things that define entities (The entity customer has attributes like name and address) exercise. It helps provide a clearer sense of just what the database is about. It also is the first step toward identifying the “entities” that will be used in the database design. One way to begin identifying the major themes is to look at the nouns in your notes. See if they cluster together around certain themes. These themes are most likely the major topics of your database. We will look at this technique more closely later when we talk about defining entities and attributes. It is important to note that a database may contain several themes, but all those themes should relate to a single overarching topic like tutoring. If there is more than one overarching topic, it may indicate that that you should develop additional databases. Writing the Statement of Work 36 Hands ON Database Now that she has the big topics in mind, she begins to compose the Statement of Work. She begins with the History. The history is a statement of the problem. It can narrate how the current situation came to be the way it is. Sharon thinks about the things she saw and the things that Terry told her. For a long time the tutoring program has used a paper schedule to sign students up for tutoring. Tutors identify their schedule for a two week period and then a schedule is printed and placed in the computer lab. Students look through the schedule for sessions that match courses they are taking and the times they have available. This system has worked and continues to work, but it has several significant problems. For one, it can be difficult for students to find appropriate tutoring sessions. The paper forms are difficult to navigate and understand. Additionally, it is very difficult for the tutoring program to track the students using the tutoring. It is difficult or impossible to track demographic information. It is also difficult to assure that students are enrolled in the courses they receive tutoring in. Even tracking tutors’ hours can be difficult. A database with a client application could significantly improve the situation, by providing a flexible, searchable schedule for students, better tracking of demographics and eligibility, and better tracking of hours tutored. She pauses. That was hard to get going, but once she got started it flowed pretty well. The tutoring database will be designed to manage the tutoring program at the college. She isn’t real happy with that as an opening sentence. She modifies it a little and forges ahead. It proves to be a lot harder than she imagined. The statement has to include all the general points but still be concise enough to give a clear indication of the purpose and functions of the database. After a lot of effort she had this preliminary statement: 37 Hands ON Database The tutoring database will manage data for the tutoring program at the college. It will track available tutors and the courses they can tutor. It will also track each tutor’s tutoring schedule. The database will store demographic information for students who register for tutoring. This information will be private and used only generate general reports which include no personal information. Students, who Constraints ore limits on what the database will do. Later we will see that you can also set constraints on the types and range of data that can be entered into a column in a table have registered, will be able to sign up for available tutoring sessions for courses in which they are enrolled. The database will track whether students attended their scheduled sessions. Sharon looks it over carefully. What about the data about student success? Should that be a part of this database, or should that be a separate project? She decides to set it aside until she has talked with Terry. She also wonders if she should state some of things the database won’t do. Things like: The database can be used to get the hours worked for each tutor, but it will not process pay or payroll information. The database will not validate student information against the school’s registration database. For the moment, she can’t think of any other constraints. She consults an example her instructor gave her to look at. The next step is to set out the objectives for the database. She spends some time thinking about this. Most of the objectives are spelled out in the scope. She pulls out some of the main points and makes a list. 38 Hands ON Database  Streamline the process by which the tutors enter their schedules and students sign up for them  Improve tracking of demographic data of students using the tutoring program  Improve tracking of tutor’s hours and students use of tutoring sessions Next she needs to add tasks and a timeline. She jots down some notes on a paper. The first thing she will have to do is to gather information. She needs to know all the relevant data and processes. How long will that take? She makes a rough guess of two to three weeks. Then she will have to evaluate all the information she has gathered and use it to start developing a list of business rules and first rough model of the data. That could take another couple weeks. Next she will have to refine and normalize the model. Sharon thinks she can do this in two or three days. Then she needs to actually make the database. That won’t take long. She can probably do that part in a couple of hours. What then? Sharon muses for awhile. The last part may take a fair amount of time. She will need to test the database and make sure that it meets all Terry’s needs. She will also have to test for security issues and privacy. That could take two or more weeks of intense work. Where does that put her? Sharon calculates and taking the longer times in each case comes up with nine or ten weeks. None of this is counting the fact that it will take a completely different development project to create a client application for Terry, the tutors and students to interact with the database. But, Sharon says to herself, one project at a time. 39 Hands ON Database Sharon almost has everything she needs for the statement of work, Things to Think About: Estimating Times One of the most difficult things for anyone who is new to developing databases is estimating the time it will take to complete the various tasks. Experience will help, but before you have enough experience how to do you even begin to guess an appropriate time? There are some techniques that can help. One is to make a weighted average. To do this write down your most optimistic time—if everything goes perfect--your best guess at the probable time it will take, and your most pessimistic time estimate—if everything that can goes wrong. Add them all together, but multiply your most probable estimate by 3, then divide the sum by five. (0 + Pt x 3 + p)/5 What other ways can you think of to help your time estimates be more accurate? but there is still something missing. After a while it occurs to her: every task should also have a deliverable, something concrete she can show Terry to let her know that the database is on track. Sharon spends the next couple of hours completing her statement of work. Reviewing the Statement of Work The following afternoon she returns to Terry’s office and shows her the statement. As Terry looks it over Sharon says, “It is important that we both are clear about what we are working on. I don’t want to go off and make a database and then find out it is not what you had in mind at all.” “No, I can see that is a really good idea.” She sets the paper down. “What about the surveys of student success?” “I thought about that, and I am not sure. Sometimes I think that does belong in this project, and other times I think that it is a separate project on its own. I am not sure how we could get objective data on their success but we could include evaluations by students or a quarterly survey. If we build the database as I have described it we should be able to add the success tracking features later or we could look at adding a second database devoted to tracking student success.” “Ok, I can live with that. It would be nice if you could validate student information.” 40 Hands ON Database “Yes, but I don’t really know how to do that. I also think it unlikely that I would be granted the permissions I would need on the School’s registration database. You might be able to get the school’s developers to look at that piece later.” “Fair enough. One other thing you don’t have here, and I am not sure we talked about it, but it would be nice if students could request tutoring in courses that we don’t currently have tutors for. It would help us know where the need is and where we need to try to recruit new tutors.” “That shouldn’t be a problem. I can add that.” “Good. What do you need to proceed?” “Well, let’s go over the tasks and timeline. First, I am going to need to gather some information. I am going to need to see how you have been doing things. I will need to talk to some tutors, and maybe some students, and I probably need to see the reports you make to make sure that the database contains all the information you require. Then I will need to analyze all the information I get and begin to make a data model. After all that I can actually make the database and test it.” Terry studies the timeline. “This is very clear and well done. How realistic do you think this timeline is.” Sharon smiled. “It represents my very best guess. It could go faster if everything works out well, but it could also go slower if I encounter problems. I tried to be very conservative on the times, so I think there is a good chance it can be completed on schedule.” “Good, it would be ideal if the database could be in place by the beginning of next term.” Sharon warns, “There is another piece to all this. There will need to be client application developed so you, the students and tutors can interact safely and easily with the database. But that is really a separate project.” 41 Hands ON Database Terry smiles. “You’re right. We can tackle that when we have finished with the database.” “Tell you what, I will come by tomorrow with a revised version of this statement, and I will give you a preliminary plan of where we go next.” Terry stood up and put out her hand to shake. “Sounds good. I look forward to working with you on this.” The Statement of Work Home, later. Sharon revised the statement of work to include student requests. Here is her completed statement of work: STATEMENT OF WORK: TUTORING DATABASE PROJECT HISTORY For a long time the tutoring program has used a paper schedule to sign students up for tutoring. Tutors identify their schedule for a two week period and then a schedule is printed and placed in the computer lab. Students look through the schedule for sessions that match courses they are taking and the times they have available. This system has worked and continues to work, but it has several significant problems. For one, it can be difficult for students to find appropriate tutoring sessions. The paper forms are difficult to navigate and understand. Additionally, it is very difficult for the tutoring program to track the students using the tutoring. It is difficult or impossible to track demographic information. It is also difficult to assure that students are enrolled in the courses they receive tutoring in. Even tracking tutors’ hours can be difficult. A database with a client application could significantly improve the situation, by providing a flexible, searchable schedule for students, better tracking of demographics and eligibility, and better tracking of hours tutored. SCOPE The tutoring database will manage data for the tutoring program at the college. It will track available tutors and the courses they can tutor. It will also track each tutor’s tutoring schedule. The database will store demographic information for students who register for tutoring. This information will be private and used only generate general reports which include no personal information. Students, who have registered, will be able to sign up for available tutoring sessions for courses in which they are enrolled. The database will track whether students attended their scheduled sessions. It will also track student requests for tutoring in additional course and subjects. 42 Hands ON Database Constraints The database can be used to get the hours worked for each tutor, but it will not process pay or payroll information. The database will not validate student information against the school’s registration database. OBJECTIVES     Streamline the process by which the tutors enter their schedules and students sign up for them Improve tracking of demographic data of students using the tutoring program Improve tracking of tutor’s hours and students use of tutoring sessions Track Student requests for additional tutoring TASKS AND TIMELINE 1. Gathering Data: This task will consist in a number of interviews, questionnaires and observations. Time allotted 3 weeks. Deliverable: A list of scheduled interviews and observations, text of the questionnaires. 2. Analyzing Data: The data gathered will be analyzed to determine business rules and preliminary data modeling. Time allotted 2 weeks. Deliverable: List of business rules to be reviewed, basic entities and attributes. 3. Normalization: the data model will be completed with entities and relationships normalized. Time allotted 1 week. Deliverables: Entity Relation Diagram for Review. 4. Building the physical database: The data model will be translated to the Relational Database Management system. Tables, Columns with specific data types and Relational and other constraints created. Time allotted 3 days. Deliverables: The Schema of the database for review. 5. Testing and security; Sample data will be entered and each of the business rules and requirements will be tested. General database security and security related to business rules will also be tested. Time allotted 3 weeks. Deliverables: Documented test results. 6. Database Completion and installation: final changes and corrections are made. Sample data will be removed and the database installed on a server. Final testing for server access and connections. Time allotted 2 weeks. Deliverables: The working database. Total time between beginning of project and end: 11 weeks, 3 days. 43 Hands ON Database Things we have done In this chapter we  identified a situation in which a database could prove valuable  reviewed briefly the history of databases  identified some of the components of relational databases such as entities and key fields  observed an interview to gather general information about a database  broke the general information into major topics  used the major topics to develop a statement of work for the database Vocabulary Match the definitions to the vocabulary words 1. Attribute 2. Foreign Key 3. Statement of work 4. Primary Key 5. Data integrity 6. Redundancy 7. Delimited files 8. Relational Database 9. Entity 10. Relational Design 11. SQL 12. Constraints a. A type of database that uses “relations,” tables, to store and relate tables. 44 Hands ON Database b. The process of organizing data into tables or entities and then determining the relations among them c. The language Relational Databases Use to create their objects and to modify and retrieve data. d. These files have some sort of character separating columns of data. The delimiter is often a comma or tab, but can be any non alphanumeric character. e. Files where the length in characters of each column is the same. f. Refers to the accuracy and the correctness of the data in the database. g. Refers to storing the same data in more than one place in the database. h. This key uniquely identifies each row in the table. i. This key is the primary key repeated in another table to create a link between the tables. j. A short statement of one or more paragraphs that says in clear, but general, terms what the project will do. k. Something that the database is concerned with, about which data can be stored, l. Things that define aspects of entities m. Limits on what the database will do. n. A document including the scope, objectives and timeline for a given project Things to look up 1. Look up Codd’s twelve rules. Choose one of the rules to explain to your fellow students. 2. Look up the history of SQL. How many revisions of the standard have there been? What was added in the most recent one? 3. Use the internet to look up Database related jobs. Make a brief report summarizing what you find. 45 Hands ON Database 4. A recent trend for major commercial Database developers is to offer free “Express” versions of their databases. Microsoft has Sql Express, Oracle has Oracle Express and DB2 has DB2 Express. Go to the company web sites and look up these Express editions. What features does each one have? What limits do they have? How do they compare to each other? 5. For some time there have been attempts to move beyond Relational databases, to find some new data model. One direction has been to move toward Object Oriented Databases. Another area of research is into XML based databases. Choose one of these to look up and write a brief summary of what the model entails and what is the current status of the model. 6. Look up Statements of Work. What are some additional elements that can be included? Practices 1. Think about keeping a home budget. Would it be better to keep the budget in spreadsheets or to create a budget database? Write a couple of paragraphs that describe your choice and at least three reasons to justify it. 2. An Entity is something the database is concerned with. For instance, a movie rental business would probably have an entity called DVD. Attributes are things that describe the entity. Make a list of possible attributes for a DVD entity. 3. You are going to interview a small business owner about creating a database for his sandwich shop and bakery. Make a list of questions for this initial interview. Remember at this point you just want the big picture and major requirements. Don’t get too deep into the details. 4. Look around the school or think of some businesses or non profits with whom you are familiar. Identify at least one situation in which a database could be a help. a. Describe why a database would improve the situation. b. Describe what the major topics of this database would be 46 Hands ON Database c. Write a statement of work for this database 5. An instructor has been keeping all his grade books in Excel for years. He has a separate spreadsheet for every course. In the spreadsheet he tracks the scores for every assignment and test and then assigns term grades based on the overall averages. Whenever a former student contacts him requesting a letter of recommendation or whenever the administration requests information concerning a student in a previous term he has to open and search several spreadsheets to get the student’s information. a. What are some of the advantages a database would have over the current system for this instructor? b. What would be some of the major topics for the database? c. Write a statement of work for the database above. Scenarios These scenarios are designed to give you the opportunity to experience database development from beginning to end. Each has its own unique challenges. They can be pursued individually or in small groups. I would suggest choosing one scenario that interests you to follow throughout the term. Later, if you are so inclined you can return and work through some of the others. Wild Wood Apartments owns 20 different apartment complexes in Washington, Oregon, California, and Idaho. Each apartment complex contains anywhere from 10 to 60 separate apartments, of varying sizes. All apartments are leased with a six month or yearlong lease. 47 Hands ON Database It is the company’s practice to hire one of the tenants to manage each apartment complex. As managers they need to admit new tenants to the building, collect rents from existing tenants, and close out leases. The manager also needs to maintain the apartments conducting any repairs, replacements, or renovations. These can be billed back to the parent company. For acting as manager, the tenant gets free rent and a stipend. The stipend varies depending on the size of the apartment building. Each manager is expected to send a report to the Wild Wood Apartments company headquarters in San Francisco every quarter. This report summarizes the occupancy rate, the total revenues in rent, the total expenses in maintenance and repairs, etc. Currently managers fill out a paper form and mail it back to headquarters. Many apartment managers have complained that preparing this report is a very difficult and time consuming process. Also, the managers at corporate headquarters have expressed concerns about the accuracy and verifiability of the reports. To allay these concerns and to improve the ease and efficiency with which the apartment managers conduct their daily business, the company is proposing to development a centralized database that can be used by the managers to track the daily business of their apartment building and to prepare their reports. To do 1. List the major topics for this database 2. Write a draft statement of Work. Include a brief history, a statement of scope, objectives and a preliminary timeline. 48 Hands ON Database Vince Roberts runs a vintage record shop in the University district. His shop sells 45’s, LPs and even old 76 RPM records. Most of his stock is used—he buys used vinyl from customers or finds them at yard sales and discount stores--but he does sell new albums that are released on vinyl. For a couple of years he has kept most of his inventory either in his head or in a spiral notebook he keeps behind the sale counter. But his inventory and his business have grown to where that is far from sufficient. Vince is looking for someone to make him a database. He knows he needs to get a better handle on several aspects of his business: he needs to know the extent and condition of his inventory. He needs to know the relative value of his inventory—some records are worth a fortune; some are nearly worthless. He also needs to track where, from whom and for how much he purchased his stock. He needs to track his sales. He often is not entirely sure how much money he has spent or how much money he has earned. In addition he would like to allow customers to make specific requests and notify them if a requested item comes in. More generally he would like to make an email list of interested customers in order to let them know about new items of interest. Someday, he would like to expand his business on-line. But he knows he needs to have everything under control before then. To do 1. List the major topics for this database 2. Write a draft statement of work. . Include a brief history, a statement of scope, objectives and a preliminary timeline. 49 Hands ON Database The law requires that any business, including a school track its software. It is important to know what software the school owns, in what versions, and what the license agreement for that software is. There are several different licensing schemes. The least restrictive is a "site" license which allows an institution to have a copy of the software on any machine on the business property. Other licenses specify a certain number of active copies for an institution but don't worry about which machine or user has the copy. The more restrictive licenses do specify one copy per a specific machine or user. Whatever the license agreement for particular software, it is essential for the institution to know which software is installed on which machine, where that machine is located, and what users have access to that machine. It is also important to track when the software is uninstalled from a machine, and when a machine is retired. An additional useful feature of any software tracking database would be to track software requests from users to determine 1) if a copy of the software is available, and 2) if it something that should be purchased. All installations are reviewed and must be approved. For now the school just wants the database to track faculty and staff computers and software. Software for student machines is a separate and complex issue and will be treated as a separate project at a later time. To do 1. List the major topics for this database 50 Hands ON Database 2. Write a draft statement of work. Include a brief history, a statement of scope, objectives and a preliminary timeline. A hospital is conducting a double blind test of a new depression drug. It will involve about 20 doctors and about 400 patients. Half of the patients will get the new drug and half will get traditional Prozac. Neither the doctors nor the patients will know who is getting which drug. Only two test supervisors will know who is getting what. The test will last about 18 months. Each doctor will see 20 patients initially, though it is expected some patients will drop out over time. Each patient will coming in twice a month for a checkup and interviews with their doctor. The drugs will be dispersed in a generic bottle by the two supervisors one of whom is a pharmacist. To track this study the hospital will need a database. It will need to track patient information from their first screening through each of their interviews. In particular they are looking at whether the patient seems more depressed or less, what their appetite is like, are they sleeping, what kind of activities they are engaged in if any. Also it will be looking for specific physical side effects such as rashes, high blood pressure, irregular heart rhythms or liver or kidney problems. 51 Hands ON Database Doctors need to be able to see their own patient’s information, but not other doctor’s patients. They also need to be able to enter blood pressures, blood test results etc, the depression indicators and their own notes for each session Patients should be able to see their own medical profile, the doctor’s notes, and nothing else. Only the two researchers should be able to see everything. All patient information, all doctors notes and which drug each patient is being given. There is always some danger of spying by other companies interested in similar drugs, so in addition to the security of the blind test, the database needs to be secured against outside intrusion as well. To do 1. List the major topics for this database 2. Write a draft statement of work. . Include a brief history, a statement of scope, objectives and a preliminary timeline. 52 Hands ON Database Chapter Two: Gathering Information (Interviews, Observations and Reviewing Documents) 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 sheets 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 and a couple of students. As a follow up she 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 Looking at the Documents Sharon has arranged to meet with Terry early in the morning. She arrives on time and Terry greets her. “Let’s go look at how students sign up for tutoring now.” 53 Hands ON Database Sharon follows Terry to the lab. On the counter of service station at the front of the lab there is a clipboard with sign in sheets for tutoring. Each sheet is for one week. Across the top are the days of the week. Down the left margin are times. Tutors mark the times they are available and what topics they are tutoring by listing their name and the class they are tutoring for in a time slot. Students sign up for a time slot. Figure 6: Morning Tutoring Appointments Tutoring for the Week of 4/12 to 4-16 2009 Monday Tuesday 9:00 AM TT: TT: CL: CL: ST: ST: ------------------------------TT: TT: CL: CL: ST: ST: ------------------------------TT: TT: CL: CL: ST: ST: Wednesday TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: Thursday TT:Aimes CL:(Math 290) ST:Laura Jones ---------------TT:Carson CL: (ITC 110) ST: --------------Johnson (ITC 224) Shanna Taylor Friday TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: 9:30 Am TT:Johnson CL:(ITC224) ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT:Carson CL: (ITC 110) ST:Peter Laws _________ TT:Johnson CL: (ITC 224) ST:Sara Lewis ---------------TT: CL: ST: TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT:Johnson CL:(ITC 224) ST:Bob Green ---------------TT: CL: ST: ---------------TT: CL: ST: 10:00 AM TT: CL: ST: ---------------TT: CL: ST: ---------------TT: TT: CL: ST: ---------------TT: CL: ST: ---------------TT: TT: CL: ST: ---------------TT: CL: ST: ---------------TT: TT: CL: ST: ---------------TT: CL: ST: ---------------TT: TT:Stevens C:(Math 100) ST:homas Seth ---------------TT: CL: ST: ---------------TT: 54 Hands ON Database CL: ST: CL: ST: CL: ST: CL: ST: CL: ST: 10:30 AM TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT:Mary L CL:(ENG 101) ST:Ly Poon ___________ TT:Sanderson CL:(ITC 110) ST: Anderson ---------------TT: CL: ST: TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT:Mary L CL:(ENG 101) ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT:Stevens CL:(Math 100) ST:Thomas ---------------TT: CL: ST: ---------------TT: CL: ST: 11:00 AM TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT:Mary L CL:(ENG 101) ST: Snodgrass ---------------TT: CL: ST: ---------------TT: CL: ST: TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT:Mary L CL:(ENG 101) ST:Martin Yang ---------------TT: CL: ST: ---------------TT: CL: ST: TT:Stevens CL:(Math 100) ST:Brown ---------------TT: CL: ST: ---------------TT: CL: ST: 11:30 AM TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT:Mary L CL:(ENG 101) ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT:Mary L CL:(ENG 101) ST: ---------------TT: CL: ST: ---------------TT: CL: ST: TT: CL: ST: ---------------TT: CL: ST: ---------------TT: CL: ST: Sharon looks at the sheets. “I presume TT: stands for tutor and CL: for class and ST: for student. Is that correct? 55 Hands ON Database Tracy nods, “Yes that is correct.” “Is this all the information you have about the tutoring sessions? How do you know if the student showed up or not?” Things You Should Know Gathering Information Before you can actually begin designing a database, you must understand what data the database needs to store and how that data will be used. It is tempting to think you understand the gist of what is going on and start sketching out tables and columns, but it is always better to wait. Gather information. Make sure that you understand exactly what the customer needs to store in the database and why. Gathering information is complex task. Most projects have many facets that need to be accounted for. It can be quite daunting, but there are some basic steps to help you proceed.  initial interviews with the chief stakeholders (the managers or executives that are initiating the database project  Review of business documents to identify data elements  interviews with stakeholders  Questionnaires  Work shadowing (observing the flow of Work Shadowing: following and observing person as they go through their work routine information) The initial interview should provide the overview of the Domain: the focus of the database. If the database is about the tutoring program, its domain is “tutoring” database. In it you define the domain of the database, that is what business tasks and information the database is meant to 56 Hands ON Database handle. You may get a few specific requirements in this initial interview, but the primary goal should be to get a clear picture of why the database is needed Requirement: Something the database must do. For instance, It must keep track of tutors and the classes they can tutor for. and what, in general, it is meant to do. One of the first tasks should be to review any business documents. Business documents consist of forms and reports related to the data, but can also include things like memos, organizational charts, mission statements, company goals, plans etc. Reviewing documents allows you to begin to make a list of what kind of content your database will have. It is important to ask about any abbreviation or item you don’t understand. Next you should identify the chief stakeholders. A stakeholder is anyone who will interact with the database directly or indirectly. A stakeholder is anyone who has a “stake” in the results. Stakeholders include the managers and the employees who will work with database. They probably also include IT staff who will develop, maintain and support the database. They may also include direct customers and business partners. Once you have identified stakeholders, you should arrange interviews with each group or possibly with all the stakeholders together. The purpose of the interviews is to Stakeholder: Anyone with a “stake” in the final product. Anyone who will use or be affected by the database get each stakeholders perspective on what data the database needs to store for their use and how they will need to process that data. Questionnaires may be more efficient to gather some types of information. You can often get responses from more people than in an interview. Questionnaires are best for technical information and close ended questions that require simple straight forward answers. 57 Hands ON Database Finally, it is extremely valuable to watch how people work with the system they currently have. You can observe the “flow” of the data, how it is captured, how it is used. It is also a valuable way to discover exceptions to the rule. “Oh, we always give Mr. Johnson a discount, he has been such a good customer.” or “Sometimes we wave the fee. It is up to the clerk.” If your Exception: A variation in how things are done or recorded, an alternate process database doesn’t allow for common exceptions it may prove too rigid to be useful. “I use these sheets but I also have the tutor’s reports. Each tutor is supposed to fill out short little report form for each session time they sign up for. In fact, the reports are my primary source of data. The signup sheets are just a check to make sure that I have all the report forms. Some tutors are a little lax about turning them in.” “Do you have any of those forms that I could look at?” Terry smiles, “Of Course. “ She walks behind the desk. “We keep the forms here for the tutors.” Sharon takes the form and looks at it briefly. “It seems simple enough.” Terry nods. “It is quite simple. We wanted the tutors to focus on tutoring not on paper work.” Figure 7: Tutor session Report Form Tutor Session Report Form Tutor Name Session Date Session Time StudentID Student Name (NA if no student signed up) Materials covered (NS if no show) 58 Hands ON Database Sharon asks, “Does it give you the information you need to make your reports.” Things to Watch Out For Make sure you understand all the terms and abbreviations on the forms and reports you review. Terry smiles wryly. “That’s difficult to say. I use them, but it’s certainly not easy to make my reports from them.” Sharon says, “Maybe you can show me some of the reports Form: a document, paper or electronic that is used to gather data Report: a document paper or electronic used to display summarized or formatted data you need to make and explain what you have to do to complete them.” “No problem, let’s go back to my office. Things You Should Know Reviewing Business Documents The forms and reports a business uses to gather and disseminate information are an invaluable source for understanding several aspects of a business’ data needs. For one thing, they provide clear insights into the daily business processes. They show how information is gathered about various transactions, and then how that information is passed to other people and departments. Studying business documents can reveal not only what information is needed, but when and in what sequence. Secondly, carefully scanning the forms and reports will reveal many, perhaps most, of the individual pieces of data the database will need to contain. Business documents can reveal how the data will be used, how it will be summarized, analyzed and presented. 59 Hands ON Database There are several kinds of basic business documents that can be relevant. Two of the most important documents are forms and reports. Forms are documents, either on paper or on the computer that businesses use to capture data. They are used to “input,” things like new customer information, sale details or an employee’s hours. Reports are documents that present “output” from the system. They summarize and analyze the data that was collected through forms and other means, or the current status of inventory. Several other types of documents can also be useful when trying to get a picture of the data a database need to process. Manuals and procedures can give you a sense of how things are processed, or, at least, how they are supposed to be processed. Memos and letters can provide some insight into issues that can arise in the Procedures: documents that describe the approved steps for completing some business process. A “How to” document. system and also provide a sense of how the information moves through an organization and who is responsible for what parts of the information. Annual reports offer insights into the state of the organization and into what function the proposed database might serve within the broader business context. Even mission statements and goals can be useful. A database should be supportive of the mission and contribute to one or more of the stated goals. In her office Terry logs into her computer and brings up Excel. She opens a spreadsheet. “Here is an example of a simple time sheet.” 60 Hands ON Database Tutor Pay For weeks beginning Tutor Aimes, Tabatha Carson, Karen Johnson, Luke Lewis, Mary Sanderson, Nathan Stevens, Robert Totals 4/6/2009 and 4/16/2009 Week1 Week2 Total Hours 0.5 2 2.5 8 10 18 3 4.5 7.5 1 3.5 4.5 3 3 6 4 5.5 9.5 19.5 28.5 48 Wage $ 10.50 $ 10.50 $ 10.50 $ 10.50 $ 10.50 $ 10.50 Gross Pay $ 26.25 $ 189.00 $ 78.75 $ 47.25 $ 63.00 $ 99.75 $ 504.00 Figure 8: Tutor Pay Spreadsheet Sharon looks over the spreadsheet. “You get the hours for each tutor by going over those signup sheets and the Report forms?” “Yes.” “I imagine that can be labor intensive and error prone.” “You can only imagine. I used to assign this task to a work study student. But, no matter how good they were or how much I trusted them, I never felt confident until I had rechecked all the materials. So now I just do the payroll report myself.” “I think we can make this task a lot easier with a database and a lot more accurate. What other reports do you have to make?” “Well, one important report is total student Usage. For this I report the total of all sessions attended by students in a term and then the unduplicated count of students” “Unduplicated means you only count each individual student once. Is that correct?” “Yes. We need to know how many total tutoring sessions are attended, but we also need to know how many individual students are taking advantage of the tutoring.” 61 Hands ON Database Tutorial Usage Term 1 2009 2345 2500 2000 1735 1500 1000 500 0 Total Unduplicated Figure 9: Total and Unduplicated Students “Here are two other important reports. The first two charts cover demographics and the second for what tutoring topics are most sought after.” Unduplicated Student Count by Gender F 54% M 46% Figure 10: Gender Report 62 Hands ON Database Unduplicated Student Count By Ethnicity NAmer 2% PacIs 7% other 5% Asian 18% white 50% AfrAm 18% Figure 11: Ethnicity Report 63 Hands ON Database Unduplicated Students by Subject Area SCI 14% BUS 7% ENG 20% HIS 8% ACC 8% ITC 8% MAT 35% Figure 12: Subject are Usage Sharon looks at the charts carefully for a moment and then asks a question: “How do you get the demographic information?” Terry sighs, “It’s not always easy. As long as the tutor Things to think about: remembers to put in the studentID number, I can locate the Are there other forms you would have asked to look at, if you were Sharon? student on the School’s enrollment database. I can get their What other kinds of forms could be relevant to the tutor database? gender and ethnicity information there. If there is no student number for a particular student on any of the forms turned in, I can usually locate a student on the School’s enrollment database by searching for their last name and comparing that with the classes they are enrolled in and what topics they are seeking tutoring in. The hardest part is actually the unduplicated counts. I have to manually eliminate duplicates.” 64 Hands ON Database “That sound like way too much work.” “Believe me it is. But many of our grants depend on ethnicity reports. We must show that we are serving a diverse population. Here is the actual spreadsheet I use to create the charts.” Report Statistics Fall Term 2010 Students Total Usage Unduplicated Usage Difference 2345 1735 610 Workforce retraining Unduplicated Demographics Male Female Total 937 798 1735 Ethnicity White AfAm Asian PacIs Namer Other Total 868 312 312 121 35 87 1735 By Subject Area ACC BUS ENG HIS ITC MAT SCI Total 139 121 347 139 139 607 243 1735 65 247 Hands ON Database Sharon looked over the spreadsheet. “You have to gather all that information by hand? I have just a couple of questions about some of the abbreviations. Does “PacIs” mean “Pacific Islanders?” “Yes.” “Also what does “Workforce Retraining” refer to?” “Several students receive are identified as workforce retraining. Usually they are students who have lost their jobs and have been given government grants to return to school. Workforce retraining will pay for tutoring for those students.” “How are other students covered?” “We get some money from different federal grants. Often these are tied to the diversity of the students we serve. Some are paid from funds at the college.” “Does the database need to track which students qualify for which funding?” “No, I can handle that. If I can just get the basic counts and statistics easily, it will make my life a hundred percent better.” Transaction Database—a database that is optimized to keep track of transactions such as sales or purchases in real time Things You Should Know Types of database Relational databases can serve different needs for different users. These different needs can require different setups and structures. 66 Hands ON Database One common usage of a relational database is as a Transaction database. A transaction database, as its name suggests, records the data from immediate transactions such as sales or orders in real time, as they happen. These databases can be attached to a point of sale at a cash register or they can be behind web forms such as at Amazon.com or Ebay. Transaction databases are optimized for speed and efficiency. Nobody wants a long wait while his or her order is being processed. Also, given the global nature of business, it is essential that many of these databases be as available as possible, preferably 24 hours a day seven days a week. Another common use for a database is as a Management Information System(MIS). The purpose of a MIS is to use the data to provide data managers need to manage an organization. A management information system focuses on data analysis. It is used to query data to return reports on things like total monthly sales, number of products sold, total shipping costs, etc. The MIS bases its reports on the data gathered by the Management Information System—a database optimized for queries that return summary information about transactions Transaction database. In a simple situation like the tutoring program where the number of users is relatively small, the Transaction database and the MIS database may be the same physical database. But in enterprise organizations they are usually separated. They types of queries that a MIS runs to retrieve the data for reports would slow down the performance of the transaction database more than is acceptable. So, Business Intelligence—a set of tools for analyzing trends and patterns in business data typically, the data is periodically copied or shipped from the transaction database to the Management Information system. Increasingly, DBMS software is including tools for Business Intelligence. Business Intelligence moves beyond management systems. Business intelligence systems mine data for patterns and trends that 67 Hands ON Database might help a business improve its offerings or services. A Things to Think About Why do you think the idea of data warehouses and data mining hold such an attraction to organizations? What are some of the advantages an organization could get from using Business Intellegence tools? company, for instance, might analyze its customer data to find the ages and incomes of the customers who buy a particular product. They might look to see what other products those customers have purchased in a six month period before or after the purchase of a particular product. They might look to see if they can spot a trend related to current advertising or a current event. Data Mining, exploring data sets looking for useful trends, is related to the idea of Data Warehouses. The concept of a data warehouse is to bring together all the data that an organization generates, not just the transactions that are Data Mining—using business intelligence techniques on a variety of data sources brought together in a Data Warehouse recorded in formal databases, but also the memos, letters, minutes from meetings and other documents any organization generates. The data warehouse brings them all together in a way and a place where data can be extracted and compared. The concept of the data warehouse is obviously very attractive, but it has proven very difficult to bring about in practice. New RDBMS’s have included tools to incorporate more heterogeneous data such as documents, but it is still difficult to compare data from the different sources. One development that holds the promise of making data warehousing a reality is XML—marked up Unicode text that follows a few strict rules— increasingly used as a file format for documents and data transferal xml. XML is a set of technologies based on Unicode. XML is marked up text that follows a few simple rules. Ideally, an xml document is self describing, that is the markup tags tell a user what the text between consists of. 68 Hands ON Database Increasingly business documents are saves as XML. (It is now the default format for Microsoft documents.) Tools have been developed for querying XML, allowing a user to extract and compare pieces of documents. RDBMS systems have also added data types and tools to store and manipulate XML documents. These developments may make data warehousing a fully realized business tool. XML will be dealt with more thoroughly in Chapter 10. Sharon stands up. “Thank you. Looking at these reports will help me a lot. They give me a much better idea of what kind of data Things to think about The confidentiality of data in a database is a major issue for many companies. The database may contain private information about employees or clients, or it may contain data that could competitors could use to gain a competitive edge. Can you think of some ways that you could assure a client that you will keep all their data confidential? the database needs to track and store. Do you think I could get some copies to look at? I think I would also like to see examples of reports you have to make to your funding sources.” Terry hesitates for a moment, “I think I can do that—but some samples might have confidential information on them.” “I understand. You can give me blank ones, or you can black out private information. I promise not to divulge any information that could even remotely be considered private. I’ll even sign something to that effect if you want.” “That shouldn’t be necessary. I will get copies of the things I showed you and the other reports and get them to you tomorrow. What’s next?” “Thanks, The next thing I really need to do is interview some of the tutors and, if possible, a student or two to get their perspective. It is a good idea to have some representation of all the stakeholders. Is there a good time to do that?” 69 Hands ON Database Terry thinks for a minute. “We have a tutor’s meeting once a month. The next one is the day after tomorrow at 9:00 AM in Room 301. Would that work?” “Yes that would work just fine, ...
Purchase answer to see full attachment