Test interview (database knowledge)

1. relational database standardization is introduced to solve the problems of relational databases (insertion exceptions, deletion exceptions, and data redundancy. 2. The development of data management technology has gone through the manual management, file system, and database system phases. In these stages, data (in the database system stage) is the most independent. 3 Database (DB ),

1. relational database standardization is introduced to solve the problems of relational databases (insertion exceptions, deletion exceptions, and data redundancy. 2. The development of data management technology has gone through the manual management, file system, and database system phases. In these stages, data (in the database system stage) is the most independent. 3 Database (DB ),

1> relational database standardization is introduced to solve the problems of relational databases (insertion exceptions, deletion exceptions, and data redundancy.

2> the development of data management technology has gone through the manual management, file system, and database system phases. In these stages, data (in the database system stage) is the most independent.

3> relationships among databases (DB), database systems (DBS), and database management systems (DBMS) (DBS include DB and DBMS)

4> the database management system can define, modify, and delete data tables, indexes, and other objects in the database. These languages are called (Database Definition Language (DDL ))

5> Any two values of the same link model (not all values are the same)

6> the conceptual model is (used for modeling the information world and has nothing to do with a specific DBMS)

7> physical data independence refers to (internal mode change, mode unchanged)

8> the SQL language is (relational database language)

9> A natural connection is an effective way to form a new relationship. Generally, when a natural connection is used for the relationship R and S, requires that R and S contain one or more common (attributes)

10> the longest time-consuming operation in relational operations is (Cartesian Product)

11> the biggest difference between a file system and a database system is (Data structuring)

12> the SQL statement used for transaction ROLLBACK (ROLLBACK)

13> (Log File) is used to record every update operation performed on the data in the database.

14> data inconsistency caused by concurrent operations (loss of modifications, repeated read failures, and dirty data READING)

15> to improve efficiency, relational databases must be optimized (queried ).

16> description of the physical storage mode of the database (Internal Mode)

17> the primary role of introducing a secondary image between database-Level 3 models is (improving data and program independence)

18> A view is a virtual table. The view structure is based on (basic table or view)

19> in relational algebra, projection operations correspond to (SELECT) in SQL statements)

20> converting the E-R mode into a relational model is a database (logical design)

21> main use of transaction logs (Transaction Processing)

22> If transaction T has applied the X lock to the data R, other transactions will apply the X lock to the data R (you can apply any lock)

& Describes the differences and relationships between views and basic tables.

A view is exported from one or more basic tables. Unlike a basic table, a view is a virtual table. In a database, only view definitions are stored without the data of the view. The data is stored in the original basic table. When the data in the basic table changes, the data queried from the view also changes. View

Once defined, a new view can be queried or deleted like a basic table, or a new view can be defined on The View. However, there are some restrictions on the update operation of views.

& Briefly describe the features of transactions

Transactions have four features:

Atomicity: either a transaction is done or nothing is done.

Consistency: the transaction must change the database from one consistent state to another consistent state.

Isolation: internal operations of a transaction and data usage are isolated from other transactions.

Continuity: Once a transaction is committed, the impact on the database is persistent.

& Briefly describe the integrity of references in the relational model

Reference integrity rules: If attribute F is an external code of the basic table relation S, it corresponds to the primary code Ks of the basic table relation R. It is required that the value of attribute F must correspond to the master code of a tuples in the relational R, or be NULL.

& Existing relational databases are as follows:

Student (student ID, name, gender, Major)

Course (course number, course name, credits)

Learning (student ID, course number, score)

Use relational algebra expressions and SQL statements to fulfill the following requirements:

(T stands for projection, S stands for projection, and L stands for join)

1. Retrieve the student ID and score of all students whose course number is "C112"

SELECT student ID, score FROM study WHERE course number = 'c112'

Relational algebra: T <学号，分数> (S <课程号=‘c112’> (Learning ))

2. Search for information about the courses learned by English majors, including student IDs, names, course names, and scores.

SELECT student. Student ID, name, course name, score

FROM student, study, Course

WHERE learning. Student ID = student. Student id and learning. Course number = course. Course number AND major = 'English'

Relational algebra: T <学号，姓名，课程名，分数> (S <专业=‘英语’> (L (student, study, course )))

3. Retrieve the student ID, name, Major, and score of all students whose score is higher than 90 for the "database principles" Course

SELECT student. Student ID, name, Major, score

FROM student, study, Course

WHERE student. Student ID = learning. Student id and learning. Course number = course. Course No. AND score> 90 AND Course name = 'database print'

Algebra:

Similar to the previous question

4. Retrieve the student information of the "C135" course, including the student ID, name, and major.

SELECT student ID, name, Major

FROM student

WHERE student no. not in (SELECT student no. FROM student WHERE course No. = 'c135 ')

5. Retrieve the student information for at least the course Numbers "C135" and "C100", including the student ID, name, and major.

SELECT student ID, name, Major

FROM student

WHERE student id in (SELECT X1. student id from study X1, study X2 WHERE X1. student ID = X2. student id and X1. course number = 'c135' AND X2. course number = 'c100 ')

& A teaching management database is set up with the following attributes: Student ID (S #), course number (C #), score (G), instructor (TN ), the Department (D) where the instructor is located. The meaning of the data is as follows:

1. Each course of a student has a score.

2. Each course has only one instructor, but each instructor can teach multiple courses.

3. There are no duplicate teachers. Each teacher belongs to only one department.

Problem:

Determine the function dependency set based on the preceding semantics.

If all the above attributes form a relational mode, what is the mode of this relational mode? Examples

Break it down into 3NF with dependency persistence

Answer:

(1) F = {(S #, C #)-> G, C #-> TN, TN-> D}

(2) This relational mode only meets 1NF (that is, all attributes are atomic attributes)

The candidate keywords in this relational mode are (S #, C #) and non-primary attributes G, TN, and D.

Because some NNS dependencies and primary attributes of non-primary attributes do not meet 2NF requirements

Insertion exception: for example, if a student has not selected a course, the student cannot join the database.

(3) R1 = {S #, C #, G} R2 = {C #, TN} R3 = {TN, D}