关系数据库的结构

1、感柏谝器醢邝铱愈郑斯矽芋岫胼Structure of the relational database亏瞍瞠豫杲旃嬲柙鑫墟倥噢牦叵The relational model is the basis for any relational database management system (RDBMS).A relational model has three core components: a collection of objects or relations, operators that act on the objects or relations, and data integri

2、ty methods. In other words, it has a place to store the data, a way to create 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 support a business. Lets

3、 go over the basic components of a traditional relational database system and look 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.如齄禁蕾陵氩谇呶耿喹

4、阗匾萍杳Tables, Rows, and Columns涤嚎笤实舴冯疮瘕笕鳄粝按图浚A table in a relational database, alternatively known as a relation, is a two-dimensional structure used to hold related information. A database consists of one or more related tables.坏奖待甘素霓卅蹴麟懔逻茁葭拗Note：Dont confuse a relation with relationships. A relation

5、 is essentially a table, and a relationship is a way to correlate, join, or associate two tables.豌晚郏懒蒂辉哟颚昏孝蔡构芗捐A row in a table is a collection or instance of one thing, such as one employee or one line Item on an invoice. A column contains all the information of a single type, and the piece of data

6、 at the intersection of a row and a column, a field, is the smallest piece of information that can be retrieved with the databases query language. For example, a table with information about employees might have a column called LAST_NAME that contains all of the employees last names. Data is retriev

7、ed from a table by filtering on both the row and the column.嗌铨懒蟪栋壕荆翁勤哂凳明姬井Primary Keys, Data types, and Foreign Keys祗謇褴猷仨萁酷挲蝎螽州朋裁瞪The examples throughout this article will focus on the hypothetical work of Scott Smith, database developer and entrepreneur. He just started a new widget company and wan

8、ts to implement a few of the basic business functions using the relational database to manage his Human Resources (HR) department.涠罱蓑跆傺烃头圈蚌警官鲨恪窨Relation：A two-dimensional structure used to hold related information, also known as a table.坝抠瓞殍祺聱危碣厢璇放儆奘练Note：Most of Scotts employees were hired away fro

9、m one of his previous employers, some of whom have over 20 years of experience in the field. As a hiring incentive, Scott has agreed to keep the new employees original hire date in the new database.鲈涎钉扑宄壕鹑檬褡求兆硌线堪Row：A group of one or more data elements in a database table that describes a person, pl

10、ace, or thing.颇阗步绨尺舡芫膛削寨钤目牌衫Column：The component of a database table that contains all of the data of the same name and type across all rows.扛摁纫珊霁泼酶骶沸跋从魂祭孑Youll learn about database design in the following sections, but lets assume for the moment that the majority of the database design is completed

11、 and some tables need to be implemented. Scott creates the EMP table to hold the basic employee information, and it looks something like this:坦坯徇抖生吵蹈轿澶牢陇筲策玮EMPNO 瞵哟绾唇氖断嗾钨阒逝祠锴溱蠼ENAME蚌睬幛癯竞陋濑迕瓶跄咱己急谁 JOB穹生瀚哀尸旰良媛幌贪鲆互姗槊MGR休戋挞标厨渭涂麽掣卟嵩铺杵吝HIREDATE劝璺玩锖傅髯熄彗柔猜耿攘姥髅SAL塑醑铽躐蝠黥诂衍涨糠獯裤矾榨COMM空宅白裎褚鸽恁铩督粽渝姘蓟逵DEPTNO术旒嘁绚仑怙裸

12、镯师弧级苔槟酸7369犷炝睬利芳汗铕燥苟栎窟考禾粕 SMITH葬哙溻甜胭脱侑际蹭鉴舭喹肼蟓 CLERK闼晚饰艚唰姐抽盾鸿胰稗舸椠卵7902监噻阜似欤氟溏炎敞次骜塥帼煅17-DEC-80袁綮髭佶洽劣煤滏肄价务咬矗侑 800潜晌仞纳排帚烊蝙躔浩酵睫洒鳋鼙鞠吝酞逄阂冀央芫豁甍压识湾20檐猎封晗叹浈椿嫔慎殷雌僵暂杲7499妓处缸句髻濯罕惹歆铯频谴琉任ALLEN鹾威贡鞣嗯钆锪挖拎幂邀抠怂峪SALESMAN绳剔标拦骖欣瘿亿菇菝石塔睇春7698孤苍的鲔溴簿矣严湄屣七瑟脎矽20-FEB-81豚般篷轹乞学衄竞饫跫蒜瓮钲粪1600杯锘寒檫氚捶鸾恐报咆啼祝廒渡300萌爿窑诟剐垧师巳俞这薏圃溲检30倬妹迹戬俎瞅尔杼轮

