Basic Database Theory and relational database theory

Basic Database Theory and relational database theory
Reprint please indicate the source: http://blog.csdn.net/u012637501
I. Basic Concepts1. Data:It refers to the symbolic record describing a thing. It has a certain structure and has the types and values, such as integer, real, and complex types.2. Database: DataBase (DB) is a data set that organizes and stores data in the memory according to a certain data model. It is a method to organize, store, and share data on a computer. The database has a high degree of data independence, that is, it does not rely on the specific database management soft.3. Database Management System: DataBase Management System (DBMS), a large service software used to manipulate and manage databases, not only enables us to quickly retrieve and maintain data, it also guarantees data security, integrity, concurrency control, and data recovery. The core of the database management system is a database used to store a large amount of data, and provides data definition language, data operation language, and data control language to complete the database management system functions. Database Management System Function: (1) data mode definition: Build a data framework for the database; (2) physical construction of data access: provides effective methods and means for the physical access and construction of data models (Data Definition Language implementation); (3) data manipulation: provides methods for users to use database data, such as query, insert, and modify (data operation language implementation); (4) data integrity, security definition and check (Data Control Language implementation); (5) database concurrency control and fault recovery; (6) data services, such as copy, transfer, reorganization, and analysis. Common Database Service software: Oracle, MySQL (Oracle Company), SQL Server, Access (Microsoft)4. Database System: A running entity consists of five parts: Database (data), database management system (software), Database Administrator (personnel), hardware platform (hardware), and software platform (software. Basic Features of the database system: data integration, highly shared domain redundancy, Data independence, and unified data management domain control.5. Relational databasesRelational DataBase Management System (RDBMS) is a Relational DataBase (Relational Data Base, RDB ). A relational database contains several relationships, each of which is determined by the relational mode. Each relational mode contains several attributes and their corresponding domains. Therefore, defining a relational database is to define the relational mode one by one, define attributes and their corresponding domains for each link mode. Relational databases store data in rows and columns for your understanding. These rows and columns are called tables. A group of tables form a database. Relational databases usually contain the following components: a) Client Applications (Client); B) Database servers; c) Databases)(1) Entity Relationship Model and E-R DiagramIn the database design stage, you need to create a logical model. The logical model of a relational database is called an entity-relational model. The most common tool for Entity modeling is the Entity Relationship diagram (Entity-Relationship), which is a simple graphic technology used to define the tables, fields and relationships in the database. The first step in database design is unrelated to the specific database management system we use. The conceptual model is an intermediate layer from the real world to the machine world. The most commonly used conceptual model is the E-R model, the main concepts in the E-R model:★Entity: objects that exist objectively and can be differentiated from each other are called entities, such as students and cars.★Attribute: an object has several characteristics, such as the student's student ID and name;★Domain: the Domain of an attribute;★Key: the attribute or minimum attribute group that uniquely identifies an object is called a Key. Multiple candidate codes may exist. The designer must specify a master code.★Connections: there are connections between things in the real world. in the information world, these connections are reflected in the connections between the physical and physical entities, there is a link between the two physical entities. ※one-to-one () For example: department and manager. ※one-to-many (1: n) for example: department or employee ※many-to-many (m: n) for example: students, courses
Example: E-R diagram of Student Entity
E-R diagram has the following advantages:. effectively collect and express the information requirements of the Organization; B. provides an easy-to-understand System Description diagram; c. easy to develop and refine; d. clearly defines the scope of information requirements; e. separate business requirement information from business activities.
(2) Basic concepts of the relational modelThe task of a data model is to describe entities and their relationships in the real world. The relational data model uses an ordered array to describe an object and its attributes. It uses this ordered set to describe an object set, and uses the relationship defined on two sets to reflect the relationship between different entities.★Link: A link is a two-dimensional table. Generally, a two-dimensional table without duplicate rows or columns is considered as a link. Each link has a link name;★Tuples: each row in the orders table is called the ancestor in the link, that is, the row, also known as "Record );★Attribute: each column in the tables Table is called an attribute in a link, that is, a column, or Field );★Domain: the value range of an attribute is called a Domain;★Key: a Key word in a link that uniquely identifies and determines properties or combinations of different tuples. Keywords composed of a single attribute are called single keywords, and keywords combined by multiple attributes are called combination keywords. Note: The attribute value of a keyword cannot be null ".★Candidate keywords: attributes or attribute combinations that can be called keywords in a link may not be unique. Any attribute or Attribute combination that uniquely identifies and determines different ancestor in a link is called a candidate keyword.★Primary Key: select a Primary Key in the Candidate keyword, called the link. The Primary Key is unique in the link.★Foreign keywords (Foreign Key): If an attribute or Attribute combination in a link is not a keyword, but is the primary keyword of another link, the combination of this attribute or attribute is an external keyword or Foreign Key of the Alibaba un series ). In relational databases, external keywords are used to indicate the relationship between two tables.
(3) Advantages of relational databasesA) easy to understand: the structure of a two-dimensional table is very close to the logic world. Relational Models are easier to understand than other models such as mesh and hierarchy. B) ease of use: the general SQL language makes it very convenient to operate relational databases. programmers can operate databases at the logic level without having to understand the underlying implementation. c) Easy Maintenance: the wide range of integrity (entity integrity, referential integrity, and user-defined integrity) greatly reduces the probability of data redundancy and data inconsistency.(4) integrity constraints of the relational modelA) entity Integrity: Object Integrity means that all the tuples in a relational model are unique and there are no two identical tuples, that is, no two identical rows in a two-dimensional table, it is also called row integrity. B) domain Integrity: domain integrity is the specification of the column data in the table, also known as column integrity, used to limit the column data type, format, and value range. C) integrity of reference: when a data table contains external keywords (that is, this column is a keyword of another table), all values of the external keyword column, must all appear in the corresponding table. D) User-Defined integrity

