IT650 Labs

HANDS ON DATABASE by Steve Conger © 2010 Hands ON Database 2 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 narrati ve 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 th at parallel the narrative. After completing this book student s will have a good sense of what is involved in developin g 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 def inition 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 Hands ON Database 3  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 i s 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 so lution 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 repor ts 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 abo ut defining its requirements, designing it, and building a prototype. She enters some sample data and then Hands ON Database 4 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 chapte r, 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 sho p 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 inv olved, 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 e nded, 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 stu dents 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 Hands ON Database 5 among the data. It shows them the value of carefully designing a databa se 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 a gainst 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, b ecause 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. =n 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 Thi rd 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 aggre gate 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. Hands ON Database 6 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 considerati on 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 c ould 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 pro cedures 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 Hands ON Database 7 course subject to change. But one can always go to the Microsoft site an d 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 Micr osoft 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 managem ent 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. Microso ft 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 softwa re 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. Hands ON Database 8 Chapter Conventions Each c hapter 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 A bout 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 answe rs. 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 t o other resources and topics not fully covered in the book. Hands ON Database 9 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 highlight ed 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 practic es 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 s tudents 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 w ould 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. Hands ON Database 10 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 disco vers 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 Da tabases  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 supervi sor, and creates a questionnaire for students who use the Hands ON Database 11 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, Shar on 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 Hands ON Database 12 Chapter Four: Database Design Narrative: Sharon is ready to design the database. She looks at her topics lists and diagrams an initial set o f 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 assig ns 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  Res olve 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 Hands ON Database 13 Chapter Six: Physical Narrative: Sharon builds a prototype of the database, creating all the tables and setting up the relationships. (SQL Server Express –though = could other DBMS’s also.) When she has it set up she en ters 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 wri tes 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 q uery  Use the Aggregate Functions COUNT, AVG, SUM, MIN, and MAX  INSERT, UPDATE and DELETE records  Use SQL to test business rules Hands ON Database 14 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 pl anning. 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 s pecial focus on the database templates Hands ON Database 15 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 stu dents 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 th an 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 op timize 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. Hands ON Database 16 Chapter One : Who Needs a Database Overview of Relational Databases and their Uses In this chap ter we meet Sharon, a college student who is working toward a degree in Database Development and Administration. She signs u p 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 posi tion 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 stud ent taking databa se 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 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 17 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 col lege is named Terry Lee. Terry invites Sharon into her office and offers her a seat. She smiles. “So you want to tutor?” “Yes. = think = would be good at it.” “What subjects do you think you could tutor?” “= was thinking especially of database related topics. = can do relational design and SQL. I think I could tutor M icrosoft Access, Sql Server and even other Database Management Systems. = 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 Sho uld Know Databases 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 modif y and retrieve data. Hands ON Database 18 A database is, at its simplest level, a collection of related data. =t 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 an d 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 files usually consist of a file which store data i n 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 n o protections for data integrity and they often contain many redundant elements. Spreadsheets, such as Excel, can also be used as flat file 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 Redundancy — refers to storing the same data in more than one place in the database Data Integrity — refers to the accuracy an d the correctness of the data in the database 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 Hands ON Database 19 data (For inst ance, 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 despit e these enhancements spreadsheets still share many of the redundancy and data integrity probl ems of other flat file formats. Figure 1: Delimited Text Example Hands ON Database 20 Figure 2: Excel Spreadsheet Hierarchical Dat abases 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 par ent. 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 i n those subdirectories can be other subdirectories or files. You navigate through them by following a path . Hands ON Database 21 C: \Users \ITStudent \Documents \myfile.txt 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 na med Edgar F. Co dd 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 (Ac tually 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 o f repe ating an attribute or column 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 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 dat abases 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 compan ies to upgrade to their newest database products? Hands ON Database 22 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 ty pes and disk writing and reading functions. :e 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 datab ase 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 a ccount. But you might Hands ON Database 23 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 informat ion, an address or phone number, for example, the greater the likelihood that one of the entries could miss be ing 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. =n 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 a llowed 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) LastName FirstName Address City State C41098X3 Carson Lewis 121 Center Street Seattle WA CV1099B1 Madison Sarah 1324 Broadway Seattle WA D345XU24 Brown Lisa 2201 Second Ave Seattle WA TransactionID TransactionType TransactionDate CustomerID (FK) Amount 10002345 Deposit 2009 -2-12 10:25:06 C41098X3 1245.76 10002346 Deposit 2009 -2-12 10:27:13 CV1099B1 500.00 10002347 Withdrawel 2009 -2-13 -14:45:57 C41098X3 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. Hands ON Database 24 The chief drawbac k 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. T hese 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 conta ining several pieces of paper . “We have 2 page s 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.” “:ow do you check that?” “Right now it is mostly a matter of trust.” “:ow 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.” Hands ON Database 25 “Thanks.” Sharon looks over the notebook. “Hust out of curiosity, what do you do with the schedules at the end of the month?” “Actually, = take them back to my offic e 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. =t would make it much simpler to track schedules and availability and it could make doing your reports much easier.” Terry sighs. “=’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 bus y and no one else feels competent or has the time to take on the task.” Sharon hesitates a little, then says, “= 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 c ould pay you something for your work.” “= am still learning database,” Sharon cautions, “but = 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.” “= will be there.” Things to think about There are many situations that could be improved with the addition of a database. Whenever ther e 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. Hands ON Database 26 Things you should know RD BMS A Relational Database Management System (RD BMS) is, as its name suggests, a system for managing relational databases. As a minimum an RD BMS 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 user s and groups have access only to the databases and data that they are authorized to use. Most com mercial RD BMSs offer many features beyond these basic ones. Most include tools for monitoring and optimizing the performance of their databases. Many include r eporting 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 RD BMS Comments URL ORACLE The first commercial RDMS and the biggest.

Powers many of the world’s largest companies http://www.Oracle.com SQL Server Microsoft’s RDMS product. Ships in many versions designed for different company needs.

Also powers many large enterprises http://www.microsoft.com/sql/default.mspx DB2 IBMs RD BMS http:/ /www306.ibm.com/software/data/db2/9/ MySQL The most popular Open Source RD BMS currently http://www.MySql.com Hands ON Database 27 owned by SUN ACCESS Microsoft’s Desktop Database http://office.microsoft.com/en ; us/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. :ow can = help you today? ” She t ells 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, “=’ve got a thousand ideas about how the database should look and what should b e 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 des igning 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 m aking 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.” Hands ON Database 28 “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 need any help or advice on this project, feel free to ask me.” “Thank you. Thank you very much.” Th ings 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 clarifi cation 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 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 Hands ON Database 29 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 The First Interview The next day Sharon sit s 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 dat abase she creat ed 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 ea ch 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.” Element Description History 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. Scope 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. Objectives 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 sup posed to achieve, why the client wants the database in the first place. Tasks and Deliverables Breaks the project into discrete tasks. Each task should have an estimated duration and concrete deliverables. Hands ON Database 30 Sharon nods, “= really hope = 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” Hands ON Database 31 Figure 5: Sample Entity Diagram for a Non -Profit Terry looks at the diagram as Sharon explains it. Hands ON Database 32 “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. =t uses what is called “crows 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 Cl ientNotes 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 i s associated with a particular councilor and can involve several different types of assistance. That is the reason for the AssistanceDetail table.” “=t looks complex.” “=t is a little. But = also built some forms and reports that made it so the Capital Cha rities staff didn’t have to navigate the database directly. =t 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. = 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.” 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. Hands ON Database 33 Terry hesitates, “Ok…Where do = start.” “You already suggested a couple of things. You need to track what works and what doesn’t. :ow would you determine that something is working or not working?” Things you should know You sh ould 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 course s are they ta king tutoring for and how the tutoring they receive help s the m succeed in the ir course s. Do they get better grades? Does tutoring st op 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 st udents 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?” Hands ON Database 34 “=f we could, that would be great. =t 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.” “= 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 = can think of right now.” “OK. What = 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. =f 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.” Hands ON Database 35 “Thanks,” Terry stands up. “= actually think we can do this. You really seem to know what you are doing.

I am loo king forward to it.” Sharon smiles, though she doesn’t feel nearly as confident in her abilities. “= 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 “=s there anything else ? Anything = 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 Hands ON Database 36 She thinks a bit longer. “We need to track whether students attended the sessions they scheduled . That is important, but is it a n ew topic? =t 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 databa se like a good table should be focused on a single topic. She decides to leave the list as it is . Things you should know Identi fying the major topics of a database is an important 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 ident ifying 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 topic s 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 S tatement of Work 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) Hands ON Database 37 Now that she has the big topics in mind, she begi ns 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 situa tion 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 significan t 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 signifi cantly 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 prett y well. The tutoring database will be designed to manage the tutoring program at the college. She isn’t real happy w ith that as an opening sentence. She modifies it a little and forge s 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 state ment: Hands ON Database 38 The tutoring database will manage data for the tutoring program at the college. It will track available tutors and the courses they can tutor. =t will also track each tutor’s tutoring schedule. The database will store demographic information for stu dents 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. 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 momen t, 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 th e objectives are spelled out in the scope. She pulls out some of the main points and makes a list. 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 i nto a column in a table Hands ON Database 39  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 tut oring program  =mprove 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 da ta. 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 coupl e 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 c lient application for Terry, the tutors and students to interact with the database. But, Sharon says to herself, one project at a time. Hands ON Database 40 Sharon almost has everything she needs for the statement of work, 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, “=t is important that we both are clear about what we are working on. = 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, = can see that is a really good idea.” She sets the paper down.

