Table, Record và Field
Speaking to the database, we immediately think of SQLServer, Access or Oracle. Etc., where contains a lot of data so that we can store or retrieve them conveniently and quickly. Most programs we write will have access to databases, and we use it as a tool to work with a lot of data while focusing on the programming interface to the user (users).
So we need a basic knowledge about the architecture of the database to understand the reasons why we design or access it in certain ways.
We will use biblio.mdb Access Database, located at C: Program FilesMicrosoft VisualStudioVB98 iblio.mdb to illustrate the concepts need to know about databases.
In this database has 4 tables: Authors (author), Publishers (publishers), Titles (section)vaTitle Author.
Honestly speaking, we do not need a computer for storage or working with the Authors table like this. I could have used a box cutter, cut each one recording the details Au_ID, Author and Year Born of an Author.Such a plate is equivalent to a record crop and resource box is equivalent to the Authors table.
I'll plan to cut the number of boxes in order to Au_ID can quickly access records to know Au_ID. Only a suffering that, if you want to know how many authors, of the 300 cut in the box, older than 50 years it may take a few minutes can be answered. Faster than a computer database in the system manually (Manual) is in place.
The field or set of multiple fields (ie firstname and surname) for use in sorting this is called Index (finger).A table can have one or more of the Index. Each Index will be a small table of pointers , which contains the location of the records in the Authors table. It's like a catalog index at the end of the book contains page numbers to point us to the right place we want to find in the book.
When designing a table we specify datatype of each field to be able to check in with valid data or not.The common Datatypes is Number, String (containing text), Boolean (Yes / No), Currency (to treat the amount) and Date (to include date / time). Number datatype datatypes again include a variety of figures such as Integer, Long (integer accounting for 32 bits), Single, Double,. Etc.
Here are the fields in the record of Datatypes Author:
For example two tables Publishers and Titles are based on field relationships PubID ( Pub lisher IDentification - the publisher's history). Moreover, if you'll notice at the top of the table lines Publishers have numbers 1 , also at the wire side table Titles marked with infinity (∞). We call relation ( 1 - ∞ ) is one-to-many , that says a publisher could release many titles of books / CDs.
Meanwhile the two tables Author Titles and Title, we have a relationship one-to-one , ie corresponding to each record is a record only Title Author Title. The question is the relationship one-to-many is something important.
Imagine when we did with table Titles (temporarily called work), much as we want to know details of the publisher of her work. Actually, we were able to contain details of the publisher of each work day in the Titles table. However, doing so would disadvantage of records of the works of the same publishing house that will contain the same data. Every time you want to modify the details of a publishers have to fix them in each record of the publishers that Title. Because they want to have details of a publishers in a single place, avoid repetition, so we did contain them in a separate table, ie table Publishers.
If suppose we start designing the database with the Titles table, and then decided to separate the details of the publisher to enter a new table, named Publishers, the technique is called Normalization . In other words, Normalization is the design of tables in the database how each piece of data (not a Key) only appears in one place.
In relation to one-to-many between the Publishers and Titles tables, the Primary Keyfield in the table PubID Publishers. Titles in the table, field PubID called Foreign Key,which means that this is the Primary Key of a table is (Foreign). Or put another way, while working with the Titles table, when to publisher details, we will get strange key(Foreign Key) used as a Primary Key of Table Publishers want to access records.Notice that the Table Primary Key Titles have its ISBN.
Entity Integrity Rule said that the Primary Key is indispensable, ie values can not be NULL . This rule was confirmed for each Primary Key led to a unique row in the table, so of course it must be a decent value.
Note that Primary Key can be a composite key , which is a collection of some keys (columns / fields), should not have given any of the key columns are NULL.
Referential Integrity Rule nói rằng database không thể chứa một Foreign Key mà không có Primary Key tương ứng của nó trong một table khác. Điều ấy hàm ý rằng:
If you have installed on your computer available MSAccess, then you can use it to design the database tables or data in the tables.
When we create a record first, if not for the value of a field, the field that has value is NULL . Normally, Visual Basic 6.0 does not like NULL value, so we have to try a field with Function ISNULL () to ensure it does not have NULL values before working with it. If ISNULL returns the value False, then we can work with that field. Remember when NULL value is used in an expression, even when the program is not for the error, the result is NULL.
Meanwhile, a Database Server as SQLServer running their own to serve any client program (client) is needed. Database Server is suitable for applications with many users because it alone is responsible for all data access clients. It may contain many local routines, known as Stored Procedures , to perform the client requests a performance. Database Server is often effective ways to cope with the problems of hardware such as disk failure or power failure. In addition, the Database Server is available the means of security and backup. It also has additional functionality to use this network.
Today, we gather data in various forms such as email, Word documents, Speadsheet. Not necessarily always contain data in the form of the records table and not necessarily the data is always stored in a database properly. However, they still regarded as a database under the eye applications. Therefore, we use from the Data Store (Database) to replace the database refers to data container. For programs and data consumption, said the Data Source (source data) instead of the database.
When programming in VB6 to access the database, we looked databse in the abstract, ie it is oil Access, dBase, SQLServer or Oracle is also considered equal. If a change of the database below, our programming will not change much.
In the future, an XML file can also be seen as a small database. It can stand alone or as a table extracted from a primary database command. XML is a standard that can be used to import / export data from all types of database support for XML. We can exchange data on the Intenet Network as XML. Also, instead of working directly with a large database, we can extract a few tables from that database into a XML file. Then we just programming with XML file until the end would make up (merge / Chỉnh hợp) XML file with a large database. If the majority of application programs are designed to work this way, the future does not need a very strong Database Server.
So we need a basic knowledge about the architecture of the database to understand the reasons why we design or access it in certain ways.
We will use biblio.mdb Access Database, located at C: Program FilesMicrosoft VisualStudioVB98 iblio.mdb to illustrate the concepts need to know about databases.
In this database has 4 tables: Authors (author), Publishers (publishers), Titles (section)vaTitle Author.
Authors table contains many records. Each record in the Authors table contains threefields: Au_ID, vaYear Born Author (year of birth). We can present the Authors table in the form of a spreadsheet as follows:
Because the same field of the records displayed in the same column of the spreadsheet, so we also speak of a field as a column (column). And because each data record occupies a row (row) of the spreadsheet, so sometimes we also talk about a record as a row .
Honestly speaking, we do not need a computer for storage or working with the Authors table like this. I could have used a box cutter, cut each one recording the details Au_ID, Author and Year Born of an Author.Such a plate is equivalent to a record crop and resource box is equivalent to the Authors table.
I'll plan to cut the number of boxes in order to Au_ID can quickly access records to know Au_ID. Only a suffering that, if you want to know how many authors, of the 300 cut in the box, older than 50 years it may take a few minutes can be answered. Faster than a computer database in the system manually (Manual) is in place.
Primary Key và Index
To avoid confounding, usually a record field of, for example Au_ID Authors table, which is intended to contain a unique value (unique). Authors in the table that is only one record with a numeric field that Au_ID only. They call it the Primary Key .I also do not always want to access a record based on Au_ID Author. Sometimes we want to use the name of the Author to access, so we also need to sort the records available in order of alphabet. We can also back with more fields to sort the records. In fact, the records themselves need not be moved to positions in the right order. One need only remember where its position in the table is enough.
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (570x240)
The field or set of multiple fields (ie firstname and surname) for use in sorting this is called Index (finger).A table can have one or more of the Index. Each Index will be a small table of pointers , which contains the location of the records in the Authors table. It's like a catalog index at the end of the book contains page numbers to point us to the right place we want to find in the book.
When designing a table we specify datatype of each field to be able to check in with valid data or not.The common Datatypes is Number, String (containing text), Boolean (Yes / No), Currency (to treat the amount) and Date (to include date / time). Number datatype datatypes again include a variety of figures such as Integer, Long (integer accounting for 32 bits), Single, Double,. Etc.
Here are the fields in the record of Datatypes Author:
Is there a special datatype called Autonumber . Long, but it is really value is generated automatically every time we add a new record into the table. I am not doing more is to accept that figure.
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (574x355)
Relationship và Foreign Key
Now, if you're running a Microsoft Access database to look biblio.mdb, you can use the Command Menu Tools | Relationships as follows to see the relationship (relationships) between the tables.Access will display the Relationships dialog delivery, where each table contains the names of the fields.Each table has one or two connecting wires through the tables klhac. Each string is a link (relationship), it connected a field in a table with a field with the same name in another table.
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (739x281)
For example two tables Publishers and Titles are based on field relationships PubID ( Pub lisher IDentification - the publisher's history). Moreover, if you'll notice at the top of the table lines Publishers have numbers 1 , also at the wire side table Titles marked with infinity (∞). We call relation ( 1 - ∞ ) is one-to-many , that says a publisher could release many titles of books / CDs.
Similarly, in relation to one-to-many table between Authors and Title Author, we find an author (the first is number 1) has composed many works can be represented by the record Author Title.
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (631x287)
Meanwhile the two tables Author Titles and Title, we have a relationship one-to-one , ie corresponding to each record is a record only Title Author Title. The question is the relationship one-to-many is something important.
Imagine when we did with table Titles (temporarily called work), much as we want to know details of the publisher of her work. Actually, we were able to contain details of the publisher of each work day in the Titles table. However, doing so would disadvantage of records of the works of the same publishing house that will contain the same data. Every time you want to modify the details of a publishers have to fix them in each record of the publishers that Title. Because they want to have details of a publishers in a single place, avoid repetition, so we did contain them in a separate table, ie table Publishers.
If suppose we start designing the database with the Titles table, and then decided to separate the details of the publisher to enter a new table, named Publishers, the technique is called Normalization . In other words, Normalization is the design of tables in the database how each piece of data (not a Key) only appears in one place.
In relation to one-to-many between the Publishers and Titles tables, the Primary Keyfield in the table PubID Publishers. Titles in the table, field PubID called Foreign Key,which means that this is the Primary Key of a table is (Foreign). Or put another way, while working with the Titles table, when to publisher details, we will get strange key(Foreign Key) used as a Primary Key of Table Publishers want to access records.Notice that the Table Primary Key Titles have its ISBN.
Relational Database
A database has many tables and supports contact, especially one-to-many, is called Relational Database . When designing a database, we will seek to arrange the data from the real world outside of the tables. I will decide on the column (columns / fields) does, select Primary Key, Index and set of relationships, that is located where the Foreign Key.Benefits
Among the benefits of a Relational Database design is:- Modify data, add new records or delete (crossed out) records available very efficient (fast).
- Access data, make a report (Reports) are also effective.
- Because the data are arranged in order and is regulated so we can trust the love of the database (not third round, when the body, while the other is - shock shock).
- Since most data in the database, instead of application programs, so the own database documentation (document explains).
- Easy to modify the structure of the tables.
Integrity Rules (integrity rules)
Integrity Rules are used to talk about the need to follow rules while working with a database to ensure better database. There are two types of rules: general rules (General Integrity Rules) and the rules for the database (Database-Specific Integrity Rules.) These laws are usually subject to separate rules on trade (Business Rules).General Integrity Rules
There are two Integrity rules related entirely to the database: Entity (nature) and referential integrity rule (only) Integrity Rule.Entity Integrity Rule said that the Primary Key is indispensable, ie values can not be NULL . This rule was confirmed for each Primary Key led to a unique row in the table, so of course it must be a decent value.
Note that Primary Key can be a composite key , which is a collection of some keys (columns / fields), should not have given any of the key columns are NULL.
Referential Integrity Rule nói rằng database không thể chứa một Foreign Key mà không có Primary Key tương ứng của nó trong một table khác. Điều ấy hàm ý rằng:
- You can not add a Row into a Table with Foreign Key value in the Row was not found in the list of table Primary Key side one (1) which it relates.
- If there is a change of the value of Primary Key Row or delete a Row in a table-side one (1), we can not let the records in the table side nhiều (∞) contains the rows become orphans (Orphans).
- Disallow (do not): Absolutely not let this happen.
- Cascade (chain effect): If the value is changed, the Primary Key Foreign Key corresponding to the numbers in the records of the table side nhiều (∞) is changed.
If the Primary Key Row containing the records were deleted, the corresponding tableside nhiều (∞) was deleted by. - Nullify (to NULL): If Row is deleted contains the Primary Key Foreign Key correspondingto the numbers in the records of the table side nhiều (∞) is changed to NULL, so that suggests not to go to find more information any where.
Database-Specific Integrity Rules
Any integrity rules other than referential Integrity Entity Integrity Rule or Rule shall be known as Database-Specific Integrity Rules. These rules are based on the type of database and is subject to the rules of trade (Business Rules) for the database we use, for example, each record of the wages of field workers must have a Tax Number (Tax Number ) by the Department of Taxation issued to citizens. Note that these rules are also important as the general rules of integrity. If we do not apply the Database-Specific Integrity Rules, the database can be seriously damaged and no longer used.Microsoft Access Database Management System (MSAccess DBMS)
Microsoft Access Database Management System including Database Engine, and the tools to go together to provide users a friendly working environment with the database, such as Database Design (design of tables and relationships), data entry and reporting ( reports). Included with Visual Basic 6.0 when you buy a copy of your MSAccess Database Engine. His name was Jet Database Engine , the core of the DBMS MSAccess. VB6 programs can access the database via the Jet Database Engine.If you have installed on your computer available MSAccess, then you can use it to design the database tables or data in the tables.
Properties Required và Allow Zero Length
When designing a table field, note the property is Required and Allow Zero Lengthproperty of the text. If a field's Required property is Yes, then we can not update (write) arecord with fields that have NULL values. If a Text field with Allow Zero Length propertyis No, then it can not update a record when that field contains an empty string. | This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (870x365) |
When we create a record first, if not for the value of a field, the field that has value is NULL . Normally, Visual Basic 6.0 does not like NULL value, so we have to try a field with Function ISNULL () to ensure it does not have NULL values before working with it. If ISNULL returns the value False, then we can work with that field. Remember when NULL value is used in an expression, even when the program is not for the error, the result is NULL.
Working with different versions
If your computer is running MSAccess2002 then you can work with Access database file version 1997, 2000 and 2002. If you need to convert from this version over the other version, you can use the Command Menu Access DBMS Tools | Database Utilities | Convert Database | To Access 2002 File Format ... .If you want to keep the version, you can convert the database over 2002 File Format for amendment, then convert back to the old File Format.Access database file grows very quickly, because the records had been deleted is still raw, so each week you should remember to compress it to put all the records had been deleted by using the Command Menu Access DBMS Tools | Database Utilities | Compact and Repair Database ... or use DBEngine.CompactDatabase function in VB6.
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (752x274)
Used to write SQL Query
One way to access a database language used Structured Query Language (SQL) standard by the ISO / IEC released in 1992, referred to as SQL92 . All support the common database SQL, though sometimes they add more functions very well but not in the standard. The most common are SQL SELECT, UPDATE, INSERT and DELETE . Means we can use the design of MSAccess Query to SQL. After Query design by drag drop the fields, you can use the Menu Command View | SQL View is as follows:Following is the SQL Query statement above that you can copy to paste into the VB6 code:
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (786x433)
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (511x80)
Link table used to work directly with the database Other
We could use a different database types, such as dBase, directly in an Access database using VB6 as usual. He wanted to set the hook, you use the Menu Command File | Get External Data | Link Tables ... and then select the file type of the dBase table you want to put it in the Access database is open:
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (638x542)
Database Server and a number of ideas
Although Jet Database Engine is a relational database and performance is very good, it kind Based File database , ie it is passive, but I have not run a program depending on its use. Based database file is not suitable for applications with many users at once.Meanwhile, a Database Server as SQLServer running their own to serve any client program (client) is needed. Database Server is suitable for applications with many users because it alone is responsible for all data access clients. It may contain many local routines, known as Stored Procedures , to perform the client requests a performance. Database Server is often effective ways to cope with the problems of hardware such as disk failure or power failure. In addition, the Database Server is available the means of security and backup. It also has additional functionality to use this network.
Today, we gather data in various forms such as email, Word documents, Speadsheet. Not necessarily always contain data in the form of the records table and not necessarily the data is always stored in a database properly. However, they still regarded as a database under the eye applications. Therefore, we use from the Data Store (Database) to replace the database refers to data container. For programs and data consumption, said the Data Source (source data) instead of the database.
When programming in VB6 to access the database, we looked databse in the abstract, ie it is oil Access, dBase, SQLServer or Oracle is also considered equal. If a change of the database below, our programming will not change much.
In the future, an XML file can also be seen as a small database. It can stand alone or as a table extracted from a primary database command. XML is a standard that can be used to import / export data from all types of database support for XML. We can exchange data on the Intenet Network as XML. Also, instead of working directly with a large database, we can extract a few tables from that database into a XML file. Then we just programming with XML file until the end would make up (merge / Chỉnh hợp) XML file with a large database. If the majority of application programs are designed to work this way, the future does not need a very strong Database Server.
(Collected)
0 comments:
Post a Comment