Conclusion 1: What are the relationships between tables, fields, and records? Fields are the properties of transactions, records are the transactions themselves, tables are the collection of transactions, and summary 2: What are the differences between data structures and databases? Databases are used to store and operate data at the application software level (external storage); data structures are used to store and operate data at the system software level (primary storage; Ii. Database Design Methods 1. Database Design Process◎ Requirement analysis ◎ conceptual design ◎ logical design ◎ physical design ◎ implementation and maintenance 2. E-R model and its transformation to Relational Model
(1) Conversion from an independent entity to a relational modelAn independent entity is converted into a relational model (a relational table), entity code is converted into key attributes of a relational table, and other attributes are converted into attributes of a relational table, determine the value range of The Link property based on the actual object property. For example, for the Student Entity shown in, it should be converted into a link: Student (student ID, name, nationality, nationality), where the attribute marked by underline represents a keyword.
(2) converting a contact to a relational modelManager (name, nationality, address, date of birth, phone number, name) Company (name, registration location, type, phone number, name) where, the name and name are the keywords of the Relationship Model "company" and "manager" respectively. In the relationship "manager" and "company", to indicate the relationship between the two, each adds the other's keywords as external keywords. When the following tuples appear in the two tables, Zhang Xiaohui is the manager of jingguang Industrial Company. (Zhang Xiaohui, Han, No. 156 Qianmen Street, 278, 68705633, Jing Guang Industrial Company) (Jing Guang Industrial Company, No. 65783265 Fuxingmenwai Street, Beijing, limited liability, Zhang Xiaohui)
(3) The conversion from 1: n to the relational model should be converted to 1: n.You need to add an attribute to the n-party (I .e., one-to-multiple-link multi-party) object table to process the other's keywords as external keywords. for example, the connection between the "class" and "student" is 1: n, and the student side is n, which converts Figure 2-5 to get the relational model: Student (student ID, name, national, date of birth, class number) Class (class number, name, grade, department, Major) Add the keyword "class" in the student table as the external keyword.
(4) m: the transformation from n to the relational modelA m: n link requires a separate relational mode, using the keywords of two entities as external keywords. The description of the student's contact with the course is m: n, the E-R map into three relationships: Student (student ID, name, nationality, date of birth) Course (course number, course name, number of hours) Learning (student ID, course number, score ).
(5) transforming a multi-correlation to a relational model the so-called multi-correlationThat is, the contact involves more than two entities. For example, a curriculum involves four entities: Class, course, teacher, and classroom. For example, a curriculum involves four entities: Class, course, teacher, and classroom. During conversion, a separate relational table should be created. the keywords of all entities involved in the link should be used as the external Keywords of the relational table, and other appropriate attributes should be added, the link mode is as follows: curriculum (course number, course number, instructor number, classroom number, weekly ).