Database Design Process
The database design process consists of a number of steps listed below. We will focus mainly on step 2, the conceptual database design, and the models used during this step.
Step 1: Requirements Collection and Analysis ▪ Prospective users are interviewed to understand and document data requirements ▪ This step results in a concise set of user requirements, which should be detailed and complete. ▪ The functional requirements should be specified, as well as the data requirements. Functional requirements consist of user operations that will be applied to the database, including retrievals and updates. ▪ Functional requirements can be documented using diagrams such as sequence diagrams, data flow diagrams, scenarios, etc.
Step 2: Conceptual Design ▪ Once the requirements are collected and analyzed, the designers go about creating the conceptual schema. ▪ Conceptual schema: concise description of data requirements of the users, and includes a detailed description of the entity types, relationships and constraints. ▪ The concepts do not include implementation details; therefore the end users easily understand them, and they can be used as a communication tool. ▪ The conceptual schema is used to ensure all user requirements are met, and they do not conflict.
Step 3: Database Implementation
▪ Many DBMS systems use an implementation data model, so the conceptual schema is transformed from the high-level data model into the implementation data model. ▪ This step is called logical design or data model mapping, which results in the implementation data model of the DBMS.
Step 4: Physical Design ▪ Internal storage structures, indexes, access paths and file organizations are specified. ▪ Application programs are designed and implemented.
Entity Relationship (ER) Model
The most popular high-level conceptual data model is the ER model. It is frequently