“What about the surveys of student success?” “= thought about that, and = am not sure. Sometimes = 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 a ble to add the success tracking features later or we could look at adding a second database devoted to tracking student success .” “Ok, = can live with that. =t would be nice if you could validate student information.” Things to Think About: Estimating Times One of the most difficult things for anyone who is new to developing database s 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 aver age. 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 pro bable 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? Hands ON Database 41 “Yes, but = don’t really know how to do that. I also think it unlikely that I would be granted the permissions = 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. =t would help us know where the need is and where we need to try to recruit new tutors.” “That shouldn’t be a problem. = 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 act ually make the database and test it.” Terry studies the timeline. “This is very clear and well done. :ow realistic do you think this timeline is.” Sharon smiled. “=t represents my very best guess. It could go faster if everything works out well, but it cou ld 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 w arns, “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.” Hands ON Database 42 Terry smiles. “You’re right. We can tack le 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. = 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 a nd 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. =t will also track each tutor’s tutoring schedule. T he 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. Hands ON Database 43 Constraints The database ca n 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  =mprove tracking of tutor’s hours and students use of tutoring sessions  Track Student requests for additional tutoring TASKS AND TIME LINE 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 sy stem. 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 req uirements 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. Samp le 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. Hands ON Database 44 Things we have d one 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 gener al 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. Hands ON Database 45 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 no n alphanumeric character . e. Files where the length in characters of each column is the same . f. Refers to the accuracy and the correct ness 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 ide ntifies 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 datab ase 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 summar izing what you find. Hands ON Database 46 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 towar d 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 add itional 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 j ustify 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 g oing 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 t he 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 datab ase would improve the situation. b. Describe what the major top ics of this database would be Hands ON Database 47 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 an d 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 s earch several spreadsheets to get the student’s information. a. What are s ome 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. Hands ON Database 48 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 re pairs, 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 repo rt 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 re ports. 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 busines s 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. Hands ON Database 49 Vince Roberts runs a vintage record shop in the University district. :is 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 viny l. 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 ha ve grown to where that is far from sufficient. Vince is looking for someone to mak e him a database. He knows he needs to get a better handle on several aspects of his busi ness : 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 nearl y 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 custom ers 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. Hands ON Database 50 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 leas t 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 ha s 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 mach ine 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 softw are 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 comp uters 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 Hands ON Database 51 2. Write a draft state ment of work . Include a brief history, a statemen t 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 som e 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 hospit al 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 sleepi ng, 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. Hands ON Database 52 Doctors need to b e able to see their own patient’s info rmation, 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 th e 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. Hands ON Database 53 Chapter T wo : 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 student s. As a fol low 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 re levant 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.” Hands ON Database 54 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 a re 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 Wednesday Thursday Friday 9:00 AM TT: CL: ST: ---------------- TT: CL: ST: ---------------- TT: CL: ST: TT: CL: ST: ---------------- TT: CL: ST: ---------------- TT: CL: ST: TT: CL: ST: ---------------- TT: CL: ST: ---------------- TT: CL: ST: TT: Aimes CL: (Math 290) ST: Laura Jones ---------------- TT: Carson CL: (ITC 110) ST: --------------- Johnson (ITC 224) Shanna Taylor 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: Joh nson 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: Hands ON Database 55 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. “= presume TT: stands for tutor and CL: for class and ST: for student. Is that co rrect? Hands ON Database 56 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 th e 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 b asic steps to help you proceed.  initial interviews with the chief stakeholders (the man agers 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 information) The initial interview should provide the overview of the database. In it you define the domain of th e database, that is what business tasks and information the database is meant to Work Shadowing : following and observing person as they go through their work routine Domain : the focus of the database. If the database is about the tutoring program, its domain is “tutoring” Hands ON Database 57 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 and what, in gen eral, 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 impor tant to ask about any abbreviation or item you don’t understand. Next you sh ould identify the chief stakeholders. A stakeholder is anyone who wil l 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 a nd 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 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 th an in an interview. Questionnaires are best for technical information and close ended questions that require simple straight forward answers. Stakeholder : Anyone with a “stake” in the final product. Anyone who will use or be affected by the database Requirement : Something the database must do. For instance, It must keep track of tutors and the classes they can tutor for. Hands ON Database 58 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. =t is also a valuable way to discover exceptions to the rule. “Oh, we always give Mr. Hohnson a discount, he has been such a good customer.” or “Sometimes we wave the fee. =t is up to the clerk.” =f your database doesn’t allow for common exceptions it may prove too rigid to be useful. “= use these sheets but = also have the tutor’s reports. Each tutor i s 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 = could look at?” Terry smiles, “Of Course. “ She walks behind the desk. “We keep the forms here for the tutors.” Sharon takes th e form and looks at it briefly. “=t seems simple enough.” Terry nods. “=t 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) Exception : A variation in how things are done or recorded, an alternate process Hands ON Database 59 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. = use them, but it’s certainly not easy to make my report s from them.” Sharon says, “Maybe you can show me some of the reports you need to make and exp lain 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 b usiness 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 abo ut 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. B usiness documents can reveal how the data will be used, how it will be summarized, analyzed and presented. 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 Hands ON Database 60 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,” thi ngs 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 o ther types of documents can also be useful when trying to get a picture of the data a database need to process. Manuals and proced ures 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 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 a nd 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. “:ere is an example of a simple time sheet.” Procedures : documents that describe the approved steps for completing some business process. A “How to” document. Hands ON Database 61 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.” “= 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.” “= 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 = report the total of all sessions attended by students in a term and then the undup licated count of students” “Unduplicated means you only count each individual student once. =s 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.” Tutor Pay For weeks beginning 4/6/2009 and 4/16/2009 Tutor Week1 Week2 Total Hours Wage Gross Pay Aimes, Tabatha 0.5 2 2.5 10.50 $ 26.25 $ Carson, Karen 8 10 18 10.50 $ 189.00 $ Johnson, Luke 3 4.5 7.5 10.50 $ 78.75 $ Lewis, Mary 1 3.5 4.5 10.50 $ 47.25 $ Sanderson, Nathan 3 3 6 10.50 $ 63.00 $ Stevens, Robert 4 5.5 9.5 10.50 $ 99.75 $ Totals 19.5 28.5 48 504.00$ Hands ON Database 62 Figure 9: Total and Unduplicated Students “:ere are two other important reports. The first two charts cover demographics and the second for what tutoring topics are most sought after.” Figure 10: Gender Report 2345 1735 0 500 1000 1500 2000 2500 Total Unduplicated Tutorial Usage Term 1 2009 M 46% F 54% Unduplicated Student Count by Gender Hands ON Database 63 Figure 11: Ethnicity Report white 50% AfrAm 18% Asian 18% PacIs 7% NAmer 2% other 5% Unduplicated Student Count By Ethnicity Hands ON Database 64 Figure 12: Subject are Usage Sharon looks at the charts carefully for a moment and then asks a question: “:ow do you get the demographic information?” Terry sighs, “ =t’s not always easy. As long as the tutor remembers to put in the studentID number, I can locate the student on the School’s enrollment database. = can get their 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 unduplic ated counts. = have to manually eliminate duplicates.” ENG 20% MAT 35% ITC 8% ACC 8% HIS 8% SCI 14% BUS 7% Unduplicated Students by Subject Area Things to think about: Are there other forms you would have asked to look at, if you were Sharon? What other kinds of forms could be relevant to the tutor database? Hands ON Database 65 “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 ar e serving a diverse population. Here is the actual spreadsheet I use t o create the charts.” Report Statistics Fall Term 2010 Students Total Usage 2345 Workforce retraining 247 Unduplicated Usage 1735 Difference 610 Unduplicated Demogra phics Male 937 Female 798 Total 1735 Ethnicity White 868 AfAm 312 Asian 312 PacIs 121 Namer 35 Other 87 Total 1735 By Subject Area ACC 139 BUS 121 ENG 347 HIS 139 ITC 139 MAT 607 SCI 243 Total 1735 Hands ON Database 66 Sharon looked over the spreadsheet. “You have to gather all that information by hand? = have just a couple of questions about some of the abbreviations. Does “Pac=s” mean “Pacific =slanders?” “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.” “:ow 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, = can handle that. =f = ca n just get the basic counts and statistics easily, it will make my life a hundred percent better.” 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. Transaction Database — a database that is optimized to keep track of transactions such as sales or purchases in real time Hands ON Database 67 One common usage of a relational database is as a Transaction database. A transaction database, as its name su ggests, 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 r egister or they can be behind web forms such as at Amazon.com or Ebay. Transaction data bases 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 sol d, total shipping costs, etc. The MIS bases its reports on the data gathered by the 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 t he 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, 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 Management Information System — a database optimized for queries that return summary information about transactions Business Intelligence — a set of tools for analyzing trends and patterns in business data Hands ON Database 68 might help a business improve its offerings or services. A 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 recorded in formal d atabases, 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 inc orporate 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. 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. Data Mining — using business intelligence techniques on a variety of data sources brought together in a Data Warehous e XML — marked up Unicode text that fol lows a few strict rules — increasingly used as a file format for documents and data transferal 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? Hands ON Database 69 Increasingly business documents are saves as XML. (It is now the default format for Mic rosoft 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 warehousi ng 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 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, “= think = 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. =’ll even sign something to that effect if you want.” “That shouldn’t be necessary. = will get copies of the things = showed you and the other reports and get them to you tomorrow. What’s next?” “Thanks, The nex t 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?” 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 o r 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? Hands ON Database 70 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, thank you.” “=’ll ask the tutors if they know of any students willing to attend.” Sharon pauses a moment, thinking and then asks “:ow long do these meetings last?” Terry says, “About an hour .” “And how many minutes can = use of it?” Terri thinks for a minute. “= think we can give you 45 minutes of it.” “Thank you. = will see you then.” Things You Should Know Interviews Interviews can be an excellent way of gathering information. They are especially good for asking “open ended qu estions.” Open ended questions are questions that don’t have a definite answer. You can ask things like “What is the best thing about the current system?” or, “ what would you most like to see out of a new system?’ You can also ask questions like “describe what a typical sale is like,” or “walk me through the process of registering a new customer.” There are several things to think about when conducting interviews: You need to make sure you capture the points of view of every stakeholder group. It is not en ough to get the manager’s perspective on what a database should contain and do, you also need to get the Open ended questions : Questions that don’t have a fixed answer, that involve getting a participants opinion or thoughts on a topic Hands ON Database 71 perspective of the people who are going to work with the data every day. You need to get the opinions and needs of the IT people who will have to supp ort and maintain the system. It is also likely that you will want to get some reaction and ideas from customers who will also be affected by the new system. Often it is a good idea to get these opinions in separate interviews. You don’t want those who wor k with the system to be intimidated or influenced by their managers. But if you can’t do the interviews separately, try to get as many different groups of stakeholders together as you can and, if possible, arrange an outside facilitator —someone with no sta ke in the system, whose whole purpose is to make sure the process unfolds as fairly and completely as possible. If you are conducting the interviews, it is absolutely vital that you be prepared. Know what questions you want to ask ahead of time. Know how m uch time you want to devote to each question. Also know how much time you can allot for follow -up questions or clarifications. Layout the rules and timelines at the beginning of the interview so that everyone understands the process. During the interview y ou must act as a facilitator. As such, your chief responsibilities will be to ask the questions and make sure everyone has a chance to respond. It is important to keep people on track and to politely cut them off if they veer too far from the subject or if their response is too long for the time allotted and will prevent others from having their turn. It is a delicate process, because you want as full an answer as possible. Because facilitating is such a complex task, you should not complicate it further by also being the note taker. If you attempt to take notes, you will find either that you have to pause the interview while you record the responses or you will continue with the interview and your notes will be incomplete. Neither is optimal. Assign this task to someone else, or use a recording device. Hands ON Database 72 Preparing for the interview That evening Sharon makes some notes for questions to ask during the interview. It is important that she ask the right questions. She jots down a few questions for the tutors.  How do you set your schedule?  How does a typical tutoring session go ?  What do you enter into the topics covered section of the report ?  How do you cancel a session ? Next she thinks of some questions to ask the students.  How do you figure out what tutoring is available?  How do you sign up for a session ?  Would you be willing to enter demographic information to get tutoring ? Sharon takes out her laptop and works out an interview plan. Tutor Interview Total time : 45 minutes. Allow 5 minutes for introductions. Question For Time Allotted How do you set your schedule? Tutors 5 min utes How do you sign up for a tutoring session ? What would make the process easier? Students 3 min utes What do you typically put in the section of the tutor report labeled “Materials Covered?” Tutors @ minuteV How do you check to see if a student is in the class they are being tutored for? Tutors 3 minuteV Why do you sometimes not get the studentID number Tutors @ minuteV How do you cancel a session? Tutors ,Students C minuteV Hands ON Database 73 Would you be willing to enter your demographic information to get tutoring? Students 2 minute What is the number one thing you would like to see changed in the current system? Tutors, Students 8 minutes What part of the current system do you like? Tutors, Students 5 minutes Time for follow up questions Tutors, students 5 minutes Figure 13: Interview Plan Things to Watch Out For Always prepare for an interview. Conducting an interview without planning, at best, will result in an interview that is less focused than in could be, and, at worst, could result in an awkward disaster that could erode confidence in you and the database project. Sharon looks over the plan. It looks goo d on paper but it is a pretty tight schedule. She is going to have to keep close track of the time. She is also worried about keeping notes. It will be almost impossible to both keep notes and facilitate the session. Then she remembers a digital recorder s he had bought to record class lectures. She hadn’t used it much because she found she preferred to type the lecture notes directly into her laptop, but for this interview it would be perfect. Now she felt ready and could relax. The interview Sharon arrives a few minutes early for the Monthly Tutors meeting. She waits for a moment at the door of room 301, reviewing the questions and the timing in her mind. She was going to have to make sure the answers were concise which could be difficult. People tended to want to talk and go off on tangents and accounts of personal experience. Shortly after Sharon arrives, Terry walked up and opened the room. “Good Morning,” she said. Over the next five minutes several people arrived and took seats.

When it is time for the meeting to start, Terry stands in front of the classroom and introduces Sharon. Hands ON Database 74 Sharon stands and smiles, “Good morning. = think the first thing we should do is introduction. Most of you probably know each other, but I would like to know you better. Just tell your name and what you tutor, or, if you are a student, give me your name and what subject you are getting tutoring for. We can start with you.” She points to a young man sitting in the back corner of the room. Sharon listens as each person introduces themselves. She jots down their first names as they do the introduction. There are nine tutors and two students. Sharon is surprised to learn during that one of the tutors is not a student at the school. He is in fact an MBA student from another school. T erry explains, “ Not all our tutors are students. We utilize people from the community and other schools who want to participate in our tutoring program. ” A tenth tutor arrives late. Sharon smiles as he enters and asks him to introduce himself. Then, wit h a glance at the clock, Sharon begins: “As Terry said, = am working on building a database to help keep track of tutoring. I hope it will make all your lives a little easier. To build it, I need to understand what you do better, and what you would like to see, so I am going to ask you some questions. We don’t have much time this morning, only about 30 minutes, so we are going to have to keep the answers pretty short. I will leave you with my email so you can let me know of things that you forgot about or d idn’t have a chance to tell me, or any questions you might have. Also, = am going to record your answers on my digital recorder, if no one objects. It will make it so I can focus on your answers.” Things to Watch Out for If possible use a recorder or have someone else take notes. It is almost impossible to facilitate a meeting and take notes too. Sharon asks her first question. On e tutor explains how she figures out her schedule. The hours she is in class are obviously unavailable. But she also looks at th e meeting times for the classes she is tutoring. It doesn’t make much sense to schedule tutoring sessions for when the students would be in class. Then Hands ON Database 75 she decides how many hours she can do based on her own class work and other activities. The other tutors nod in agreement. “That’s pretty well how we do it too.” Terry chimes in: “Tutors can work any number of hours up to the maximum of 15 a week.” Sharon looks at the students. “ Jason, Sandy, how do you sign up for a session and w hat would make the process e asier? ” Hason looks a Sandy, she nods so he answers first. “= go into the computer lab and look at the signup sheets, First I see what time slots are available, then I look at who the tutor is. If I can I choose a tutor I know and like. It can be really ha rd sometimes to see what is available. The sheets can get pretty messy and it’s can be really hard to read some tutor’s handwriting.” Sandy adds. “=t would be nice if there were some easy way to search for all the sessions that go with a class and see the time and tutors. It would be really nice if you could look ahead too. I would love to schedule a series of sessions for a month or more, but the sheets don’t go out that far.” “The next question is for the tutors and it is pretty specific. =’ve seen the re port forms you are supposed to fill out for each session and = was wondering what exactly you put in the box labeled “Materials covered?” A female tutor, Sharon glanced at the list to recall her name —Ann, replies: “=t varies, sometimes = put a subject in l ike ‘quadratic equations’, or ‘ratios’, sometimes = put in a specific lesson number.” Another tutor replies, “= teach English. = usually put down things like ‘paragraphing,’ or ‘agreement’ or ‘sentence fragments’. We don’t put down everything in detail, j ust the gist of what we covered.” Sharon thinks of a quick follow up question for Terry. “=s that enough? Do you get the information you need?” Hands ON Database 76 Terry nods, “Yes, = really only need a general sense of what was covered.” Sharon looks at her list of questions . “This one is for the tutors again. How do you check to see if a student is registered in the class they are requesting tutoring in? How about you, Nathan? ” She has noticed that Nathan, one of the tutors, seems to be a bit reluctant. He is sitting with hi s arms crossed in a protective stance, and his expression is not as friendly as most of the others. He takes a few seconds before he answers. “= usually don’t check. = generally trust the student s. We really don’t have a good way to check anyway. We don’t have rosters for the classes, and we can’t really look it up.” :e pauses again for a moment and then adds, “= like the current system. =t’s flexible and easy to understand. Everybody is familiar with it. I am afraid that changing things will just make it all more complicated” Sharon smiles and says, “That’s good to know. = reall y hope that, in the end, this database will make everyone’s life easier, but you can help keep me honest. =f something makes things more complicated as we develop this, let me know and we will see if we can fix it.” Sharon proceeds with the rest of the in terview questions. She finds out that StudentIDs are missed because the form is filled out after the session and sometimes the tutor forgot to ask for it before the student left. Also, Mary tells her that the forms can be turned in a couple of different w ays. They can be left after each session at the desk for Terry to pick up. They also can be kept by the tutor and turned in directly to Terry at the end of the pay period. Sh aron also realizes, hearing the discussion, that canceling Things to Think About Change, such as creating an new database, affect people’s lives at work. It means a change in the way they have always done things. Some people anticipate change with excitement, looking forward to a new and hopefully better way to do things. Others are less enthusiastic. Some are actually resentful or see it as a threat. Don’t be too quick to dismiss the negative attitudes. They may well have valid reasons for feeling as they do. What would be the best way to handle such resistance in an interview? Do you think it would help to try to anticipate some of the objections before hand? Hands ON Database 77 sessions was going to b e a complicated matter, one that she was going to have to follow up on. The two students present are willing to enter their demographic information and don’t have any concerns, but Sharon isn’t sure everyone will feel the same. The one thing everyone would like to see changed is the scheduling process. And, the one thing everyone liked about the current system was it flexibility. When the interview is over, Sharon glances at the clock. Three minutes to spare. She thanks everyone for their participation, tu rns off her digital recorder. Before she leaves the meeting to Terry she asks if any of the tutors would be willing to let her shadow them as they go through a couple of tutoring sessions. Mary Lewis said that would be fine. “When would you like to do it?” “When is your next session?” “Tomorrow at 11:00 A .M. in the computer lab.” “OK, =’ll meet you there. Things to Watch Out for Go over your notes or recording within twenty four hours. It is important to review them while the memory of the interview is stil l fresh. The Questionnaire Sharon still has some questions about how the students who use the tutoring services will interact with the database. She suspects it will be very hard to get an interview set up with enough students to constitute a representative sample, so she decides to create a simple questionnaire that the tutors can give their students after a session. Tutoring Services Questionnaire Hands ON Database 78 1. Would you be willing to enter demographic information such as gender and ethnicity to sign up for tutoring? a. Yes b. No 2. Would you be willing to list the classes in which you are currently enrolled? a. Yes b. No 3. Which is the most important factor when you are looking for a tutoring session to sign up for? a. The particular tutor b. The time slot c. Neither of the abov e 4. When you can’t make a tutoring session which do you do most often ? a. Leave a note on the schedule b. Contact the tutor by email or phone c. Contact the tutoring office d. Simply not show for the session 5. Which of the following best describe s the process of finding a session and signing up? a. Difficult and Confusing b. Not as easy as it should be c. Not too difficult d. Easy 6. If you could sign up on line, which layouts would you prefer. Rank them in order of preference a. __View all available tutoring sessions for all classes b. __View all available tutoring sessions for a specific class Hands ON Database 79 c. __View all available tutoring sessions for a given date d. __View all available sessions for a specific tutor She prints it out. She will show it to Terry after her session with the tutor tomorrow. Things to Watch Out for Make sure your questions are clear and not ambiguous. If possible have two or three other people review your questions to make sure they are asking what you meant to ask. Things You Should Know Questionnaires Questionnaires are best for “close ended” questions. Close ended questions are questions that can be answered with a yes or no, by multiple choice or by ranking a set of values. They are good for quick assessments of processes or attitudes toward a system. Questionnaires have some advantages over interviews. They can be quicker and easier to arrange than interviews. They can also be less expensive because they take les s of the stakeholder’s time. With interviews, you can get responses from a wider number of stakeholders. Questionnaires can be easier to summarize and evaluate than interviews. Closed ended questions : M ultiple choice, true and false, ranked value questions — questions with a definite answer. Hands ON Database 80 But they also have some disadvantages. For one thing it is harder to evaluate the accuracy or honesty of the response. In an interview, you ha ve all the nonverbal clues to guide you, and you have the ability to ask an immediate follow up question. With the questionnaire you have only what is on paper. Also questionnaires are not good for open ended and complex questions. Generally people don’t w ant to write long blocks of text in response to a question. Interviews and questionnaires are, of course, not exclusive. Both can be useful. If you use a questionnaire there are a couple of things of which to be careful. First, make sure your questions ar e not ambiguous. Words can often be taken to mean two or more entirely different things. You know what you mean, but with a questionnaire you won’t be there to clarify. =t is always a good idea to have two or three people read your questions and make sur e that they are indeed asking what you meant them to ask. Secondly, make sure you get a representative response. That is, make sure that your questionnaire is given to enough people in enough different situations in order to get the fairest and most accura te response. Tutors at Work The next day at 10:55 A.M. Sharon shows up at the computer lab. Mary Lewis arrives at the same time.

They greet each other and Mary begins explaining the process. She walks over to ward the clipboard. “The first thing = do is look at the schedule here to see if anyone is signed up. I also look to see if I know the student. =f =’ve worked with them before, it helps me have some idea of what they need.” Things to Think About Consider the following question: What are the top 5 things you do at work each day? Would this work better as a question in an interview or on a questionnaire? Why? Hands ON Database 81 Sharon thinks about that a second. “That’s got to be hard. English is a big subject. How do you know have any idea what a student is going to need?” Mary laughs, “=t’s not really that bad. Tutoring is always tied to a specific class. So, I know what the instructor covers in that class and have a pretty good idea of what most stude nt’s have trouble with. “ They have to wait for a moment because a student is rummaging through the papers. He looks a little frustrated. Mary offers, “Can = help?” :e looks up. “= am looking for a math tutor.” “What class is that?” “Math 110.” “= think H ohn tutors for that class. Let me look.” She scans the sheets. “Yes. :e has two one session this afternoon a nd two tomorrow afternoon. Here: ” She points out the sessions on the paper. :e sign s his name under the first one. “Thanks. They should make it easi er to find what you need. Thanks again.” “Now = can see what we have going today.” She glances at the paper. “Looks like = have a new student today, a Mark somebody —= can’t really read the last name.” Mary goes to the desk and gets one of the Tutor Session Report Forms. “= always fill this out first thing. Some tutors don’t bother to fill them out until their due for payroll. That’s hard. =t is almost impossible to remember everything.” She enters her name, the date and the time. As she finishes she glances at her watch. “Looks like Mark is running late.” Sharon asks, “ Does that mess up the rest of your schedule?” Hands ON Database 82 “No, if = have another session immediately after, = will just cut his short. =f = don’t have one right after, = might go a bit long.” “So you may be working more than you’re getting paid for?” Mary Smiles, “=t balances out.” Mark shows up and apologizes for being late. Mary asks him to spell his last name so she can put it on the form. The she introduces Sharon. “She’s watching me today to get some ideas for a database, if that is alright with you.” “Sure, no problem.” “What can = help you with today?” Mark is having a problem with the bibliography for his research paper. Mary leads him over to a computer reserved for tutors and begins to show him how to cite different types of sources. When the session is finished, she says, “Well, Mark, = hope that helps.” Mark replies, “Thanks, yes, that does help very much.” After he leaves, Mary enters the materials covered in the Tutor Report Form. Sharon asks, “What do you do with the report form when you are done with it?” “That’s a go od question. You can give it to the people at the desk to pass on to Terri, but nobody does that. The desk workers are busy and it’s easy for them to mislay a piece of paper. So generally we just keep them ourselves until they’re due. “ “=t must be pretty easy to lose them that way too.” “=t can be if you aren’t organized —and some of the tutors aren’t. They can have troubles sometimes. ” Hands ON Database 83 “Do you have another session today?” Mary nods. “Yes, in a couple of hours. = have class in between.

Let’s take a look.” Mary goes back to the clipboard and searches through the papers. “Looks like nobody is signed up yet.” “You get paid anyway, rig ht?” “Yes, “ Mary says, “but the problem is, if over half your sessions go unfilled for a month, Terri will reduce the number of sessions you can offer.” “= didn’t know that. =s that a rule that always applies?” “Yeah, it’s a rule, though Terri might let i t slide for an extra month if you think you can get business to pick up.” Mary reaches into her notebook and pulls out a sheet of paper. “:ere, Terry gives this to all the tutors. =t states some of the basic rules. = am surprised she didn’t give it to you. ” Sharon glances at the paper: Things to think about Can you think of some other insights you can gain by observing people actually working with the data? Business managers may actually want some common exceptions to the process to be eliminated, for business reasons. But, how do you think workers would react to a database application that enforces strict procedural rules without any room for exception? Hands ON Database 84 “Thanks you, this is really helpful. I will meet you back here for the next appointment. Just out of curiosity, what do you do if no one shows up?” “Usually, = just work on my own homework.” Things You Should Know Work Shadowing It is important to see ho w the data that your database is going to store is actually used in day to day business process es. You can ask people to describe what they do, and you can review the procedure manuals, but there is no substitute for actually watching people at work. There are several insights you can gain from this: One is to see the actual flow of data, how it is captured, how it is transmitted to the next stage, how it is transformed or changed in the process. It also lets you observe how frequently something is used, a nd its relative importance. Perhaps the most important thing work observations can provide you is information about exceptions and undocumented Your Responsibility as a Tutor  Schedule your availability every two weeks.  You can tutor a maximum of 15 hours in a week.  Show up for every session even if no students are scheduled and stay the length of the session  Fill out a session form for every session  Turn in all session forms on the 10th and 20th of each month Never do a student's homework for them. You are there to help them understand how to do their homework:

If it comes to my attention that you have been doing student's homework, you could lose your tutoring privileges. If you have fewer than half of your sessions filled in a 4 week period you will be asked to reduce the number of sessions you offer. Hands ON Database 85 processes. When people describe their jobs they tend to describe the main activities they are supposed to do, th e ones that match their job description. They tend to forget all the little things they do that are not part of the job description, shortcuts, or exceptions. “Well, = am supposed to give this to Hill and then she gives it to John, but Jill is very busy, s o = usually give it directly to Hohn.” “Oh, we never charge Mr. Clemson a late fee. He has a hard time getting around since his stroke and we know he is always good for the payment, so we just wave the fee.” If your database rules are too strict to allow some of these kinds of exceptions it may prove too rigid to actually use. Documentation It is important to keep a record of your information gathering process. A list of the business documents you looked at, along with your questions and answers about each can prove invaluable later when you are reviewing your database for completeness. Summaries of interviews and questionnaire results are also, important. All these documents should be kept in a project notebook Things We Have Done In this chapter we  looked over documents and reports to gather information about the data the database will need to store  prepared an interview  conducted the interview  prepared a questionnaire  followed a tutor to observe the actual work process Hands ON Database 86 Vocabulary Match the definition s to the vocabulary words: 1. Closed Ended Question 2. Domain 3. Business Intelligence 4. Exception 5. Form 6. Transaction Database 7. Open Ended Question 8. Procedure 9. Data Mining 10. Report 11. XML 12. Management Information System 13. Requirement 14. Data Warehouse 15. Stakeholder 16. Work shadowing a. Anyone who has a stake in the process b. A document for gathering data input c. A document for displaying summarized data d. A question that has no set answer e. A collection of all the various types of business information including databases and docume nts Hands ON Database 87 f. A multiple choice question g. A set of tools for analyzing business trends h. Something the database needs to do to be successful i. An alternate way of doing a process j. Marked up Unicode text that follows a set of a few strict rules k. A database optimized for queries that summarize transaction data l. The official steps and rules for completing some process m. The purpose or subject of a database n. Combing data in a variety of formats for trends and patterns o. Observing workers handling data on the job p. A database optimiz ed for storing and processing real time transactions Things to Look Up 1. Information Gathering is often presented as a part of a Systems Design and Analysis. Look up the Systems Analysis and Design Life Cycle. What are the parts of this life cycle? How do yo u think this relates to database development? 2. Look up “Hoint Application Development” or HAD. Briefly describe the process. Do you think this would work with Database Development? 3. Search for an article on Database Design. Does it have any discussion of inf ormation gathering? If so, what steps does it suggest? 4. What does the term Business Intelligence mean? What tools does the Microsoft Business Intelligence suite that ships with SQL Server contain? 5. Look up Management Information Systems. What are some of the features that are associated with such systems. Hands ON Database 88 Practices 1. Look at any common receipt from a grocery store or a restaurant. List all the potential data elements on the receipt. What abbreviations of terms don’t you understand? Make a list of questions you would ask someone if you were going to make a database to store this data. 2. Here is a report from a help desk database. R#: 44331 Status: In Process User: Michael Lawrence C#: NA Rm: 2176B P#: NA Date Entered 8/19/2010 6:00 PM Assigned to: David Betting Assigned On: 8/20/2010 11:00 AM Description: Please quickly install a computer from order 317026 (faculty ones in 3157) before Michael gets here to start work next week. Standard staff office setup, and we'll add his special needs after he's here. I think his old dead computer is there, but he migh t want something from it. Ticket is a level 1. T Notes: New computer is in place. Old computer is at my office. – M. Betting Figure 14: Help Desk Re port List the stakeholders that should be interviewed. 3. Using the form from practice 2, w hat abbreviations o r terms don’t you understand? Make a list of questions you would ask if you were going to make a database to store this data. 4. Create a questionnaire for the users of the form in practice 2 with 4 or 5 questions. Your goal should be to understand how and when they use the form. 5. Here is a form to create a new account at a web based company: *Email Address *Last Name Firs Name Address City State *Home Phone *Zip Code *Enter a password Hands ON Database 89 *Confirm Password *Enter a password Hint You have an interview with a manager at the company. List at least 3 questions you would ask him about this form. 6. You are going to create a database to track clubs and activities on campus. Make a list of some of the types of documents you would like to look at. 7. Tomorrow you are going to interview several students who belong to various clubs mentioned in practice 6 above, and their faculty advisors. You will h ave one hour to conduct the interview. Think about what questions you might ask and make a plan like the one Sharon made on page 20 for the interview. 8. Create a questionnaire with at least 5 questions to follow up on the interview in question 3. It will be distributed to about 20 classrooms on campus 9. Think about some job that you have held. Can you list two or three exceptions —that is, things you did that were different than the standard procedures, shortcuts or one time variations? (If you can’t think of a job, think of your classroom experience. Have you ever seen an instructor make an exception for a class or a student?) List the exceptions and briefly comment on why a database should or should not allow for each of them. 10. Think of a job you held, or, if yo u haven’t held a job, think of yourself as a student. What would somebody doing a job shadow on your day ob serve? Scenarios Each of the Scenarios have different requirements. Each is documented differently. Hands ON Database 90 As a follow up on your initial interview with the project coordinators, Wild Wood Apartments has agreed to show you some samples of various forms and reports. The first example is of a spreadsheet to keep track of leases at one apartment complex: Apt # LeaseNumber Lessee Name StartDate EndDate Rent Am ount Deposit Current 201 #201050109 Charles Summers 5/1/2009 5/1/2010 $ 1,500.00 $ 3,500.00 1 110 #110060109 Marilyn Newton 6/1/2009 12/1/2009 $ 1,200.00 $ 2,900.00 1 306 #306060109 Janice Lewis 6/1/2009 6/1/2010 $ 1,250.00 $ 3,000.00 1 102 #102060109 Larry Thomas 6/1/2009 6/1/2010 $ 1,250.00 $ 3,000.00 1 209 #209060109 Mark Patterson 6/1/2009 12/1/2009 $ 1,450.00 $ 3,400.00 1 The second example is of a spreadsheet used to track rent payments. Date Name Apartment Lease Number Amount paid Late 7/1/2009 Martin Scheller 203 #203011208 $ 1,200.00 7/1/2009 Roberta Louise 311 #311060108 $ 1,400.00 7/1/2009 Sue Tam 111 $ 1,400.00 7/1/2009 Laura Henderson 207 #207020209 $ 1,350.00 7/1/2009 Thomas Jones 110 #110010109 $ 1,200.00 7/2/2009 Shannon Hall 205 #205010109 $ 1,350.00 7/2/2009 Bob Newton 104 #104030209 $ 1,250.00 7/9/2009 Dennis Smith 209 $ 1,400.00 X The third is an example of tracking Maintenance requests and responses Ap t Date Problem type Resolution Res Date BExpense Texpense 303 7/5/2009 Left burner out on range electrical Electriction rewired 7/10/2009 $ 150.00 $ - 201 7/5/2009 Water damage from overflowing bathtub floor Replaced flooring new tile 7/21/2009 $ 200.00 $ 350.00 101 7/6/2009 Dishwasher backing up plumbing filter clogged cleared it 7/6/2009 $ 35.00 $ - 207 7/15/2009 Hole in plaster walls Patched hole 7/17/2008 $ - $ 250.00 Hands ON Database 91 113 7/15/2009 Refrigerator failed utilities new refrigerator 7/20/2 009 $ 690.00 $ - Finally, here is an example of the report that each apartment manager must turn in to the main office quarterly. Wild Wood Apartments Quarterly Report Building# #12 Address 1321 EastLake, Seattle, WA. 98123 Quarter Spring Year 2009 Total Apartments Currently Occupied Percent No. changing tenants 45 40 89% 13 Revenues Total Rent Revenue 175,500.00 Expenses Utilities 2,450.00 Maintenance 11,298.00 Repairs 9,790.00 Insurance 5,340.00 New Tenant cleaning 10,400.00 Wages 19,200.00 Total Expenses 58,478.00 Unrecovered rents 3,200.00 Hands ON Database 92 Total Profit/Loss 113,822.00 Job Shadow Report: I followed Apartment Manager for the Eastlake Apartments, Joe Kindel , for four hours on March 1 st, 2010. =t was the day the rents were due. Hoe’s apartment is also his office. The first thing he did after he opened up and let me in was pick up a locked box that was chained to the floor just outside his apartment door. “The Tenan ts can drop in their rents here” he told me. Joe took the box inside unlocked it and pulled out the checks while his computer started up. When it was ready he began entering the renter’s names, apartment numbers and payment amounts into a spreadsheet. Whil e he was working a tenant came in and handed him a check. He thanked them and added it to the pile. When he had finished he checked his list against a list of tenants. He told me that three had not paid their rent yet. He called each of the three. The fi rst did not answer so he left a message. “= am not too worried about him,” Hoe told me. “:e isn’t always on time, but he always pays within the 5 day grace period.” = asked about the grace period. Hoe answered me, “The Company allows a renter to be up to 5 days late without a penalty. =f you pay after that there is a $100.00 penalty tacked on to the rent.” He called the second renter. She was at home and asked if he could wait until the 10 th. Joe said OK and then explained to me, “She’s an older woman and d ependent on Social Security and retirement checks. I give her a little more leeway. The Company lets me because she has lived her forever and has always been a good tenant. This last one though is just no good.” :e picked up the phone and called. :e got no answer and there was no answering machine. Joe told me that he was about ready to evict this last tenant. He is habitually late and he is actually two months behind in his rent. Joe tells me how difficult it is to actually evict someone. Hands ON Database 93 While he is telli ng me stories about past evictions, the phone rang. A woman in apartment 211 told him that her stove wasn’t working Hoe opened a second spreadsheet and entered some of the details. :e also wrote some notes on a pad of paper. He reassured the woman that he would deal with it quickly and promised to come by in the afternoon. After four hours, I thanked Joe for his time and left him to his lunch. To do 1. Make a list of questions that you would ask about these forms and reports 2. Identify the stakeholders for Wild Wood Apartments. 1. Create a plan for an hour long interview with representatives of these stakeholders . Then meet with the instructor to discuss possible answers to the questions. 3. Create a questionnaire of at least 5 quest ions for the managers of the 20 apartment buildings. 4. Look at the Job shadow report. Do you see any exceptions to the general rule s? Do you see any new business rules uncovered? What additional questions arise from the report? Vince hasn’t kept very comp lex records, but he does have a few things he can show you. The first thing he has is an example of the notes he takes when he purchases an album from a customer. Date 5/14/2009 Seller Name Seller Phone Album Notes cond paid John Raymond 206.555.2352 Rubber Soul Amer. Not British vers. 2nd edition, good Sleeve fair 4 Hands ON Database 94 Marylin Tayler 206.555.0945 Led Zepplin IV Not orig. Sleeve damaged, viny l good good 4.75 Jennifer Louis 206.555.4545 Gift of the flower to the Garder Rare Donovan, box set, box cond poor, but vinyl excellent excellent 12.25 Laura Hall 206.555.2080 Dark Side of the Moon good 4.45 Here is an example of a sale to a customer : Date 5/12/2009 Customer Album price Tax Total John Larson Dylan, Blond on Blond 19.95 1.65 21.60 Tabitha Snyder America $ 5.95 Joni Mitchell, Blue $ 6.25 Joan Baez, Ballads $ 4.20 $ 1.36 $ 17.76 Brad Johnson McCartney, Venus and Mars $ 5.00 $ 0.42 $ 5.42 Maureen Carlson Decemberists, The Crane wife $ 15.50 Muddy Waters $ 7.75 1.92975 $ 25.18 Job Shadow Report I sat with Vince for a full day of work. The morning was quiet and Vince spent the time sorting through a stack of albums that he had purchased earlier in the week. He took each one out of the sleeve and inspected it carefully. “Sometimes = catch things th at = didn’t see when = actually purchased it,” he explained to me. “=t is too late now, of course, to do anything about it, but = want to be fair to the people = sell it to.” :e put a sticker on the cover and put “good” and a price of $6.50. = asked him ab out how he classified and priced things. He told me he had four levels: mint, good, fair, poor. Mint was only for things that were nearly perfect. Good meant there were no scratches and the vinyl was not warped and not too worn. Fair meant the vinyl was a bit more worn and might have a light scratch or two. Poor Hands ON Database 95 meant the vinyl was scratched and probably warped. :e didn’t buy poor vinyl unless it was an extremely rare album. Prices were based on what he thought the album would bring. He based it mostly on experience. After a while a customer came in. He asked if Vince had seen a copy of an old album. He commented that he didn’t think it had ever made the transition to CD. Vince said he had seen it, but he didn’t have a copy currently, but if the customer wan ted he would take his name and number and let him know when he next got a copy. The customer agreed and then, after looking around for about 20 minutes, returned to the counter with 5 albums. Vince wrote down each album title and the price and then added t he prices on a hand calculator. The total came to $35.50. Vince said. “Make it thirty and we’ll call it good.” Vince explained that it was good for business. It made the customer feel good and they were more likely to come back. Several more customers cam e in and their transactions followed a similar patter n. In the afternoon a customer came in with a stack of albums he wanted to sell to Vince. Vince went through the albums, taking each one out of its sleeve and inspecting it. In the end split the albums into two piles. He told the customer he was interested in the first pile of about 12 albums and would offer him $20.00 for them. The customer pulled one album out of the pile Vince had selected and said “= thought this one might be worth a little more. =t is a first print.” Vince looked at it again. “Yes it is, but it is scratched and only in fair condition. Still, =’ll make it $25 dollars if that makes it seem more fair to you.” The customer agreed. Vince told him he wasn’t really interested in the second pile of albums. The cus tomer could either take them back or Vince would put them on his 5 for a dollar pile. The customer chose to leave them. Vince put the albums in a pile by his desk. Several more customers came and went. Vince chatted pleasantly with all of them. Several purchased an album or two. At about f our, Vince turned the open sign in his window to closed and I thanked him for his time and left. Hands ON Database 96 To do 1. Study Vince’s sample notebook entries. Make a list of question you would ask about the data in them. 2. =dentity the stakeholders in Vince’s record store. 2. Prepare an interview with Vince and two of his best customers. One who both sells albums to Vince and buys, and one who mostly just buys. Then meet with the instructor to discuss possible answers to the questions. 3. Create a questionnaire for those who sell albums to Vince about changes they would like to see in the process. 4. Look at the Job Shadow Report for Vince. Do you see any exceptions? What additional business rules do you see? What additional questions does the report raise? The software management team has several spreadsheets to keep track of software. They show you several samples. The first is just a listing of software: Software Version Company License type Windows Vista Business, Service Pack 2 Microsoft MS Site MS Office 2007 Microsoft MS Site Visual Studio Professional 2008 Microsoft MS Instuctional PhotoShop CSS3 Adobe Adobe1 FileZilla 5 FileZilla Open Source German 2.5 LanguageSoft LanguageSoft1 The second is a key to the different licensing agreements and types: Hands ON Database 97 Licence Type Start Date End Date Terms Pricing pricing unit MS Site 7/1/2005 7/1/2010 can install as many copies as needed on campus and on laptops controlled by the school. Includes all service patches, updates and version changes 12500 5 yrs Ms Instructional 7/1/2005 7/1/2010 Use for instructional purposes only. Cannot be used for school development projects 3000 5 yrs Adobe1 7/1/2009 7/1/2010 reduced price per installed copy, Max of 25 active copies 450 per active copy Open Source 7/1/2009 7/1/2020 Free for use as long as registered 0 LanguageSoft1 7/1/2009 7/1/2010 25 copies 5200 for 25 copies Here is an example of the list of who has what software CCS# Location Assigned User 3214 Rm214 Cardwell Software Install date Rmv Date Vista Business 5/3/2008 Ms Office 5/3/2008 PhotoShop 6/4/2008 DreamWeaver 6/4/2008 CCS# Location Assigned User 3114 Rm212 Larson Software Install Date Rmv Date Vista Business 4/15/2008 MsOffice 4/15/2008 Visual Studio Pro 6/12/2009 DreamWeaver 6/14/2009 7/12/2009 And, finally here is sample of a request for new software: Hands ON Database 98 Requests CCS# User req Date Software Reason Response Res Date Status 2123 Johnson 5/20/2009 Camtasia I am conducting several on line classes.

I need to be able to create visual demos to post to the class web site We don't currently have a license for Camtasia but will explore aquiring one 5/24/2009 Pending Job Shadow Report I spent the day on 4/12/2010 following Sheri , a member of the software management team at Grandfield College. The first thing she did after settling into her office was check a spreadsheet that listed pending installations. She showed me the list and told me that she had about six installations to do this morning. She also noted that it was the most boring part of her job. “Nothing like watching the progress bar on the monitor for hours at a time, “ she said. Next she checked her emails. There were three r equests for additional software. She opened a spreadsheet and entered the request information. She told me that she would check later to see if the school had the software or if it was something they would have to purchase. If it was a purchase she would h ave to get permission. She replied to each of the emails to acknowledge their request. After noting the requests, she looked again at the installation to be done. She went to a cupboard and pulled out some disks. She told me that some software can be insta lled from a network drive, but for some she has to bring the media. She also grabbed a notebook. We went to the first office. She spoke for a few moments with the woman who occupied the office. They laughed at a few things. Sheri said that with luck the in stallations should take no more than 30 minutes. The woman left the o ffice to let Sheri work. Sheri logged into the computer as administrator and slipped in a DVD. She started the install. Hands ON Database 99 I asked her about the notebook. Sheri told me that she carried it for two reasons. If there were any problems with the install that she couldn’t solve, she would write down the error messages and take them to the other techs to resolve. She also would note in the book whether the installation was a success or not. She di dn’t put it in the spreadsheet until the installation was complete and successful. The rest of the morning, Sheri moved from office to office installing software. On that day, at least, there were no major installation issues. While we waited she told me a bout other days that didn’t go so easily. She told me about how difficult it could be to troubleshoot a bad install, how obscure and undocumented settings could require hours of research before they were discovered and resolved. The installations were fini shed by lunch. After lunch, Sheri checked with receiving for new software and packages. There were several that had arrived. Sheri carefully unpackaged each arrival and noted it in a spreadsheet. Then she checked the licensing agreements. Some she knew, ot hers she had to check, often looking up the licensing agreement on -line. “Everybody is different,” she told me. “Some let you install the software anywhere on site. Some will only allow a certain number of copies. Some can be placed on a server, some only allow client installations. Some are tied to a particular user. It would make my life easier if things were consistent.” Late in the afternoon Sheri received a call for an instructor requesting disks for a piece of software. She told him sure, if he would come up and get it. He arrived at the door shortly afterward. She gave him the disks and made him sign for them in a notebook. “=’ll have them back to you tomorrow morning, ” he said. Sheri explained, “There are two or three instructors who have administrat ive privileges on their machines. They do their own installations and their own support.” = asked if they track the software on those instructors machines. Sheri told me that they do as best as they can, but the instructors can do pretty much as they want. To get the admin privileges they have to sign a release saying they won’t Hands ON Database 100 violate any licensing agreements and that they accept the fact that the school IT staff will not support their computers. Following this it was time to quit. Sheri shut down her co mputer. I thanked her for allowing me to follow her and said good evening. To Do 3. Study the forms above. Make a list of question you would ask about the data in them. 4. Identity the stakeholders in the Software tracking system. 5. Prepare a plan for a one hour interview with representatives of the stakeholders listed above. Then meet with the instructor to discuss possible answers to the questions. 6. Create a questionnaire for faculty and staff about changes they would like to see in the request process. 7. Review th e job shadowing report. Do you see any exceptions? Do you see any additional business rules? What additional questions does the report raise? The drug study is unique in many ways. For one, the forms and the type of information they capture is more comp lex. For another , privacy rules make it difficult to shadow doctors or researchers . But, still, if you are going to create a database, you must begin to gather the requirements and figure out what data is needs to be tracked. Here is the initial medical form that each patient is asked to fill out: Initial Medical History Form Hands ON Database 101 Name ______________________________ Date__________________ Birth Date ___________________________ Address__________________________________ City_____________________________ State___ _______________ zip_____________ Phone__________________ email__________________________________ List any prescription or non -prescription medicines you are currently taking _____________________________________________________________________________________ _______________________________________________________ List any know allergies to medicines ______________________________________________________________________________ _______ _______________________________ Have you ever been told you had one of the following? _________________________________________ Lung disorder  yes  no _________________________________________ High blood pressure  yes  no Heart trouble  yes  no Group no.:________________________________ Nervous disorder  yes  no Disease or disorder of the digestive tract  yes  no Agreement no.:____________________________ Any form of cancer  yes  no Disease of the kidney  yes  no Diabetes  yes  no Art hritis  yes  no Hepatitis  yes  no Malaria  yes  no Disease or disorder of the blood? (describe)_______________ Any physical defect or deformity? (describe)_______________ Any vision or hearing disorders? (describe)________________ Any life -threateni ng conditions? (describe)_________________ Any contagious disorders? (describe)______________________ How would you describe your depression? a. Severe and continuous b. Severe but intermittent c. Moderate and continuous. d. Moderate but intermittent When did your depression first begin? _______________________ Hands ON Database 102 Which of the following symptoms have you experienced  Sleep difficulties  Loss of appetite  Loss of libido  Inability to leave house  Anxiety in social situations  Thoughts of suicide Briefly Describe your history of depression. Include any earlier attempts at treatment. _____________________________________________________________________________________ _____________________________________________________________________________________ ______________ ___________________________________ Is there a history of Depression in your family?  Yes  No If yes, explain ____________________________________________________________________________ The next form is the form the doctor would fill out for each patient visit. Patient Visit Form Vitals Blood Pressure _________ Weight _______________ Pulse _________________ Does the patient believe his/her depression  Has increased  Decreased  Remained the same Check all symptoms the patient has experienced Hands ON Database 103  Sleep difficulties  Loss of appetite  Loss of libido  Inability to leave house  Anxiety in social situations  Thoughts of suicide List any additional symptoms or side effects _____________________________________________________________________________________ _______ _____________________________________________________________________ Doctors Notes Recommendation:  Continue with study  Drop from study If drop, explain -___________________________________________________________________ Job Shadow Report. The doctors and the directors of the study were relunctant to have me observe them with an actual patient, but one of the doctors, Dr. Lewis, did agree to sit with me and walk me through the process of a patient visit. “The first thing = do in the morning ,” he told me, “is review the day’s appointments.” :e turned on the computer and showed me the way it is done currently. The secretary sends an email with a table of the patients and times of the appointments. He prints out the list and then goes to his ca binet to pull out Hands ON Database 104 the files of the individual patients for review. He reviews their initial medical history and the notes of previous visits, He makes some notes on a notepad for each patient. When the first patient arrives, he greets them and ask s how t hey are doing. He told me he keeps it casual, but he notes any complaints or signs of deepening depression. Then he goes through the parts of the patient visitation form. The nurse has already taken their blood pressure, heart rate and weight. He looks at them and if the blood pressure is high, or if there has been a dramatic change in one of the measures since the last visit, he asks the patient about it. The he asks about their depression. :e doesn’t necessarily use the exact words of the form or follow it in order, but he makes sure he covers all of it. He records a few notes in a notebook while the patient talks, but waits until the patient leaves to write most of the summary. He also waits until the end to make his recommendation to continue or to drop the patient from the study. I asked Dr. Lewis how he makes that determination. He told me that it is a judgment call. Most of the time it’s in the patient’s interest to continue with the study, but if the patient is showing signs of significant side effec ts or if the patient seems in eminent danger of doing harm to themselves, I would recommend the patient be dropped and give alternative or more aggressive treatment. I asked if there were any other reasons for dropping a patient. He said that some patients were dropped from the study because of lack of participation, because they didn’t show for appointments or were inconsistent in taking their medications. He also noted he always worried that that those patients were possibly the most depressed and needed the most help. To do 1. Study the forms above. Make a list of question you would ask about the data in them. 2. Identity the stakeholders in the Drug Study. Hands ON Database 105 3. Prepare for a one hour interview with representatives of the stakeholders listed above. Then meet with t he instructor to discuss possible answers to the interview questions. 4. Create a questionnaire for Doctors about what they thing would help improve the process . 5. Review the Job Shadow Report. Do you see any exceptions? Do you find any additional business rul es in the account? What additional questions does the report raise? Suggestions for Scenarios =t is obvious these scenarios don’t have all the information that you need. Focus your questions on making sure you understand all the bits of data you will need to make your database. You, your team, if you are working with a group, and your instructor can decide on the answers to these questions. As you discuss possible answers, several real world issues may arise that add a great deal of complexity to the data base design. Handling some of these complexities can be a good exercise, but students and instructors should feel free to simplify where needed. Too much complexity can be overwhelming to someone just beginning to develop databases. Hands ON Database 106 Chapter Three : Requirements and Business Rules Having gathered all t he information about the database she can , Sharon must figure out what to do with it. She decides to review her notes to identify all the issues with the current system. First she looks again at the issues with the current database. This helps her refocus on the purpose of the database.

Then she lists the requirements for the database. Next she clarifies the business rules that define how the data is gathered and used. Wi th all this analysis done she begins to identify the specific attributes the database must contain. She reviews the materials including the forms and reports and identifies the key nouns. Then she begins organize them into entities and attributes. Finally she identifies some candidate keys for the entities. Outcomes:  Identify the issues with the current database  Define and list requirements  Define business rules  Search materials for nouns to define entities and attributes  Identify candidate keys for entitie s Getting Started Sharon feels a bit overwhelmed by all the information she has gathered. How is she going to organize it in a way that makes sense and helps her determine the structure and design of the database ? She pulls out a notebook and tries to sketch a plan of action. It is not easy. She decides to give her instructor, Bill, Hands ON Database 107 a call. Luckily, he is in the office and picks up the phone. She explains her dilemma. “= need a plan , some way to make sense of all this material.” Bill thinks for a moment and then says, “:ere is what = usually do: = go through the materials and identify all the issues with the current system. That helps me get the purpose of the database back in focus. Usually the reason for develo ping a database is to fix those issues. Next, I look at all the requirements. What exactly does the database need to do ? Remember to look at it from each user ’s perspective. Then I would go through the materials and identify all the business rules. The rul es can give you clues as to what data must be included and how people will use it. Some of it can be incorporated into the database and some will need to be implemented in the client application that will need to be developed at some point. Does that help? ” Sharon replies, “Enormously, = don’t know how to thank you enough.” Bill laughs, “No problem. Hust make a good database.” Things You Should Know Client/Server Relations A server is a program that makes a “service” or resource available for a “client” that requests it. For instance, a web server makes a web page available to a browser that requests the page to view. Some computers are called “Servers.” Generally this mean s that they are optimized to run server software. They often have more processing power and memory than other computers. They also often run an operating system that has tools to monitor and balance service requests such as Windows Server 2008. Server — a program that offers services to requesting programs Hands ON Database 108 Most databa se management programs also act as servers. They make database resources such as data available to the programs that request it. The requesting program is called a client. The client could be a web page or a windows program or even another database request ing data. It is important to note that what makes a server or a client is the relationship between them: A server provides services requested by a client. The server and client can be on the same physical machine, or they can be on separate machines in di fferent parts of the world. Figure 15:Client \Server Typically users access the database through a client application such as a Windows program or a web page. Most users do not have the skills or the patience to navigate a relational database to find the data they need. They require an application to query the Browser Database Web Server Request web page containing data Request for data and reply Return web page with data Client — a program that requests a service from a server Hands ON Database 109 database, to return and organize the data in ways that they can use. In addition giving users direct access to the da tabase poses numerous security risks and issues. Database design is one major task. Designing the client application or applications for the database is another major task. In chapter eight we will briefly explore some of the tools and processes involved i n creating a simple client application for the Tutor database Review of the Issues Sharon pulls out her notes. She looks at the notes from her first discussion with Terry. Terry had mentioned a couple of issues with the current system. For one, she found i t difficult to determine student demographics for her reports. For another, Terry noted that sometimes it was difficult to determine even how many hours an individual tutor worked in a given time period. The interview had revealed additional issues. Tutors sometimes found it difficult to keep track of report sheets. They didn’t fill them out on time, or they lost the papers before the turn in date, or they turned them in late. Students found it difficult to locate the right tutor for their class on the sign up sheet. Next Sharon reviewed the results of her questionnaire. She had received about 80 responses which was quite good. She had spent some time and summarized the results. She looked at that summary sheet now.  About 80% would be willing to enter demogra phic information including ethnicity .  95% would be willing to enter their current classes  About 70 percent said the time slot was the most important factor; 25% said the tutor and 5 % said neither of the above Hands ON Database 110  Of Students who canceled a tutoring session 30 % said they left a note on the schedule form. 12% said they called the desk and about 5% said they called the tutoring office. 15% said they simply didn’t show. 28 of the Students put an NA and several of them also noted they had never canceled a session  For question 5 on the difficulty of signing up 40% found it confusing, 30% found it not as easy as it should be, 23% said not too difficult and 7% said easy  The consensus on ways to look for sessions was b, c , d, a The que stionnaire reinforced the idea that locating an appropriate session and signing up are important issues Finally, Sharon reviews her notes from her observation of tutoring sessions. The issue of the difficulties students’ experiences signing up recurred again, plus the occasional difficulty of reading a student’s name from the schedule. A couple of additional issues were uncovered. Tutoring times can overlap, run long or be cut short. Sharon wonders if this is just inevitable, or if so me mechanism can be built into the scheduling to help fix it. Th e last issue she notes is that Mary said there were different ways to turn in the tutoring report. Either the tutor could turn in the report at the computer lab desk, or the tutor could hold t he reports and turn them into Terry on the due date. Sharon believes having two ways to turn in the reports contributes to the issue of lost or late reports. Sharon sits down and jots down the issues she has uncovered:  It is difficult to get and track demo graphic information  it is difficult to summarize and confirm individual Things to Think About It is always good to review what you have already done. Database development is iterative process.

You have to constantly go back and refine what you have already done. What do you think would be some of the dangers of just forging ahead in a purely linear way? Hands ON Database 111 tutors tutoring hours  A related issue: getting the tutoring session reports filled out and turned in on time  It can be difficult to find an appropriate tutoring session in the paper s chedule  It can be difficult to read the schedule  Times can overlap, run long, or be cut short Sharon sits back. T hese are the issue her database will be designed to solve. Just to be sure, Sharon reviews the original Statement of work again. In the History section it says 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. Her review has revealed the same issues that were identified in the original statement of work. So now that she is confident she understands t he problem domain, she is ready to move on. Requirements The next thing Bill said to do was to go through her notes again and identify all the requirements, the things the database must do. Once again Sharon returns to the Statement of Work. The Scope section lays out the general requirements clearly: Problem Domain — the business problem the database is meant to address Hands ON Database 112 The tutoring database will manage data for the tutoring program at the college. It will track available tutors and the courses they can tutor. =t will also track each tutor’s tutoring schedule. The databa se 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 availabl e 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. He also noted that she should look a t it from different user’s points of view. What does the database need to do for the tutor? What does it need to do for Terry? What does it need to do for the student? The next step is to work these requirements out in greater detail. Things You Should Kno w Requirements A database requirement is something the database needs to have or do in order to meet the business needs of the organization for which it is being built. For instance, in the tutoring database, if Terry needs to make reports on student gender and student ethnicity, then the database must have attributes that store those values. It is a requirement of the database. Another requirement might be that, for legal and privacy reasons, the personal data of students must be secured so that only those with valid reasons and permission can view or edit it. There are different kinds of database requirements: Requirement — Something the database must do in order to meet the business needs of an organization Hands ON Database 113  Data requirements . That is what attributes the database must contain in order to store all the information an organization needs for its activities . To record a point of sale transaction, for example, the database would need to have attributes for the sale number, the date, the customer, the items and quantities purchased and the prices of those items among others.  Report requirements . Most databases need to generate several different kinds of reports, summary information often gathered from several different entities. The entities must contain the dat a needed to make these reports , as above, but also be related in a way that makes it possible to bring the various pieces of data together. This is a function of relational design which we shall look at in detail in the next chapter.  Access and Security Requirements . Often some, or all, the data in a database is confidential. Database s typically contain core business information that could be of great value of to a competitor, or it may contain things like credit card numbers or social security numbers th at could pose financial and legal risks if revealed to the wrong people. An essential requirement of most databases is to develop a security schema that determines who has access to what data.

Anyone without the proper authentication credentials should be excluded. Chapter Two looked at most of the techniques for gathering requirements: interviews, questionnaires, review of documents, and job shadowing. From those it is necessary to distill the requirements into a usable list. One of the techniques, used in this chapter, is to look at the requirements in terms of each user who will interact with the database. What does the database need to do and contain for that user to successfully complete his or her tasks? The chapter starts with the higher level approac hes , looking at the general requirements first and then getting down to the detail of what attributes and entities the database needs to contain. This approach can help organize what is admittedly a complex task. Hands ON Database 114 Finally, it is essential to review the req uirements you find with those who will be using the database. Having a full understanding of the requirements is crucial if you are to develop a successful database.

Leaving out requirements, even small ones, may render the entire database useless to the o rganization . Thinking about this, Sharon remembers that Professor Collins had told her to make sure that she looked at the requirements in terms of each of the stakeholders for the database. That gives her a place to start. First she will look at the tutors. What does the database have to do for each tutor? She writes out a list:  Allow tutors to enter their monthly schedules  Allow tutors to view the schedule to see which sessions have students signed up  Allow tutors t o cancel a session  Allow tutors to fill out and submit a session report She ponders for a moment trying to decide if there is anything else the database has to do for tutors. It has to allow their hours to be tracked for payment. But that’s requirement see ms to belong more to the Tutor Administrator. Sharon decides to list all the requirements she can for students next. For students the database must:  Allow student to register for tutoring (includes entering demographic data and current courses)  Allow the student to view session schedule  Allow the student to sign up for session  Allow student to cancel a session Sharon isn’t sure of the last one. Are students allowed to cancel their own sessions? She will have to check with Terry when they review the require ments together. Hands ON Database 115 Thinking of Terry, Sharon decides to list the requirements for the Tutoring Administrator next. For Terry the database must:  Allow her to view session schedules  Allow her to add and remove tutors  Allow her to add and remove courses  Allow h er to view student requests  Allow her to view and summarize session reports Sharon thinks about this for a moment. There are other reports Terry needs to view, besides just reports on the sessions. And it might be possible that she needs to be able to gene rate new reports. Sharon adds a few more items to Terry’s list of requirements:  View and generate reports  Summarize tutor hours for payroll There a couple more actors who will be involved in the database. In addition to the Tutoring Administrator, there will need to be a database administrator. She or he will need to maintain the database by backing it up regularly, and will need to maintain it sec urity, especially for student information. IT staff members will need to make the database available over the network and secure access to it. Sharon decides not to diagram these for the moment. Hands ON Database 116 Another set of requirements involves Access and Security. Sh aron knows that she will have to fully develop these in the database itself, but for now she just makes a few notes. First she looks at the Access requirements for Terry:  The database administrator should have select access to the all the data. That means he or she can view all the data in the tables.  The database administrator needs to be able to add, edit and remove records for tutors and courses  The database administrator should be able to create queries as needed  The database administrator should not be able to create or remove tables or other database objects? The last one she will have to check with Terry , but her basic instinct is that no one except the database administrator should be able to add or remove database objects. Things to Watch Out for It is essential that you include security considerations in your planning from the beginning. Too often developers wait until after the database has been designed and developed to think about the security issues of a database. Adding security as an aftertho ught can result in an insecure database, vulnerable to data theft or to accidental violations that can result in a loss of data integrity. Next she thinks about the tutor:  A tutor needs to be able to enter and edit their own schedules but no one else’s.  A tutor needs to be able to enter a session report  A tutor needs to be able to cancel one of their own sessions, but no one else’s.  A tutor should not be able to see student information. Hands ON Database 117 Lastly, she looks at students:  A student must be able to view all available s essions  A student must be able to enter their own demographic information  A student must be able to enter the courses in which they are currently enrolled  A student should be able to cancel one of their own sessions, but no one else’s. Things to Think Abou t Access and Security Security involves determining who has access to database objects and data, and what kind of access they should have. The following table lists some of the types of access a user can have. Each type of access also represents a set of SQL commands. SQL will be covered in more detail in Chapter Six and Security in Chapter Seven. Table 3: Types of Database Permissions Type o f Access Description Create The permission to make new database objects such as tables or views Alter The permission to modify database objects Drop The permission to remove database object Select The permission to see data in a table or view Update The permission to modify data in a table Insert The permission to add data rows to a table Delete The permission to remove data rows from a table Execute The permission to run database executables such as stored procedures Things to Think About For the moment, disregard any malicious intent by a user. What do you think would happen to the data in a database if every user could access and change every other user’s data? User Access — refers to what objects and data in a database a user has permission to use. Hands ON Database 118 Looking at the Use Case Diagrams, Sharon makes a list of all the requirements she has identified. The database must:  Allow tutors to enter their monthly schedules  Allow tutors to view the schedule to see which sessions have students signed up  Allow tutors to cancel a session  Allo w tutors to fill out and submit a session report  Track and summarize tutor hours  Track and summarize student demographic data  Track and summarize Tutoring sessions by subject area  Allow the administrator to view session schedules  Allow the administrator to add and remove tutors  Allow the administrator to add and remove courses  Allow the administrator to view student requests  Allow the administrator to view and summarize session reports  Allow student to register for tutoring (includes demographic data and cu rrent courses)  Allow the student to view session schedule  Allow the student to sign up for session  Allow student to cancel a session  Secure student demographic information Hands ON Database 119 Sharon looked over her requirements. Do they provide the data needed to resolve all the issues she had identified ? It should be easier for Terry to get demographic information.

Most students will enter it and those that don’t will need to at least enter their student id. That will make it easier to look them up on the school’s system. Th e session entity data should make it easy to track tutor’s hours and the session usage. =t should also make it much easier for students to locate appropriate sessions by time, class and tutor. She isn’t sure it will help with the sessions running over time , but the database should solve most of the issues. Business Rules Sharon is starting to feel a little better. Listing the requirements is a big step toward being able to design the database. Next she needs to list the business rules. Business rules, she k nows, are rules about how the data is captured and used and what limits or constraints are placed on the data. Some of these rules can be enforced in the database and some will need to be built into the client application that is built on the database. Onc e again she looks through her notes. Things You Should Know Business Rules Business rules describe the rules that govern the way data is acquired , stored and used by the business. They are important database developer must make sure the database he or she develops can support all the business rules and operations. Some of the business rules can be enforced directly in the database.

For instance, consider a database to track students’ grades and grade point averages. =f the school is on a 4 point system most databases will support putting a “constraint” on the grade column that limits the value to a number between 0.0 and 4.0. A database developer can also limit the length of a column. If Business Rule — a rule that covers the way data is acquired, stored or processed. Hands ON Database 120 all states are to be represented by the two letter abbreviation, then the length of the column can be set to two. There are several other ways to enforce rules within the database as well. But some kinds of rules require extra programming to enforce. If a library has a limit of 20 items out at a time, for instance, there is no way to enforce this rule in the data table. =t is possible though to create a “trigger” which will query the database every time someone checks out an item to see how many items are currently out. It can then flag or block a checkout if it exceeds the number of items. (We will talk more about triggers and procedures in a later chapter.) Still other business rules can only be enforced in the client application through which users will interact with the database. First, she knows the database is going to create a couple of new rules: Every student must register for tutoring and they must enter their current courses. As part of that registration students will be encouraged to enter their demographic information. As Sharon understands it, they can’t be forced to en ter it, but she will check back with Terry. Students must be registered in the courses they want to be tutored for. Those are some of the business rules that apply specifically to Students. Next Sharon tries to identify the business rules that apply to Tutors. Here Sharon finds she still has some major questions. Do tutors enter their own contact information? She assumes that Terry will want to control that information. Are all tutors also students? She remembers from the interview that one of the tuto rs was an MBA student from a different college, so not all tutors are students. S he know s that tutors enter their schedules every two weeks and that they are limited to 15 hours total a week. Tutors are paid for scheduled sessions even if no student shows up. She also knows from the form she was Trigger — database code, usually written in SQL, which exec utes when “triggered” by an event such as an insert or a delete Hands ON Database 121 shown while job shadowing, that if a tutor has too many empty sessions the maximum hours could be reduced. In fact that was spelled out on the Responsibilities form. She shuffles through her papers until she finds it: “ If you have fewer than half of your sessions filled in a 4 week period you will be asked to reduce the number of sessions you offer. ” Now, thinks Sharon, “What do I know about t he tutoring sessions themselves?” She starts to list what she has learned :  Students sign up for tutoring sessions  Tutoring sessions are 30 minutes long  Tutors fill out a Session Report for every session they are scheduled for even if no student is scheduled or the student doesn’t show  Tutors must show up for scheduled sessions even if no one is signed up Some of the most puzzling aspects of the session for Sharon w ere the rules around canceling. From the interview she knew that Tutors could cancel a session if there was no one scheduled. If someone were scheduled they were requi red to try to contact the student scheduled. But what happened if the tutor couldn’t contact the student? She also knew students could cancel a session, but were there any limits to that? And, were there any penalties for frequent cancelations for either t he student or the tutor? She would have to ask Terry that. While thinking of Terry, Sharon tries to identify some of the business rules related to the administrator’s reports:  Tutors hours are calculated from the Session schedule and Session reports  Term reports are based on unduplicated student counts Sharon also releases she doesn’t know the rules for handling requests. Hands ON Database 122 Sharon makes a list of all the rules she has so far:  Students must register for tutoring and enter their current courses  Students are encouraged but not required to enter demographic data including ethnicity  The Administrator will enter tutor information  Not all tutors are students (so they won’t all have a student =D)  Tutors are limited to a maximum of 15 hours a week  Tutors are paid f or scheduled sessions even if no student is scheduled or if the scheduled student fails to show  If over ½ of a tutors sessions have no students signed up over a 4 week period tutors may have their maximum weekly hours reduced  Students sign up for tutoring sessions  Tutoring sessions are 30 minutes long  Tutors fill out a Session Report for every session they are scheduled for even if no student is scheduled or the student doesn’t show  Tutors must show up for scheduled sessions even if no one is signed up  Tuto rs can cancel a session if no student is signed up. If a student is signed up for the session they must try to contact the student  A student can cancel a session  Tutors hours are calculated from the Session schedule and Session reports  Term reports are bas ed on unduplicated student counts Review of Requirements and Business Rules with Terry Hands ON Database 123 Sharon calls Terry and sets up an appointment for the afternoon. When she arrives Terry invites her in and offers her a chair. Sharon pulls out the printed Use cases s he made earlier. She tells Terry , “= made these diagrams to help review the database requirements. Sometimes pictures are much clearer than just words.” Sharon explains the elements of the use case and then goes over the diagrams one at a time. She also sh ows Terry her list of requirements. “Do they cover everything the database needs to do or did = forget something?” Terry studies them for a moment and then says, “That looks complete to me. I wonde r, though , if students should be allowed to cancel sessions .” Sharon responds, “Actually that brings me to a couple of questions = have about the business rules. The whole process of canceling a session is a bit confusing to me. As I understand it, a tutor can cancel a session if no student is scheduled. If a stud ent is scheduled they must try to notify the student. What happens if they can’t notify the student?” Things to Watch Out for It is critical that you review the requirements and business rules with the clients for the database. You need to ensure that you haven’t forgotten any requirements or misunderstood any of the business rules. It is also important that you document each of the requirements and business rules so that everyone involved is clear on what they have agreed to. Use cases and other diagrams a re an important part of documentation, but you should also write them out. Terry muses, “=t depends on the reason for canceling. =f it is possible to make the session the tutor should meet the student. Often, though, it’s not. =n that case we leave a note on the schedule and at the computer desk.” “:ow about the students? :ow do they cancel?” “Typically, they just don’t show up. Sometimes they call me or the tutor.” Hands ON Database 124 “=s there any penalty for missing a session?” “We have a general rule that if a student mis ses more than 3 sessions they are no longer eligible for tutoring, though it is not always enforced.” “Thank you.” Sharon pulls out the list of business rules. “= identified these other business rules. =f you could look at them and tell me what I missed or what = got wrong.” Terry nods, “Those look good to me.” Sharon asks, “The rule about reducing a tutor ’s hours —is that always enforced.” Terry smiles, “No, but we really can’t afford to have our tutors sitting around getting paid for doing nothing. If it i s a pattern, = do have to reduce the hours sometimes. =t is not necessarily the tutor’s fault. =t may just be that the students that term don’t need a tutor, or maybe they don’t know tutoring is available.” Sharon picks up the diagrams and the rules. Thank you, Terry. I think I am ready to start putting things together. The next thing = will show you will be the design for the database.” A Little Bit of Grammar Now that Sharon has got a clear sense of what the issues, requirements and rules are for the database, she feels ready to start brainstorming the major content of the database. The task is daunting though. Where does she start? She remembers a technique her professor Bill Collins taught them. She can start by just listing all the nouns she has en countered. She remembers her first list of topics Tutor Student Hands ON Database 125 Session Request Next she looks at the Tutor’s 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) Figure 16:Tutor Session Report Form There are several fields on the form . She writes them down: Student ID, Session date, session time, tutor name, student name , materials covered. She looks at t he scheduling form: Tutoring for the Week of 4/12 to 4 -16 2009 Monday Tuesday Wednesday Thursday Friday 9:00 AM TT: CL: ST: ---------------- TT: CL: ST: ---------------- TT: CL: ST: TT: CL: ST: ---------------- TT: CL: ST: ---------------- TT: CL: ST: TT: CL: ST: ---------------- TT: CL: ST: ---------------- TT: CL: ST: TT:Aimes CL:(Math 290) ST:Laura Jones ---------------- TT:Carson CL: (ITC 110) ST: --------------- Johnson (ITC 224) Shanna Taylor TT: CL: ST: ---------------- TT: CL: ST: ---------------- TT: CL: ST: Figure 17:Tutoring Schedule Form From it she can gather “tutor,” “Class” and “Student .” There are also time indicators for “Month,”, “week,” “Year”, “ time” and “ weekday.” Then she scans the reports Terry gave her. A lot of this material is summarized so it is a little harder to get information. The payroll report for instance is all summarized and calculated data. Hands ON Database 126 Figure 18: Tutor Payroll Spread sheet The main thing the database needs to provide for is the Tutor name or ID and the hours worked grouped by week month and year. Sharon remembers that as a rule, you should not store calculated fields in a database. You can always recreate the calculati on in a query and it will be more accurate because it is based on live data. The hours per week and the total hours can be calculated from the number of sessions a tutor has on the schedule. The form that Terry bases her reports on also contains a great d eal of summarized information. Report Statistics Fall Term 2010 Students Total Usage 2345 Workforce retraining 247 Unduplicated Usage 1735 Difference 610 Unduplicated Demogra phics Male 937 Female 798 Total 1735 Ethnicity White 868 AfAm 312 Tutor Pay For weeks beginning 4/6/2009 and 4/16/2009 Tutor Week1 Week2 Total Hours Wage Gross Pay Aimes, Tabatha 0.5 2 2.5 10.50 $ 26.25 $ Carson, Karen 8 10 18 10.50 $ 189.00 $ Johnson, Luke 3 4.5 7.5 10.50 $ 78.75 $ Lewis, Mary 1 3.5 4.5 10.50 $ 47.25 $ Sanderson, Nathan 3 3 6 10.50 $ 63.00 $ Stevens, Robert 4 5.5 9.5 10.50 $ 99.75 $ Totals 19.5 28.5 48 504.00$ Hands ON Database 127 Asian 312 PacIs 121 Namer 35 Other 87 Total 1735 By Subject Area ACC 139 BUS 121 ENG 347 HIS 139 ITC 139 MAT 607 SCI 243 Total 1735 Figure 19: Tutoring Statistics Report For a moment she ponders the word “unduplicated.” But, “unduplicated” is an adjective rather than a noun. It is describing something in the database, not a new elem ent in itself. But “Gender,” “Ethnici ty” “Worker Retaining,” and “Subject area” can count as nouns. Time also crops up again in terms of “Quarter” and “Year.” Things to Watch Out for It is easy to get the data attributes contain confused with the attributes themselves. An Attribute is a gen eral descriptor of an Entity. For instance, ‘Last Name” would be an attribute of a Customer entity, but “Hohn Smith” is data that would be stored in that attribute. Attributes are the column heads that describe the data. One way to think of it is that on a computerized form the attributes are in the labels and the data are what are entered into the textboxes . Sharon listens carefully to the notes she recorded during her interview with the tutors and students.

Many of the same nouns show up. Sharon notes the noun “schedule.” It also appears in her notes about the observation of Mary’s tutoring session. Hands ON Database 128 Sharon lo oks at her list of nouns so far. tutor, session, student ID, Student name , session date, session time, tutor name, weekday, materials covered , class name, gender, ethnicity, subject area, schedule , term , year, month worker retraining, Subject area , reques t It is not a very long list but it is a place to start. The next step she knows is to list them into related groups. Again she can use the original big themes she identified as a starting place. She writes down the word tutor. Which elements go with tutor ? Tutor Tutor Name She thinks about Class name, but classes don’t just belong to the tutor. Students take classes and a tutoring session is focused on a class, so class must be a separate group. So now she has Tutor Class Tutor Name Class Name There are also student, session and request groups, of course. She adds the groups: Tutor Class Student Session Request Tutor Name Class Name Student ID Session Date Student Name Session Time Gender Term Ethnicity Year Month Materials covered That leaves “Schedule” and “Subject area.” From The report she knows that the subject area is broader than just the class. =t actually maps pretty well to the class’ department, such as ENG or Math. She places it with the Class group. “She wonders if “schedule” is just a synonym for “Session .” She decides to hold it aside for the moment. Another issue she sees is in Session . Quarter, Year and Month are really Hands ON Database 129 redundant. All that information can be gathered from the Date itself. She makes th e modifications and then scans her list so far: Tutor Class Student Session Request Tutor Name Class Name Student ID Session Date Subject Area Student Name Session Time Gender Materials covered Ethnicity She knows she can modify this list some. The student name and tutor name can be divided into first and last name. The class name can be divided into department, class number and section. She also knows she needs to add term and year. Sharon isn’t sure what additional demographic information Terry needs. She will have to talk to her again and get a precise list. She also isn’t sure how much information the database will need to store about each tutor. Again she will have to ask. She knows that a Session will al so contain at least a tutor, a student, a class and materials covered , so she adds them. Finally, she can sure that a request will contain a student name or ID, a class name and the date of the request. Now her list looks like this: Tutor Class Student Session Request Tutor First Name Class Name Student ID Session Date Class Name Tutor Last Name Department Student Last Name Session Time Request Date Class Number Student First Name Term Student ID Term Gender Year Year Ethnicity Month Section Materials covered Entities and Attributes Sharon looks at her lists of nouns. She knows that the big items, the group headings su ch as tutor, Class. Student, Session and Request will probably be Entities in her database design. The items listed under Hands ON Database 130 them will be attributes , or things that describe or belong to the entity. She also knows the list is not complete. It is only a beginning, but it does give her a good place to start when she gets down to the details of designing the database. Things You Should Know Entities and Attributes As was mentioned in earlier chapters, Entities are things that a database is concerned with, like students, inventory, orders, or courses etc. Attributes are aspects of entities. They are things that describe an entity or belong to it. Entities are a part of the logical design of a database. The logical design is independent of any Database Management System. =t doesn’t take into account any of the implementation issues such as file locations or sizes, or database tuning and efficiency. Logic de sign is concerned only with defining the entities, their attributes, and their relations to other entities. One of the great features of logical design is that it is the same no matter what software or operating system you are using. Most entities will become tables in the final database, but there is not always a one -to-one correspondence. Entities, attributes and relations will be covered in much greater depth in the next few chapters. Candidate Keys Although she knows it is early in the process, Sharo n decides to start identifying some potential keys. She knows that keys are used to uniquely identify each record in a database and to relate records to each other that are stored in different tables. So she begins trying to find some candidate keys. Hands ON Database 131 Things you Should Know Candidate Keys Ideally, every entity should have a key attribute —one attribute that uniquely identifies an instance of that entity. Candidate keys are attributes that co uld possibly be used as identifying attributes. There is much discussion as to what makes a good candidate. It must be unique. That means it can never occur twice in the same entit y. Last names, for instance, don’t make good candidate keys. =t is far too p robable that more than one person will share the same last name. Telephone numbers might make a good candidate key, if all that needs to be unique is the household. Many web sites use email addresses. If there is no good candidate key singly, attributes c an be combined to form a “composite key.” For example, in an Appointment entity in a database tracking dental appointments, the date is not unique because several people could have appointments on the same day. The date and time together are not necessaril y unique, because more than one appointment could be scheduled at the same time. The date, time and patient name or ID should be unique however. In combination they are a candidate to be the entity’s key. Keys that are based on attributes that belong naturally to the entity are sometimes called “Natural keys.” Many advocate the use of Natural keys b ecause they protect data from accidental duplication. No two households, for instance, should have the same telephone number. If you accidentally enter a household a second time, the database management system will throw an error because the phone number o f the second row will conflict with the uniqueness requirement of a primary key. Others argue, however, that all keys should be arbitrary. They argue that it is very difficult to always find a natural key and that often designers have to resort to Composite Key — a key that consists of more than one attribute Natural Key — a key made from one or more of an Entities “natural” attributes Surrogate Key — an artificially created key, often just auto - incremented numbers Hands ON Database 132 awkward composite (multi -attribute) keys that add to a database’s redundancy. =nstead, t hey advocate just assign ing a number to each instance of an entity . These are sometimes called “Surrogate Keys.” Surrogate Keys guarantee that the key will always be unique. Ho wever it provides less protection against accidentally repeating an instance. A new instance (row) could be identical in every aspect except the key attribute. There will be much more discussion of these topics in later chapters She starts with Tutor. Wh at would uniquely identify a tutor? The tutor’s name is one idea, especially if you combined the first and last names. There are not a lot of tutors and the chance of any two tutors having exactly the same name is slight , but it does exist. Although it i s not listed, students, have a student ID which could be used to uniquely identify each student. Most tutors are also students and would have a student id, but not all tutors are students. Perhaps there is some sort of employee I D. She will have to ask Te rry. Each course has a unique name so that could be a potential key for that Entity. For the session, the sessi on date or the session time, perhaps in combination , could be a key, but that wouldn’t really be unique because different tutors could have sessions on the same day at the same time. If the tutor ID was added to the key, that could be unique. After all the analysis, Sharon feels ready to get to work on the logical design of the database. What we have covered In this chapter we have:  Revisited the problem domain by reviewing the issues with the current system  Developed a list of requirements for each user using UML Use Cases  Reviewed the business rules for the tutoring database Hands ON Database 133  Reviewed the materials collected in the previous chapters and extracted nouns that may be come entities and attributes  Organized the nouns into preliminary entities and attributes  looked for attributes that could serve as candidate k eys —that is, attributes that could potentially work as primary keys for the entities Things to look up 1. Look up UML on the web. What are the other types of diagrams? 2. What is the current version of UML? 3. Look up two or more definitions for “Business Rule s.” 4. Look up an article on the web that discusses natural vs. surrogate keys in databases. Which does the author prefer? 5. What are some additional plusses or minuses of each? Vocabulary 1. User Access 2. Server 3. Surrogate Key 4. Actor 5. Requirement 6. Natural Key 7. UML 8. Client Hands ON Database 134 9. Trigger 10. Composite Key 11. Business Rule 12. Use Case 13. Problem Domain a. A program that requests a service b. A key that consists of more than one attribute c. Unified Modeling Language d. A program in SQL that is triggered by a database event e. A use that a particular us er has for a database f. A program that offers a service to requesting programs g. A key based on one or more “natural” attributes of an entity h. A rule about how data is acquired, stored or processed i. The general problem area with which a database is concerned j. An artificial key, often just an incremented number k. Something a database must do to meet a business need l. A person or program that makes some use of the database m. The permissions a user has to use or view database objects and data Practices Use the following sc enario for each of the practice exercises: Hands ON Database 135 You have been asked to build a database for a pet foster and adoption shelter . The agency is a non - profit that takes in stray or abandoned pets and places them with foster care givers until the pet is adopted. Fo ster care givers are volunteers, though they must first be screened. The database needs to track all animals in its care, their species, breed, name and condition. It also needs to track all approved foster care givers and which animals are currently in th eir care. Foster care givers are also supposed to turn in monthly reports on the animals in their care. The database also needs to track the adoptions of the animals. Currently, volunteers come into the shelter and fill out a paper form. After a background check they are added to a file. Some volunteers complain that they are never contacted again. The shelter staff admits, they tend to go with foster care givers they know and some people get forgotten in the file. The shelter has also occasionall y lost track of an animal in foster care when the care giver failed to turn in the monthly reports. Another recurring problem is that when someone comes into the shelter looking to adopt, it is not always easy or even possible to let them know about all th e animals available for adoption. Ideally the shelter would like people to be able to register as a volunteer on -line. They would like to be able to call up a list of all available foster volunteers. They also would be like to be able to pull up all the an imals of the kind a potential adopter is interested in and know exactly where those animals are and who is caring for them. 1. Make a list of some of the major issues with the current system used in the shelter 2. Identity who the major actors are and list them 3. Make a use case diagram for each of the actors showing how they would interact with the database 4. Make a list of business rules for the shelter. Hands ON Database 136 5. Make a list of all the nouns and break them up into entities and attributes 6. Identify some candidate keys Scenar ios The Wild Wood Management team is ready to see some results. You have a meeting with them at the end of the week. It is time to analyze and organize all the information. Look back at the material from the last chapters. 1. Make a list of issues with the current system . 2. Make use case diagrams that show the database requirements for each stakeholder involved in the database . 3. Make a list of business rules. 4. Take at look at each of the forms and make a list of all the nouns in them. Do the same for the interv iew, the questionnaire and the Job shadow report. Then set up some preliminary entities and attributes . 5. Identify some candidate keys . Hands ON Database 137 You are eager to show Vince some progress on the database. You sit down to analyze all the materials you have gathered to see if you can make some sense of them. Make sure you review the material in the previous two chapters. 1. Make a list of issues with the current system. 2. Make use case diagrams that show the database requirements for each stakeholder involved in the databa se. 3. Make a list of business rules. 4. Take at look at each of the forms and make a list of all the nouns in them. Do the same for the interview, the questionnaire and the Job shadow report. Then set up some preliminary entities and attributes. 5. Identify some candidate keys. It is imperative that the college get the software tracking database on -line as soon as possible. You have assured the management team that you will be able to show some progress very soon. It is time to set down and review all the forms and materials. 1. Make a list of issues with the current system. 2. Make use case diagrams that show the database requirements for each stakeholder involved in the database. 3. Make a list of business rules. Hands ON Database 138 4. Take at look at each of the forms and make a list of all the nouns in them. Do the same for the interview, the questionnaire and the Job shadow report. Then set up some preliminary entities and attributes. 5. Identify some candidate keys. The drug study is set to begin in just a few months time. It is important t o make some progress toward the database. It is time to gather all the materials you have collected and try to make some sense of them. 1. Make a list of issues with the current system. 2. Make use case diagrams that show the database requirements for each stake holder involved in the database. 3. Make a list of business rules. 4. Take at look at each of the forms and make a list of all the nouns in them. Do the same for the interview, the questionnaire and the Job shadow report. Then set up some preliminary entities an d attributes. 5. Identify some candidate keys. Hands ON Database 139 Chapter Four: Database Design Entity Relation Diagrams Having organized her materials and determined the business rules, in this chapter, Sharon begins the logical design of the tutoring database. Using Microsoft Visio, she defines the database entities, their attributes, and the relationships among them. Out comes By the end of this chapter you will be able to  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 De signing the Database Sharon is ready to prepare the logical design of the database. The logical design, she knows is separate from any consideration of which DBMS the database is going to be developed on. =t also doesn’t take into accou nt how the files will be stored or accessed. It also ignores any features or limitations of the target DBMS. It is focused purely on the logical structure of the entities and their relationships with each other. Things to think about The logical design of a database is the same no matter what the RDMS is going to be. Physical design is specifically tailored to the features and limits of a particular RDMS What is the advantage of separatin g the logical from the physical design? Hands ON Database 140 For this process she is going to use the dat a modeling template in Microsoft Visio and create a new Entity Relation Diagram or ERD. (For a complete description of opening the Entity diagram in Visio see Appendix Three) Things you should Know Entity Relation Diagrams As the name suggests, an entity relation diagram (ERD) is a diagram of entities, their attributes and the relations among the entities. Most ERDs represent the entities as rectangles divided into three horizontal parts --the entity name, the primary key and then the other attributes -- and two or more vertical parts, the first containing information about keys and indexes , the second containing the attribute name . Figure 20: entity Attributes in bold are re quired attributes. Relationships between entities can be represented in different ways. In Microsoft Visio, the default way is as a line with an arrow on one end. The arrow always points to the one side of a relation, usually the Logical Design — The entity relation design without regard to what RDBMS or system it will be on Physical Design — the design adapted to the RDBMS and system constraints and features ERDs — Entity Relation Diagrams — one common method of depicting Entities and relations in a diagram EntityName PK PrimaryKey Attribute 1 Attribute 2 Hands ON Database 141 side with a primary key. We discuss all these concepts more below. Here is an example using the arrow headed line for the relationship: Figure 21; Arrow symbol for relationship. The relationship can also be represented in “crow ’s feet” notation. You can change the relationship representation in Visio by going to the Database menu, selecting Options/Document clicking the Relationship tag and then selecting the crow ’s feet radio button. Figure 22: Crow' s feet Option Building PK BuildingKey BuildingName Address City State PostalCode Room PK RoomKey RoomNumber RoomDescription FK 1 BuildingKey Hands ON Database 142 Th e crow’s feet notation actually conveys more information about a relationship than the arrow notation. Look at the following example that uses the same two entities but uses the crows feet notation. Figure 23: Crow’s foot notation The end with three lines is the “crow’s foot.” It is the many side of the relation. The straight line and 0 on the building or one side mean that a building can have zero to many rooms. The straight line befor e the crow’s foot indicates that every room must be associated with one building . Although it may seem a bit confusing at first, this book will use the crow’s foot notation. You will often encounter this notation in your database work, and it is good to get familiar with it as soon as possible. We won’t, however, in this book, worry about all the subtle nuances of the notation. Sh aron opens a new data model template in Visio and drags an entity symbol onto the grid. Building PK BuildingKey BuildingName Address City State PostalCode Room PK RoomKey RoomNumber RoomDescription FK 1 BuildingKey Hands ON Database 143 Figure 4: Selecti ng the Database Template She increases the zoom to 100% so she can see the entities better. She clicks on the new entity to select it and sets its name in the definition property: Tutor . Hands ON Database 144 Figure 5: Visio Entity Next she selects the columns property and ke ys in the first attribute name TutorKey . She clicks on the check box to make it the primary key. Right now Sharon isn’t worried about the data types of the columns. They are important, and in the design phase it is a good idea to have some sense of what da ta type a pa rticular attribute will require, but choosing specific data types belongs more to the physical side of database development. For now Sharon is focusing on identifying all the entities, attributes and relationships. We will discuss data types thoroughly in the Chapter Six . Hands ON Database 145 Figure 6: Primary Key attribute Now she types in the other attributes. She decides to use a naming convention that puts the entity name at the front of every attribute name. For instance, a every tutor wil l have a first name and a last name in the database so she names the attributes TutorFirstName , TutorLastName . It can get a bit awkward and make for long names, but it makes it clear which entity an attribute belongs to. Foreign keys, she decides, will be named the same as their corresponding primary keys. Things you should know Naming conventions Naming conventions vary book to book, individual to individual and company to company. The most important thing is to be consistent. Some people like to put “tbl” in front of all table names, but that doesn’t make sense for the logical design. Entities are not yet tables. Some people always name entities w ith a plural noun on the theory that each entity will contain multiple instances or rows. Others always name them with a singular noun on the theory that they are an abstract representation of an element of the database. Naming Conventions — a set of rules or suggestions that promote consistency in the naming of database objects Hands ON Database 146 Attribute names are another issue. Ideally no two attributes in a database should have the same name, with perhaps the exception of Foreign keys which often retain the name of the Primary key to which they relate. The problem is that many Entities have the same or similar attributes. A Cus tomer entity, for instance will have a LastName, Firstname, Address, City, etc.

