Conceptual database design

conceptual database design

1. Muhammad Umair Conceptual Database Design Oracle Database 11g Developer Track

2. Why do you need a database? To solve a problem You have some data(information) of your client or your business you want to create. There must be must be some data you want to store And this data could be anything about customers, employee, products, dates, numeric from, document fine, images, audio/video etc. But you can already store data Write in a text file and type in your information Use spreadsheet If you got documents organize them in folder

3. Why do you need a database? Many small business starts with spreadsheet And work continue like this for years And that might be causing no problem J ust having data is not good enough reason to have database Having data is not the problem

4. Why do you need a database?

5. Why do you need a database? Problems Size Ease of update Accuracy Security Redundancy Importance

6. Why do you need a database? Size

7. Why do you need a database? Ease of update

8. Why do you need a database? Accuracy

9. Why do you need a database? Security

10. Why do you need a database? Redundancy

11. Why do you need a database? Importance

12. Why do you need a database? You might have all of these problems or some of these problems These are the reason we need database These are the problems

13. Building an Application with a DBMS Requirements modeling (conceptual, pictures) Decide what entities should be part of the application and how they should be linked .

14. Database Design Process Conceptual Model Merge the collective needs of all applications Determine what Entities are being used Some object about which information is to maintained What are the Attributes of those entities? Properties or characteristics of the entity What attributes uniquely identify the entity What are the Relationships between entities How the entities interact with each other?

15. Entities Attributes Relationships between entities Product address buys Database Design Process

16. Database Design Process Logical Model How is each entity and relationship represented in the Data Model of the DBMS Hierarchic? Network? Relational? Object-Oriented?

17. Determine user requirements Determine business rules Build conceptual data model outcome is an entity-relationship diagram or similar communication tool Conceptual Database Modeling Enterprise Modeling Conceptual Data Modeling Logical DB Design Physical DB Design/Creation DB Implementation DB Maintenance

18. Build Conceptual Data To build a conceptual data model of data requirements of enterprise Model comprises entity types, relationship types, attributes and attribute domains, primary and alternate keys, and integrity constraints Step 1 Identify entity types To identify required entity types Typically nouns, noun phrases, major objects

19. Extract from data dictionary for Staff user views of Dream Home showing description of entities

20. Build Conceptual Data Step 2 Identify relationship types To identify important relationships that exist between entity types Typically verbs, verb phrases Determine multiplicity constraints

21. Extract from data dictionary for Staff user views of Dream Home showing description of relationships

22. First-cut ER diagram for Staff user views of Dream Home

23. Build Conceptual Data Step 3 Identify and associate attributes with entity or relationship types To associate attributes with appropriate entity or relationship types and document details of each attribute Determine information required Step 4 Determine attribute domains To determine domains for attributes in data model and document details of each domain

24. Extract from data dictionary for Staff user views of Dream Home showing description of attributes

25. Step 1 Build Conceptual Data Step 5 Determine candidate, primary, and alternate key attributes To identify candidate key(s) for each entity and if there is more than one candidate key, to choose one to be primary key and others as alternate keys Strong entity Primary key easily identifiable Weak entity Primary key not identifiable Need to map entity & relationship to owner entity to identify primary key

26. ER diagram for Staff user views of Dream Home with primary keys added

27. Build Conceptual Data Step 6 Consider use of enhanced modeling concepts (optional step) To consider use of enhanced modeling concepts, such as specialization / generalization, aggregation

28. Build Conceptual Data

29. Build Conceptual Data Model Check model for redundancy To check for presence of any redundancy in model and to remove any that does exist

30. Example of removing a redundant relationship called Rents

31. Example of a non-redundant relationship Father Of (Time dimension example)

32. Build Conceptual Data Model Step 8 Validate conceptual model against user transactions To ensure that conceptual model supports required transactions Describe transactions Use transaction pathways Example List the details of properties managed by a named member of staff at the branch

33. Build Conceptual Data Model Step 9 Review conceptual data model with user To review conceptual data model with user to ensure that model is ‘true’ representation of data requirements of enterprise