The procedure of the test interview (Database-paradigm) and the test paradigm

The procedure of the test interview (Database-paradigm) and the test paradigm

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 the principle of combining <structure design and behavior design>

 

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.

Urgent database paradigm issues

Answer: paradigm 1
Let me briefly introduce you to the definition of the One, Two, and Three paradigm.
The premise of meeting the second paradigm is to satisfy the first paradigm. The premise of meeting the third paradigm is to satisfy the second paradigm. There are 6 levels of paradigm in total, but the second paradigm is commonly used.
The first paradigm is the basic requirement that relational databases must meet. databases that do not meet the first paradigm are not relational databases.
The first paradigm is that each column of a database is an inseparable basic data item. (For example, each record is unique, but the values of each column are repeated, because the course and major are many-to-many relationships) the same column cannot have multiple values, and an attribute in the entity cannot have multiple values or duplicate values. (for example, a course number corresponds to a course name .)
Second paradigm: the instance or row in the database table must be uniquely identified (that is, there must be a proxy primary key, for example, generating a unique ID with a sequence as the identifier)
The third paradigm: requires that a database table not contain non-primary keywords that have already been included in other tables. (For example, the Department table stores the Department name and address of the Department encoding department. the employee table stores the employee code department code and other employee personal information, but does not contain the Department name or address or other field columns ).
After reading this, I believe you can quickly understand why your question is the first paradigm. simply store the description of an object without considering the primary key or the relationship with other tables. This is the minimum requirement for relational databases. first paradigm.

A database paradigm exercise

(1) In line with the first paradigm, each column is an inseparable basic data item.
It does not conform to the second paradigm, because the primary key should be the Book Number + reader number (assume that a person only needs to read a book once, otherwise the primary key must be added with the lending date), but the book name and author are only determined by the book number, this does not conform to the attributes of the second paradigm and relies entirely on the primary key.
(2) split the data into three relationships:
1. Book (book number, title, author)
2. Reader (Reader ID, reader name)
3. Book Management (book number, reader number, lending date, due date)