Relational Databases and Microsoft Access

Relational Databases and Microsoft Access

Relational Databases and

Microsoft Access

Version 3.0, September 2016

1

Relational Databases and Microsoft Access

? 2014-2017, 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............................................................................................................................................. 9 1 Relational Databases and MS Access ........................................................................................ 10

1.1 Relational Databases ........................................................................................................... 10 1.2 Microsoft Access................................................................................................................. 15

1.2.1 Modifying Rows ........................................................................................................... 19 1.2.2 Adding New Rows........................................................................................................ 20 1.2.3 Deleting Rows .............................................................................................................. 21 1.2.4 Table Design View ....................................................................................................... 22 2. Creating Tables ......................................................................................................................... 28 2.1 Using Design View to Create Tables .................................................................................. 30 2.1.1 Data Types .................................................................................................................... 32 2.1.2 Properties ...................................................................................................................... 35 2.1.3 Primary Keys ................................................................................................................ 39 3. Creating Forms.......................................................................................................................... 44 3.1 Using the Form Wizard ....................................................................................................... 44 3.2 Modifying the Form ............................................................................................................ 45 3.2.1 Adding a Button............................................................................................................ 46 3.2.2 Adding a Label ............................................................................................................. 47 3.2.3 Adding a Calculated Field ............................................................................................ 48 3.3 Advanced Forms ................................................................................................................ 49 4 Microsoft Access Queries .......................................................................................................... 50

3

Relational Databases and Microsoft Access 4.1 Simple Query....................................................................................................................... 52 4.2 Projection Query ................................................................................................................. 55 4.3 Selection Query ................................................................................................................... 56 4.4 Sorting the Result ................................................................................................................ 58 4.6 And ...................................................................................................................................... 60 4.7 Or......................................................................................................................................... 61 4.9 Joins..................................................................................................................................... 63 5 Relationships and the Relationships Tool.................................................................................. 66 5.1 Integrity ............................................................................................................................... 68 5.2 Relationships ....................................................................................................................... 69

5.2.1 One-to-Many ................................................................................................................ 70 5.2.2 One-to-One ................................................................................................................... 71 5.2.3 Many-to-Many.............................................................................................................. 71 6 Microsoft Access Queries ? Advanced...................................................................................... 75 6.1 Logical Expressions ............................................................................................................ 75 6.1.1 And ............................................................................................................................... 76 6.1.2 Or .................................................................................................................................. 77 6.1.3 Not ................................................................................................................................ 78 6.2 Query Operators .................................................................................................................. 80 6.2.1 Like ............................................................................................................................... 80 6.2.2 In ................................................................................................................................... 82 6.3 Query Properties.................................................................................................................. 84 6.3.1 Top Values.................................................................................................................... 84

4

Relational Databases and Microsoft Access 6.3.2 Unique Values .............................................................................................................. 86 6.4 Totals Query........................................................................................................................ 88 6.5 Parameter Query.................................................................................................................. 93 6.6 Crosstab Query.................................................................................................................... 95 6.7 Action Queries..................................................................................................................... 97 6.8 Inner and Outer Joins ........................................................................................................ 101 6.8.1 Inner Join .................................................................................................................... 103 6.8.3 Outer Join ................................................................................................................... 106 6.8.5 Cartesian Product........................................................................................................ 109 6.8.7 Self-Join...................................................................................................................... 110 6.8.9 Anti-Join ..................................................................................................................... 112 6.8.11 Non-Equi Join........................................................................................................... 113 6.9 SQL Select Statement........................................................................................................ 114 6.10 SQL Union and Union ALL............................................................................................ 117 7 Entity Relationship Modelling................................................................................................. 118 7.1 Introduction ....................................................................................................................... 118 7.2 Entities............................................................................................................................... 124 7.2.1 Weak Entities.............................................................................................................. 126 7.3 Attributes ........................................................................................................................... 129 7.3.1 Atomic Attributes ....................................................................................................... 130 7.3.2 Composite Attributes .................................................................................................. 131 7.3.3 Single-Valued Attributes ............................................................................................ 132 7.3.4 Multi-Valued Attributes ............................................................................................. 133

5

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

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

Google Online Preview   Download