Database "Paradigm"

1. Why do you want to learn the database "paradigm"?

When we are independent to complete a small project of their own, we must design the "appropriate" data model is the logical architecture, then, how do we know that our design data model is the most "appropriate"? There must be a standard to measure the data model of their design, see here, you know why to learn the paradigm.

2. What problems can the "paradigm" solve?

As I said earlier, the learning paradigm is designed to design a "suitable" data model, so what can be solved by a "fit" data model?

1. Eliminate data redundancy.
2. Remove the update exception.
3. Eliminate INSERT Exceptions
4. Remove the delete exception.

3. The concept of paradigm

Paradigm is a set of relational patterns conforming to a certain level, which is the basis of relational database theory and the rules and guidelines we should follow in designing database structure. The database has 5 basic paradigms: The first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the BCNF (BC Paradigm), and the fourth paradigm (4NF). Even there is the 5NF,6NF,DK paradigm, this article only discusses the first five paradigms, these five paradigms, is to solve the second point of the problem. From 1NF to 4NF step by step to raise high standards, you can think of it as an ape-to-human evolutionary process.

4. Specific examples

A specific example is used to analyze these paradigms.

student number	name	Department name	dorm building	Course number	results
03001	Joey	Telecommunication Department	a floor	language	up
03001	Joey	Telecommunications Department	a floor	math	89 /td>
04015	Yong	Computer Department	C floor	big things	series
04015	Yong	Computer Department	Floor C	English	-
03018	Wentao	Telecommunication Department	a floor	language
03019	Ma Tijun	Legal Department	Floor b	big things	,
03019	He Zhiping	Legal Department	Floor b	big things	all

The relationship pattern is mentioned above. And this chart has a relationship.
1. School Number (x) –> name (y).
2. Study number (x) –> name (y).
3. Department name (x) –> dormitory building (y).
4. (School number, course) (x) –> score (y).
How to understand the relationship above? Their relationship is y=x^2, and when X is determined, y must have a unique value, that is, x uniquely determines Y.

4. Several special function dependencies

1. Trivial function dependency: If the A attribute group uniquely determines b, and the B attribute belongs to the A attribute group, then a–>b is a trivial function dependency. Eg: in the above table (school number, course) and course. The course belongs to the attribute group (school number, course). For any relational mode, trivial function dependencies are established. So, talking about it is, meaningless, so everyone knows on the line.
2. Non-trivial function dependency: If the A attribute group uniquely determines b, and the B attribute does not belong to the A attribute group, then a->b is a non-trivial function dependency. Eg: in the table above (study number, course)--scores, grades do not belong to (study number, course) This attribute group, so non-trivial function dependency.
3. Partial function Dependency: A attribute group can determine B, but B is not entirely dependent on X, what does it mean, eg (school number, course), name. (Study number, course) can only determine the name, but the name only need a school number to be able to determine the only, no courses can also, this is called Part B depends on a.
4. Full function dependency: A attribute group uniquely determines B, and any one true subset of a attribute group cannot uniquely determine B. So B is completely dependent on a.eg: (School number, course) –> results. (School number, any one of the courses can not only determine the results), so that the results are entirely dependent on (school number, curriculum);
5. Transfer function Dependency: If the property is A–>b,b–>c, then a–>c. and B is not a, a, then the only one can not determine a, then a–>c.eg: study number, name, department name, dormitory building. So study number Dormitory building. For transfer function dependency.
6. Code: A property or group of attributes can uniquely determine all properties other than this property or group of attributes, that is, other properties are completely dependent on this property or group of properties. EG (student number, course) the only certainty (name, department name, dorm building, results), then, how to find the code, If there are n attribute columns, then all the combinations, a combination of an attribute n, a combination of two attributes, a Cn2 (representing any two from the n medium), a combination of three attributes Cn3 (representing any three from N) ... of course, there is no way to try it one by one. That's tricky, when you already know (study number, Course) is the code, then, the composition that contains this column is ignored directly. Because the code is fully functional dependent.
7. Main attribute: The attribute contained in the code is called the main attribute.
8. Non-primary properties: All attributes except those contained in the code.
9. Single code: A single attribute is a code.
10. Full code: The entire attribute group is a code, called a full code.

6. First Paradigm (1NF)

If the relationship mode R does not contain multivalued attributes, then R satisfies the first normal form. Moreover, all the attributes in the relational schema are non-sub-transactional data items, the first paradigm is the minimum requirement for the relational schema, and the database schema that does not satisfy the first paradigm is called the relational database. Let's look at an example:

In the above table, students correspond (school number, name), the students are obviously both corresponding to the number, and the corresponding name. So, this should be adjusted separately, the first one (the table below satisfies the first paradigm).

student number	name	Department name	dorm building	Course number	results
03001	Joey	Telecommunication Department	a floor	language	up
03001	Joey	Telecommunications Department	a floor	math	89 /td>
04015	Yong	Computer Department	C floor	big things	series
04015	Yong	Computer Department	Floor C	English	-
03018	Wentao	Telecommunication Department	a floor	language
03019	Ma Tijun	Legal Department	Floor b	big things	,
03020	He Zhiping	Legal Department	Floor b	big things	all

Looking at the 4 paradigms we mentioned above, we have to solve the 4 problems, whether the first paradigm solves the above problem and solves several:
* Data redundancy can be seen name, name, dorm building a lot of repetition.
* Update exception: If a student is renamed, all of the selected tuples for this student will have to be updated (eg: if Joey is renamed, it needs to be changed to two columns).
* Delete exception: If the computer department students all graduate, the corresponding computer department and the C floor information will also be deleted.
* Insert Exception: If the school opened a new department, but did not recruit students, the department will not be inserted.

7. Second paradigm (2NF)

On the basis of the first paradigm, if each non-principal attribute is fully functional dependent on the primary attribute, then the requirement of the second paradigm (2NF) is met, in other words, to eliminate the partial function dependency of all non-primary attributes on the main attribute (PS: This is the requirement of the second paradigm), so how to analyze it? See our example above
Main attribute: (study number, course); PS: The main attribute must be a code OH.
Non-main attributes: Name, department, dormitory, results.

(School number, course), Name: name by the school number can be determined only, so name pairs (school number, course) is part of the function of dependence.
(School number, course): Department and name, the number can be determined only.
In order to eliminate part of the function dependency in the relational pattern, a projection decomposition method is used to separate part of the function dependency from the relational schema, and the following two tables are obtained.

SC (School number, course, score);
SL (School number, name, department name, dormitory building);
The two tables were:
SC table

School Number	Courses	Achievements
03001	Chinese	95
03001	Mathematical	89
04015	Big Things	70
04015	English	85
03018	Chinese	87
03019	Big Things	88
03020	Big Things	56

s table

School Number	name	Department Name	Dormitory Building
03001	Joey	Telecommunication Department	Building a
04015	Yong	Computer Department	C Floor
03018	Wentao	Telecommunication Department	Building a
03019	Ma Tijun	Faculty of Law	b Floor
03020	He Zhiping	Faculty of Law	b Floor

Looking at the 4 questions mentioned above we have solved several:
Data redundancy: Obviously the name, the name, and the dean's data redundancy have been significantly improved.
Update Exception: Now, a student renaming, only need to change the name in the S table, and there is no need to change his every record of the course selection.
Insert exception: If the school opens a new department, in the absence of enrollment, the department still cannot insert into the S table, because the student number is the main attribute. ()
Delete exception: When all students of a department graduate and then delete all student information, they will be accompanied by the deletion of all information from the department.
Since the second paradigm has only reduced data redundancy, the rest has not been improved. Then, let's look at the third paradigm together:

8. The third paradigm (3NF)

Similarly, the third paradigm is based on the second paradigm. The purpose of the third paradigm is to eliminate the transfer function dependency of the non-principal attribute on the main attribute.
In the second paradigm, we get two tables:
SC table: Elective course (school number, course, score).
S table: (student number, name, department name, dorm building)
Remember what a transfer function depends on, if you forget, turn to the top to see. Analyzing the SC table, we found that there is no transfer function dependency, and in the S table (student number) –> (name) (department name) and (the dormitory) there is a transfer function dependency, then we solved this transfer function dependency, See if the above question can not be improved?
SC table: Elective course (school number, course, score)
s table: Student (School number, name, name);
Table D: Department (department, Dormitory building);
　
Get three Sheets
SC table

School Number	Courses	Achievements
03001	Chinese	95
03001	Mathematical	89
04015	Big Things	70
04015	English	85
03018	Chinese	87
03019	Big Things	88
03020	Big Things	56

s table

School Number	name	Department Name
03001	Joey	Telecommunication Department
04015	Yong	Computer Department
03018	Wentao	Telecommunication Department
03019	Ma Tijun	Faculty of Law
03020	He Zhiping	Faculty of Law

D table

Department	Dormitory Building
Telecommunication Department	Building a
Faculty of Law	b Floor
Computer Department	C Floor

And look at the above questions. Our third paradigm solves several:
Insert exception: If there is a new department in the school. This department can be inserted into the D table even if it is not included in the admissions program.
Delete exception, even if all the students in a department now graduate, delete the information of all students in this department, the information of this department will not be deleted.
As you can see, data redundancy, update exceptions, insert exceptions, and deletion exceptions have all been improved until now. It has been able to basically meet our needs.

9.BC Paradigm (BCNF)

BC Paradigm: The BC Paradigm is a special case on the basis of the third paradigm, where there is only one candidate key for each table (the value of each row in a database is different, which can be called a candidate key).
Each determining factor is a key, then R must belong to the BC Paradigm, and for BCNF's relational pattern, it has the following properties:
(1) All non-primary properties are completely dependent on each primary property. (already implemented in the second paradigm 2NF)
(2) All primary attributes are fully functional dependent on each primary attribute that does not contain it;
(3) No property full function relies on any set of properties in a non-primary property. (already implemented in the third paradigm)
So, the BC paradigm is all about the main attribute, which is the second one above. all primary properties are fully functional dependent on each primary attribute that does not contain it;
Look at a concrete example.
Suppose the Warehouse Management Relationship table is Storehoursemange (warehouse ID, store item ID, administrator ID, quantity).

Warehouse ID	Store Item ID	Administrator ID	Quantity
A1	S1	C1	20
A1	S2	C1	30
A2	S3	C2	40
A2	S4	C2	50
A2	S5	C2	60

This table satisfies:

• An administrator works only in one warehouse
• A warehouse can store a variety of items
  This database table has the following determinant relationships:
  (Warehouse ID, store item ID)--(administrator ID, quantity)
  (Administrator ID, store item ID)--(warehouse ID, quantity)
  Main properties: Warehouse ID, store item ID, administrator ID.
  Non-primary attribute: quantity.
  Code (remember the concept of the code, do not remember to look up):(warehouse ID, store item ID), (Administrator ID, store item id).
  First to determine whether this table satisfies the third normal form (3NF), because the BCNF paradigm is based on the third paradigm.
  The analysis table can be seen. There is no non-primary property for the partial function dependency and transitive function dependency on the primary attribute. Satisfies the third normal form.
  So let's take a look at the four questions mentioned above:
  Data redundancy: It is obvious that this problem does not exist.
  Insert Exception: If you create a new warehouse now, but do not store any items in this warehouse, this data cannot be deposited.
  Delete exception: If the items in a warehouse are all out of the library, the corresponding warehouses will be deleted as well.
  Update exception: If a warehouse is replaced by an administrator, multiple data will be changed.
  What is the reason?
  Look at this relationship (warehouse ID,)
  (Admin ID, store item ID)--(warehouse ID); To see this relationship, the three attributes are the primary attribute, but (the administrator ID uniquely determines the warehouse ID), so the warehouse ID pair (Administrator ID, store item ID) has a partial function dependency.
  The Decomposition table gets:
  Warehouse: (warehouse ID, administrator ID);
  Items: (Warehouse ID, store item ID, quantity);
  Warehouse:

Warehouse ID	Administrator ID
A1	C1
A2	C2

Items:

Warehouse ID	Store Item ID	Quantity
A1	S1	20
A1	S2	30
A2	S3	40
A2	S4	50
A2	S5	60

