Relational Databases and Microsoft Access

[Pages:211]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

r.mcfadyen@uwinnipeg.ca Department of Applied Computer Science University of Winnipeg 515 Portage Avenue Winnipeg, Manitoba, Canada R3B 2E9 ron.mcfadyen@ This work is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International Public License. To view a copy of this license visit . 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

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) entityrelationship modeling for supertypes and subtypes.

8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download