Transformation of E-R Model of database design into relational model

Source: Internet
Author: User

Personal reconstruction version of the computer room charge system needs to re-design the database, then how to design the database? This is also our self-examination in an important course "database principle", for the students who have already benefited from this subject, can be used to learn directly. I took this book in October this year, so let's practice it first.

Originally seen in the Jiang Jianling teacher's video, which focuses on the database inside the specific operation, there is no way to relate to the database design is an indispensable step from the conceptual model to the transformation of the logical model. Go to the Chase:

Background: Data System lifetime

We refer to the database system lifetime from the beginning of the planning, design, implementation, maintenance to the end of the entire period of time when the new system is replaced. This lifetime can generally be divided into seven stages: planning, demand analysis, conceptual design, logical design, physical design, implementation, operation and maintenance. Today's focus on conceptual design to the transformation of logical design.

More about e-r in Wikipedia

Based on the computer room charge system, the global conceptual model is drawn.

to convert a e-r diagram into a relational model:

Step one: Convert three entity types to three modes:

Student (Studentno, Studentname,sex,academy,major,grade,class)

Card (CardID, Money)

User (UserID, Password,level)

The second step: for the 1:1 relationship "use", you can add a number in the "card" mode (the number is a foreign key). The three patterns that are obtained in this first step are as follows:

Student (Studentno, Studentname,sex,academy,major,grade,class)

Card (CardID, Money,Studntno)

The third step: the relationship between the m:n, the formation of new relations

Registe (cardid,studentno,userid, Note,date,time)

Cancelcard (cardid,studentno, Cancelcash,date,time,userid,status)

Charge (cardid,studentno, Addcash,date,time,userid)

Online (Cardid,studentno,onlinedate,onlinetime,computer)

Outline (cardid,studentno, Outelinedate,outlinetime,concumetime,concume,)

The last 8 relationship patterns are formed:

T_student (studentno, Studentname,sex,academy,major,grade,class)

T_card (CardID, Money,Studntno)

T_user (UserID, Password,level)

T_registe (cardid,studentno,userid, Note,date,time)

T_cancelcard (cardid,studentno, Cancelcash,date,time,userid)

T_charge (cardid,studentno, Addcash,date,time,userid)

T_online (Cardid,studentno,onlinedate,onlinetime,computer)

T_outline (cardid,studentno, Outelinedate,outlinetime,concumetime,concume,)

The following relational model is designed according to the system function:

T_worklog (UserID, Onlinedate,onlinetime,outelinedate,outlinetime)

T_basicdate (Rate,tmprate,unittime,leasttime,preparetime,limitcash,head,date,time) (about the report's table needs to be updated)

This process let me understand that the database table is not imaginary, but also not according to the system function need what I design what field, but now according to the planning phase of the database design and the requirements analysis phase of the user needs and feasibility analysis to derive entities, Contact, after the attribute design table, apply to the system function and then design the view according to the function. The table is designed according to the above steps, reducing coupling and also conforming to the three paradigms (first paradigm: non-re-divided, second paradigm: preventing local dependence, third paradigm: eliminating transitive dependencies)

PS: Beginners, if there is a mistake to correct!


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.