Designing Database Steps

Designing a database requires a clear three point

1, Identity Entity (entity) identifies the key object or entity (noun) to be managed by the database

Equivalent to the name of the table

2, identify the attributes of each entity (Attribute) (noun)

Columns equivalent to tables

3. Identify the relationship between entities (relationship) (verb)

Relationships between tables, primary keys and foreign key relationships

In the E-r diagram, the entity is represented by a rectangle, the attribute is represented by an ellipse, and the relationship is represented by a diamond.

The relationship between tables

1. Single-to-one: an entity in X is most aligned with an entity in Y, and an entity in Y is associated with at most one entity in X. Eg: A person has only one ID card.

denoted by 1:1

2. One-to-many: an entity in X can be associated with any number of entities in Y; An entity in Y is associated with at most one entity in X. Eg: There are many students in a class.

expressed in 1:n

3. Many-to-many: an entity in X can be associated with any number of entities in Y, and vice versa. Eg: The relationship between students and the curriculum, a student can have multiple courses, a course can correspond to more than one student.

expressed in M:n

Cases:

4, a hospital ward Computer Management Center needs the following information: Department: Department name, Branch address, telephone, doctor name Ward: Ward number, bed number, the department name of the Doctor: name, title, department name, age, ID number of patients: Medical record number, name, gender, diagnosis, competent physician, ward number of which, A ward can only belong to a department, a department may have multiple wards, a doctor only belong to a department, a department can have more than one doctor, a doctor can be responsible for the diagnosis and treatment of multiple patients, a patient's competent doctor only one. A patient can only stay in one ward, and a ward may accommodate multiple patients. Complete the following design: (1) the E-r Diagram (2) of the Computer Management system is designed to convert the E-r diagram into a relational schema structure.

E-r Chart

Focus

1. If it is a 1:1 relationship: then convert the entity to a table, the primary key of any 1-side entity to the other end of the entity to do foreign keys.

2. If the relationship is 1:n: then the entity is converted into a table, the relationship is not a table, the 1-side entity's primary key to the N-side entity to do foreign keys.

3. If the relationship is m:n: convert the entity into a table, the relationship forms a table, and the primary key of the two entities is taken as the foreign key of the table, forming a composite primary key.

Three paradigms of database design

In order to build a database with small redundancy and reasonable structure, we must follow certain rules when designing database. In a relational database, this rule is called a paradigm. A paradigm is a summary of a design requirement. In order to design a relational database with reasonable structure, a certain paradigm must be met.

First paradigm: Ensuring that each column remains atomic

Requires that each field of the table must be an indivisible independent unit

Second paradigm: Make sure that each column in the table is associated with the primary key

On the basis of the first paradigm, each table is required to express only one meaning. Each field of the table is dependent on the primary key of the table.

Third paradigm: Ensure that each column is directly related to the primary key column, rather than indirectly related

On the basis of the second paradigm, all fields other than the primary key of each table are required to directly determine the dependency relationship with the primary key.

Designing Database Steps