Database design of computer room reconfiguration

First, draw ER diagram

E-r graphs are also called entities - contact Diagrams (Entity Relationship Diagram), which provide methods for representing entity types, attributes, and connections. A conceptual model used to describe the real world.

Drawing method:

⑴ determining all the entity collections

⑵ Select the attributes that the entity set should contain

⑶ determining the linkage between entity sets

⑷ determines the keywords for the entity set, using underscores to indicate the attribute combination of the keyword on the attribute

⑸ determine the type of contact, when the line will represent the contact of the diamond box to the entity set, next to the online note is 1 or n ( more) to indicate the type of contact

Second, the ER diagram into a relational model

1:1 : For example cardinfo and studentinfo are 1:1 of the relationship

Cardinfo(cardno,balance,cardstate)

Studentinfo (Stuid,stuname,studept,stusex,stuclass,,stustate)

After conversion

Studentinfo (Stuid,cardno,stuname,studept,stusex,stuclass,,stustate)

Or

Cardinfo(cardno,stuno,balance,cardstate)

That is, adding the primary key to either party can

1:n : For example UserInfo and cardinfo are on the registration side is 1:n the Relationship

UserInfo(userid,password,username,userlevel)

Cardinfo(cardno,balance,cardstate)

After conversion

Cardinfo(cardno,userid,balance,cardstate)

The primary key is placed on the n - side

N:m : For example UserInfo and cardinfo are in the recharge aspect is n:m Relationship ( One user can recharge multiple cards, one card can be recharged by multiple users )

Cardinfo(cardno,balance,cardstate)

UserInfo(userid,password,username,userlevel)

After conversion

Cardinfo(cardno,balance,cardstate)

UserInfo(userid,password,username,userlevel)

Recharge (Cardno,userid,money,optime)

Add a new relational schema and add two primary keys to the new relational schema, respectively

Third, standardize the database with three normal forms

1. First paradigm (atomicity)

The system does not find a good example, just give an example to illustrate the problem

Non-first paradigm relationship

Department Name	Number of senior titles
	Professor	Associate professor
Computer Department	6	10
Department of Information Management	3	5

The first paradigm Relationship

Department Name	Number of Professors	Number of associate professors
Computer Department	6	10
Department of Information Management	3	5

The number of senior titles in the first table is not an atomic attribute, and he also includes two sub-values

2. The second paradigm (there is no local dependency)

Recharge (Cardno,userid,money,optime)

In this recharge relationship,Cardno,userid decided to OpTime, so they combined the key, but Cardno himself can decide Money , so there is a local dependency.

Optimized for

Recharge (Cardno,userid,optime)

Balance (Cardno,money)

That meets the requirements of the second paradigm

3. The third paradigm (without transfer dependency)

No suitable examples are found in this system, and other examples illustrate this problem

Student(stuno,studept,studeptteacher)

Stuno can decide studept

Studept can decide studeptteacher

So there is a transitive dependency

After optimization

Student(stuno,studept)

Dept (Studept,studeptteacher)

4. The relationship between the three paradigms

Satisfying the third paradigm must satisfy the first and second paradigms

Database design of computer room reconfiguration