Database design for mere mortals pdf download

The editors will have a look at it as soon as possible. Delete template? Cancel Delete. Cancel Overwrite Save. Don't wait! Try Yumpu. Start using Yumpu now! EMBED for wordpress. Want more? Advanced embedding details, examples, and help! Relational Database Design -- Ch. The Relational Database -- Ch. Design Objectives -- Ch. Terminology -- Pt. The Design Process -- Ch. Conceptual Overview -- Ch.

Starting the Process -- Ch. Analyzing the Current Database -- Ch. The third phase is creating the data structures: tables, fields, establishing keys and defining field specifications. Tables are the first structures you define in the database.

Once subjects are identified, theyre established as tables, then fields are associated with the appropriate tables. Tables should be review to be sure they represent only one subject and that no fields are duplicated. Next, fields are reviewed to be sure there are no multipart or multivalued fields. If so, you modify those fields so each fields stores a single value. Then a Primary key is established, making sure it uniquely identifies each record within the table.

Finally, field specifications are established. Interviews should be conducted with users to help identify any specific field characteristics that may be important to them. In the fourth phase, table relationships are established. Interviews are conducted with users and management to identify relationships, relationship characteristics and establish relationship-level integrity.

Once relationships have been identified, it is necessary to establish the logical connection for each relationship. Depending upon the type of relationship, you would use either a Primary key or a linking or composite table to make the connection between a pair of tables based upon the type of relationship you want to establish.

The fifth phase of the database design process is to define the business rules. How an organization views and uses its data will determine limitations and requirements that must be built into the database.

Again, this information is gained through interviews with users and management. Next, validation tables are defined. For example, if certain fields are found to have a finite range of values due to the way they are used by an organization, validation tables are used to ensure the consistency and validity of the values stored in those fields.

Determining and establishing views is the sixth phase of the database design process. Interviewing users and management will help identify the different ways data is viewed. One group may view data from a different perspective than another group. Another group may only need to view one specific field from a certain table.

The last phase is reviewing the final database structure for data integrity. First, each table is reviewed to ensure that it meets proper design criteria. Then field specifications are reviewed and checked. Then, you test the validity of each relationship. Finally, business rules are reviewed and confirmed. Chapter 5: Starting the Process To begin the design process, you must identify the purpose of the database as well as a list of tasks that can be performed against the data.

Conducting interviews provides valuable information that affects the design of the database structure. Having a list of prepared questions is highly recommended. Its important to ask open-ended questions.

This gives the participant an opportunity to provide complete and objective answers to questions. The following are suggestions for interview guidelines: o o o o Set a limit of six people or less for each interview. Conduct separate interviews for users and managers. If several groups are interviewed, designate a group leader for each group. Prior to the interview, inform the participants of what will be discussed and how the interview will be conducted.

Make sure everyone understands you appreciate their participation and that their responses are valuable to the overall design. Conduct interview in well-lit room, separated from distracting noise, large table and comfortable chairs and have coffee and munchies on hand. If youre not good at taking notes, assign the task to a dependable transcriber or get the groups permission to use a tape recorder.

Give everyone your equal and undivided attention. Make sure everyone understands that youre the official arbitrator. Keep the pace of the interview moving. Always maintain control of the interview. Defining the Mission Statement A good mission statement is succinct and to the point.

It should be very general and should not describe specific tasks. Interviewing management and staff will bring an overall understanding of the organization and general comprehension of why the database is necessary. Defining the Mission Objectives Mission objectives are statements that represent the general tasks performed against the data in the database.

Each statement represents a single task and should not contain unnecessary detail. Mission objectives are used to help define table structures, field specifications, relationship characteristics and Views.

Information used to define the mission objectives is gathered through interviews with users and management. General tasks are determined by asking open-ended questions. The interviews should be very general in nature to get an overall idea of the general tasks the database should support. Chapter 6: Analyzing the Current Database The database currently in use can provide a great resource for developing the new database. Many features can remain useful, whereas others can and should be discarded.

An analysis is conducted reviewing the various ways data is collected and presented, as well as through interviews with users and management. A preliminary field list is defined, as well as a list of tables that should be included in the initial database structure.

Data that is literally collected, stored and maintained on paper is known as a paperbased database. Some common formats include index cards, hand-written reports and various types of preprinted forms. Typically these types of databases contain inconsistent data, erroneous data, duplicate data, redundant data, incomplete entries and data that should have been purged from the database long ago.

The only reason to analyze this type of database is to identify various items that will be incorporated into the new database. A database that has been in use for five years or more is considered to be a legacy database. The term legacy may also mean that the individual who originally created the database is working elsewhere and the database has become his or her legacy to the organization. Many of these legacy databases are improperly structured or inefficiently designed. Many times they are based on hierarchical or network database models and store duplicate fields and redundant data.

To conduct the analysis of the current database, one must first review the ways in which data is collected. Begin by reviewing all paper-based items and gather a single sample of each type. Next find sample screen shots in the database programs that best represent how the various programs are used.

The second step in the analysis process is to review any methods used to present information, such as a report. A report is a way to present data that is meaningful to those viewing it. Gather samples of all reports. The final step is to review any on-screen presentations that use the data in the database. Make screen shots of the slides that are used in the presentations.

Next, conducting interviews with users and management is useful in determining how the organization uses its data. For example, o o o o They provide details about the samples you assembled in reviewing how data is collected and how information is presented. They provide information on the way the organization uses its data. They are instrumental in defining preliminary field and table structures.

