A MySQL sample database

This section describes a sample database that may be used in all parts of this book. This database gives you a reference example when learning to put MySQL into work. We mainly give examples from the two scenarios described above:
Secretariat programme for the Agency. We need something more specific than the "agency," so now we're going to construct one that has the characteristics of a group of people gathered together to study the common Purpose of American history (for the time being a better name is called the American Historical Union). Regularly updated on the basis of the rendezvous fee
The qualifications of each member. Membership dues constitute the activities of the Alliance, such as the publication of the newspaper "America's Yun  thin 4 , the eb site, but the development of the function is not much." So far this site is limited to providing some basic information, such as the nature of the group, who is in charge, and what kind of person can participate.
Credit retention program. In the credit period, you need to manage the participants, record the scores and give the scoring level. The final scoring level is then determined and handed over to the school's office in conjunction with attendance. Let us now consider these situations further in the light of the following two requests:
You must determine what information you want to get from the database, that is, what you want to achieve.
You must plan what you want to enter into the database, and what data is about to be saved. Perhaps, before considering what data to enter into the database, reverse consider what data needs to be output from the database. Before the data can be retrieved, the data must be sent to the database. However, the way you use the database is driven by your goal, and the relationship between these methods and what information you want to take out of the database is more closely related to what information you enter into the database. Unless you intend to use this information later, you will certainly not waste time and effort in entering the database.
1.2.1 American Historical Alliance The initial situation of this program is that you, as the Secretary of the League, use Word processing documents to maintain the membership list. This makes it possible to generate a printed name and address record, but it is limited when you use the information to do something else. Suppose you intend to do the following:
You want to be able to use this name and address record to produce output in different formats and give only the information you need for the appropriate purpose. One of the goals is to generate an annual print name address record, which is what the alliance used to do, and you intend to continue printing. In addition, you can assume that the information in the name and address record is assigned to some other purpose, as in the annual party of the league.
A list of current members is given in the program provided. This application involves different sets of information. All the contents of each member's entry are used in the printed name and address record. For banquet programs, it is only necessary to take out a member's name (it is sometimes not easy to do this with a word processor).
You want to search for names and addresses to find members whose entries meet certain conditions. For example, you want to know which members will soon need to update their membership. Other applications involving search are generated by the need to maintain a list of key words for each member. These keywords describe some aspect of American history of particular interest to each member (such as civil War, economic depression, civil rights or Thomas Jefferson Life, etc.). Members will sometimes ask you for a list of members who have a similar hobby with them, and you will be happy to meet their requirements.
I want the name and address directory to be available online on the league web site. This is good for both members and you. If you can convert your name and address to a Web page, the online version of the name and address can be kept up to date in a more timely manner than the printed version. And if you can make this online name and address record available for search, then members can easily find their own information. For example, if a member wants to know about other members who are interested in the civil war, he can find the member himself without asking you to help him find it, and you don't have to take the time to do it. We are well aware that the database is not the most exciting thing in the world, so we are not going to enthusiastically
Claims that the use of databases can promote creative thinking. But when you stop seeing information as something you have to wrestle with (which is true when you're working with a document) and start imagining it as something that can be manipulated relatively easily (as you would expect with MySQL), Your ability to propose a new method of using or representing information will be liberated to some degree, such as the following examples are new methods:
If the information in the database can be moved to a Web site in the form of an online name and address record, you may have the information flow in other ways. For example, if members can edit their own entries online and update the database, you won't have to do all the editing work yourself, which will help make the information in the name and address record more accurate.
If you store email addresses in your database, you can use them to send emails to members who haven't updated their entries for a long time. Messages can display their entries to these members, ask them to view them, and then indicate how to make the necessary modifications using the utility provided by the Web site.
Databases help make your Web site more useful not only by associating it with a member table. For example, the league publishes a newspaper, "The American Chronicle," each of which has a page for children, containing history questions. Recent issues have focused on the biography of the President of the United States. The league's Web site can also contain a layout for the child, which brings the questions online. You might even be able to make this layout interactive by placing the questions that are fetched from the database and having the Web server query the random questions.
At this point, you may have remembered the usefulness of many databases, which makes you a little bit out of control. Before you go back to reality, you start asking special questions:
Isn't that a little ambitious? Do you have to do a lot of work in preparation? Of course, if you just want to do nothing, then everything is simple, and I do not pretend that all of these things are trivial to achieve. At the end of the book, however, all of the things we described were realized. Just remember one thing and don't have to do it all at once. We will decompose the work and do only one part at a time.
Can mysql do all this? No, it can't be enough. For example, MySQL has no direct web capabilities. While MySQL itself can't complete everything we're talking about, we can get the tools to work with MySQL to improve and expand MySQL's capabilities. We will use the Perl scripting language and the DBI (Database Interface) Perl module to write scripts to access the MySQL database. Perl has excellent text processing capabilities, allowing you to process query results in a highly flexible manner to produce output in a variety of formats. For example, we can use Perl to generate the name and address record of the multi-message text format (RT F), which is a format that can be read by all word processors. We can also use PHP in another scripting language. PHP is especially good for writing web applications, and it works with databases. This allows you to run a MySQL query from a Web page and generate a new page that contains the results of a database query. PHP works well with Apache, the world's most popular Web server, which makes it easy to do things like give a search window and display search results. MySQL integrates well with these tools and gives you the flexibility to combine them in your own way, and you can choose to implement your vision. Instead of being limited to the so-called "integration" features that are marketed aggressively, the actual work is only a fixed combination of each other.
Finally, there is a big question, and that is, how much is all this stuff going to cost? First, the budget of the League is limited. The answer is, probably not spending any money, which may surprise you. If you are familiar with the general database system, you will know that they are generally quite expensive. However, MySQL is generally free of charge. In some environments, it does not
A license is required, and if the number of users is unlimited, it will only cost $. (For a general introduction to the license, see the preface, please refer to the MySQL Reference guide for specific details.) The other tools we'll use (Perl, DBI, PHP, Apache) are also free, so everything is taken into account and can be fairly inexpensive to form a useful system. The choice of operating system to develop this database depends on you. All of the software we have described can run under UNIX, most of which can run under Windows. The authors recommend running MySQL and other tools under UNIX. They all originate under UNIX and then go to Windows. This means that their Windows version has a shorter maturity period and has not been thoroughly tested and used.
Now, let's consider other scenarios that use the sample database.
1.2.2 Credits Preservation Program
The initial idea is that as a teacher, there is a duty to save credits. The teacher wants to transfer credit processing from the manual operation of the credit book to MySQL on the electronic representation. In this case, what you want to get from the database is what is included in the credits book:
For each test or test, credit is recorded. For tests, the credits are sorted so that you can determine the range of scores for each character (A, B, C, D, and F).
At the end of the credits period, calculate the total score for each student, then sort the total score and determine the score level according to them. The total score may involve weight calculations, as it would probably be desirable to make the test score larger than the test and scoring weights.
At the end of each credit period, provide attendance information to the school office. The aim is to avoid manual sorting and summary of credits and attendance records. In other words, I want MySQL to sort the credits at the end of the credits period and to complete the calculation of each student's total score and miss number. In order to achieve this goal, the student roster in the class, the score for each test and test, and the date of absence from school are required.
1.2.3 sample database How to meet demand
If you're not interested in historical alliances or credits, you might wonder why you have to do these examples? The answer is that these sample schemes are not an end in themselves, but are used to illustrate what it can do with MySQL and its associated tools. With a little imagination, you'll see how the query for the sample database applies to the problem that you want to solve. If you work in the dental clinic mentioned earlier, you will see a lot of dental inquiries in this book. For example, determining which members of the History league need to update their membership immediately is something akin to determining which patients have not come to the dentist recently. Both are date based queries, so once you have learned to write a member-updated query, you can use the technology to write a more interesting delay in booking patient inquiries.