0 0 0 0

Database knowledge

Database system dbs is a system that implements the organization, dynamically stores large amounts of associated data, and makes it easy for users to access software, hardware and data resources.

A typical database system includes databases, hardware, software ( applicatio ), and database administrato (DBA)4Part of it.

According to Computer architecture,DBSDivided into centralized, customers./Service type, parallel, distributed.


In the design of database logic, we convert e R model into relational model. For 3 different entity sets and multiple pairs between them, m: n: p, the minimum can be converted to 4 relational patterns.

Analysis: the rules for converting e R models into relational models are as follows

A real shape is converted to a relational pattern, and the entity attribute is the relationship attribute, and the entity code is the relationship code.

- a 1: 1 contact can be converted to a separate relational schema or combined with any one end of the relational schema.

- a 1: n can be converted to a separate relational schema, or can be combined with an arbitrary n relationship pattern. If converted to a separate pattern, the code for each entity connected to it, as well as the properties of the contact itself are converted to the attributes of the relationship, and the code of the entity. If you merge with the entity, you need to add the code for the 1 relational schema and the properties of the contact itself in the

- an m: n relation is converted to a separate relationship pattern, and the code for each entity connected to it, as well as the properties of the contact itself are converted to the attributes of the relationship, and the relation codes are combined with the combination of each entity code.

A relationship between 3 entities can be converted to a separate relational model, and the codes of each entity connected to the connection are.

Data slice

In the vertical slice of distributed database, the necessary condition of slice satisfaction is to ensure the reconfigurable and minimum redundancy of global data. for any slice, there's always another slice that can be connected to a connection.

Analysis: data slice classification as follows

- Horizontal slice depending on a certain condition, the relationship is divided into several subsets ( horizontal direction ), each of which is a fragment of the relationship.

- Vertical slice divide a relatio & hip into several subsets. So, so, In order to ensure the reconfigurable and minimum redundancy of global data, the necessary requirement of slice satisfaction is for any slice, and there's another .

- Export slice To guide horizontal slices, that's, the condition of the horizontal slice isn't the condition of the property but other relationships.

- Mixed slices Is a fragment of the above 3 slices, and continue to slice in another way.

Principle of database system

Database system3Level mode: external mode, mode, internal mode.

Network model, hierarchical model, relational model is a common data model.

Data model is the core and foundation of database system Is a set of concepts defined strictly. These concepts describe the characteristics of the system accurately. Dynamic and integrity co & traints. So .Data model is byData structureData manipulation and integrity constraints3Elements composition.

Entity Is an objective and can be distinguished from each other. Contact Refers to the relationship between the attributes of the entity. Properties It's a characteristic of an entity, which is a few basic concepts involved in conceptual models.

View view

To implement a view by rebuilding the view

A Data logic independence B data physical independence c program logic independence d program physical independence

Analysis: in the 3 mode of database system, the view corresponds to the external mode. External mode is the logical structure and feature of the local data that database users can see and use. The reconstruction of the view is equivalent. Therefore, reconstruction view can realize the logical independence.

Relational database

Relational databases are collections of tables whose structure is defined by relationships.

Analysis: The relationship model is one of the most commonly used data models. Relational database system uses relational model as the organization of data, and uses table structure to express entity set in relation model, and the relationship Its maximum feature is the consistency.

In relation model, the relationship between entities and entities is expressed in relation to relationships. The relational database is also and. The type of relational database is a relational database schema, which is a description of relational databases, and is a collection of relational schemas. The value of a relational database is also known as a relational database, a collection of relationships. Relational database schemas and relational databases often collectively referred to as relational databases.

Relationship mode/Relational operation P237 ~ p241

For queries, to determine which query is the highest efficiency, the least time is used when getting the same result. That's, how do I get the least steps?

If the relationship mode R ( a, b, c ) and s ( c, d, e ) have the following relation algebra:

Analysis: in the given 4 options, the meaning is the ad column for b <'2013 'and e = 80, so e1 = e2 = e3 = e4.

In e1vs e2, the operation mode is similar, but since R and s modes are selected first, R * s gets fewer results, thus the efficiency is higher than e1.

