Business Database Application
Business Database Application CIS 4600
Popular in Course
Popular in Business Info Systems CIS
Dr. Ima Lakin
verified elite notetaker
Dr. Ima Lakin
verified elite notetaker
Dr. Ima Lakin
verified elite notetaker
verified elite notetaker
verified elite notetaker
verified elite notetaker
This 277 page Class Notes was uploaded by Dr. Ima Lakin on Wednesday September 30, 2015. The Class Notes belongs to CIS 4600 at Western Michigan University taught by Bernard Han in Fall. Since its upload, it has received 65 views. For similar materials see /class/217003/cis-4600-western-michigan-university in Business Info Systems CIS at Western Michigan University.
Reviews for Business Database Application
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: 09/30/15
Advanced SOL Inquiry Chapter 9 CIS4600 Business DB Applications Lecture 8 Dr Bernard Han Outline 0 Outerjoin Why 0 Type nested queries 0 Type II nested queries and difference problems 0 Nested queries in the FROM clause 0 Division problems 0 Null value effects Slide 2 Outer Join Why 7 0 Equal Join excludes non matching FOWS 0 Outer Join preserves non matching rows is important in some business s ua ons 0 Types of Outer Join 0 Full outerjoin o Onesided outerjoin left and right Slide 3 Outer Join Operators Fuiicuter join A Left Outer Join Join Right Outer Join Unmatched rows Matched rows Unmatched rows of the left table using the join of the right table condition Slide 4 Full Outer Join Example Facu y FacNo FacName 111111111 Joe Outer Join of Offering and Faculty 222222222 sue 333315 3333 sara FacNo FacName OfferNo 111414111 be 11M 222222222 sue 2222 O e ng 0quot9quotquotgt FacNO 111411111 joe 3333 1H1 H14141H 2222 222222222 333333333 sara H14141H Slide 5 StdFirstName StdLa stNa me StdCit y StdState Sthajor StdClass SthF39A StdZip Sthc EndGrade r u 39 H H C 3 UfferNo Co urseN D Of erm Off r ear Off Lo cati 0 r1 Of 39ime FacNo OffDa39ys i39 Hquot 1 CourseNo CrsDesc CrsUnits m FacNo FacFirstName FacLastName FacCity FacState FacDEpt FacRank FacSaIary FacSupewisor FacHireDate FacZipCode Slide 6 LEFT JOIN and RIGHT JOIN Keywords Example 1 Access SELECT OfferNo CourseNo OfferingFacNo FacFirstName FacLastName FROM Offering LEFT JOIN Faculty ON OfferingFacNo FacultyFacNo WHERE CourseNo LIKE 39IS39 Example 2 Oracle SELECT OfferNo CourseNo OfferingFacNo FacFirstName FacLastName FROM Faculty RIGHT JOIN Offering ON OfferingFacNo FacultyFacNo WHERE CourseNo LIKE 39IS39 Slide 7 Twe I Nested SQL Query 0 Query inside a query 0 Use in WHERE and HAVING conditions 0 Similar to a nested procedure 0 No reference to outer query 0 Also known as noncorrelated or independent nested query Slide 8 Type I Nested Quer Example I y Example Access List nance faculty who teach IS courses SELECT FacNo FacLastName FacDept FROM Faculty WHERE FacDept 39FIN39 AND FacNo IN SELECT FacNo FROM Offering WHERE CourseNo LIKE CIS39 Slide 9 DELETE Exam le I Use Type I nested queries to test conditions on other tables Use for UPDATE delete statements also Example 8 Delete offerings taught by Leonard Vince DELETE FROM Offering WHERE OfferingFacNo IN SELECT FacNo FROM Faculty WHERE FacFirstName 39Leonard39 AND FacLastName 39Vince39 Slide 10 Type II Nested S L Query 0 Similar to nested loops 0 Executes one time for each row of outer query 0 Reference to outer query 0 Also known as correlated or variably nested query 0 Use for difference problems not joins Slide 11 Type II Nested Query i Exam le for a Djfference Problem Example 9 Retrieve MS faculty who are not teaching in Spring 2009 SELECT FacNo FacLastName FacDept FROM Faculty WHERE FaCDept 39M839 AND NOT EXISTS SELECT FROM Offering WHERE OffTerm Spring39 AND Oferar 2009 AND FacultyFacNo OfferingFacNo Slide 12 Nested Queries in the FROM Clause l o More recent introduction than nested queries in the WHERE and HAVING clauses 0 Consistency in language design 0 Wherever table appears table expression can appear 0 Specialized uses 9 Nested aggregates 9 Multiple independent aggregate calculations Slide 13 Nested FROM Query Example Example 13 Retrieve the course number course description the number of offerings and the average enrollment across offerings SELECT TCourseNo TCrsDesc COUNT AS NumOfferings AvgTEnrollCount AS Angnroll FROM SELECT CourseCourseNo CrsDesc OfferingOfferNo COUNT AS EnrollCount FROM Offering Enrollment Course WHERE OfferingOfferNo EnrollmentOfferNo AND CourseCourseNo OfferingCourseNo GROUP BY CourseCourseNo CrsDesc OfferingOfferNo T GROUP BY TCourseNo TCrsDesc uuuu 1 r Divide Operator 0 Match on a subset of values 0 Suppliers who supply all parts 0 Faculty who teach w IS course 0 Specialized operator 0 Typically applied to associative tables representing MN relationships Slide 15 Div1ample Part SuppPart DIVIDEBY Part uppNo S3 S3 S3 3 0 53 p1 I02 I03 S1 contains p1 p2 p3 Slide 16 COUNT Method for Division Problems I Compare the number of rows associated with a group to the total number in the subset of interest Type I nested query in the HAVING clause Example 14 List the students who belong to all clubs SELECT SthO FROM StdClub GROUP BY SthO HAVING COUNTU SELECT COUNTU FROM Club Slide 17 Typical Division Problems Compare to an interesting subset rather than entire table Use similar conditions in outer and nested query Example 15 List the students Who belong to all social clubs SELECT StudentlStho SName FROM StdClub Club Studentl WHERE StdClubClubNo ClubClubNo AND StudentlSthO StdClubSthO AND CPurpose 39SOCTAL39 GROUP BY StudentlStho SName HAVING COUNT SELECT COUNT FROM Club WHERE CPurpose 39SOCTAL39 Slide 18 Advanced Division Problems 0 Count distinct values rather than rows 0 Faculty who teach at least one section of selected course offerings o Offering table has duplicate CourseNo values 0 Use COUNTDISTNCT column 0 Use stored query or nested FROM query in Access Slide 19 Advanced Division Problem Example Example 16 List the number and the name of faculty Who teach at least one section of all of the fall 2007 IS courses SELECT EacultyEacNo EacEirstName EacLastName FROM Faculty Offering WHERE EacultyEacNo OfferingEacNo AND OffTerm 39EALL39 AND CourseNo LIKE 39IS39 AND Oferar 2007 GROUP BY FacultyEacNo EacEirstName EacLastName HAVING COUNTDISTINCT CourseNo SELECT COUNTDISTINCT CourseNo FROM Offering WHERE OffTerm 39FALL39 AND Oferar 2007 AND CourseNo LIKE 39IS39 Compound Conditions Reference Tables False False Null False False False Null m True False True True False Null Null Null True False Null False True Null Slide 21 Aggregate Functions 0 Null values ignored 0 Effects can be subtle o COUNT may differ from CountColumn Slide 22 Gr0uping Effects 0 Rows with null values are grouped together 0 Grouping column contains null values 0 Null group can be placed at beginning or end of the nonnull groups Slide 23 Summary 0 Advanced matching problems not common but important when necessary 0 Understand outerjoin difference and division operators 0 Nested queries important for advanced matching problems 0 Lots of practice to master query formulation and SQL Slide 24 Chapter 2 Introduction to Database Development CS4600 Business DB Applications Lecture 2 Dr Bernard Han Your Interests in Studing 334600 Slide 2 Outline 0 Context for database development 0 Goals of database development 0 Phases of database development 0 CASE tools Slide 3 INPUTS I OUTPUTS Loan Applications 39 PROCESSES Delinquency Notices ENVIRONMER Student Loan ENVIRONMENT Payments gt Processing gt Statements System Status A Cash Changes Disbursements DATABAS E Problem Statement Preliminary Feasibility Study Investigation System Requirements Systems Systems DeSIQH SpeCIfications Feedback Design 1 Operational Systems System Feedback Implementation Maintenance Feedback Slide 5 De velopment Alternatives 0 Common Difficulties 9 Operational system is produced late 9 Rush to begin implementation 0 Requirements are difficult to capture 0 Alternative methodologies 9 Spiral approaches Rapid application development RAD o Prototyping may reduce risk Slide 6 Graphical Data Models 0 Explicit or implicit Data Requirements 0 Data model ER Diagram 0 Process model DFD or Process Mapping 0 Environment interaction model 0 Emphasize data model macro vs micro Slide 7 Broad Goals of Database Development 0 Develop a common vocabulary 0 Define data meaning no confusion 0 Ensure data quality sustainability 0 Provide efficient implementation Slide 8 Develop a Common Vocabulary o Diverse groups of users level of interests 0 Difficult to obtain acceptance of a common vocabulary synonyms vs acronyms o Compromise to find least objectionable solution Politics gtgt functionalities o Unify organization by establishing a common vocabulary Data Repository Slide 9 Define Meaning of Data 0 Business rules support organizational policies restrictiveness of business rules 0 Too restrictive reject valid business interactions 9 Too loose allow erroneous business interactions o Stored Procedures SP 0 Exceptions allow flexibility triggers amp SP Slide 10 Data Quality 0 Poor data quality leads to poor decision making wrong info is worse than no info 0 Costbenefit tradeoff to achieve desired level of data quality level of precision 0 Longterm effects of poor data quality useless database Slide 11 Data Quality Common Measures 0 Completeness 0 Lack of ambiguity o Timeliness o Correctness 0 Consistency 0 Reliability Slide 12 Database Development Phases I Data Z 1 Slide 13 Conceptual Data Modeling 0 Focus on the Information content of a DB 0 Final Deliverable 9 entity relationship diagram ERD a comprehensive blue print 0 Capture diverse contents for database requirements attributes objects relationships Slide 14 Logical Database Design 0 Refine the conceptual design 0 Convert ERD into table design Relations 0 Analyze if there exists redundancies o Normalize table design with data integrity 0 Add constraints to enforce business rules Slide 15 Distributed Database Design 0 Determine the location of dataprocessing 9 Improve system performance with no alternation in information content 0 Segment a database into subsets for different sites 0 Replicate subsets of database to improve data accessibilityavailability Slide 16 Physical Databa e Design 0 Conducted at each independent database site 0 Minimize response time without consuming excessive resources 9 Tradeoffs retrieval convenience vs update workload 0 Further Decisions indexes and location of data placement Slide 17 Conceptual Design View Integration Conceptual Data Modeling Data Requirements View ERDs View Integration Entity Relationship Diagrams Slide 18 Design Skills need both soft and hard 0 Soft Skills 9 EQ o Degree of subjectivity o Peopleoriented 0 Hard 9 IQ o ObjectiveGoal 0 Intensive data analysis Slide 19 Skills Required in DB Development Phage Data Requirements Conceptual Data 30ft Modeling Entity Relationship Diagrams Logical Database Design Relational Database Tables Distributed Database Design Distribution Schema Physical Database Design Internal Schema Populated Database Slide 20 Commercial CASE Tools 0 PowerDesigner 12 0 Oracle Designer 10g 0 Visual Studio Team System 2008 Database Edition 0 CA ERWin Data Modeler o ERStudio Schema Examiner DB Artisan o Visible Analyst Slide 21 Common Features of CASE Tools rgt 0 Diagramming 0 Documentation 0 Logical Analysis 0 Prototyping Q amp D version Slide 22 Tools for our class Visio and E RD Assistant I 0 Drawing tools oStenciIs for database diagrams oGIue feature to retain connections 0 Data dictionary support oAnaIysis tools oDiagram layout oReverse engineering Slide 23 Summary 0 Background for Chapters 5 to 8 0 Relationship to information systems development 0 Broad database development goals 0 Development phases Slide 24 Chapter 5 ER Diagramming CIS4600 Business DB Applications Lecture 5 Dr Bernard Han Outline 0 Basic Notations for ER diagramming 0 Understanding relationships 0 Generalization hierarchies 0 Business rule representation 0 Diagram rules Slide 2 Ba sig Symbols 39 Mquot H 1 117 quot 7 m H HUM r L y M i OffLocation O Time U 3133W1L x i nth Slide 3 Car dinalities number of instances associated with two ends of a elationship 39lt Course Offering Course1 Offering1 CourseZ 39OfferingZ COUFSG3 Offering3 Offering4 Slide 4 Classi cation of Cardinalities 0 Minimum cardinality inside symbol 9 Mandatory 9 existence dependent on its parent 0 Optional may or may not exist 0 Maximum cardinality outside symbol 9 the total possible number of instances that may associate with an entity Slide 5 Camili1 XIV9 3 0 CrsDesc CrsUnits CourseNo g L xH H I 1 by V 1 H HH Hg H 2 is VF OfferNo OffLocation OffTime Slide 6 Patterns Types of Relationships Three major relationship patterns types o1M One to Many oMN Many to Many 011 Oneto One Slide 7 71M Relationship Identi cation Dependency E r 1 HR I L gWWHMI iHlW quot flew r v 7quot 21 am 15M M in zii if39i aj M13 Lyi fixiiWM M M We xii slewvim 7 v7 A 11 7 M x H 153 BIdID BIdgName BIdgLocation Slide 8 M39N Relationship g 7 V m memo Stho m OffLocation Sthame O Time 3 H M i39w m 1 1 V A Slide 9 More MN Relationships Part SUNO PartNo SuppName L PartName t n x 39 quot r H HH1 IJLL WM AuthNo AuthName 1 Title Slide 10 71 Relations hip Weak entity OfficeNO Assi nedTo Phone 9 OfficeType L Employee RelatedTo Slide 11 Comparison of Notations ourse CourseNo CrsDesc OffLocation OffTime CrsUnits 1 1 Has OfferNo Offering Course OfferNo Courseno H T CourseNo V OftLocation OffTime CrsDesc CrsUnits Slide 12 Some Complex Relationships l 0 Self identifying relationships recursive o Mway relationships 0 Equivalence between MN and 1M relationships one MN is equivalent to two 1M relationships Slide 13 mgiamg Diagram for SelfIndentif ng Recu iy elationships 7 CIS3600 Faculty2 Faculty3 CIS4600 CIS464O H uiwvf wuizuri 1 My 77quot 39 if uj Faculty1 CIS2600 7 w 7 Faculty4 Faculty5 39 39 CIS4990 Slide 14 ER Diagram of SelfIdenti ing Recursive Relemnghlfi 7 Facuny FacNo CourseNo FacName CFSDeSC Slide 15 Associative Relationship Mway Relationships J I i a y I i i V 17 PartNo SuNo PartName Pro39No SUPPName ProjName i ii i H 1 5iiiii quotwig Slide 16 Relationship Equivalence I 0 Replace MN relationships in ER Diagram 9 First create an associative entity type 9 Second create two 1M relationships 0 Why using associative entity type 9 Easily handled by RDBMS 9 Reduce complexity Slide 17 Create an Aeeeer etttre Enttty Type t0 covert M39N into two 1M relationships m MN SthO gtlt OffLocation Sthame OffTime Eanrade AttDate 1 RecordedFo 5 Slide 18 SuperSub Relationships m m EmpName EmpHireDate l yEmp Salar lf r HourlyEmp EmpSalary w 3 EmpRate Slide 19 Inheritance I o Subtypes inherit attributes of supertypes direct and indirect 0 Reduction of attribute list 0 Applies to code methods as well as attributes data Slide 20 Gezv era zgtion Constraints Smbol SecName LastClose 1 OutShares IssuedShares FaceValue Slide 21 Wti39gle L eyels 0f Generalization 7 ymbol SecName LastClose if i Stck 7 OutShares IssuedShares my Common I PERatio Dividend Rate FaceValue C allPric e Arrears Slide 22 A Comprehensive Example with SuperSub relationships m Faculty OfferNo Teaches i FacSalary OffLocation FacRank FacHireHate Std Major SthPA OffTime StdCIass Supervises W 5 i CourseNo CrsDesc CrsUnits Slide 23 Business Rules I o Enforce organizational policies 0 Promote efficient communication 0 Formal representation in ERD o Informal representation in documentation associated with an ERD 0 Use rules language to formally represent in relational database after conversion Slide 24 ER Representation A Summary I 0 Primary key constraints entity identification 0 Named relationships direct connections among business entities 0 Identification dependency knowledge of other entities for identification 0 Cardinalities restrict number of related entities in a business situation 0 Generalization hierarchies classification of business entities and organizational policies Slide 25 E R Representation additional specs o Candidate key constraints alternate ways to identify business entities o Reasonable values fixed collection of values or consistent with another attribute o Null value constraints data collection completeness 0 Default values simplify data entry and provide value when unknown Slide 26 Diagramming Rules l 0 Ensure that ERD notation is correctly used 0 Similar to syntax rules for a computer language 0 Completeness rules no missing specifications 0 Consistency rules no conflicts among specifications 0 Supported by the ER Assistant Slide 27 Completeness Rules 0 Prima Ke Rule Each entity type has a PK direct indirect or inheriteng o Naming Rule all entity types relationships and attributes have a name 0 Cardinaliy Rule cardinality is specified in both directions for eac re a Ions ip o Entity Participation Rule a entitytypes participate in an at eas one re a ions ip except for entity types In a generalization hierarchy ie no isolated entity in the iagramll O Generalization Hierarch Partici ation Rule at least one nlity type in a generaliza ion hierarchy paFEICIpates in a relationship Slide 28 Primary Key Rule Issue 0 Primary key rule is simple in most cases 0 For some weak entities the PK rule is subtle 0 Weak entity with only one 1M identifying relationship 0 Weak entity must have a local key to augment the borrowed PK from the parent entity type 0 Violation of PK rule if local key is missing Slide 29 PK Rule Violation Example l 7quot M NH l illi ll l 39 ll MW A J AM Jl l l ll ll H BIdID BIdgName 39 m Bldg Location Roomcapac39ty L A Slide 30 Naming Consistency Rules 0 Entitv Name Rule entity type names must be unique 0 Attribute Name Rule attribute names must be unique within each entity type and relationship o Inherited Attribute Rule attribute names in a subtype shall not match any inherited direct or indirect attribute names Slide 31 Relationship Names 0 No uniqueness requirement 0 Participating entities provide a context for relationship names 0 Use unique names as much as possible to distinguish relationships 0 Must provide unique names for multiple relationships between the same entity types Slide 32 Connection Consistency Rules l o RelationshipEntity Connection Rule relationships connect two entity types not necessarily distinct o RelationshipRelationship Connection Rule relationships are not connected to other relationships 0 Redundant Foreiqn Kev Rule foreign keys are not used Slide 33 Identi cation Dependency Rules 0 Weak entity rule weak entities have at least one identifying relationship 0 Identifying relationship rule at least one participating entity type must be weak for each identifying relationship 0 Identification dependency cardinality rule the minimum and maximum cardinality must equal 1 for a weak entity in all identifying relationships Slide 34 Example of Diagram Errors Faculty l FacSalary r FacRank quot FacHireHate Has Supervises H M1 LI A 1 I l rim quot CourseNo Eanrade CrsDesc CrsUnits Slide 35 Corrected E RD FacSalary FacRank FacHireHate mm Eanrade CrsUnits Slide 36 Summary 0 Data modeling is an important skill o Crow s Foot ERD notation is widely used 0 Use notation precisely 0 Use the diagram rules to ensure structural consistency and completeness 0 Understanding the ERD notation is a prerequisite to applying the notation on business problems Slide 37 Database Design and Normalization Theory Chapter 7 Dr Bernard Han CIS4600 Lecture 7 Why 00 fa NormalIa an f 0 We need a methodologytheory to verify the nal relations are properly transformed from our ER model Data will be accurate all the time Data can sustain all kinds of processing 0 We a robust database design What 12539 Da fa NormaI39m A process to validate improve the logial design of a relation so that it satis es certain constraints and avoid unnecessary processing errors Addition anomaly insertion anomaly Update anomaly Deletion anomaly Da fa Anomalies Addition anomaly New facts data can not be added to the DB due to current relation design Update anomaly Data become inaccurate when some record in a relation is updated Deletion anomaly Unintentional data loss occurs due to the deletion of some record in a relation Bene ts of Da fabas e Normaz ion 0 Data Redundancy is minimized Data Integrity is enhanced Data Anomalies are avoided All of the above three are causal effects of one on another Normaiza an Theory Basic Concept Functional Dependency Functional Dependency A dependence relationship between attIu w Notation a 9 b the value of a uniquely determines the value of b I a is a determinant I b is functionally dependent on a I a functionally determines b DFD is the foundation of Normalization Q What is the difference between a key and a determinant Normal3920 an Theory Functional Dependency Student Data em Interpretation SID or SSN can uniquely determine hisher Name Phone and DOB but not the reverse Final Relation SI D SSN Name Phone DOB Note You may choose either SID or SSN as the PK Normal3920 an Theory Functional Dependency Automobile Data i i Interpretation VIN can uniquely determine a vehicle s of doors Color and Type but not the reverse Final Relation VIN of doors Color Type Note You may choose either SID or SSN as the PK Da fabas39e Norma13920 an Where is The beef Reality is not that simple Determinant may not be the Primary Key Example t w ail calm Both EID and Phone are determinants Q Can we put all these four items in one relation NO Da fabas39e Norma13920 an Where is The beef One more example mww Both CID and Section are determinants Q Can we put all these four items in one relation NO Da fabase Norma13920 an A Bird39seyeview A relation is in its rst normal form INF if it does not contain repeating groups A relation is in its second normal form 2NF if every nonkey attribute is fully dependent on the primary key A relation is in its third normal form 3NF if it has no transitive dependency between nonkey attributes Da fabase Norma13920 an A Bird39seyeview con r39d A relation is in its BoyceCOW BCNF if every determinant may serve as the PK A relation is in its fourth normal form 4NF if it does not contain any multivalued functional dependency A relation is in its fth normal form SNF if it does not involve any join dependency Are you lost rst Normal Form INF A relation is in its rst normal form INF if it does not contain repeating groups Example 1 Normalization A xedlength rst Normal Form Whaf s Wrong What is wrong With INF Ex STUDENT SID Name Sex DOB Major 1123 John M 11181 CIS 1123 John M 11181 FIN 1125 Lisa F 103081CIS 1129 Mark M 11581 FIN 1129 Mark M 11581 MKT I Can you give some examples for the following I Deletion Anomaly I Update anomaly 39 Addition Anomaly How to Improve 1 NF I A relation is in its second normal form 2NF if every nonkey attribute is fully dependent on the primary key What is the PK for STUDENT mule Sex DOB 1 Primary key is SID Major Improve it to 2NF Impro veo SfUdenf Rem STUDENT STUDENT SI D Name Sex DOB STUMAJORSID Maior Sgcond Agrma Fagm I NF ne more xamp e i 2N F A relation is in its second normal form 2NF if every nonkey attribute is fully dependent on the primary key Ex JOBHISTEID Name Position SDate Dept 123 B Han Instructor 010190 BIS 123 B Han Asst Professor 010194 BIS 211 H Lee Asso Professor 090194 MKT 211 H Lee Full Professor 010198 MKT 235 K Foley Asst Professor 070196 Acct What is the primary key for JOBHIST EID SDate 9 Position Name Dept EID 9 Name EID 9 Dept I Q Do we see any partial FD First Normal Form Wha f s Wrong What s wrong With the 2N39F S Ex JOBHIST EID Name Position SDate Dept 123 B Han Instructor 010190 BIS 123 B Han Asst Professor 010194 BIS 211 H Lee Asso Professor 090194 MKT 211 H Lee Full Professor 010198 MKT 235 K Foley Asst Professor 070196 Acct l Anomalies Can you give some examples I Deletion Anomaly I Update anomaly I Addition Anomaly I mpro veo J 03 HI 5 T Rea E JOBHIST EMPLOYEE EID Name Dept JOBPOSTION EID S Date Position Comma1 7 5 an ZNF VerifC 39 0 You need not worry about ifa re a ion is in its ZNF if it has a simple primary key Why Because I Partial FD only occurs When the PK is a composite compound key Third Normal Form 3NF A relation is in its third normal form 3NF if it has no transitive dependency between non key attributes Def If a 9 b and b 9 c then there is a TFD between a and c EX STAFFEID Name Phone Of ce Street City State ZIP Q What is the primary key EID 9 Name EID 9 Phone EID 9 Of ce EID 9 Street EID 9 State EID 9 ZIP so STAFF is in ZNFXX But we also found ZIP 9 City ZIP 9 State and Phone 9 Of ce So STAFF is NOT in 3NF Whaf s Wrong wifh ZNF I STAFFEID Name Phone Of ce Street City State ZIP 3937 1 K It A MIR l Anomalies Can you give some examples I Deletion Anomaly I Update anomaly I AdditionAnomaly How f0 Improve fhe Design Can verf if m to 3NF 1 Resolution for STAFF Divide and conquer EX STAFFEID Name Phone Of ce Street City State ZIP CIFinal Design I STAFF m Name Phone Street ZIP PK EID CK EID FK ZIP Phone I OFFICE Phone Of ce PK Phone CK Phone Of ce FK none ZIPTABLE City State PK ZIP CK ZIP FK none CINow is the 3NF good enough depends Boyce odd Normal Form BCNF BCNF A relation is in its Z form BCNF if every determinant as the PK for the relation Ex ADVISORY FID SID MajID A faculty can advise gt 1 student A student may have gt 1 major A faculty can only serve as advisor for one major Q Which NF is ADVISORY Q What is the PK FID SID MajID If so then ADVISORY is lNF SID MajID FID If so then ADVISORY is 3NF Q ADVISORY can be quali ed as 3NF What s wrong with it What s Wrong With the Non BC39NF A ADVISORY FID SID MajID 7A 39 r 2 5 quotla PU 7 J 97 J l Anomalies Can you give some examples I Deletion Anomaly I Update anomaly I Addition Anomaly Resoufion for I mpro Ving 3rd Normal Form 3NF CIResolution for ADVISORY FID SID MajorID CIDivide and Conquer How Alternative 1 ADVISOR Q Male STUMAJ SID2 MajID allkey relation Alternative 2 ADVISOR Q Male STUFAC SID2 FID 9 allkey relation Q Which design is the valid one Boyce Codd Normal Form BCNF A Graphrd Illustration Original Tableii Alternative 1 Alternative 2 FID Male AAA CIS BBB FIN CCC CIS Boyce add Narmai Farm BCNE A Graphrd Illustration Or39 inal Table Not Reversible I Reversible Z 5 gm 1111 1WD Majm 31 101le 1 A 111 AAA 7 39AAA C15 111 CIS 111 BBB BBB FIN 111 FIN AAA CCC CIS CIS mmwy A Journey of Normal3920 an First Normal Form Remwce M l R mnm Second Normal Form I r 1 ANT i I quot V 1113quot Thlrd Normal Form 93331er FlZornszl39tw iwl i itep ly Remove BoyceCodd Normal Remaining Functional Form Dependency Chapter 1 Introduction to Database Management CIS4600 Business DB Applications Lecture 1 Dr Bernard Han Welcome to CIS4600 0 Learn modern database technology 0 Crucial to the operationmanagement of modern organizations 0 Focus on business transformations 0 From business needs to database applications 0 Need significant time commitment 0 Learn by making mistakes 0 How to succeed in this class 0 Attend the class absences S 3 othenNise E Textb00k for this class 0 4th Edition 0 Database Design Application Development and Administration oAuthor Michael Mannino 0 ISBN 9780615231044 0 Publisher Ingram 0 Website httpmmanninocom What do you take away from this class Sufficient working knowledge in o Threetier DB architecture using clientserver approach 0 Data modeling using graphic diagramming ERD 0 DB inquiry using SQL Access SQL 2005 and Oracle 0 DB design normalization and transformation skills 0 DB administration 0 Advanced DB programming using views triggers and stored procedures Lecture 1 Outline 0 Database characteristics 0 Threelayered Database Architecture 0 Organization administrative roles Basic Vocabulary 0 Data raw facts about things and events B 30 4500 0 Information valueadded transformation on data for decision making 0 GPA Average transaction amount 0 Data field atomic item 9 Attribute 9 Column in a record 0 Object a tangible or intangible thing of a designer s interest Entity 9Table 9 Relation in a database 1 r onverteinto Primitive Data class Buster Course MET 5cm Semester Spring Business Pulic39y Information Section 2 LT 13 Name II Baker Hemneth DI 3241311528 Dayle Juan E 6193248 Fijnirle Clive FL 548429344 Lewis J hii l G 551T42186 McFerran Debra Fin 409 3145 Sisnems Michaei 392415582 Www ww mewwkm 044 Example 2 Data Processing Graphic Information actual estimated l I Iquot 639 m a E OJ t 3 D h o L up 4 E a Z 1996 199 1998 1999 2000 2001 Year Percent Enrollment by Major 1 99X Enrollment Projections Database Characteristics 0 Persistent to be used for a long time without processing errors 0 Interrelated all data about entities are logically integrated connected from one access point to reach all data stored in the database 0 Shared to be used accessed addeddeleted by multiple users An Example gUniversity Database l lgzllv lvl il students faculty courses offerings enrollments Relationships faculty teach offerings students enroll in r i lijii offerings offerings made is iza iiiu ling 39 of courses An Example Water Utility Database Entities customers meters bills payments meter readings Relationships bills sent to customers customers make payments customers use meters L L I H Database Architecture T hreelayered View Application 1 Application 2 Application 3 Iii It I It I DBMS a comprehensive software to manages data operations to an existing database Database centralized shared data Database Architecture An Example Frontend Front39end Frontend Application Prog A n n PI Apphcatlon Prog License Renewal State Revenue Auto Registration Dept Dept Dept DBMS Drivers file Automobile file Traffic violation file Fee payment file Auto accident file Threetiered Database Architecture 0 ANSISparc 1975 Model 0 Adopted by almost all existing DBMSs External Level Enduser View Conceptual Level DBA s View Physical Level System Designer s View Some synonyms User View ltgt DB Schema ltgt physical Data Structure Advantages of Database Approach 0 Minimal Data Redundancy 0 Increased Data Independence 0 Enhanced Security and Performance 0 High Resource Sharing 0 Efficient Application Development 9 Many CASEGraphic tools 0 Simple programming 9 Reduced maintenance Organizational Roles Users vs Builders Functional User a 39 Information Systems m ms Database Specialists DBA vs DA 0 Database administrator DBA o More technical o DBMS specific skills 0 Data administrator 0 Less technical 0 Planning role Database Management System DBMS Jhat is it o A comprehensive data management software not database that supports data acquisition storage dissemination maintenance and report generation 0 Enterprise DBMS OracleSQL2005DB2 0 Desktop DBMS MS AccessParadox 0 Embedded DBMS resides within PDA O DBMS 35 DB DBMS is to createmanage DB Summary o Databases and database technology vital to modern organizations 0 Threetiered DB architecture supports daily operations and decision making 0 Numerous opportunities to work with databases DBA DA DB Designer SA Your Assignment 0 Read Chapter 1 and 2 0 See you Sept 10 Developing Data Models Chapter 6 CIS4600 Business DB Applications Lecture 6 Dr Bernard Han Outline 0 Guidelines for analyzing business information needs 0 Transformations for generating alternative designs 0 Finalizing an ERD 0 Schema Conversion Slide 2 o Poorly defined o Conflicting statements o Wide scope o Missing details 0 Many stakeholders o Requirements in many formats 0 Add needed structure 0 Eliminate irrelevant details 0 Add missing details 0 Narrow scope Slide 3 Goals of Narrative Problem Analysis 0 Consistency with your project narrative o No contradictions of explicit narrative statements 0 Identify possible shortcomings o Ambiguous statements 9 Missing details 0 Simplify preferences 0 Choose simpler designs especially in initial design oAdd refinements and additional details later Slide 4 Steps of Narrative Probem Analysis 0 Identify entity types and attributes 0 Determine primary keys 0 Add relationships 9 Determine connections 9 Determine relationship cardinalities o Simplify relationships Slide 5 Determine Entity Types and Attributes o For entity types find nouns that represent groups of people places things and events 0 For attributes look for properties that provide details about the entity types 0 Simplicity principal consider as an attribute unless other details Slide 6 HOW to determine a quotPrimary Key 0 Stable never change after assigned 0 Single purpose no other purpose 0 Good choice automatically generated values 0 Compromise choice for industry practices 0 Or Identify other unique attributes Slide 7 EntMeatL EaI ion Example rquot CUStNO MeterNo ReadNo CUStNal e MtrAddr ReadTime ReadLevel CustType MtrModel EmpNo CustAddr MtrSize BillNo RateNo BillDate RateDesc BillStartDate RateFixedAmt BillEndDate RateThresh BillDueDate RateVarAmt Slide 8 jentify Relationships 0 Identify relationships connecting previously identified entity types 0 Relationship references involve associations among nouns representing entity types 0 Sentences that involve an entity type having another entity type as a property 0 Sentences that involve an entity type having a collection of another entity type Slide 9 Rele 9 hg31denti cation Example 7 lt7 m m ReadBy Fr w a Slide 10 Relationship Simpli cation 0 Problem statement requires direct or indirect connections 0 Hub entity types to simplify 9 Connect other entity types 9 Sometimes associated with important documents 0 Reduce number of direct connections Slide 11 Diagram Re nements 0 Construct initial ERD o Revise many times 0 Generate feasible alternatives and evaluate according to requirements 0 Gather additional requirements if needed 0 Use transformations to suggest feasible alternatives Slide 12 Attribute t0 Entity Type a sfe atien ReadNo ReadTHne ReadLevd EmpNo gt Reading ReadNo ReadTHne ReadLevd Pe onns Employee No EmpName EmpTMe Slide 13 Colmqgng ttribute Transformation Customer CustNo CustName CustAddr CustType Customer CustNo CustName CustStreet CustCity CustState CustPostal CustType Slide 14 Entiti zoo Expansion Transformation RateSetNo RSApprDate RateNO RSDesc RateDesc 3 RateFixedAmt 39 RateVarAmt RateThresh 1 7 i MinUsae MaxUsage FixedAmt VarAmt 5 Slide 15 RateSetNo RSApprDate RSEffDate RSDesc i it w MinUsae MaxUsage FixedAmt VarAmt L Weak t0 Strong Entity Transformation RateSetNo RSApprDate RSEffDate RSDesc RateNo MinUsage MaxUsage FixedAmt VarAmt Slide 16 Jttribuig isjggyTransformation 7 w EmNo EmpName Employee EmNo a I itleHistory Vi VersionNo Beg EffDate End EffDate EmpTitIe L Slide 17 17MR lo 1 ionship History Transformation 7 Customer Customer CustNo r H V V V MeterUsage Version No uses 7 7 Beg EffDate End EffDate L4 M V MeterNo UsedB Slide 18 gr MN Relationship History Transformation I H mm 7 VersionNo 7 Beg EffDate End EffDate F k V V Residence Residence ReSNO Slide 19 M N Relationship History Transformation II 7 i i i Resides Period V I VersionNo Res esAt i r BegE Dam EndEffDate 11 Residence Residence ResNo if L Slide 20 Limited History Transformation Employee EmNo EmpName EmpCurrTitle EmpCurrTitleBegEffDate EmpCurrTitleEndEffDate EmpPrevTitle EmpPrevTitleBegEffDate EmpPrevTitleEndEffDate Slide 21 Generalization Hierarchy CustNo CustNo 39 CustName CustName CustAddr CustType CustAddr TaXP ayerID EntelpriseZone Commercial Subsidized T axp 3336111 SL Sidized DwellingType EntelpriseZmle Slide 22 Summary of Transformations o Attribute to entity type 0 Compound attribute split 0 Entity type expansion 0 Weak entity to strong entity oAdd history attributes 1M relationships and MN relationships 0 Generalization hierarchy addition Slide 23 Documenting an E RD 0 Important for resolving questions and communicating a design 0 Identify inconsistency and incompleteness in a specification 0 Identify situations when more than one feasible alternative exists 0 Do not repeat the details of the ERD o Incorporate documentation into the ERD Slide 24 Documentati0n With the ER Assistant 0 Attribute comments 0 Entity type comments 0 Relationship comments 0 Design justifications 0 Diagram notes Slide 25 Common Design Errors 0 Misplaced relationships wrong entity types connected 0 lncorrect cardinalities tyRilcally using a 1M relationship instead ofa N relationship 0 Missing relationships entity types should be connected directly 0 Overuse ofspecializedmodelin tools eneralization hierarchies iden ification ependency selfreferencmg relationships Mway assomative entity types 0 Redundant relationships derived from other relationships Slide 26 Resolving Design Errors 0 Misplaced relationships use entity type clusters to reason about connections 0 Incorrect cardinalities incomplete requirements inferences beyond the requirements 0 Missing relationships examine implications of requirements 0 Overuse of specialized modeling tools only use when usage criteria are met 0 Redundant relationships examine relationship cycles for derived relationships Slide 27 Exampl eggtig Type Cluster RateSetNo 1 RSEffDate RSAapprDate r 7 a 39x CustNo BiIINo BillDate 32 r BillStartDate Slide 28 Summary of Data Modeling Guidelines 0 Use notation precisely o Strive for simplicity o ERD connections 0 Avoid over connecting the ERD 0 Identify hubs of the ERD 0 Use specialized patterns carefully o Justify important design decisions Slide 29 Summary of Basic Conversion Rules 0 Each entity type becomes a table 0 Each 1M relationship becomes a foreign key in the table corresponding to the child entity type the entity type near the crow s foot symbol 0 Each MN relationship becomes an associative table with a combined primary key 0 Each identifying relationship adds a column to a primary key Slide 30 AoMoyszasic Rules 0 Efr CourseNo CrsDesc CrsUnits OfferNo Course Has 7 OffLocation OffTime CREATE TABLE Course PRIMARY KEY CourseNo CREATE TABLE Offering PRIMARY KEY OfferNo FOREIGN KEY CourseNo REFERENCES Course Slide 31 Application of Basic Rules ll OfferNo 777777777777777 w E n rol sl n 77777777777777 OffLocation OffTime Eanrade CREATE TABLE Enrollment PRIMARY KEY Stho OfferNO FOREIGN KEY Stho REFERENCES Student FOREIGN KEY OfferNo REFERENCES Offering Slide 32 jpl aiioppf asic Rules Ill g Stho OfferNo Std Name OffLocation if 7 V T l L A Sarne conversion result as the previous slide Different application of rules Slide 3 3 Generalization Hierarchy Rule 0 Mimic generalization hierarchy as much as possible 9 Each subtype table contains specific columns plus the primary key of its parent table 9 Foreign key constraints for subtype tables 0 CASCADE DELETE option for referenced rows 0 Reduce need for null values 0 Need joins and outerjoins to combine tables Slide 34 Ger ra zgchq Hierarchy Example 7 EmNo EmpName EmpHireDate quot SalaryEmp HourlyEm p Employee table EmpNo PK SalaryEmp table EmpNo PK EmpNo FK HourlyEmp table EmpNo PK EmpNo FK Slide 3 5 Optional iM Rule 0 Separate table for each optional 1M relationship 0 Avoids null values 0 Requires an extra table and join operation 0 Controversial in most cases 1M rule is preferred Slide 36 Opti vgl Zfoample gr Facuuy OfferNo FaCNO OffLocation FacName OffTime I CREATE TABLE Teaches PRIMARY KEY OfferNo FOREIGN KEYOIferNo REFERENCES Offering FOREIGN KEYFacN0 REFERENCES Faculty Slide 37 7 I elatierzsfzips gr OfficeNo r OffAddress OffPhone CREATE TABLE Of ce PRIMARY KEY Of ceNo FOREIGN KEYEmpN0 REFERENCES Employee UNIQUE EmpNO Slide 3 8 Summary 0 Data modeling is an important skill 0 Use notation precisely 0 Preference for simpler designs 0 Consider alternative designs 0 Review design for common errors 0 Work many problems Slide 3 9 Chapter 3 Introduction to Relational Data Model CS4600 Business DB Applications Lecture 3 Dr Bernard Han Lecture Outin 0 An Overview of Data Models 0 Relational model basics o Integrity rules 0 Rules about referenced rows 0 Relational algebra Chapter 3 Slide 2 An Overview Data Models l graphic representation of realworld data structures 0 Four Data Models 0 Hierarchical Data Model a tree model 1960s 19703 0 Network Data Model a complex model mid 70s early 80s 0 Relational Data Model a flat file table model late mid 80s to mid 90s most popularll o Objectoriented Data Model an event model still evolvingll Chapter 3 Slide 3 Comment on Data Model Data Model is 0 An abstraction of the reality a conceptual understanding of the needed facts to be collected and organized 0 An organized structure about facts but there is no data substance definition vs data Chapter 3 Slide 4 Student 1 Sthu StdFirstName StdLastName StdCity StdState Ethajor Stdtlass SthPA StdZip Chapter 3 Relationships Ezr u39ollment OfferNo Stho EndGrade I GRAND CHILD Cixfferil39lg OfferNo CourseNc O 39l39erm Off r ear Off Locati 0 r1 OffTime FachI OffDays I PARENT COLII SE 13 CourseNo CrsDesc CrsUnits Facult FacNo FacFi rstNa me FacLastName FacCity39 FacState FacDept FacRa nk FacSalary FacSupewism FacHireDate FacZi p Code Slide 5 Tables Relational DB is a collection of tablesfiles and each table 0 Headings table name and column names schema 0 Body a number of rowsrecurrences of data Student HimWWW 123456789 HOMER WELLS SEATTLE WA 981211111 IS FR 300 124567890 BOB NORBERT BOTHELL WA 234567890 CANDY KENDALL TACOMA WA 980112121 FIN JR 990423321 ACCT JR 270 350 Chapter 3 Slide 6 A schema for STUDENT table CREATE TABLE Student Chapter 3 Stho CHAR11 StdFirstName VARCHAR50 StdLastName VARCHAR50 StdCity VARCHAR50 StdState CHAR2 StdZip CHARlO Sthajor CHAR6 StdClass CHAR6 SthPA DECIMAL32 CONSTRAINT PKStudent PRIMARY KEYSthO Slide 7 A Schema for the Course Table CREATE TABLE Course CourseNo CHAR6 CrsDesc VARCHAR250 CrsUnits SMALLINT CONSTRAINT PKCourse PRIMARY KEYCourseNo Chapter 3 Slide 8 Common Data Ty es Used in RDB l o CHARL o VARCHARL o INTEGER o FLOATP o DateTime DATE TIME TIMESTAMP o DECIMALW R o BOOLEAN Y or N Chapter 3 Slide 9 Relationships in Relational Data Model Offering Stho StdLastName OfferNo CourseNo 123456789 WELLS 1234 IS320 124567890 KENDALL 4321 IS320 234567890 123456789 234567890 123456789 124567890 Chapter 3 Slide 10 A Table B Set C Record oriented oriented oriented Reeo dbm ypeg al l We Row Reeom Column 39 e coil Chapter 3 Slide 11 DB Integrity Rules 0 Domain integrity only a set of values shall be assumed by the specified columnfield O Entity integrity the primary key 0 Each table has columns with a unique value 0 Ensures entities are traceableretrievable o Referential integrity the foreign keys 9 Values of a column in one table match values in another sourceparent table o Ensures valid references among tables Chapter 3 Slide 12 Formal De nitions for Keys 0 Compound key More than one column are used to form a unique value 0 Candidate key a potential identifier for a table eg WIN SSN 0 Primary key a designated candidate key cannot contain null values 0 Foreign key a column that is used as the primary key of another table Chapter 3 Slide 13 r l ygt g39m PARENT StdFirstName StdLastName Std City StdState Std M ajcn r StdCI a s s SthF A StdZip Relationships Stho EndGrade t ACHmD Chapter 3 PARENT w EH m OfferNo CourseNu lefTerm Of ear foLc cation foTime Fa chI Off Days I ACHmD PARENT 39i 39 CU FEE CourseNo CrsDesc CrsUnits 3H FacNo FacFirstName Fa La 5tNan Ie FacCity FacState Fa cDept FacRank FacSalary FacSupenrisor FacHireDate Fa cZipCode Slide 14 A Schema for Offering Table CREATE TABLE Offering OfferNo INTEGER CourseNo CHAR6 OffLocation VARCHAR50 OffDays CHAR6 OffTerm CHAR6 Oferar INTEGER FacNo CHAR11 OffTime DATE CONSTRAINT PKOffering PRIMARY KEY OfferNO CONSTRAINT FKCOUIS NO FOREIGN KEY CourseNo REFERENCES Course CONSTRAINT FKFaCNO FOREIGN KEY FaCNO REFERENCES Faculty Chapter 3 Slide 15 A Schema for Enrollment Tabe CREATE TABLE Enrol lment Chapter 3 OfferNo INTEGER Stho CHAR11 EanradeDECIMAL32 CONSTRAINT PKEnrOllment PRIMARY KEY OfferNo Stho CONSTRAINT EROfferNO FOREIGN KEY OfferNo REFERENCES Offering CONSTRAINT ERSthO FOREIGN KEY SthO REFERENCES Student Slide 16 SelfReferencing Relationships 0 Foreign key that references the same table 0 Represents relationships among members of the same set 0 Not common but important in specialized situations Chapter 3 Slide 17 Relationship Window With 44w Relatiansbips 7quot Relationships Connections are frtjm tha primary key bold font to the foreign key Chapter 3 Slide 18 A Schema for Facult Table I y W131 ty FacNo CHARll FacFirstName VARCHAR50 NOT NULL FacLastName VARCHAR50 NOT NULL FaCCity VARCHAR50 NOT NULL FacState CHAR2 NOT NULL FacZipCode CHARIONOT NULL FacHireDate DATE FacDept CHAR6 FacSupervisor CHARll CONSTRAINT PKFaculty PRIMARY KEY FaCNO CONSTRAINT FRLFacsupervisor FOREIGN KEY FacSupervisor REFERENCES Faculty Chapter 3 Slide 19 Relational Algebra Overview l 0 eclon of table operators 0 Transform one or two tables into a new table 0 Understand operators in isolation 0 Classification 9 Table specific operators 9 Traditional set operators 9 Advanced operators Chapter 3 Slide 20 Subset Operators Restrict Chapter 3 Slide 21 Subset Operator Notes 0 Restrict 0 Logical expression as input 9 Example OffDays 39MW39 AND OftTerm 39SPRING39 AND Oferar 2008 0 Project 9 List of columns is input 9 Duplicate rows eliminated if present 0 Often used together Chapter 3 Slide 22 Extended Cross Product 0 Building block forjoin operator 0 Builds a table consisting of all combinations of rows from each of the two input tables 0 Produces excessive data 0 Subset of cross product is useful join Chapter 3 Slide 23 EdeEggquot Cross Product Example Chapter 3 Faculty FacNo 1 1 11 111 1 1 222222222 333333333 Student Stho 1 1 11 11 1 1 1 444444444 555555555 Faculty PRODUCT Student FacNo 1 1 11 11 1 1 1 1 1 11 11 1 1 1 1 1 1111 1 1 1 222222222 222222222 222222222 333333333 333333333 333333333 Stho 11 11 11 1 1 1 444444444 555555555 1 1 1111 1 1 1 444444444 555555555 1 1 11 11 1 11 444444444 555555555 Slide 24 Join Operator 0 Most databases have many tables 0 Combine tables using the join operator 0 Specify matching condition 0 Can be any comparison but usually 9 PK FK most common join condition 0 Relationship diagram useful when combining tables Chapter 3 Slide 25 Natural Join Operator I 0 Most common join operator 0 Requirements 0 Equality matching condition 0 Matching columns with the same unqualified names 9 Remove one join column in the result 0 Usually performed on PKFKjoin columns Chapter 3 Slide 26 NatqraiJoin Example 1 1 11 11 1 1 1 222222222 333333333 sara FacName OfferNo joe 1111 sue 2222 OfferNo FacNo 111111111 joe 3333 1111 111111111 2222 222222222 3333 111111111 Chapter 3 Slide 27 Wua formulation 0f Join En rG ra le Field La Tabla student Sort Criteria or Chapter 3 Slide 28 Outer Join Overview 0 Join excludes non matching rows 0 Preserving non matching rows is important in some business situations 0 Outerjoin variations o Full outerjoin o Onesided outerjoin Chapter 3 Slide 29 Outer Join Operators uiicuter join A Left Outer Join Right Outer Join Unmatched rows Matched rows Unmatched rows of the left table using the join of the right table condition Chapter 3 Slide 30 Full O uter Join Example Facu y FacNo FacName 111111111 Joe Outer Join of Offering and Faculty 222222222 sue 333333333 sara FacNo FacName OfferNo 111111111 joe 1111 222222222 2222 Offering OffemO Fa N 111 11 1111 39 3333 1111 111414111 2222 222222222 33333333 111414111 Chapter 3 Slide 31 Visual Formulation 01 Outer Join FacSupewisor acHIreDate nn Criteria 0139 Chapter 3 Slide 32 Traditional Set Operators l A UNION B A INTERSECT B 1 A MINUS B Chapter 3 Slide 33 Union Compatibility 0 Requirement for the traditional set operators 0 Strong requirement 0 Same number of columns 0 Each corresponding column is compatible 0 Positional correspondence 0 Apply to similar tables by removing columns first Chapter 3 Slide 34 Summarize Operator I o Decisionmaking operator 0 Compresses groups of rows into calculated values 0 Simple statistical aggregate functions 0 Not part of original relational algebra Chapter 3 Slide 35 Summarize Example SUMMARIZE Enrollment Enrollment ADD AVGEanrade Stho OfferNo Eanrade GROUP BY Stho 111111111 1111 38 111111111 2222 30 Sth AVGEanrade 111111111 3333 34 1 111411111 34 22222222 1111 35 222422222 33 22222222 3333 31 333333333 30 33333333 1111 30 Chapter 3 Slide 36 Divide Operator 0 Match on a subset of values 0 Suppliers who supply aH parts 0 Faculty who teach w IS course 0 Specialized operator 0 Typically applied to associative tables representing MN relationships Chapter 3 Slide 37 DAism n Example Part Su Part DIVIDEBY Part SuppNo PanNo PanNo pp uppNo 53 p1 s3 53 p2 3 0 83 p1 I02 I03 S1 contains p1 p2 Chapter 3 Slide 38 Summary 0 Relational model is commercially dominant 0 Learn primary keys data types and foreign keys 0 Visualize relationships 0 Understanding existing databases is crucial to query formulation Chapter 3 Slide 39 View Definition and Integration Chap 12 CIS4600 Business DB Applications Lecture 10 Dr Bernard Han Outline 0 View definition a base for App development an Example 0 View Integration VI Why 0 VI a reverse process to support Database Design 0 A Summary Slide 2 Creating a View for Application Inquiry o Visualize your data entry form 0 Identify necessary tables that are involved in your enduser form 0 Identify attributes that are needed to be retrieved identified tables 0 Develop proper SQL queries to serve as the base for Form Development Slide 3 FacultyNo 09855432 First Name LEONARD Last Name plN CE Department MS Assignments foer No I Course N0 Units Term Year Location I Stait Time 1234 ISSQU 4 FALL 2007 BLM302 1030 AM 3333 IS320 4 SPRING 2008 BLM214 8230 AM 4321 S320 4 FALL 2007 BLM214 3230 PM Ecnrd 39 l of 3 Record 1 of 6 Slide 4 F acuity Assignment Requirements 0 Step 1 Faculty parent table Offering child table 0 Step 2 Faculty FaCNo Offering FaCNo 0 Step 3 Course table in the subform 0 Step 4 update Offering FaCNo Slide 5 Faculty Assignment Queries 7 0 Main form A query SELECT FacNo FacFirstName FacLastName FacDept FROM Faculty 0 Subform A query SELECT OfferNo OfferingCourseNo FacNo OffTime OffDays OffLocation CrsUnits FROM Offering INNER JOIN COURSE ON OfferingCourseNo CourseCourseNo Slide 6 Registration Main Form Query Another Example SELECT RegNo RegTerm Rngear RegDate RegistrationStho RegStatus StdFirstName StdLastName StdClass StdCity StdState FROM Registration INNER JOIN Student ON RegistrationStho StudentStho Slide 7 Registration Subform Query SELECT FROM RegNo OfferingCourseNo OffTerm Oferar FacFirstName FacLastName CrsDesc CrsUnits Enrollment INNER JOIN Offering ON EnrollmentOfferNo OfferingOfferNo INNER JOIN Course ON OfferingCourseNo CourseCourseNo LEFT JOIN Faculty ON FacultyFacNo OfferingFacNo EnrollmentOfferNo OffTime OffLocation OfferingFacNo Slide 8 Why View lnte ration 0 Database Design is a very complex process It reflects organizational compleXIty otimeconsuming and laborintensive 0 Collect requirements from different user groups 0 Combine results from members of the design team o It is to manage complexity of large designs Slide 9 Overview of View Design and Integration Proposed Interviews formsreports Documentation L View design ConfllCt Conflict resolution 39 39 39 Identification View integ ratio n Conceptual schema Slide 10 View Design Start With existing Forms I 0 Important source of database requirements 0 Reverse the process described in Chapter 10 o Derive an ERD that is consistent with the form 0 Five step procedure Slide 11 Sample Customer Order Form 7 fem r H1 1quot m j Mr 391 m M128 Bookcase S gt 7 WW ii r quot L was m iiHH M H Slide 12 Form Analysis 0 Create an ERD to represent a form 0 ERD supports form and other anticipated processing 0 ERD should be consistent with the form 0 ERD is a view of the database Slide 13 Form Analysis Steps Step 1 Define form structure g Step 2dentify entity types Step 3 Attach attributes i 2 Step 7 Add relationships Step 5 Check completeness and consistency Slide 14 Step 1 De ne Form Structure 0 Construct a hierarchy that depicts the form structure 0 Most forms consist of a simple hierarchy where the main form is the parent and the subform is the child 0 Complex forms can have parallel sub forms and more levels in the hierarchy Slide 15 Hierarchical Form Structure Parent Node Order No Order Date Customer No Customer Name Address City State Zip Salesperson No Salesperson Name Child Node Product No Description Quantity Unit Price Slide 16 Step 2 Identify Entity Types 0 the hierarchical structure into one or more entity types 0 Make an entity type if a form field is a potential primary key and there are other associated fields in the form Slide 17 Entity Types for the Customer Order Forwr ex xtt 7 Customer Product Customer No Product No Order SalesPerson Order No SalesPerson No Slide 18 Step 3 Attach Attributes to Entities 0 Attach attributes to the entity types identified in the previous step 0 Group together fields that are associated with the primary keys found in Step 2 0 Form fields close together may belong in the same entity type Slide 19 A gributes Customer Customer No PrOduct Customer Name Product No Address Description City Unit Price State Zip SalesPerson SalesPerson No SalesPerson Name Order OrderLine Order No Quantity Order Date i Slide 20 Step 4 Add Relationships 0 Relationships involving the form entity type o Form entity type contains the form39s primary key 9 Relationships between the form entity type and other entity types derived from the parent node usually 1M o Add a relationship to connect the form entity type to an entity type in the child node 0 Add relationships to connect entity types derived from the child node if not already connected Slide 21 E n tity Relationship Diagram Salesperson SalesPerson No SalesPerson Name Order No Customer Customer No Customer Name Product No Description Unit Price Order Date Slide 22 Step 5 Check Completeness and Consistency o The ERD should adhere to the diagram rules specified in Chapter 5 o In addition the ERD should be consistent and complete with respect to the form structure 0 Explore diagram transformations as suggested in Chapter 6 Slide 23 Summary 0 View design and integration is an important skill for designing large databases 0 Manage complexity of large development e ons o The result of form analysis is an ERD that is a view of the database Slide 24 Chapter 4 Database Inquiry Using SQL CIS4600 Business DB Applications Lecture 4 Dr Bernard Han Structured Query Language SQ L l SQL a 4th generation language to createmodifyretrievecontrol data from databases managed by any RDBMS ie relational DBMS such as Oracle Sybase Microsoft SOLServer Access etc I SQL is a DDL DML DCL o Data Definition Create Alter Drop 9 Data Manipulation Select Insert Update 0 Data Control Grant Revoke Commit Rollback SQL vs RDBMS vs Other Programming Apriica tieni K Jaxw quot SQLServer Oracle DB2 Sybase 115mg 19 1 Application Prog COBOL PL1 l gt Databases Embedded SQL SOL Statements Found in Textbook I L Statement Chapter CREATE TABLE 3 14 18 SELECT 4 9 10 16 18 INSERT UPDATE 4 10 18 DELETE 4 9 10 CREATE VIEW 10 16 CREATE TRIGGER 11 GRANT REVOKE 14 COMMIT ROLLBACK SET TRANSACTION 15 CREATE TYPE 18 SQL A General Syntax SELECT field1 fieldm Aggr Func FROM table1 tableZ tablen WHERE condition1 Condition2 GROUP BY eld1 eldm HAVING Aggn Func ORDER BY field1 eldm Std FirstName Std La stNam e Std City StdState Sthajor Std Class SthPA Stdzip E1113 Hl39r39wiz39ri UfferNu Stha EndGrade H Off erNo Cd urseNd OffTerm Df 39ear Ofdecati d n Df ime FacNo OffDays 22 m e I COUFSEND CrsDesc CrsUnits uh 391 Fa cNo FacFirstName Fa cLa stNam e FacCity39 FacState FatDept FacRank Fa Salary FacSupewisor FacHireDate Fa cZip Simple SOL Examples quotSELECT FROM Example 1 SELECT FROM Faculty Example 2 SELECT FROM Faculty WHERE FacSSN 3954321098739 Example 3 SELECT FacFirstName FacLastName FacSalary FROM Faculty Example 4 SELECT FacFirstName FacLastName FacSalary FROM Faculty WHERE FacSalary gt 65000 AND FacRank 39PROF39 SQL Examples Complex Expressions Example 5 Access SELECT FacFirstName FacLastName FacCity FacSalaryll AS IncreasedSalary FacHireDate FROM Faculty WHERE YearFacHireDate gt 1998 Example 5 Oracle SELECT FacFirstName FacLastName FacCity FacSalaryll AS IncreasedSalary FacHireDate FROM Faculty WHERE tqnumbertocharFacHireDate 39YYYY39 gt 1998 Fuzzy Matching Match against a pattern LIKE operator Use meta characters to specify patterns Wildcard 0r Any single character 0r Example 6 Access SELECT FROM Offering WHERE CourseNo LIKE 39IS39 Example 6 Oracle SELECT FROM Offering WHERE CourseNo LIKE 39IS39 Using Dates Dates are numbers Date constants and functions are not standard Example 7 Access SELECT FacFirstName FacLastName FacHireDate FROM Faculty WHERE FacHireDate BETWEEN 112001 AND 12312002 Example 7 Oracle SELECT FacFirstName FacLastName FacHireDate FROM Faculty WHERE FacHireDate BETWEEN 391 Jan 200139 AND 3931 Dec 200239 More SQL Examples Single Table Example 8 Testing for null values SELECT OfferNo CourseNo FROM Offering WHERE FacSSN IS NULL AND OffTerm 39SUMMER39 AND Oferar 2008 Example 9 Mixing AND and OR SELECT OfferNo CourseNo FacSSN FROM Offering WHERE OffTerm 39FALL39 AND Oferar 2007 OR OffTerm 39WINTER39 AND Oferar 2008 Join Operator 0 Most databases have many tables 0 Combine tables using the join operator 0 Specify matching condition 9 Can be any comparison but usually 9 PK FK most common join condition 9 Relationship diagram useful when combining tables 1017159981 Natural Join of Offering and Faculty 333333333 sara FacName OfferNo joe 1111 222222222 sue 2222 OfferNO FaCNO 111 1 1 1 111 joe 3333 1111 111 11 1111 2222 222222222 3333 111 11 1111 Cross Product Style List tables in the FROM clause List join conditions in the WHERE clause Example 10 Access SELECT OfferNo CourseNo FacLastName FROM Offering Faculty WHERE OffTerm 39FALL39 AND Oferar 2007 AND FacRank 39ASST39 AND CourseNo LIKE AND FacultyFacNo OfferingFacNo FacFirstName CIS39 GROUP BY Exa pies Example 12 Grouping on a single column SELECT FacRank AVGFacSalary AS Angalary FROM Faculty GROUP BY FacRank Example 13 Row and group conditions SELECT Sthajor AVGSthPA AS Angpa FROM Student WHERE StdClass IN 39JR39 39SR39 GROUP BY Sthajor HAVING AVGSthPA gt 31 Rules on using Aggregate Functions 0 Columns in SELECT and GROUP BY 0 SELECT non aggregate and aggregate columns o GROUP BY list aH non aggregate columns 0 WHERE versus HAVING 0 Row conditions in WHERE 0 Group conditions in HAVING Aggregation and Joins l Powerful combination List join conditions in the WHERE clause Example 14 List the number of students enrolled in each 2008 offering SELECT Offering0fferNo COUNT AS NumStudents FROM Enrollment Offering WHERE Offering0fferNo EnrollmentOfferNo AND Oferar 2008 GROUP BY Offering0fferNo Conceptual Evaluation Problem I Example 15 List the number of offerings taught in 2008 by faculty rank and department Exclude combinations of faculty rank and department with less than two offerings taught SELECT FacRank FacDept COUNT AS NumOfferings FROM Faculty Offering WHERE OfferingFacNo FacultyFacNo AND Oferar 2008 GROUP BY FacRank FacDept HAVING COUNT gt 1 Query Formulation Process Problem D t b Statement a a ase 39 Representation Critical Questions 0 What tables are needed 9 Columns in output 9 Need Conditions to test 9 Need further processing 0 How to combine the tables 9 Usuallyjoin PK to FK where are they 9 More complex ways to combine 0 Individual rows or groups of rows 9 Need Aggregate functions in Select Statement 9 Conditions with aggregate functions Efficiency Considerations I 0 Little concern for efficiency 0 Intelligent SQL compilers 0 Correct and non redundant solution o No extra tables o No unnecessary grouping 0 Use HAVING for group conditions only 0 Chapter 8 provides additional tips for avoiding inefficient SELECT statements Joining Three Tables Example 16 List Leonard Vince s teaching schedule in fall 2007 For each course list the offering number course number number of units days location and time SELECT OfferNo OfferingCourseNo OffDays CrsUnits OffLocation OffTime FROM Faculty Course Offering WHERE FacultyFacNo OfferingFacNo AND OfferingCourseNo CourseCourseNo AND Oferar 2007 AND OffTerm 39FALL39 AND FacFirstName 39Leonard39 AND FacLastName 39Vince39 Joining Four Tables I Example 17 List Bob Norbert s course schedule in spring 2008 For each course list the offering number course number days location time and faculty name SELECT OfferingOfferNo OfferingCourseNo OffDays OffLocation OffTime FacFirstName FacLastName FROM Faculty Offering Enrollment Student WHERE OfferingOfferNo EnrollmentOfferNo AND StudentStho EnrollmentStho AND FacultyFacNo OfferingFacNo AND Oferar 2008 AND OffTerm 39SPRING39 AND StdFirstName 39BOB39 AND StdLastName 39NORBERT39 SelfJoin A Special Case Join a table with itself 0 Usually involve a selfreferencing relationship 0 Useful to find relationships among rows of the same table o Find subordinates within a preset number of levels 9 Find subordinates within any number of levels requires embedded SQL SelfJoin Example Example 18 List faculty members who have a higher salary than their supervisor List the social security number name and salary of the faculty and supervisor SELECT SubrFacNo SubrFacLastName SubrFacSalary SuprFacSSN SuprFacLastName SuprFacSalary FROM Faculty SUB Faculty SUP WHERE SUBFacSupervisor SUPFacNo AND SUBFacSalary gt SUPFacSalary Multiple Joins Between Tables Using Alias I Example 19 List the names of faculty members and the course number for which the faculty member teaches the same course number as his or her supervisor in 2008 SELECT FacFirstName FacLastName OlCourseNo FROM Faculty Offering 01 Offering 02 WHERE FacultyFacNo OlFacSSN AND FacultyFacSupervisor 02FacNo AND OlOferar 2008 AND 020ferar 2008 AND OlCourseNo 02CourseNo Multiple Column Grouping Example 20 List the course number the offering number and the number of students enrolled Only include courses offered in spring 2008 SELECT CourseNo Enrollment0fferNo Count AS NumStudents FROM Offering Enrollment WHERE Offering0fferNo Enrollment0fferNo AND Oferar 2008 AND OffTerm 39SPRING39 GROUP BY CourseNo Enrollment0fferNo How does SQL work Based on a Set theory relational algebra 0 Each table is a set of records rows and each row is a set of fields columns 0 Relevant tables share some common columns 0 Primary key used to identify a particular row 0 Foreign key used to refer to a related parent row in the database Traditional Set Operators A UNION B A INTERSECT B A MINUS B Union Compatibility 0 Requirement for the traditional set operators 0 Strong requirement 9 Same number of columns 9 Each corresponding column is compatible 9 Positional correspondence 0 Apply to similar tables by removing columns first SOL UNION Example Example 21 Retrieve basic data about all university people SELECT FacNo AS PerNo FacFirstName AS FirstName FacLastName AS LastName FacCity AS City FacState AS State FROM Faculty UNION SELECT Stho AS PerNo StdFirstName AS FirstName StdLastName AS LastName StdCity AS City StdState AS State FROM Student Relational Operators for Condition Statements Jk V J There are 6 relational operators Equal Not Equal Less Than Greater Than Less Than or Equal To Greater Than or Equal To Outer Join LeftRight Outer Join I T3 Parent Child T1 Parent Objective Sometimes we want to list all parent rows regardless if they have corresponding child rows or not 0 Left Join list all left parents with if any their child rows 0 Right Join list all right parents with if any their child rows Syntax for Left Join and Right Join I OSELECT T1field2 T2field3 FROM T1 left join T2 on T1redfield T2redfield SELECT T2field1 T3field2 FROM T2 right join T3 on T2b1uefield T3bluefield An Example Left Join T1 7 39 3 A T2 a1 a2 a3 SELECT T1A T2C FROM T1 LEFT JOIN T2 ON T1A T2A Rmu tg A C all 11 a2 a3 35 An Example Right Join I SELECT T2A T3c FROM T2 RIGHT JOIN T3 ON T2c T3c Rcegu ftg C A all all 82 a3 83 Data Manipulation Statements l o INSERT adds one or more rows 0 UPDATE modifies one or more rows 0 DELETE removes one or more rows 0 Use SELECT statement to INSERT multiple rows 0 UPDATE and DELETE can use a WHERE clause 0 Not as widely used as SELECT statement INSERT Example I Example Insert a row into the Studenttable supplying values for all columns INSERT INTO S tudent Stho StdFirstName StdLastName StdCity StdState StdZip StdClass Sthajor SthPA VALUES 399999999993939JOE3939STUDENT39 Benton Harbor39 MI39 49024 1121 FR39 CIS39 30 UPDA TE Example Example Change the major and class of Homer Wells UPDATE S tudent SET Sthajor 39ACC39 StdClass 39SO39 WHERE StdFirstName 39HOMER39 AND StdLastName 39WELLS39 DELETE Example Example Delete all CIS majors who are seniors DELETE FROM Student WHERE Sthajor CIS AND StdClass 39SR Summary 0 Apply problem solving guidelines 0 Traditional set operators are not common in queries 0 Manipulation statements are simpler than SELECT 0 Lots of practice to master query formulation and SQL
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'