Test interview (Database-paradigm)

1. Related Concepts and knowledge data dependency: it reflects the constraint relationship between internal attributes of a relationship, and is the abstraction of the associations between real-world attributes, it is the embodiment of the inherent nature and semantics of Data Standardization Theory: it is the basis for designing a good relationship model.

1. Related Concepts and knowledge data dependency: it reflects the constraint relationship between internal attributes of a relationship, and is the abstraction of the associations between real-world attributes, it is the embodiment of the inherent nature and semantics of Data Standardization Theory: it is the basis for designing a good relationship model.

1> related concepts and knowledge

Data Dependency: reflects the constraint relationship between internal attributes of a relationship. It is an abstraction of the association between real-world attributes and reflects the inherent nature and semantics of data.

Standardization Theory: it is the basis for designing a good relationship model. It eliminates inappropriate Data Dependencies by means of the relationship decomposition mode to solve insertion exceptions, deletion exceptions, update exceptions, and data redundancy problems.

Function dependency: To put it simply, for the two attribute subsets X and Y of the relational mode, if any value of X uniquely determines the value of Y, then the Y function depends on X, x-> Y

Non-trivial function dependency: for the two attribute subsets X and Y in the relational mode, if X-> Y, but Y is not a subset of X, X-> Y is called a non-trivial function dependency.

Full function dependency: for the two attribute subsets X and Y of the relational mode, if X-> Y, and for any real subset X, X has x'-> Y, called full function dependency

Paradigm: a set of relationships that conform to a certain level.

1NF: If all attributes of the link mode are basic data items that cannot be divided, the relationship belongs to 1NF.

2NF: 1NF link mode. If all non-primary attributes are fully dependent on codes, the link mode is 2NF.

3NF: if every non-primary attribute in the link mode does not depend on code or code, the link mode is 3NF.

BCNF: if each determining factor in a relational model contains a code, the relational model is BCNF.

Database Design: constructs an optimized data logical mode and physical structure for a given environment, and establishes a database and its Application System accordingly to effectively store and manage data, meet the application requirements of various users, including information management requirements and database operation requirements.

Six basic steps of Database Design: requirement analysis, conceptual structure design, logical structure design, physical structure design, database implementation, Database Operation and Maintenance

Conceptual Structure Design: the user requirements obtained from requirement analysis are abstracted as information structures. Representing E-R graph build

Logical Structure Design: converts a conceptual structure model (E-R diagram) into a data model supported by a DBMS system. Convert code E-R to relational data mode

Physical Structure Design: select a physical structure process suitable for an application environment for a given Logical Data Model. Including data storage structure design and saving methods

Abstract: it refers to human processing of actual people, things, things, and concepts, extracting common features of interest and ignoring non-essential details.

Database Design must follow <结构设计和行为设计> Principles of Integration

The data dictionary consists of five parts: data items, data structures, data streams, data storage, and processing.

Three Common Abstract METHODS: classification, aggregation, and Generalization

The conflicts between partial E-R graphs are mainly manifested in three aspects: attribute conflicts, naming conflicts and structure conflicts.

Common Database Access methods: Index, clustering, and HASH

The main factors to consider when determining the data storage location and storage structure are: access time, space utilization, and maintenance cost.

& Database paradigm &&&&&&&&&&&&&

1> No duplicate columns in the first paradigm (1NF)

In 1NF, each column of the database table is an inseparable basic data item.

The same Column cannot contain multiple values.

That is, a property in an object cannot have multiple values or duplicate attributes. Databases that do not meet 1NF are not relational databases.

2> the 2NF attribute fully depends on the primary key (eliminating some function dependencies)

To satisfy the second paradigm, we must first meet the 1st paradigm

The second paradigm requires that each instance or tuples in the database table be uniquely distinguished.

3> the third paradigm (3NF) eliminates transmission Dependencies

Example: Before designing the database table structure, determine the content to be designed. Student ID, Student name, age, course, course credits, department, subject score, department office address, telephone number, and other information. To better understand the information, the above information is divided into the following aspects:

Student Information

What courses have students selected and their scores?

What are the credits of each course?

Which department does the student belong to and what is the basic information of the department?

First, the first paradigm (1NF): fields in the database table are single attributes and cannot be divided. This single attribute is composed of basic types, including integers, characters, logical type, and date type. Currently, one column cannot be divided into multiple columns in any relational database, so the database is in line with the first paradigm.

Consider the 2nd paradigm. Put all the information in one table (student ID, Student name, age, gender, course, course credits, department, subject score, subject address, and phone number) the following dependencies exist.

1) (student ID)-> (name, age, gender, Department, Department address, Department phone number)

2) (Course name)-> (credits)

3) (student ID, course)-> (subject score)

Based on the dependency, we generate three tables:

Student: student (student ID, name, age, gender, Department, Department address, Department phone number)

Course: Course (Course name, credits)

Course Selection relationship: selectCourse (student ID, course name, score)

In contrast to the requirements of the second paradigm, the above table has already met the second paradigm. If a database does not meet the 2nd paradigm, it will produce:

Data redundancy, update exceptions, deletion exceptions, etc. For example, if n students take the same course, the credits are repeated for n-1 times, and m courses are selected for the same student, then his name will be repeated multiple times.

If you adjust the credits of a course, all credits will be updated; otherwise, multiple credits will occur for one course. If a group of students have completed their courses and want to delete these records from the database, they will be deleted together with the course information.

Next, we will consider changing the database to a database table that meets the 3rd paradigm:

Check the Student ID (Student ID, name, age, gender, department, office address, Department phone number) in the Student table above. The Student ID is a single code. The Department address and Department phone number depend on the department. Function dependencies are passed.

Split the student table into two tables:

Student (student ID, name, age, gender, Department)

Department (Department, Department address, Department phone number)

The above relationship satisfies the three paradigms.