版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、Extended Learning Module CDesigning Databases and Entity-Relationship DiagrammingINTRODUCTIONDatabase collection of information that you organize and access according to the logical structure of that informationRelational database uses a series of logically related two-dimensional tables or files to
2、 store information in the form of a database DESIGNING & BUILDING A RELATIONAL DATABASEDefine entity classes & primary keysDefine relationships among classesDefine information for each relationRelation = table = fileUse a data definition language to create database Remember Solomon Enterprises?From
3、Chapter 3Provides concrete to commercial builders & home ownersChapter 3 the CRM side of Solomons databaseFocus now SCM side of Solomons database Remember Solomon Enterprises?Remember Solomon Enterprises?Solomons database tablesCustomerConcrete TypeOrderTruckEmployee Observations for Solomon5 concre
4、te typesHome foundation and walkwaysCommercial foundation and infrastructurePremier speckled (with gravel)Premier marblePremier shell Observations for SolomonObservations for Solomon6 raw materialsWaterCement pasteSandGravelMarbleShellMixing instructions are for a cubic yard Observations for Solomon
5、Some raw materials are in several concrete typesConcrete types require several raw materialsInventory (QOH) is tracked for all raw materials Observations for SolomonSuppliers provide raw materialsSolomon uses only 1 supplier for a given raw materialA supplier can provider several raw materials Obser
6、vations for SolomonWaterSupplier not trackedQOH not tracked Business Rules for SolomonGiven concrete type will have many raw materialsGiven raw material may appear in many concrete typesEach raw material has one and only one supplier Business Rules for SolomonA supplier may provide many raw material
7、sThere may be suppliers present not providing any raw materialsThese business rules are very important to remember STEP 1: DEFINE ENTITY CLASSES & PRIMARY KEYSEntity class concept typically, person, place, or thing about which you wish to store information and that you can identify with a unique key
8、 (primary key)Concrete TypeRaw MaterialSupplier STEP 1: DEFINE ENTITY CLASSES & PRIMARY KEYSPrimary key a field (or group of fields) that uniquely describe each recordA record in a database is sometimes called an instance (of an entity class) STEP 1: DEFINE ENTITY CLASSES & PRIMARY KEYSIn general, s
9、tay away from names for primary keys (duplicates)Concrete Type Concrete TypeRaw Material Raw Material IDSupplier Supplier ID STEP 1: DEFINE ENTITY CLASSES & PRIMARY KEYSSTEP 2: DEFINE RELATIONSHIPS AMONG ENTITY CLASSESTo define relationships, you create an E-R diagramEntity-relationship (E-R) diagra
10、m a graphic method of representing entity classes and their relationships E-R DiagramsUse 5 symbolsRectangle entity classDotted line relationship| - single relationship0 zero/optional relationshipCrows foot () multiple relationship E-R DiagramsE-R DiagramsFigure C.2 on page 375 reads asA Concrete Ty
11、pe is composed of Raw MaterialA Raw Material is used to create a Concrete TypeA Supplier provides a Raw MaterialA Raw Material is provided by a Supplier E-R Diagrams - CardinalityE-R diagrams show relationshipsThey also show numerical nature of relationshipsThis is called cardinality| - single relat
12、ionship0 zero/optional relationship - multiple relationship E-R Diagrams - CardinalityFollowing lines marked A:A Supplier may not provide any Raw Material (0) but may provide more than one Raw Material ()E-R Diagrams - CardinalityFollowing lines marked B:A Raw Material must be provided by a Supplier
13、 (|) and can only be provided by one Supplier (|) NormalizationNormalization process of assuring that a relational database structure can be implemented as a series of two-dimensional tablesWe will follow three rules of normalization Normalization RulesEliminate repeating groups or many-to-many rela
14、tionshipsAssure that each field in a relation depends only on the primary key for that relationRemove all derived fields from the relations Eliminating Many-to-Many RelationshipsA many-to-many relationship exists if there is a crows foot () on each endYou must eliminate these by creating an intersec
15、tion relation Eliminating Many-to-Many RelationshipsEliminating Many-to-Many RelationshipsIntersection (composite) relation a relation you create to eliminate a many-to-many relationshipIntersection relation will have a composite primary keyComposite primary key consists of the primary key fields fr
16、om the two intersecting relations Solomons New Database Structure Steps to Eliminate a Many-to-Many Relationship Draw the part of the E-R diagram with many-to-many relationshipWrite some primary keys for each relationCreate new E-R diagram with intersection relation in the middleWrite some composite
17、 primary keys for intersection relation Steps to Eliminate a Many-to-Many Relationship Create a meaningful name for intersection relationMove minimum cardinality next to left relation to the right of intersection relationMove minimum cardinality next to right relation to the left of intersection rel
18、ation Steps to Eliminate a Many-to-Many Relationship Maximum cardinality on both sides of intersection relation is always many ()General rule new minimum and maximum cardinalities for the 2 original relations will be one (|) and one (|) STEP 3: DEFINING INFORMATION FOR EACH RELATIONTo ensure that ea
19、ch field is in the right relation, ask the following question:“Does this piece of information depend only on the primary key for this relation?”Yes its in the correct relationNo Its in the wrong relation STEP 3: DEFINING INFORMATION FOR EACH RELATIONSee Figure C.6 on page 381Look at Raw Material rel
20、ationEvery field must depend only on Raw Material IDRaw Material Name, QOH, and Supplier ID doSupplier Name does not STEP 3: DEFINING INFORMATION FOR EACH RELATIONWhat does Supplier Name depend on?It depends on Supplier IDSupplier ID is primary key for Supplier relationTherefore, Supplier Name belongs only in Supplier relation STEP 3: DEFINING INFORMATION FOR EACH RELATIONSee Figure C.6 on page 381Do you see any derived information?Counts?Sums?Averages?If you see them, remove them STEP 3: DEFINING INFORMATION FOR EACH RELATIONRaw Material Total i
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 2024年度股权投资合同:某创业公司A轮融资2篇
- 虞美人教学课件
- 《铁路货物运输》课件
- 《肺癌病人的护理》课件
- 2024中国移动政企客户分公司暑期实习生校园招聘易考易错模拟试题(共500题)试卷后附参考答案
- 2024中国石化海洋石油工程公司毕业生招聘15人易考易错模拟试题(共500题)试卷后附参考答案
- 2024中国正大甘肃区校园招聘70人易考易错模拟试题(共500题)试卷后附参考答案
- 2024中国大唐集团资本控股限公司招聘16人易考易错模拟试题(共500题)试卷后附参考答案
- 04版建筑设计合同:某地标性建筑的设计与施工
- 《营销管理与分析》课件
- 聚氨酯混凝土地坪施工方案
- 网络管理员备考资料网络管理员
- 高空吊车吊篮作业施工方案
- 剧院行业SWOT分析
- 主题公园特色小镇-主题立面雕刻TCP(水泥直塑、水泥塑石)施工方案
- 人工造林项目投标方案
- 医药魔方-CAR-T细胞疗法研发现状与发展趋势-2023.07
- 矿山环境保护教材或演讲课件
- 【历史课件】中国近代史
- 基础英语语法中国大学mooc课后章节答案期末考试题库2023年
- 保育员-项目二-组织进餐课件
评论
0/150
提交评论