Introduction
What is an Access Database?
A database is collection of records and files that are organized for a specific subject or purpose.
A record is a collection of fields that may be treated as a whole or individually.
Significance of the Study
Chapter 1 .1
Not everyone can be a Programmer. Microsoft Access Database was designed for anyone to utilize it with a Programmer background. Unlike SQL Server Databases.
Without writing code or being an database expert you can:
• Use templates to build databases quickly.
• Easily find and report on data stored in Access.
• Create rich data entry forms.
• Import, transform, and export a variety of data sources. You can use access to create databases such as:
• A telephone directory
• A timetable
• Address book
Once a record has been defined for a program, one task is to get data into the record. This is usually accomplished by reading from a data file.
Access Database consists of a collection of
Tables, Queries, Forms, and Reports that you build to manage, analyze, and present your data. These identifiable units (components) are called Objects.
These objects are dependent to other objects in the database.
That is a Query, Form, or Report draws information from Tables so changing data in any of these objects changes the data in all these objects and the database
Database is a structured collection of information pertaining to a specific topic.
• Database Objects are database elements such as tables, queries, forms, and reports.
• Database Relationships are logical linking between tables. They are ties that are created between tables in the database so that Access can combine data from more than one table.
The linking between two tables is called Join
Field is a space allocated for information about an object. It is the smallest unit of information you can retrieve from a database.
• Foreign key is the primary key in another table
• Form is an object designed and used for data input or display, one record at a time.
• Null is a missing value (commonly the result of human errors).
• Primary key is a field that uniquely identifies a record
• Record is a row of data in a table, which is a set of attributes as defined by schema.
• Report is an object designed and used for formatting, calculating, summarizing, and printing selected data.
• Table is an object you define and use to store data.
• Query is a request to retrieve specific information from a database
Creating Records (Populating the Table)
The Primary Key A primary key is a field or set of fields with values that are unique throughout a table. Values of the key can be used to refer to entire records because each record has a different value for the key.
Each table can only have one primary key. Access can automatically create a primary key when you create a table, or you can what field you want to use as the primary key
Data Types for Access Databases When creating tables, you need to select a data type for each column of data. The Short Text data type is the default data type since it lets you enter almost any character (letter, symbol, or number). However, careful selection of data types can help you with data validation and functions and improves the accuracy of the information you are storing.
When to Use Which Data Type Data type is a set of qualities that applies to all the values that are contained in the field. For example, values that are stored in a Text field can contain only letters, numbers, and a limited set of punctuation characters, and a Text field can only contain a maximum of 255 characters.
1.2
Data Definition Let us now understand what Data Definition is:
§ In document or a spreadsheet, you generally have complete freedom to define the contents of the document or each cell in the spreadsheet.
§ In a document, you can include paragraphs of text, a table, a chart, or multiple columns of data displayed with multiple fonts.
§ In spreadsheet, you can have text data at the top to define a column header for printing or display, and you might have various numeric formats within the same column, depending on the function of the row.
§ An RDBMS allows you to define the kind of data you have and how the data should be stored.
§ You can also usually define rules that the RDBMS can use to ensure the integrity of your data. § For example, a validation rule might ensure that the user can’t accidentally store alphabetic characters in a field that should contain a number.
1.3
Data Manipulation MS Access Working with data in RDBMS is very different from working with data in a word processing or spreadsheet program.
· In a word processing document, you can include tabular data and perform a limited set of functions on the data in the document.
· You can also search for text strings in the original document and, with ActiveX controls, include tables, charts, or pictures from other applications.
· In a spreadsheet, some cells contain functions that determine the result you want, and in other cells, you enter the data that provides the source information for the functions. An RDBMS provides you many ways to work with your data. For example,
· You can search a single table for information or request a complex search across several related tables.
· You can update a single field or many records with a single command.
· You can write programs that use RDBMS commands to fetch data that you want to display and allow the user to update the data. Access uses the powerful SQL database language to process data in your tables.
Using SQL, you can define the set of information that you need to solve a particular problem, including data from perhaps many tables.
Data Control Spreadsheets and word processing documents are great for solving single-user problems, but they are difficult to use when more than one person needs to share the data.
· When you need to share your information with others, RDBMS gives you the flexibility to allow multiple users to read or update your data.
· An RDBMS that is designed to allow data sharing also provides features to ensure that no two people can change the same data at the same time.
· The best systems also allow you to group changes (which is also known as transaction) so that either all the changes or none of the changes appear in your data.
· You might also want to be sure that no one else can view any part of the order until you have entered all of it.
· Because you can share your Access data with other users, you might need to set some restrictions on what various users are allowed o see or update.
Chapter 2
Microsoft Access’s strength it has a strong ER Diagram background that gives Non Programmers a clear understanding of E Codd Theory
What is an Entity Relationship Diagram (ER Diagram)? An ER diagram shows the relationship among entity sets. An entity set is a group of similar entities and these entities can have attributes. In terms of DBMS, an entity is a table or attribute of a table in database, so by showing relationship among tables and their attributes, ER diagram shows the complete logical structure of a database. Lets have a look at a simple ER diagram to understand this concept. Facts about ER Diagram Model: ER model allows you to draw Database Design It is an easy to use graphical tool for modeling data Widely used in Database Design It is a GUI representation of the logical structure of a Database It helps you to identifies the entities which exist in a system and the relationships between those entities
A general understanding to the three data models is that business analyst uses a conceptual and logical model to model the business objects exist in the system, while database designer or database engineer elaborates the conceptual and logical ER model to produce the physical model that presents the physical database structure ready for database creation. The table below shows the difference between the three data models.
Conceptual model vs Logical model vs Data model:
| ERD features | Conceptual | Logical | Physical |
| Entity (Name) | Yes | Yes | Yes |
| Relationship | Yes | Yes | Yes |
| Columns | Yes | Yes | |
| Column’s Types | Optional | Yes | |
| Primary Key | Yes | ||
| Foreign Key | Yes |
Conceptual data model
Conceptual ERD models the business objects that should exist in a system and the relationships between them. A conceptual model is developed to present an overall picture of the system by recognizing the business objects involved. It defines what entities exist, NOT which tables. For example, ‘many to many’ tables may exist in a logical or physical data model but they are just shown as a relationship with no cardinality under the conceptual data model.
Conceptual data model example
NOTE: Conceptual ERD supports the use of generalization in modeling the ‘a kind of’ relationship between two entities, for instance, Triangle, is a kind of Shape. The usage is like generalization in UML. Notice that only conceptual ERD supports generalization.
Logical data model
Logical ERD is a detailed version of a Conceptual ERD. A logical ER model is developed to enrich a conceptual model by defining explicitly the columns in each entity and introducing operational and transactional entities. Although a logical data model is still independent of the actual database system in which the database will be created, you can still take that into consideration if it affects the design.
Logical data model example
Physical data model
Physical ERD represents the actual design blueprint of a relational database. A physical data model elaborates on the logical data model by assigning each column with type, length, nullable, etc. Since a physical ERD represents how data should be structured and related in a specific DBMS it is important to consider the convention and restriction of the actual database system in which the database will be created. Make sure the column types are supported by the DBMS and reserved words are not used in naming entities and columns.
Major Feature in Microsoft Access to create the Database
