Test interview that little thing (database knowledge), test interview

Test interview that little thing (database knowledge), test interview

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 <student ID, score> (S <course number = '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 <student ID, name, course name, score> (S <major = 'English '> (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}

 

When a fresh graduates interview software implementation engineers, they will ask what knowledge about the database? Both questions and answers are required.

It mainly refers to SQL query statements and SQL optimization.
As for the questions and answers, go through the relevant teaching materials or find the articles summarized by others. Why is the answer here?

Database knowledge

What is a database?

A database is a set of data that is organized according to a certain data model and stored in the second-level memory. This type of data set has the following features: it serves as many applications as possible in the best way as possible for a specific organization. Its data structure is independent of its application, the unified software manages and controls the addition, deletion, modification, and retrieval of data. From the development history, the database is an advanced stage of data management, which is developed by the file management system.

The basic structure of the database is divided into three layers, reflecting the three different perspectives of observing the database.

(1) physical data layer. It is the innermost layer of the database and a set of data actually stored on physical storage devices. These data are raw data and are the objects processed by users. They are composed of character strings, characters, and characters for instruction operations described in the internal mode.

(2) concept data layer. It is the middle layer of the database and represents the overall logic of the database. It points out that the logical definitions of each data and the logical connections between data are a collection of storage records. It involves the logical relationship of all objects in the database, rather than their physical conditions. It is a database under the concept of a database administrator.

(3) logical data layer. It is the database that the user sees and uses. It represents a set of data used by one or more specific users, that is, a set of logical records.

The relationships between different levels of databases are converted through ing. Databases have the following features:

(1) data sharing. Data sharing allows all users to access data in the database at the same time, and also allows users to use the database through interfaces in various ways, and provides data sharing.

(2) reduce data redundancy. Compared with the file system, the database achieves data sharing, thus avoiding the creation of application files. This reduces a large amount of duplicate data, reduces data redundancy, and maintains data consistency.

(3) Data independence. Data independence includes the Logical Structure and Application of the database in the database, and the change of the physical structure of the data does not affect the logical structure of the data.

(4) centralized data control. In the file management mode, data is in a scattered state. Different users or the same user have no relationship with each other in processing their files. Databases can be used to centrally control and manage data, and data models can be used to represent various data organizations and relationships between data.

(5) data consistency and maintainability to ensure data security and reliability. It mainly includes: ① Security Control: to prevent data loss, error update, and unauthorized use; ② Integrity Control: to ensure data correctness, validity, and compatibility; ③ concurrency control: enables multi-channel access to data within the same period of time, and prevents abnormal interaction between users. ④ fault discovery and recovery: the database management system provides a set of methods, promptly detects and repairs faults to prevent data from being damaged.

You may be a beginner. In this case, the database is to create a table, and then pin a table together to produce a lot of data. In professional terms, we call such a large number of tables a database.
There are a lot of books. <Introduction to databases> This is a good book. I have learned this book. Although I am a little more familiar with lilun, I have learned this book and I have to find another practical teaching material, (I am referring to a book dedicated to learning database software.
At that time, I was talking about this book and talking about <SQL server 2000>

SQL and MySQL are suitable for learning databases at home,
ORCAL, suitable for company applications
DEPHI is not a database, but a database system. It is quite practical.
ACCESS, which is suitable for applications and is not suitable for learning databases using it, because ACCESS generally uses no language and is intended for common users, that is, it is more convenient and practical to use,

I don't know how much you can understand, so you should find a book to learn,
By the way, I forgot to talk to you. On "VeryCD", you can download a lot of related materials from the book I just mentioned.
References:... the remaining full text>