Business Analysis ITEC-630 Fall 2010

1. Business AnalysisITEC-630 Fall 2010 Information & Data Analysis Professor J. Alberto Espinosa

2. Agenda • Introduction to database concepts • Data modeling & relational database design • Transitional artifacts: the CRUD matrix – linking requirements to data design • Normalization

3. The Big Picture BPM/UCMatrix CRUDMatrix SystemFunctional &Non-FunctionalRequirementsAnalysis Business Process Analysis InformationAnalysis Figure out the data requirements to support the application

4. Data Modeling Concepts

5. How Most Business Applicationsare Implemented BusinessApplication 1 BusinessApplication 2 BusinessApplication 3 Etc Database Management System (i.e., Database Platform)(e.g., Oracle, Access, SQL Server, etc.) Database 1 Database 2 Database 3 Database 4 Etc.

6. Stand-alone DBMS DBMS and database work in the same computer: the user’s computer  OK for personal productivity Stand-aloneDBMS(e.g., MS Access) Database

7. DBMS in a Client/Server Environment:Better for corporate use  the DBMS has two components DBMS Server:runs the “back-end” part of the DBMS and performs most of the data management functions – e.g., queries, updates, etc. DBMS Client:runs “front-end” part of the DBMS that provides the user interface (e.g., data entry, screen displays or presentation, report formatting, query building tools) DBMSClient DBMSServer Data Request (e.g., query) Database Response(e.g., query result) Retrieve, add, delete and/or update data

8. DBMS in a Web Server Environment:Very common when there are large numbers of users and would be impractical to deploy and install a DBSM client  access to the database is done through a browser (e.g., on-line purchases) Request (ex. get a price quote, place an order) Response (ex. query results with HTML-formatted product price or order confirmation notice)

9. Business to Business E-Commerce Example using XML DBMS(e.g., MS SQL Server) INSERT query e.g., supplier XML Document (e.g., Purchase Order) XML Processor Internet XML Processor XML Document (e.g., Purchase Order) DBMS(e.g., Oracle) e.g., buyer SELECT query

10. Most Common Database Models • Hierarchical (of historical interest only) • Network (of historical interest only) • Relational • Object Oriented (new)

11. Relational Database • For a database to be truly relational, it must comply with 12 rules defined by its inventor (Dr. E. F. Codd). • No commercially available database complies with the full set of rules, but the 12 rules are used as guidelines for sound database design. • Rule 1 states that data should be presented in tables • Rule 2 states that data must be accessible without ambiguity • We will talk more about other rules later (i.e., about entity integrity and referential integrity – stay tuned).

12. Implications about Rule 1 A relational database must have: • Tables: or “entities” Every table has a unique name Ex. Students, Courses • Fields: or “columns”, “attributes” Every field has a unique name within the table Ex. Students (StudentID, StudentName, Major, Address) Ex. Courses (CourseNo, CouseName, CreditPoints, Description) • Records: or “rows”, “tuples”, “instances” Every record is unique (has a unique field that identifies it) Ex. {“jdoe”, “John Doe”, “CS”, 5000 Forbes Ave.) Ex. {“MGMT-352-001”, “MIS”, Fall 2002, “A great course”}

13. Object Oriented (OO) Databases • OO languages + added database functionality, or • Database products + added OO programming facilities • Similar to relational databases • “Classes” (a grouping of similar objects -- like tables) • “Objects” (an instance of a class -- like records) • “Object properties” (object attributes -- like fields) • Plus: • Methods (i.e., procedures or programs)Programs embedded in classes and objects • Other OO Properties (inheritance, encapsulation, etc.)

14. Terminology Equivalence

15. Important Data Modeling Concepts

16. Data Modeling Goals • Data integrity Avoid anomalies in the data • No data redundancy Record the data in one place only • Efficient data entry Duplicate data means having to enter the same data more than once • Consistency Duplicate data can lead to inconsistencies when the data changes e.g., 2 different addresses for same client • Flexibility and easy evolution East to maintain, update and add new tables

17. Data Integrity Issue #1:Enforcing Entity Integrity  Inspect Each Table