13、揣龚爰鲭榆7521圮佩娑钲冕开狗癌柽昀抬鼍呕誊WARD残奘缜撩罴锌守桶弘恶爨跋逮楫SALESMAN霸嗔临秧痼蟪扈岈荫狄帑佾瓮毯7698财揭蓉鸟灸身陶鲮蘸枫鞍赤训扣22-FEB-81页角酥髌堰士束混谋钥剪押苡筘1250讵瑟者浚假枰耪荛扯荩濑莘遣彰500秽浸直坎锼醉佝馏延珠悲伊潜拭30葑沟榈幄痈阒墼休拳剔苄簖痛笾7566脖耩碰渠鼯曾癯撤楗葶涮蘅哚审JONES返忿诧堠佶罢本芎鲁劣蓄超的氓MANAGER禹蹙恪甾颌瞵埠逗涤擀赜唰悼晦7839昔冥洹萁款踱仍缄疚估关瘕樊干02-APR-81脏歼年忆杜悔琚岗籼烈韧由镞肉2975酪坳淄邵羌袄隰源酩恹犊滞圉陷扑靶补鼎洛濠确蔗鬏枚妊聿皈谢20谘痛闫败摇柳莆鹬氓浙郝鼹

14、柚仂7839绔验西解恫糟邝绿诉烙纣剀薰卑KING沐蛊杠毁甥否庙千蠢棉懦艰属欺PRESIDENT榔摊罗毙痢梦拼峪炳噔躇堑苌葺趸姥锚僻聚糕滠圃笃鲴吻藩夥瞢17-NOV-81捆鳘皈旁卿铸戏浅愚绡侠绒鳊汀5000脲酐沂岛螈欢耗布千桐浸良屉锛琉鲼蔽矗喃罨矧肾之撬遮坊鞣弛10黛弪沁忱吴铊宄瑁後稀恂烁巷须7902拷糖锖惭陌汜涣倥涝蟛士碎鳎芑FORD怆固剩俯旗逅央玛娼薨拮咝跪勋ANALYST抓欢弭什殉汨蕊尔党苦薯罕匚卉7566泡硅潭绥义趄候鹩煜蝻莳唤邹遑03-DEC-81美录蔓夥郭楠量咩琥眶偷颂偌坞3000逯双窟每蛳惰磋辊恪苔鞅悴蹇结抟镆穴扉丑侍终膝渥连庀鹑喳舳20航队腐篡粹缔鲳宾猞驿年眭崩滞Notice t

15、hat some fields in the Commission (COMM) and Manager (MGR) columns does not contain a value; they are blank. A relational database can enforce the rule that fields in a column may or may not be empty. 3In this case, it makes sense for an employee who is not in the Sales department to have a blank Co

16、mmission field. It also makes sense for the president of the company to have a blank Manager Field, since that employee doesnt report to anyone.酥蓝腻怯蕞庳永稀颧丨猥斤堵科Field：The smallest piece of information that can be retrieved by the database query language. A field is found at the intersection of a row an

17、d a column in a database table.埭断跪琛距诗喂氐杖嘞疤簇曹冀On the other hand, none of the fields in the Employee Number (EMPNO) column are blank. The company always wants to assign an employee number to an employee, and that number must be different for each employee. One of the features of a relational database

18、is that it can ensure that a value is entered into this column 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.揠泽庋背

19、五甑榷栩贰迁缯递曛擅Notice the different data types that are stored in the EMP table: numeric values, character or alphabetic values, and date values.衫瘤飚耻扉锣侨普裹逖挎黎恙名As you might suspect, the DEPTNO column contains the department number for the employee. But how do you know what department name is associated wi

20、th what number? Scott created the DEPT table to hold the descriptions for the department codes in the EMP table.丹氐茂逶佚睥衽墓隶瞰颐瑰滦帽DEPTNO滞儆淮鸺七肷创溶殍脾汩怫命馓DNAME胍跟啥脚瑰侃融崴熳蕊螨余伏艹 LOC诽内墩舁之钼蟪觚击秧尔逄几痖10茇镓绚嫉宫尬耽吗玫苏沫霏够丸ACCOUNTING豹坜螬逭翘扪哆诃菝轸历为凑NEW YORK嫂哽圉志性盏禺崩棒洲努鬯氢盾20犁锋坯枵笃纤诏谯坳时羟局储袁RESEARCH嗯趵獍僬僬恰瞿玩坚剪凳铍舣胱DALLAS莜橄虐皎缈芑流速紫佾患斡

21、缓款30统顾蔚陇湃跑豺鹩谕颧铥吹谱贲SALES坐底嗒牢仄弄穹浑疖嘞芒尊减础CHICAGO墓安道俯比剥鄣典郯缜袢阏洇底40仝槁康首蒴超铿鸹夥忪梦狞洽鲐OPERATIONS赦痰涨仂纣脬芰寸经团姚穑嘭喂BOSTON胀诗鼯姗橇酆骀堡巧思舰吁蕤拦The DEPTNO column in the EMP table contains the same values as the DEPTNO column in the DEPT table. In this case, the DEPTNO column in the EMP table is considered a foreign key to th

22、e same column in the DEPT table.濒应泸苁驹瑁伪茈梭铁舷通糈獭A foreign key enforces the concept of referential integrity in a relational database. The concept of referential 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

23、 from being deleted if there are employees still assigned to that department.三缂帑浆毯骼畏耗墩啊宴鹱对祢Foreign Key：A column (or columns) in a table that draws its values from a primary or unique key column in another table. A foreign key assists in ensuring the data integrity of a table.彳直蟹嘱芝兀阿乒闸鞲遛拘陕阊Referentia

24、l Integrity：A method employed by a relational database system that enforces one-to-many relationships between tables.呦妒悻侩崃气邱瘳雒仁枇来赝捞Data Modeling烃埙栋虽氕诙宏囫戛蒲螨氤缺娇Before Scott created the actual tables in the database, he went through a design process known as data modeling. In this process, the develope

25、r conceptualizes and documents all the tables for the database. One of the common methods for modeling a database is called ERA, which stands for entities, relationships, and attributes. The database designer uses an application that can maintain entities, their attributes, and their relationships.

26、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 entities, attributes, and relationships between the entities in preparation for creating the physical database.覆憋卡溉紫

27、菇己泺氚邳贽曛芄已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 complete, it is repeated as many times as necessary to ensure that the designer is capturing what is import

28、ant enough to go into the database. Lets take a closer look at each step in the data-modeling process.貅约扔尿愍魏胥蝥涯原檗馆脲陨Defining the Entities睹弊辽胂熘鲒愉汨翱摔怠磨寰里First, the designer identifies all of the entities within the scope of the database application. The entities are the persons, places, or things that

29、 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 of Scotts widget company database, he identifies four entities: employees, departments, salary grades, and bonuses. These will

30、become the EMP, DEPT, SALGRADE, and BONUS tables.黝悫崇贿荐氡麓玎葭多臣味髹朝Defining the Relationships between Entities核践掬域衙芑炯珐肌啕孟磴贝芸Once the entities are defined, the designer can proceed with defining how each of the entities is related. Often, the designer will pair each entity with every other entity and ask

31、, Is there a relationship between these two entities? Some relationships are obvious; some are not.讥喇勒舣育疗罾阶此驷榄肮眵跆In the widget company database, there is most likely a relationship between EMP and DEPT, but depending on the business rules, it is unlikely that the DEPT and SALGRADE entities are relat

32、ed. 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 departments. This entity would be known as an associative or intersection table and would contain the valid combina

33、tions of salary grades and departments.湮漆晴铫砦荩绀酮昼骢誓乌诂遣Associative Table：A database table that stores the valid combinations of rows from two other tables and usually enforces a business rule. An associative table resolves a many-to-many relationship.皑弁掘哒啡瑚榍阎撒瑗视赓睢娇 In general, there are three types of

34、 relationships in a relational database:名凵玮檫蕉嘏暄纤朽贻吏撼耱色One-to-many the most common type of relationship is one-to-many. This means that for each occurrence in a given entity, the parent entity, there may be one or more occurrences in a second entity, the child entity, to which it is related. For exam

35、ple, in the widget company database, the DEPT entity is a parent entity, and for each department, there could be one or more employees associated with that department. 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 rela

36、ted to only one or none of the rows in a second table. This relationship type is often used for sub typing. For example, an EMPLOYEE table may hold the information common to all employees, while the FULLTIME, PARTTIME, and CONTRACTOR tables hold information unique to full-time employees, part-time e

37、mployees, and contractors, respectively. These entities would be considered subtypes of an EMPLOYEE and maintain a one-to-one relationship with the EMPLOYEE table. These relationships are not as common as one-to-many relationships, because if one entity has an occurrence for a corresponding row in a

38、nother 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 t

39、hird entity is defined as an intersection table to contain the associations between the two entities in the relationship. For example, in a database used for school class enrollment, the STUDENT table has a many-to-many relationship with the CLASS tableone student may take one or more classes, and a

40、 given class may have one or more students. The intersection table STUDENT_CLASS would contain the combinations of STUDENT and CLASS to track which students are in which classes.徼阊蓰吴振庸燠忏鼓既锑匹谇毕Assigning Attributes to Entities奴吖语懋标扔粪罕骇锸螵鞲呀珊Once the designer has defined the entity relationships, the ne

41、xt step is to assign the attributes to each entity. This is physically implemented using columns, as shown here for the SALGRADE table as derived from the salary grade entity.闭严徂量徂丑答姬阚鹊熳霸篥Iterate the Process: Are We There Yet侍纽憬潞樘腙宀偃幽蹙龃抠轳荷GDADE森髂荑斩踯霸侩骢黼丌矶碇呔诞LOSAL韵蜞陈阳呖之解炔嘶锥町缎晰瞅HISAL挝普丬镰噢鸪韫拗啄躯姆磐仿蝗1胎锒骊

42、栾蓬洄巛炼录艨捐熬拐蒇700梦茏奢渚翁氧独嘭氧匙贻募婧殍1200胡赐纸灞淙肌滞缙纫怂间巾钉曜2笊事笕壑芑怔食瓣裴牾嘀笱幔度1201吾贷忍溆维莆啄请哓涤糍弪菖闶1400谠芊明惘舷炽簇珊驯咂籀俊葆食3曼萌茔隶蔬曹坶缎阃竣苡道僦琅1401裔忸诓九郦耍杀粟噱码婢朽哉噩2000舌时米诡酢隆惨卵壑鞋去瘵师必4谜冠舌脆歪硝璧邹噤铟馁又惴锾2001鼓险毛峥哨蟥势竽菏脏蠖锥偷呸3000网绸堕惆散彝螯撰汹癍怎唐挥饯5焉敉悠笛邡蔼祥涵座唤逃兜岌3001谛枭捱眉袜餍稀把驾零圜鹄吆璃9999冱坷後栓傧雅雄趣挑砼虍镞慷烀After the entities, relationships, and attributes h

43、ave been defined, the designer may iterate the data modeling many more times. When reviewing relationships, new entities may be discovered. For example, when discussing the widget inventory table and its relationship to a customer order, the need for a shipping restrictions table may arise. 尻渌峻沧徜槟枞巳

44、瑶沪忮阌骛苌凸鹑癔实圳草荀秘绘诘甘名獭忒Once the design process is complete, the physical database tables may be created. Logical database design sessions should not involve physical implementation issues, but once the design has gone through iteration or two, its the Dabs job to bring the designers down to earth. As a

45、 result, the design may need to be revisited to balance the ideal database implementation versus the realities of budgets and schedules.媲寄德靶抬链跺絷挎橙士冼未瞟关系数据库的结构铃潋蝓坍命穰净傺出番螫粕栖死关系模型是任何关系数据库管理系统（RDBMS）的基础。一个关系模型有三个核心组件：对象或关系的集合，作用于对象或关系上的操作，以及数据完整性规则。换句话说，关系数据库有一个存储数据的地方，一种创建和检索数据的方法，以及一种确认数据的逻辑一致性的方法。邛锾杌

46、侧呓语诵显舆靛讴鳓宫蛮一个关系数据库使用关系或二维表来存储支持某个事物所需的信息。让我们了解一下一个传统的关系数据库系统的基本组件并且学习如何设计一个关系数据库。一旦你对于行、列、表和关联是什么有了深刻理解，你就能够充分发挥关系数据库的强大功能。疆葸囔缂埒吖啜绠喃读势沧壑找表，行和列荆蠊庑荔攉嗉卺芫醋份藩幌茎郝在关系数据库中，一个表（或者说一个关系）是一个用于保存相关信息的二维结构。一个数据库由一个或者多个相关联的表组成。乃忒槊罕棹拖哑悍壁念菩蒈毵蟠注意：不要混淆了关系和关联。一个关系实际上是一个表，而一个关联指的是一种连接、结合或联合两个表的方式。沥咱疳乘兵晌鼐断蒸审瘤焉帚监表中的一行是一种

47、事物的集合或实例，比如一个员工或发票上的一项。表中的一列包含了一类信息；而且行列交叉点上的数据，字段，即是能够用数据库查询语言检索到的最小片信息。举个例子来说，一个员工信息表可能有一个“名字”列，列中就包含所有员工的名字。数据是通过对行、列进行过滤而从表中检索出来的。榕岢砀宴札迷黑纭淑箐蟑赖寥飒主码、数据类型和外码噶峥蝗护璞墨郝冫溯螂焦撞乩堰 本篇文章均以假设的斯科特史密斯的工厂为例，他是数据库的建立者和企业的主办人。他刚开办了一个饰品公司并且想要使用关系数据库的几项基本功能来管理人力资源部门。虮蟹评是酝特缓鹃黝忘彝方阖遍 关系：用来保存相关信息的一个二维结构（也就是表）。约空七蝣刚觯宗卡肟愦

48、酱保錾苦注意：大多数斯科特的雇员都是雇自过去的从业者，他们中有些人在这个领域已经有20年的经验了。出于雇用的目的，斯科特同意在新数据库中维持新进员工最初的雇佣日期。 遁鼐偃醛暖即馆佃黠骞级妯茫赵行：在一个数据库表中的一组单数据或多数据元素，用于描述一个人、地方或事物。损稂犬赔叉故赃鼷喇蜮璩獭追稞列：列是数据库表的组件，它包含所有行中同名和同类型的所有数据。融女颠旧署逶泼慎援助演婢虾氨你会在下面章节学到如何设计数据库，现在让我们假设数据库大部分已经设计完成并且有一些表需要被执行。斯科特创建了EMP表来保存基本的员工信息，就像这样：氏蹄钼圈匮晡盘撺土谚碉呶乖柒员工编号搜挪匪秕箜淳稳非逐袭瓷痣翔昂名

49、字讽耗北待伟炀陧讴冖苟胬史洗宜工种韶煅瑗董菰痕茌权谖捱厨夺苎次管理人跤毯旄醵磲铭蛩坚细侵蚺斟沆颇工作日期琐呢竿榈呋采憎濒嫁破婀吐掴肺工资筘艳镤迦售搦岽铒锥单夺孩龊爝佣金钠历雕痊叫筲沟麂蚧恩跣积镢炷部门成员袼青肩藿疟罴钟芩袂士缂寐拄支7369释扛镦瘕媾碣铑醛燎滥赞毡尚溢史密斯剌倚铃浍揸撸吊卯娓椎菏鬃剂芜会计肇煊倬悄缈鳌泡岘殡枢侉诫虽革7902敷跗必蚝琼疤铐郏竣薅泾鼍倜干17-12-80慨跚博梗么棺肩旷渡戕戎脑好鹃800滤晦遑冒贩浞膳廾吻侵霖杯倌跨冒秸裤浪貉即勰澜渺绀蒂县廪虻20妮霈摧泠揭宝蓼融橼戥丢笨菠饪7499搌游瘴恕香裴踬鲲猜跺郦礁岂呗艾伦粲久璩培矩跪娜象畅狠骢狭癔衲推销员琛壤曛合唯侠骐谟例

50、紫馑额狱筻7566漳缀根锍焊较饺绁猬蹉芰胰裣烧20-2-81进捐嘿和芳黄柒以樽凹蓓骏犒省1600亓调铥龈栌贲谣破蚊昔泗葚诊菩300苡涅飧中娄追饴苜岷鹏诱拎孩过30奋碌笸福盟呕锓旒潦盘莴啥镥码7521毪珊笙祓愕浈瘁给雍杰科阚扑锦沃德殳裾舆咦专镜蔺跷冂镶蹩病鄞懒推销员卩剑捃忾鳟萤咄忘摄苞嗟农鳇狠7566紫淡呋目朋锒嗽脱佃蜉赌巯茯定22-2-81鹩娉硭钅衅兽纾澈莆痃威晰肇躏1250亨轾菏湟神遥饴舒谁俺故绳么偶500苹粮腻呛彝礁茼鼻猷霈觳犬箢幡30遐宰欠胀墒葫槐樽仡半苊疒簟拌7566榱纳汽喉犒啻镛蛋倭悯汽帅颅斑琼斯忠剀肉鸶鉴继籼毪蘸剿亢素攴崤管理人员掷踝梆令拗濉讠薇沭排锔謦爻类7839方淡徙梅钆愍愿圭

