Shulou(Shulou.com)06/01 Report--

1.8. Database design paradigm

The database design paradigm is actually very important, but from the perspective of actual development, if you really follow the paradigm, the program cannot be written, including query statements will become complex.

All the tables of scott users in Oracle actually reflect a good design idea, employee-department relationship.

1) the first paradigm

For example, suppose you have the following database creation script

Create table person (

Pid number (4) primary key not null

Name varchar2 (50)

Info varchar (200)

);

Insert the following test data

Insert into person (pid,name,info) values was born on November 23,1983 and now lives in Xicheng District, Beijing.

In fact, for people, it consists of the following parts:

|-Birthday: January 23, 1983

|-provinces and cities: Beijing

| |-area: Xicheng District |

|-detailed information:.

Each field can no longer be divided, so the above database creation script is modified as follows:

Create table person (

Pid number (4) primary key not null

Name varchar2 (50)

Birthday date

Area varchar2 (200)

Subarea varchar2 (200)

Address varchar2 (200)

);

This design seems that each field can no longer be divided, but we should notice that in the registration of some websites, users are required to enter "last name" and "first name" respectively, so the above design can be modified as follows:

Create table person (

Pid number (4) primary key not null

Last name varchar2 (50)

Name varchar2 (50)

Birthday date

Area varchar2 (200)

Subarea varchar2 (200)

Address varchar2 (200)

);

Therefore, when designing table fields, it is best to ensure that each field can no longer be divided.

2) the second normal form

The requirements of the first paradigm are very simple, ensuring that each field is meaningful. But if all operations use the first normal form, there will be a problem:

Now set up a list of students' courses: student number, name, age, course name, grades, credits

Create table selectcourse (

Stuno varchar2 (50)

Stuname varchar2 (50)

Stuage number

Cname varchar2 (50)

Grade number

Credit number

);

The above scripts meet the requirements of the first paradigm, but there will be problems if they are designed according to the first paradigm:

Insert into selectcourse values ('s001century recorder' Zhang San', 21recorder JAVANGBEI 89pr 0.3)

Insert into selectcourse values ('s001century recorder' Li Si', 20mineJAVANGBELER 78pr 0.3)

Insert into selectcourse values ('s001 magic recorder' Wang Wu', 23 recordJAVANGBEI 80 Phenol 0.3)

Insert into selectcourse values ('s001century, Zhao Liu', 22phathjavajiajie 90pr 0.3)

From the above database script, you can find that all the course information is redundant, and there are the following problems:

|-if no student chooses a course, the achievement disappears completely from the school.

|-A course number should be included in the course itself, but if you follow the above design, the course number must be duplicated.

|-if you want to change the course information, you need to change many records.

We use the second normal form to modify the database script:

| |-Student is an entity-- Student Table |

Create table student (

Stuno varchar2 (50) primary key not null

Stuname varchar2 (50)

Stuage number

);

|-- course should also be an entity-- course schedule

Create table course (

Cid number (5) primary key not null

Cname varchar2 (50)

Credit number

);

|-Student course selection information is also an entity-student course selection schedule

Create table selectcourse (

Stuno varchar2 (50)

Cid number (5)

Grade number

Join the foreign key association, because the students are gone, the grades are gone, because the courses are gone, the grades are gone.

);

The above design solves the following problems:

|-when students do not take courses, the course information will not disappear.

|-you can update the course schedule directly when updating the course

|-all relationships are reflected in the relationship table.

3) the third paradigm

In actual development, the third paradigm is the most frequently used.

For example, if you are now required to design a student table with student number, name, age, university, college address, and college telephone number, you certainly cannot use the first paradigm at this time, but what if you use the second paradigm now?

Create table student (

Stuno varchar2 (50) primary key not null

Stuname varchar2 (50)

Stuage number

);

Create table collage (

Cid number (4) primary key not null

Cname varchar2 (50) not not null

Caddress varchar2 (200) not nul

Ctel varchar2 (200) not null

);

Create table studentcollage (

Stuno varchar2 (50)

Cid number (4)

Set the primary-foreign key relationship

);

According to the above design, a student can attend classes in multiple colleges at the same time, and multiple colleges will have the same student at the same time. At this time, the best practice is that a college contains multiple students, and one student belongs to one college. In fact, this design is completely similar to the design structure of the department and employee table.

Create table collage (

Cid number (4) primary key not null

Cname varchar2 (50) not not null

Caddress varchar2 (200) not nul

Ctel varchar2 (200) not null

);

Create table student (

Stuno varchar2 (50) primary key not null

Stuname varchar2 (50)

Stuage number

Cid number (4)

Establish a primary-foreign key relationship

);

The design is a very clear one-to-many relationship design.

The only principle of the database:

|-the fewer associated queries of database tables, the better, and the lower the complexity of SQL statements, the better.

1.9. Database design tools

In practice, the database also has its own design tools, the more commonly used is the Sybase PowerDesigner development tool, this tool can easily do a variety of design, after startup, you can use this tool to model and design the database.

After starting PowerDesigner, select New, Physical Data Model, and select Oracle database

Let's use the PowerDesigner tool to restore the dept and emp tables in Oracle

After creating the table-- manipulating the primary-foreign key in the tool-- and getting the relationship, you can use the Powerdesigner tool to create the database script.

1.10. Database design and analysis.

1) requirements

Design requirements, require the design of an online shopping program (using Powerdesigner to build a model and write test data), there are the following requirements

| |-the administrator can add goods in the background. Each item belongs to a product group. |

| |-administrators can be grouped and each group can be authorized separately, that is, an administrator group can have multiple administrators, an administrator group can have multiple permissions, and an administrator group can have multiple groups. |

| |-users can purchase goods on their own. When purchasing goods, you need to add information to the order form. A user can purchase multiple goods at the same time, and users can choose their own region to deliver goods. |

| |-users can discount goods according to their purchase points. |

2) implementation

According to the first requirement, if a commodity belongs to a commodity group, an one-to-many relationship should be established at this time.

According to the second requirement, administrators can be grouped, including administrator table, administrator group table, permissions table, administrator-administrator group table, administrator group-permissions table.

There should also be a relationship between administrators and commodity tables.

A user table is required, which is related to a region table, a sub-region table, an order table, an order details table and a points table.

Under normal circumstances, an order must be displayed in the above format, so how many tables do you have to query?

|-user table (user name, user phone number, user address)

| |-region table-Sub-region table (user region) |

|-order form, order details form (Total Price of goods, date of order, Postal Code)

This query needs to query 6 tables at the same time. All the code in this program is completed in accordance with the standard paradigm, so the above problem arises at this time.

The method of reducing multi-table queries in development can be accomplished by redundant data.

Oracle notes

one

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.