Overview of Database Development

1. Overview of Database Development Data Modeling and Relational Database Design

2. Objectives • Describe the phases of the database development process • Describe the phases of the application development process • Explain conceptual data modeling and database design

3. Strategy and Analysis Design Build and Document Transition Production SYSTEM DEVELOPMENT LIFE CYCLE

4. Conceptual Data Modeling Function Modeling Database Design Application Design Database Build Application Build Database Development Process Business Requirements Information Process Cross-checking ER model, entity definition Function hierarchy, function definition Module (screen, report, menu, program design) Table, index, view Cross-checking Database Application Operational System

5. Conceptual Data Modeling Database Development Process Business Information Requirements Business view Systems view Logical Database Design Physical Database Build Operational Database

6. Terminology CONCEPTUAL LOGICAL (Business view) (Systems view) ANALYSIS DESIGN ENTITY TABLE RELATIONSHIP FOREIGN KEY ATTRIBUTE COLUMN PRIMARY KEY UNIQUE IDENTIFIER UNIQUE KEY

7. Information Models • Organize thought processes • Accurately model business data • Communicate with stakeholders • Analyze the scope • Provide sound basis for system design

8. Goals of Conceptual Data Modeling • Robust syntax • User communication • Ease of development • Definition of scope • Integration of multiple applications

9. Modeling Basic Entities and Attributes

10. Overview • Why conceptual modeling? • Introduction of the Key role players: • Entities • Attributes • Relationships

11. Why Create a Conceptual Model? • It describes exactly the information needs of the business • It facilitates discussion • It helps to prevent mistakes, misunderstanding • It forms important “ideal system” documentation • It forms a sound basis for physical database design • It is a very good practice with many practitioners

12. Conceptual Model Between Dream and Reality...

13. Entity Relationship Modeling • Models business,not implementation • Is a well-established technique • Has a robust syntax • Results in easy-to-read diagrams… • ...although they maylook rather complex at first sight

14. Goals of Entity Relationship Modeling • Capture all required information • Information appears only once • Model no information that is derivable from other information already modeled • Information is in a predictable, logical place

15. Server CONCEPTUAL DATA MODELS Model of systemin client’s mind Entity model ofclient’s model Table modelof entity model Tables on disk

16. Database Types ER Model Network Hierarchical Relational

17. ENTITY RELATIONSHIP MODEL • Create an entity relationship diagram from business specifications or narratives DEPARTMENT #* number * name o location EMPLOYEE #* number * name o job title assigned to composed of • Scenario • “. . . Assign one or more employees to a department . . .” • “. . . Some departments do not yet have assigned employees . . .”

18. Entity • An Entity is: • “Something” of significance to the business about which data must be known. • A name for the things that you can list. • Usually a noun. • Examples: objects, events • Entities have instances.

19. Identify and Model Entities • Identify a noun • Is it significant? • Is there information about it that the business needs to keep? • Is it a group or an instance? • Name the entity • Write a description of it • Identify a few attributes • Draw a soft box for it

20. Entity Instances Head office Finance Personnel Sales EMPLOYEE DEPARTMENT

21. EMPLOYEE badge number name date of birth salary Identifying a Unique Instance

22. Entities and Instances PERSON PRODUCT PRODUCT TYPE EMPLOYMENT CONTRACT JOB SKILL LEVEL TICKET RESERVATION PURCHASE ELECTION PRINTER PREFERENCE DOCUMENT VERSION Mahatma Gandhi 2.5 x 3.5 mm copper nail Nail My previous contract Violinist Fluent Tonight: Hamlet in the Royal The CD I bought yesterday For parliament next fall … …

23. Entities and Sets • An entity represents a set of instances that are of interest to a particular business. JOB manager cook waitress dish washer financial controller porter waiter piano player

24. Attribute • Also represents something of significance to the business • Is a single valued property detail of an entity • Is a specific piece of information that: • Describes • Quantifies • Qualifies • Classifies • Specifies an entity.

25. Attribute Examples Entity EMPLOYEE CAR ORDER JOB TRANSACTION EMPLOYMENT CONTRACT Attribute Family Name, Age, Shoe Size, City, Email, … Model, Weight, Catalog Price, … Order Date, Ship Date, … Title, Description, ... Amount, Transaction Date, … Start Date, Salary, ...

26. EMPLOYEE JOB ELECTION TICKET RESERVATION ORDER JOB ASSIGNMENT Entity Representation in Diagram • Drawn as a “softbox” • Name singular • Name inside • Neither size, nor position has a special meaning During design, entities usually lead to tables.

27. Attributes in Diagrams EMPLOYEE*family Name address birth Dateoshoe Sizeo email JOBtitleo description * o * * Mandatory attribute, that is, known and available for every instance Optional attribute, that is, unknown or unimportant to know for some instances o During design, attributes lead to columns.

28. ENTITY RELATIONSHIP MODELING CONVENTIONS Entity Soft box Singular, unique name Uppercase Synonym in parentheses Attribute Singular name Lowercase Mandatory marked with “*” Optional marked with “o” DEPARTMENT #* number * name o location EMPLOYEE #* number * name o job title assigned to composed of Unique Identifier (UID) Primary marked with “#” Secondary marked with “(#)”