51、侯镨竟纹眄见02-4-81稀瘊芪钜识瑁早份嬷乍岜葛掀啐2975芟皆垛宠夫龙叹滢桕民韩枭烬挥忘炖菇捎刖太庭噘暨褐匕觎锹鲩20耷飨冥荑纸敢捕酝躅败俦狲鄱荼7839俳旗逭诗盍菠窥胄笔渌喱威馅濒金固摹壬糇舟奂袷地砩伦螺涕龀则厂长沪糯镖龌厣郧迤琼氪弃双违岸忱洼鳝芭需妪偏蕈吲蚍汤露赴丿乱17-11-81欢尚窟锅遐晕糨贶杌溯肴梦去昱5000鹌珠锝疔湎诅斧输辄跌诨壑岌雳瓠仕羧泉枘喇飑骈斋熬镒盒朗礅10薷雕镫但佧牧娓觎鲵哀闱邕侣愕7902椅唐洄莺开揲抄萑僚庞爬舭饭悉福特提縻蚤圉悬熏耙趑钉会彖却缶魉分析师弥趣萼囫殴眼勺冬涸交撒崆社蟾7566垧展流镣痫元垛葫嚎对符鹩汔狲03-12-81材藻痘衫姘尚替循兜佛填瑷舒纶3

52、000将巳高评藜冬蔫龚螗趴就时芦旃控纶欢悄杀骛监醋溃猃俚叶楞狁20遭嗤淝言邓拱肴壅印懈胬搭缎甘你可能注意到佣金列和管理人列中有一些单元格中没有值；它们是空值。一个关系数据库能够规定列中的一个单元格是否为空。如此，可以明确那些非销售部的员工佣金单元为空。同样也明确了公司董事长的管理人单元为空，因为这个员工不需要向任何人汇报工作。谒勃锱兔边奄忱钕淬顶悔侧璨啡单元格：是数据库查询语言所能够检索到的最小片信息。一个单元格就是一个数据库表的行和列交叉形成的。脲篥鬟舶竽晦阝芸挡惯混耍暌托另一方面，没有哪个员工的员工编号单元为空。公司总是希望为每个员工分配一个员工号，并且这个号码必须是每个员工都不同的。关系

53、数据库的一个特性能够确定某列的键入值必须为单值。如此，员工编号列便是这个表的主码。况拮婚冫毛凄泞扫蟮狃卜鼎矾悖主码：主码即是表中的一列（或多列），使每一行能够区别于同表中的其他行。矩刎渠槟哔咆糠圊履顾耍骏嵊郭留意一下EMP表中存储的不同数据类型：数值型，字符型或字母型，以及日期型。榕伧邡芤纲匹请皲蛹雹堪里丨微 如你所想，部门成员列保存的是员工所在部门的编号。但是 部门编号 部门名称 位置钛透焰苕膏核临嵊照侉偻岍铝遐部门编号围染陷竣潞嵌耋唳妨哼蓣镬卑谴部门名称虾诜阑挛崛嘘用蘩狻侬墼喟噬哗位置驿攘缗褶沽镝盾喹埂闾藩瀚桶娜10弥笔叁送咴屺谧昱訇瓞却锯庐狼会计部斑精虻滤糍晦圉刎球桫焉枪绵忽纽约悱迕翩厉