E3vs e4: the operation is similar, but in e3, because R and s modes are selected first, the R and s connectio get fewer results, and this e3 efficiency is higher than e4.

E3vs e2: similar, sequential similar. But different is R * s and R natural connection s. According to the definition, natural connection to cancel the repeat column, in this question, we should be able to cancel a c column after the R.

Operations in relational databases have projection, selection, connection, division, and external connection They're the most basic operations of relational algebra, as well as a complete set of operations. In relation algebra, a finite compound equation of 5 basic algebraic operations is called relational algebra expression expression expression. The expression of expression is still a relationship, which can be expressed by relational algebraic expressions to represent various data query and update processing operations.

The first item, selected and projected in the data operation will affect the result, because the expression on the right side of the equation does a projection, if the order in the right of the data will affect the result, if the expression on the right side of the equation does a projection, there's no effect on the condition limit if the expression in the right side of the f.

Second, the result isn't necessarily correct because there are no attributes involved in the conditional operation. Assuming that both e1 and e2 have only 2 attributes, there are 4 attributes; if f is 2 <3, it cannot be reflected in the right.

Third, select the two relations, select a tuple of conditions, and then cut. And first cut, then make a choice in the difference, the result is consistent.

The fourth item, because it's natural connection, in the right formula, the projection, must be removed from the public attribute, the connection.

When the two relationship R and s are natural connected, select the equal tuples on the common attributes of the two relations R and s, remove the duplicate. In this way, some tuples in relation R may have a tuple that doesn't exist in the relationship s, and the value of these tuples in relation R is discarded. Some tuples in the same relationship s may also discard.

Left outer connection: when the natural connection of R and s is made, only the tuples that are discarded in R are put into the new relationship.

connection: when the natural connection of R and s is connected, only tuples discarded in s are put into the new relationship.

Fully external connection: when a natural connection to R and s is made, the tuples that are discarded in R and s are put into the new relationship.

Data inconsistent

The main reason that the concurrent operation caused the inconsistent data is the isolation of things. In order to avoid inconsistencies, concurrent operations must be scheduled in the correct way, which makes the execution of one thing independent of other things.

Eg: sell 2 tickets separately from 2 sales points, but the remaining votes in the database are only 2 sheets, resulting in inconsistent data. You can solve this problem by locking.

Database dump

Categories have massive dump and incremental dumps, and each can be done in two states, divided into 4 classes:

- Dynamic mass dump, database runtime dump all databases .

- dynamic incremental dump, database runtime dump updated data after last dump.

-Static mass dump, database stops at runtime dump all databases.

- static incremental dump, the database stops at runtime dump the updated data after the last dump.

Function dependency

Relationship modeRProperty set% 7ba, b, c. %.Function dependency setF = % 7bab-> c, ac-> b, b-> c %then,RBelongs to3NF.

Analysis: for a given relationship R ( u, f ), u = % 7ba1, a2. An %, f is a function dependency set of R, which can be divided into 4 classes.

L class: only functio & that appear on the f are dependent on the left property.

R class: only functions that appear in f depend on the right property.

Lr class: functions that don't appear on both sides of the function of f.

A nlr class: functions that appear on both sides of the function depend on the properties that appear on both sides.

The judgment method is as follows:

If x is a class of, then x is a member of any candidate code of R, then x is a unique candidate code.

If x is a R attribute, x isn't in any candidate code for R.

If x is a nlr class attribute, then x is a candidate for R.

If x is an attribute set of the l class and nlr class attributes, then x is the unique candidate for R.

Relationship mode

An entity is converted to a relational schema, and the attributes of the entity are attributes of the relationship. The properties of the entity are attributes of the relationship. The code of the entity is the code of the relationship. A link is converted to a relational schema that's linked to the code of each entity connected to it and the attributes of the connection to the attribute of the relationship, which has 3 cases.

If the connection is 1: 1, the code for each entity is the candidate code for that relationship.

If the connection is 1: you, the code of the code n entity, the external code of the relationship is 1.

If it's connected to m: n, the code of the entity code of the relation is the code of each entity.

Data mining

Objective: to find potential rules from known large amounts of data.