But an Employee entity also has these attributes. One way to differentiate them is to put the entity name in front of the attribute name or some abbreviation of the table name. Often these are separated with underscores, such as Customer_LastName or Cust_LastName. Key attributes are also a naming issue. Often the key attribute is called an ID, like CustomerID, or EmployeeID. Often the Foreign Key retains the name of the Primary key it relates to. But the foreign k ey doesn’t have to have the same name as its associated primary key. (=t does have to have the same or at least a compatible data type). If a Foreign key is not named the same as the primary key, it should be named something that makes it clear that it is, in fact a foreign key, and it should be clear what primary key it relates back to. This book uses the following naming conventions :  Entities and tables are named as single nouns like Tutor , Student , Session  Attributes are named with the entity name follow ed by the attribute name . There are no underscores between. Each new word is capitalized: TutorLastName , StudentLastName . This can make for long attribute names, but it makes for maximum clarity.  Primary keys end with the work “Key: “ TutorKey , StudentKey . Foreign keys retain the name of the primary key. Things to think about Why do think it is important to be consistent in naming? What would be the disadvantage of not being consistent? What role do you think nami ng conventions could play in documenting a database? Hands ON Database 147 It is important to note that there is nothing standard about these conventions. There are many different conventions that are equally valid. The important thing is to be consistent and clear. Things to Wat ch Out F or A lack of naming conventions can lead to confusion and can make it much harder to maintain or extend a database A note about entities, attributes, tables and columns: Below is a table with some equivalencies Table 4:Term Equivalencies Logical Design Physical design Theoretical Entity Table Relation Attribute Column, field attribute Row, Record tuple Entities and attributes are used to describe the elements in Logical design. Most often, they become the tables and the columns or fields when implementing the database in a particular DBMS. A Row or record is one complete set of data —one customer, for instance, or one inventory item. “Relation” is a theoretical term for a table, and “tuple” is a theoretical term for a row of data. You can encounter these terms in more advanced books on database. Though these categories are not as absolute as the table might make them seem, we will try to be consistent in our use of terms . When Sharon finishes her Tutor entity looks like this: Hands ON Database 148 Figure 7: Tutor Entity version 1 Sharon looks at the entity for a moment. Something about it bothers her. Then it hits her. What if a tutor tutors for more than one course? She could modify the entity to look like this : Figure 8: Tutor entity revision 1 But as she looks at it she knows it is wrong. What if a tutor does only tutor for one class? That means two of the attributes would always be null. What if a tutor tutored for four classes? There would be no place to put the fourth one. And, she realized, if someone wanted to find out what tutors tutored a specific class, he or she would have to always query three separate columns to be sure. Also, if her memory serves her, the entity violates the first normal form. (Normal forms and Normalization will be covered in detail in chapter five.) Sharon revises the Tutor entity one more time. She realizes that course doesn’t belong to Tutor . It is an entity in itself, with its own attributes and its own key. She creates another entity called Course . Tutor PK TutorKey TutorLastName TutorFirstName TutorPhone TutorEmail TutorHireDate TutorCourse Tutor PK TutorKey TutorLastName TutorFirstName TutorPhone TutorEmail TutorHireDate TutorCourse 1 TutorCourse 2 TutorCourse 3 Hands ON Database 149 Figure 9: Tutor and Course Entities Sharon looks at the two entities, trying to determine what kind of relationship exists between the two. It puzzles her for a moment and then she realizes it is a many -to-many relationship. One tutor can tutor for many courses and each course can have many tutors. She smiles as she remembers her instructor in class going over just this situation agai n and again. “Whenever you have a many -to-many relationship, you must always make a linking table.” Things You Should Know Relationships There are three kinds of relationships that can exist between entities:  one -to-one  one -to-many  many -to-many One -to -one In databases, a one -to-one relationship is rare, but can be useful. A one -to-one relationship specifies that for each row in the primary entity there can be one and no more than one related record in the secondary entity. In a one -to-one relationship the p rimary key of the first entity is often the primary key of the second entit y. Tutor PK TutorKey TutorLastName TutorFirstName TutorPhone TutorEmail TutorHireDate TutorStatus Course PK CourseKey CourseName CourseDescription Hands ON Database 150 =n crow’s feet notation, one to one relationships can be represented in two ways: Figure 10: zero or one Figure 11: Ex actly one One use for this kind of relationship is to express a class/subclass relationship. Say a database is keeping a list of different resources. The resources can be in any of several different media and the attributes to describe each media are sign ificantly different. If you put all the attributes in the one Resource entity, each entry will have several nulls for the attributes it doesn’t need. To solve this you can break the Resource entity into several one -to–one relationships. Figure 10 one -to-one relationship Resource PK ResourceKey ResourceTitle ResourceType Video PK ,FK 1 ResourceKey VideoFormat VideoDateReleased VideoLength Book PK ,FK 1 ResourceKey BookPublisher BookYear BookCity BookISBN Magazine PK ,FK 1 ResourceKey MagazineName MagazineIssue MagazineVolume MagazinePage Hands ON Database 151 The entities above don’t include all the relevant attributes, just enough to show the relationship. Notice that each entity has the same primary key. That means each resource will appear once i n the resource table, and exactly once in one of the resource type tables. To get a clearer sense of this relationship, look at the following tables based on this design. Figure 11: Resource Table ResourceKey ResourceTitle ResourceType 235091 Database Programming with ADO Book 244088 PhotoShop Basics Video 200211 Data Binding with LINQ Magazine 202883 Relational Algebra Book Figure 12: Book Table ResourceKey BookPublisher BookYear BookCity BookISBN 235091 Westland Press 2005 San Francisco 123 -77 -6576 -X 202883 PL University Press 1998 Seattle 234 -11 -2345 -0 Figure 13: Magazine Table ResourceKey MagazineName MagazineIssue MagazineVolume MagazinePage 200211 Visual Studio Magazine March 2008 3 76 One -to-one relationships are also sometimes used as part of a security structure. A single entity may be broken into two entities. One will contain publicly viewable content and the second private content. For example, an employee’s information might be broken into two entities. The first one contains non sensitive content such as the employee’s name, department, business phone and position title. The second table contains sensitive material such as the employee’s social security number, home address, home phone and salary information. Ther e i s a one -to-one relationship between the tables. Each one employee has exactly one related record in the private table. Hands ON Database 152 Figure 1 24: One to One It should be noted, this is not necessarily the best way t o deal with security issues. There are many ways to allow the public aspects of the Employee entity to be accessed while protecting the private information. Creating a view or using a stored procedure (See Chapter Seven) to control which columns a user can access is generally a better strategy. One -to -Many Most of the entities in any relational database will have a one -to-many relationship. A one -to-many relationship means that for each record in the primary entity there can be many associated records in the secondary or child entity. There are two crow’s feet symbols for one -to-many relationships: Figure 1 25: Zero or More Figure 126: One or more For an example of a one -to-many relationship consider the relationship between a Department in a business and its employees. Each Department can contain zero or more employees. Each Employee belongs to one department. Employee PK EmployeeKey EmployeeDept EmployeeBusinessPhone EmployeTitle EmployeePrivate PK ,FK 1 EmployeeKey EmployeeSocialSecurity EmployeeAddress EmployeeSalary Hands ON Database 153 Figure 127: One -to-many relationship You only enter the department information once in the department table. You use the primary key to link the table to a child table by repeating it in that table as a Foreign Key. The foreign key can repeat as often as needed in the child table. Figure 128: Department Table DepartmentKey DepartmentName DepartmentPhone DepartmentRoom ACC Accounting (206)555 -1234 SB201 IT Information Technology (206)555 -2468 NB100 Figure 129: Employee Table EmployeeKey EmployeeLastName EmployeeFirstName DepartmentKey FB2001D Collins Richard IT BN2004N Faulkner Leonore IT NC2004M Brown Carol ACC LL2006O Anderson Thomas IT Things to Watch Out For It is important that you do not create a “cross relationship.” There is a temptation to think that because a department contains employees, the department entity should contain a foreign key for employee. Employee PK EmployeeKey EmployeeLastName EmployeeFirstName FK 1 DepartmentKey Department PK DepartmentKey DepartmentName DepartmentPhone DepartmentRoom Hands ON Database 154 Figure 30: Cross Relationship Error Doing this will create an impossible situation. In effect, a Department will only be able to contain a single employee. The second employee will create a conflict with the DepartmentKey which cannot repeat. This is a fairly common e rror among novice designers. =t often isn’t discovered until the attempt to enter data into the tables fails. Many -to -many Many –to-many relationships are common and they are legitimate relationships in logical terms, but no database can implement them. A many -to-many relationship means that each record in the primary entity can have many related records in a second entity and each record in the second entity can have many related records in the primary entity. The symbol for a many -to-many relationship h as a crows foot on both sides of the relationship: Figure 31: Many -to-Many Relationship Visio doesn’t contain a symbol for this relationship. Employee PK EmployeeKey EmployeeLastName EmployeeFirstName FK 1 DepartmentKey Department PK DepartmentKey DepartmentName DepartmentPhone DepartmentRoom FK 1 EmployeeKey Hands ON Database 155 For an example, consider the relationship between Subscribers and a n entity designed to store a list of various magazines. Each customer can subscribe to many magazines and each magazine can be subscribed to by many customers. That creates a many -to-many relationship. Figure 32: Many -to-Many relationship In any RDMS a many -to-many relationship must be resolved into two one -to-many relationships. This is done by creating a linking entity. In this case the Magazine and the Subscriber are linked by a Subscription entity. A subscriber subscribes to one or more magazines. A magazine can be subscribed to by zero to many subscribers . Often, as in this case, creating the linking entity reveals a forgotten or undiscovered enti ty, Subscription is more than a linking entity. It is a legitimate entity with attributes of its own. Subscriber PK SubscriberKey SubscriberLastName SubscriberFirstName SubscriberAddress SubscriberCity SubscriberState SubscriberPostalCode Magazine PK MagazineKey MagazineName MagazinePrice Hands ON Database 156 Figure 33: Linking table Below are tables that show how these entities would be translated into a database. These are, of course much simplified. A real database would contain many more columns of essential information such as the subscription length, the magazine type (is it weekly, quarterly, etc), the magazi ne publisher information, etc. Also for t he subscribers it would be necessary to distinguish between the billing address and the shipping address, since they are not necessarily the same. Figure 34: Magazine Table MagazineKey MagazineName MagazinePrice TM2K1 Time 35.50 NW2K1 Newsweek 36.40 Figure 35: Subscriber Table SubscriberKey Subscriber LastName Subscriber FirstName Subscriber Address Subscriber City Subscriber State Subscriber PostalCode 4231 Johnson Leslie 101 Best Ave. Seattle WA 98007 4333 Anderson Mark 1200 Western Blvd Tacoma WA 98011 5344 Manning Tabitha 100 Westlake Seattle WA 98008 Magazine PK MagazineKey MagazineName MagazinePrice Subscriber PK SubscriberKey SubscriberLastName SubscriberFirstName SubscriberAddress SubscriberCity SubscriberState SubscriberPostalCode Subscription PK SubscriptionKey SubscriptionStartDate FK 1 MagazineKey FK 2 SubscriberKey Hands ON Database 157 Figure 36: Subscription Table SubscriptionKey MagazineKey SubscriberKey SubscriptionStartDate 1004 TM2K1 4333 1/15/2009 1005 NW2K1 4333 1/15/2009 1006 NW2K1 4231 2/1/2009 1007 TM2K1 5344 2/15/2009 Sometimes, however, the linking entity only serves to resolve the many -to-many relationship. Consider the relationship between authors and books. Each book can have several authors and each author can author several books. This relationship can be resolved with a linking table as in the figure below: Figure 37: Linking table 2 It is not uncommon for a linking entity to have a composite key made up of the foreign keys from the two tables whose relationship it resolves . One other note: you may have noticed in the earlier diagrams most relationships are represented by dashed lines. The relationships above and the one -to-one relationships are represented as solid lines. Visio distinguishes between identifying and non -ide ntifying relationships. An identifying relationship is one where the foreign key in the child table is also a part of the primary key of that child table. For instance, AuthorKey is both a foreign key and part of the composite primary key in the Linking Entity — an entity which resolves a many -to -many relationship into two one -to -ma ny relationships Book PK BookKeyISBN BookTitle BookPublisher BookYear Author PK AuthorKey AuthorLastName AuthorFirstName BookAuthor PK ,FK 1 BookKeyISBN PK ,FK 2 AuthorKey Hands ON Database 158 entity Boo kAuthor. A non -identifying relationship is one in which the foreign key is not a part of the primary key of the child table. Below are some examples of how these entities would be translated into tables in a database. Notice how the Head First book has three Authors. Figure 38: Book Table BookKeyISBN BookTitle BookPublisher BookPubl isherYear 0-07 -222513 -0 Java 2 Beginners Guide Oracle Press 2002 0674019999 -1 After the Ice Harvard 2003 0-596 -00867 -8 Head First Object Oriented Analysis and Design O’Reilly 2007 Figure 39: Author Table AuthorKey AuthorLastName AuthorFirstName HSCHLT Schildt Herbert SMITHN Mithen Steven BMCLAU McLaughlin Brett GPOLLIC Pollice Gary DWEST West David Figure 40: Linking table BookAuthor BookKeyISBN AuthorKey 0-07 -222513 -0 HSCHLT 0674019999 -1 SMITHN 0-596 -00867 -8 BMCLAU 0-596 -00867 -8 GPOLLIC 0-596 -00867 -8 DWEST Things to Watch out for Always resolve a many -to-many relationship by creating a linking table. An unresolved many -to-many relationship will cause your database to fail. Composite Key — a key that consists of more than one column Surrogate Key — a random or arbitrary key often generated by just incrementing numbers Hands ON Database 159 Sharon adds a linking entity to resolve the many -to-many relationship . Figure 41: Linking table Now Tutor has a one -to-many relationship with TutorCourse and Course has a one -to-many relationship with TutorCourse , also. That is, one tutor can tutor for many courses and one course can have many tutors. The composite key, which consists of the two foreign keys TutorKey and CourseKey, ensures that the same tutor won’t b e linked with the same course twice. As she looks at her work, Sharon realizes that Student would have the same relationship with Course that Tutor does. One student can enroll in many courses and one course can contain many students. It is another many -to-many relationship. Sharon adds a Student entity to her diagram. She reviews the attributes carefully to make sure she has all the demographic information included. Then she adds the linking tables and makes the relationship. Here is her whole diagram so far: Tutor PK TutorKey TutorLastName TutorFirstName TutorPhone TutorEmail TutorHireDate TutorStatus Course PK CourseKey CourseName CourseDescription TutorCourse PK ,FK 1 CourseKey PK ,FK 2 TutorKey Hands ON Database 160 Figure 42: Student Course 1 In the Student entity, Sharon decides to specify ethnicity as a foreign key. Her idea is that she will create a lookup table for the different ethnicities. One big thing remains to be done. Sharon still needs define the tutoring sessions themselves. Many databases have a central entity where everything is tied together. For this database it is the Session entity. She reviews her notes. A session must have a d ate and time. It needs a course and a tutor. The student is optional because not every session that is available will be taken. She comes up with this entity diagram: Tutor PK TutorKey TutorLastName TutorFirstName TutorPhone TutorEmail TutorHireDate TutorStatus Course PK CourseKey CourseName CourseDescription TutorCourse PK ,FK 1 CourseKey PK ,FK 2 TutorKey Student PK StudentKey StudentlLastName StudentFirstName StudentPhone StudentEmail StudentGender StudentAge StudentCitizen StudentWorkForceRetraining EthnicityKey StudentCourse PK ,FK 1 StudentKey PK ,FK 2 CourseKey Hands ON Database 161 Figure 43: Session Entity She is a bit uncomfortable with a four attribute composite key, but it takes all four to uniquely identify a session, and she thinks Session won’t have any child relations. She is also unsure of the attribute session status. SessionStatus ,as she is thinking about it, would contain a valu e like “completed” if a student showed up and received tutoring, or “canceled” if they did not —or maybe “tutor canceled,” “student canceled.” She would also need a value if the session remained unused. Another issue with Session, she realizes, is that the re is a limit to the number of sessions a student can sign up for, and, for that matter, a limit to how many sessions a tutor can teach in a given time. She remembers that Bill, her professor, called these issues of cardinality. They can be important cons iderations for design and enforcing business rules, but generally they cannot be enforced in a database through the relationships themselves. Things to think about Composite vs Surrogate keys Composite keys provide better protection for data integrity because they prevent accidental entry of identical information, but they can get awkward and can result in more redundant information. Surrogate keys, like an identity or autonumber remove those data redundancies but do nothing to protect data integrity. Most database specialists choose one or the other, but all say don’t mix them. Which do you think is the better option? Session PK SessionDate PK SessionTime PK TutorKey PK CourseKey StudentKey StudentLastName SessionStatus SessionMaterialCovered Hands ON Database 162 Things You Should Know Cardinality Relationships can also have a property called cardinality.

Cardinality refers to the number of allowed related rows between entities. The usual one -to-many relationship assumes that for each one record in the primary key entity, there can be any number of related rows in the Foreign Key entity. This is often expressed as with an infinity sign. But a one -to-many relationship can have limits on the number of related rows. For example, say an Account entity can have no more than five email addresses in a related Email entity. That would mean that the relationship has a Maximum Cardinality of five. Let’s also say that each account must have at least of one email account. That would make the Minimum Car dinality of the relationship One. RDMSs really don’t have ways to enforce cardinality rules directly. Usually these kinds of rules are enforced by means of triggers and other extra database code. Next, she adds a lookup entity for Ethnicity. Cardinality refers to the number of permitted records in a related entity. Maximum cardinality is the highest number permitted, minimum cardinality is the smallest number permitted Triggers are executable scripts of SQL code that are triggered by an event such as an Insert, Update or Delete. They can be used to enforce bus iness rules that cannot be enforced by database design alone Hands ON Database 163 Figure 44: Ethnicity Lookup Entity Things You Should Know Types of Entities As you have seen in the discussion above, entities can play various roles in a database. It can be useful when designing a database to identify what roles different entities play. Domain Entities Domain entities are the entities that relate directly to the business of the database. In a database to t