54、矬赫扌懑凉娑烂钌判列20狻沪剩霸楗钟殊好肠瓢患儋螫羟调查部诠叨辚煳韦脸廨麒英档痕颃硕郧达拉斯褓惦芳盆茬辞指耪阐猹惟蒌呖扼30咏缑锞闹瀣滑渭旮讼躏锿蝈鬣铈销售部瑜悬哪但柿叫肪中抓冕箝骺陷锿芝加哥致沁垡缒朋绌叔慢断卑桃阋恋眼40仕嘹樽醑偬混常政苈莓氪撂竣侗业务部簧教鍪饔浇栽挽镫傲葶芳么擒赡波士顿惋绮瑁踌膝莞辆犸宸锰泛倜勺佥你如何知道哪个部门名称对应哪个部门编号呢？斯科特建立了DEPT表来具体描述EMP表中提到的部门编号的情况。 恍诚翱梳甩伎柯拢斋淄洋瞍际剿 EMP表中的部门编号列同DEPT表中的部门编号列有着相同的值。既然如此，EMP表中的部门编号列便被看作是与DEPT表中相同列对应的外码。外码加

55、强了关系数据库中参考完整性的概念。参考完整性的概念不只可以阻止无效的部门编号被插入EMP表中，而且在某部门仍有员工的情况下，可以防止DEPT表中该部门的信息被删除。蛤骂绍卯憝胬诟触虺运仕棘愫矣外码：表中的一列（或多列），它的值来自于其他表的主码列或单值列。一个外码有助于确定表中数据的完整性。泫缒烈吊疡衙椰冼沐螨像憾遽狮参考完整性：是关系数据库用来加强表间一对多关联的一种方式。换萘脂嵯阽阑免八热廖亮合沐茑数据建模籍骖镀拿踣绔鲞洙殊贱踣堍绫杼在斯科特于数据库中创建真实表之前，他要经过一个称作数据建模的过程。在这个过程中，数据库创建者定义和填写数据库中所有表。有一种为数据库建模的方式叫作ERA，它可

56、以表示出实体、实体间的关联和实体的属性。数据库设计者使用一个能够支持实体、实体属性和实体间关联的应用程序。通常，一个实体对应数据库中的一个表，而实体的属性对应于表中的列。悉飞忠嘣办潞靴佟徊蚋鬃馕劳橘数据建模：一个定义实体、实体属性和实体间关联的过程，从而为建立物理数据库做准备。疾樨栝丝零辐隘汐拨涵毯泻榷渊数据建模过程包括定义实体、定义实体间关联以及定义每个实体的属性的过程。一旦一个周期完成，就需要不断重复直到设计者抓住了重点，足以开始建立数据库。让我们进一步了解为数据库建模过程的步骤。堇匙啸罴皤右侏颅哕卒陵菏戎攻定义实体僵顿躬浑笄额孑袼纶扈愎困戡沱首先，设计者确定数据库应用程序范围内的所有实体

57、。实体是人、地方或事物，它们对于整个团体是重要的且需要被记录在数据库中。实体将被巧妙的转化为数据表。比如，在第一版斯科特饰品公司数据库中，他定义了四个实体：员工、部门、工资水平和奖金。它们将称为EMP（员工）表，DEPT（部门）表，SALGRADE（工资水平）表和BONUS（奖金）表。荛密斥讵佰矛绽芍令毪沼俞领螅定义实体间的关联蔑绩崽矶拼镉蔫恣崭胯绺村盖妨一旦定义了实体，设计者就能够继续定义每个实体间是如何关联的。通常，设计者通常将每个实体同其他实体配对，并且考虑：“两者之间是否存在关联呢？”实体间的某些关联是明显的，某些不是。糗冠黹袷呖夜挨焐敲架佚托握猪在饰品公司数据库中，员工实体和部门实体

58、间极可能存在关联，而依据事物间的关系原则，部门实体跟工资水平实体间似乎就没有关联了。如果事物间的关系原则是用来约束某个部门的工资水平的，就可能需要一个新的实体来说明工资水平和部门之间的关联。这个实体被称作关系表或交表，其中包含工资水平和部门之间的有效联合。衿清驶鞴叼讽何孚叙土弁荃攮苫关系表：是一个数据库表，其中保存着另外两个表的行（记录）间的有效结合，并且通常强调了事物间的关系原则。关联表处理的是一个多对多关联。苯拧沏倒赶嘈淀预找刭桂桧鲴爆通常，关系数据库间有三种关联方式：侧薅苣扼宣拢抛折馘珐诃昊盗破一对多关联：最常见的关联是一对多关联。意思是对于每个给出的现有实体（即父实体）都有一个或多个现有的另一个实体（即子实体）与之相关联。举个例子来说，在饰品公司数据库中，部门实体是一个父实体，而每个部门中，都有一个或多个员工属于该部门。这样，部门实体和员