- slide 1 of 3
MS Access is a premier relational database management application among desktop database systems. Access is good fit in small and medium enterprises. Besides database creation, Microsoft also bundles other features into the Access program. As a database, it supports both SQL (structured query language) and QBE (Query by example). Access includes full-fledged programming language, macro capability, forms and report development tools, wizards and builders that enable fast development.
- slide 2 of 3
A database is a structured arrangement of data held in storage so that all kinds of inquiries can be made through the structured query languages mentioned earlier. This retrieval of data depends on how well the data is organized. This means careful thought in the design of this structure is required. Careful thought should be given, as changing the structure is usually difficult, if not impossible, once the Access database is populated with data and grows in size.
The complete database in Access is stored as a single Windows file, which can contain one or more tables. A table is a collection of data about one type of item. For example, a school database may have a table regarding students and another for teachers. An item about which data is collected is known as an entity in general database parlance. An entity may be defined as a thing which is recognized as being capable of an independent existence and which can be uniquely identified. A physical item as a house or a car, an event such as a house sale or a car service, or a concept such as a customer transaction or order can be an entity. The entities have individual attributes as well as relationships with other entities. An attribute is a characteristic of the entity. For example, the date of birth of a student in the student database is an attribute of the entity “student." A teacher who teaches a specific class is a relationship between the entity “teacher" and an entity "class."
Tables in Access can have multiple fields. You can create a record by assembling several fields. Each field has a specific data-type associated with it as a check for legal value that may be entered into these fields. For example, a field defined as "LastName" will have a text data-type associated with it. At the last level is the value that is attached to the field.
- slide 3 of 3
Entity and Relationships
One simple view of all the data is a spreadsheet-like table that contains a unique identifier. Some examples include name of a student, his date of birth, date of enrollment, class in which he is studying and so on. The unique identifier or the “primary key" helps locate a particular record in the table easily. Suppose a database is created for tracking progress of students through the school years. The courses taken by each student and the students’ performance are recorded. This part of data would change every semester. One could plan to have fields for every subject a student is likely to take and create one flat monolithic table. Alternatively, one could have unchanging data, such as data about the student during the degree program, his ID, name and other unchanging data in a master table. There can then be another table (even a set of tables per semester) that captures the changing data of the courses taken and grades in each of them.
To find answers to queries, such as “What was (name)’s score in course 509 in the third semester," one would need to locate the master record first. The other table(s) will have to have a key that is the same as the primary key found in the master record. This typically is called the foreign key. As you can see, this organization takes a lot of planning. You cannot modify the structure over time and add or delete fields and records as needed.
An entity, its attributes and relationships help you plan during the design stage, before any data bit has been entered. If the entities, their attributes and the relationships are thought through completely, one can easily design the master table and related secondary tables. This will ensure that all possible queries can be satisfied easily over the lifetime of the database. What helps with finalization is an ER diagram (entity-relationship diagram) that brings out these relationships visually, allowing you to verify whether all entities, their attributes and the required relationships have been captured. There are several accepted conventions of drawing these diagrams, including the universal modeling language or the UML. What is important is to use any one of these and create an ER diagram, so that the needed structure of the database is absolutely clear and confirms it can do the intended job.