关系数据库的结构外文翻译

1、Structure of the Relational databaseThe relational model is the basis for any relational database management system RDBM?SA rel ational model has three core components: a collection of objects or relations, operators that act on the ob jects or relations, and data integrity methods .In other words,

2、it has a place to store the data, a way to ere ate and retrieve the data, and a way to make sure that the data is logically consistent.A relational database uses relations, or two-dimensional tables, to store the information needed to s upport a business? Lefs go over the basic components of a tradi

3、tional relational database system and loo k at how a relational database is designed. Once you have a solid understanding of what rows, columns, tables. and relationships are, youll be well on your way to leveraging the power of a relational database?Tables, Row. and ColumnsA table in a relational d

4、atabase, alternatively known as a relation. is a two-dimensional structure us ed to hold related information. A database consists of one or more related tables.Note: Dont confuse a relation with relationships ? A relation is essentially a table, and a relationship is a way to correlate, join, or ass

5、ociate two tables.A row in a table is a collection or instance of one thing, such as one employee or one line item on a n invoice ? A column contains all the information of a single type, and the piece of data at the intersectio n of a row and a column, a field. is the smallest piece of information

6、that can be retrieved with the datab ase*s query language? For example, a table with information about employees might have a column calle d LAST_NAME that contains all of the employees* last names. Data is retrieved from a table by filterin g on both the row and the column.Primary Keys, Datatypes,

7、and Foreign KeysThe examples throughout this article will focus on the hypothetical work of Scott Smith, database d eveloper and entreprenet匚 He just started a new widget company and wants to implement a few of the b asic business functions using the relational database to manage his Human Resources

8、 HR department.Relation: A two-dimensional stnicture used to hold related information, also known as a table.Note: Most of Scotts employees were hired away from one of his previous employers, some of wh om have over 20 years of experience in the field. As a hiring incentive, Scott has agreed to keep

9、 the new employe1es original hire date in the new database?Row:A group of one or more data elements in a database table that describes a person. place, or thing-Column:Thc component of a database table that contains all of the data of the same name and type across all rows.Yoiril learn about databas

10、e design in the following sections ? but lefs assume for the moment that th e majority of the database design is completed and some tables need to be implemente?d Scott creates th e EMP table to hold the basic employee information, and it looks something like this:Notice that some fields in the Comm

11、ission COMM and Manager MGR columns do not contain a value; they are blank. A relational database can enforce the nile that fields in a column may or may not be empty. In this case, it makes sense for an employee who is not in the Sales department to have a blan k Commission field .It also makes sen

12、se for the president of the company to have a blank Manager field, since that employee doesn*t report to anyone?Field:The smallest piece of information that can be retrieved by the database que)rr language? A fiel d is found at the intersection of a row and a column in a database table?On the other

13、hand, none of the fields in the Employee Number EMPNO column are blank. The co mpany always wants to assign an employee number to an employee, and that number must be different f or each employee? One of the features of a relational database is that it can ensure that a value is entered into this co

14、lumn and that it is unique? The EMPNO column, in this case, is the primary key of the table ?Primary Key:A column or columns in a table that makes the row in the table distinguishable from every other row in the same table?Notice the different datatypes that are stored in the EMP table: numeric valu

15、es, character or alphab etic values, and date values?As you might suspect, the DEPTNO column contains the department number for the employe?e Bu t how do you know what department name is associated with what number? Scott created the DEPT tabl e to hold the descriptions for the department codes in t

16、he EMP table?The DEPTNO column in the EMP table contains the same values as the DEPTNO column in the D EPT table. In this case, the DEPTNO column in the EMP table is considered a foreign key to the same c olumn in the DEPT table.A foreign key enforces the concept of referential integrity in a relati

17、onal database ? The concept of r eferential integrity not only prevents an invalid department number from being inserted into the EMP table, but it also prevents a row in the DEPT table from being deleted if there are employees still assigned to that department.Foreign Key:A column or columns in a t

18、able that draws its values from a primary or unique key c olumn in another table? A foreign key assists in ensuring the data integrity of a tablc. Referential Integrit yA method employed by a relational database system that enforces one-to-many relationships between t ables.Data ModelingBefore Scott

19、 created the actual tables in the database, he went through a design process known as d ata modeling. In this process, the developer conceptualizes and documents all the tables for the databas?e One of the common methods for modeling a database is called ERA, which stands for entities, relation ship

