The most complete Oracle-sql notes (8)

1.8. Database Design Paradigm

The database design paradigm is actually very important, but from the actual development point of view, if it is really all in accordance with the paradigm, then this program can not be written, including query statements will become complex.

All of the Scott users ' tables in Oracle, in fact, have been a good reflection of a design idea, employee-department relationship.

1) First Paradigm

For example, now assume a database creation script like the following

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 (1111, ' Zhang San ', ' November 23, 1983 births, now the address is: Beijing Xicheng District ..... ‘);

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

|-Birthday: January 23, 1983

|-Provinces: Beijing

|-area: Xicheng District

|-Detailed information: ...

Each field is not re-divided, so the database creation script above 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 looks like each field is non-divided, but we should be aware that in some Web site registration, users will be asked to enter the "last Name" and "First name", so the above design can be modified as follows:

CREATE TABLE Person (

PID Number (4) primary key NOT NULL,

Surname 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 cannot be divided.

2) Second Paradigm

The requirements of the first paradigm are very simple and ensure that each field is meaningful. But if all operations use the first paradigm, then there is a problem:

Now set up a student selection schedule: School number, name, age, course name, score, credits

CREATE TABLE Selectcourse (

Stuno VARCHAR2 (50),

Stuname VARCHAR2 (50),

Stuage number,

CNAME VARCHAR2 (50),

Grade number,

Credit number

);

The above script conforms to the requirements of the first paradigm, but if it is designed according to the first paradigm, there are problems:

INSERT into selectcourse values (' s001 ', ' Zhang San ', +, ' JAVA ', 89,0.3);

INSERT into selectcourse values (' s001 ', ' John Doe ', ' JAVA ', 78,0.3);

INSERT into selectcourse values (' s001 ', ' Harry ', +, ' JAVA ', 80,0.3);

INSERT into selectcourse values (' s001 ', Zhao Liu ', +, ' JAVA ', 90,0.3);

As you can see from the database scripts above, all of the course information is redundant and the following problems exist:

|-If a course does not have a student's choice, then the achievement is completely gone from the school

|-The course itself should also contain a number of courses, but if you follow the above design, the course number must be repeated

|-If you want to change course information, you want to change many records

We use the second paradigm to modify the database script:

|-Student is an entity--student table

CREATE TABLE Student (

Stuno varchar2 () primary key NOT NULL,

Stuname VARCHAR2 (50),

Stuage number

);

|-curriculum should also be an entity--curriculum

CREATE TABLE Course (

CID number (5) primary key NOT NULL,

CNAME VARCHAR2 (50),

Credit number

);

|-Student Selection Course information is also an entity--students choose a timetable

CREATE TABLE Selectcourse (

Stuno VARCHAR2 (50),

CID number (5),

Grade number,

Join the foreign Key Association, because the student is gone, the result is gone, because the course is gone, the result is gone

);

The above design solves the following problems:

|-The course information does not disappear when students do not choose a class

|-Update the curriculum directly when updating the course

|-All related relationships are reflected in the relational table.

3) Third Paradigm

In the actual development, the use of the third paradigm is the most.

For example, it is now required to design a student's table, including the number, name, age, institution, college address, and college phone, which must not be used at this time, but what if the second paradigm is used now?

CREATE TABLE Student (

Stuno varchar2 () primary key NOT NULL,

Stuname VARCHAR2 (50),

Stuage number

);

CREATE TABLE Collage (

CID Number (4) primary key NOT NULL,

The CNAME varchar2 () Not is not NULL,

CAddress VARCHAR2 (+) not nul,

Ctel VARCHAR2 (+) NOT NULL

);

CREATE TABLE Studentcollage (

Stuno VARCHAR2 (50),

CID number (4),

Setting the primary-foreign key relationship

);

According to the design above, a student can simultaneously attend multiple colleges at the same time, multiple colleges will have the same student at the same time, the best thing is: A college contains multiple students, a student belongs to a college, in fact, this design is completely similar to the Department and employee table design structure.

CREATE TABLE Collage (

CID Number (4) primary key NOT NULL,

The CNAME varchar2 () Not is not NULL,

CAddress VARCHAR2 (+) not nul,

Ctel VARCHAR2 (+) NOT NULL

);

CREATE TABLE Student (

Stuno varchar2 () 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 sole principle of the database:

|-fewer associated queries for database tables the better, the lower the complexity of the SQL statement, the better.

1.9. Database design Tools

In fact, the database also has its own design tools, more commonly used is Sybase's powerdesigner development tools, this tool can easily do a variety of design, start, you can use this tool, database modeling design.

After starting PowerDesigner, select New, Physical Data Model, select Oracle Database

The following uses the PowerDesigner tool to restore the Dept and EMP Tables in Oracle

Create a table--the operation of the main-foreign key in the tool--after the relationship is obtained, the database script can be created by the PowerDesigner tool.

1.10. Database design Analysis

1) Requirements

Design requirements, require the design of an online shopping program (using PowerDesigner to build models and write test data), with the following requirements

|-Admin can add items in the background, each item belongs to a product group

|-administrators can be grouped, each group is individually authorized, that is, one administrator group can have multiple administrators, one administrator group has multiple permissions, one administrator can more than one group

|-users can buy their own goods, the purchase of goods to add information in the order form, a user can buy multiple products at the same time, users can choose their own region for the delivery of goods

|-users can discount items based on their points of purchase

2) Realization

According to the first requirement, a product belongs to a commodity group, then a "one-to-many" relationship should be established at this time

Depending on the second requirement, administrators can be grouped, administrator tables, Administrators group tables, permission tables, administrators-Administrators group tables, Administrators groups-permissions table

There's a relationship between the administrator and the commodity table.

A user table is required, with a regional table, a sub-region table, an order form, an Order Detail table, an integration table, and a relationship

Under normal circumstances, an order will certainly be in accordance with the format shown above, then ask, so how many tables to query?

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

|-Region Table-sub-region table (user area)

|-order form, Order Details table (total price, order date, zip code)

This query requires a simultaneous query of 6 tables. All of the code in this program is done in accordance with the standard paradigm, so the above problem arises at this point.

The method of reducing multi-table queries in development can be done with redundant data. </pre>

<p> </p>

<pre name= "code" >oracle notes

1

This article is from the "lake and Laughter" blog, please make sure to keep this source http://hashlinux.blog.51cto.com/9647696/1792707

The most complete Oracle-sql notes (8)