Basics - A Historical Perspective
- File Systems v/s DBMS
- Data Models
- Levels of Abstraction
- Structure of DBMS
- Database Design
- Database Design
- ER Diagrams
- Attributes
- Entity
- Relationships
- Additional Features of the ER Model
- Relational Model
- Introduction to the Relational Model
- Integrity constraint over relations
- Enforcing Integrity Constraints
- Querying Relational Data
- Views
- Relational Algebra
- Tuple Relational Calculus
- SQL
- Form of basic SQL query
- UNION
- INTERSECT
- EXCEPT
- Aggregation Operators
- Complex Integrity Constraints in SQL
- Triggers and Active data bases
- Schema Refinement
- Problems caused by redundancy
- Decompositions and its problems
- Functional Dependencies and its reasoning
- Introduction to Normal Forms
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
- Fourth Normal Form (4NF)
- Fifth Normal Form (5NF or PJNF)
- Sixth Normal Form (6NF)
- Transaction
- Transaction Concept
- Transaction State
- Implementation of Atomicity and Durability
- Concurrent Executions
- Serializability
- Recoverability
- Implementation of Isolation
- Lock Based Protocols
- Timestamp Based Protocols
- Validation Based Protocols
- Multiple Granularity
- Recovery and Atomicity in dbms
- Recovery with Concurrent Transactions
- Indexing
- Data on External Storage
- File Organization and Indexing
- Cluster Indexes
- Primary Indexes
- Secondary Indexes
- Index data Structures
- Comparison of File Organizations, Indexes and Performance Tuning
- Indexed Sequential Access Methods
- Dynamic Index Structure - B+ Tree
Database Design in DBMS
Database design is the organization of data according to a database model. Properly designed databases are easu to maintain, improves data consistency.
The database design process can be divided into six steps. The ER model(Entity Relationship model) is most relevant to the first three steps.
- Requirement analysis
- Conceptual database design
- Logical database design
- Schema refinement
- Physical database design
- Application and security design
1. Requirement analysis
- It is necessary to understand what data need to be stored in the database, what applications must be built, what are all those operations that are frequently used by the system.
- The requirement analysis is an informal process and it requires proper communication with user groups.
- There are several methods for organizing and presenting information gathered in this step. Some automated tools can also be used for this purpose.
2. Conceptual database design
- The information gathered, is used to develop a high-level description of the data to be stored in the database
- This is a steps in which E-R Model i.e. Entity Relationship model is built.
- The goal of this design is to create a simple description of data that matches with the requirements of users.
3. Logical database design
- This is a step in which ER model in converted to relational database schema, sometimes called as the logical schema in the relational data model.
4. Schema refinement
- In this step, relational database schema is analyzed to identify the potential problems and to refine it.
- The schema refinement can be done with the help of normalizing and restructuring the relations.
5. Physical database design
- The design of database is refined further.
- This step may simply involve building indexes on tables and clustering tables, redesign of parts of the database schema obtained from the earlier design steps.
6. Application and security design
- Using design methodologies like UML(Unified Modeling Language) try to address the complete software design of the database can be accomplished.
- The role of each entity in every process must be reflected in the application task.
- For each role, there must be the provision for accessing and prohibiting some part of database.
- Thus some access rules must be enforced on the application(which is accessing the database) to protect the security features.
Next Topic :ER Diagrams in DBMS