Data mining is referred to as simply, in general, from a huge database to refine the interest of external code, or to refine the relationship that isn't easy to observe or judge, and finally give a useful and useful conclusion; It's simple to find the relationships between patterns, knowledge, or data.


Sql is a tool for organizing, managing, and retrieving data in a database that's a high-level language for relational databases. The sql language features are as follows:

- easy to use.


- the language that handles the collection

- unified language

- general language


Q: create a data type email in database table library whose system data type is a character with length 30 and not allowed.

Analysis: 7d % 7btype %, [, system_data_type ] [, 'null_type ']where,typeIs a data type name,System_data_typeIs the data type provided by the systemNull_typeIndicates how the data type handles null values, and must be enclosed in single quotation marks. .

USE Library 
sp_addtype Email,'vacchar(30)','NOT NULL' 


Q: inquiry supplier s supplier in a sales information table t sale is"hs company", transport expense s tra fer is less than 10 and sales of goods name

S title contains sales goods information for the"hp"character.

Analysis: In onewhereIn clause, more than one can be used at the same timeandStatement to join multiple query conditio &. At this point, all query condition records must be satisfied to query for the required data.

select * from t_sale 
where s_supplier='HS公司' 
and s_trans<10 
and s_title like '%HP%'


QuseSQLStatement query book information tableT_bookinfoBook number inB_idBook nameB_nameName, publisher nameB_publish,Date of publicationB_date,AuthorB_authorAnd book priceB_valueField, and the author is""Or or"Lie four"Book information.

WHERE B_AUTHOR IN('张三','李四')


问题:To employee information tableT_employAdd a jobE_dutyColumn, and useNOT NULLCo & traints.

ALTER TABLE t_employ 

Create a view

Q: create a view view empinfo so that the employee name e, work number e nums and department e information in the employee information table t employ are displayed in this view, as well as the salary information for each month in the staff tab t salary, and other information in the data table doesn't display.

Analysis: A view is a table exported from one or more tables or views, whose structure and data are based on the query on the table. Like the real table, views include multiple defined data columns and multiple rows of data, but in essence these data columns and data rows are referenced from the tables The creation of the view is primarily implemented by the create view keyword. Create a visual structure as follows:

CREATE VIEW view_name[(column1,column2,......)] 

View_name: create view name.
A [ ( column, column. ) ]: Optional, default field name in child query results.

CheckOption: ensure that only data can be inserted, updated, or deleted by a view. This clause can only be used to update views on base tables.

Cascaded: performs option checking for the current view and all view only, applies to nested views.

Local: this option performs an option check only on the current view and applies to nested views

create VIEW view_empInfo 
SELECT a.e_name,a.e_nums,a.e_depart,b.s_yf,b,s_jje 
FROM t_employ a,t_employ b 
where a.e_nums=b.s_gh

Added: in sql server database, the views you can create include Standard view, indexed views, and partitioned views .

Standard view Combine data in one or more tables Mainly used to enhance data and simplify operations . Strengthening data refers to a user who can focus on specific data that they're interested in. Unnecessary data or sensitive data can't appear in the view. A simplified operation means that you can connect frequently.UNIONQuery and querySELECTQuery is defined as a view To enable use & to specify all conditio & and conditio & for each time an additional operation is performed for each data.

Indexed view a view that's materialized, that's, it has been calculated and stored. You can create indexes for views that create a unique clustered index on the view. Indexed views can improve performance of certain types of queries, especially for queries that aggregate many rows, but aren't too suitable for.

Partition view in one A table or server horizontally connects the partition data in a group of group membe & In this way, the data looks like a table that connects the view of a member table in the same instance of sql server as a local partition view.

Add primary key co & traints

Q: assume that the hotel information table roominfo in the hotel management system already exists in the database, add a primary key constraint to the roomid column in the information table.

Analysis: primary key co & traints can't only be created while creating tables, but also when modifying data tables. Null values aren't allowed in colum & set to the primary key co & traint.


Query statement

Qquery employee information tableT_employeeinfoName is the nameMARKEmployee information.


Analysis: the where clause gets the result set returned by the from clause and uses the where clause to filter the result set, and the data that matches the search.

Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs