Three Forms of Application Database Design Analysis (2)

2010-07-27  来源:本站原创  分类:Database  人气:186 

The third paradigm (3NF): In the second paradigm, based on data in the table if there is no section on any non-keyword candidate keyword section of the transfer function depends, is in the third paradigm. The so-called transfer function dependent on that is that if there is "A → B → C" decision between the transfer function C depends on A. Therefore, to meet the third paradigm of database tables should not exist as dependencies:
→ non-keyword segment key fields of non-keyword segment x → y
Table assumes that students Relations Student (student number, name, age, where the college, college location, college phone), the keyword for the single keyword "Student Number", because of the following decisions relationship:

(Student ID) → (name, age, where the college, college location, college phone)

The database is in 2NF, but do not meet 3NF, because of the following decisions relationship:

(Student ID) → (where the Academy) → (University site, the college phone)

Namely, the existence of non-key fields, "Institute of place", "School Phone" section on the keyword "Student Number" of the transfer function depends.

There is also data redundancy, update anomalies, insertion anomaly and deletion anomaly, readers can learn on their own analysis.

Relationship between the student table is divided into the following two tables:

Student: (Student ID, name, age, where the College);

School: (School, location, phone).

This database table is in line with the third paradigm, eliminating data redundancy, update anomalies, insertion anomaly and deletion anomaly.
Beuys - Section may Paradigm (BCNF): In the third paradigm, based on a database table if there is no field of any one candidate keyword section of the transfer function depends, is in the third paradigm.

Suppose warehouse management relations table StorehouseManage (Warehouse ID, store items ID, the administrator ID, number), and there is only one administrator work in a warehouse; a warehouse to store various items. The following decisions exist in the database table relationship:
(Warehouse ID, store items ID) → (administrator ID, quantity)
(Administrator ID, store items ID) → (repository ID, quantity)
Therefore, (warehouse ID, store items ID) and (administrator ID, store items ID) are StorehouseManage candidate keyword, the table only for the number of non-key fields, which is in line with the third paradigm. However, following the decision because of relationship:
(Warehouse ID) → (administrator ID)
(Administrator ID) → (Warehouse ID)
Namely, the existence keyword keyword paragraph to paragraph decision, so it does not meet BCNF paradigm. It will appear as exceptions:
(1) Delete Exception:
When the warehouse was empty, all "store items ID" and "quantitative" information is removed, while "storage ID" and "Manager ID" information has been deleted.
(2) Insert the exception:
When the warehouse does not store any items, not to the warehouse distribution manager.
(3) update anomalies:
If the repository for the administrator, the administrator of all rows in the table ID to be modified.
Relationship between the warehouse management table is divided into two relational tables:
Warehouse Management: StorehouseManage (Warehouse ID, manager ID);
Storage: Storehouse (Warehouse ID, store items ID, quantity).
This database table is in BCNF paradigm, eliminating the exception to delete, insert, update anomalies and abnormalities.

Model application

A forum for us to gradually buttoned database, the following information:
(1) User: user name, email, homepage, telephone, contact address (2) Posts: post title, post content, respond to the title, the first time we will return the contents of the database design for the mere existence of the table:

User name email Home Phone Contact Address Posting Title Posting content Reply Title Reply Content

The database table consistent with the first paradigm, but there is no candidate for a group of keywords to determine the entire database table row, the only key fields the user name can not be completely determined by the tuple. We need to increase the "Post ID", "Reply ID" field, will table amendments to:

