Download - Conceptual database design
![Page 1: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/1.jpg)
Muhammad Umair
Conceptual Database Design
Oracle Database 11g Developer Track
![Page 2: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/2.jpg)
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
![Page 3: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/3.jpg)
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
› Just having data is not good enough reason to have database– Having data is not the problem
![Page 4: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/4.jpg)
Why do you need a database?
![Page 5: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/5.jpg)
Why do you need a database?
› Problems– Size– Ease of update– Accuracy– Security– Redundancy– Importance
![Page 6: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/6.jpg)
Why do you need a database?› Size
![Page 7: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/7.jpg)
Why do you need a database?› Ease of update
![Page 8: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/8.jpg)
Why do you need a database?› Accuracy
![Page 9: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/9.jpg)
Why do you need a database?›Security
![Page 10: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/10.jpg)
Why do you need a database?›Redundancy
![Page 11: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/11.jpg)
Why do you need a database?›Importance
![Page 12: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/12.jpg)
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
![Page 13: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/13.jpg)
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.
![Page 14: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/14.jpg)
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?
![Page 15: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/15.jpg)
Entities
Attributes
Relationships between entities
Product
address
buys
Database Design Process
![Page 16: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/16.jpg)
Database Design Process› Logical Model
– How is each entity and relationship represented in the Data Model of the DBMS› Hierarchic?› Network?› Relational?› Object-Oriented?
![Page 17: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/17.jpg)
› Determine user requirements› Determine business rules› Build conceptual data model
– outcome is an entity-relationship diagram or similar communicationtool
Conceptual Database Modeling
EnterpriseModeling
ConceptualData Modeling
LogicalDB Design
Physical DBDesign/Creation
DBImplementation
DBMaintenance
![Page 18: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/18.jpg)
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
![Page 19: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/19.jpg)
Extract from data dictionary for Staff user views of Dream Home showing description of entities
![Page 20: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/20.jpg)
Build Conceptual Data
› Step 2 Identify relationship types– To identify important relationships that
exist between entity types– Typically verbs, verb phrases– Determine multiplicity constraints
![Page 21: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/21.jpg)
Extract from data dictionary for Staff user views of Dream Home showing description of relationships
![Page 22: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/22.jpg)
First-cut ER diagram for Staff user views of Dream Home
![Page 23: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/23.jpg)
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
![Page 24: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/24.jpg)
Extract from data dictionary for Staff user views of Dream Home showing description of attributes
![Page 25: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/25.jpg)
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
![Page 26: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/26.jpg)
ER diagram for Staff user views of Dream Home with primary keys added
![Page 27: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/27.jpg)
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
![Page 28: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/28.jpg)
Build Conceptual Data
![Page 29: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/29.jpg)
Build Conceptual Data Model
› Check model for redundancy – To check for presence of any redundancy in
model and to remove any that does exist
![Page 30: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/30.jpg)
Example of removing a redundant relationship called Rents
![Page 31: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/31.jpg)
Example of a non-redundant relationship Father Of (Time dimension example)
![Page 32: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/32.jpg)
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
![Page 33: Conceptual database design](https://reader035.vdocuments.us/reader035/viewer/2022070601/58808eea1a28ab35718b7373/html5/thumbnails/33.jpg)
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