Slide 1 Slide 6 Main Element s of an Oracle ERD Main Element s of - PDF document

Slide 1 Slide 6 Main Element s of an Oracle ERD Main Element s of an Oracle ERD Relationship Unique CUSTOMER held # * customer number Identifier Entity MEMBERSHIP by * first name Ent it y Relat ionship Diagrams * last name Attributes the holder o other initials of COMPANY Super-type held by SUPPLIER Sub-type the holder of OTHER composed COMPANY of Recursive part of Relationship Exclusive Arc Slide 2 Slide 7 Dat abase Development Dat abase Development Hardware and Software Independence Hardware and Software Independence Process Process ENTITY RELATIONSHIP MODEL Business Information Requirements Agreement Product Term Conceptual Data Business view NETWORK Modeling HIERARCHICAL Agreement DATABASE DATABASE Systems view Logical Agreement Agreement Product x Database Design Item 1 Item 2 Product y Item 1 Item 2 Physical RELATIONAL Product X Product X DATABASE Database Build Code Date Customer Code Description Agreement Number Quantity Product Operational Database Slide 3 Slide 8 Terminology Terminology Relat ionship Def init ions Relat ionship Def init ions CONCEPTUAL LOGICAL (Business view) (Systems view) • The way one ent it y relat es t o anot her • The way one ent it y relat es t o anot her • The business rules t hat link t oget her • The business rules t hat link t oget her ANALYSIS DESIGN business inf or mat ion needs business inf or mat ion needs ENTITY TABLE • What one t hing has t o do wit h • What one t hing has t o do wit h anot her anot her RELATIONSHIP FOREIGN KEY • A named associat ion bet ween ent it ies • A named associat ion bet ween ent it ies ATTRIBUTE COLUMN PRIMARY KEY UNIQUE IDENTIFIER UNIQUE KEY Slide 4 Slide 9 Relat ionship Diagramming Relat ionship Diagramming I nf ormat ion Models I nf ormat ion Models Convent ions Convent ions • Or ganize t hought processes • Or ganize t hought processes • A line bet ween t wo ent it ies • A line bet ween t wo ent it ies • Accurat ely model business dat a • Accurat ely model business dat a • Lower case r elat ionship name • Lower case r elat ionship name • Communicat e wit h st akeholders • Communicat e wit h st akeholders • Opt ionalit y (Minimum car dinalit y) • Opt ionalit y (Minimum car dinalit y) • Analyze t he scope • Analyze t he scope Mandatory - must be Optional - may be • Provide sound basis f or syst em design • Provide sound basis f or syst em design • Degree (Maximum cardinalit y) • Degree (Maximum cardinalit y) One or more One and only one Slide 5 Slide 10 Relat ionship Diagramming Relat ionship Diagramming CS 450 ERD Convent ions Convent ions Convent ions attribute Cardinality (degree) empnum COPY TITLE key M 1 Works For EMPLOYEE DEPARTMENT many entity relationship optional (crow’s foot) Participation Requirements one mandatory (optionality)

Slide 11 Slide 16 Full Reading of Relationship Full Reading of Relationship Relat ionship Synt ax Relat ionship Synt ax must be one or more relationship assigned to Ent it y 2 Each Ent it y 1 entity 1 or or entity 2 name EMPLOYEE DEPARTMENT EMPLOYEE DEPARTMENT may be one and only one responsible for Each EMPLOYEE must be assigned to one and only one DEPARTMENT Name Object Subject Each DEPARTMENT may be responsible for one or more EMPLOYEES entity entity Optionality Degree Slide 12 Slide 17 How do you read t his? How do you read t his? How do you read t his? How do you read t his? assigned to EMPLOYEE enrolled in DEPARTMENT STUDENT COURSE taken by Slide 13 Slide 18 How do you read t his? How do you read t his? How do you read t his? How do you read t his? enrolled in assigned to COURSE EMPLOYEE DEPARTMENT STUDENT taken by Each STUDENT may be enrolled in one or more COURSES Each EMPLOYEE must be assigned to one and only one DEPARTMENT Each COURSE may be taken by one or more STUDENTS Slide 14 Slide 19 Analyzing and Modeling Analyzing and Modeling How do you read t his? How do you read t his? Relat ionships Relat ionships 1 Det ermine t he exist ence of a relat ionship 1 Det ermine t he exist ence of a relat ionship 2 Name each direct ion of t he relat ionship 2 Name each dir ect ion of t he r elat ionship EMPLOYEE DEPARTMENT responsible for 3 Det ermine t he degree of each direct ion 3 Det ermine t he degree of each direct ion of t he r elat ionship of t he r elat ionship 4 Det ermine t he opt ionalit y of each 4 Det ermine t he opt ionalit y of each dir ect ion of t he r elat ionship dir ect ion of t he r elat ionship 5 Read t he relat ionship aloud t o validat e it 5 Read t he relat ionship aloud t o validat e it Slide 15 Slide 20 How do you read t his? How do you read t his? Oracle’s Layout Guidelines Oracle’s Layout Guidelines P ar ent Child EMPLOYEE DEPARTMENT Ent it y (1) Ent it y responsible for Each DEPARTMENT may be responsible for one or more EMPLOYEES Dead Crows Fly East ! P ar ent Ent it y (2)

