Analysis of Business Information Systems
Analysis of Business Information Systems BCIS 4610
Popular in Course
verified elite notetaker
Popular in Business Info Systems CIS
This 134 page Class Notes was uploaded by Jadyn Gusikowski on Sunday October 25, 2015. The Class Notes belongs to BCIS 4610 at University of North Texas taught by Anna Sidorova in Fall. Since its upload, it has received 27 views. For similar materials see /class/229187/bcis-4610-university-of-north-texas in Business Info Systems CIS at University of North Texas.
Reviews for Analysis of Business Information Systems
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 10/25/15
University of North Texas 1272009 Enterprise Analysis BCIS 4610 Lecture 2 BCIS 4610 Spring 2009 Agenda 0 Announcements team adjustments etc 0 Discussion of assigned articles 0 Lecture on Enterprise analysis BCIS 4610 Spring 2009 Anna Sidorova partially based on BA BOK by the IIBA 1 University of North Texas 1272009 S c h e d u e Date Subjects covered Reading Deliverabl es 21Jan 20w lrtromctim Cha er 1 SAampD Enumrrsnt may SAW Enurmrrsd Eden20w Enterpise analysis Cha ers 3 5 Tm nalized Nmag39ng an IS R39q39ect R39q39ect Plaming 4 Feb2008 Odlecting User mimmmts Chaan 6 R39q39ect ID we Nmagng usa39 reqilrerrsnts FED2139 Process Madelin Chapter 7 W 1 we Using Oracle CASE Tods fcr R39cness Nudel39ng 18Feb2308 Omcenual Ema Nbdeling Cha ers Prq39ect R39cpcsal we Using Oracle CHE TOdS fcr Data Nlcneling Nlcneling msine rules 25Feb Desig ng system log39c Cha era lW2L1Ie 4ka r 2m9 leainmk BCIS 4610 Spring 2009 Assignments HW1 from HGV book Ch 3 o Pr 12 draw a network diagram calculate ES EF LS LF slack for each activity and identify activities that are on a critical path by hand 0 Pr 14 using MS Project create a Gantt chart calculate ES EF LS LF slack for each activity and identify activities that are on a critical path Make sure that you zoom your Gantt chart so that it ts one page BCIS 4610 Spring 2009 k Anna Sidorova partially based on BA BOK by the IIBA 2 University of North Texas 1272009 Articles discussion BCIS 4610 Spring 2009 K Dr David Parnas Giancarlo ucc1 University of Limerick University ofBolzanorBozen Matthew Simon ThoughtWorks International Dr Barry Boehm USC BCIS 4610 Spring 2009 k Anna Sidorova partially based on BA BOK by the IIBA 3 University of North Texas Discussion questions 0 What is globally distributed development What are its characteristic challenges 0 What is agile development What are its distinctive characteristics 0 Can globally distributed development be agile What do the experts say What do you think 0 Can globally distributed development be agile What do the companies do Comment on the described practices 0 What are the takeaway conclusions of this discussion BCIS 4610 Spring 2009 Enterprise Analysis K BCIS 4610 Spring 2009 Anna Sidorova partially based on BA BOK by the IIBA 1272009 University of North Texas 1272009 BABOK v2 Knowledge Areas Business Analysis Planning and Monitoring El Requiremene A S l quot d icitation ssessment an I Analym Validan39on Requiremenm Management and Communication Source httpwwwtheiibaorgWil be onthe exam BCIS 4610 Spring 2009 j K Enterprise analysis Key Activities 1 Create and Maintain Business Architecture 2 Conduct Feasibility Studies 3 Determine the Project Scope 4 Prepare the Business Case 5 Conduct Risk Assessment 6 Prepare a Decision Package K BCIS 4610 Spring 2009 Anna Sidorova partially based on BA BOK by the IIBA 5 University of North Texas f Enterprise analysis Creating and maintaining business architecture EA Frarhevvurh Creating and EA Artifacts Guals and Strategy Maintaining Ahghrhehttu Business Business business prublEmuppununlty Gap analysis Current state hr Archltecture BA purl Business Architecture in war 5M m The purpose of the f Enterprise analySIs Creating and maintaining business architecture The Business Architecture is a set of documentation that de nes an organization s current and future capabilities strategy iuhg terrh goals and ublemives the high level business Envlrunmentthruugh a prueess ur mhetruhai View the teehhuiugreai EHVerHmE andthe Externalenv Htr rruhrherrt relevant stakehulders ete usiness Architecture is to provide a I no can xt that guides selection and management of programs and projects uni ed stru in war 5M m Anna Sidorova partially based on BA BOK by the IIBA 1272009 University of North Texas Selected frameworks Zachman s EA framework Columns What data and entities How process or function Where location and network Whopeope When time Why motivation ows Sco e Business Model S stem Model Technolo Representations ENTERPRISE ARCHITECTURE A FRAMEWORK i I l l I l i 4 4 gas 4510 Spnng 200 Model Detailed Selected frameworks POLDAT framework This model develops documents tables matrices graphs models and organizes them in the following categories 0 Process the business rocesses that ow value from the organization to the customer 0 Organization the organizational entities that operate the business processes including the management teams staff positions roles competencies knowledge and skills Location the location of the business units and other organizational entities eg call centers distribution centers etc 0 Data the data and information that is the currency of the organization owing through the processes to accomplish the business functions 0 Applications the information technology IT applications that enable the business processes to operate ef ciently and provide decision support information to the management team 0 Technology the enabling technology that supports the operation of the processes and applications CI 4610 Sprung 2009 Anna Sidorova partially based on BA BOK by the IIBA 1272009 University of North Texas 1272009 Enterprise analysis Creating and maintaining business architecture 0 Building Business Architecture should be planned like a project Determine appropriate framework and approach Determine the architectural documents and drawings to be created or updated Select appropriate resources Select relevant business architectural viewpoints eg lines of business or business units Identify appropriate tools and techniques to be used for capture modeling and analysis Determine how the architectural components will be stored BCIS 4610 Spring 2009 Enterprise analysis Conducting Feasibility studies Feasibility Study Goals and Strategy Strategic and Business Conductan technical alignment problemopportunity Feasibility Studies Alt solution BA Artifacts ranking an recommend Business BCIS 4610 Spring 2009 k Anna Sidorova partially based on BA BOK by the NBA 8 University of North Texas 1272009 Enterprise analysis Conducting Feasibility studies Typically a feasibility study is conducted to determine the viability of an idea for a new business opportunity During Enterp se nnalysis feasibility study helps the portfolio management team determine the best investment path to solve business problems and seize new business opportunities The main purpose of the study is to ascertain the likelihood of each potential solution alternative s probability of satisfying the business need in terms of Eoonomicfeasibility Operational feasibility and Technical feasibility BCIS4610 Spring 2009 Sample feasibility assessment process Source OMAFRA Factsheet Evaluating the Feasibility of Business O ortunities BCIS4610i5PFngOO9 Government of Ontario Anna Sidorova partially based on BA BOK by the NBA University of North Texas 1272009 Enterprise analysis Conducting Feasibility studies 0 The Feasibility Study Report is typically comprised of the following information 0 Executive Summary 0 Business problem andor opportunity statement 0 Feasibility study requirements including the business drivers of the initiative 0 For each option that was assessed the results of the study including Acomplete description of the solution option 0 Acomplete description of the assessment process and methods Acomplete description of the overall results document expected vs actual results scoring and other considerations Alist of identi ed risks associated with the alternative Alist of identi ed issues which adversely impact the success of the solution 0 Assumptions made during the study process LU close gaps in information 0 Alternative Solution Ranking 0 Ranking criteria Ranking scores 0 Results recommended solution including rationale for the decision 0 Appendix containing all supporting information BCIS 4610 Spring 2009 Enterprise analysis Determining Project Scope Defining the proposed project scope includes Describing business objectives Determining expected deliverables at a high level in terms of products services or other outcomes Documenting business assumptions and constraints Building a statement of the anticipated work effort Preliminary scope definition provides a documented basis for building the business cas BCIS 4610 Spring 2009 k Anna Sidorova partially based on BA BOK by the IIBA 10 University of North Texas 1272009 Enterprise analysis Determining Project Scope ngt Goals and Strategy Strategic fit High level requirements Product description Determining project Business and scope problemopportunity SCOPe Initial project BA Artifacts approach and Business Feasibility resourcing Study Alt solution ranking an recommend BCIS 4610 Spring 2009 J Enterprise analysns Determining Pr0ject Scope Techniques 0 Scope de nition and decomposition using Work Breakdown Structure 0 Creating a Context Diagram provides a visual model of the scope of the project BCIS 4610 Spring 2009 k Anna Sidorova partially based on BA BOK by the NBA 11 University of North Texas 1272009 Enterprise analysis Preparing Business Case IIgt Goals and Strategy Business Case repo Busmess Preparing Business Business Case problemopportunity Case Summary and De nitions BA Artifacts Business Feasibility Study Business Project Scope de nition IIgt 3054610 swung 200g Enterprise analysis Preparingthe Business Case The Business Case describes thejustification for the project in terms of the value to be added to the business as a result of the project outcomes vs the cost to develop the new solution To develop the business case the following steps are involved Identify and Quantify the Bene ts Identify and Quantify the Costs Prepare the Business Case 39 Process for quot quot Bene ts 3034610 swung 200g Anna Sidorova partially based on BA BOK by the IIBA 12 University of North Texas 1272009 Justifying IT investments 0 The average Clevel executive doesn t know how to evaluate if a technology investment is doing what it s supposed to doquot says Richard Chang CEO ofthe consultancy Richard Chang Associates Thus these executives focus solely on cost looking for some easy metric such as tying IT spending to a percentage of revenue or benchmarking your IT spend against your industry Galen Gruman IT Value Metrics How to Communicate ROI to the Businessquot CIO Magazine Oct 8 2007 BCIS 4610 Spring 2009 K Enterprise analysis Preparing the Business case 0 Some common metrics for financial valuation 0 Discounted Cash Flow 0 Net Present Value 0 Average rate of return 0 Pay back period 0 Internal Rate of Return o Difficulties with assessment ofthe benefits beyond cost reductions BCIS 4610 Spring 2009 K J Anna Sidorova partially based on BABOK by the NBA 13 University of North Texas m TH n ma swam M g I If a a mvwoaxms mi Numsna 39V nAssAstG THE Numnzns News w mm mm AS we m4 4 mm 25 E WWW a Mu mm mm mam m Mammyquot m whale W m pm Drambited BCIS 4510 Spnng 2009 Enterprise analysis Preparing the Business Case A sample Business Case table of contents would contain the following elements 1 Executive Summary 2 Project Background Project Rationale For Preferred Option Current Business Process Description Of The Problem Opportunity Project Objectives Project Scope Business Benefits Project Costs Assumptions Potential Business And Staff ImpactAnalysis Potential Technology Impact Analysis Other Issues Implementation Plan 3 Approach Financial Metrics Privacy Impact Assessment Alternative Evaluation Criterion 4 Key Selection Criterion Weighting Constraints And Limitations 5 Preferred Alternative Business Benefits Alternative Costs Assumptions Potential Business And Staff ImpactAnalysis Other Issues 6 Risk Management Plan RiskAssessment Risk Response Benefit Realization 7 Conclusion and Recommendations BCIS 4510 Spnng 2009 Anna Sidorova partially based on BA BOK by the IIBA 1272009 University of North Texas Multicriteria Analysis weighted multicriteria analysis Figure 44 Alternative prnjecrs and system design dccisinns can he assisted using Criteria Weighri AlternativeA i AlternativeB i AlrernariveC Requirements ReaHime dala entry 18 5 90 5 Automatic reorder 1 1 18 5 Realtime data query j 1 14 5 50 122 Constraints Developer costs 15 4 60 5 Hardware costs 15 4 60 4 Operating costs 15 5 75 1 Ease of rraining J 5 25 a 50 220 Total 1 00 342 Rating Score Rating Score Rating Score 90 5 90 90 5 90 70 5 70 250 250 75 3 45 60 3 45 15 5 75 15 3 15 165 180 415 430 BCIS4610 Spring 2009 Each requirement or constraint Score weight X rating Each alternative sum scores across requirementsconstraints Alternative with highest score wins Assessment rgt o BAArtifacts 0 Business Feasibility Study Assessment 0 Business Project Scope definition 0 Business Case report Conducting Risk BCIS4610 Spring 2009 K Enterprise analysis Conducting Risk 0 initial Risk Rating 0 Proposed Risk Responses rgt Anna Sidorova partially based on BA BOK by the NBA 1272009 University of North Texas 1272009 Enterprise analysis Conducting the initial risk assessment Project risk is an uncertain event or condition that if it occurs has a positive or negative effect on at least one project objective such as time cost scope or quality The purpose of the initial risk assessment is to determine if the proposed project carries more risk than the organization is willing to bear ECE 4610 Spnrg zoos J Enterprise analySIs Conducting the initial risk assessment The process steps to conduct the initial risk assessment include Identifying project risks Assessing risk probability and impact Planning risk responses Assessing organizational readiness and calculating an overall risk rating The deliverable is the initial Risk Rating for the proposed initiative and the nature am wm c u s wpcsc pmn these are added to the business case ms 4610 Spnrg zoos J Anna Sidorova partially based on BA BOK by the IIBA 16 University of North Texas IIgt Enterprise analy5is Preparing the De0i5ion Package Collated Package of Enterprise Activity Products Enhanced BA Artifacts Business Case 39 Business Feasibility Prepar39ng the I39EPDI39t Recommendations Executive Brie ng Materials Study Decision Package Business Project Scope de nition Business Case re ort Initial Risk Rating and Proposed Risk Responses ECB 4610 Spnrg zoos VB apnseanaw sPmpanngtheDedsmn Package The Decision Package provides an actionable set of information regarding the proposed new project to the organizational decision makers It is used by the sponsor of the proposed project to present the proposal to the portfolio management governance group The portfolio management process enables the organization to select theright investment path from the mix of potential opportunities including 1 research initiatives 2 new product development activities 3 information technology enhancements 4 internal business improvement projects and 533new business endeavors a 4610 Spnrg zoos Anna Sidorova partially based on BA BOK by the IIBA 1272009 University of North Texas 1272009 Enterprise analysis Summary Create and Maintain Business Architecture Conduct Feasibility Studies Determine the Project Scope Prepare the Business Case Conduct Risk Assessment Prepare a Decision Package WPP JNT BCIS 4610 Spring 2009 What if a project is selected lfthe project is selected the materials prepared during the Enterprise Analysis should be used to 1 Launch and plan the new project 2 Manage the project for value 3 Measure project bene ts BCIS 4610 Spring 2009 k Anna Sidorova partially based on BA BOK by the NBA 18 University of North Texas K Next Project Management BCIS 4610 Spring 2009 j K According to The Standish Group which tracks IT project success rates only 29 percent of IT projects conducted in 2004 were completed successfully k BCIS 4610 Spring 2009 Anna Sidorova partially based on BA BOK by the IIBA 1272009 University of North Texas Factors Affecting IT Project Management Tight budgets some experts expect some loosening of budgets in 2008 yet a possibility of recession makes it a serious concern Global nature of most IT projects both development and implementation A backlog of projects BCIS 4610 Spring 2009 k PROJECT Ma nagement INSTITUTE o V th more than 265000 members in over 170 countries PMI is the leading membership association forthe project management profession PMI was founded in 1969 by ve fonNardthinking individuals who understood the value of networking sharing process information and discussing common project problems 0 Certi cations PMI Risk Management ProfesSIonaI PMIRMPls39V39 PMI Scheduling Professional PMISPSM Program Management Professional PgMPSM Project Management Professional PMP Certified Associate in Pro39ect Management CAPM BCIS 4610 Spring 2009 Anna Sidorova partially based on BA BOK by the IIBA 1272009 20 University of North Texas Managing the Information Systems Project 0 Project 0 A Ianned undertaking ofrelated activities to reach an objective that has a beginning and an end 0 Project management 0 A controlled process of initiating planning executing and closing down a project 0 Project manager 0 systems analyst Witn management and Ieadersnip skills responsible for leading project initiation planning execution and closedown BCIS 4610 Spring 2009 Types of projects External 0 won proposals 0 New product development decisions Internal 0 Corporate IS planning 0 System service request from users 0 Need for upgrade to cut lT maintenance costs BCIS 4610 Spring 2009 Anna Sidorova partially based on BA BOK by the IIBA 1272009 21 University of North Texas Figure 32 swansmmmwmrmmummum swimmmm andcunlnclmiomnimnun c mourn unihngihcynlcmnxlulnm nlui mpmblznl PC lt4 and the nnmczmd mnlum Infnrnmhnn of the umquot and spunxur mumum www nmwam 1w as we mama mm mm mum m mme m mo quotMAM may i Manw Wm l I Mmdnuunhyadd m r x n hwb whimnmm mmnm huanMnmawnmumlmimmlmmmmmeInmmm m mummMimiumenwnw1mumnimuwmaw mummgmhmmum ismaw wumn smu mum swim mum u vmmDurm 7 roascowm nmsvslslswmmsom MunWm Qua4 21 l 1 WWW r 1 WummI x x mamm BCIS 4610 Spring 2009 System Service Request SSR is a form requesting development or maintenance of an information system It includes the contact person a problem statement a service request statement and liaison contact information The Triple Constraint of Project Management auammi pa BCIS 4610 Spring uu7 Anna Sidorova partially based on BA BOK by the IIBA 1272009 22 University of North Texas 1272009 SEVERAL Proiect Management Frameworks exist here is one of them 9 Knowledge Areas Tools and Core Functions techniques Scope Time Cos Quality Mgt Mgt Mgt Mgt r A L w A ProjectManagementIntegration Stakeholders39 r r r needs and expectations 7 HR Lomm Risk Procure Mgt Mgt Mgt ngv Fadlitaling Funuions Phases of Project Management Process Phase 1 Initiation Phase 2 Planning Phase 3 Execution the longest Phase 4 Closedown 30546135ng zuua Anna Sidorova partially based on BA BOK bythe IIBA 23 University of North Texas 1272009 PM Phase 1 Project Initiation Assess size scope and complexity and establish procedures Establish Initiation team Relationship with customer Project initiation plan Management procedures Project management environment Project workbook BCIS 4610 Spring 2009 K Figure36 39 r J PiirchuseF quot nine key documents in both hardcopy rind electronic 1mm l Project ayerview l 2 Initialiun plan and 35R 3 Prnjscfscnpa and risks 4 an mem procedures Pine Valle Furniture 5 Dana deecripxiuns l Inlovmalion Systems a Process descriptions Devalopmemeup 7 Team correspondence 9 Project schedule Purchasing Fulfillment System Online copies at data dictionary diagrams schedules reports etc Manager Chris Mamn BCIS 4610 Spring 2009 K J Anna Sidorova partially based on BA BOK by the NBA 24 University of North Texas 1272009 PM Phase 2 Project Planning Define clear discrete activities and the work needed to complete each activity Tasks Define project scope alternatives feasibility Divide project into tasks Estimate resource requirements Develop preliminary schedule Develop communication plan Determine standards and procedures Risk identi cation and assessment Create preliminary budget Develop a statement of work Set baseline project plan BCIS 4610 Spring 2009 K Planning Detail Figure 37 Lmqu 39 395 39 in l 1 m ngh Medium gt Planning Detail l l l 1 Week 1 Month 6 Monlhs Out Out m Planning Horizon BCIS 4610 Spring 2009 K J Anna Sidorova partially based on BA BOK by the NBA 25 University of North Texas 1272009 Some Components of Project Planning Statement of Work SOVV Contract between the IS staff and the customer regarding deliverables and time estimates for a system development project The Baseline Project Plan BPP Contains estimates of scope benefits schedules costs risks and resource requirements Preliminary Budget Costbenefit analysis outlining planned expenses and revenues BC 4510 Spring 2009 J Work Breakdown Structure WBS example Anna Sidorova partially based on BA BOK by the IIBA 26 University of North Texas Some Components of Project Planning cont Work Breakdown Structure WBS Division of roect into manageable and logicall ordered tasks and subtasks Scheduling Diagrams Gantt chart horizontal bars represent task durations Network diagram boxes and links represent task dependencies BCIS 4610 Spring 2009 Figure 39 39 i 37774 is available for this 7 iv 7 Scheduling Diagrams Gantt Chart van Specialpurpose project management software BCIS 4610 Spring 2009 Anna Sidorova partially based on BA BOK by the IIBA 1272009 27 University of North Texas Scheduling Diagrams Network Diagram Figure 1 11 oriiime it lies mm mmws Specialpurpose project management software is available for this BCIS 4610 Spring 2009 Preliminary Budget Figure 312 Am software is good for this BCIS 4610 Spring 2009 Spreadsheet Anna Sidorova partially based on BA BOK by the IIBA 1272009 28 University of North Texas PM Phase 3 Project Execution Plans created in prior phases are put into action Actions Execute baseline project plan Monitor progress against baseline plan Manage changes in baseline plan Maintain project workbook Communicate project status 5C154610Sp ng 2009 Monitoring Progress with a Gantt Chart Figure 314 mull um u m 4le r and 7 cummma WNW Wu EV Red bars indicate critical path lines through bars indicate percent complete 00 F 5C154610Sp ng 2009 Anna Sidorova partially based on BA BOK by the IIBA 1272009 29 University of North Texas 1272009 If ever thin is oin exactl to Ian something somewhere is going massively wrong A PM joke BCIS 4610 Spring 2009 j K PM Phase 4 Project Closedown Bring the project to an end Actions Close down the project Conduct postproject reviews Close the customer contract K BCIS 4610 Spring 2009 Anna Sidorova partially based on BA BOK by the NBA 30 University of North Texas 1272009 Capability maturity models The goal is improve software development and other similar processes 5 levels Initial processes are not documented and change based on user or an event Repeatable some of the SW development processes are repeatable some basic PM practices are established Defined standard processes are used to established consistency across the organization Managed Process metrics are used to manage SW development processes Optimized focus on continuous improvement of processes BCIS 4610 Spring 2009 J Project management tools BCIS 4610 Spring 2009 K Anna Sidorova partially based on BA BOK by the IIBA 31 University of North Texas Representing and Scheduling Project Plans Gantt Charts Network Diagrams PERT Calculations Critical Path Scheduling Project Management Software 3054610 swung 200g Gantt Charts vs Network Diagrams cont 3034610 swung 200g Anna Sidorova partially based on BA BOK by the IIBA 1272009 32 University of North Texas 3 39 Report Design Programming Testing collectiyf 395th 1 4 T squot gt7i Critical Path Example dependencies between tasks Figure 320 chuuncc ul39ActiVilirs Elhin illc SP39I39S pruch l PRECEDING indicate the activities that ACTIVITY ACTIVITY must be completed before 1 Requirements Collection the specified activity can 3 Sc39ei39ge igquot 1 begin see Fig 319 for epo eStgn 4 Database Design 23 tlme eStlmates39 5 User Documentation 4 6 Programming 4 7 Testing 6 8 Installation 57 BCIS 4610 Spring 2009 Critical Path Example FigureJZZ ut tt iiikin Ht ht m u tni39tltmczttttttttw Screen User Design Documentation Installation Requirements 2 i Database 5 gt a Network diagram shows dependencies Network diagram provides graphical illustration of dependencies between activities see previous slide BCIS 4610 Spring 2009 Anna Sidorova partially based on BA BOK by the IIBA 1272009 33 University of North Texas 1272009 Critical Path Calculation Figure 323 1 11 lni thu l l 39 39 1 u n ilml lw UllrllugttllILlMUMClVLJClL39L1 Vll l l k tlimilllk3910rk dk l1il 1l1Hy TE11 TE185 TE22 TL11 TL21 TL22 7 gt TE5 2 T543 5 8 TLETNLT55 ET1 l 4 39 TE21 rL21 1 3 ETE 1 i 39rE n TE 13 ETNL 11 2 nae 6 39 gt 7 ET 5 ET 3 quot Critical path 39 gtNencrilical p2quot Early and late time calculations are determined and critical path established Note Activity 5 can begin late without affecting project completion time ECIS 4510 Spring 2009 J Critical Path Calculation cont Figure 324 Activit slack lime culcululinns IUl39 thL SI I39S prqiccl ull acih illL S except number i are ml the critical path SLACK ACTIVITY TE TL TL TE ON CRITICAL PATH 1 5 5 0 I 2 11 11 0 I 3 11 11 0 v 4 13 13 0 I 5 18 5 21 25 6 16 1B 0 I 7 21 21 0 I B 22 22 0 I Note the slack time in Activity 5 ECIS 4510 Spring 2009 K Anna Sidorova partially based on BA BOK by the IIBA 34 University of North Texas 1272009 Next Class 0 Project scheduling example 0 Planning and collecting user requirements 0 Project ID is due BCIS 4610 Spring 2009 J Anna Sidorova partially based on BA BOK by the IIBA 35 University of North Texas Designing system logic BCIS 4610 Instructor Dr Anna Sidorova BCIS 4610 Spring 2009 Today s agenda 0 Oracle Developer tutorial cont d 0 Quiz 0 Schedule review HW 4 5 announcements 0 Final report discussion 0 Business rule modeling 0 Designing system logic lecture 0 Logic design example BCIS 4610 Spring 2009 Anna Sidorova Lecture Notes University of North Texas Date SubjectscoveFed Reading Deliverables 25Feo2009 M equot9 busmss ml Chapter 8 HW 2 due Designing system logic 4Mar 20 o399 117Mar72009 Designing databases normalization on 10 Or1uo intermediate Project Review ofSQL 012 3 4 9 Report due 13W hr 2009 SPRING BREAK No CLASS 25Mar72009 introduction to PLISQL Oracle 106 HW 3 due Using Oracle Developer n5 6 7 8 10 taAprVZDDQ Using Oracle Developer Oracle 106 rm pro rep update Hands onatutorial h 5 6 7 8 10 due gAszmg Designing interfaces Chapter 11 12 HW 4 due nanoour WADFZDDQ System selection customization Chapter 1339 15 HW 5 due System rmplementatron 22Apr2009 Team project work other topics Fma39 Remquot d39a zaApreztmg Project Presentations other topics NIA eMayezth Final Exam Review TBA project Final Report k BCIS 4610 Spring 2009 Entity M Crud Matr Functional Hierarchy Diagram Interim report Title page anci table of contents 0 Executive summary 0 Discussion of the current situation 39 Project vision and scope Nonrfunctional requirements De nition of Work for Stage 2 forms reports plan Appenciix most important part 0 Level 0 and Level 1 DFDS o Business ProcessMaps ix odel Reference Report Function De nition Re ort o Data ow and data store de nition reports BCIS 4610 Spring 2009 Description of system requirements with references to the appendix Anna Sidorova Lecture Notes University of North Texas How our topics relate to the SDLC Collecting req s Plannmg DFD ERD Process models Business rules xllalySis Relational DB SQL PUSQL Design Normalization Oracle deSIgner b L ogic modeling Interface design BCIS 4610 Spring 2009 J Business rule modeling Designing system logic BCIS 4610 Spring 2009 Anna Sidorova Lecture Notes University of North Texas Business rules modeling 0 A Business Rule is a statement that defines or constrains some aspect of the business 0 A business rule describes a policy guideline standard or regulation upon which the business operates 0 The Purpose of Business Rules Management is to Provide a formal structure for the identification documentation and maintenance ofbusiness rules and allow for the implementation of those rules BCIS 4610 Spring 2009 Verbal description of business rules 0 A Business Rule is usually captured as a textual statement that defines the rule exactly and unambiguously Each Business Rule has a unique identi er 0 Example 1 BR654 Payment of employee expenses requires the approval of a manager at Level 5 or above 0 Example 2 BR728 Claims for employee expenses must be submitted for approval BCIS 4610 Spring 2009 Anna Sidorova Lecture Notes University of North Texas Common types of Business Rules 0 Term de nes the meaning ofa word or hrase with a s eci c meaning to the stakeholders The term must be unique ang includes a de nition of what is known about the n Fact describes the relationship between two or more to Relationships may include one term being Part of another one term interacti with another or any other relevant connection between the two A derived fact is one t is computed or inferred from other facts e g if a always relates to b an ways relates to c then a must relate to c Derivation are used to create new information from existing information A derivation may be the result of a calculation eg the duration of a Process is the elapsed time between the start date and the end date or the result of a logical inference based on known information Assertions state what values of a term or fact are considered valid by the business under given circumstances Assertions can be Iurtner broken down into tnree types o Authorizationrrule 39 39 wiiaiiu aperson may perrmm v i o Condition 7 speci esacircumstance under which another business rule may be applied o Inte rityconstraintr Speci es whichvalues ofaterm or fact are permissible givenaspeci ed value of 0t er term or act Action Enabler rules trigger an activity or a message if a certain condition becomes true BCIS 4610 Spring 2009 X J Logic modeling techniques 0 Complex business rules need to be modeled with logic modeling techniques 0 Activity and sequence diagrams in CO 0 Decision Trees 0 Decision Tables 0 Structured English 7 at the design stage BCIS 4610 Spring 2009 Anna Sidorova Lecture Notes Unwersny uf Nunh Texas DECISION TABLES nmntnxn Pmmmn gig91 in amm m1 Wyn uquot my mamm uquot amm m1 uquot may Mm amsmn spmmu Condition Stubs Conditions Anna Swdorova Lecture Notes University of North Texas Components of a Decision Table 0 ACTION STUBS 0 The Part ofa decision table that lists the actions that result for a given set of conditions 0 CONDITION STUBS 0 The part of a decision table that lists the conditions relevant to the decision 0 RU LES 0 The part ofa decision table that specifies which action items are to be followed for a given set of conditions BCIS 4610 Spring 2009 K CONSTRUCTING DECISION TABLES 1 Name the conditions and the values each condition can assume 2 Name all possible actions that can occur 3 List all possible rules 4 Define the actions for each rule 5 Simplify the decision table BCIS 4610 Spring 2009 Anna Sidorova Lecture Notes Unwersny uf Nunh Texas Condition Stubs Condmons INDIFFERENT CONDITION In 2 deci nn able 2 candi nn whnxe value due m a en far w 2 rule which 2mm are uken n or mar amsmn spmmu Anna Swdorova Lecture Notes Unwersny of North Texas Condmons amsmn sman Conditions Courses of Ac on amsmn sman Anna Swdorova Lecture Notes University of North Texas Condmons Txme ofweek Season ofyeaz penshable D weekday A w weekend 5 summex H hohday am m u 75mm 2mm Condi ons Courses of am m u 75mm 2mm Anna Sidorova Lecture Notes University of North Texas USing a deCiSion table represent the logic Of a login process 0 The user submits login and password 0 Iflo in or password are empt 7 return to the login screen with the Empty message 0 Ifiogin is incorrect 7 return to the login screen with the User not found message 0 Ifiogin exists but password incorrect 7 return to the login screen with the Password incorrect message 0 Ifiogin exists and password is correct 7 display welcome screen OPCII user SCSSiOH BCIS 4610 Spring 2009 K Step 1 Define condition stubs Login Password BCIS 4610 Spring 2009 Anna Sidorova Lecture Notes University of North Texas Login Password Step 2 Define the rules BCIS 4610 Spring 2009 Does not exist Corr K Display empty required field Display incorrect Display incorrect DisplayWelcome smtusa session Step 3 Define action stubs Login Empty Does Empty Exism Does not Empty not exist Password Empty Empty Empty lnco39n lncorr lncon Corr BCIS 4610 Spring 2009 Anna Sidorova Lecture Notes University of North Texas Step 4 Define actions for each rule Login Does not exist Passwonl Corr Corr Return to the X D sPlay emPty i required new DisPlay incorrectlogin D incorrect PW Step 4 Define actions for each rule Login 7 Exisls Does not Exism Password 7 Erun lncorr 7 Con Remm m DisplayWelcome Smtusex session BCIS 4610 Spring 2009 Anna Sidorova Lecture Notes University of North Texas DECISION TREES A graphical representation of a decision situation in which decision points nodes are connected together by arcs one for each alternative decision and terminate in ovals the action which is the result of all the decisions made on the Path that leads to that oval BCIS 4610 Spring 2009 j Sleep 2 more hours Weekday Time to get up Sleep 1 moe hour Legend 1 51111 up Go back to sleep 2 What day is it BCIS e 10 Spring 2009 j Anna Sidorova Lecture Notes Unwersny of North Texas mm 39W Absancexagprt 39 anhourly vz g39a Legen V 1 Salaried f39YhWV a z Hnmgcwg kgg n A03 Pay ovartxmewage 3 Hnurswmfmf 933 Conditions Courses of Ac on amsmn sman Anna Swdorova Lecture Notes University of North Texas Salaried gt 40 Legend 1 Type of employee 2 Hours worked BCIS 4610 Spran 2009 Logic modeling 0 Logic modeling is also used to describe the Processing logic Within a process Slucl on Haml luv to Leveng W Anna Sidorova Lecture Notes University of North Texas Structured English Modi ed form ofthe English language used to specify logic of information system processes 0 Uses only verbs and nouns 0 Should be understood by both users and Programmers 0 Represents internal logic ofa process BCIS 4610 Spring 2009 Process 20 Update Inventory Used DO READ next Stockitem record FIND matching Inventoryrecord SUBTRACT Quantityused from Stockitemrecord from Quantityinstock UNTIL Endof file BCIS 4610 Spring 2009 Anna Sidorova Lecture Notes University of North Texas Wh ic h to U se Criteria Struct Dec Dec English Tables Trees Determining conditions amp 2 3 1 actions Transforming conditions amp 1 3 1 actions into sequence Checking consistency amp 3 1 1 k Q 61n nrin 9009 K In Class exercise 0 Model the logic for the Income Tax deduction Calculation 0 AssuIne the following standard deduction is 10 700 for married ling jointly 5 350 if single 7 850 if head of the household 0 You select a standard deduction if your itemized deduction amount is smaller than the standard deduction 0 AMT is not applicable to you 0 Draw a decision tree decision table and write Pseudoecode BCIS 4610 Spring 2009 Anna Sidorova Lecture Notes BCIS 4610 Oracle 106 Form Builder and Report Builder By Anna Sidorova Tutorial plan Create an ERD in Oracle designer Translate the ERD into a relational model in Oracle designer Create modify tables insert select data in SQL Select and output data in PL SQL Create interface in Oracle designer Create a form based on a table ortwo tables Create custom COURSE form Create switchboard Create reports introduction to Oracle Form Builder 2 By Anna SidorovaIntro to Oracle Form Builder BCIS 4610 Example STUDENT STLDCDURSE SID Name Major G39adei zge Lew1 CourseName 100 Jones H151 SR 21 150 Parks Acctg so 19 100 S5 200 Bak Nb GR 32 150 ACCZZO 250 Glass History SR 28 200 SS 300 Baker Acctg SR 24 200 c3131 350 Russell mm IR 213 3w ACC415 400 Rogers Acctg FR 18 400 ACCZZO 450 Jones Hstorz SR 25 400 CSBIZ COURSE 400 MAT101 450 ACCZZO RUUWL ACCZZO M F9 BA110 ACC415 MWF3 BA210 CSISIZ MWF8 3213 MAT101 MWF3 E3123 520 MWFlZ BAZZA mvuductmmu Ovar e Fuvm Eu dev 3 In Oracle Des gner create an ERD 5n mam I up cm 41quotL h391 04th n 39 mm mm mm m Wm m mem mm 1 III awjnw mmducuumu Ova e me Eu dev By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Database Design Transformer Allows to transform an ERD into a database server model Produces Server model and Table definitions In order to run transformer Open Database Design Transformer Select the container A Diwaliun 5mm 39 Daniainer EA 6 1 min IIW Transforming the Database design On the database design transformer dialog box view and nErAuLLDATAaAsE Deiauit ulage new I7 31mm tab 31 ganuv Way s Cummnivequzncyiur mange r Meream whale F k and afiun f Dam cummil Bun cm 5an Run SH 9 t Cancel Help By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Editing the Database design Open the database design editor Select Server model Select container and click CreateEdit DB objects r nuirhmad rohm Select Tables and Columns Mme Wasmme l39l39hatllmnagm Ill w m wwwmm um mmddamm mlh um mmudlu amun u an my 04ij n32 m mm mm Ensign spasm unaw mi Maura 1 Ml srwuw Editing the Database design cont d SEIVEI Muriel Guide 7 Tables and Cnlumns EC45107117WK Select all tables and click wea113433Esta12atrrmm Diag ra m 1 mquot 3quot gm m m m m m Wale hm Now you can view and edit 39 the database design stemmed Help 5mg Slams Starting Oracle Designer 8 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Generating tables Once you are satis ed with your DB design you can use the Design Editor tool to generate table for you r oracle Database Go to MenuGenerateGenerate Database from Server model You will need to provide login information for the DB once again Click Start Starling Oracle Designer 9 Generating tables Somewhere along the way a message will pop up DDlGenzmmnCnmlzkm gtq DDL genevalmn l8 camplelE Dayna wanl in View lhegeneialed DDL View lhe iacanmle ievml m execule an inn Fm m use cdsddlcabl Cllck Vlew DDL Data 7 Generated for Grade 91 on Tue sep 26 154355 Definition Language 2325 i ESoESElE mm A number of text les 2333M3E t 30 r LDESC VARCHARMZw WI Open SEE EECVXSE ZSE E Make sure you save a ROMPT Creating Tame 39ORDERS39 of them 352353355 giggingdgor Mm 8R83MT EX 5 22A ZOZO You can now cIIck gagingga msz aam Execute DDL I By Anna SidorovaIntro to Oracle Form Builder BCIS 4610 Generating tables cont d A progress report will tell you if tables were created successfully Emmalunar r label ream anary Key un 39ORDERS39 realmg Prlmary Key un 39oRDEstRonucTs 39 MERS39 S TO realmg Furelgn Key nu 39ORDER DL execulmn complete rucessmg Cemplere n 2nursl n warmnglsl Starting Oracle Designer 11 Checking your tables in SQL Plus 0 You can now check if the tables have been created by logging Into the SQLPlus typing DESCRIBE TableName oracle SQL39P us I 2 gm gem 9mm dalD SuLxPlus Release 1n1nu2 7 Prnnuctiun nn wen npr 1 132713 znny copyright c wez 2nns name an rights reseruen Ennnecten tn kacle Database 1n Enterprise Editinn Release 1n2n3n Prnductinn with the Partitinning nrnr ann Data Mining nptinns uL nescrine students Name Null Type Nnr NnLL NUMBEMSE nan unn22 unncunn21s mum unncunnzun saw v lt l gt Starting Oracle Designer By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Modifying tables entering data Modify table student to add a column Age Enter data at least two rows per table using insert statements Commit Run select statements to see if all the data is insened ll itroductlon to Oracle Form Builder 13 Create and run a PL SQL program which would return the average and maximum age of all students SET SERVEROUTPUT ON SIZE 4000 declare maxage integer averageage integer begin select maxage avgage into maxage averageage from students DBMSOUTPUTPUTLNE39The oldest student is39 H maxage 3939 DBMSOUTPUTPUTLNE39The average age is39 H averageage 3939 end ll itroductlon to Oracle Form Builder 14 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Start Oracle Form Builder 2 aquot u mum ntroducuon to Orade Form Bquer 15 Oracle Form Builder 9 Oracle Farms MODULE1 Fae Em ew Layuut Pmngle 2mg lads Mndaw an Di x s WdeW Menus msqx Lxhrana amen Uhvanes Mm padages LDa hase Objects By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Change the runtime preferences Go to EditPreferences select Runtime tab Change the default browser to Firefox PWi nAppsFi refox2fi refox exe l Debug Mgssages l Quiemade Reset m Qefault W m introduction to Oracle Form Builder l7 Create a new form based on MODULE1 Go to FileNewForm a new module is added to your object navigator window Click on MODULE2 and rename it into Amid uummmmw w r By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Create a data block based on a table Right click on the STUDENT module and select Data Block Wizard Click nex on the Data Block Wizard welcome page Williinmllxq clamw lritroductlori to Oracle Form Builder 19 Creating a data block cont s Select Tableview option Select a table by clicking on Browse button r gt1 i W m I n u r i M I y I introduction to Oracle Form Builder 20 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Selecting a table cont d When you click browse you will be prompted to log in Login into your usual account and select table STU DE NT from the displayed list lntroductlon to Oracle Form Bullder 21 Select database items Move all the attributes into selected attributes pane using arrow buttons zl h la base your data black Then select the columns that Cancel Help lt Back Next gt Flmsh 22 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Name your data block Ehtet a name ratyam data Mack Data ataek Mame swam lt Back Next gt rthteh aheet Hetp ntroductton to Orade Form Buttder 23 Select the then call the Layout wizard option A Vau have hhtehed descuhmg yam data Mack aerateyau chck rthteh ta cveateyum new data Mack chck a Vadm button ta tett the Data B ackWtzavd what yau wam ta do hext r tests the data Mack theh eat the Layautwtzatg Amt meats the data much You can atsa use the Data Btack w39tzam la mamy yam extslmg aata Macks shah seteet the data Mack h the nbteet Navtgatm aha chck the Data B ackWtzavd taatnat tmttah m ehaaee Data B ackWtzavd hath the Taa s menu caheet Hetp lt Back Nex gt rthteh ntroductton to Orade Form Buttder 24 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Creatin a layout Layout wizard allows you to create a form layout Click next on the welcome page and select content canvas type K 1T m 1 v4 M in FWT39I l inlruducliunlu Oracle me Builder 25 Select items to be displayed Select items to be displayed Modify the labels prompts and widths of corresponding text boxes if necessary m 39 u 397 inlruducliunlu Oracle me Builder 26 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Select a layout Select form layout Name the frame Select the number of students to be displayed meuahMuumnrurmuwm a u E h I m i in H m7 1m 1 lnlroduclionlo Oracle Form Builder Complete and view the form Complete and view the form design quot 491 7 7 few L mgram 2mg 1a ilhnwip Di iKEiS bimim 2 quotNEW gmo mavasastu Ag MW v NW Imah ll j iBJHiethiE39aa ww f an SPWiBMHHHHHHHHHH in 391 lnlroduclionlo Oracle Form Builder By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Modifying the form To change color select the form of its element Go to Property palette Modify Foreground and background color properties Add a title to your form Add a label using the toolbox on the left lnlruducllunlu Oracle Farm Builder By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Convert Major into List Item In the Property palette change item type to list Converting Major into a list item Add item values using the property palette mm Value I Eanml as lntroduction to Oracle Form Builder By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Testing your form Go to ProgramRun to view the form Use Enter Query button to enter the query Use Execute Query button to View corresponding records 4 443 i 7g lnlvuducliunlu Oracle Fuvm Builder Creating a form based on two linked tables In order to create a form based on 2 tables Create a form similar to the STUDENT form we created name it REGISTRATION Create an additional data block using a wizard Establish masterdetail relationship lnlvuducliunlu Oracle Fuvm Builder 36 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Creatin a form based on two linked tables Follow the steps suggested by the wizard lnlruduclmnlu Oracle me Bullder 35 Creatin a form based on two linked tables Forthe STUDCOURSE frame select tabular layout 4 records per page and a scrollbar I r a yrrl m lnlruduclmnlu Oracle me Bullder 36 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Compile and run your form ll39lll Elle gale mew Fg vnma Innls us la mk v v D 9 st Favnmes g1 vi A drassl m WHENahaunteduBEBENurmsg muservlel lfyrmDTearhlngpr dejurms 5CHEDV ca lunks l 39 ACCZZEI l Il W FFFFFW lntroductlon to Oracle Form Bullder Create an LOV Rightclick anywhere in module REGISTRATION and select LOV wizard Select record group based on a query Farms lawnl gPr layuutEdnnr Q ezmhraw a E W Demandva W Laywrleavd lntroducuon to Oracle Form Builder By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Create an LOV Type in a query that would retrieve all records from table course do not put a semicolumn at the end Here you will may be asked to log into the DB wkuml amp qudalmumhmmikm hmwnwum ammom m 1 W mm m7 u m Wm mum quotmm mm m mama Em sm 0 m lmmmmmmxwmlh m msmmlwm Mam away I39m 5m Duty mm Wl mn mgr lnlroducllon to Oracle Form Bullder 39 C reate a n LOV Select all the columns that you want to appear in you LOV 11 Vam LUV may mclude same m all al lhe calumns m lhe Recmd Emup Whmh Recmd Emup columns do you m m lnclude m yam LBW Becmd Emuu Ealumns LUV Ealumns gtl gtl Ll ltl am Help um W W lntroductlonto Oracle Form Bullder 40 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Create an LOV Specify the return value for COURSENAME field Return value is a field where the course name from your LOV will be inserted lOV Wizard 39 A llyau my in specify lhe my calumn Diaveiliesyau may enlei a lille mm and ielum value lav each my calumn The mill lav lhe calumn mm c Pam Cancel Help Introductionlo Oracle Form Builder ltBack New my 41 Create an LOV Specify title and dimensions Specify the number of rows displayed at once 5 cmmmmmmmmcmma cummmmmmmmmmnmmumum llywnmnmhmhluuuwrsi mmuaum m cicw ume c 2 w mmoumiyummwrmniumlmmwpnmu 9 quot f M r mwucmmmmmm r ks mumllnmm uamu 5m W W 1quot thamfumnmwmmmlaw r vuurmpmmwuwm r m lmmmumvm rm M i am innn w c m w x W Introductionlo Oracle Form Builder 42 By Anna SidorovaIntro to Oracle Form Builder 21 BCIS 4610 Create an LOV Select COURSENAME as assigned value Complete your LOV lnlmducliunlu Ovacle me Buildev a3 Create an LOV To test your LOV Run the form Click on CourseName field Press CTRLL lmmuucumw By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Create an LOV control button Close your form in the browser Return to the Form buikder Layout editor Click on the Control Button item in the toolbar on the left and drag it to a desired location on your canvas Name your button 39 B M m using Property pallete inlruducliunlu Oracle me Builder f nmi m m lammnammw llimnmmmwn WNW Create an LOV control button Right click on the button and open PLSQL editor Select Type TRIGGER Name WHENMOUSECLICKED Type in code the code below mcleFurlIISBmld unadrwwndejmmmsrmum I211 5 39 2 l 39 iE iWi ni r ml E El Nama1wHENMcusEcua L Yyne 1mm 1 I saECLcouRsE L 39 Succesde Emmi i By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Create an LOV Compile and test your button Creating a custom form Create a new form module and name it COURSE Click on data bloc and then on the plus icon Select Build a data block manually Rename your block into COURSEBLOCK mums Dang Oak gas gun Mew Lav2m Evngvam minim new Di lea ls blatl l l 1 Create a new data black 1 Use the Data Blodlt wizard lMad EDURSE EXAMPLE Filei LlRSE EXAMPLElil introduction to Oracle Form Builder By Anna Sidorovalntro to Oracle Form Builder 24 BCIS 4610 Create and modify a canvas Add a canvas by clicking on Canvas in object navigator and then on a plus sign Rename your canvas into HEADER and open it with Design Editor ml nllklmm l mu v gt 399 1 Agi Etkwwuwmwwumm igm m mm 6 mnqu r a I y lt quot i l i i u l i u i l i u l l l I 3 El l Introduction to Oracle Form Builder 49 Create and modify a canvas Change the background color of the canvas using the Property palette Add a header Course Information Add a picture download any picture from the web save in in Ctemp then go to Edit Import mm r em 7 L mr jm war jtmoo x quotW39 39 J39IH39 Q61 5amp5er 7 mu i app nmmx u Course Information By Anna SidorovaIntro to Oracle Form Builder BCIS 4610 Add text boxes and prompts Using the toolbox on the left add Course Name text box and a corresponding prompt aiomdgramsmuqrumouuz 7 Ag 1 e Edit law Lawn Emgvam gm Innis Llindaw new Diaaixuaualabimi i lgt Guzman llE i l 392 U lIoDIILEt HEADER OHRSEJLOCK ems HEADER v aim counswwcr v lea43 ll 12 vlnzleQl 39aaW Course Information E Course Name COURSENAME E Rnum ROOM Time ME J Ll Want MUDULEZ lFiie MDDULEZ Introduction to Oracle Form Builder 51 Add text boxes and prompts Using the toolbox add a stacked canvas on COURSE canvas Name your canvas COURSEITEMS Add Room and Time text boxes and corresponding prompts to the COURSEITEMS canvas mommrmmuurctm i El am aw Leinut Enigma 22M Innis wrian use D agixm iehla QR w uingig E1 32 Room ROOM 0 W 58 v39 u J l p v i gt W i l Introduction to Oracle Form Builder 52 By Anna SidorovaIntro to Oracle Form Builder 26 BCIS 4610 Add control buttons Return to the main canvas Using toolbox on the left add four control buttons Name them New Find Save and Clear introduction to Oracle Form Builder Adding PLISQL code Create triggers for each ofthe buttons Add PLISQL code to the triggers elem Ella Edit wew Lay1m Emgvam Debug wk wind1w new D aelxeelaalm sieel Gusts II I leer 392 anti war E ElwameW memes j muemlcuuRSEjLocK lm no he uses 11152 1110 course ses cuureibluckcuure cuurseiblucdeme cuurseibluckruumi messagEL39Ohe ecuzd es bee e quotsugar messagei39YD need 0 Secfy re cuse rme39i end 2 men dupivaliunilndex men messagei39A cuse Wu Ere Sane nine aleady exss39i end 4 Module mm successluiiy gt Mad EDLiRSE introduction to Oracle Form Builder By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 u 1 e er Maw Lawn Erngvam Ban Inn s mdaw new onquot SMEHQR V EEE Eilltggttq Al gate r7 r awe WHENM uusicum rvpeTn ger j uhmgtCDURSEEmCK lFxNu eee e e eeeeee yp eeeeee heme nemewee e e eeeeeeeexeeeye em eeeeeeeeeemee egen eeeeeeeeiezeeeeeeeee t e ee nee he rhen me enee eeeeeeieeee eeeeeeime need to seem e me be eee to f0 end e eheemeu ewer ltI I quot Madurehmreueeeemr Mad E uRSE r ntroductron to Orade Form Bquer Adding PLISQL code Create triggers for each ofthe buttons Add PLISQL code to the triggers DECE EIII ntroductron to Orade Form Bquer By Anna Sidorovalntro to Oracle Form Builder 28 BCIS 4610 Adding PLISQL code Create triggers for each ofthe buttons Add PLISQL code to the triggers introduction to Oracie Form Buiider 57 Adding PLISQL code Create triggers for each ofthe buttons Add PLISQL code to the triggers Nat Madmen introduction to Oracie Form Buiider 58 By Anna Sidorovalntro to Oracle Form Builder 29 BCIS 4610 Creating a switchboard o Create a new form o Create a control block similar to the one in form COURSE Add four control buttons Add student Add course Register student Exit Add code to open forms introduction to Oracle Form Builder 59 Creating a switchboard Welcome to student registration system m couRsE ADD 5mm Rama mm ii 39quotlt a Aquot gtli introduction to Oracle Form Builder 60 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Creating a switchboard Add triggers to corresponding buttons Eie gm mew Layaut Emgram E mg Innis mm tau if 5 swimwear i Name WHENMousEcucK l 3 Mai swrrcHeoARu j I mumsz we pm pm t jumwmsmmu mum szssmw tummy Igtll m imruductiun in Oracie Farm Euiider Creating reports imruduciiun in Oracie Farm Euiider By Anna Sidorovalntro to Oracle FOIIH Builder BCIS 4610 Openin report builder Use report wizard to create a report Welcome to Reports Bu der Where u slay Demgmng a r gmxdam nepunmanuauy r Qpen an existing vepult quot Learning r Eunihe uickTourlcumpts 17 Qwsday alslavhlp Cancel He p ntmductmntu Ova e me Emma 21 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 lamunma v4quot Wmmm Im Rmnwmaml wurmnwnmumum mkh mmmim amp uummnmwlhmnumrmn Using report wizard h nrum nmmnww r mama1m rumpum imruductiuhtu Oracie Farm Euiider Click OK on the welcome page and then specify the type of report that you want to create mum Wmard Using report wizard Name your report and select the desired ster time the ravmlsiyie m m ciase xresemhies ihevepmi M Ma hke m cveaie m mayaisa inciude a We r smaem Emaiimeni r gmupLeri r mauuAbwg r Maw r M 4 w w Mm mun r FDMLenev r MaihnnLabsi El imruductiuhtu Oracie Farm Euiider By Anna SidorovaIntro to Oracle Form Builder BCIS 4610 Using report wizard Select SQL query as a basis for the report Build your query you may be asked to log in at this point nmvu Eraa 5 I j Annay E laun mum Hsei Name i Daiabase Eancei Heiv introduction to Oracie Form Buiider 67 Using report wizard Designate columns as group fields 51m h mu 1mm watle like u denigrate a yuup ijaras nu liwn an by unique Avaiabia Fiaida39 gnu 5315c A39 and 1 A EDUHSENAME A RDDM Ar 1ng introduction to Oracie Form Buiider 68 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Using report wizard Select columns to appear in the report 7 it fella the eld lhalywu walla llke In dlsplay lnywl lEDalLYW may and Wm llelds amt splaye d H ld Available elds rm y Al RDDM 51 NAME Al mun 34 EDURSENAME Jig A E Jgj l l Cancel lnlmductlnn in Oracle Farm Builder Using report wizard Specify labels and field length for each of the items lntmductlnn in Oracle Farm Builder By Anna SidorovaIntro to Oracle Form Builder BCIS 4610 Using report wizard Selecta template 1 haa39 aa rempxar e Var yak yepamam yenmtwm mm Ahelem aje colovs rum in mm arm mum r gyeagnqemgwaw Ber e m cum mm M 7m Mm w r lemphaleme mws r Malemp alb ntroducuon to Orade Form Bquer 71 Using report wizard Finish and view your report gnu a 152 M as Illl nan u ntroducuon to Orade Form Bm der 72 By Anna Sidorovalntro to Oracle Form Builder BCIS 4610 Viewing Web report muuuucuunm Ovame Fuvm Emma 73 By Anna Sidorovalntro to Oracle Form Builder University of North Texas Logical Data base Design SQL Lecture 7 Instructor Anna Sidorova BCIS4610 Spring 2009 Agenda Schedule review HW 2 due HW 3 Assignment Exam Results Review of the Analysis phase VVVVVV Logical database design BCIS4610 Spring 2009 j Anna Sidorova Lecture notes University of North Texas Review of the analysis phase 0 Plan requirements collection 0 Collect s1 stem re uirements 0 Document system requirements 0 Model processes 0 Model data 0 Model business rules 0 Model user interface requirements usually combined with design 0 Manage change in system requirements BCIS4610 Spring 2009 We will switch to some design and implementation issues next o Logical DB design o SQL 0 PL SQL 0 Oracle Developer tools BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas Logical Database Design and the Relational Model BCIS4610 Spring 2009 K K O b JeCtIves De nition of terms List ve A roA erties of relations State two proper ties of candidate keys Transform EeR and EER diagrams to relations Create tables with entity and relational integrity constraints BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas Relation Anh nm anamed wardlmensmml table of data Table I made up ufmw recur mdculumns atmbute ur eld Every relauunhas aum wine 1 que name Every attribute 5 1mm nut multivalued nut cumpuslte t have wuh manly m Every ruwls umque an tvm raw e same wins mall their el Atmbutes culumns m tab e have umquemmes r t e autumn is rrrelev t Them u thervwslnrrelevmt ECtStEtU Spnng znua Anna Sidorova Lecture notes University of North Texas Correspondence with ER Model 0 Relations tables corres ond to entit t 1 es man itoiman relationshi types 0 Rows correspond to entity instances and manyitoimany relationship instances 0 Columns correspond to attributes 0 NOTE The word relation in relational database is NOT the same same the word relationship in ER model BCIS4610 Spring 2009 Key Fields 3 0 Keys are special fields that serve two main purposes 0 Primal keys are unique identi ers of the relation in question Examples include employee numbers social security numbers etc This is haw we can guarantee that all law are unique 0 Foreign keys are identi ers that enable a dependent relation on the many side of a relationship to refer to its parent relation on the one side of the relationship 0 Keys can be simple a single field or composite more than one field Keys usually are used as indexes to speed up the response to user queries More on his in BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas Figure 53 Schema for four relations Pine Valley Furniture Primar Ke Forei nKe rmpiemerrts i Nreiatrorr rp between customer and or ert Combined these are a Campositz primary k2 uniquelyidenti es the order line individually they are 70min k2 imrlementMN relationship between order and product PRODUCT Productin ProducLDescriptlon FruducLFiiish Standardjnce amend Not in Figure 122 ior simpliciiy ECiSAEtD Spring 2009 Integrity Constraints 0 Domain Constraints 0 Allowable wlues for an attribute See Table 571 0 lntity Integrity 0 No primary key attribute may be null All primary key elds MUST have data 0 Action Assortions 0 Business rules Recall from Ch 4 ECiSAEtD Spring 2009 Anna Sidorova Lecture notes University of North Texas Integrity Constraints Rename InteurltJ ute that state that art Mann 121 Va us m1 re atmnafthe maq ndeSTyyyyyv1m1 be null Fm example Delete Rule mm 7 dun t 111w delete gr 39paxmt side lfrelaiedrvw exist m39dependmt me asmder mtumuauy delete39depmdmt side raw um currespundwuh Lhe39paxmt ads raw C tube ate m u nut alluwedfur weak enuus ECtStEtU Spnng znua Anna Sidorova Lecture notes University of North Texas Referential integrity constraints Pine Valley Furniture CUSTOMER Customs cuswmeLName Referenttal Oman i cages integrity constraints are ORDER LiNE drawn via mows oyasuu ProducLlD from dependent to parent table i PRODUCT Prodmun ProducLDascriprn FrodumFinish smnuaanee OmHand i ECiSAEi n Spring 2mg Transforming EER Diagrams into Relations Mapping Regular hntitics to Relations 1 Simple attributes EVR attributes map directly onto the relation Composite attributes Use only their simple component attributes or Mul rvalued Attribute r Becomes a separate relation with a foreign key taken from the superior entity ECiSAE i n Spring 2mg Anna Sidorova Lecture notes University of North Texas Mapping a regular entity a CUSTOMER entity type with simple attributes b CUSTOMER relation CUSTOMER CUSTOMER Customer ID CustomerName CustomerAddress Customeerddress BCIS4610 Spring 2009 J Mapping a composite attribute a CUSTOMER entity type with Customer Name attribute CUSTOMER b CUSTOMER relation with address detail Customeer CustomeiName Street City I Slale I Zip BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas Mapping a multivalued attribute a Emgioyeeun Empioy me Empioyeuddress 7 EMPLOYEE Multivalued attribute becomes a separate relation with foreign key 3 EMPLOYEE 77 Emgioyeejn EmployeeiName EmployeeiAddress EMPLOYEEisKlLL EJEEWEJD i 5M 1 to many relationship between original entity and new relation BCIS4610 Spring 2009 J Transforming EER Diagrams into Relations Mapping Weak Entities 0 Becomes a separate relation With a foreign key taken from the superior entity 0 Primary key composed of Partial identi er of weak entity Primary key i39identii39y ino relation strono entity J J J b b J BCISAG10 Spring 2009 Anna Sidorova Lecture notes University of North Texas Figure 5 11 Example of mapping a weak entity a Weak entity DEPENDENT Middleilnitial Dependen1Name DaieofBith EmployeeilD EmployeeName EMPLOYEE BC84610Sprlng 2009 Figure 5 11 b Relations resulting from weak entity NOTE th d 39 constrainteforomeallzoreign key snould NOT allow null EmployeelD EmployeeName L z nELEyPENDENT 395 a A surrogate key would be a better choice in this case DEPENDENT Fore gn key FirstName Middlelnital LasLName EmployeeID DateofBirth Gender Composite primary key BCISAB IO Spring 2009 Anna Sidorova Lecture notes 11 University of North Texas Transforming EER Diagrams into Relations Mapping Binary Relationships OnertorMany 7 Primary ltey on the one side lieoomes aforeign tey onthe many side ManyrtorMany 7 Create anewlelatirm With the primary lteys oi the two entities as its primary ey OnertorOne 7 Primary ltey on the mandatory side lieoomes a foreign ltey on the optional side BClS lGlU SDllngZJm Example of mapping a 1M relationship a Relationship beiween customers and orders fus mlv amz Omeer BElMGlU Spiihnmm Anna Sidorova Lecture notes University of North Texas Mapping the relationship CUSTOMER CustomerD CustomerName CustomerAddress gain no null value in h amie mandatory ORDER cardinality OrderiD OrderDaie CustomerlD k EiCiSAEiEI Spnng zuua Foreign key l e foreign key Lhis is because minimum J K Example of mapping an MN relationship a ER diagram MN Unmet Vendor Measure Address Name RAW MATERIALS quot VENDOR l The SLgppliex relationship will need to become a separate relation EiCiSAEiEI Spnng zuua Anna Sidorova Lecture notes University of North Texas Three resulting relations RAW MA39ERIALS re MateriallD SianclardCost UnitciMeasure Composite primary key l OUO New relation Foreign key VENDOR l 80836105th92009 Mapping a binary 11 relationship a Binary 11 relationship l BClS lBlU Spring 2009 Anna Sidorova Lecture notes University of North Texas Resulting relations NURSE DateofBirth CARE CENTER Location NurseinCharge CenterName Datessigned 5054510 Spring 2009 Transforming ER Diagrams into Relations Mapping Associative Entities 0 Identifier Not Assigned Default priInary key for the association relation is composed ofthe priInary keys of the two entities as in MN relationship 0 Identifier Assigned It is natural and faIniliar to endiusers Default identi er Ina not be uni Aue 5054510 Spring 2009 Anna Sidorova Lecture notes University of North Texas Figure 51521 Mapping an associative emit 7 An msocimh c enlhy OrderecLOHanIIly BCIS4639I 0 Spring 2009 Figure 5151 Mapping an associmivc cnlil 7 Three rcsuhing rclaliuns ORDER ORDER LINE ProducLID OrderrlD Ordered Quantity PRODUCT ProductD ProducLDescription PraducLFinish StandardiF nce ProducLLineilD BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas Mapping an associative entity VendorlD a Associative entity 4 3054610 Sprlng 2009 VENDOR Three resulting relations CUSTOMER CustomerlD Name SHIPMENT E ShipmentNO CustomerlD Vend0rlD Date Other Attributes Amount VENDOR VendorlD Address Other Attributes BCIS4610 Spllng 2009 Anna Sidorova Lecture notes University of North Texas Transforming EER Diagrams into Re I ati 0 ns Mapping Unary Relationships 0 neetoeMany 7 Recursive foreign key in die same relation 0 ManyetoeMany 7 Two relations One for the entity type One for an associative relation in which le primary key has two attributes both taken from the primary key of the entity BCIS4610 Spring 2009 Mapping a unary 1N relationship a EMPLOYEE entity with Manages relationship b EWLOYE EMPLOYEE Erelation Empioy iD Name Birthdate M with recursive foreignkey BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas Q Q lrem o Nam iTEM Je a Billof aterials quot relationships MN ITEM Mapping a unary MN relationship Hermit N u LC t bITEMand r a 5 COMPONEN COMPONENTquot Trelations iiemNo ComponenLNo Ounnmy ECiSGEiEI SpnngZEIEIB Transforming EER Diagrams into Relations associative entity 0 Associative entity has foreign keys to each entity in the relationship BCiS tBiD Spnng zuua MappingTernary and nary Relationships U116 relation or each entity 3110 one or me Anna Sidorova Lecture notes University of North Texas Figure 519 Mapping a ternary relationship a Ternary relationship with associative entity m I TREATMENT 80836105pr 2009 J W Mapping the ternary relationship PATIENT PHYSICIAN ysrcianJD PhysicianName PATIENT TREATMENT PalienLlD l PnysiciarLID I TreaTmenLCode E m Resuns Remember that the TR AT NT 4 E ME prlmaIy key MUST be unique 8084610 Spring 2009 J Anna Sidorova Lecture notes 20 University of North Texas Transforming ER Diagrams into Relations Mapping SupertypeSubtype Relationships 0 One relation for supertype and for each subtype 0 Supertype attributes including identi er and subtype discriminator go into supertype relation 0 Subtype attributes go into each subtype primary key of supertype relation also becomes primary key of subtype relation 0 l 1 relationship established between supertype and each subtype with supertype as primary ta e 8084610 Spring 2009 Figure 520 Supertypesubtype relationships 8084610 Spring 2009 Anna Sidorova Lecture notes 21 University of North Texas Figure 521 Mapping Supertypesubtype relationships to relations EMPLOVEE EmployeeiNumber Empioyea ame Address Empiayeegrype Dais ired HOURLYiEMPLOVEE i SALARIEDiEMPLOVEE v 3511 la eeiNumber AnnuaLSalary Stockiomions i i CONSULTANT gamgioiewmby B K EClSAEiD Spring ZEUS Normalization K ECl39SAEiEI Spring ZEUS Anna Sidorova Lecture notes University of North Texas Learning Objectives Define Normalization Define 15 an and 3rd Normal Forms Discuss normalization process BCIS4610 Spring 2009 Normalization Definitions 0 The process of converting complex data structures into simple stable data structures 0 WellStructured Relation 0 A relation that contains a miniInum amount of redundancy and allows users to insert modify and delete the rows without errors or inconsistencies 946 Bcis4e1o Spring 2009 Anna Sidorova Lecture notes 23 University of North Texas NorniaHzann Process 0 The goal is to bring each relation into the Third Normal Form 0 The process bringing a relation into the 3rd Normal Form Goes through stages 0 15t Normal Form 0 2nd Normal Form 0 3rd Normal Form k BCIS4610 Spring 2009 K FunononalDependendes 0 Functional Dependency 0 A 7 articular relationshii between two attributes For a given relation attribute B is functionally dependent on attribute A if for every valid value of A that value of A uniquely determines the value of B 0 Instances or sample data in a relation do not Prove the existence of a functional dependency 0 Knowledwe ofA roblem domain is most reliable method for identifying functional dependency BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas Func onalDependendesNota onsin Pmmems A 9B Attribute B is functionally dependent on attribute A A determines B A B 9 C AttributesAand B together determine attribute C A 9B C Both attributes B and C are determined by functionally dependent on attribute A BCIS4610 Spring 2009 Func onalDependendes 0 We can draw functional dependencies between attributes of a relation as follows STUDENT l l l Stud ID FName LName Email 111 Mary Jones mamhotmaicom 122 Sara Smith smithhotmailcom BCIS4610 Spring 2009 Anna Sidorova Lecture notes 25 University of North Texas f Important Defl n Itlons 39 Multivalued Attributes repeating groups 7 non key attributes or groups of non key attributes the values of Which are not uniquely identified by directly or indirectly not functionally dependent on the value of the Primary Key or its part STUDENT I l l l Stud ID Name CourseD Units 101 Lennon MSI 250 300 MSI 415 300 125 Jonson MSI 331 300 BCIS4610 Spring 2009 K r m porta nt Defl n Itlons 0 A relation is unnormalized not in the 1St Normal Form if it has multivalued attributes or repeating groups Repeating Group STUDENT I l l l Stud ID Name KCourse 101 Lennon 101 Lennon MSI 415 300 125 Jonson MSI 331 300 BCIS4610 Spring 2009 Anna Sidorova Lecture notes 26 University of North Texas Im porta nt Definitions 0 A relation is in the 1 t Normal Form if it has no multivalued attributes or repeating groups STUDENT l l l l Stud ID Name Course u Units 101 Lennon MSI 250 300 101 Lennon MSI 415 300 125 Jonson MSI 331 300 BCIS4610 Spring 2009 J W m porta nt Definitions 0 Partial Dependency when an non key attribute is determined by a part but not the whole of a COMPOSITE primary key Partial 39 CUSTOMER Dependency Cust Name Order 101 ATampT 1234 101 ATampT 156 125 CiSCO 1250 BCIS4610 Spring 2009 K Anna Sidorova Lecture notes 27 University of North Texas f m porta nt Definitions 0 A relation is NOT in the 2nd Normal Form if it has partial dependencies CUSTOM E I l Cust ID Name Order ID 101 ATampT 1234 101 ATampT 156 125 CiSCO 1250 BCIS4610 Spring 2009 K r m porta nt Definitions 0 A relation is in the 2nd Normal Form ifit is in the 1St Normal Form AND has no partial dependencies EMPLOYEE I l 1 l I l tmp IU IName LName ue pIIU ue pINa me 111 Mary Jones 1 Acct 122 Sara Smith 2 Mktg BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas f Im porta nt Defl n Itlons 0 Transitive Dependency when a non key attribute determines another non key attribute EM PLOYEE I I I I I tmp IU IName LName uept me 111 Mary Jones 1 Acct 122 Sara Smith 2 Mktg BCIS4610 Spring 2009 K r m porta nt Defl n Itlons 0 A relation is NOT in the 3rd Normal Form if it has transitive dependencies EM PLOYEE I I I I I I tmp IU IName LName uept me 111 Mary Jones 1 Acct 122 Sara Smith 2 Mktg BCIS4610 Spring 2009 Anna Sidorova Lecture notes 29 University of North Texas Important Definitions 0 A relation is in the 3rd Normal Form if it is in the an Normal Form and has no transitive dependencies EM PLOYEE l l l l m IL Na me IName ue ptu 1 1 1 Mary Jones 1 122 Sara Smith 2 BCIS4610 Spring 2009 K Normal Forms Review 0 Unnormalized There are multivalued attributes or repeating groups 0 1 NF No multivalued attributes or repeating groups 0 2 NF 1 NF plus no partial dependencies 0 3 NF 2 NF plus no transitive dependencies 950 BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas Example 1 Determine NF All attributes are directly or indirectly determined by the primary key therefore the relation is at least in 1 NF ISBN 9 Title ISBN 9 Publisher 0 Publisher 9 Address BOOK i i i ISBN Title Publisher Address k Bols4e10 Spring 2009 j K Example 1 Determine N F 0 ISBN 9Tide he relation is at least in 1N ISBN 9 Publisher There is noHCOMPOSITE Publisher 9 Address prim ary key theremre We can t be partial dependenCIes Therefore the relation is at least in 2NF BOOK l l l ISBN Title Publisher Address K Bols4e10 Spring 2009 Anna Sidorova Lecture notes University of North Texas Example 1 Determine NF Publisher isra nonkey attribute and it determines Address another nonkey attribute Therefore there is a transitive dependency which means39that the relation is NOT in 3 NF ISBN 9 Title ISBN 9 Publisher 39 Publisher 9 Address BOOK i i i ISBN Title Publisher Address k BCIS4610 Spring 2009 j K Example 1 Determine N F ISBN 91315 We knowthat the relation is at leastrin 2NF and it is not in 3 ISBN 9 Puthher NF Therefore we conclude 39 Pubhsher 9 Address that the relation isin 2NF BOOK i i i ISBN Title Publisher Address BCIS4610 Spring 2009 j Anna Sidorova Lecture notes University of North Texas ISBN 9 Title ISBN 9 Publisher Publisher 9 Ad ress BOOK i i i ISBN Title Publisher Address BCIS4610 Spring 2009 J Example 2 Determine NF 0 ProductilD 9 Description ORDER l l I Order Nol Product ID Description BCIS4610 Spring 2009 Anna Sidorova Lecture notes 33 University of North Texas Example 2 Determine NF 0 ProductilD 9 Description The relati on is at least in391 NF There39is a COMPOSITE Primary Key PK Order No Product ID therefore there can be partial dependencies ProductD which is a part of PK determines Description henceLther e isia partial dependency Therefore the relation is not 2NF No sense to check for transitive dependencies ORDER l l Order No Product ID Description BCIS4610 Spring 2009 j Example 2 Determine NF 0 ProductilD 9 Description We know thatthe relation isat least in 1rNF39and it is39not in 2NF Therefore we conclude that the relation isin 1 NF ORDER l l Order No Product ID Description BCIS4610 Spring 2009 j Anna Sidorova Lecture notes University of North Texas Example 3 Determine NF n yoursolution you will write the followinngstification 1 Thereare MN attributes therefore not 1NF Conclusion The relation is unnormalized 0 ProductilD 9 Descriptio 39 ProductilD 9 Price Fargo Comoij 9 PART H l i l l PartlD Descr Price ComplD No BCIS4610 Spring 2009 k j K Bringing a Relation to 1NF STUDENT l l l l Stud ID Name 101 Lennon 101 Lennon MSI 415 3 00 125 Jonson MSI 331 300 BCIS4610 Spring 2009 k Anna Sidorova Lecture notes University of North Texas Bringing a Relation to 1NF 0 Option 1 Make a determinant of the repeating group or a multivalued attribute a part of the primary key Composite Primary Key STUDENT l l Stud Name Cturse ID Units 101 Lennon 101 Lennon MSI 415 300 125 Jonson MSI 331 300 Beis4e1o Spring 2009 k Bringing a Relation to LNF I Option 2 Remove the entire repeatin roup from the relation Create another relation whic would contain lgthe attributes of the repeating group plus the primary key from the first relation In this new relation the primary key irom the original relation and the determinant oi the repeating group will comprise a primary ey STUDENT Stud ID Name CourselD Units 101 Lennon 300 101 Lennon 125 Jonson MSI 331 300 BCIS4610 Spring 2009 Anna Sidorova Lecture notes 37 University of North Texas Bringing a Relation to lNF STUDENT II Stud ID Name 101 Lennon 101 Lennon 125 Jonson STUDENTCOURSE Stud ID Course Units 101 MSI 250 3 101 MSI 415 3 125 MSI 331 3 k BCIS4610 Spring 2009 K Bringing a Relation to 2NF Composite Primary Key STUDENT Stud ID Name Course ID Units 101 Lennon MSI 250 300 101 Lennon MSI 415 300 125 Jonson MSI 331 300 BCIS4610 Spring 2009 K Anna Sidorova Lecture notes University of North Texas Bringing a Relation to 2NF 0 Goal Remove Partial Dependencies Partial Depend 39hcies Com posite Primary Key l l Name ourse ID Units 101 Lennon MSI 415 300 125 Jonson MSI 331 300 BCIS4610 Spring 2009 Bringing a Relation to 2NF n rorn original relation For each partial de corresponding part ofthe primary key from t r 39 wiioie ofthe prirna key pendency create a new relation with t e e original as the primary key l lIq Stud ID Name Course ID Units 1 l 250 300 101 Lennon MSI 415 300 125 Jonson MSI 331 300 BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas Bringing a Relation to 2NF M lame ourse ID Units 1 250 300 101 Lennon MS415 300 125 Jonson MS331 300 Stud ID Course ID 101 MSI 250 101 MSI 415 125 MSI 331 STUDENT COURSE l I 1 Stud ID Name w Units 101 Lennon MSI 250 300 101 Lennon MSI 415 300 125 Jonson MSI 331 300 DUIO IOIU Opllllg UU5 f Bringing a Relation to 3NF 0 Goal Get rid of transitive dependencies Transitive Dependency EM P LOYEE l l in Emp Depmme 111 Mary Jones 1 Acct 122 Sara Smith 2 Mktg BCIS4610 Spring 2009 Anna Sidorova Lecture notes 40 University of North Texas Bringing a Relation to 3NF LL r u 39 39 Forezch r 4 WWW J L 4 J L r EMPLOYEE m l l 1 l EmE ID FName LName Dept Name 111 Mary Jones 1 Acct 122 Sara Smith 2 Mktg BCIS4610 Spring 2009 K Bringing a Relation to 3NF EMPLOYEE mg In lame Llama he 7 7 me 111 Mary Juries 1 Am 122 Sam Smith 1 Mktg EMPLOYEE Erhp ID Fi39iame LN39ame DeptlD 111 Mary Jones 1 122 Sara Smith 2 DEPARTMENT De pt ID De ptNa me 1 Acct 2 M ktg BCIS4610 Spring 2009 Anna Sidorova Lecture notes University of North Texas Other Normal Forms from Appendix B 0 Boyce7Codd NF 0 All determinants are candidate keys there is no determinant that is not a unique identi er 0 Usually if a relation is in NF it is in the BCNF except when a part of the Primary key is determined by a nonrkey attribute 0 4 1 NF and 5 11 NF 7 used primarily for theoretical purposes k BCIS4610 Spring 2009 Merging Relations 0 View Integration 7 Combining entities from multiple ER models into common relations Issues to watch out for when merging entities from different ER models 0 Synonyms 7 two or more attributes with different names but same meaning 0 Homonyms 7 attributes with same name but different meanings 0 Transitive dependencies 7 even if relations are in SNF prior to merging they may not be after merging 0 Supertype subtype relationships 7 may be hidden prior to merging BCIS4610 Spring 2009 Anna Sidorova Lecture notes 42 University of North Texas Anna Sidorova Enterprise Keys advice from some experts 0 PriInaIy keys that are unique in the Whole database not just Within a single relation 0 Corresponds with the concept ofan object ID in objectoriented systems BCIS4610 Spring 2009 Figure 53111 Enlerprise My sample dam quoth lemma key Figure 5313 Enterprise key 7 Relations with enterprise key OBJECT Oi Objeclfype EMPLOYEE OBJECT m ObjecLType CUSTOMER EMPLOYEE m Ernijv EmpJ lame DeptName Satary cusromm CUSTOMER 112 CusllD CustNamo Address EMPLOYEE l Enlij EmprNama Dept ame Satary r Jannmgs Fred Mmkalmg some r Hook quot5 Dan Puvehzsmg Duo Aceounlmg 450m 9MB 7 4 Cust m Cuswame Address 00 Fled39s Walehause m1 Eargam Bonanza r02 Jasper s 03 Desks 39R Us KelleHng OH BCIS4610 Spring 2009 Lecture notes 43 University of North Texas K Review of SQL BCIS4610 Spring 2009 K K Definitions 0 SQL Structured Query Language A 4th generation language used by most relational databases for data manipulation BCIS4610 Spring 2009 Anna Sidorova Lecture notes 44
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'