Embedded Files
UNIT 2
1.1 Overview of the Database design Process
• Creating a database application involves multiple steps, from designing the database schema to specifying how data is accessed and maintained.
• This process ensures that the database meets the needs of users, supports efficient operations, and remains secure.
Design Phases :
For large, complex applications, the design process must be systematic and involve collaboration with various stakeholders. This is particularly important since it’s difficult or a single person to fully understand all the data needs of an application. The process can be broken down into the following key phases:
1. Characterizing User Requirements:
o Understand user data needs and operations through interaction with domain experts and users.
o Outcome: A textual specification of user requirements.
2. Conceptual Design:
o Choose a data model (e.g., Entity-Relationship model) to represent the requirements.
o Translate the user needs into a high-level conceptual schema, focusing on entities, attributes, relationships, and constraints.
o Outcome: An Entity-Relationship Diagram (ERD).
3. Functional Requirements:
o Specify operations (e.g., CRUD) users will perform on the data.
o Ensure the schema supports these operations.
4. Logical Design:
o Map the conceptual schema to a logical data model (typically relational).
o Convert the conceptual model into tables, keys, and relationships (relation schema).
5. Physical Design:
o Specify physical storage details, including file organization and indexing strategies.
o Optimize for performance and access speed.
6. Implementation and Maintenance:
o Implement the database and application code.
o Perform ongoing maintenance and adjustments as needed.
Each phase builds on the previous one, ensuring the database is robust, functional, and efficient.
Design Alternatives :
In database design, we represent different things (like people, places, products) as entities. These entities are connected by relationships, which describe how they interact with each other.
1. Entities and Relationships
• Entities: These are distinctly identifiable things in the database. For example, in a university database, entities could include:
o Instructors
o Students
o Courses
o Departments
o Course Offerings (or sections, if a course is offered multiple times in different
semesters)
• Relationships: Entities are connected by relationships. For example:
o A student "takes" a course offering.
o An instructor "teaches" a course offering.
2. Common Problems to Avoid:
• Redundancy (repeating information):
o Storing the same data in multiple places can cause problems, like inconsistencies
if the data is updated in one place but not another.
o Example: If you store the course title with every course offering, you might end
up with different titles for the same course.
o Solution: Store the course title only once, and use a course identifier to link it to the course offerings.
• Incompleteness (missing important data):
o If the design doesn't fully capture all parts of the business, some information might be hard or impossible to represent.
o Example: If you only store information about course offerings and not courses, you can't represent a course that isn't currently offered.
o Solution: Have separate entities for both Courses and Course Offerings to capture all necessary information.
3. Design Choices:
• Sometimes, you need to decide between different ways to model things. For example:
o Should a sale be a relationship between a customer and a product, or should it
be its own entity that connects to both?
• The choice affects how you store and use data.
4. Challenges in Database Design
• Database design involves balancing multiple concerns:
o Redundancy: Avoiding unnecessary repetition of data.
o Completeness: Ensuring all relevant information is represented.
o Consistency: Avoiding conflicting or ambiguous data.
o Efficiency: Ensuring the design supports efficient queries and transactions.
Page updated
Google Sites
Report abuse