Content of this Chapter◆ Database overall analysis and design ◆ database establishment ◆ improvement of database objects This chapter reviews, contacts, and consolidates previous studies. By establishing a micro-student information database instance, to use the previous knowledge flexibly.
I. Database Function AnalysisThe student information database instance has the following functions: ◆ the user can store the personal information of the student. ◆ The user can store information about student learning. ◆ The user can store information about student and teacher information. ◆ Users can store Students' Accommodation information. ◆ Users can store information about student classes. ◆ You can enter, modify, or delete the preceding information. ◆ You can query and count the preceding information in multiple ways. ◆ You can report the query and statistical results.
Ii. Database Design and table Creation
1.
Design tableAccording to the above functional requirements, the following information should be collected: ◆ student: Student ID, name, gender, nationality, age, political outlook, class name, Dormitory number ◆ course (class ): course ID, course name, instructor ID, credits, class hours ◆ Instructor (teacher): Instructor ID, name, home address, phone number, email ◆ dormitory (dormitory ): dormitory No., dormitory Director, phone number, dormitory bed ◆ class (Team): Class Name, department, number of members, class teacher, class leader, Study Committee Member, Life Committee member ◆ score (score ): score ID, student ID, course Id, score
2.
Create a data tableIn the process of designing a table, there are two key issues: first, what tables should be created, and second, what fields should be included in the table. This is a very important initial step. In this way, the structure of the database table is actually established. The following describes how to create a data table. In this example, enter some sample data. As shown in:
3.
Create a relationship between tables
4.
Exercise filtering and viewing wizardExercise 1: view the records of all students whose "Class Name" is "Xin Ke 0101. Exercise 2: view all student records whose political outlook is not empty. Exercise 3: Modify the type of the "instructor ID" field in the "Course" table to display the name of "instructor.
Iii. Query DesignQuery is one of the most frequently used objects in the database. We have introduced various queries of Access 2003, which can also be used in our student information database. The key to establishing a query is the design scenario. The so-called scenario design is to simulate the user's needs and propose a query request mode. Next, we will first review the general knowledge and general steps of selecting a query, and then design some scenarios to create corresponding query examples.
1.
ExpressionAn expression is one or more operations controlled by operators. In Access 2003, you can use expressions to complete various tasks. In SQL statements, queries, and filters, you can use expressions to set attributes, create conditions, and define functions. The system automatically calculates the expression result every time an expression is used. If the expression is in a form or report, access recalculates the expression result every time a form or report is refreshed. If the expression is used as a condition in the query, the system calculates the value of the expression each time the query is executed. If an expression is used as a validity rule in table design, the system performs this calculation every time a field value is entered. The following is an example of several expressions: = [Customer First name] & "& [customer last name] -- connection between customer last name and name = [total amount]-([total amount] * [discount]) <25 -- the discount amount is less than 25 [deceased] = yes -- late [animal type] = "cat" and [gender] = "M" -- male cat [Date of Birth] between 1/91 and 12/93 -- from January to December, expressions are generally composed of operators, object names, functions, literal values, and constant equivalents. You can create an expression in the expression input box.
2.
Input field conditions(1) Input Single value field condition single value condition input simply put an expression in a field. The expression can be sample data or a function. For example, input character (text or remarks) conditions, use the like operator and wildcard, specify unmatched values, and enter Numerical Conditions. (2) Input Multiple conditions in a field using and, Or, between... and... to specify multiple conditions for a field. In addition, there are also the in operator and null value lookup. (3) You can enter conditions in multiple fields by setting the [condition] and [or] Columns of multiple fields in the query designer.
3.
Create a total queryTo create a total query, you must first create a SELECT query, and then activate the "Total:" line in the query design window. You can perform the following operations: View, total, toolbar, and total. (1) Total of all records (2) Total of group records a field can be specified as a group field in the query design window. The system divides the same records in this field into the same group, and calculate the total number of groups. Total of a single group and multiple groups (3) specify the conditions for a total query
4.
Create a cross tabulation Query
Iv. Report Design
1.
Create query reportA report can be seen as a way to view data records in one or more tables. This concept is the basis of all reports. The preceding section describes the report output of a single table. You can use the query report to collect the expected data from multiple tables. In this case, you must select query or table to create a report and display the fields in the report. Unless you want to view all records of a single table in the report, you must bind the Report to the query. It can be said that if you want to access data in multiple tables to generate a report, the only way is to bind the report and query together. (1) Create query (2) create a report and bind it to a query (3) design query report (4) beautify query report
2.
Create a quota GroupIf the value of a field is numeric, you can use the period group control to create a report. Other controls can also be designed and created based on their features to generate a variety of reports.
3.
Create multi-column reportsAccess 2003 also provides a report called "Multi-column Report", which can define each part of the Report and print multiple columns of data on a single page. It is created in a page Setting dialog box.
V. Form Design
6. Complete SystemFrom the previous situation, it seems that the system is not enough. Why? In fact, we lack the function module design for the system in this example. Next we will analyze what functional modules the system will have. As for the implementation of the modules, some of them can be designed immediately, some of them may be better completed after the subsequent chapters are learned. The main function modules of the system mainly include the following: ◆ student information database data entry module this module mainly includes the following sub-modules: student Information entry, instructor information entry, course entry, score entry, class entry, dormitory entry, etc. ◆ Student information database data modification, deletion, and editing modules this module includes functional sub-modules for modifying, deleting, and Editing data in the database. ◆ Student information database data query module this module includes data query submodules related to student information: student query, instructor query, class query, course query, score query, dormitory query. ◆ Student information database data statistics module this module includes the following submodules: basic student information statistics and student achievement statistics. ◆ Student information database data printing module this module mainly includes the following sub-modules: basic student information printing and student achievement printing. ◆ Data database management. Database sorting, compression, backup, and restoration are important. ◆ Help information and information about the system. Provides help documentation. Provide version information, copyright information, and help contact information. To this end, we have created the following switching panel:
