Summer self-study day 12 | database (V) – multi table, database design

Time:2021-10-23

Multi table relation

  • One to one: person and ID card
  • One to many: departments and employees: one department, multiple employees
  • Many to many: students and courses

one-on-one

  • You can add a unique foreign key on either side to point to the other’s primary key
  • Generally, one-to-one relationships are merged into one table

One to many

  • Establish association: establish a foreign key in one of the multiple parties, and point to the primary key of one party
  • For example, establish a foreign key in the employee table, pointing to the Department’s primary key

Many to many

  • Establish association: an intermediate table is required. The intermediate table contains at least two fields – the primary keys of the two tables. The union primary key of the intermediate table needs to be unique.
  • An example:
    #Intermediate table

Database design paradigm

To comply with the following paradigm requirements, you must first comply with the previous paradigm requirements.

First paradigm

Each column is an indivisible atomic data item.

That is, a column cannot contain one or more child columns.

Second paradigm

On the basis of the first normal form, the non code attribute must be completely dependent on the candidate code (eliminating the partial dependence on the basis of the first normal form)

  • Function dependency: the value of attribute B can be determined by the value of attribute a or (A1, A2) attribute group, a – > B.
    • Student number – > name, (student number, course) – > grade
  • Full functional dependency: determining the value of B attribute depends onA attribute groupThe values of all properties in the.
    • (student number, course) – > grade
  • Partial functional dependency: determining the value of B attribute only depends onA attribute groupThe value of some property in the.
    • (student number, course) – > name
  • Transfer function dependency: a – > b, B – > C
    • Student number – > department name – > Dean
  • Code: an attribute or primary attribute is completely dependent on all other attributes.
    • Primary attribute: the attribute in the attribute group of the code
    • Non primary attribute: all attributes except code

Third paradigm

Any non primary attribute does not depend on other non primary attributes

  • For example, in the “student number, name, Department, department head” table, the department head depends on the Department name, and the Department and department head are non primary attributes in this table, so the design of this table is unreasonable.

This work adoptsCC agreement, reprint must indicate the author and the link to this article