Introduction to the Database system review notes.

The university textbook Review notes, the knowledge point summarizes. 2012-08-15 data model type, type, study number, name .... values, value, 0001, Ender ..... The three-level mode of the database system, level two mapping, external mode, External schema/sub schema, is the direct use that the user can see. A database has multiple external schemas that apply to different needs of the user. However, only one external mode can be used per application. Ensure safety. Each user can only access the corresponding outside mode. schema, schema,is a description of the logical structure and characteristics of all data in the database, only the description of type type. A specific value of the pattern, called the instance instance. Is the public view of the data for all users. Hardware physical storage is not involved and is not relevant to the specific user's application. There is only one schema for a database. Is the view of the database at the logical level. internal mode, Internal schema/storage schema,A database has only one internal schema, which is a description of how the data is physically stored.  Whether compression, encryption, how to index ..... 2012-08-17 integrity, integrity,1. Entity Integrity primary key cannot be empty. Null values are non-marked and are not allowed. 2. Referential integrity, referential integrity, the student's professional number must exist in the professional professional number inside.   The student's professional number value, need to refer to the professional professional number. Student (school number, name, Professional NumberProfessional Professional Number, professional name) 3. User defined integrity, user-defined integrity, ViewMultiple tables are consolidated and exported Virtual Table。 No data is stored by itself, and views can be redefined on the view. SCHEMA. Creating a schema actually defines a namespace in which the database objects (tables, views, indexes) contained in this schema can be further defined. Create a schema test for user Ender and set up a table for it tbl. CREATE SCHEMA TEST AUTHORIZATION ender        create TABLE TBL (                                                 ID NUMERIC (10,0),                                                 NAME CHAR ()                                                );D ROP schema CASCADE, CASCADE, delete mode while deleting all database objects for subordinates.                               RESTRICT, restrictions, when this mode contains database objects, is not allowed to execute, you must first delete subordinate objects, To remove this mode.   TABLE. Drop TABLE CASCADE, Cascade, delete schema while deleting indexes, views, triggers, stored procedures, functions. RESTRICT, restrictions, cannot have views, triggers, stored procedures, functions on this table. INDEX.Create unique, each index value pair has a unique record, CLUSTER, clustered index, which is indexed by the actual physical storage order of the records in the table after this index is established. Only one can be built per table. When new records are added to the table, the table's record order changes greatly, so the clustered index is not appropriate for the frequently changed columns. Index on table_name (column1_name, column2_name ...) indexes are usually implemented by B + tree and hash, which is the technology of the internal implementation of the relational database. belongs to internal mode。 Without user intervention, indexing is required to reduce query time, but frequent data additions, deletions, and updates can make the system spend a lot of time maintaining the index, instead reducing efficiency. DROP INDEX index_name; When a table is built, if no shema is specified, it is built in the current Shema and the current search path is viewed with show Search_path. Use set Search_path to "schema_name" and public; Set the current search path. Aggregation FunctionsSELECT COUNT (DISTINCT SEX) from STUDENT; SELECT max| min| Avg| SUM from STUDENT. GROUP byThe results are grouped by the specified columns, and if they are equal only. SELECT Course_nbr, COUNT (STUDENT_NBR) from the SC GROUP by COURSE_NBR; Each course and the number of classes selected, this statement groups the selected records by COURSE_NBR, and then calculates each group with the Count function. If you want to group, you also need to filter by criteria, with Havingselect STUDENT_NBR from SC GROUP by STUDENT_NBR have COUNT (*) > 3; Search for students with more than 3 elective courses. This statement groups the STUDENT_NBR with group by, and then calculates each group with count. Where, having a difference, where acts on a table, a view, a record from a selection, a having action on a group, from which a group is selected. View, viewWhen you create a new view, you do not query immediately, but only the structure, which is not really queried until you access this view. You cannot use order BY, DISTINCT. You can use aggregate functions and group by, called grouped views. Example: Based on multiple tables, connect queries, query 0002 students ' names and English scores. CREATE VIEW Student_grade (student_id,student_name,student_grade) Asselect STUDENT. student_id, STUDENT. Student_name, COURSE. Gradefrom STUDENT, Coursewhere student.id=0002 and STUDENT. Student_id=course. student_id and COURSE.  Course_name= ' 中文版 '; Updates to the view are eventually converted to updates to the base table. The general row subset view can be updated, with each DBMS specified differently. Security Autonomous access Control, Dac,discretionary Access Control. Through Grant, Revike control is implemented. GRANT Selecton TABLE Studentto ENDER; GRANT all Privilegeson TABLE stuent, Courseto ENDER, TOTO; GRANT UPDATE (student_name), Selecton TABLE Studentto rockywith GRANT OPTION; (ROCKY can grant this permission to other users) REVOKE UPDATE (student_name) on TABLE studentfrom ROCKY cascade| Restrict | Deny, default values vary by DBMS. roles, role. A role is a set of permissions. Create a role for multiple users with the same permissions. simplifies rights management. CREATE role select_student; Grant Selecton Studentto select_student, or assign role to role Grant Select_student, ... to Select_coursewith ADMIN OPTION; (Can be transferred, assigned to other roles) recycled, Revokerevoke Selecton studentfrom select_student; Audit, AUDIT, cost time space. But safe. DBA operation. AUDIT ALTER, Updateon STUDENT; Noaudit Alteron Student When the audit switch is turned on (Audit_trail is true), you can see the audit information in the system table Sys_audittrail. 2012.08.18 database integrity. Trigger, Trigger. Row trigger, for each row, statement trigger, for each STATEMENT, UPDATE STUDENT SET age= ' 0 '; If there are 100 data, the row trigger executes 100 times, and the statement triggers only once. If the inserted/updated student score is less than 60, give 60. CREATE TRIGGER Insert_student_gradebefore INSERTOR UPDATEOn Studentfor each Rowas BEGIN IF (new. Student_grade <) New. Student_grade:=60 end-if; END If the student's name changes, add a new data to the change table. CREATE TRIGGER update_nameafter UPDATE on studentfor each rowas BEGIN IF (new. Studnet_name<>old. Student_name) then inserts into Name_change the VALUES (old. Student_name,new.      Student_name, Current_User); END IF; END; DROP TRIGGER Update_nameon STUDENT; Paradigm,Normal FORM1NF, each column can not be divided, is the basic condition, if not satisfied, it can not be called a relational database. 2NF, you must have a primary key, and other properties depend entirely on the primary key. The so-called full dependency is the inability to have a property that depends only on the primary key, and if so, this part of the property and the primary key should be separated to form a new entity, and the new entity is a one-to-many relationship with the original entity. 3NF, the property does not depend on other non-primary properties. Requires that a database table not contain non-primary key information that is already contained in other tables. After the department number is listed in the Employee Information table, the department name, department profile and other departments related information can not be added to the Employee Information table, if there is no departmental information table, it should be constructed according to the third paradigm (3NF), instead of storing all attributes in a table, otherwise there will be a lot of data redundancy. Database System DesignThree-point technology, seven-point management, more than basic data. 2012.08.21 database Programming,Embedded SQL (Embedded), PL/SQL (procedural language/sql), ODBC (Open Data base Connectivity), JDBC (Java Data Base Connectivity), OLE DB (Object linking and embedding DB).  esql, the pre-compilation method is generally used by the DBMS to scan the source program, identify the ESQL statement and convert it into the main language call statement, so that the main language compiler can recognize and compile the program. To facilitate the preprocessor to recognize SQL statements, each statement begins with the exec SQL.   1. DB passes SQL execution state information to the main language, mainly using the communication area SQLCA (SQL Communication Zone);    SQL executes, writes the execution state to SQLCA, and the application reads these states from SQLCA.     is defined with the exec SQL INCLUDE SQLCA, and the variable in the SQLCA is called Sqlcode to hold the return code of the execution SQL; 2. The main language provides the parameters of the SQL statement to the DB, mainly using the main variable in the host variable;    SQL statement, which is called the main variable. Input main variable and output main variable.     All primary variables must be declared between the Begin DECLARE section and the End DECLARE section. To differentiate between the database object name (table name, view name, column name), the main variable is added ":". 3. The result of executing SQL is given to the main language processing, mainly using the main variables and cursor;    sql oriented collection, while the main language is oriented to records. Cursors are used for coordination. Cursors are user-opened data buffers that hold SQL results.   Establish a connection, exec SQL Connect to (hostname:port) Close the connection, exec SQL DISCONNECT   Do not use cursors, query results are single-recorded, non-current forms of INSERT, Delete,update. If the result is not a single, the DBMS returns an error message to SQLCA. A cursor must be used, and the result of the query is multiple, current insert,delete,update.   Dynamic SQLTemporarily assemble SQL statements, main variables, query columns, conditions, and so on. Prepare sql,exec SQL PREPARE < statement variable name > from &LT;SQL statement main variable;. Example:exec SQL BEGIN DECLARE sectinoconst char * stmt = "INSERT into Endertest (?);"; EXEC SQL END DECLARE section; EXEC SQL PREPARE mystmt from:stmtexec SQL EXECUTE mystmt USING 100. stored procedure, belonging to sql-invoked routines. written using PL/SQL. PL/SQL is an extension of the. High efficiency, reduce traffic, execute a series of SQL at a time.

ODBCOpen databases Interconnect (Open database Connectivity ODBC) is an early database interface technology introduced by Microsoft. is an integral part of the database in Microsoft's Open service architecture (Wosa,windows Open Services Architecture) It is the predecessor of ADO. ADO (ActiveX data Objects) is a COM component that is used to access data sources. It provides a programming language and uniform data access for an intermediate layer of OLE DB. Allows developers to write code that accesses data without worrying about how the database is implemented, but only the connections that are concerned with the database. When accessing a database, knowledge of SQL is not necessary, but SQL commands supported by a particular database can still be executed through the Command object in ADO. The application calls the standard ODBC functions and SQL. The application layer uses the ODBC API to invoke interfaces and databases for Exchange. The data source is the data that the user eventually accesses, including the location and type information of the DB. is an abstraction of a data connection. Each data source has a data source name DSN. Use DNS instead of user name, server name, database name. API consistency, syntax consistency, Query Optimization transaction processing, TransactionCharacteristics: Acid atomicity (atomicity), a transaction is either done or not. Consistency (consistency), isolation (isolation), not interfered by concurrent transactions.    Persistence (durability), once a transaction is committed, it is permanent. END.

From for notes (Wiz)

Introduction to the Database system review notes.