Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the knowledge points of the SQL server database application system". In the daily operation, I believe that many people have doubts about the knowledge points of the SQL server database application system. The editor consulted all kinds of materials and sorted out the simple and easy-to-use operation methods. I hope it will be helpful to answer the questions of "what are the knowledge points of the SQL server database application system?" Next, please follow the editor to study!

Database concept

Database

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.

Database management system (DataBase Management System, DBMS)

Manage databases and other systems, which organize a large amount of data according to a certain data model.

The function of storing, maintaining and retrieving data, so that the application system can be convenient in a unified way.

Get the required information from the database timely and accurately.

   some people may ask: my data can be stored in the excel table of my own computer, why do I need to set up a database?

   that's because databases have more advantages than excel. The database can store a large amount of data, allowing many people to use the data at the same time. For example, you can see that excel is like a removable hard drive. If you use this removable hard drive, no one else can use it. A database is like a network disk, in which many people can access the data at the same time, and the network disk can hold more data than the mobile hard disk.

1.1 A simple database application system

How does the database organize (store) data?

When developing a project,    must choose an appropriate database management system to organize the data according to a certain data model, and manage the data uniformly, so as to provide consistent access means for applications that need to use data. The data model mainly includes relational model, hierarchical model and mesh model. At present, relational models are commonly used.

The    relational model organizes the 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 time.

A row in a table is called a record, 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)

Code: the value of a field or minimum combination of fields can uniquely identify its corresponding record. One

There can be multiple codes in a table. Generally, one code is designated as the master code, and an underscore table is used in the relational table.

Show. For example, student form xs (library card number, name, gender, time of birth, major, borrowing books

Quantity).

1.2 Database design

Relationships between tables:

Student table

Score sheet

   for example, I would like to know what the student's score is "0001". Then I can look for rows with a "student number" value of 0001 in the grade sheet, and finally find that the student numbers with three lines of data are all "0001", corresponding to the student's grades in three courses. A relationship is a match that the data can correspond to.

1.2.1 Emurr model

The graph used to represent the relationship between the entity set and the entity set is called the entity (entity)-relation (relationship) model (   Emurr graph).

In general, the    model calls the individuals of each type of data objects "entities", and the set of individuals of each type of objects is called "entity sets". For example, in the library management system, it mainly involves entity sets such as "readers" (also known as students) and "books", while non-main entity sets include classes, head teachers and so on. The information items involved in each entity set are called attributes.

   (1) entity set representation in Emurr graph, rectangle is used to represent entity set, ellipse is used to represent attributes, and diamond is used to represent linkage. Connect the entity set with the attribute with a line segment, and mark a slash on the connecting line between the entity set and the attribute when an attribute or attribute combination is specified as the master code.

(2) there are various relationships between entity sets.

   1. One-to-one connection (1: 1) one entity in An is associated with at most one entity in B, and one entity in B is associated with at most one entity in A. For example, the relationship between the two entity sets of "class" and "head teacher" is one-to-one, because there is only one head teacher in a class, in turn, a head teacher belongs to only one class. The Emurr model of the two entity sets of "class" and "head teacher" is shown in figure 1.2.

   2. One-to-many connection (1: n) one entity in A can be associated with multiple entities in B, while one entity in B can be associated with at most one entity in A. For example, the relationship between the two entity sets of "class" and "student" (reader) is an one-to-many relationship, because a class can have several students, and in turn, a student can only belong to one class. The Emurr model of the two entity sets of "class" and "student" is shown in figure 1.3.

   3. Many-to-many connections (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 of "readers" (students) and "books" is many-to-many, because one reader can borrow more than one book, and in turn, a book with one book number can be borrowed by multiple students. The Emurr model of the two entity sets of "reader" and "book" is shown in figure 1.4.

Exercise 1

   has two entities: store and customer. "store" has attributes: store number, store name, address, telephone call, and "customer" has attributes: customer number, name, address, age, gender. Suppose the customer has a consumption amount and date every time he goes to the store. Try to draw the Emurr diagram, and indicate the attribute and contact type.

= = exercise 2 =

   assumes that each student takes several courses, and each student has only one grade for each course, each teacher only teaches one course, and a course is taught by several teachers. "student" has attributes: student number, name, address, age, gender. "teacher" has attributes: employee number, teacher's name, professional title, "course" has attributes: course number, course name. Try to draw an ER diagram and indicate the attribute and contact type.

1.2.2 logical data model

The following is the way to get the relational schema from the    R diagram based on three connections.

   1. (1: 1) Transformation from Emurr diagrams of connections to relational schemas

a. Each entity set corresponds to a relational schema.

b. For the connection of (1: 1), it can correspond to either a relation pattern alone or not.

   (1) the relation corresponds to a relation pattern separately, then the relation pattern is composed of the connection attribute and the main code attribute of each entity set participating in the association, and the main code can choose the main code of either party of the entity set participating in the association.

   for example, considering that the entity set of "bj" and "bzr" described in figure 1.2 contacts the Emurr model by belonging to (sy), the following relational schema can be designed (the lower underline indicates that the field is the main code):

Bj (class number, department, major, number)

Bzr (employee number, name, contact number)

Sy (employee number, class number)

   (2) the relation does not correspond to a relation pattern alone, and the attribute of the connection and the master code of one party are added to the relation pattern corresponding to the entity set of the other party.

   for example, considering the entity set of "bj" and "bzb" described in figure 1.2, contacting the Emurr model by belonging to (sy), the following relational schema can be designed:

Bj (class number, department, major, number)

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) Transformation from Emurr diagrams of connections to relational schemas

a. Each entity set corresponds to a relational schema.

