Chapter 1 - Database Design Flashcards
What are the six stages of database design?
- Requirement analysis
- Conceptual Database Design
- Logical Database Design
- Refine the Schemas
- Physical Database Design
- Application and Security Design
What is stage 1 - requirement analysis?
MAIN: Client tells us what they want
Developers ask and clarify needs
Determine
Data to be stored
Applications using the data
Operations to be performed
What is stage 2 - conceptual design?
in this stage, we determine
1. what tools we need to model requirements,
2. determine the high level description and
3. determine constraints to be modelled
USING the given user requirements
In stage 2 conceptual design, high level description of data is formed based on requirement analysis info. What is this high level description? Also give example of Constraints to be modelled
It is description about what entities exist, their attributes, and how they are related.
Constraints - referential integrity etc.
This helps to ensure data integrity and validity
What is stage 3 - logical design? What does it involve
Transition from Entity-Relationship (ER) model into relational database schemas.
Involves defining the structure of database tables,
specifying what the columns (attributes) are,
and
determining how rows (records) will be organised.
Schemas look like this btw:
CAR (CAR_ID, Serial_No, Model_No, Color, Year, CustomerID, EmpID)
BUT IN TABLE FORM
What are the things to ensure in a database schema?
Specifies all important or relevant data elements
Ensures data consistency (Consistent formatting for all data entries)
Ensure unique keys for all entries and DB objects (will be used for identifying)
Ensure that each column in table has a name and data type
What stages are ER model most relevant to?
First 3 stages
What is stage 4 - schema refinement?
The focus is on improving the structure of the database schema.
In this stage, you thoroughly analyse the relationships and structures of database tables (relations) to identify an potential problems
What is schema refinement also referred to as?
Normalisation: the process to reduce data redundancy and improve data integrity
What is redundancy and anomalies?
Redundancy: repetition of same data or duplicate copies of same data
* Anomalies: the problems occurred after poorly planned
What is decomposition?
The most popular technique for schema refinement is decomposition. It involves breaking down tables into smaller, related tables to eliminate data redundancy. This is achieved through the creation of new tables and defining appropriate relationships between them.
What is Database Design stage 5 – physical design?
It is the process of transition from logical data model to physical model.
What is physical design optimised for?
Data access, performance requirements and hardware/software constraints.
Constraints include considerations such as server specifications, storage systems, and the database management system (DBMS) being used.
What does it mean when we say that physical design is optimised for data access and performance?
This means configuring the database to efficiently handle typical workloads, queries, and transactions.
Consider typical Workloads (For performance):
You consider the typical workloads that the system will encounter.
Analysing the types of queries and transactions that will be executed
and ensuring that the database schema, indexing,
and data storage methods align with these workloads.
Build indexes on tables and cluster some tables: (For data access)
You determine which indexes to create on tables. Indexes improve query performance by allowing for faster data retrieval. Additionally, you may decide to cluster certain tables, which physically reorganises the data to improve data access patterns for specific use cases.
What does database design stage 6 – Application and security design, focus on?
The focus is on designing and implementing security measures to protect DB from
unauthorised access
malicious actions
accidental loss
Misuse