They help to define future information requirements. Its better to speak to the users first because they have the clearest picture of the details connected with the day-to-day operations of the organization. Use open-ended question to focus on specific subjects, use closed questions to obtain specific details on a certain subject. You can identify subjects by looking for nouns within the sentences that make up the responses. Subjects are always represented by nouns and identify an object or an event.

You can then use these subjects to come up with further questions during the interview process. The purpose is to gain as much detailed information as possible about the subjects youve identified.

Youll also want to identify nouns that represent characteristics of the subjects. These will ultimately become fields in the database. This technique is known as the characteristic identification technique. Its important to use a separate sheet of paper for listing the characteristics. The first part of the interview process involves conducting user interviews, which will focus on: o o The types of data users are currently using; How users are currently using their data;.

The data-collection samples, report samples, and on-screen presentation samples; and The types of information users need in conjunction with their daily work. The next part of the discussion should focus on any additional information that is not being supplied to them currently.

Once this is identified, youll define new data structures to support this extra information. The last part of the interview process concerns future information that may be required by a growing organization. Once such information is identified, you can be sure that the data structures needed to support that information are defined in the database. The second part of the interview process involves conducting interviews with management, which will focus on: o o o o The types of information managers currently receive; The types of additional information they need to receive; The types of information they foresee themselves needing; and Their perception of the businesss overall information requirements.

To begin, review current information requirements. Identify information that management routinely receives and determine whether they currently receive any reports that are not represented in your group of report samples. If so, obtain a sample of each new report. The next subject concerns managements needs for additional information. If there is any information that is missing from the reports they currently receive, that information must be identified. Next, review future information requirements, and finally, review overall information requirements.

If there is any data that the organization needs to maintain, it will need to be accounted for in the database structure. Compiling a Complete List of Fields Now that an analysis of the current database is complete and the interviews with users and management have been conducted, a preliminary field list can be created. This list represents the fundamental data requirements of the organization and constitutes the core set of fields that will be defined in the database. First, review and refine the list of characteristics you have complied.

Then determine whether there are any characteristics in the data collection samples, report samples, and on-screen presentation samples that need to be added to the primary field list. Be sure that each item on your list represents a characteristic field and not a subject table. And, finally, remove any fields that are calculated and place them on a separate list. A calculated field is one that stores the result of a mathematical calculation as its value.

Be sure to review both lists with users and management. Three procedures are used to develop this list. The first involves using the preliminary field list, the second involves using the list of subjects gathered during the interview process, and the third involves using the mission objectives that were defined at the beginning of the database design process. The process of defining the tables begins with a review of the preliminary field list to determine what subjects are implied by these items.

If you can infer a subject from the listed fields, enter that subject on the new preliminary table list. Next, create a second version of the preliminary table list by merging the list you created during the interviews with users and management with the first version of the preliminary table list.

Remove any duplicate items, resolving items that represent the same subject and combine the remaining items together into one master list. Finally, use the mission objectives to determine whether any subjects were overlooked during the previous procedures.

This is a final opportunity to add tables to the preliminary table list. Defining the Final Table List Its time to transform the preliminary table list into the final table list. To do this, youll need to add two elements: table type and table description. There are four table types: o o o Data: This type of table stores data used to supply information. Linking: This table is used to establish a link between two tables in a many-tomany relationship. Subset: This table contains supplemental fields that are related to a particular data table and further describe the subject of that table in a very specific manner.

Validation: This type of table is used to implement data integrity. Table descriptions are used to provide a clear definition of the subject represented by the table. One final task in developing the final table list is to refine the table names.

Refining the Table Names There are certain guidelines that should be used when creating a table name. These guidelines ensure that a name is clear and unambiguous, that it is descriptive and meaningful, and that each table is named in a consistent manner. Guidelines for creating a table name: o o o o o o o o Create a unique, descriptive name that is meaningful to the entire organization.

Create a table name that accurately, clearly and unambiguously identifies the subject of the table. Use the minimum number of words necessary to convey the subject of the table.

Do not use words that convey physical characteristics. Do not use acronyms and abbreviations. Do not use proper names and other words that will unduly restrict the data that can be entered into the table. Do not use names that implicitly or explicitly identify more than one subject. Use the plural form of the name in contrast, field names are always singular. Indicating the Tables Types Youll indicate each tables type on the final table list i.

Each item on the list will be a data table because it represents a single subject that is important to the organization. Linking and validation tables will be missing because you have not yet defined any relationships or data integrity. These issues will be addressed later in the design process. Subset tables are missing as well because they are defined after fields have been assigned to data tables.

Composing the Table Descriptions Table descriptions are important for understanding why each table exists and why the organization is concerned with collecting the data for each table. It must explicitly define the table and state its importance. If you are unable to explain the importance of a table, youll need to further investigate when and how the table was identified and whether it really is necessary.

Start here. I am proud of such an accomplishment. Mike Hernandez has written a literate explanation of database technology—a topic that is intricate and often obscure. If you design databases yourself, this book will educate you about pitfalls and show you what to do. If you purchase products that use a database, the book explains the technology so that you can understand what the vendor is doing and assess their products better. The second edition is packed with more real-world examples, detailed explanations, and even includes database-design tools on the CD-ROM!

This is a must-read for anyone who is even remotely interested in relational database design, from the individual who is called upon occasionally to create a useful tool at work, to the seasoned professional who wants to brush up on the fundamentals. Simply put, if you want to do it right, read this book!

Orders delivered to U. Learn more. View Larger Image. Also available in other formats.