18. Entity Integrity • Is ensuring that every record in each table in the database can be addressed (i.e., found) – this means that there each record has to have a unique identifier that is not duplicate or null (i.e., not blank) • Examples: every student has an AU ID; every purchase order has a unique number; every customer has an ID Primary key (PK) helps enforce Entity Integrity: • Field(s) that uniquely identifies a record in a table (e.g., AU user ID) • Entity integrity = PK is not duplicate & not blank • PK can be: • A single field (e.g., UserID), or • Or more than one field (e.g., OrderNo, LineItem)

19. Data Integrity Issue #2:Enforce Referential Integrity  Inspect each relationship between any two tables

20. Referential Integrity • Is ensuring that the data that is entered in one table is consistent with data in other tables • Examples: purchase orders can only be placed by valid customers; accounting transactions can only be posted to valid company accounts Foreign key (FK)  helps enforce referential Integrity: • A field in a table that is a PK in another table • That is, a field that “must” exist in another table • This is how referential integrity is maintained

21. Illustration: Primary and Foreign Keys PK FK PK

22. Entity, Referential Integrity PK Database Schema:The structure of the database, which contain tables, views, constraints, relations, etc. – just about everything, except the data itself PK FK PK, FK PK, FK PK

23. Other Important Keys • Candidate Keys: • Often there are more than one keys that could serve as a primary key • Example: Order, LineItem vs. Order, ProdID • Example: AU ID, SSN, AU Login ID • These are called candidate • Any candidate can be selected as the primary key • Alternative Keys: • Once a primary key has been selected from the choice of candidate keys, the other keys (not used as PKs) are referred to as “alternative keys”

24. Developing Data Modelsalso called Entity-Relationship Diagram (ERD)

25. Data Model ExampleCourse Registration System Courses Instructors CourseNo InstructorID Teach CourseDescription LastName Many 1 FirstName InstructorID Entities CreditPoints Telephone EMailAddr PreRequisites ClassroomNo 1 Relationships Students Includes StudentID Many Enrollments LastName FirstName Enrolls StudentID SSN CourseNo Department Many 1 College Comments Major EMailAddr

26. Data Model Example (MS Access equivalent)Course Registration System Cardinality 1 toMany Enrolls Includes Teaches Entities Relationships

27. The Textbook’s ERD Notation Entities InstructorID CourseNo LastName FirstName InstructorID(FK) CourseDescr Instructors Teach Courses Telephone EMail CreditPoints PreReqs Relationships

28. Peter Chen’s ERD Notation Instructors Course PK InstructorID PK CourseNo Teaches LastName CourseDescription FirstName FK1 InstructorID Telephone CreditPoints EMail PreRequisites

29. Conceptual Data Modeling • Data-oriented modeling method that describes the data and relationships among data entities • Goal: capture meaning of the data • 2 main ERD or data model constructs: •  Entities and its attributes •  Relationships between entities

30. Entity • “An object, person, place, event or thing or which we want to record data” • Equivalent to a table in a database • Examples: instructors, students, classrooms, invoices, registration, machines, countries, states, etc. • Entity instance: a single occurrence of an entity • Example: Espinosa, KSB T58, ITEC 455 • Entities can be identified in a requirements analysis description by following the use of NOUNS

31. Relationships • Relationships describe how two entities relate to each other • Relationships in a database application can be identified following the VERBS that describe how entities are associated with one another • Examples:studentsenroll in courses countrieshavecities, etc.

32. Cardinality • Cardinality is an important database concept to describe how two entities are related • The Cardinality of a relationship describes how many instances of one entity can be associated with another entity • The cardinality of a relationship between two entities has two components: • Maximum Cardinality: is the maximum number of instances that can be associated with the other entity – usually either 1 or many (the exact number is rarely used) • Minimum Cardinality: is the minimum number of instances that can be associated with the other entity – usually either 0 or 1 • Symbols: • 0 • 1 • Many

33. Cardinality (cont’d.) • A relationship is fully described by describing the cardinality in both directions of the relationship: e.g., a client places zero (i.e., optional) or many orders and each order must relate to only one (i.e., mandatory) client. • Examples:1 student can only park 1 (or 0) cars  1 to (0 or) 11 client can place (0 or ) many orders  1 to (0 or) many1 student can enroll in (at least 1 or) many courses anda course can have (0 or) many students  (0 or) many to (1 or) many