Look at the question just now:

• Insert Exception: Create a new warehouse, even if there is nothing inside, you can insert the warehouse table.
• Update exception: When the administrator of a warehouse changes, only one data in the warehouse table needs to be changed.
• Delete exception: All items in a warehouse are out of stock and this warehouse is not deleted.

10. Paradigm Four (4NF)

The above solves the problem of normalization of relational patterns in the context of function dependency, if a relational pattern reaches bcnf, is it perfect. Look at the following example:
In a teaching management system, there is a relational model teaching (C,T,B), where C denotes a course, T denotes a teacher, and B represents a reference book.

The following relationships exist:

• A course is taught by multiple teachers who use the same set of reference books.
• Each teacher can teach multiple courses, and each reference book can be used for multiple courses.

course c	teacher T	reference book B
Database Original Management and Application	Deng Yu	database System introduction
database principles and Applications	Deng Yu	SQL Server2000
database principles and Applications	Deng Yu	discrete math
database principles and Applications	Magosawa	Database System introduction
database principles and Applications	Magosawa	Database System introduction
principles and applications of databases	Magosawa	Introduction to Database Systems
data structure	Magosawa	data Structures and algorithms
data structure	Magosawa	data structure
data structure	Magosawa	discrete math
data Structure	Cao Peng	data structures and algorithms
data Structure	Cao Peng	data structures and algorithms
data Structure	Cao Peng	discrete math

Obviously: This table is full key;

Let's take a look at the 4 questions mentioned above:

• Data redundancy: Courses, teachers, reference books, and information about large volumes of repetitive storage.
• Insert exception: When an instructor is added to a course, multiple tuples must be inserted.
• Update exception: If a course changes to a teacher, you must modify the multiline value.
• Delete exception: To delete a reference book, you need to delete more than one record.
  Why does this watch have such a problem?
  Let's look at the relationship of this table first:
  

A course has a number of reference books, which is obviously a one-to-many relationship. This means that the course has a multivalued dependency on reference books.
When it comes to multivalued dependencies, let's take a look at what is called multivalued dependency:

Set R (U) is a relational pattern on the attribute set U, and X, Y, Z is a subset of U, and z=u-x-y. The relationship Mode R (U) is a multi-valued dependent x–>->y, when and only if any of R (U) r, given a pair (x,z) value, Has a set of Y values. This set of values only determines the value of x and is not related to the value of Z.
Eg: in the above relational mode, for a (c,b) value (Database principle and application, SQL Server 2000), there is a set of T-value {Deng Yu, Magosawa}, and this set of values is determined only in course C (Database principle and application), so the value of T is independent of the value of B and M is only determined by C, c->- >T;
Decompose the above table into two relational patterns:
T (course c, teacher T);
B (course c, reference book);
Relationship T

Course C	Teacher T
Principle and application of database	Deng Yu
Principle and application of database	Magosawa
Data	Magosawa
Data	Cao Peng

Relationship B

Course C	reference book B
Principle and application of database	Introduction to Database System
Principle and application of database	SQL Server2000
Principle and application of database	Discrete mathematics
Data	Data structure and algorithms
Data	Data
Data	Discrete mathematics

See if the above question has been improved:

• Information redundancy: improved;
• Insert problem: Add a lesson to a course, as long as a record is added to the T table.
• Delete a problem: Delete a book and simply delete a record in the BC table.

To summarize:

• It is not difficult to see the process of decomposition of the database paradigm, the higher the application paradigm registration, the more tables. There are many problems with the table:
• Multiple tables are concatenated when querying, increasing the complexity of the query.
• When querying, you need to connect multiple tables to reduce database query performance.
• In the current situation, disk space costs are negligible, so the problem caused by data redundancy is not the reason to apply the database paradigm.
• Therefore, it is not the case that the higher the application paradigm, the better, depends on the actual situation. The third paradigm has largely reduced data redundancy and reduced the insertion exception, update exception, and deletion exception. My personal view is that the majority of cases are sufficient to apply to the third paradigm, and in some cases the second paradigm is also possible.

Database "Paradigm"