Database, it is a place use to store lots of data. It can be seen as a filing cabinet. And database management system (DBMS) is software that specificity designs for managing the data in database such as create, update or delete them. General is including security, retrieval and backup features. User also allows using the data to create a report or share data with multi-user.
MS Access is a database management system design by Microsoft. It offers user four important objects they are:
* Table A Table is where you are organised and store data so you have to create the table before store any data. By creating table, you need to define few things, Fields name, Data type, and the Description.
* Fields Name Fields name is the heading of column e.g. first name, last name and phone number.
* Data Type Data type is the type of content and it decides the content can be input and the file size.
* Description Description is use for explain what content should input to the user.
* Queries A query is use for searching some specific record. It allow user to set conditions and then search the record which match the conditions. User also is able to make the search mix up with other table and sort the order of the result.
* Report A Report is a summary of record. Everything you plain to print should put inside. Its purpose is for user to view information quickly and easily so display the result simply and efficiently is the main point.
* Form Form is used for collect and edits information and transfers them to table. It is similar to application forms that allow user to fill in or delete their data easily and it also able to let user design own user interface. User can insert as much control as they like to build the user interface beautifully.
Database Features Every database management system should have these feature to protect and ensure the data is safe and accuracy.
* Entities * Primary Keys Primary Keys is use to make a unique data. It will make sure user cannot enter the same text or number again. This is often use on ID or account number. But primary key is only able to use once in a table.
* Foreign Keys Foreign key is a column in a table where it is use as primary keys in another table. For example, a column calls “Order ID” and it is a primary keys in table 1, and in table 2 we have the same column “Order ID”. But in table 2 “Order ID” is not the primary keys any more, we will call it to foreign keys. It is use to build a relationships between tables.
* Referential Integrity It is used to prevent data inconsistencies. It is like a relationship between primary keys and foreign keys. If we insert, edit, or delete a data in primary keys but without change anything in foreign keys, it will affect the integrity of the data. So referential integrity can prevent you delete or change related data accidently.
* Auto incremented Keys Auto increment keys will automatic increase the value in the next field at the same column and the value must be a whole number. For example the last record you enter is 10, and then the new record you insert will auto increase to 11. This feature often use in ID numbers.
* Field Attributes * Data Types It decide what data user can enter. The available data types have Text, Number, Data and Time, Yes/No and etc. By using a correct data type can resize the database effectively.
* Size Field size is use to limit the length of the characters that user can enter. The reason to doing this is because database will reserve 255 spaces for a field and not matter how many spaces user actually use but this will cause the database larger than it need to be. By modify the size user can make the database smaller and to increase more record.
* Validation Rules Validation rules is to limit the action which user will do to the field. This rule will run when user enter data into the field and prevent enter a bad data. It is only allow user to enter the set text or value.
* Data Redundancy – Relationships General a database has a lot of tables and some tables are related to each other so relationship is use to make the connection between two or more tables. It is a method to avoid excessive data and to ensure the data integrity. There have three types of relationships:
* One to One For example, one employee only has one ID and one ID only for one employee. In many case, “One to One” relationship is for reduce the time to searching data.
* One to Many Simply, “One to Many” means one thing or one person has many relations with other. For example, one department can have many employees.
* Many to Many “Many to Many” is different with other two. To build “Many to Many” relationship you need to have at least three tables. It is because “Many to Many” cannot make the connection directly. For example, a student can choose many course but a course can have many student too so you cannot actually link them together. To link them, you will need a table to transfer data so the relationship will become “Many to One” and “One to Many”.