Database Learning (1) -- Introduction to relational databases

Database Learning (1) -- Introduction to relational databases, including Oracle databases, MySQL databases, Microsoft SQL Server databases, and DB2 databases. The data model is an abstraction of real-world data features. A complete data model consists of three parts: data structure, data operations, and integrity constraints. The multi-level description of data models mainly includes conceptual models, logical data models, and physical data models. The conceptual model is the abstraction of real-world things based on the customer's views and ideas. The Logical Data Model refers to the data model in the database that the user sees and commonly uses the relational data model; the physical data model is used to represent the data storage structure. 1. Conceptual Model in the design of relational database, conceptual model is usually described by E-R diagram. E indicates the meaning of the object, and R indicates the meaning of the link. Therefore, a E-R graph is also called a solid-relationship diagram. In the E-R diagram, a link is used to indicate the interconnectivity between an object and an object. Links can be divided into one-to-one, one-to-many, and multiple-to-many types. One-to-one (): for example, the relationship between the principal and the school is one-to-many (1: n): for example, the relationship between the Department and the student. Many-to-many (n: m): for example, the relationship between the course and the instructor. 2. In the implementation of the relational data model in the database management system, the relational data model describes the relationship between objects and attributes in the form of a two-dimensional table. The tables in the relational data model are somewhat different from those in general two-dimensional tables. A. the relationship is a standardized two-dimensional table. Each attribute value cannot be further divided. B. Duplicate rows are not found in the tables Table of the relational data model. That is, the data in each row in the tables Table of the relational data model should be different. In a relational data model, commonly used relational operations include query, connection, intersection, merger, difference, data insertion, modification, and deletion. These operations must meet certain constraints on link integrity. Integrity constraints of a link include entity integrity, referential integrity, and user-defined integrity. 3. The link mode is used to describe the link. Relational databases have three relational modes: Conceptual mode, external mode, and internal mode. Schema: A logical or conceptual Schema that describes the logical structure of data in a database. The concept mode can be understood as the actual implementation of entities in the real world in the database system. For example, a two-dimensional data table corresponding to a relational logical structure can be understood as a schema in a relational database. The concept mode has nothing to do with environments such as applications and computer hardware. External Schema: an External Schema can also be called a user pattern, which is part of a conceptual pattern. Data that allows user operations is defined in the external mode. For example, the view that the user sees in the database can be understood as the external mode in the relational database. Of course, in relational databases, the external modes of different data are not identical due to different user requirements and data storage. Internal Schema: the Internal Schema is also called the storage Schema. It is used to describe the physical structure of data and the storage method of data. For example, the organization of indexes in a relational database and the storage of data records can be understood as the internal mode of a relational database. 4. Common relational databases a. Oracle Database Oracle database is a relational database system launched by Oracle. It is one of the mainstream database systems. As a large-scale database system, Oracle database provides a complete data management function, mainly used for large and medium-sized application systems, and C/S (Client/Server) and B/S (Browser/Server) servers. For systems with high data volume, high concurrency, and high real-time requirements, Oracle databases are generally used for server-side databases. B. Microsoft SQL Server database is a relational database system launched by Microsoft. It is also one of the mainstream widely used database systems. SQL Server database features high performance, scalability, advanced system management, support for WINDOWS Graphical management tools, and good transaction processing functions. C. MySQL database MySQL is a powerful, flexible, multi-user, and multi-thread SQL database management system developed by MySQL AB in Sweden. It provides a wide range of application interfaces and useful feature sets. It is a popular database server on the Internet. Many software developers and commercial users are also using MySQL databases.