University of St Andrews
Library and Information Services
Getting started with
Microsoft Access
An introduction to
Relational Database
Management Systems
Index
Section 1: Introduction to Relational Database Design 5
1.1 What is a database? 5
1.2 The relational model 5
1.3 Designing the database 6
1.4 Data Normalisation 6
1.5 The Student/Module database 7
1.6 The Student/Module database Logical Design 8
Section 2: Introduction to Microsoft Access 9
2.1 What is Microsoft Access? 9
2.2 A note on versions 9
2.3 Using Microsoft Access at the University of St Andrews. 9
2.4 Starting up and creating a blank database 10
Section 3: Table Creation 11
3.1 tblStudent Table in Design View 11
3.2 tblStudent Table Design Details 12
3.3 tblModule Table in Design View 13
3.4 tblModule Table Design Details 14
3.5 tblStudMod and tblSchoolDept Tables 15
Section 4: Form Creation 16
4.1 Designing the Student Form 16
4.2 Designing the Module Form 19
4.3 frmModule Form Design View Properties 19
4.3 frmModule Form Design View Properties 20
4.4 The Dropdown Combo Box 21
Section 5: Entering and Deleting Data 25
5.1 Adding and Editing Records 25
5.2 Deleting records 25
5.3 tblStudent Table Data 26
5.4 tblModule Table Data 27
Section 6: Report Creation 28
6.1 rptStudent Report in Design View 29
6.2 rptStudent Report Design View Properties 30
6.3 rptModule Report in Design View 31
6.4 rptModule Report Design View Properties 32
Section 7: Querying the Database 33
7.1 Opening the database 33
7.2 Querying the database 33
7.2.1 Querying the tblModule table 33
7.2.2 Saving your query 35
7.3 More Criteria 35
7.4 Comparison operators 36
7.5 More on the Modules report 37
Section 8: Linking Tables and Setting Relationships 38
8.1 Relating Tables 38
8.2 Referential Integrity 39
Section 9: Manipulating Data Using Linked Tables 41
9.1 The Finished Students & Modules Form 41
9.2 The Main Student/Module Form 42
9.3 Properties for frmStudMod 43
9.4 The Modules Subform 44
9.5 frmSubStudMod 45
9.7 Linking the form and subform 46
8.9 Deleting records 50
Appendix 1: Data Types 51
Appendix 2: The Data Dictionary 53
Section 1: Introduction to Relational Database Design
Many scholars, software engineers and mathematicians have spent their lives devising the theories behind the relational model, and database design continues to evolve to suit the increasing complexity of data storage requirements. With limited time available it would be impossible to explain in any detail how a relational database is designed so, in this course, we can only hope to gain an insight into the basics of relational database design. But, by its conclusion, we hope you will have gained enough knowledge to design and construct simple databases which will adhere to the rules of the relational database model.
1.1 What is a database?
Any information held on a computer can be described as a database, but the term is usually reserved for information that is structured in a way that makes it easy to store, retrieve and search. There are many database models, from the network data and hierarchical models to the relational model. Each of these models has been described in many learned treatises and would take too long to summarise. Microsoft Access is the most commonly used desktop relational database management system (RDBMS), and it is used widely in the University, so we will use Access to construct our database.
1.2 The relational model
The aim of a relational database design (schema) is to remove data redundancy by storing repeating data in related tables. In this way we organise the data in a way that removes the duplication of data. If the schema is correctly designed we can combine data in related tables or queries when we need to use it, rather than having to store it repeatedly. This will greatly reduce the workload when entering data and, in turn, will reduce the human error which inevitably occurs when someone is required to enter or edit large amounts of data. Every spelling mistake or mistyped number is, potentially, a lost record when examining the statistical information available to the database user.
Consider the case that you are required to store the progress of a group of students through one semester at the University of St Andrews. During that time they will study five modules and will take three examinations. For each module undertaken, there will be two separate marks awarded for coursework, and one mark will be recorded for each exam. This means that the student must have at least thirteen marks recorded to define his or her progress through the semester. In addition there is personal information about the student to record, such as name, age, sex, date of birth, home address, term-time address, telephone numbers, financial details, etc. In addition everything about the modules must be recorded - module number, title, level, etc. All of this data could easily amount to thousands of entries in a database. In a flat-file database each individual record must have ALL of the data pertinent to the record stored within it. In a relational database each individual record is composed of unique data and contains only references to repeating data.
Imagine now that every time you add a new module to a student’s record, all the personal and module data must also be re-entered. This would be the case if the data were not structured in some way to remove the need to record everything each time a student’s progress was updated with new module or exam details. And what if the student changes his or her term-time address? Every record pertaining to that student would have to be retrospectively updated with the new information.
If we use a database which conforms to the relational module we remove the need to re-enter everything in every record in the database. Some data is unique and some data repeats, some data changes regularly and some remains static. A student’s personal data is unique to him or her and a module’s data is unique to it, but a student will study a number of modules and each module will be studied by several students.
In our case we must create a table to store the student data, another to store the module data and a third table to somehow match a particular student with all of the modules he or she studies. This third table could also store the marks for each exam and coursework undertaken. A fourth table will hold data about the school running each course. At this point in our sample database we will stop. At a later time you may wish to consider adding tables to hold examination data, etc. but for now our database is complex enough.
So, you can see that database design is of paramount importance. If you don’t get it right at the design stage you will find it impossible to store and retrieve the data at a later date.
1.3 Designing the database
The easiest way of picturing the structure of a relational database is to see it as a number of related tables, each comprised of rows and columns. In database terminology the rows are known as records and the columns as fields. Each row will have a field, or combination of fields which will hold values that will be unique to that record. This field, or combination of fields, is called the primary key. By referring to the value(s) in the primary key we uniquely identify the data in the record to which it refers. By holding rows of similar data in tables we complete the database design.
In a relational database management system such as Microsoft Access, data should not need to be repeated anywhere. Data are stored in tables and are related to other data by means of linking fields in corresponding tables. The goal of the database designer is to design a logical model (the schema) to represent this data. This process is called data normalisation.
1.4 Data Normalisation
There are seven levels of normalisation:
- un-normalised data All of the defined fields in an unorganised state
- zeroth normal form The same fields placed in a single table
- first normal form Remove repeating groups to new tables
- second normal form Remove partial dependencies to new tables
- third normal form Remove transitive dependencies to new tables
- Boyce-Codd normal form
- fourth normal form
- fifth normal form
As said previously it is not possible in this course to explain the technique behind the normalisation of data; it would take too long, but using common sense and asking the question: ‘Which data belongs together?’ should suffice to get a reasonable approximation of data normalised to third normal form. After some practice it becomes natural to ‘see’ which data is unique and which repeats. An obvious example in our database is that of student data and module data. Let us look first at the student data. In themselves, the student’s name, age, sex, address, etc. have no relevance to a module so they obviously ‘belong’ with the student table. Similarly, the module title, level, etc. have no relevance to the student data and therefore ‘belong’ with the module table.
If ten student records are entered in the database each record will be unique, but each of the ten students will study the same modules so the module data will repeat ten times for each module entered. The opposite also occurs. If the students each study five modules there must be five repeated records for each student as well. So now we have fifty records in an un-normalised table containing both student and module data. In this case the student data and the module data need to be held in separate tables.
A simple rule of thumb is to ask the question: Can a record in one table be related to more than one record in another table? If the answer is yes, this is a called a one-to-many relationship (1:M). In this case a relationship can be established between the two tables. If the answer to the question is no, then we have a one-to-one relationship (1:1) and if this is the case the data belongs in the same table.
There is, however, a third possible answer. For example; a student can study MANY modules and a module will be studied by MANY students. Here we have a many-to-many relationship (M:N) between students and modules. Relational databases do not like many-to-many relationships so we must resolve this problem by creating a third table. This table will have two fields; the primary keys from the two tables in question. By selecting both the fields and designating them as a shared (concatenated) primary key we can allow repeated entries for a module and repeated entries for a student but we prohibit the same combination of student and module appearing in the same row (record) more than once.
The standard way of allowing relationships to be set is to place a copy of the primary key from the ONE side into the MANY side table at the database design stage. This field becomes the foreign key when inserted into the many-side table. Every time a repeating value is entered into the ‘many’ table the primary key value from the ‘one’ side is also recorded in the record. Thus the two tables can be directly related on this field. We will see this theory put into practice when we design the Student/Module database which we will create and use in this course.
As stated earlier there are seven levels of normalisation, but most database designers stop at third normal form. The rules of normalisation are defined using set algebra and to explain them in any detail would take far too long. This may be the subject of a more advanced database design course in the future However a less experienced user can approximate organising their data to third normal form by answering a number of predefined questions of the data … and, of course, lots of practice!
1.5 The Student/Module database
The database we will create will store minimal data about students, modules, and schools. Of course, if the database were to be used in ‘real life’ situation, we would be required to hold more data and create more tables but, for the purposes of the course, the tables we define will suffice.
It seems logical that we would store student data in one table and module data in another, but we must test this assumption. If we ask the question ‘Can a record in a table holding student data be related to more than one module?’ the answer is yes, a student studies many modules. In this case we must split the module data out to another table. So, at this time we need to create two tables, one to hold student data and one to hold module data.
The student table should be on the ONE side of the relationship and the module table on the MANY side. So, according to the rules of the relational model, when we construct the module table there must be a field in the module table to hold the student ID. However, this would break the rules as well. The relationship between students and modules is a many-to-many relationship, which is not allowed in relational database design. A student studies MANY modules and a module is studied by MANY students. To overcome this problem we must create a third table to hold the primary keys from the student and module tables. This removes the M:N relationship and replaces it with two 1:M relationships, a state which the relational model requires. You will see this graphically if you study the diagram on page 34 of this workbook.