b. For the connection of (1: n), it can either correspond to a relation pattern alone or not.

Relationship model.

   (1) the relation corresponds to a relation pattern separately, then the relation pattern is composed of the attribute of the relation and the master code attribute of each entity set participating in the connection, and the master code on the n side is the main code of the relation pattern.

   for example, considering the Emurr model of the entity set of "bj" and "xs" described in figure 1.3, the following relational schema can be designed:

Bj (class number, department, major, number)

Xs (library card number, name, gender, time of birth, major, number of books borrowed)

Sy (library card number, class number)

   (2) if the relation does not correspond to a relation schema alone, the attribute of the connection and the master code of the 1-end are added to the n-terminal real.

In the relational schema corresponding to the volume set, the master code is still the main code on the n side.

   for example, the Emurr model of the entity set of "bj" and "xs" depicted in figure 1.3 can be designed as follows:

Bj (class number, department, major, number)

Xs (library card number, name, gender, time of birth, major, number of books borrowed, class number)

   3. (M: n) Transformation from Emurr diagrams of connections to relational schemas

a. Each entity set corresponds to a relational schema.

b. For the connection of (m: n), it separately corresponds to a relation schema, which includes the attribute of the connection and the master code attribute of each entity set participating in the connection, and the master code of the relation pattern is composed of the master code attribute of each entity set.

  , for example, the borrowing link between the entity set of "xs" and "book" depicted in figure 1.4

Jy can design the following relational patterns:

Xs (library card number, name, gender, time of birth, major, number of books borrowed)

Book (ISBN, title, translator, publisher, price, copy quantity, inventory, synopsis)

Jy (library card number, ISBN, call number, borrowing time)

The master code of the relational schema jy is composed of two attributes: "library card number" and "ISBN".

A relational schema can only have one master code.

   exercise 1: there are two entities: store and customer. "store" has attributes: store number, store name, address, telephone call, and "customer" has attributes: customer number, name, address, age, gender. Suppose the customer has a consumption amount and date every time he goes to the store. Draw the Emurr diagram and transform it into a relational model.

   exercise 2: assume that each student takes several courses, and each student has only one grade for each course, each teacher teaches only one course, and a course is taught by several teachers. "student" has attributes: student number, name, address, age, gender. "teacher" has attributes: employee number, teacher name, professional title, "course" has attributes: course number, course name. Try to draw the ER diagram and transform the Emure R diagram into a relational model.

1.3 Database application system

   describes the composition of the database system as shown in figure 1.5.

1.3.1 data interface of application system

1.ODBC database interface

   ODBC, namely Open Database Interconnection (Open DataBase Connectivity), is an interface standard developed by Microsoft to realize the communication between applications and relational databases. The database that meets this standard can operate on the database through the program written by SQL statement, but only for the relational database. At present, all relational databases meet this standard. ODBC is essentially a set of database access API (application programming interface), composed of a set of function calls, the core of which is SQL statements.

2.ADO database interface

   ADO (ActiveX Data Object) is a database application program interface based on COM developed by Microsoft. By connecting to the database through ADO, you can manipulate the data in the database flexibly. There are two ways to access a relational database using ADO: one is through the ODBC driver, and the other is through the database-specific OLE DB Provider, which has higher access efficiency.

3.ADO.NET database interface

The    ADO.NET data model is developed from ADO, but it is not only an improvement on ADO, but also uses the

A whole new technology. It is mainly reflected in the following aspects:

(1) ADO.NET is not a product of ActiveX technology, but a product of close integration with .NET Framework.

(2) ADO.NET includes full support for the XML standard, which is of great significance for data exchange across platforms.

(3) ADO.NET can work not only in the environment connected to the data source, but also in the condition of disconnecting from the data source. In particular, the latter is very suitable for the needs of network applications, because in the network environment, always maintaining a connection with the data source does not meet the requirements of the website, which is not only inefficient and costly, but also often leads to conflicts caused by multiple users visiting at the same time.

   ADO.NET provides an object-oriented view of the database and encapsulates many database properties and relationships in its objects. Most importantly, it encapsulates and hides many details of database access in a variety of ways. You can have no idea that objects are interacting with ADO.NET objects, and you don't have to worry about details such as moving data to another database or getting data from another database. Figure 1.6 shows the interface model for accessing the database through ADO.NET.

4.JDBC database interface

   JDBC (Java DataBase Connectivity) is developed by JavaSoft (formerly the business unit of SUN company). It is a class and interface for database connection and operation written by Java language, which can provide a unified access mode for a variety of relational databases. Access to the database through JDBC includes four main components: the Java application, the JDBC driver manager, the driver, and the data source.

Using the JDBC interface to manipulate the database has the following advantages:

(1) JDBC API is very similar to ODBC, which is convenient for users to understand.

(2) to free programmers from complex driver calls of commands and functions and devote themselves to application

The realization of program function

(3) JDBC supports different relational databases, which enhances the portability of the program.

5. Database connection pool technology

   for database applications in the network environment, due to the large number of users, using the traditional JDBC way to connect to the database, the excessive cost of system resources has become a bottleneck restricting the efficiency of large-scale enterprise applications. Using database connection pool technology to manage database connections can greatly improve the efficiency and stability of the system.

1.3.2 Application system Architecture

1. Application system based on client / Server Architecture

   DBMS manipulates 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 to develop database interface are Visual Basic, Visual C++, Visual C# and so on. The relationship between the application and the database and database management system is shown in the figure.

two。 Application system of browser / Server Architecture

The    Web-based database application uses a three-tier (browser / Web server / database server) model, also known as the BPX S architecture, as shown in the figure.

At this point, the study of "what are the knowledge points of SQL server database application system" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.