Develop an entity-relationship diagram (ERD) and its corresponding database (Microsoft Access) to be used by a product manufacturer to manage the orders it receives from
Part A: Develop an entity-relationship diagram (ERD) and its corresponding database (Microsoft Access) to be used by a product manufacturer to manage the orders it receives from its customers.
- Each customer is identified uniquely by a customer id; include the first name, last name, and address for each customer.
- The company has several products that it stocks and for which customers place orders.
- Each product has a unique id, unique name, unit price, and a quantity on hand. At any time, a customer may place an order which will involve possibly many products.
- For each product ordered the database must know the quantity ordered and the unit price at that point in time. If the customer does this through a phone call then an employee is involved in the call and will be responsible for the order from the company side.
- Some orders are placed via the Internet. For each order, an order number is generated. For each order, the database must keep track of the order number, the date the order was placed, and the date by which the customer needs to receive the goods.
Part B: Consider the Orders database. This database has several one-to-many relationships.Create appropriate forms to list:
- A customer and the customer’s orders
- Order and its detail lines
- A product and the order detail lines where the product is referenced
- A category and the products belonging to the category.
Relational Databases and Microsoft Access
1
Relational Databases
and
Microsoft Access
Version 3.0, September 2016
Relational Databases and Microsoft Access
2
© 2014-2017, Ron McFadyen
Department of Applied Computer Science
University of Winnipeg
515 Portage Avenue
Winnipeg, Manitoba, Canada
R3B 2E9
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/
Relational Databases and Microsoft Access
3
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
Relational Databases and Microsoft Access
4
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
Relational Databases and Microsoft Access
5
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
Relational Databases and Microsoft Access
6
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
Relational Databases and Microsoft Access
7
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
Relational Databases and Microsoft Access
8
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
Relational Databases and Microsoft Access
9
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.
Relational Databases and Microsoft Access
10
1 RELATIONAL D ATABASES 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 RE LAT IONA L 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.
Relational Databases and Microsoft Access
11
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.
Relational Databases and Microsoft Access
12
The World Almanac and Book of Facts: 1980 (Newspaper Enterprise Association, p. 427). That
data is reproduced in figure 1.2.
Circulation of Leading U.S. Magazines
Magazines Circulation
TV Guide 19,547,763
Reader's Digest 18,094,192
National Geographic 10, 249,748
Better Homes & Gardens 8,007,202
Family Circle 7,611,578
Woman 's Day 7,535,855
McCall's 6,502,880
Figure 1.2 Circulation of leading U.S. magazines.
A major point the authors make is that such information can be re-conceptualized as a series of
simple document sentences formed from a basic document sentence. This document sentence
expresses an understanding of the tabular data in natural language. The document sentence for
figure 1.2 is:
Magazine X has a circulation of Y.
Kirsch and Mosenthal use variables (X and Y) to stand for data that comes from a table. Taking
values from a row, we plug values for X and Y into the document sentence to obtain sentence
instantiations:
TV Guide has a circulation of 19,547,763.
Reader's Digest has a circulation of 18,094,192.
National Geographic has a circulation of 10,249,748.
Better Homes & Gardens has a circulation of 8,007,202.
Family Circle has a circulation of 7,611,578.
Woman's Day has a circulation of 7,535,855.
McCall's has a circulation of 6,502,880.
Document sentences and verbalization sentences are essentially the same. Both sentences use
natural language to express in words the meaning of tabular data. Whether one is designing
databases or reading structured information, it can be useful for understanding to re-formulate
data as statements in natural language.
Let us be a bit formal for a moment. Commercial relational database systems are systems where
data is organized into relations. Figure 1.3 shows the general structure of a relation. We say a
relation comprises a set of tuples where each tuple has the same number of attribute values,
Relational Databases and Microsoft Access
13
where each attribute value is taken from some corresponding domain, and where a domain
represents a set of valid values for an attribute.
Figure 1.3 General structure of a relation
The Employees table in figure 1.1 can be considered a relation of 5 tuples where each tuple has 4
values drawn from each of the employee identifier, first name, last name, and gender domains.
Similarly we can say the lists comprising the Circulation of leading U.S. Magazines in figure 1.2
can be considered a relation with 7 tuples each having 2 attribute values.
Relations are typically implemented in commercial databases as tabular structures comprising
rows and a fixed number of columns. Everybody is familiar with tables as they are commonplace
in textbooks, papers, magazines, etc. This simplicity of representation is one reason why
relational databases have been very successful as repositories for important data.
Relational Databases and Microsoft Access
14
Exercises
To design a database, a database engineer needs to find good representations of how an
organization uses data. Good sources include: input forms, reports, web pages, etc. A challenge
for database designers is to find these sources and interpret them.
1. Consider the following table of product information sold by ABC Foods. Verbalize the
information presented.
Product ID Product Name Unit Price Units In Stock
1 Black Tea $2.00 44
2 Green Tea $3.00 33
3 Vegetarian Lasagne $10.00 20
4 Cajun Seasoning $11.00 29
5 Cranberry Sauce $21.00 0
2. Consider the following report that the Human Resources department of ABC Foods must
produce. Verbalize the information in that report.
Employee ID First Name Last Name Department
1 John Smith Receiving
2 Lee Daniels Sales
3 April Turner Sales
4 Thomas Trump Marketing
5 Lee Smith Marketing
3. Suppose the following input form is used to enter contact information. Verbalize the
information that is being collected.
Relational Databases and Microsoft Access
15
1.2 M ICROSOFT ACCESS
MS Access is a relational database system for workstations that run the Microsoft Windows
operating system. MS Access is typically used by individuals for data they use personally, but in
some situations a single MS Access database may be used by a group of people or small
department.
MS Access databases are stored in a single file that has a file suffix of “.accdb” or “.mdb”.
Databases created using MS Access 2007 and later have a file suffix “.accdb”, and databases
created using MS Access 2003 or earlier have a file suffix “.mdb”. We will be using databases
where the files have names ending in “.accdb”. You need to use MS Access 2007 or later to open
these databases.
Ou
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