User name email Home Phone Contact Address Posting ID Posting Title Posting content Reply ID Reply Title Back contents
相关文章
  • Three Forms of Application Database Design Analysis (transfer) 2010-04-19

    Simple and clear! Application database design analysis of the three major paradigms Introduction Database design paradigm is needed to meet database design specifications that meet these specifications database is simple, clear structure, at the same

  • Three Forms of Application Database Design Analysis (2) 2010-07-27

    The third paradigm (3NF): In the second paradigm, based on data in the table if there is no section on any non-keyword candidate keyword section of the transfer function depends, is in the third paradigm. The so-called transfer function dependent on

  • Three Forms of Application Database Design Analysis (1) 2010-07-27

    Introduction Database design paradigm is needed to meet database design specifications that meet these specifications database is simple, clear structure, at the same time, does not occur insert (insert), delete (delete) and update (update) operation

  • Three Forms of Application Database Design Analysis (3) 2010-07-27

    This data table keywords (user name, posting ID, reply ID) to determine the entire line: (User name, posting ID, reply ID) → (email, homepage, telephone, address, post title, post content, respond to the title, back content) However, this design does

  • Application database design analysis of the three major paradigms 2010-03-19

    Introduction Database design paradigm is needed to meet database design specifications that meet these specifications database is simple, clear structure, at the same time, does not occur insert (insert), delete (delete) and update (update) operation

  • Application database design analysis of the three major paradigms (put it more clearly) 2011-05-09

    First say a few words: The first fruits of labor, so I find this rather vague on the paradigm now have a clearer understanding. However, with my own understanding and practical experience, which I will add some of my personal notes in order to facili

  • [Transfer] application example database design, analysis of the three major paradigms 2010-04-08

    Introduction Database design database design paradigm is required to meet specifications, to meet these specifications database is simple, the dimension structure, and Tong Shi, does not occur Insert (insert), Delete (delete) and Geng Xin (update) op

  • Simple. Clear! Database design, analysis of the three major paradigms Application 2010-03-12

    Introduction Database design paradigm is needed to meet database design specifications that meet these specifications database is simple, clear structure, at the same time, does not occur insert (insert), delete (delete) and update (update) operation

  • Database design analysis of the three major paradigms application example - turn 2011-08-01

    Introduction Database design paradigm is needed to meet database design specifications to meet these specifications database is simple, clear structure, at the same time, does not occur insert (insert), delete (delete) and update (update) operation e

  • Database design analysis of the three major paradigms Applications 2011-03-25

    Introduction Database design paradigm is needed to meet database design specifications to meet these specifications database is simple, clear structure, at the same time, does not occur insert (insert), delete (delete) and update (update) operation e

  • Very large Oracle database application system design 2010-11-10

    Very large Oracle database application system design This article introduces several large Oracle database application system design, the details see below. I. Introduction Large system features are: 1, the number of users are generally dealing with

  • Database design skills of 14 2009-04-20

    1. Original documents and the relationship between entities can be one-on-one, one-to-many, many-to-many relationship. Under normal circumstances, they are one-on-one relationship: the one and only original documents corresponding to the correspondin

  • 14 database design skills 2009-05-05

    1. Original documents and the relationship between entities can be one-on-one, one-to-many, many-to-many relationship. Under normal circumstances, they are one-on-one relationship: the one and only original documents corresponding to the correspondin

  • JAVA database design skills 2009-06-01

    The following 14 techniques are lot of people at many database analysis and design practice, summed up gradually. For the use of these experiences, the reader can not help bowls of Health, by rote, and to digest the understanding, seek truth from fac

  • SQL database design experience 2009-06-19

    SQL database design experience 2009-03-21 16:47 SQL database design experience A successful management system is: [50% + 50% of business software] formed, and 50% of the successful software and [25% database + 25% of the procedures] which consists of

  • Database design techniques in the 14 2009-07-15

    Database design techniques in the 14 Published: 2007-12-03 16:55 | Author: kider | Source: MySQL Community portal Author: maXiaoKe, Source: IT Experts Net / Javereserarch, Editor: LI Shu-qin, 2007-11-23 09:22 The following 14 techniques, many people

  • java database design techniques in the 14 2009-11-06

    The following 14 techniques, many people in a large number of database analysis and design practice, summed up gradually. For the use of these experiences, the reader can not help bowls of Health, by rote, and to digest the understanding, seek truth

  • Experience of database design (change) 2010-04-03

    A successful management system is: [50% + 50% of business software] is composed, while another 50% of the successful software [25% database + 25% of the programs] are composed of good and bad database design is a key. If you compared the life of the

  • [Change] java database design techniques in the 14 2010-04-06

    The following 14 tips, many people in a large number of database analysis and design practice, gradually summed up. The use of these experiences, the reader can not help rigidly students, rote memorization, and to digest understanding, pragmatic, fle

  • Database design techniques 14 2010-04-22

    1. Original documents and the relationship between entities <br /> can be one to one, one to many, many to many relationship. In general, they are one to one relationship: the one and only original documents corresponding to a corresponding entity.