Business Database Management Systems

1. Business Database Management Systems Class 3 DSC 544/444 Fall 2007

2. Agenda Wrap-up second design exercise from last time Database design overview Conceptual design for databases Entities, attributes, and relationships

3. Database design overview The design challenge is all about defining the information architecture What information needs to be stored? What database table structures can hold and properly relate the data so that we can pull the desired information back out of the database? Fortunately, methodologies exist to help us fashion good information architecture!

4. Database design overview Remember from our prior discussion that when designing our information architecture, we don’t need to be concerned about: Who will enter the data, and when? What will the user interface look like (e.g., will there be drop-down menus or…)? Will the data be displayed in a web-browser or a different application? In what sequence is data entered, and what triggers data-entry to occur? These application-development issues do not usually impact the information architecture in any substantive way, so thankfully we can ignore these questions during our design phase.

5. Tables, yes; easy, no! What’s the big deal… “It’s only tables!”, right? As you have already seen, designing information-architecture is challenging Even small-scale organizations present complexities Consider larger contexts…e.g., ERP implementations like SAP

6. A Design-Hierarchy Framework Recommended sequencing of efforts: Conceptual Defining the information content and all relationships Logical Defining the database structures (tables, typically, or objects in an O-O database) Physical Implementation details regarding the precise computer platform (considering both hardware & software)

7. A Design-Hierarchy Framework Where within this framework did your team fall, when you faced the Snowboard-club and fundraising organizations’ challenges? Conceptual? Logical? Physical? I hope not!

8. What is Conceptual Design? An answer to a common corporate “911 call” Conceptual design methods will provide the basis for a powerful visual approach for grappling with corporations’ complex information needs.

9. Conceptual Design Basics Focus on the real-world problem at hand About what (things) are you collecting information about? Such things will be called entities. What things potentially need to be associated with other things? Such associations will be called relationships. Forget about tables (for now) Tables will ultimately be the vehicle for implementing our design, but the best way to go about the initial (conceptual) design is to forget all about tables

10. Conceptual Design Importance Common misunderstanding: Conceptual design is so vague. I should write down my tables to be more precise. Wrong! Given a nicely defined conceptual design, it is (will be) easy to convert that design to a logical design. The conceptual phase is the essence of the design effort. We will learn how to convert from conceptual to the logical designs.

11. Before Conceptual Design Begins A critical phase that precedes the conceptual design effort is called Requirements Analysis Ask: what should the desired system do? Review/critique current system (if any) Collect relevant paper forms Historically, the #1 source of information Discussions with intended users Result of the requirement analysis step should be a full accounting of all information the new system should handle Conceptual design then addresses the architecture of that information

12. Conceptual Design:3 Primary Questions What are the entities? Each entity will become a table in the (relational) logical design. What are the attributes? Each attribute will become a column in the (relational) logical design. What are the relationships? Each relationship will become either a column or a new table in the logical design Sorry, but we don’t get to choose. It depends on the nature of the relationship.

13. Conceptual Design: Entities What are entities? You create a database to keep track of information about certain things. We call each different category of thing an “entity” What are examples of entities in the University of Oregon’s database? ___________ ___________ ___________ ___________

14. Conceptual Design: Attributes What are attributes? You create a database to keep track of information about certain things. We call each piece of entity-specific information an “attribute” What are examples of attributes for students in the Univ. of Oregon’s database? ___________ ___________ ___________ ___________

15. Conceptual Design: Relationships What are relationships? You create a database to keep track of information about certain things. We call information that connects entity instances (either across entities, or within a single entity class) a “relationship” What are examples of relationships for a class in the Univ. of Oregon’s database? ___________ ___________ ___________ ___________

16. Conceptual Design: Relationships Understanding when a relationship exists Consider: Is there a relationship? Knowing when a relationship exists requires that we understanding the business/problem context

17. Entities and Attributes In a conceptual model, an entity is defined by 3 things: It’s name It’s identifier It’s attributes The name should be descriptive, but brief. The identifier must be some piece(s) of data (can be an arbitrary #: 1,2,3,…) that will be unique for each instance of the entity.

18. An example entity Consider a “grade entity” in the Univ. database Official name: StudentGrades What is a valid identifier for each entity instance (i.e., a particular grade in the univ. db)? _________________________________ Or… ____________ What should its attribute(s) be? _________________________________

19. An example entity (cont.) What are the obvious relationships for a “grade entity”? Think of a particular grade of “B+” floating around in the univ. database…. To what other entity (instances) should we be able to relate any given grade to? At a minimum, we need to relate that grade to a particular _________, and to… …a particular ___________. And, indirectly, to what other entities? e.g., _____________________________

20. Entities for Snowboarding Club ________________ ________________ ________________ ________________ ________________ ________________ Member, Meeting, Trip, SnowboardArea, Equipment, and (potentially) RentalMember, Meeting, Trip, SnowboardArea, Equipment, and (potentially) Rental

21. Entities for Fundraising organization ________________ ________________ ________________ ________________ ________________ ________________ Member, Committee, Event, Charity, Donor, and (potentially) DonationMember, Committee, Event, Charity, Donor, and (potentially) Donation

22. Listing Attributes for Each Entity A crucial output of the requirements analysis phase is your understanding of what pieces of information (i.e., attributes!) you wish to collect. We need to define, for each entity, the list of attributes. Let us now do so specifically for the member entity in our two club scenarios.

23. Brainstorm: Attributes for Snowboard Club member ________________ ________________ ________________ ________________ ________________ ________________ ________________ ________________ ________________ ________________

24. Brainstorm: Attributes for Fundraising club member ________________ ________________ ________________ ________________ ________________ ________________ ________________ ________________ ________________ ________________

25. Relationships: big-picture thinking Because relationships can (and typically do) span entities, we need to think “outside of the box” Let us draw each entity as a labeled box, and then use lines to denote relationships between those entities. We again consider our two club scenarios to highlight the meaning of the term relationships.

26. Relationships: Snowboard Club(ignore rental entity for now)

27. Relationships: Fundraising club(ignore donation entity for now)

28. # of tables we will have in the relational database Now let’s think tables (“relational DB”) again… Each entity will become a table in the (relational) logical design. Each attribute will become a column in the (relational) logical design. Each relationship will become either a column or a new table in the logical design So, (# tables) > (# entities) e.g., even if both the Snowboard Club and Fundraising organizations imply 6 entities, we might find the Snowboard Club implies more tables due to its different relationship structures.

29. Designing with Visualization Business-oriented database applications are highly sophisticated The business world: the most complex database environment there is! Many external information sources Even more data generated within the firm “Big picture” visualization (as on prior 2 slides) techniques help users/designers understand the problem We will need to be proficient at “ER diagramming” techniques That is our next step!

30. Summary 3 design levels: conceptual, logical, physical We must first master conceptual design Defining entities, attributes, and relationships With these design elements in hand, we will then be ready to conquer the logical design phase We have defined the elements of a conceptual data model So, we now know the essentials of what a data model is, but we still need an approach for creating one Visualization via ER-diagramming will help us bring our conceptual models to life quickly, and with fewer errors