20、s, and attributes? The database designer uses an application that can maintain entities, their attributes ,and their relationships. In general, an entity corresponds to a table in the database,and the attributes of the entity correspond to columns of the table?Data Modeling:A process of defining the

21、 entities, attributes, and relationships between the entities i n preparation for creating the physical database?The data-modeling process involves defining the entities, defining the relationships between those entities, and then defining the attributes for each of the entities? Once a cycle is com

22、plete? it is repeated a s many times as necessary to ensure that the designer is capturing what is important enough to go into th e database. Lets take a closer look at each step in the data-modeling process.Defining the EntitiesFirst, the designer identifies all of the entities within the scope of

23、the database application.The entit ies are the persons, places, or things that are important to the organization and need to be tracked in the database. Entities will most likely translate neatly to database tables? For example, for the first version o f Scotts widget company database, he identifies

24、 four entities: employees, departments, salary grades, an d bonuses. These will become the EMR DEPT, SALGRADE, and BONUS tables ?Defining the Relationships Between EntitiesOnce the entities are defined? the designer can proceed with defining how each of the entities is rel ated.Often, the designer w

25、ill pair each entity with every other entity and ask? Is there a relationship bet ween these two entities?11 Some relationships are obvious; some are not.In the widget company database, there is most likely a relationship between EMP and DEPT, but d epending on the business mles, it is unlikely that

26、 the DEPT and SALGRADE entities are related. If the business rules were to restrict certain salary grades to certain departments, there would most likely be a new entity that defines the relationship between salary grades and departmen?ts Tliis entity would be kn own as an associative or intersectio

27、n table and would contain the valid combinations of salary grades an d departments.Associative Table:A database table that stores the valid combinations of rows from two other tables and usually enforces a business mle? An associative table resolves a many-to-many relationship?In general there are t

28、hree types of relationships in a relational database:One-to-many The most common type of relationship is one-to-many. This means that for each occu rrence in a given entity, the parent entity, there may be one or more occurrences in a second entity, the c hild entity, to which it is related. For exa

29、mple, in the widget company database, the DEPT entity is a par ent entity, and for each department, there could be one or more employees associated with that departme nt. The relationship between DEPT and EMP is one-to-many.One-to-one In a one-to-one relationship. a row in a table is related to only

30、 one or none of the rows i n a second table? This relationship type is often used for subtyping ? For example, an EMPLOYEE table may hold the infonnation common to all employees, while the FULLTIME, PARTTIME, and CONTRA CTOR tables hold information unique to full-time employees, part-time employees,

31、 and contractors, res pectively. These entities would be considered subtypes of an EMPLOYEE and maintain a one-to-one rel ationship with the EMPLOYEE table. These relationships are not as common as one-to-many relationsh ips, because if one entity has an occurrence for a corresponding row in another

32、 entity, in most cases, the attributes from both entities should be in a single entity.Many-to-many In a many-to-many relationship, one row of a table may be related to many rows of another table, and vice versa? Usually, when this relationship is implemented in the database? a third enti ty is defi

33、ned as an intersection table to contain the associations between the two entities in the relationsh ip. For example, in a database used for school class enrollment, the STUDENT table has a many-to-man y relationship with the CLASS table ne student may take one or more classes, and a given class may

34、have one or more students. The intersection table STUDENT_CLASS would contain the combinations of STUDENT and CLASS to trackwhich students are in which classes.Once the designer has defined the entity relationships. the next step is to assign the attributes to eac h entity. This is physically implem

35、ented using columns, as shown here for the SALGRADE table as dcri ved from the salary grade entity.After the entities, relationships ? and attributes have been defined, the designer may iterate the dat a modeling many more times? When reviewing relationships, new entities may be discovered. For exam

36、 pie, when discussing the widget inventory table and its relationship to a customer order. the need for a s hipping restrictions table may arise?Once the design process is complete, the physical databasetables may be created? Logical database design sessions should not involve physical implementatio

37、n issues. but once the design has gone throug h an iteration or two, its the DBA*s job to bring the designers down to earth/1 As a result, the design m ay need to be revisited to balance the ideal database implementation versus the realities of budgets and s chedules.关系数据库的结构关系模型是任何关系数据库管理系统 RDBMS 的

38、根底。一个关系模型有二个 核心 组件：对象或关系的集合， 作用于对象或关系上的操作， 以及数据完整性规那么。 换句话说， 关系数据库有一个存储数据的地方， 一种创立和检索数据的方法， 以及一 种确认数据的逻 辑一致性的方法。一个关系数据库使用关系或二维表来存储支持某个事物所需的信息。让我们了解一下一个传统的关系数据库系统的根本组件并 U 学习如何设计一个关系数据库。一旦 你对 于行、列、表和关联是什么有了深刻理解，你就能够充分发挥关系数据库的强大功能。表，行和列：在关系数据库中， 一个表或者说一个关系 是一个用于保存相关信息的二维结 构。 一个数据库由一个或者多个相关联的表组成。注意：不要混淆

39、了关系和关联。 一个关系实际上是一个表， 而一个关联指的是一 种连 接、结合或联合两个表的方式。表中的一行是一种事物的集合或实例，比方一个员工或发票上的一项。表中的一列包含了一类信息；而且行列交义点上的数据，字段，即是能够用数据库查询语言检 索到的 最小片信息。 举个例子来说， 一个员工信息表可能有一个“名字列， 列中就包 含所有员 工的名字。数据是通过对行、列进行过滤而从表中检索出来的。主码、数据类型和外码本篇文章均以假设的斯科特 ?史密斯的工厂为例，他是数据库的建立者和企业的 主办 人。他刚开办了一个饰品公司并口想要使用关系数据库的儿项根本功能来管理人力资源部门。关系：用来保存相关信息的一

40、个二维结构也就是表。 注意：大多数斯科特的雇员都是雇自过去的从业者，他们中有些人在这个领域己经有 20 年的经验了。出于雇用的口的，斯科特同意在新数据库中维持新进员工最初的雇佣日期。行：在一个数据库表中的一组单数据或多数据元素， 用于描述一个人、 地方或事 物。 列：列是数据库表的组件，它包含所有行中同名和同类型的所有数据。你会在下面章节学到如何设汁数据库，现在让我们假设数据库大局部己经设汁完 并且有一些表需要被执行。斯科特创立了 EMP 表来保存根本的员工信息，就像这 样： 你可能注意到佣金列和管理人列中有一些单元格中没有值；它们是空值。一个关 系 数据库能够规定列中的一个单元格是否为空。

41、如此，可以明确那些非销售部的员工 佣金单 元为空。 同样也明确了公司董事长的管理人单元为空， 因为这个员工不需要向 任何人汇报 工作。单元格：是数据库查询语言所能够检索到的最小片信息。一个单元格就是一个数据库表的行和列交叉形成的。另一方面，没有哪个员工的员工编号单元为空。公司总是希望为每个员工分配一个员工号， 并訂这个号码必须是每个员工都不同的。 关系数据库的一个特性能够确定 某列的 键入值必须为单值。如此，员工编号列便是这个表的主码。主码：主码即是表中的一列或多列，使每一行能够区别于同表中的其他行。留意一下 EMP 表中存储的不同数据类型：数值型，字符型或字母型，以及日期型。如你所想，部门成

42、员列保存的是员工所在部门的编号。但是你如何知道哪个部门名称对应哪个部门编号呢？斯科特建立了 DEPT 表来具体描述 EMP 表中提到的部门 编号 的情况。EMP 表中的部门编号列同 DEPT 表中的部门编号列有着相同的值。既然如此， EMP 表中的部门编号列便被看作是与 DEPT 表中相同列对应的外码。外码加强了关系数据库中参考完整性的概念。参考完整性的概念不只可以阻止无 效 的部门编号被插入 EMP 表中，而且在某部门仍有员工的情况下，可以防止 DEPT 表中该 部门的信息被删除。外码：表中的一列或多列，它的值来自于其他表的主码列或单值列。一个外 码有 助于确定表中数据的完整性。参考完整性：

43、是关系数据库用来加强表间一对多关联的一种方式。数据建模 在斯科特于数据库中创立真实表之前，他要经过一个称作数据建模的过程。在这 个 过程中，数据库创立者定义和填写数据库中所有表。 有一种为数据库建模的方式叫 作 ERA, 它可以表示出实体、实体间的关联和实体的属性。数据库设计者使用一个能 够支持实体、 实体属性和实体间关联的应用程序。通常，一个实体对应数据库中的一 个表，而实体的属 性对应于表中的列。数据建模：一个定义实体、实体属性和实体间 关联的过程，从而为建 立物理数据库做准备。数据建模过程包括定义实体、定义实体间关联以及定义每个实体的属性的过程。旦一个周期完成，就需要不断重复直到设计者抓住