This article brings you relevant knowledge about SQL server, which mainly introduces related issues about database basics, including an introduction to simple database application systems and database design. The following is Let's take a look, hope it helps everyone.
SQL Tutorial"
Data organized according to a certain model is called a database (DataBase, DB). The database is the core and foundation of
transaction processing, information management and other application systems.
manages databases and other systems. It organizes a large amount of data according to a certain data model and provides
storage, The functions of maintaining and retrieving data enable the application system to obtain the required information from the database conveniently,
timely and accurately in a unified manner.
How does the database organize (store) data?
During project development, a suitable database management system must be selected to organize the data according to a certain data model, conduct unified management of the data, and provide consistent access means for applications that need to use the data. . Data models mainly include relational models, hierarchical models, and network models. Currently, the relational model is more commonly used. The relational model organizes data in the database in the form of two-dimensional tables (relational tables). For example: Table 1.1 describes the student data in the student book lending system at a certain moment.
A row in the table is called For records, a column is called a field, and the title is the field name.
Relational table structure representation: Relational table name (field name 1, field name 2...field
- name n)
1.2 Database designRelationship between tables: Student tableCode: The value of a field or the smallest combination of fields can uniquely identify its corresponding record. There can be multiple codes in a
- table. Generally, one code is designated as the main code, which is represented by an underline
in the relationship table. For example, student table xs (library card number, name, gender, date of birth, major, number of borrowed books
).
(1) Entity set representation In the E-R diagram, rectangles are used to represent entity sets, ellipses are used to represent attributes, and diamonds are used to represent relationships. Use a line segment to connect the entity set and attributes. When an attribute or attribute combination is designated as the main code, mark a slash on the line connecting the entity set and attributes.
(2) Various relationships exist between entity sets
1. One-to-one relationship (1:1) An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. For example, the relationship between the two entity sets "class" and "class teacher" is a one-to-one relationship, because a class has only one class teacher, and conversely, a class teacher only belongs to one class. The E-R model of the two entity sets "class" and "class teacher" is shown in Figure 1.2.
2. One-to-many relationship (1:n) An entity in A can be associated with multiple entities in B, and an entity in B is associated with at most one entity in A. For example, the relationship between the two entity sets "class" and "student" (reader) is a one-to-many relationship, because a class can have several students, and conversely, a student can only belong to one class. The E-R model of the two entity sets "class" and "student" is shown in Figure 1.3.
3． Many-to-many relationship (m : n) An entity in A can be associated with multiple entities in B, and an entity in B can also be associated with multiple entities in A. For example, the relationship between the two entity sets "readers" (students) and "books" is a many-to-many relationship, because a reader can borrow multiple books, and conversely, a book with a book number can be borrowed by multiple students. . The E-R model of the two entity sets "reader" and "book" is shown in Figure 1.4.
There are two entities: store and customer. "Store" has attributes: store number, store name, address, phone number, "customer" "There are attributes: customer number, name, address, age, gender. Assume that every time a customer goes to the store to shop, he has a consumption amount and a date. Try to draw an E-R diagram and indicate the attributes and contact types. 
Suppose each student takes several courses, and each student only has one grade for each course, and each teacher only Responsible for teaching one course, and one course is taught by several teachers. "Student" has attributes: student number, name, address, age, and gender. "Teacher" has attributes: employee number, teacher name, and professional title, and "course" has attributes: course number, course name. Try to draw an ER diagram and indicate the attributes and contact types.
The following is a method to obtain the relationship pattern from the E-R diagram based on three connections.
1. (1 : 1) Conversion of the associated E-R diagram to the relationship schema
a. Each entity set corresponds to a relationship schema;
b. For (1 : 1 ) may correspond to a relationship model alone, or may not correspond to a relationship model alone.
(1) A relationship alone corresponds to a relationship model, and the relationship model is composed of the contact attributes and the primary key attributes of each entity set participating in the contact. The primary code can be the primary code of any party in the entity set participating in the contact.
For example, consider the "class (bj)" and "class teacher (bzr)" entity sets described in Figure 1.2 to contact the E-R model through belonging (sy). The following relationship pattern can be designed (the underline indicates that the field is the main code) :
bj (class number, department, major, number of people)
bzr (employee number, name, contact number)
sy (employee number, class number)
(2) Contact does not correspond individually In a relationship schema, the attributes of the relationship and the primary key of one party are added to the relationship schema corresponding to the entity set of the other party.
For example, consider the entity set of "Class (bj)" and "Class Teacher (bzb)" described in Figure 1.2 to contact the E-R model through belonging (sy). The following relationship model can be designed:
bj (class number, department , major, number of people)
bzr (employee number, name, contact number, class number)
or:
bj (class number, department, major, number of people, employee number)
bzr (employee Number, name, contact number)
2. (1: n) Conversion of E-R diagram to relationship schema
ˆ ˆ a. Each entity set corresponds to a relationship schema;
ˆ ˆ b. For (1: n) contact, it can correspond to a separate relationship schema , it does not need to correspond to a
relationship pattern alone.
(1) A relationship alone corresponds to a relationship pattern, and the relationship pattern is composed of the attributes of the association and the primary key attributes of each entity set participating in the association. The primary key at the n end is used as the primary key of the relationship pattern.
For example, considering the E-R model of the "class (bj)" and "student (xs)" entity sets described in Figure 1.3, the following relationship model can be designed:
bj (class number, department, major, number of people)
xs (library card number, name, gender, birth time, major, number of books borrowed)
sy (library card number, class number)
(2) The relationship does not correspond to a separate relationship model, then Add the attributes of the relationship and the primary code at the 1 end to the relationship pattern corresponding to the real
entity set at the n end, and the primary code is still the primary code at the n end.
For example, the E-R model of the "class (bj)" and "reader (xs)" entity sets described in Figure 1.3 can be designed with the following relationship pattern:
bj (class number, department, major, number of people)
xs (library card number, name, gender, date of birth, major, number of books borrowed, class number)
3． Conversion of E-R diagram of (m : n) contact to relationship schema
ˆ ˆ a. Each entity set corresponds to a relationship schema;
ˆ ˆ b. For (m : n) contact, there is a separate relationship schema, which The relationship model includes the attributes of the relationship and the primary key attributes of each entity set participating in the relationship. The primary key of the relationship model is composed of the primary key attributes of each entity set.
For example, the borrowing relationship (jy) between the "reader (xs)" and "book (book)" entity set described in Figure 1.4 can be designed as follows:
xs (library card) Number, name, gender, date of birth, major, number of books borrowed)
book (ISBN, book title, translator, publisher, price, number of copies, inventory, summary)
jy (borrowed Library card number, ISBN, call number, borrowing time)
The main key of the relationship model jy is a
primary key and a relationship composed of the two attributes "library card number" and "ISBN" A pattern can only have one primary code.
Exercise 1: There are two entities: store and customer. "Store" has attributes: store number, store name, address, phone number. "Customer" has attributes: customer Number, name, address, age, gender. Assume that every time a customer goes to the store to shop, he has a consumption amount and a date. Draw an E-R diagram and convert it into a relational model.
##
: Assume that each student takes several courses, and each student only has one grade for each course, and each teacher only Responsible for teaching one course, and one course is taught by several teachers. "Student" has attributes: student number, name, address, age, and gender. "Teacher" has attributes: employee number, teacher name, and professional title, and "course" has attributes: course number, course name. Try to draw an ER diagram and convert the E-R diagram into a relational model.
1.3.1 Data interface of application system
2. ADO database interface
3. ADO.NET Database Interface
The ADO.NET data model is developed from ADO, but it is not just an improvement on ADO, but uses a brand new technology. Mainly reflected in the following aspects:
(1) ADO.NET does not use ActiveX technology, but is a product closely integrated with the .NET framework.
(2) ADO.NET includes full support for the XML standard, which is of great significance for cross-platform data exchange.
(3) ADO.NET can work both in an environment connected to the data source and in an environment disconnected from the data source. In particular, the latter is very suitable for the needs of network applications, because in a network environment, always maintaining a connection with the data source does not meet the requirements of the website. It is not only inefficient and costly, but also often causes problems due to multiple users simultaneously. Conflicts caused by access.
JDBC (Java DataBase Connectivity) is developed by JavaSoft (the original business department of SUN company). It is a class and interface written in Java language for database connection and operation. It can provide unified access to a variety of relational databases. Way. Access to the database through JDBC includes 4 main components: Java application, JDBC driver manager, driver and data source.
Using the JDBC interface to operate the database has the following advantages:
(1) The JDBC API is very similar to ODBC, which is helpful for users to understand;
(2) It frees programmers from complex driver calling commands and functions , and is dedicated to the implementation of application
program functions;
(3) JDBC supports different relational databases, enhancing the portability of the program.
For database applications in network environments, due to the large number of users, the traditional JDBC method is used for database connections. Excessive system resource overhead has become a bottleneck restricting the efficiency of large enterprise-level applications. Database connection pool technology is used to Connection management can greatly improve the efficiency and stability of the system.
DBMS operates the database through commands and an interface suitable for professionals. For general database application systems, in addition to DBMS, it is also necessary to design an interface suitable for ordinary people to operate the database. At present, the popular tools for developing database interfaces mainly include Visual Basic, Visual C, Visual C#, etc. The relationship between the application, the database, and the database management system is shown in the figure.
Web-based database applications adopt a three-tier (browser/Web server/database server) model, also known as B/S architecture, as shown in the figure. 
SQL Tutorial"
The above is the detailed content of SQL server quick basic database application system. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
