SQL server quick basic database application system

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.

## Recommended study: "

SQL Tutorial"

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 (DBMS)

  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.
  

Some people may ask: My data can be stored in the excel table on my computer, why do I need to build a database?

Then This is because the database has more advantages than excel. Databases can store large amounts of data and allow many people to use the data inside at the same time. For example, you will understand that Excel is like a mobile hard disk. If you use this mobile hard disk, others cannot use it. A database is like a network disk. Many people can access the data in it at the same time, and a network disk can hold more data than a mobile hard disk.

1.1 A simple database application system

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
2. name n)
   

Code: The value of a field or the smallest combination of fields can uniquely identify its corresponding record. There can be multiple codes in a
3. 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.2 Database design

Relationship between tables:

Student table

Grade table

# For example, I want to know what the grade of the student number "0001" is? Then I can look for the row with the "student ID" value of 0001 in the grade table. Finally, I find that there are 3 rows of data in the grade table with student IDs all "0001", and correspondingly I find the three courses of the student. results. A relationship is a match that data can correspond to.

1.2.1 E-R model

The diagram used by E-R model to represent the relationship between entity sets is called entity-relationship model (E-R diagram).

Usually, the E-R model calls the individuals of each type of data objects "entities", and the collection of individuals of each type of objects is called "entity set". For example, in the library management system, it mainly involves "readers" "(also known as students) and "books" and other entity sets, non-main entity sets include classes, class teachers, etc. The information items involved in each entity set are called attributes.

 (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.

Exercise 1

 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.

==Exercise 2==

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.

1.2.2 Logical data model

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.

## 　

Exercise 2

: 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 Database application system

The structure of the database system is described as shown in Figure 1.5.

1.3.1 Data interface of application system

1. ODBC database interface

ODBC stands for Open DataBase Connectivity. It is an interface standard introduced by Microsoft to implement communication between applications and relational databases. Databases that meet this standard can operate the database through programs written in SQL statements, but only for relational databases. All current relational databases comply with this standard. ODBC is essentially a set of database access APIs (application programming interfaces), consisting of a set of function calls, with the core being SQL statements.

2. ADO database interface

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

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.

  ADO.NET provides an object-oriented database view and encapsulates many database properties and relationships in its objects. The most important thing is that it encapsulates and hides many details of database access in various ways. You can have no idea that the object is interacting with the ADO.NET object, and you don't have to worry about details such as moving data to another database or obtaining 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 (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.

5. Database connection pool technology

  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.

1.3.2 Application system architecture

1. Client/server architecture application 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.

 2. Browser/server architecture application system

 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.

Recommended study: "

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!