Relational Databases and Microsoft Access

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

Relational Databases and Microsoft Access 7.3.5 Derived Attributes ...................................................................................................... 135 7.3.6 Key Attributes............................................................................................................. 136 7.3.7 Partial Key .................................................................................................................. 138 7.3.8 Surrogate Key ............................................................................................................. 141 7.3.9 Non-Key Attributes .................................................................................................... 141 7.3.10 Nulls.......................................................................................................................... 143 7.3.11 Domains.................................................................................................................... 143 7.4 Relationships ..................................................................................................................... 144 7.4.1 Degree......................................................................................................................... 144 7.4.2 Participation................................................................................................................ 145 7.4.3 Cardinality .................................................................................................................. 146 7.4.4 Recursive Relationships ............................................................................................. 150 7.4.5 Identifying Relationships............................................................................................ 152 8 Mapping an ERD to a Relational Database ............................................................................. 156 8.1 Mapping Rules .................................................................................................................. 156 8.2 Examples ........................................................................................................................... 159 9 Data Definition Language (DDL) ............................................................................................ 163 9.1 Running DDL in MS Access............................................................................................. 163 9.2 Example............................................................................................................................. 164 9.2.1 DDL Commands......................................................................................................... 165 9.2.2 Creating the Database ................................................................................................. 166 10 Normalization ........................................................................................................................ 170 10.1 Functional Dependencies ................................................................................................ 172

6

Relational Databases and Microsoft Access Example 1. ........................................................................................................................... 172 Example 2. ........................................................................................................................... 173 Example 3. ........................................................................................................................... 175 10.1.2 Keys and Non-Keys.................................................................................................. 178 10.1.3 Anomalies ................................................................................................................. 179 10.1.4 Partial Functional Dependencies .............................................................................. 181 10.1.6 Transitive Functional Dependencies ........................................................................ 183 NORMAL FORMS ................................................................................................................. 185 10.2 First Normal Form (1NF) ................................................................................................ 186 Example 1. ........................................................................................................................... 186 Example 2. ........................................................................................................................... 187 10.3 Boyce-Codd Normal Form (BCNF)................................................................................ 189 Example 1. ........................................................................................................................... 191 Example 2 ............................................................................................................................ 192 Example 3 ............................................................................................................................ 194 Example 4. ........................................................................................................................... 197 10.4 summary .......................................................................................................................... 199 Appendix A: Forms Involving Multiple Tables.......................................................................... 205 Appendix B: SuperTypes and Subtypes ..................................................................................... 209 B.1 Drawing Supertypes and Subtypes on the ERD ............................................................... 209 B.2 Supertypes, Subtypes and Relationships .......................................................................... 211 B.3 Supertypes, Subtypes and Attributes ................................................................................ 211 B.3.1 Discriminator Attributes ............................................................................................ 212

7

Relational Databases and Microsoft Access B.4 Mapping Supertypes and Subtypes to a Relational Database........................................... 213

B.4.1 Relations for all Entity Types ................................................................................... 214 B.4.2 Relations for Bottom-Most Entity Types.................................................................. 218 B.4.3 One Relation Representing The Whole Hierarchy ................................................... 220

8

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

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

Google Online Preview   Download