Relational Databases and Microsoft Access Relational Databases and Microsoft Access Version 2.0, January 2015 1 Relational Databases and Microsoft Access © 2014-2015, Ron McFadyen [email protected] Department of Applied Computer Science University of Winnipeg 515 Portage Avenue Winnipeg, Manitoba, Canada R3B 2E9 [email protected] This work is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International Public License. To view a copy of this license visit http://creativecommons.org/licenses/by-nc-sa/4.0/. This work can be distributed in unmodified form for non-commercial purposes. Modified versions can be made and distributed for non-commercial purposes provided they are distributed under the same license as the original. Other uses require permission of the author. The website for this book is http://www.acs.uwinnipeg.ca/rmcfadyen/CreativeCommons/ 2 Relational Databases and Microsoft Access TABLE OF CONTENTS Table of Contents ............................................................................................................................ 3 Preface............................................................................................................................................. 8 1 Relational Databases and MS Access .......................................................................................... 9 1.1 Relational Databases ............................................................................................................. 9 1.2 Microsoft Access ................................................................................................................. 14 1.2.1 Modifying Rows ........................................................................................................... 18 1.2.2 Adding New Rows........................................................................................................ 19 1.2.3 Deleting Rows .............................................................................................................. 20 1.2.4 Table Design View ....................................................................................................... 21 2. Creating Tables ......................................................................................................................... 27 2.1 Using Design View to Create Tables .................................................................................. 29 2.1.1 Data Types .................................................................................................................... 31 2.1.2 Properties ...................................................................................................................... 34 2.1.3 Primary Keys ................................................................................................................ 38 3. Creating Forms.......................................................................................................................... 43 3.1 Using the Form Wizard ....................................................................................................... 43 3.2 Modifying the Form ............................................................................................................ 44 3.2.1 Adding a Button............................................................................................................ 45 3.2.2 Adding a Label ............................................................................................................. 46 3.2.3 Adding a Calculated Field ............................................................................................ 47 3.3 Advanced Forms ................................................................................................................ 48 4 Microsoft Access Queries .......................................................................................................... 49 3 Relational Databases and Microsoft Access 4.1 Simple Query....................................................................................................................... 51 4.2 Projection Query ................................................................................................................. 54 4.3 Selection Query ................................................................................................................... 55 4.4 Sorting the Result ................................................................................................................ 57 4.6 And ...................................................................................................................................... 59 4.7 Or ......................................................................................................................................... 60 4.9 Joins ..................................................................................................................................... 62 5 Relationships and the Relationships Tool .................................................................................. 65 5.1 Integrity ............................................................................................................................... 67 5.2 Relationships ....................................................................................................................... 68 5.2.1 One-to-Many ................................................................................................................ 69 5.2.2 One-to-One ................................................................................................................... 70 5.2.3 Many-to-Many .............................................................................................................. 70 6 Microsoft Access Queries – Advanced ...................................................................................... 74 6.1 Logical Expressions ............................................................................................................ 74 6.1.1 And ............................................................................................................................... 75 6.1.2 Or .................................................................................................................................. 76 6.1.3 Not ................................................................................................................................ 77 6.2 Query Operators .................................................................................................................. 79 6.2.1 Like ............................................................................................................................... 79 6.2.2 In ................................................................................................................................... 81 6.3 Query Properties .................................................................................................................. 83 6.3.1 Top Values .................................................................................................................... 83 4 Relational Databases and Microsoft Access 6.3.2 Unique Values .............................................................................................................. 85 6.4 Totals Query ........................................................................................................................ 87 6.5 Parameter Query .................................................................................................................. 92 6.6 Crosstab Query .................................................................................................................... 94 6.7 Action Queries..................................................................................................................... 96 6.8 Inner and Outer Joins ........................................................................................................ 100 6.8.1 Inner Join .................................................................................................................... 102 6.8.3 Outer Join ................................................................................................................... 105 6.8.5 Cartesian Product ........................................................................................................ 108 6.8.7 Self-Join ...................................................................................................................... 109 6.8.9 Anti-Join ..................................................................................................................... 111 6.8.11 Non-Equi Join ........................................................................................................... 112 6.9 SQL Select Statement........................................................................................................ 113 6.10 SQL Union and Union ALL ............................................................................................ 116 7 Entity Relationship Modelling ................................................................................................. 117 7.1 Introduction ....................................................................................................................... 117 7.2 Entities ............................................................................................................................... 123 7.2.1 Weak Entities .............................................................................................................. 125 7.3 Attributes ........................................................................................................................... 128 7.3.1 Atomic Attributes ....................................................................................................... 129 7.3.2 Composite Attributes .................................................................................................. 130 7.3.3 Single-Valued Attributes ............................................................................................ 131 7.3.4 Multi-Valued Attributes ............................................................................................. 132 5 Relational Databases and Microsoft Access 7.3.5 Derived Attributes ...................................................................................................... 134 7.3.6 Key Attributes............................................................................................................. 135 7.3.7 Partial Key .................................................................................................................. 137 7.3.8 Surrogate Key ............................................................................................................. 140 7.3.9 Non-Key Attributes .................................................................................................... 140 7.3.10 Nulls.......................................................................................................................... 142 7.3.11 Domains .................................................................................................................... 142 7.4 Relationships ..................................................................................................................... 143 7.4.1 Degree ......................................................................................................................... 143 7.4.2 Participation ................................................................................................................ 144 7.4.3 Cardinality .................................................................................................................. 145 7.4.4 Recursive Relationships ............................................................................................. 149 7.4.5 Identifying Relationships ............................................................................................ 151 8 Mapping an ERD to a Relational Database ............................................................................. 155 8.1 Mapping Rules .................................................................................................................. 155 8.2 Examples ........................................................................................................................... 158 9 Data Definition Language (DDL) ............................................................................................ 162 9.1 Running DDL in MS Access ............................................................................................. 162 9.2 Example ............................................................................................................................. 163 9.2.1 DDL Commands ......................................................................................................... 164 9.2.2 Creating the Database ................................................................................................. 165 10 Normalization ........................................................................................................................ 169 10.1 Functional Dependencies ................................................................................................ 171 6 Relational Databases and Microsoft Access 10.1.2 Keys and Non-Keys .................................................................................................. 176 10.1.3 Anomalies ................................................................................................................. 177 10.1.4 Partial Functional Dependencies .............................................................................. 179 10.1.6 Transitive Functional Dependencies ........................................................................ 181 10.2 First Normal Form (1NF) ................................................................................................ 183 10.3 Second Normal Form (2NF) ........................................................................................... 186 10.4 Third Normal Form (3NF) .............................................................................................. 188 10.5 Boyce-Codd Normal Form (BCNF) ................................................................................ 190 Appendix A: Forms Involving Multiple Tables.......................................................................... 195 Appendix B: SuperTypes and Subtypes ..................................................................................... 199 B.1 Drawing Supertypes and Subtypes on the ERD ............................................................... 199 B.2 Supertypes, Subtypes and Relationships .......................................................................... 201 B.3 Supertypes, Subtypes and Attributes ................................................................................ 201 B.3.1 Discriminator Attributes ............................................................................................ 202 B.4 Mapping Supertypes and Subtypes to a Relational Database........................................... 203 B.4.1 Relations for all Entity Types ................................................................................... 204 B.4.2 Relations for Bottom-Most Entity Types .................................................................. 208 B.4.3 One Relation Representing The Whole Hierarchy ................................................... 210 7 Relational Databases and Microsoft Access PREFACE This text is a free introductory text that introduces MS Access and relational database design. The motivation is to support a second-year course on database systems which, to the student, is either a service course providing an introduction to database concepts, or, as a prerequisite for more advanced study in the field. Various texts have been used with some success but were felt lacking for various reasons such as: (1) being workbook style with extensive tutorial lessons, (2) being too focussed on a technology, (3) having design material that did not fit well with more advanced courses, and (3) being so expensive that some students opted not to purchase. Our second-year course has no prerequisites and is taken by students from various disciplines. However, most students are registered in either a Computer Science major program or the Computer Science minor. Students who enroll in the course obtain: (1) a working knowledge of a personal database system (MS Access), (2) knowledge of SQL (primarily the Select statement), and (3) awareness of concepts and techniques necessary to database design. Following this course, students can take third- and fourth-year courses in the database subject area. The coverage of Entity Relationship Modelling in those courses is based on the Chen notation – as is usual for academic texts. To be consistent with those higher level courses the same approach is used here. It is our opinion that many students find normalization theory a difficult topic. Many presentations on normal forms are more complicated than necessary (e.g. some texts will give more than one definition of some normal forms). Our approach has been largely motivated by writings of Chris Date. We have attempted to give a suitable introduction to normalization theory for the beginning database student and to relate that material to other topics such as entity relationship diagrams. Version 2.0 includes two appendices that cover a) creating forms that display data in a parent/child format where two tables are related via a one-to-many relationship, and b) entity- relationship modeling for supertypes and subtypes. 8 Relational Databases and Microsoft Access 1 RELATIONAL DATABASES AND MS ACCESS A database is an organized collection of data. A database may be on paper, or held in computer files such as spreadsheets or more formally in a software system known as a computerized database management system (for example: DB2, db4o, IMS, MS Access, MS SQL Server, mySQL, Oracle, Sybase, Total, Versant). In this book we focus on Relational databases and one specific relational database system: Microsoft Access. There are many different commercial relational database systems and what you learn here will assist you in using those others. Because MS Access is a workstation/personal system it is a convenient system for beginners. 1.1 RELATIONAL DATABASES Relational Databases were introduced by E. F. Codd in 19691; Codd’s 1970 paper2 is considered one of the great papers in Computer Science. We begin with a very small example: a database with one relation, the list of employees shown in figure 1.1. You should notice this looks just like a two dimensional table of rows and columns. The name of the table is Employees, each column of the table has its own title, and each row has the same structure. Each row has a value for employee number, first name, last name, and gender. As tables of data appear in so many places (newspaper articles, text books, web pages, etc.) it is very likely you have seen and used this representation for data previously. 1 Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks, IBM Research Report, 1969. 2 A Relational Model of Data for Large Shared Data Banks, CACM 13, No. 6, June 1970. 9 Relational Databases and Microsoft Access Employees Employee ID First Name Last Name Gender 123 Joe Smith Male 333 Jim Jones Male 456 April Smith Female 842 Jenny Jones Female 777 Tom Lee Male Figure 1.1 A list of employees. Let us assume the Employees table in figure 1.1 has one row for each employee who works for some hypothetical company. Data kept for each employee comprises their employee identification number, their first and last names, and their gender. Information structured in tables is very concise; at a glance we can obtain useful information. According to the database design methodology in Information Modeling and Relational Databases 3, a database designer must be able to express structured information as verbalizations. A verbalization that fits the information in one row of the Employees table is: Employee with ID … has a first name …, a last name …, and is of … gender In verbalizations like this the ellipses are placeholders: we can use values from a single row to create complete statements that explain the meaning of a row. For example, Employee with ID 123 has a first name Joe, a last name Smith, and is of Male gender Employee with ID 333 has a first name Jim, a last name Jones, and is of Male gender A similar approach to organizing knowledge about data appears in the literature on literacy. In the Journal of Reading several articles by Kirsch and Mosenthal discuss the organization of information and its conceptualization as document sentences. In Building Documents by Combining Simple Lists4, Kirsch and Mosenthal present an example based on information from 3 Information modeling and relational databases, 2nd edition, by Terry Halpin and Tony Morgan; Morgan Kaufmann Publishers; ISBN -13 978-0-12-373568-3. 4 Irwin S. Kirsch and Peter B. Mosenthal. Building documents by combining simple lists. Journal of Reading, Vol. 33, No. 2, pp. 132-134. 10