34. Example: 2 Entities, 1 Relationship Zero or many Instructors Course PK InstructorID PK CourseNo Teaches LastName CourseDescription FirstName One and only one FK1 InstructorID Telephone CreditPoints EMail PreRequisites Peter Chen’s notation& MS Visio software

35. ERD SYMBOLS (cont’d.)Note: high level conceptual models don’t show attributes, just entities Employee BioData Has 1 to 1 MaximumCardinality(outer symbol) Employee FamilyData Has Mandatory Optional Minimum Cardinality(inner symbol) Peter Chen’s notationusing Systems Architect software

36. ERD SYMBOLS (cont’d.) → Advises← Have Advisor Student 1 to Many MaximumCardinality 1 to Many (or None) Faculty Course Teaches Mandatory Optional Minimum Cardinality Peter Chen’s (“crow’s feet”) notationusing Systems Architect software

37. Many to Many Relationships? Many to Many Orders Products Convert a Many-to-Many into 2 One-to-Many’s Orders Products 1 to Many LineItems 1 to Many (or None) Intersection Table

38. Cardinality: 1 to 1 (MS Access notation)

39. Cardinality: 1 to many(MS Access notation)

40. Steps in data modeling Modeling • Identify and diagram all ENTITIES • Add PK attributes – i.e., implement entity integrityEnsure PK’s are non-null & non-duplicates • Identify and diagram all RELATIONSHIPSNote CARDINALITIES(1 to 1, 1 to n, n to n) • Add FK attributes – i.e., implement referential integrity (this is automatic in some tools—MS Access) • Add remaining attributes

41. ERD Example:Course Registration System Courses (CourseNo (PK), CourseDescripition, InstructorID, CreditPoints, ClassroomNo) PreRequisites (CourseNo (PK), PreRequisiteNo (PK), Comments) Students (StudentID (PK), LastName, FirstName, SSN, Department, College, Major, EMail) Enrollment (StudentID (PK), CourseNo (PK), Comments) Instructors (InstructorID (PK), LastName, FirstName, Telephone, EMail) Classrooms (ClassroomNo (PK), ClassroomName, Building, BuildingRoomNo, Equipment, Capacity) Note: PK denotes a primary key

42. Example: Course Registration SystemStep 1. Draw Entities

43. Example: Course Registration SystemStep 2. Add PK’s (undeline/separate with a line)

44. Example: Course Registration SystemStep 3. Add Relationships (w/Cardinalities) PreRequisites has Course Instructors Teaches PK,FK1 CourseNo PK CourseNo PK InstructorID PK PreRequisiteNo Includes Assigned Enrollment ClassRooms Students PK,FK1 StudentID Enrolls PK ClassroomNo PK,FK2 CourseNo PK StudentID

45. Example: Course Registration SystemStep 4. Add FK’s PreRequisites Course has Instructors Teaches PK,FK1 CourseNo PK CourseNo PK InstructorID PK PreRequisiteNo FK1 InstructorID FK2 ClassroomNo Assigned Includes Enrollment ClassRooms Students PK,FK1 StudentID Enrolls PK ClassroomNo PK,FK2 CourseNo PK StudentID

46. Example: Course Registration SystemStep 5. Add Remaining Attributes Course Instructors PreRequisites Has PK CourseNo PK InstructorID PK,FK1 CourseNo Teaches PK PreRequisiteNo CourseDescription LastName FirstName FK1 InstructorID Comments CreditPoints Telephone EMail FK2 ClassroomNo Assigned Students Includes ClassRooms PK StudentID PK ClassroomNo LastName FirstName Enrollment ClassroomName SSN Enrolls Building PK,FK1 StudentID Department BuildingRoomNo PK,FK2 CourseNo College Equipment Major Capacity Comments EMail

47. Example:Course Registration System(in MS Access)

48. EXAMPLE:Package Delivery Tracking System

49. Example:Package Delivery Tracking System

50. EXAMPLE:Airline Reservation System