Machine Translated by Google
IIIII
TELFER
VOTRE LIEN AVEC CE QUI COMPTE - CONNECTS YOU TO WHAT MATTERS
Tools for the application of management information systems
ADM1770 - Winter 2024
Access 2 Laboratory
Reminder
The laboratories must be completed independently. The professor will be available to answer your
questions during the online laboratory session on Teams. Subsequently, if you have any questions,
please contact your teaching assistant by email.
Remember that you must do this work individually. To complete this assignment, you should allow
approximately two hours. This laboratory will be graded. The submission procedure is at the end of this
document. You will need to submit an Excel file and the personal ethics certificate.
Important note: Do not wait until the last minute to start this work.
Please follow the steps in How to Access MS Access on Brightspace to access MS Access if you do not have
it on your computer. The instructor and teaching assistant will not answer last-minute MS Access login
questions and are not responsible if you are unable to upload your work on time.
Introduction to the relational model – relational database
In order to create the tables, the fundamental objects of an Access database, there must be prior methodical
thinking about the design and organization of the information to be managed. Relationships can be set up
between all tables by linking the primary key of the main tables to a foreign key field of a table to be related.
The relationships must be possible and must be formalized by the establishment of referential integrity
constraints.
A relational database contains several tables. A linking field (between primary key and foreign key) securely
links the tables. There must be a common field in the tables you want to create relationships with. For
example, if you have a table that contains a customer's demographics and another table that contains the
day's sales, one way to link them would be to identify the customer by a number. This number must therefore
be found in both tables. Using relationships, it will be easier to create forms without having to select individual
data from each of these two tables.
We will illustrate these different notions with the following case.
Model design
Note: This part of the instructions document is an explanation of what you are going to do, but you will not
find any instructions per se (the instructions begin
1 | Page Machine Translated by Google
ADM1770
Fall 2023
to the next part). You need to read this part carefully to understand what you are going to
do
You have completed your internship at Anne of Green Gables Chocolates (AGGC) and graduated.
After your graduation, you decide to return to AGGC and manage to get hired as a manager by the chain. You
notice that the company has changed a bit since your internship, and that now AGGC stores sell chocolates
from manufacturers other than AGGC itself.
Having already been convinced of the usefulness of Excel thanks to your past experience,
you are now determined to find a way to make a database to keep track of all of your
products. You must therefore design a database on Access to know which chocolates you
have in which stores.
With this information, two tables to create are obvious:
⚫ a "CHOCOLATE" table, to designate the chocolates that you offer or have
offered for sale. ⚫
a ❝STORE" table, to designate each store in your chain.
The most common type of relationship in Access database is the 'One-to-many'
relationship. A 'One to many' relationship could be defined as follows: a store can
have several different chocolates, but the same chocolate can only be sold in one store.
On the other hand, in our case, what we are trying to do corresponds more to a 'Many-to-
many' relationship:
•
⚫ the same chocolate can be sold in several stores and ⚫ a
store can sell several different chocolates.
This 'Many-to-many' relationship must be expanded into multiple 'One-to-many'
relationships using a 'STOCK' junction table, which contains a 'One-to-many'
relationship with the other two tables. This table will indicate the stock of a certain
chocolate that a certain store has:
• the same chocolate can be in the stock of different stores • a
stock of chocolates is only associated with one type of chocolate
And
• a store can have stocks of different chocolates but • a stock
of one chocolate only belongs to one store.
So our relational model contains 3 tables:
⚫ a "CHOCOLAT" table which contains the primary key "ID_CHOCOLAT" and all the
fields necessary for its file (sale price, release date, cream, etc.).
2| Page Machine Translated by Google
ADM1770
Fall 2023
⚫ a ❝STORE" table which contains the primary key “STORE_ID" and all the
fields relating to the description of this store.
⚫ a “STOCK” table which contains a primary key "ID_STOCK", the foreign key
"ID_CHOCOLAT" and the foreign key "ID_STORE".
To improve the database, three tables will be added: • a
"MANUFACTURER" table, which will display information relating to the manufacturers of the
different chocolates that we offer. This table will be linked to the "CHOCOLATE"
table by the "ID_FABRICANT" key.
⚫ a "STATUS" table, which will allow you to know the status of a store's stocks with
regard to replenishment (unplanned, planned, in progress). This table will be
linked to the "STOCK" table by the
.
"ID_STATUT" key. ⚫ a "CITY" table, which will link postal codes and cities. This table
will be linked to the “STORE" table by the "CODE_VILLE” key and to the
"MANUFACTURER" table by the "CODE_VILLE" key.
Now let's think about the choice of primary keys:
•
In the "CHOCOLATE" table, the ID will consist of a simple automatic number (1, 2, 3,
4...): the first chocolate will have the number 1, the second will have the number 2, etc.
• In other tables, the primary keys will have the same numbering. (the CITY table is
not an exception, the CITY_CODE key will also consist of an automatic
number (1, 2, 3, etc.))
BEFORE GOING FURTHER, OPEN ACCESS IN FRENCH
Creating tables
The reflection on the structure of the database having been completed, it is possible
to move on to the physical creation of the database. Refer to the first Access Lab to
understand the specific steps.
If the instructions below are not enough for you, you can also consult our CAPSULE
ACCESS devoted to the creation of a table under ACCESS on Brighspace (week 10):
01:03 Creating the table
01:15 Creating fields
03:03 Primary key
3 | Page Machine Translated by Google
ADM1770
Fall 2023
Finally, you can consult our ACCESS CAPSULE dedicated to modifications that may be useful to make to fields under
Access (on Brighspace week 10):
00:25 The Forbidden Null
02:23 L'indexation
03:22 Other settings
Create the "Chocolates
-
Last Name, First Name" database. Don't forget to close the first table that
opens automatically.
Then create the "CHOCOLATE" table with the following fields:
⚫ CHOCOLATE_ID, primary key, AutoNumber, Long Integer, Indexed without duplicates
•
⚫ CHOCOLATE_TYPE, Short text, 255 characters, Null prohibited, Indexed with duplicates ⚫
CREAM, Short text, 20 characters, Null prohibited, not Indexed ⚫
RELEASE_DATE, Date/Time, “Date, complete”, Null prohibited, not Indexed ⚫
SALE PRICE, Monetary, Null prohibited, not Indexed ⚫
MANUFACTURER_ID, Numeric, Long integer, Decimals 0, Null prohibited, not Indexed
PERCENTAGE_CHOCOLATPURE, Numeric, Long integer, Null prohibited, not Indexed
Then create the "STORE" table with the following fields:
•
•
STORE_ID, primary key, AutoNumber, Long Integer, Indexed without duplicates ⚫
STORE_NAME, Short text, 50 characters, Null prohibited, Indexed without duplicates •
EMPLOYEE_NUMBER, Numeric, Long integer, Decimals 0, Null prohibited, not Indexed
• CITY_CODE, Numeric, Long integer, Decimals 0, Null prohibited, not Indexed
Then create the "MANUFACTURER" table with the following fields:
• MANUFACTURER_ID, primary key, AutoNumber, Long Integer, Indexed without
•
duplicates ⚫ MANUFACTURER_NAME, Short text, 50 characters, Null prohibited, Indexed without duplicates
•
• SLOGAN, Short text, 100 characters, Null prohibited, Indexed with duplicates ⚫
CODE_VILLE, Numeric, Long integer, Decimals 0, Null prohibited, not Indexed
Then create the "STATUS" table with the following fields:
• STATUS_ID, primary key, AutoNumber, Long Integer, Indexed without duplicates
• STATUS_NAME, Short text, 50 characters, Null prohibited, Indexed without duplicates
Then create the "CITY" table with the following fields:
• CITY_CODE, primary key, AutoNumber, Long Integer, Indexed without duplicate
4| Page Machine Translated by Google
ADM1770
Fall 2023
•
CITY, Short text, 50 characters, Null prohibited, Indexed without duplicates
Then create the “STOCK” table with the following fields:
• STOCK_ID, primary key, AutoNumber, Long Integer, Indexed without
•
duplicate ⚫ CHOCOLATE_ID, Numeric, Long Integer, Null prohibited,
•
not Indexed ⚫ STORE_ID, Numeric, Long Integer, Null prohibited,
•
not Indexed ⚫ STATUS_ID, Numeric, Long Integer, Null prohibited,
•
not Indexed ⚫ STOCK_AVAILABLE, Numeric, Long integer, Decimals 0, Null prohibited, not Indexed
Setting up referential integrity - establishing relationships
Before applying referential integrity, you must close each of your tables in the navigation bar.
If the instructions below are not enough for you, you can also consult our CAPSULE
ACCESS where you will be shown how to connect tables and what it is for (on Brighstapce week 12):
01:17 Create relationships between tables
03:25 A relationship: what it's for and how it works
• In the "Database Tools" menu, click "Relationships".
DUNNELS LATLINNES
Relations Dépendances
d'objet
Relations
• Double-click all tables and close the window (or select all tables then click add).
5 | Page
