Access Database Design
Technical Training From Libraries Computing and Technology
http://train.msu.edu
ACCESS DATABASE DESIGN Writer: Todd Ring Author: Todd Ring Developed for: Michigan State University
Copyright 2010 Todd Ring and Michigan State University All rights reserved. No part of this publication may be reproduced, transcribed or used in any form or by any means without prior written permission of the author: Revisions 1 - 6/2010 2 - 12/22/2010 3 - 12/27/2011
2
CONTENTS Access Database Design ......................................................................................................................................... 2 Contents ................................................................................................................................................................ 3 Lesson 1 – Data Concepts....................................................................................................................................... 5 Overview ...................................................................................................................................................................6 Two Types of Software ..............................................................................................................................................6 Spreadsheet Applications.....................................................................................................................................6 Advantages: .....................................................................................................................................................7 Disadvantages: ................................................................................................................................................7 Relational Databases ............................................................................................................................................8 Advantages: .....................................................................................................................................................9 Disadvantages: ................................................................................................................................................9 What application to use? ........................................................................................................................................11 Protecting and Maintaining Data .......................................................................................................................11 Data Protection .............................................................................................................................................12 Data Maintenance .........................................................................................................................................12 Lesson 2 – Database & Terminology .................................................................................................................... 13 What is a database? ...............................................................................................................................................14 What is an entity? ..............................................................................................................................................14 What are attributes? ..........................................................................................................................................14 Putting it together ..............................................................................................................................................14 A pictorial view ...................................................................................................................................................15 A deeper look at terminology..................................................................................................................................15 Discovering Data Types ...........................................................................................................................................17 Activity Time ..................................................................................................................................................18 Lesson 3-Designing A Database............................................................................................................................ 19 Creating the tables ........................................................................................... Error! Bookmark not defined. Three Step Process ..................................................................................................................................................20 Analyze your situation........................................................................................................................................20 Activity Time ..................................................................................................................................................21 Creating the Entity Relationship diagram ..........................................................................................................21 Sample Entity Relationship Diagram .............................................................................................................23 Activity Time ..................................................................................................................................................24 Understanding the Keys ..........................................................................................................................................24 Primary Key ........................................................................................................................................................24 Foreign Key.........................................................................................................................................................24 Understanding Cardinalities & Relationship Types .................................................................................................25 One to One .........................................................................................................................................................25 One to Many ......................................................................................................................................................25
3
Many to Many ....................................................................................................................................................26 Thoughts ............................................................................................................................................................26 Activity Time ..................................................................................................................................................26 Creating the Tables .................................................................................................................................................26 Activity Time ..................................................................................................................................................27 Lesson 4 – final steps ........................................................................................................................................... 29 Normalization .........................................................................................................................................................30 First Normal Form ..............................................................................................................................................30 The Second Normal Form...................................................................................................................................31 The Third Normal Form ......................................................................................................................................32 Thoughts on Norminalization .............................................................................................................................32 Discovering Junction Tables ....................................................................................................................................32 Activity Time ..................................................................................................................................................33 Appendix A - Glossary .......................................................................................................................................... 35 Appendix B - Leszynski Naming Convention ......................................................................................................... 39 Object Name Prefix .................................................................................................................................................39 Data Type ................................................................................................................................................................39 Appendix C- ERD Worksheet ................................................................................................................................ 41 Appendix D – Table Worksheet ............................................................................................................................ 43
4
LESSON 1 – DATA CONCEPTS INTRODUCTION We work with data every day. Data doesn’t tell us much. What is needed is an application that can turn the data into information. In this lesson we will look at different approached to do this. We will examine two specific types of software and then choose what is best for our needs. LESSON TIME
60 minutes
TOPICS TO BE COVERED
Overview Two Types of Software What Application to use
LEARNING OBJECTIVES
Understand the different types of Software Be able to choose the correct Application
ACTIVITIES
None
5
Lesson 1 – Data Concepts
OVERVIEW Data needs for enterprises are forever increasing. Advances in technology allow for these increases. The storing, manipulating and saving of vast volumes of data has been made easier with technology. Data can increase knowledge, knowledge is power! However, these technological advances can increase the job requirements for those in the workplace. Employees can find it frustrating trying to keep up with efficient ways of working with data. What software application to use and why can be daunting to many. This database design class will provide an overview of basic concerns and considerations to explore when you have a workplace project to complete. Note: The word Data is plural. The word Datum is singular. Database systems can be described simply as a way to INPUT data, STORE data and RETRIEVE data. Data, plural, is a collection of individual data elements or characteristics that are important to the enterprise or organization. An example of 3 important individual data elements for an MSU system would be a student’s first name, last name and PID. Once the data is collected, a means of data entry and storage is necessary. The archiving of data is important but the value lies in being able to produce output from the data collection. Data is transformed into information in the output process.
TWO TYPES OF SOFTWARE Working on new data projects in a general office setting usually dictates using a spreadsheet application or a relational database application. The difference between these two may not be apparent to the beginner but they are very different in their approach. Our class examples will use the spreadsheet software of Microsoft Excel and the relational database software of Microsoft Access. Other examples of both types are: Spreadsheet Programs
Relational Database Programs
Excel
Access
Lotus 123
Paradox
Quattro Pro
Oracle
Open Office Spreadsheets
Sequel Server
SPREADSHEET APPLICATIONS Spreadsheet applications are commonly used for calculations. This was their original purpose. They organize data into columns and rows. Data in a spreadsheet can be displayed in a variety of layouts, generally based on the preference of the person 6
Lesson 1 – Data Concepts constructing the spreadsheet. Each data element in a spreadsheet is stored by the program by recognizing a space or tab between the elements. Therefore, spreadsheet files are commonly referred to as tab delimited files. Spreadsheets allow for “What-If” type of analysis. Variables can be changed and number crunching is immediate. Spreadsheets provide information for decision-making. The use of spreadsheets with text data was cumbersome in the early days. However, today, spreadsheet programs include many features for text. Text entries can be sorted and filtered to display the data in a preferred arrangement. They have developed to include the use of multiple sheets in one file. Recently, due to ODBC (open database connectivity), many spreadsheet programs allow for their data to be imported and used with sister programs such as word processing. Excellent uses for spreadsheets include collecting detailed budget data to summarize and for address lists to use with the mail merge feature. Files can be protected and given to departments or units to supply budget data for line items and this data can be easily collected and summarized. Addresses in a spreadsheet can be used as the source for a mail merge into a word processing document. One letter document can be used for multiple recipients. Data is retrieved based on the choices of the user. Filtering in a spreadsheet does allow for presenting a subset of the data. Otherwise, manipulating the data in a spreadsheet is cumbersome when a sub-set of the data is needed. ADVANTAGES: Easy to work with Merge addresses into a document Collect financial data from others to produce a summary sheet Create a chart of data. DISADVANTAGES: Trying to extract criteria based data Redundant data and therefore a loss of data integrity Each file is limited to its specific purpose
7
Lesson 1 – Data Concepts Microsoft Spreadsheet example:
Notice the data in this example has redundancy. Student names and addresses are repeated. This can lead to inconsistent data. A spreadsheet that contained only the student name and address would not have redundancy. If only data was to be used for student addresses, then a spreadsheet would be appropriate. A spreadsheet of this nature could be used for mailings. But to include data on courses, a relational database structure would be better.
RELATIONAL DATABASES Relational databases are the most common type of databases. They are based on the idea of connecting a series of flat tables by using a common linking field. The connections, or relationships, allow for the sharing of data between the stand-alone tables. In order to accomplish this feat, specific design principles should be followed when constructing a relational database. These design principles provide for no data redundancy and the ability to store data for an entire enterprise in one database. Reduced data redundancy improves data integrity. Relational Databases store data in flat tables with columns and rows. The columns are referred to as fields. The rows are records. Each table has a designated field, or occasionally fields, called the primary key. The primary key provides for a unique value for each record. Common examples of a primary key field are student number and 8
Lesson 1 – Data Concepts customer number. This unique value insures that a specific record can be located. Primary keys are the vehicle that allows tables to be connected with relationships. Objects in a database, including fields, have properties. These properties define the object. For example one field property is field size. This does not change from record to record, therefore relational databases are known as fixed-width files. Although a relational database table looks similar to a spreadsheet, the concepts behind both and the operation of both are entirely different. Other objects normally found in relational databases are forms, queries and reports. Forms aid in data entry and are therefore, an input object. They allow for more seamless entry of data as compared to entering data directly in the tables. A query is a powerful object in a database. They enable a user to retrieve the exact data needed to produce information. An example of a query could be to provide a listing of all classes a specific student has completed. Another example would be to determine the GPA of all students. Reports are the output objects in a database. Reports are based on tables or queries. Forms, queries and reports use the data that is stored in the tables. A relational database can have many objects but the foundation is the tables. Relational databases can store vast volumes of data. They are flexible in that they can be added to in a relatively easy fashion. For example, if you are storing data on students and courses and want to add faculty data, it can easily done by creating a faculty table and connecting it to courses. ADVANTAGES: Data integrity due to decreased redundancy Increase validity of data with field properties Flexible - can add additional tables or objects later Ease of input and viewing using forms Robust output of data via queries Nice layout of information with reports DISADVANTAGES: Time intensive to set up Users need training to understand how a relational database works Potential for loss of all enterprise data as the data is in one file First the tables would be constructed. For this example, there would be three tables. The tables are constructed in design view and then connected with the common linking 9
Lesson 1 – Data Concepts fields in the Relationships view. Also a database need to be backed up often to prevent the loss of any data. Database Tables and Relationships
Student table
Course Table
10
Lesson 1 – Data Concepts
Student Course Table – linking fields circled
WHAT APPLICATION TO USE? The requirements of the data should indicate which software should be used. However, other considerations are the time and software knowledge of the data users. Review the uses and advantages of the software to determine which would be best for your projects. Remember a mix of the software available can be practical since most spreadsheet data can be converted into a relational database table and relational database tables and queries can be imported into a spreadsheet program. There is compatibility for the software applications developed by the same manufacturer, such as Microsoft, Lotus or Corel.
PROTECTING AND MAINTAINING DATA Protecting and maintaining data is extremely important. Both spreadsheet and relational database data need protecting and maintaining.
11
Lesson 1 – Data Concepts DATA PROTECTION Data protection involves securing data from mishandling and being lost. Both spreadsheet and relational database applications have the ability to password protect data files and data views. Other protections available include preventing changes to the structure of your spreadsheet or database objects. Spreadsheet programs provide for protecting some data values in a sheet while others do not. This allows for data entry to be restricted to appropriate places (cells). Also, spreadsheets can hide the formulas used to calculate data values. Backing-up your data files is extremely important. Whenever you have made changes to your spreadsheets and especially your relational database files, you should save a back-up copy of the file to your network or a removable disk. When saving back up copies use some method to insure your stored back up file contains the most recent changes. This may be as simple as just replacing a previous back up copy or you may want to include a date in the back up copy file name. DATA MAINTENANCE Data maintenance insures that your data is accurate. New data may need to be added. Data rows or records may need to be checked for completeness. For example, does every row or record contain a value for the State column or field. Data values may need to have a consistency check. For instance, is each entry for a State a 2 digit postal value or is there a mix of postal values with complete state words. Insure the data entered is valid. An example would be if data entered for State is MU, which is not a valid entry. Remember the old computer adage, “Garbage In, Garbage Out.” If you want to have reliable information, you need to have accurate data. By using a database such as Microsoft Access, this data accuracy is somewhat built in. By using forms for data entry, you can create lists to select from for the various input fields. These master lists eliminate the need to type the entry every time there by avoiding mistakes and limit the data entry user from typing just anything they want into the entry field.
12
Lesson 2 – Relational Databases
LESSON 2 – DATABASE TERMINOLOGY INTRODUCTION When learning a new technology there are always new words or terminology that associate with that technology. In this lesson we will examine the terminology and structure of a database. We will also look at classifying pieces of data into data types. LESSON TIME
60 Minutes
TOPICS TO BE COVERED
What is a Database A Deeper Look at Terminology Discovering Data Types
LEARNING OBJECTIVES
Understand the terminology of a database Understand the data types in a database
ACTIVITIES
None
Created for LCTTP June 2010
13
Lesson 2 – Relational Databases
WHAT IS A DATABASE? A database can be described as a collection of information with persistence. Persistence is defined as an entity that with go on forever. We all know that nothing goes on forever so we have to look at persistence as a means to say that this entity has good value and with last for quite some time.
WHAT IS AN ENTITY? Within a database we want to store information about different objects. Each of these objects can be called an entity. In a given Student database we may have an entity called Student and an entity called Faculty. The Student entity would contain everything we wanted to know about the student, and the same for the Faculty entity. Since there are students all over the world, we would call this an entity class. The entity class is all the possible students that exist. For our database we do not care about all students in the world, the ones that we do care about are referred to as an entity set.
WHAT ARE ATTRIBUTES? Attributes would then be the different pieces of information that we want to know about the Student. A value goes along with the attribute. For example, with our student, we want the attribute of an email address. The value of that attribute would be the actual email address. One again there are many, many attributes for the student. The attributes that we care about are the attribute set.
PUTTING IT TOGETHER So then, a database is made up of entity sets or Tables, the tables are made up of attributes sets. There are many tables in a database that are all related together in some way and this makes a Relational Database. Tables are just one of the types of Objects in a database. Other types of objects are Queries, Forms, Reports, Macros, and Modules. Tables are the base of all the objects in a database. The tables hold the data and the other objects use the data.
Created for LCTTP June 2010
14
Lesson 2 – Relational Databases
A PICTORIAL VIEW
Tables *Store data
Forms
Queries
* One record/view * Multi-media view * Created from tables or queries
* Created from tables * Can sort/filter data
Reports * Created from tables or queries * Designed to be printed
TABLES ARE THE SOURC E OF ALL DATA. A FORM ALLOWS YOU TO SEE AND INPUT/UPDATE DATA IN THE TABLE A QUER IES ALLOWS YOU TO RETR IEVE AND UPDATE DATA IN THE T ABLE A REPORT ALLOWS YOU TO PRINT A SET OF DATA FROM A TABLE OR QUERY
A DEEPER LOOK AT TERMINOLOGY
REPORTS DO NOT ALLOW FOR UPDATING A We start with a database as: QUERYdefined OR TABLE A database is one or more tables (entities) of data related to a particular purpose, and the tools for using the data. For example, your company might have a sales database and a personnel database that hold all the data relating to either sales or personnel.
This database then has some tables: A table is a collection of related data (or entity sets) stored in rows and columns.
The tables then have records: A record is a row in a table. It contains all the attributes (or attribute class) for one person, thing, or event. For example, all of the information for one employee makes up a record.
Created for LCTTP June 2010
15
Lesson 2 – Relational Databases The records are made up of Fields: A field is a column in a table. Fields contain the information. For example, a table about a person could contain six fields: Last Name, First Name, Address, City, State, the zip code. The fields hold the data. Each field has a data type. The data type tells the database what kind of data will be stored in this field. We will look at these next.
Created for LCTTP June 2010
16
Lesson 2 – Relational Databases
DISCOVERING DATA TYPES We have previously laid out our table structure and have normalized it. Now we need to look closely at each field and determine the type of data that the field will contain. The table below lists the data types that are available in Access and the size limits. Setting
Type of Data
Size
Text
Text or combinations of text and numbers, such as addresses. Also numbers that do not require calculations, such as phone numbers, part numbers or postal codes.
Up to 255 characters. Microsoft Access only stores the characters entered in a field; it does not store space characters for unused positions in a Text field. To control the maximum number of characters that can be entered, set the FieldSize property.
Memo
Lengthy text and numbers, such as notes or descriptions.
Up to 65,000 characters.
Number
Numeric data to be used for mathematical calculations, except calculations involving money (use Currency type). Set the Field Size property to define the specific Number type.
1, 2, 4, or 8 bytes. 16 bytes for Replication ID.
Date/Time
Dates and times.
8 bytes.
Currency
Currency values. Use the Currency data type 8 bytes. to prevent rounding off during calculations. Accurate to 15 digits to the left of the decimal point and 4 digits to the right.
AutoNumber
Unique sequential (incrementing by 1) or random numbers automatically inserted when a record is added.
4 bytes, 16 bytes for Replication ID.
Yes/No
Fields that will contain only one of two values, such as Yes/No, True/False, On/Off.
1 bit.
OLE Object
Objects (Word documents, Excel spreadsheets, pictures, sound files) created in other programs using the OLE protocol, which can be linked to or embedded in an Access table. You must use a bound object frame in a form or report to display the OLE object.
Up to 1 gigabyte (limited by disk space.)
Hyperlink
Field that will store hyperlinks. A hyperlink can be a UNC path or a URL.
Up to 64,000 characters.
Lookup Wizard
Creates a field that allows you to choose a The same size as the primary key field. value from another table or from a list of This is also the Lookup field, typically 4 values using a combo box. Choosing this bytes. option in the data type list starts a wizard to define this for you.
Created for LCTTP June 2010
17
Lesson 2 – Relational Databases To add to this table further, there are several sub types for numerical data types. These sub types are listed in the table below. Setting
Description
Decimal
Storage Size
Byte
Stores numbers from 0 to 255 (no fractions).
None
1 byte
Integer
Stores numbers from –32,768 to 32,767 (no fractions)
None
2 bytes
Long Integer (Default) Stores numbers from –2,147,483,648 to None 2,147,483,647 (no fractions)
4 bytes.
Single
Stores numbers from –3.4028E38 to –1.4012E-45 7 for negative values and from .4012E-45 to 3.4028E38 for positive values.
4 bytes
Double
Stores numbers from –4.941E-324 for negative values and from 1.798E308 to 4.941E-324 for positive values.
15
8 bytes
Replication ID
Globally unique identifier (GUID)
N/A
16 bytes
ACTIVITY TIME Now that we have an understanding of the data types when we create our tables on paper we will add the data types and sizes where necessary. A note about the size of a text data type, when you assign a size to a text field, that amount of space is set aside for future data to be filled in. So when you think that a text field can contain up to 255 characters you might think that it is not a problem to leave the field size at this default. This is not a good idea and here is why. If we have a first name field that is 255 characters long but on the average only use 20 characters and say use 30 characters max. We are wasting storage space for 255-30=225 characters. Since there are 2 bytes of storage taken up for each character we would be wasting 225 * 2 = 550 bytes of storage. You may think that that is not much, now times that by three for the middle name and last name, 550 * 3 = 1.66 kb. Still think that this is not much, try this times 10000 records in the database and you have 1.65*10000=16.5Mb. This is only for three fields in one table. Add this concept to the several tables that you will have in your database and no you have some serious wasted space.
Created for LCTTP June 2010
18
LESSON 3-DESIGNING A DATABASE INTRODUCTION When learning a new technology there are always new words or terminology that associate with that technology. In this lesson we will examine the terminology and structure of a database. We will also look at classifying pieces of data into data types. LESSON TIME
180 Minutes
TOPICS TO BE COVERED
The Three Step Process Understanding the Keys Understand the Cardinalities Creating the Tables
LEARNING OBJECTIVES
To evaluate your needs Understand table relations and key Learn to create / layout tables on paper
ACTIVITIES
Create a Entity Relationship Diagram Create Tables on paper
19
Lesson 3 – Relational Databases
THREE STEP PROCESS There are three main steps to designing a database 1. Analyze your situation 2. Design on paper using the Entity relationship diagram (ERD) or a table layout model 3. Implement your design into a database program We will look at the first two steps in detail in this class. The third step will be done in the Microsoft Access Class.
ANALYZE YOUR SITUATION. This is by far the most important and time consuming task in designing a database. The more time and effort put into this task the better your database will be in the end. Here we need to reflect and think about what data is needed. Gather all the information that is available to you. If there are any current paper forms, this is a good place to start. At this point, nothing is insignificant. Just jot down all bits of information that you think will be needed. Think broadly here. We will narrow the scope at a later step. Here are some questions to help get started: 1. 2. 3. 4.
What is the purpose of this database? What type of work is done on paper that you want in the database? What kind of data do you work with? What do you want to gather information on?
Now we want to take a closer look at the big picture and then narrow this down to the particulars that we want to know and track.
Created for LCTTP June 2010
20
Lesson 3 – Relational Databases Here are some detailed questions to help narrow the scope: 1. What type of entities do we need to collect data on? a. Students b. Faculty c. Grades d. Courses 2. What specific attributes of the entities are needed? a. Names b. Addresses c. Costs d. Dates e. Grades 3. What type of information are you currently collecting on paper? a. Admission information b. Class schedule 4. What type of information do you want to have on a report? a. Class Schedule b. Grades Remember to spend plenty of time on this part of the process. If you think too small you will have a lot of rework to do later. Consult colleagues that have created databases and gain some insightful knowledge on what has been done in the past. This can help you to think broad enough to cover all information that is needed. ACTIVITY TIME Using the provided handout, create an outline of the information that you want to capture in this database.
CREATING THE ENTITY RELATIONSHIP DIAGRAM We start with these questions to create the entity relation design. We use symbols for the entities and the attributes for those entities. We start the process by creating the entity symbol (see diagram below)
Use one symbol for each entity
Now we add attributes to the entities using the attribute symbol.
Use one symbol for each attribute Name each of the attributes (do not be cryptic with the name)
Next create a relationship symbol between each of the entities with relationship symbol.
Created for LCTTP June 2010
21
Lesson 3 – Relational Databases
Use one symbol between each table Mark each side with a 1 or N to indicate the cardinality
This is a list of the symbols: Term
Definition
Examples
Symbol
Entity
An independent Unit. A real world object such as people, places, things, events, assets.
Grade
Rectangle
Class Student
Relationships The connection between two entities
Students participate in classes.
Diamond
Each class has a grade Attributes
The basic characteristics or data elements of and entity
Entity = Student
Cardinalities
The number of records in one table that matched the number of records in a related table
Created for LCTTP June 2010
Account Number First Name Last Name Phone Address Email
A Student (1) will take many (N) classes
Circle with a line to the Entity
1 or N
22
Lesson 3 – Relational Databases SAMPLE ENTITY RELATIONSHIP DIAGRAM Below is a sample of an entity relationship diagram
Student 1
Name
Add.
City
Zip
State
N Student_Courses
N Student Course ID
StudentID
CourseID
Courses
CourseID
Name
Code
Created for LCTTP June 2010
TermID
Grade
1
Desc.
23
Lesson 3 – Relational Databases ACTIVITY TIME Using the handout, create the entities for the following:
Students Faculty Classes Grades
UNDERSTANDING THE KEYS PRIMARY KEY With each entity we need to have a way to uniquely identify each record within the entity. To do this we assign a key to each entity. This key is defined as the Primary Key. This key allows a database to locate a specific record within a dataset. This primary key is never repeated with in this table. Every table must have a primary key because:
Keeps data in a table unique This becomes the index to speed up searching for records Will be the joining field for relation to other tables.
When creating a primary key for an element one has to be careful to use something that is not possible to be repeated. An example of a bad primary key would be a social security number as these are reused after someone passes on. The best primary key is to use an auto number data type. We will discuss data types shortly. An auto number type will automatically increment itself and never be reused, ever.
FOREIGN KEY Just as the primary key defines a unique identity in a table, the foreign key is the other side of the relation in the second table. So in the second table the foreign key identifies the unique record in the first table. The relationship from the student table where the studentid is the primary key, the second table for the grades will have the studentid as a foreign key. Because all of the student’s grades are in the grades table we need to identify which grade belongs to which student. This is the job of the foreign key.
Created for LCTTP June 2010
24
Lesson 3 – Relational Databases
UNDERSTANDING CARDINALITIES & RELATIONSHIP TYPES Cardinalities are a way of expressing a relation of the number of records in one table that can have a match in another table. Relationships are the way in which one table knows about its matching record in another table. Relationship Types are how we describe the Cardinality between the tables. There are three types of Relationships:
One to One One to Many Many to Many
Just as we have described the Foreign Key in the grades table, we now see that there can be many grades for one student. This is defined as Cardinality of one to many, or, one student to many grades. Now let’s look at all three examples:
ONE TO ONE In a One to One Relationship, or a Cardinality of 1-1, only one record in the first entity (or table) is related to one record in the second entity. For example: 1
1
Student
Student_note
This type of relationship can be identified by a primary key relating to another primary key.
ONE TO MANY In a One to Many relationship, or a Cardinality of 1-M, one record in the first entity (or table) is related to many records in the second entity. For Example: In the entity of students, you have one student named Steve. In the second entity, Classes, the student Steve has taken many classes. 1 Student
Many Classes
This type of relationship can be identified by a primary key relating to a foreign key.
Created for LCTTP June 2010
25
Lesson 3 – Relational Databases
MANY TO MANY In a Many to Many relationship, or a Cardinality of M-M, there are many records in the first entity (or table) that are related to many records in the second entity. For Example: You have two entities, Faculty and Classes. For a given Faculty, Tom Smith, he teaches many classes, math 101, 102, 103. However for a given class, math 101, there are many faculty that teach it, Sara Johnson, Susan Ellis, etc.. M M Faculty a n This type of relationship can be yidentified by a foreign key relating to a foreign Classes
key. This is not a valid type of relationship. If you end up with this type of relation then you must add another entity called a Junction Entity. This entity will have a One to Many relationship on both sides of the entity. This will be discussed in more detail later.
THOUGHTS Your ER Diagram may (and should) go through many changes before becoming finalized. You may find that you need to add some more entities or that a particular attribute does not belong to a particular entity. When we begin the process called normalization, more things will become clear on Entities and their Attributes. It will also become clear where we need to add a Junction Entity. A Junction Entity removes the problems associated with a many to many relationship and replaces it with two One to Many relationships. ACTIVITY TIME Using your ERD (Entity Relation Diagram), add the appropriate Cardinality designations to each entity using the diamond symbol.
CREATING THE TABLES Now that we have created our ERD, we need to move forward and finish the design of the tables that will be used in the Access Database. Using the handout, we will transform our entities into a more vivid table that will then show us the attributes of the fields that are needed. Follow these guidelines when creating the tables: 
The table name should be descriptive of what information the table contains and not be cryptic.
Created for LCTTP June 2010
26
Lesson 3 – Relational Databases
The name can be up to 64 characters long The name can contain upper case, lower case and numeric characters The name cannot contain spaces, however, underlines are acceptable. Prefix the name of the table with “tbl”. (this follows the Leszynski naming convention in the back of this book)
Follow these guidelines when creating the Fields of the table:
This name should be descriptive of what information the attribute contains and not be cryptic. The name can be up to 64 characters long The name can contain upper case, lower case and numeric characters The name cannot contain spaces, however, underlines are acceptable. Optional: prefix the name of the Field with the correct data type. (this follows the Leszynski naming convention in the back of this book)
ACTIVITY TIME Using the table handout, create the tables for the following entities:
Student Faculty Classes Grades
Created for LCTTP June 2010
27
LESSON 4 – FINAL STEPS INTRODUCTION a types. LESSON TIME
90 Minutes
TOPICS TO BE COVERED
Normalization Junction Tables
LEARNING OBJECTIVES
Understanding the Normalization process Understanding why we need Junction tables
ACTIVITIES
Normalize your tables Create a Junction Table
29
Lesson 3 – Relational Databases
NORMALIZATION Now we need to look at making sure that our tables are normalized. Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. This process makes sure that all Attributes are placed into the correct Entity. It also ensures that we break up any many to many relations and that all Entities have their Attributes broken down to the correct piece of the Attribute. The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF. The fifth normal form is very rarely seen We will look at the first three normal forms.
FIRST NORMAL FORM The First Normal Form consists of three points. They are:
Eliminate repeating groups in individual tables. Create a separate table for each set of related data. Identify each set of related data with a primary key.
Let’s break this down. Here is a sample of an Employee Table ID
Name
Phone
Class
Room
Building
123
Tom Jones
555-1515
jones@xyz
Math 101
room 502
Bessie Hall
456
Sally Smith
555-1212
smith@xyz
Math 101
room 502
Bessie Hall
789
Tim Johnson
555-1616
johnson@xyz
Math 101
room 502
Bessie Hall
147
Todd Miller
555-1717
miller@xyz
ADV 206
room 308
Linten Hall
258
Jane Doe
555-8181
doe@xyz
ADV 206
room 308
Linten Hall
Do we have any repeated groups of data? Yes: dept.name, dept location, dept.phone. Why is this bad? Well if the purchasing department decides to change the Name of the department or the room number or the phone, then every record of every person who works in that department with have to be updated. Created for LCTTP June 2010
30
Lesson 3 – Relational Databases So how do we fix it? First we eliminate those three fields in this table. Then we create a new table that contains the three fields, and lastly we add a primary key field to the new table. It now looks like this: ID
Name
Building
Class ID
123
Tom Jones
555-1515
jones@xyz
1
456
Sally Smith
555-1212
smith@xyz
1
789
Tim Johnson
555-1616
johnson@xyz
1
147
Todd Miller
555-1717
miller@xyz
2
258
Jane Doe
555-8181
doe@xyz
2
And the new department table looks like this: Class ID
Class
Room
Building
1
Math 101
room 502
Bessey Hall
2
ADV 206
room 308
Linten Hall
We have added a field to the new department table names Dept ID. This is the primary key so that each department now has its own ID. We have also added a field to the employee table names Dep ID. This field matched the primary key of the department table. This new field is the Foreign key. These tables are now in First Normal Form
THE SECOND NORMAL FORM There are 3 points to the second normal form and they are:
The table is in first normal form Create separate tables for sets of values that apply to multiple records Relate these tables with a foreign key
For the second point lets say that the student has a state field, the faculty has a state field, and the parents have a state field. Instead of naming the city, state and postal code in all three tables, we create a separate table that lists postal codes, cities and states. We then create the relationship back to the main table with the foreign key.
Created for LCTTP June 2010
31
Lesson 3 – Relational Databases
THE THIRD NORMAL FORM There are 2 points to the third normal form:
Meet all the requirements of the second normal form. Remove columns that are not dependent upon the primary key. Reduce fields to hold only singular data.
For the second point, we need to look closely at all of the fields in our table and make sure that each one is solely dependent on the primary key. This is to say that if a field is not dependant on the primary key then it does not belong in this table. For example, if we take the student table and have a field for a professor, does this field (professor) have anything to do with the student? The answer is no. The student table contains all the attributes of the student and that is all. The will be another table that would list all of the professors that a student has encountered with their courses. The third bullet point is meaning to say that any field that is of multiple parts must be broken down into the smallest piece. For example, a field called Student Name that contains the student’s first, middle, and last name is incorrect. The correct layout is to have three fields, First name, Middle name, and Last name.
THOUGHTS ON NORMINALIZATION This operation of normalization takes time and several versions. It is not the simplest thing to accomplish. You may very well go through many drafts and add several tables to end up with a normalized database.
DISCOVERING JUNCTION TABLES We have mentioned the Many to Many relationship and that this is a mistake to have. Let’s take a closer look at this issue and how we fix it. Consider the following tables: Student
Classes
Studentid
Classid
Firstname
Classcode
Lastname
Classname
Classid
Studentid
Created for LCTTP June 2010
32
Lesson 3 – Relational Databases Here we have a table of students with the class they are taking. On the other side is the table of all classes with a student listed. This is a many to may relationship. One student can take many classes but also one class can be taken by many students. To correct this issue we add another table called a junction table. The junction table with the student id’s with the class id’s in a much better way. We have spoke in the past about a primary key. In a junction table we have a dual primary key. This means that not one but two fields together make up the key of uniqueness for the table. Individually each piece of the key can be repeted, but together the combination cannot ever be repeted. It would look like this: Students
Students_Classes
Classes
Studentid
Studentid
Classid
Firstname
Classid
Classcode
Lastname
Classname
This now gives us a one to many between students and student_classes and a one to many between classes and student_classes. This is a much better structure than we had previously. This is a process that needs to be completed anywhere you find that you have a many to many relationship. ACTIVITY TIME Now that we understand junction tables, let’s look at our tables and see where we need to break up any many-to-many relations.
Created for LCTTP June 2010
33
APPENDIX A - GLOSSARY Access: A Microsoft software application that is based on relational database design principles. Data: Individual elements or items that are grouped together into a collection. Database: Data that is collected and organized into a system that has data input, storage and output. Excel: Microsoft software application developed to aid in “number crunching” and performs “what-if” type of analyses. It is a type of spreadsheet computer program application. Field The individual column in a row that contains the individual piece of data Information: Data that has been retrieved from a database and manipulated in order to produce a meaningful output report. Information is the goal of stored data or a database. Information is used by decision makers in an enterprise. Query: A relational database object that retrieves requested stored data. Many times queries are based on criteria specified by a user. Queries can include calculations on fields.
35
Lesson 2 – Relational Database Records A collection of rows of data Relational Database: A data storage system that uses a series of tables related to each other. The tables have fixed-width columns and each row is a data record. The tables are related via a common linking field. A relational database is useful for storing large amounts of data. A well designed relational database can incorporate into one system multiple aspects of an organization. For example, accounting data, customer data, store locations and employee information can all be a part of the same database. Relational databases delete the need of redundant data. They are flexible, and if properly designed should not have any data inconsistencies. Some examples of relational database programs are Microsoft Access, FoxPro, Oracle and FileMaker Pro. Row One complete record in a table Spreadsheet Programs: These programs were developed to aid in financial analysis. Numbers were the type of data planned to be used with spreadsheets. Spreadsheets do calculations and the calculations can be structured to allow for considering different scenarios. For example, house payment amounts could be viewed by comparing differing mortgage rates and differing loan principle amounts. Spreadsheets have columns and rows and the data can be placed according to the user’s needs. Spreadsheets are delimited files, meaning that each data instance is separated by a space, character or tab space. Some examples of spreadsheet programs are Microsoft Excel, Lotus 123, and Quattro Pro. System Flowchart: A pictorial depiction of the physical flow of data throughout a database system. System flowcharts use a specific set of symbols. These symbols are available in word processing programs and can be drawn by hand with a template. Table A collection of related data about a specific topic. This data is stored in rows and columns.
Created for LCTTP June 2010
36
Lesson 2 – Relational Database What-If analysis: A type of analysis that allows for comparing results based on different scenarios. For example, what would your car payment be based on different interest rates and down payments.
Created for LCTTP June 2010
37
APPENDIX B LESZYNSKI NAMING CONVENTION OBJECT NAME PREFIX Table
tbl
Select Query
qry
Report
rpt
Form
frm
Module
bas
Macro
mcr
DATA TYPE Auto Number
ids
Interger
int
Binary
bin
Long
lng
Byte
byt
Single
sng
Currency
cur
Memo
mem
Date/time
dtm
OLE
old
Double
dbl
Test
chr
Hyperlink
hlk
Boolean – yes/no
bol
Created for LCTTP June 2010
39
APPENDIX C- ERD WORKSHEET Draw your ERD diagram using the appropriate symbols
Created for LCTTP June 2010
41
APPENDIX D – TABLE WORKSHEET Table Name Field Name
Field type
Size
Key Type
Related Table
Field
Field type
Size
Key Type
Related Table
Field
Table Name Field Name
Created for LCTTP June 2010
43
able Name Field Name
Field type
Size
Key Type
Related Table
Field
Field type
Size
Key Type
Related Table
Field
Table Name Field Name
Created for LCTTP June 2010
44