APS-database management

数据库原理面试知识点 1. 数据库（关系模型，范式和解释什么是Transaction） A. Explain how to design a good database? To design a database means to create some tables that can represents all the data we want to store in the database, and no exception happens, when we insert, delete and update data in the database, and no repeated data are stored in the database. First of all, the database must be 1-NF, which means all the tables must be atomic. We say a table is atomic, if its elements are considered to be indivisible units. For example, suppose that we must design a database to store informations about students and courses, we have a table like this:

studentID studentName courseID 101 Jack 201, 202 102 Tom 201 which means Jack has selected course 201 and 202, Tom has selected course 201, but this table is not atomic, because the element courseID can has multivalue that can be divided. We must change the table like this to satisfy 1-NF: studentID studentName courseID 101 Jack 201 101 Jack 202 102 Tom 201 In the table we use (studentID, courseID) to be the primary key of the table, primary key is used to mark a record of a table uniquely, and cannot be empty. Suppose that, Tom does not want to learn the content of course 201 after attending the first lecture of the course, and he quits the course, then we delete the record of (102, 201) in the table. After the deletion, the president of the school wants to know if there is student called Tom in his school, he cannot find the information in the table, but actually there is a Tom. The delete exception happens. To avoid insert, delete and update exception, the database must satisfy 2-NF, which means the database satisfy 1-NF and all the nonprimary key in a table must reply on the primary key of the table, for example, the key studentName of the table does not rely on courseID, which is part of the primary key, so the two keys of the table must be divided:

studentID studentName 101 Jack 102 Tom

courseID teacherID teacherName 201 301 Professor A 202 302 Professor B

studentID courseID 101 201 101 202 102 201 The third table means the relationship betweem students and courses, this a many-to-many relationship. Here I add some information to course table. The teacherID and teacherName is reply on the prinary key courseID, because when we know the courseID, we know who teaches the course. So the database satisfy 2-NF, but here we still have delete exception, when we delete the information of course 201, which mean the cource will be cancelled, but we also delete the information of Professor A, and Professor A still works in the school. So the course table must be divided to satisfy 3-NF, which means the database satisfy 2-NF and all the nonprimary-key of a table cannot rely on the primary key through a transitive function. In the example, teacherName relies on courseID through a transitive function, teacherName = F(teacherId). We divide the course table like this:

courseID teacherID 101 301 102 302

teacherID teacherName 301 Professor A 302 Professor B

After 3-NF, we also have 4-NF, but we do not learn it thoroughly in the course Database Design. In practice, a database that satisfies 3-NF can be used in almost all the applications.

数据库原理 Principles of database system Relational Database: 关系数据库 A relational database is a collection of data items organized as a set of formally described tables from which data can be accessed easily. A relational database is created using the relational model 关系模型. The software used in a relational database is called a relational database management system (RDBMS). A relation is defined as a set of tuples 元组 that have the same attributes. A tuple usually represents an object and information about that object. Objects are typically physical objects or concepts. A relation is usually described as a table, which is organized into rows and columns. All the data referenced by an attribute are in the same domain and conform to the same constraints. The relational model specifies that the tuples of a relation have no specific order and that the tuples, in turn, impose no order on the attributes. Applications access data by specifying queries, which use operations such as select to identify tuples, project to identify attributes, and join to combine relations. Relations can be modified using the insert, delete, and update operators. New tuples can supply explicit values 确定值 or be derived from a query. Similarly, queries identify tuples for updating or deleting. It is necessary for each tuple of a relation to be uniquely identifiable by some combination (one or more) of its attribute values.