Slide 21 Slide 26 At t ribut es Which have At t ribut es Which have At t ribut es At t ribut es At t ribut es At t ribut es TITLE Badge Number - Identifies an employee Does information need to be product code title stored about any of the Name - Qualifies an employee description attributes? review details Payroll category (weekly or salaried) - Classifies an employee Yes, review details. An entity is missing. Date of birth - Quantifies an employee REVIEW TITLE Employment status (active, leave, terminated) - Classifies of an employee product code author title comment description date recorded review details Slide 22 Slide 27 Finding Common or Derived Finding Common or Derived Finding At t ribut es Finding At t ribut es Dat a Dat a 12 • Count • Count 08 30 • Tot al • Tot al 22 ---- 72 • Maximum, Minimum, Aver age • Maximum, Minimum, Aver age Is this attribute really needed ? ---- • Calculat ion • Calculat ion Beware of obsolete requirements from previous systems Derived attributes are redundant and Beware of derived data can lead to inconsistent values Slide 23 Slide 28 At t ribut e Diagramming At t ribut e Diagramming At t ribut e Opt ionalit y At t ribut e Opt ionalit y Convent ions Convent ions Mandatory Attributes • I nside t he ent it y' s • I nside t he ent it y' s • A value must be st ored f or each ent it y inst ance • A value must be st ored f or each ent it y inst ance sof t box sof t box EMPLOYEE • Tagged wit h • Tagged wit h * badge num first name • Singular • Singular Optional Attributes last name payroll num • A value may be st or ed f or each ent it y • A value may be st or ed f or each ent it y date of birth • Lower case • Lower case inst ance inst ance employment status • Tagged wit h o • Tagged wit h o Slide 24 Slide 29 Meaningf ul Component s Meaningf ul Component s At t ribut e Opt ionalit y At t ribut e Opt ionalit y PERSON PERSON last name EMPLOYEE name first name badge num * Break down aggregate attributes first name * * last name ITEM ITEM o type title code vendor o weight num Slide 25 Slide 30 At t ribut e Det ails and At t ribut e Det ails and Verif y f or Single Value Verif y f or Single Value Volumes Volumes RENTAL transaction date Can an attribute have more total amount paid than one value for one item instance of the entity? Attribute - * Engine Size Format Type Number Yes, more than one item may be rented at a time. Maximum length 4 An entity is missing. Average length 4 Decimal place 1 Unit of measure cc RENTAL ITEM RENTAL Allowable values 900,1000,1500,1800,2000 item num transaction date Volume Initial 100% total amount paid

Slide 31 Slide 36 Diagrammer Behavior Using a Domain Using a Domain Movie • Def init ion changes ar e st ored in Def init ion changes ar e st ored in • Mono Reposit ory even if t he diagram is Reposit ory even if t he diagram is not saved. not saved. AUDIO Stereo • Saving a diagr am saves t he layout . • Saving a diagr am saves t he layout . MON Audio STE • Diagrams are not synchronized Diagrams are not synchronized • SUR Game aut omat ically. aut omat ically. • Requerying Requerying is your responsibilit y. is your responsibilit y. Surround • • • Diagr ams can be expor t ed t o, f or Diagr ams can be expor t ed t o, f or Sound example, Word document s. example, Word document s. Slide 32 Slide 37 Edit ing in t he RON Creat ing an Ent it y For DB Planning Slide 33 Adding Det ails Slide 34 Domains Slide 35 Creat ing Relat ionships