Hubbry Logo
Database designDatabase designMain
Open search
Database design
Community hub
Database design
logo
7 pages, 0 posts
0 subscribers
Be the first to start a discussion here.
Be the first to start a discussion here.
Contribute something
Database design
Database design
Database design
View on Wikipedia
from Wikipedia

Database design is the organization of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model.[1] A database management system manages the data accordingly.

Database design is a process that consists of several steps.

Conceptual data modeling

[edit]

The first step of database design involves classifying data and identifying interrelationships. The theoretical representation of data is called an ontology or a conceptual data model.

Determining data to be stored

[edit]

In a majority of cases, the person designing a database is a person with expertise in database design, rather than expertise in the domain from which the data to be stored is drawn e.g. financial information, biological information etc. Therefore, the data to be stored in a particular database must be determined in cooperation with a person who does have expertise in that domain, and who is aware of the meaning of the data to be stored within the system.

This process is one which is generally considered part of requirements analysis, and requires skill on the part of the database designer to elicit the needed information from those with the domain knowledge. This is because those with the necessary domain knowledge often cannot clearly express the system requirements for the database as they are unaccustomed to thinking in terms of the discrete data elements which must be stored. Data to be stored can be determined by Requirement Specification.[2]

Determining data relationships

[edit]

Once a database designer is aware of the data which is to be stored within the database, they must then determine where dependency is within the data. Sometimes when data is changed you can be changing other data that is not visible. For example, in a list of names and addresses, assuming a situation where multiple people can have the same address, but one person cannot have more than one address, the address is dependent upon the name. When provided a name and the list the address can be uniquely determined; however, the inverse does not hold – when given an address and the list, a name cannot be uniquely determined because multiple people can reside at an address. Because an address is determined by a name, an address is considered dependent on a name.

(NOTE: A common misconception is that the relational model is so called because of the stating of relationships between data elements therein. This is not true. The relational model is so named because it is based upon the mathematical structures known as relations.)

Conceptual schema

[edit]

The information obtained can be formalized in a diagram or schema. At this stage, it is a conceptual schema.

ER diagram (entity–relationship model)

[edit]
A sample entity–relationship diagram

One of the most common types of conceptual schemas is the ER (entity–relationship model) diagrams.

Attributes in ER diagrams are usually modeled as an oval with the name of the attribute, linked to the entity or relationship that contains the attribute.

ER models are commonly used in information system design; for example, they are used to describe information requirements and / or the types of information to be stored in the database during the conceptual structure design phase.[3]

Logical data modeling

[edit]
Main article: Logical schema

Once the relationships and dependencies amongst the various pieces of information have been determined, it is possible to arrange the data into a logical structure which can then be mapped into the storage objects supported by the database management system. In the case of relational databases the storage objects are tables which store data in rows and columns. In an Object database the storage objects correspond directly to the objects used by the Object-oriented programming language used to write the applications that will manage and access the data. The relationships may be defined as attributes of the object classes involved or as methods that operate on the object classes.

The way this mapping is generally performed is such that each set of related data which depends upon a single object, whether real or abstract, is placed in a table. Relationships between these dependent objects are then stored as links between the various objects.

Each table may represent an implementation of either a logical object or a relationship joining one or more instances of one or more logical objects. Relationships between tables may then be stored as links connecting child tables with parents. Since complex logical relationships are themselves tables they will probably have links to more than one parent.

Normalization

[edit]
Main article: Database normalization

In the field of relational database design, normalization is a systematic way of ensuring that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics—insertion, update, and deletion anomalies that could lead to loss of data integrity.

A standard piece of database design guidance is that the designer should create a fully normalized design; selective denormalization can subsequently be performed, but only for performance reasons. The trade-off is storage space vs performance. The more normalized the design is, the less data redundancy there is (and therefore, it takes up less space to store), however, common data retrieval patterns may now need complex joins, merges, and sorts to occur – which takes up more data read, and compute cycles. Some modeling disciplines, such as the dimensional modeling approach to data warehouse design, explicitly recommend non-normalized designs, i.e. designs that in large part do not adhere to 3NF. Normalization consists of normal forms that are 1NF, 2NF, 3NF, Boyce-Codd NF (3.5NF), 4NF, 5NF and 6NF.

Document databases take a different approach. A document that is stored in such a database, typically would contain more than one normalized data unit and often the relationships between the units as well. If all the data units and the relationships in question are often retrieved together, then this approach optimizes the number of retrieves. It also simplifies how data gets replicated, because now there is a clearly identifiable unit of data whose consistency is self-contained. Another consideration is that reading and writing a single document in such databases will require a single transaction – which can be an important consideration in a Microservices architecture. In such situations, often, portions of the document are retrieved from other services via an API and stored locally for efficiency reasons. If the data units were to be split out across the services, then a read (or write) to support a service consumer might require more than one service calls, and this could result in management of multiple transactions, which may not be preferred.

Physical design

[edit]

Physical data modeling

[edit]
Main article: Physical schema

The physical design of the database specifies the physical configuration of the database on the storage media. This includes detailed specification of data elements and data types.

Other physical design

[edit]

This step involves specifying the indexing options and other parameters residing in the DBMS data dictionary. It is the detailed design of a system that includes modules & the database's hardware & software specifications of the system. Some aspects that are addressed at the physical layer:

  • Performance – mainly addressed via indexing for the read/update/delete queries, data type choice for insert queries
  • Replication – what pieces of data get copied over into another database, and how often. Are there multiple-masters, or a single one?
  • High-availability – whether the configuration is active-passive, or active-active, the topology, coordination scheme, reliability targets, etc all have to be defined.
  • Partitioning – if the database is distributed, then for a single entity, how is the data distributed amongst all the partitions of the database, and how is partition failure taken into account.
  • Backup and restore schemes.

At the application level, other aspects of the physical design can include the need to define stored procedures, or materialized query views, OLAP cubes, etc.

See also

[edit]

References

[edit]

Further reading

[edit]
[edit]
Revisions and contributorsEdit on WikipediaRead on Wikipedia

Database design

View on Grokipedia
from Grokipedia
Database design is the systematic process of defining the structure, organization, and constraints of a database to support efficient , retrieval, , and within a database management system (DBMS). It involves creating a detailed that captures the real-world entities, their attributes, and relationships to minimize , ensure consistency, and facilitate for various applications. Primarily focused on relational databases, though applicable to systems, this discipline bridges user requirements with technical implementation to produce a reliable and performant repository. The database design process typically unfolds in several iterative stages to transform high-level requirements into a functional . It begins with , where stakeholders' data needs, business rules, and processing demands are gathered through interviews and documentation to identify entities and constraints. This is followed by , which develops an abstract representation using models like the Entity-Relationship (ER) diagram to depict entities, attributes, and relationships such as one-to-one, one-to-many, or many-to-many. Subsequent logical design translates this into a relational with tables, columns, primary keys (unique identifiers), and foreign keys (for linking tables), often using SQL's (DDL). Schema refinement applies normalization to eliminate redundancies, followed by physical design for optimizing storage, indexes, and access methods, and finally security design to define access controls. Key principles underpinning database design emphasize , efficiency, and independence to support long-term maintainability. Normalization, a core technique, organizes data into progressively higher normal forms (e.g., 1NF for atomic values, 3NF to avoid transitive dependencies, and BCNF for functional dependency resolution) to reduce anomalies during insertions, updates, or deletions. The , introduced by E.F. Codd, forms the foundation with tables as relations, ensuring through keys and constraints. Additionally, principles of allow schema changes without disrupting applications, while considerations for and address distributed or environments. These elements collectively ensure that database designs are robust, adaptable, and aligned with organizational objectives.

Overview

Definition and Scope

Database design is the process of defining the structure, constraints, and organization of data within a database to meet the specific requirements of applications that interact with it. This involves creating a detailed that specifies how data is stored, accessed, and maintained to support efficient operations and reliable . The core objectives of database design are to ensure by enforcing rules that prevent inconsistencies and invalid entries, promote efficiency through optimized storage and query performance, enable to accommodate increasing data volumes and user loads, and improve by providing intuitive access mechanisms for developers and end-users. These goals collectively aim to create a robust foundation for data-driven applications while minimizing redundancy and supporting long-term maintainability. Historically, database design emerged in the with E.F. Codd's introduction of the , which formalized data organization into tables (relations) with rows and columns, emphasizing mathematical rigor and independence from physical storage details. This model laid the groundwork for modern management systems (RDBMS). Over subsequent decades, the field evolved to incorporate object-oriented paradigms in the late and , enabling the design of databases that handle complex, hierarchical data structures akin to those in . More recently, since the early 2000s, influences from systems have expanded design approaches to support flexible schemas for unstructured or in distributed environments, addressing limitations of rigid relational structures for applications. The scope of database design is delimited to the conceptual and structural aspects of data organization, such as defining entities, relationships, and integrity constraints, while deliberately excluding implementation-specific elements like application coding, hardware selection, or low-level storage configurations. This focus ensures that the design remains abstract and adaptable to various technologies. At a high level, the process unfolds in three primary phases: to capture user requirements and high-level models, logical design to translate those into a specific like relational or object-oriented, and physical design to fine-tune for —each building progressively without overlapping into operational deployment.

Importance in Information Systems

Effective database design plays a pivotal role in information systems by optimizing and . It reduces , thereby conserving storage resources and mitigating risks of inconsistencies across datasets. This approach also enhances query performance through strategic selection of storage structures and indexing, which lowers access times and operational costs. Moreover, it ensures consistency by enforcing relationships and constraints that prevent discrepancies during concurrent updates or transactions. Finally, it supports , enabling systems to expand seamlessly in distributed environments without proportional increases in complexity. In broader information systems, robust database design drives informed by delivering reliable, accessible for analytical processes. It facilitates , such as with the General Data Protection Regulation (GDPR), by embedding privacy principles like data minimization and granular access controls directly into the and storage mechanisms. Additionally, controls inherent in thoughtful design minimize errors in data-driven applications, validating inputs and safeguarding against invalid states that could propagate inaccuracies. Real-world applications underscore these benefits across domains. In (ERP) systems, effective design integrates disparate data sources to streamline business operations and support real-time reporting. For web applications, it enables handling of dynamic user loads through optimized retrieval paths. In , it accommodates vast volumes and varied formats, allowing efficient processing for deriving actionable insights. Poor database design, however, incurs significant drawbacks, including data anomalies like insertion, update, and deletion inconsistencies that compromise reliability and elevate maintenance expenses. Such flaws also heighten vulnerabilities, often stemming from misconfigurations or inadequate that expose sensitive information to unauthorized access. The significance of database design has grown with technological shifts, evolving from centralized relational paradigms to cloud-native and distributed architectures in the , which prioritize resilience, elasticity, and integration in scalable, multi-node setups.

Conceptual Design

Identifying Entities and Attributes

Identifying entities and attributes is a foundational step in the conceptual phase of database design, where the primary data objects and their properties are recognized to model the real-world domain accurately. This process begins with analyzing user requirements to pinpoint key objects of interest, such as "" or "Product" in a sales system, ensuring the database captures essential information without redundancy. Domain analysis follows, involving a thorough examination of the business context to identify tangible or abstract nouns that represent persistent data elements, as outlined in the Entity-Relationship (ER) model introduced by Peter Chen. Brainstorming sessions with stakeholders further refine this by listing potential entities based on organizational needs, forming the basis for subsequent development. Techniques for entity identification include requirement gathering methods like structured interviews, surveys, and analysis, which elicit descriptions of business processes and data flows to reveal core entities. For instance, in a database, requirements might highlight "" as an entity through discussions on enrollment and grading processes. A is then employed to document these entities systematically, recording their names, descriptions, and initial attributes to maintain consistency throughout design. This tool also aids in validating completeness by cross-referencing gathered requirements against the dictionary entries. Attributes are the descriptive properties of entities that specify their characteristics, such as values or states. They are defined by their types: simple attributes, which are atomic and indivisible (e.g., an ID); composite attributes, which can be subdivided into sub-attributes (e.g., a full comprising , , and ); and derived attributes, computed from other attributes (e.g., age calculated from birth date). Each attribute is assigned a domain, defining allowable data types like , , or date, along with constraints such as length or range to ensure . Keys are critical attributes for uniqueness: a primary key uniquely identifies each entity instance (e.g., Student ID), while candidate keys are potential primaries that could serve this role. In the university example, the entity might include attributes like studentID (, integer domain), name (composite: first name and last name, string domain), and enrollmentDate (simple, date domain), with a derived attribute like yearsEnrolled based on the current date. These are documented in the to specify domains and keys explicitly. Common pitfalls in this process include over-identifying entities by treating transient or calculable items as persistent (e.g., mistaking "current grade" for a separate entity instead of a derived attribute), leading to overly complex models. Conversely, under-identifying occurs when key domain objects are overlooked due to incomplete , resulting in incomplete data capture and future redesign needs. To mitigate these, iterative validation against user feedback is essential. Identified entities provide the building blocks for defining relationships in the subsequent design phase.

Defining Relationships and Constraints

In database conceptual design, relationships represent associations between entities, capturing how real-world objects interact, as formalized in the entity-relationship (ER) model proposed by Peter Chen in 1976. These relationships are essential for modeling the semantics of data, ensuring that the database structure reflects business requirements without delving into implementation details. Entities, previously identified as key objects with attributes, serve as the foundational building blocks for these associations. Relationships are classified by their , which defines the number of instances that can participate on each side. A one-to-one (1:1) relationship occurs when each instance of one is associated with at most one instance of another , such as a and their , where each holds exactly one valid and each belongs to one . A one-to-many (1:N) relationship links one instance of an to multiple instances of another, but not vice versa; for example, one department relates to many employees, while each employee belongs to exactly one department. A many-to-many (N:M) relationship allows multiple instances of each to associate with multiple instances of the other, such as students enrolling in multiple courses and courses having multiple students. Cardinality is further refined by participation constraints, specifying whether involvement is mandatory or optional. Total participation requires every instance of an to engage in the relationship, ensuring no isolated entities exist in that context—for instance, every employee must belong to a department. Partial participation permits entities to exist independently, as in optional relationships where a project may or may not have an assigned manager. These are often denoted using minimum and maximum values, such as (0,1) for optional single participation or (1,N) for mandatory multiple participation, providing precise control over relationship dynamics. Constraints enforce data validity and integrity within relationships, preventing inconsistencies during database operations. Domain constraints restrict attribute values to valid ranges or types, such as requiring an age attribute to be a positive greater than 0 and less than 150. Referential integrity constraints ensure that foreign references in relationships point to existing entities, maintaining consistency across associations—for example, an employee's department ID must match an existing department. Business rules incorporate domain-specific policies, such as requiring voter age to exceed 18, which guide constraint definition to align with organizational needs./09%3A_Integrity_Rules_and_Constraints) The ER model employs a textual notation to describe these elements without visual aids: entities are named nouns (e.g., "Employee"), relationships are verb phrases connecting entities (e.g., "works in" between Employee and Department), and attributes are listed with their types and constraints (e.g., Employee has SSN: unique string). and participation are annotated inline, such as "Department (1) works in Employee (0..N, total for Employee)." This notation facilitates clear communication of the model. Many-to-many relationships are resolved in conceptual modeling by introducing an , which breaks the N:M into two 1:N relationships and captures additional attributes unique to the association. For instance, in a customer order system, an N:M between and Product is resolved via an OrderLine associative entity, which links orders (1:N to customers) and line items (1:N to products) while storing details like . This approach enhances model clarity and supports subsequent logical design.

Developing the Conceptual Schema

The conceptual schema represents an abstract, high-level description of the requirements for a database, independent of any specific database or physical implementation details. It focuses on the overall structure, entities, relationships, and business rules without delving into technical aspects such as data types or storage mechanisms. This serves as a bridge between user requirements and the subsequent logical design phases, ensuring that the database captures the essential semantics of the domain. The primary tool for developing the conceptual schema is the Entity-Relationship (ER) model, introduced by Peter Chen in 1976 as a unified framework for representing data semantics. The ER model structures the schema using entities (real-world objects or concepts), relationships (associations between entities), and attributes (properties describing entities or relationships). ER diagrams visually depict this schema through standardized notation: rectangles for entities, diamonds for relationships, ovals for attributes, and lines to connect components, with indicators (e.g., 1:1, 1:N, M:N) specifying participation constraints. To construct an ER diagram, begin by listing identified entities and their key attributes, then define relationships with appropriate cardinalities, iteratively refining based on domain semantics to ensure semantic completeness. This diagrammatic approach facilitates communication among stakeholders and provides a technology-agnostic blueprint. Once constructed, the conceptual schema undergoes validation to confirm its completeness, consistency, and alignment with initial requirements. This involves stakeholder reviews, where domain experts verify that all entities and relationships fully represent the processes without redundancies or ambiguities, often using iterative feedback loops to resolve discrepancies. Tools may assist in detecting structural issues, such as missing keys or inconsistent cardinalities, ensuring the accurately models the real-world domain before proceeding. In object-oriented contexts, UML class diagrams offer an alternative to ER models for development, capturing both and behavioral aspects through classes, associations, and hierarchies that can map to relational databases. The resulting is a cohesive, validated artifact ready for translation into a logical model, such as the relational schema. For example, in a simple library system, the ER diagram might include: Entity "" (attributes: as , Title, Author); Entity "Member" (attributes: MemberID as , Name, Email); Relationship "Borrows" (diamond connecting Book and Member, with 1:N indicating one member can borrow many books, but each book is borrowed by at most one member at a time, including attribute LoanDate). This text-based representation highlights the integrated structure without implementation specifics.

Logical Design

Mapping to Logical Models

The mapping process transforms the , typically represented as an entity-relationship (ER) model, into a logical that specifies the structure of without regard to physical implementation details. This step bridges the abstract to a implementable form, primarily the , where entities become tables, attributes become columns, and relationships are enforced through keys. The process follows a systematic algorithm to ensure and referential consistency. In the , the dominant logical structure since its formalization by E.F. Codd in 1970, data is organized into tables consisting of rows (tuples) and columns (attributes), with relations defined mathematically as sets of tuples. Regular (strong) entities in the ER model map directly to tables, where each entity's simple attributes become columns, and a chosen key attribute serves as the to uniquely identify rows. Weak entities map to tables that include their partial key and the of the owning entity as a , forming a composite . For relationships, binary 1:1 types can be mapped by adding the of one participating entity to the table of the other (preferring the side with total participation), while 1:N relationships add the "one" side's as a to the "many" side's table. Many-to-many (M:N) relationships require a junction table containing the s of both participating entities as s, which together form the composite ; any descriptive attributes of the relationship are added as columns. Multivalued attributes map to separate tables with the attribute and the entity's as a composite key. Attributes in the logical model are assigned specific data types and domains to constrain values, such as for numeric identifiers, for variable-length strings, or DATE for temporal data, based on the attribute's semantic requirements in the . Primary keys ensure entity integrity by uniquely identifying each row, often using a single attribute like an ID or a composite of multiple attributes when no single key suffices. Foreign keys maintain by referencing primary keys in other tables, preventing orphaned records, while composite keys combine multiple columns to form a in cases like junction tables. Although the relational model predominates to its flexibility and support for declarative querying via SQL, alternative logical models include the hierarchical model, where forms a tree structure with parent-child relationships (e.g., IBM's IMS), and the network model, which allows more complex many-to-many links via pointer-based sets (e.g., standard). These older models map ER elements differently, with hierarchies treating entities as segments in a tree and networks using record types linked by owner-member sets, but they are less common today owing to limitations. A representative example is mapping a conceptual ER model for a library system, with entities Book (attributes: ISBN, title, publication_year), Author (attributes: author_id, name), and Borrower (attributes: borrower_id, name, address), a M:N relationship Writes between Book and Author, and a 1:N relationship Borrows between Borrower and Book (with borrow_date as a relationship attribute). The relational schema would include:
  • Book table: ISBN (primary key, VARCHAR(13)), title (VARCHAR(255)), publication_year (INTEGER)
  • Author table: author_id (primary key, INTEGER), name (VARCHAR(100))
  • Writes junction table: ISBN (foreign key to Book, VARCHAR(13)), author_id (foreign key to Author, INTEGER); composite primary key (ISBN, author_id)
  • Borrower table: borrower_id (primary key, INTEGER), name (VARCHAR(100)), address (VARCHAR(255))
  • Borrows table: borrower_id (foreign key to Borrower, INTEGER), ISBN (foreign key to Book, VARCHAR(13)), borrow_date (DATE); composite primary key (borrower_id, ISBN)
This mapping preserves the ER constraints through keys and data types, enabling efficient joins for queries like retrieving books by author.

Applying Normalization

Normalization is a systematic approach in relational database design aimed at organizing data to minimize redundancy and avoid undesirable dependencies among attributes, thereby ensuring data integrity and consistency. Introduced by in his foundational 1970 paper on the , normalization achieves these goals by decomposing relations into smaller, well-structured units while preserving the ability to reconstruct the original data through joins. The process addresses issues arising from poor design, such as inconsistent , by enforcing rules that eliminate repeating groups and ensure attributes depend only on keys in controlled ways. Codd further elaborated on normalization in 1971, defining higher normal forms to refine the and make databases easier to maintain and understand. A key tool in normalization is the concept of functional dependencies (FDs), which capture the semantic relationships in the data. An FD, denoted as X→YX \to Y where XX and YY are sets of attributes, states that the values of XX uniquely determine the values of YY; if two tuples agree on XX, they must agree on YY. FDs form the basis for identifying redundancies and guiding decomposition. For instance, in an employee relation, EmployeeID →\to Department might hold, meaning each employee belongs to exactly one department. Computing the closure of FDs (all implied dependencies) helps verify keys and normal form compliance. Normalization primarily targets three types of anomalies that plague unnormalized or poorly normalized schemas: insertion anomalies (inability to add data without extraneous information), deletion anomalies (loss of unrelated data when removing a ), and update anomalies (inconsistent changes requiring multiple updates). Consider a denormalized EmployeeProjects table tracking employees, their departments, and assigned projects, with FDs: {EmployeeID, ProjectID} →\to Department (composite key) and EmployeeID →\to Department.
EmployeeIDDepartmentProjectIDProjectName
E1HRP1Payroll
E1HRP2Training
E2ITP1Payroll
E2ITP3Software
An update anomaly occurs if Employee E1 moves to IT: the Department must be updated in two rows for P1 and P2, risking inconsistency if only one is changed. An insertion anomaly prevents adding a new department without an employee or . A deletion anomaly arises if E2's only P3 ends: deleting the row loses IT department info. These issues stem from transitive and partial dependencies, as addressed by normalization.

First Normal Form (1NF)

A relation is in 1NF if all attributes contain atomic (indivisible) values and there are no repeating groups or arrays within cells; every row-column intersection holds a single value. This eliminates nested relations and ensures the relation resembles a . Codd defined 1NF in his paper as the starting point for relational integrity, requiring domains for each attribute to enforce atomicity. To achieve 1NF, convert non-atomic attributes by creating separate rows or normalizing into additional tables. For example, if the EmployeeProjects table had a non-atomic ProjectName like "Payroll, Training" for E1, split it:
EmployeeIDDepartmentProjectIDProjectName
E1HRP1Payroll
E1HRP2Training
This step alone does not resolve dependencies but provides a flat structure for further normalization.

Second Normal Form (2NF)

A relation is in 2NF if it is in 1NF and every non-prime attribute (not part of any candidate key) is fully functionally dependent on every candidate key—no partial dependencies exist. Defined by Codd in 1971, 2NF targets cases where a non-key attribute depends on only part of a composite key, causing redundancy. Using the 1NF EmployeeProjects example, with candidate key {EmployeeID, ProjectID} and partial dependency EmployeeID →\to Department, the relation violates 2NF because Department depends only on EmployeeID. To normalize:
  1. Identify the partial dependency: EmployeeID →\to Department.
  2. Decompose into two relations: Employees ({EmployeeID} →\to Department) and EmployeeProjects ({EmployeeID, ProjectID} →\to ProjectName, with EmployeeID referencing Employees).
Resulting tables: Employees:
EmployeeIDDepartment
E1HR
E2IT
EmployeeProjects:
EmployeeIDProjectIDProjectName
E1P1
E1P2
E2P1
E2P3Software
This eliminates the update anomaly for department changes, now updated in one place. The is lossless, as joining on EmployeeID reconstructs the original.

Third Normal Form (3NF)

A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on a (i.e., non-prime attributes depend only directly on keys, not on other non-prime attributes). Codd introduced 3NF in 1971 to further reduce redundancy from transitive dependencies, ensuring relations are dependency-preserving and easier to control. Suppose after 2NF, we have a Projects table with {ProjectID} →\to {Department, Budget}, but Department →\to Budget (transitive: ProjectID →\to Department →\to Budget). This violates 3NF.
ProjectIDDepartmentBudget
P1HR50000
P2HR50000
P3IT75000
To normalize:
  1. Identify transitive FD: Department →\to Budget.
  2. Decompose into Projects ({ProjectID} →\to Department) and Departments ({Department} →\to Budget).
Projects:
ProjectIDDepartment
P1HR
P2HR
P3IT
Departments:
DepartmentBudget
HR50000
IT75000
This prevents update anomalies if budgets change for a department. A standard algorithm for 3NF synthesis, proposed by in 1976, starts with FDs, finds a minimal cover, and creates one relation per FD (key + dependent), merging if needed, ensuring dependency preservation.

Boyce-Codd Normal Form (BCNF)

A relation is in BCNF if, for every non-trivial FD X→YX \to Y, XX is a superkey (contains a candidate key). BCNF, a stricter refinement of 3NF introduced by Boyce and Codd around 1974, ensures all determinants are keys, eliminating all anomalies from FDs but potentially losing dependency preservation. Consider a StudentCourses relation with FDs: {Student, Course} →\to Instructor, but Instructor →\to Course (violating BCNF, as Instructor is not a ).
StudentCourseInstructor
S1C1ProfA
S1C2ProfB
S2C1ProfA
Here, Instructor →\to Course holds, but Instructor is not a key. Decompose using the violating FD:
  1. Create Instructors (Instructor →\to Course).
  2. Project StudentCourses onto {Student, Instructor}, removing Course.
Instructors:
InstructorCourse
ProfAC1
ProfBC2
StudentInstructors:
StudentInstructor
S1ProfA
S1ProfB
S2ProfA
The BCNF decomposition algorithm iteratively finds violating FDs and decomposes until none remain; it guarantees losslessness but not always dependency preservation. Higher normal forms extend BCNF to handle more complex dependencies. Fourth Normal Form (4NF), introduced by Ronald Fagin in 1977, requires no non-trivial multivalued dependencies (MVDs), where X→→YX \to\to Y means for a fixed XX, YY values are independent of other non-XX attributes; it prevents redundancy from independent multi-valued facts, like an employee's multiple skills and projects. Fifth Normal Form (5NF), also known as Project-Join Normal Form, defined by Fagin in 1979, eliminates join dependencies, ensuring no lossless decomposition into more than two projections introduces spurious tuples; it addresses cyclic dependencies across multiple attributes, such as suppliers, parts, and projects in a supply chain. These forms are relevant for schemas with complex inter-attribute independencies but are less commonly applied due to increased decomposition complexity.

Refining the Logical Schema

After achieving a normalized , refinement involves iterative adjustments to balance integrity, usability, and while preserving relational principles. This process builds on normal forms by introducing targeted enhancements that address practical limitations without delving into physical implementation. introduces controlled to the schema to optimize query , particularly in read-heavy applications where frequent joins would otherwise degrade efficiency. It is applied selectively when analysis shows that the overhead of normalization—such as multiple table joins—outweighs its benefits in reducing redundancy, for instance by combining related tables or adding derived attributes like computed columns. A common technique involves precomputing aggregates or duplicating key data, as seen in star schemas for (OLAP) systems, where a central links to denormalized dimension tables to simplify aggregation queries. However, this must be done judiciously to avoid widespread anomalies, typically targeting specific high-impact relations based on patterns. Adding a computed column may accelerate reporting but increase storage in large systems. Views serve as virtual tables derived from base relations, enhancing usability by providing tailored perspectives without modifying the underlying structure. Defined via SQL's CREATE VIEW statement, they abstract complex queries into simpler interfaces, such as a CustomerInfo view that joins customer and order tables to present a unified report, thereby supporting and restricting access to sensitive columns for . Assertions, as defined in the SQL standard, complement views by enforcing declarative constraints across multiple relations, using CREATE ASSERTION to specify rules like ensuring the total number of reservations does not exceed capacity; however, implementation in commercial DBMS is limited, and they are often replaced by triggers. These mechanisms allow iterative evolution, where views can be updated to reflect refinements while base tables remain stable. For complex integrity rules beyond standard constraints, triggers and stored procedures provide procedural enforcement at the logical level. Triggers are event-driven rules that automatically execute SQL actions in response to inserts, updates, or deletes, such as a trigger on an Enrollment table that checks and adjusts capacity limits to prevent overbooking, ensuring referential integrity without user intervention. Stored procedures, implemented as precompiled SQL/PSM modules, encapsulate reusable logic for tasks like updating derived values across relations, exemplified by a procedure that recalculates totals in a budget tracking system upon transaction commits. These tools extend the schema's expressive power, allowing enforcement of business rules that declarative constraints alone cannot handle, such as temporal dependencies or multi-step validations, though they may introduce some overhead that potentially slows transactions in high-volume environments. Validation of the refined relies on systematic techniques to verify correctness and before deployment. Testing with sample populates relations with representative instances to simulate operations and detect anomalies, such as join inefficiencies or constraint violations in a populated Students and Courses . Query evaluates expected workloads by estimating execution costs and identifying bottlenecks, often using tools to profile join orders or aggregation patterns. Incorporating user feedback loops involves stakeholder reviews of schema diagrams and queries to refine attributes or relationships iteratively, ensuring alignment with real-world needs. These methods collectively confirm that refinements enhance rather than compromise the 's . Refining the requires careful consideration of trade-offs, particularly between normalization's emphasis on minimal redundancy—which promotes update and storage savings—and the performance gains from or views that reduce query complexity at the expense of potential inconsistencies. For example, adding a computed column may accelerate reporting but increase storage in large systems, necessitating workload-specific decisions to avoid excessive join costs that could multiply query times. Assertions and triggers add overhead that potentially slows transactions in high-volume environments, yet they are essential for robust in mission-critical applications. Overall, these adjustments prioritize query and while monitoring storage impacts through validation.

Physical Design

Selecting Storage Structures

Selecting storage structures in database physical design involves determining the physical organization of data on storage media, guided by the logical schema to ensure efficient storage and retrieval. This process translates relational tables into file-based representations, considering factors such as insertion frequency, query types, and system resources. Common storage models include heap files, sequential files, and hash files, each suited to different workloads. Heap file organization stores records in the order of insertion without imposing any specific sequence or indexing, making it ideal for applications with high insert rates and occasional full table scans, as new records can be appended quickly to available space. In contrast, sequential file organization maintains records in a sorted order based on a key field, which supports efficient and range scans but requires periodic reorganization for inserts to preserve order. Hash file organization employs a to compute storage locations from key values, providing constant-time access for equality searches at the cost of inefficiency for range queries or uneven distribution if the is poor. Additionally, storage can be clustered, where data records are physically grouped and sorted according to a clustering attribute to minimize seek times for related accesses, or unclustered, where no such physical ordering exists, leading to potentially scattered disk locations. File organization techniques further refine how these models are implemented on disk. The Indexed Sequential Access Method (ISAM) combines sequential storage with a multilevel index, where a master index points to index blocks that locate data records, enabling direct access but suffering from overflow issues in dynamic environments as files grow. organization, introduced by and McCreight, uses a self-balancing tree structure with variable to maintain ordered data across nodes, supporting efficient insertions, deletions, and range queries while adapting to file growth without frequent reorganizations. For large-scale databases, partitioning strategies divide into manageable subsets to improve manageability and performance. Horizontal partitioning splits a table into row subsets, with range partitioning assigning rows to partitions based on key value intervals for ordered access, and hash partitioning distributing rows evenly via a to balance load across partitions. Vertical partitioning divides tables by columns, storing related attributes separately to reduce I/O for specific queries, though it complicates joins. Sharding extends horizontal partitioning across distributed servers, often using to minimize movement during resharding, enabling scalability in cloud environments. Key considerations in selecting storage structures include data volume, access patterns, and underlying hardware. High data volumes necessitate partitioning to avoid single-file bottlenecks, as unpartitioned files can exceed practical limits on individual storage devices. Access patterns guide choices: sequential patterns favor sequential or organizations for bulk reads, while random point queries suit hash structures; mismatched selections can degrade performance by orders of magnitude. Hardware differences, such as solid-state drives (SSDs) excelling in with low latency versus hard disk drives (HDDs) optimizing for sequential throughput due to mechanical seeks, influence structure selection—hashing benefits more from SSDs' uniform access times, while sequential files leverage HDD strengths. For instance, in an system managing inventory, a high-read/write table for product stock might employ hash partitioning to evenly distribute records across based on product IDs, ensuring balanced query loads and fault isolation without hotspots.

Designing Indexes and Access Methods

In database physical design, indexes serve as auxiliary structures that enhance query retrieval efficiency by providing quick access paths to stored in tables, building upon selected storage structures such as B-trees or hash tables. Access methods, in turn, define the algorithms used by the database (DBMS) to traverse these indexes or scan directly, optimizing operations like searches, joins, and aggregations. The design process involves evaluating query patterns, distribution, and hardware constraints to select appropriate index types and access strategies that balance retrieval speed with storage and update costs. Common index types include primary, secondary, clustered, non-clustered, , and full-text indexes, each suited to specific characteristics and query workloads. A primary index is defined on the table's , ordering records sequentially to support unique lookups and range scans with minimal overhead. Secondary indexes, by contrast, are built on non-key attributes to accelerate queries on frequently filtered columns, though they require additional storage as separate structures pointing to the primary . Clustered indexes physically reorder the table rows according to the index key, allowing efficient range queries since follows the index order directly; only one clustered index is typically permitted per table. Non-clustered indexes maintain a logical ordering separate from the physical table layout, enabling multiple such indexes but often incurring extra I/O for access via pointers. Bitmap indexes use bit vectors to represent the presence of values in low-cardinality columns, excelling in data warehousing for fast bitwise operations on aggregations and intersections. Full-text indexes, specialized for textual content, tokenize and store word positions across columns to support relevance-based searches like keyword matching or phrase queries. Access methods leverage these indexes to execute queries efficiently, with choices depending on data size, join conditions, and available . Sequential scans read the entire table or index in order, suitable for small tables or unindexed full-table operations where index overhead would not justify use. Index scans traverse only relevant portions of an index structure—such as branches for equality or range predicates—followed by row fetches, reducing I/O compared to full scans for selective queries. For joins, algorithms like nested loop joins iterate over the outer relation and probe the inner via index or for each , performing well with small result sets or indexed inner tables. Hash joins build in-memory hash tables on the join keys of one relation to probe with the other, offering constant-time lookups for equi-joins on larger datasets when memory suffices. Key design principles guide index creation to minimize I/O and CPU costs during query execution. Selectivity measures the of values in an indexed column, expressed as the ratio of distinct values to total rows; high selectivity (close to 1) enables precise filtering, making the index effective for point queries, while low selectivity may favor full scans. The clustering factor quantifies how well table rows align with the index order, ranging from low (ideal, few block jumps) to high (poor, many scattered I/Os); it influences the optimizer's cost estimates for index range scans. Covering indexes include all queried columns within the index itself, allowing the DBMS to satisfy the query from the index alone without accessing the base table, thus eliminating additional I/O for non-key . Despite these benefits, index design involves trade-offs between query acceleration and maintenance overhead. While indexes speed up reads by reducing scanned data volume—potentially cutting query times from linear to logarithmic complexity—they impose costs during inserts, updates, and deletes, as the DBMS must synchronize index entries, which can significantly increase write latency in multi-index scenarios. Over-indexing exacerbates storage bloat (indexes can consume a substantial amount of additional storage space) and fragmentation, while under-indexing leads to suboptimal scans; designers must analyze statistics to prune unused indexes. For instance, in a table with columns for ID, , last_name, and , creating a composite non-clustered index on (last_name, ) supports efficient lookups for queries like "SELECT * FROM customers WHERE last_name = 'Smith' AND LIKE 's%'", leveraging selectivity on the unique field and covering common projections to avoid table access. This reduces I/O for frequent searches while minimizing overhead if updates to these fields are infrequent.

Optimizing for Performance and Security

Optimizing the physical design of a database involves fine-tuning storage, access paths, and system configurations to balance efficiency, reliability, and . This process ensures that the database meets demands while safeguarding and confidentiality. Key adjustments include refining query execution plans, implementing caching layers, managing concurrent access through locking, enforcing security protocols like and role-based access, designing for backups and recovery, evaluating via core metrics, and adapting to environments with automated scaling. Performance tuning begins with query optimization, an iterative process that identifies high-load SQL statements and improves their execution plans to reduce response times and resource usage. For instance, tools such as Oracle's SQL Tuning Advisor or SQL Server's Tuning Advisor analyze statements for inefficiencies such as full table scans and recommend fixes like rewriting queries or updating statistics. Caching strategies further enhance by storing frequently accessed in , such as using buffer pools sized to about 75% of available instance to minimize disk I/O. mechanisms, including locking, prevent inconsistencies during multi-user access; databases employ exclusive and shared locks on resources like rows to allow concurrent reads while serializing writes. Row-level locking, in particular, provides finer than table-level locking, improving under high contention. Security integration into the physical design emphasizes access controls and to protect sensitive data. (RBAC) assigns permissions based on user roles, such as granting SELECT privileges only to analysts, which simplifies management and enforces least-privilege principles. Encryption at rest uses techniques like (TDE) to protect database files, while in transit employs TLS to secure data during transmission. Row-Level Security (RLS) further restricts visibility to authorized rows based on user context, often combined with column-level permissions for granular control. Backup and recovery designs incorporate redundancy to ensure data availability and minimal downtime. configurations, such as RAID-5, provide by striping data and parity across multiple disks, allowing recovery from single-drive failures without data loss. Replication strategies duplicate data across servers for , enabling in case of hardware issues. (PITR) facilitates restoring databases to a specific moment by replaying transaction logs from continuous backups, achieving precision within seconds and supporting retention up to 35 days in cloud environments. Performance is evaluated using key metrics like throughput, which measures operations processed per second (e.g., in OLTP workloads), latency (time from query submission to response), and (ability to handle increased loads without proportional degradation). Testing involves simulating workloads to benchmark these, identifying bottlenecks such as I/O limits that could reduce throughput by up to 50% if unaddressed. In modern cloud deployments, optimizations like auto-scaling in Amazon RDS adjust compute and storage resources dynamically based on metrics from Amazon CloudWatch, such as increasing during peaks to maintain low latency. This approach supports elastic scaling for variable workloads, reducing manual intervention while optimizing costs for provisioned throughput.

Advanced Topics

Handling Non-Relational Data

Database design principles traditionally rooted in relational models face limitations when handling non-relational , such as unstructured or semi-structured information that does not fit neatly into fixed or tables. databases address these by offering flexible, scalable alternatives optimized for specific data types and access patterns, adapting design processes to prioritize horizontal scaling, high ingestion rates, and schema flexibility over strict normalization. In such systems, traditional normalization techniques become less applicable, as is often embraced to enhance read performance by related data within single records. NoSQL databases are categorized into four primary types, each suited to distinct data structures and use cases, with emerging types like vector databases gaining prominence for AI applications. Key-value stores, like Redis, treat data as simple pairs where a unique key maps to a value, ideal for caching and session management due to their simplicity and low-latency retrieval. Document stores, such as MongoDB, organize data into flexible, JSON-like documents that can nest sub-documents, accommodating semi-structured data like user profiles or content articles. Column-family databases, exemplified by Cassandra, group data into dynamic columns within families, excelling in write-heavy workloads across distributed nodes for time-series or log data. Graph databases, like Neo4j, represent data as nodes, edges, and properties to model complex relationships, such as social networks or recommendation engines. Vector databases, such as Pinecone or Milvus, specialize in storing and querying high-dimensional vector embeddings for similarity searches, supporting machine learning tasks like semantic search and recommendation systems in AI-driven applications. Design approaches in diverge from relational norms by employing schema-on-read, where structure is imposed during query time rather than enforced at write, enabling rapid iteration on evolving data models. In contrast, schema-on-write validates structure upfront, akin to relational databases, but is less common in NoSQL to avoid bottlenecks in high-velocity environments. is the default strategy, intentionally duplicating data to minimize joins and support efficient reads in distributed setups. further adapts designs, allowing temporary inconsistencies across replicas that resolve over time, prioritizing availability over immediate synchronization in BASE (Basically Available, Soft state, ) models. NoSQL systems are particularly advantageous for , such as multimedia or log files; high-velocity ingestion in real-time ; and flexible schemas in applications like feeds, where post structures vary unpredictably. For instance, platforms handling benefit from document stores' ability to ingest diverse formats without predefined fields, scaling to millions of writes per second. Hybrid designs incorporate , a strategy that combines relational databases for transactional integrity with for specialized needs, such as using a alongside a relational one for relationship queries in . This approach, coined by Scott Leberknight and popularized by Martin Fowler, allows applications to select storage technologies best matched to data kinds, mitigating the limitations of a single model. Challenges arise in ensuring (Atomicity, Consistency, Isolation, ) properties within NoSQL's distributed architectures, where full ACID compliance can hinder . The , formulated by Eric Brewer, underscores these trade-offs: in partitioned networks, systems must choose between consistency (all nodes see the same data) and availability (every request receives a response), with partition tolerance assumed in distributed setups. For example, favors availability and partition tolerance (AP), achieving , while systems like offer tunable options closer to CP for stricter needs.

Incorporating Modern Design Practices

Modern database design increasingly integrates Agile and methodologies to support iterative development and rapid schema evolution. In Agile practices, database are refined incrementally through sprints, allowing teams to adapt to changing requirements without overhauling the entire structure. extends this by incorporating and () pipelines, which automate schema migrations, testing, and deployment to minimize downtime and errors during updates. For instance, tools within CI/CD frameworks enable versioned changes to be applied atomically across environments, ensuring consistency in production systems. Contemporary tools facilitate these processes by bridging application code and database structures. Object-relational mapping (ORM) frameworks, such as Hibernate, abstract database interactions into object-oriented code, enabling developers to schemas that evolve alongside application logic without manual SQL boilerplate. Modeling software like ER/Studio supports visual of logical and physical schemas, enforcing best practices such as normalization and naming conventions to ensure and maintainability. platforms, including Collibra and Alation, integrate metadata management and policy enforcement into the design phase, promoting compliance and from inception. Emerging trends in database design emphasize flexibility for distributed and data-intensive architectures. Data lakes enable the ingestion of raw, at scale, shifting design focus from rigid schemas to schema-on-read approaches that accommodate diverse sources, with data lakehouses evolving this by combining lake with features like transactions and governance for unified analytics. In architectures, the database-per-service pattern assigns dedicated databases to individual services, enhancing isolation, , and independent deployment while requiring careful inter-service data consistency mechanisms. AI-assisted design tools further advance this by providing automated indexing suggestions based on query patterns, optimizing performance proactively without extensive manual tuning. Best practices in modern design prioritize maintainability, adaptability, and environmental responsibility. version control, using tools like and , treats database changes as code commits, enabling rollback, branching, and collaborative reviews akin to . Designing for cloud portability involves selecting vendor-agnostic structures, such as standard SQL dialects and containerized deployments, to facilitate multi-cloud migrations and avoid lock-in. Sustainability considerations include energy-efficient storage choices, like solid-state drives over traditional hard disks, to reduce power consumption in large-scale deployments. Looking ahead as of 2025, integration promises predictive schema adjustments, where algorithms analyze usage trends to recommend or automate modifications, such as partitioning or , for optimal performance and resource use. These advancements, drawn from AI-driven database research, aim to make designs self-optimizing in dynamic environments.

References

  1. https://www.bu.edu/tech/files/2018/06/intro2DB20180611.pdf
  2. https://orc.library.atu.edu/context/atu_oer/article/1002/viewcontent/OER_DatabaseDesignImplementation_WeiruChen.pdf
  3. https://web.eecs.umich.edu/~teorey/lec.notes.pdf
  4. https://users.csc.calpoly.edu/~dekhtyar/366-Spring2025/lectures/lec01.366.pdf
  5. http://www.csc.villanova.edu/~enwafor/database/lectures/lecture_2.pdf
  6. https://eecs.csuohio.edu/~sschung/cis430/Fundamentals-of-Database-Systems-Pearson-2015-Ramez-Elmasri-Shamkant-B.-Navathe.pdf
  7. https://homepages.uc.edu/~chundusa/databasemethodology.html
  8. https://dl.acm.org/doi/10.1145/362384.362685
  9. https://cacm.acm.org/research/50-years-of-queries/
  10. https://orc.library.atu.edu/cgi/viewcontent.cgi?article=1002&context=atu_oer
  11. https://nvlpubs.nist.gov/nistpubs/Legacy/IR/nistir4987.pdf
  12. https://condor.depaul.edu/gandrus/240IT/accesspages/normalization3.htm
  13. https://dl.acm.org/doi/10.1145/2723372.2751519
  14. https://dl.acm.org/doi/10.1145/3714334.3714360
  15. https://www.cs.utexas.edu/~vijay/papers/hotstorage19-gdpr.pdf
  16. https://www.cerritos.edu/dwhitney/SitePages/CIS201/Lectures/IM-7ed-Chapter09.pdf
  17. https://scholarsbank.uoregon.edu/bitstreams/ccd1f3e8-6ecb-4d11-b86f-06cbf1c47ff4/download
  18. https://dl.acm.org/doi/10.1145/320434.320440
  19. https://tinman.cs.gsu.edu/~raj/4710/sp03/ch3.pdf
  20. https://www.khoury.northeastern.edu/home/kathleen/classes/cs3200/2-ERM2014.pdf
  21. https://opendsa.cs.vt.edu/ODSA/Books/Database/html/ERDComponents.html
  22. https://www.pmtutor.org/resources/guides/9a._Data_Modeling.pdf
  23. https://www.cse.cuhk.edu.hk/~taoyf/course/bmeg3120/notes/er.pdf
  24. https://opentextbc.ca/dbdesign01/chapter/chapter-8-entity-relationship-model/
  25. https://www.ibm.com/think/topics/database-schema
  26. https://aws.amazon.com/what-is/database-schema/
  27. https://www.lucidchart.com/pages/er-diagrams
  28. https://www.red-gate.com/blog/database-design-guide
  29. https://sparxsystems.com/resources/tutorials/uml/datamodel.html
  30. https://gitmind.com/er-diagram-example.html
  31. http://www.csc.villanova.edu/~enwafor/database/lectures/lecture_5.1.pdf
  32. https://opendsa.cs.vt.edu/ODSA/Books/Database/html/ERDMappingToRDD.html
  33. https://users.cms.caltech.edu/~donnie/dbcourse/intro0607/lectures/Lecture17.pdf
  34. http://swindsor.web.wesleyan.edu/class/Chapter6.html
  35. https://www.usna.edu/Users/cs/adina/teaching/it360/spring2011/slides/it360_Set3_RelationalModel.pdf
  36. https://codex.cs.yale.edu/avi/home-page/publication-dir/Journals/acm-survey-96a.pdf
  37. https://dl.acm.org/doi/10.1145/320557.320571
  38. https://pages.cs.wisc.edu/~zuyu/files/dbms3e.pdf
  39. https://people.vts.su.ac.rs/~peti/Baze%20podataka/Literatura/Elmasri-Navathe-Fundamentals%20of%20DB%20Systems-EbookDB.pdf
  40. https://www.cs.purdue.edu/homes/clifton/cs44800/Storage.pdf
  41. https://www.semanticscholar.org/paper/Indexed-Sequential-Access-Method-%28ISAM%29%253A-A-Review-Zaqout-Abbas/550b9073cc51d51ce6dd4a39fe1e2342c28b6097
  42. https://infolab.usc.edu/csci585/Spring2010/den_ar/indexing.pdf
  43. https://cs.brown.edu/courses/cs227/archives/2012/papers/partitioning/agrawal-2004.pdf
  44. https://arxiv.org/pdf/2404.04384
  45. https://docs.aws.amazon.com/wellarchitected/2022-03-31/framework/perf_right_database_solution_access_patterns.html
  46. https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/hdds-ssds-and-database-considerations/
  47. https://dl.acm.org/doi/10.1145/320083.320092
  48. https://www.researchgate.net/publication/333843847_A_Study_on_Indexes_and_Index_Structures
  49. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver17
  50. https://www.researchgate.net/publication/355717838_DATABASE_RECORDS_AND_INDEXING
  51. https://www.researchgate.net/profile/Adham-Saeed/publication/321481582_A_guideline_to_select_indexing_technology_for_Relational_Database_Management_Systems/links/5a23b3800f7e9b71dd0565a9/A-guideline-to-select-indexing-technology-for-Relational-Database-Management-Systems.pdf
  52. https://www.researchgate.net/publication/200084950_Bitmap_Indices_for_Data_Warehouses
  53. https://learn.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver17
  54. https://courses.cs.duke.edu/fall17/compsci516/Lectures/Lecture-11-QueryOptimization.pdf
  55. https://www.vldb.org/conf/1990/P186.PDF
  56. https://vladmihalcea.com/index-selectivity/
  57. https://docs.oracle.com/en/database/oracle/oracle-database/21/cncpt/indexes-and-index-organized-tables.html
  58. https://www.red-gate.com/simple-talk/databases/sql-server/learn/using-covering-indexes-to-improve-query-performance/
  59. https://aerospike.com/blog/database-index-selection/
  60. https://www.sqlrevisited.com/2023/09/10-examples-of-creating-index-in-sql.html
  61. https://www.mongodb.com/resources/basics/databases/nosql-explained
  62. https://www.altexsoft.com/blog/nosql-databases/
  63. https://harsh05.medium.com/diving-deeper-into-mongodb-normalization-denormalization-and-embedded-documents-531ef8d0add3
  64. https://www.mongodb.com/resources/basics/databases/types
  65. https://www.geeksforgeeks.org/dbms/types-of-nosql-databases/
  66. https://www.datacamp.com/blog/the-top-5-vector-databases
  67. https://www.upsolver.com/blog/manage-your-data-schema-on-read-vs-schema-on-write
  68. https://celerdata.com/glossary/schema-on-read-vs-schema-on-write
  69. https://highscalability.com/building-scalable-databases-denormalization-the-nosql-moveme/
  70. https://rivery.io/data-learning-center/database-types-guide/
  71. https://www.mongodb.com/resources/basics/databases/nosql-explained/advantages
  72. https://martinfowler.com/bliki/PolyglotPersistence.html
  73. https://www.gigaspaces.com/blog/acid-distributed-transactions
  74. https://www.mdpi.com/2079-9292/8/5/488
  75. https://www.oreilly.com/library/view/devops-for-databases/9781837637300/
  76. https://dl.acm.org/doi/abs/10.1002/smr.2720
  77. https://hibernate.org/orm/
  78. https://erstudio.com/blog/designing-a-database/
  79. https://www.collibra.com/products/data-governance
  80. https://www.alation.com/blog/data-governance-tools/
  81. https://www.sentinelone.com/cybersecurity-101/data-and-ai/data-lake-solutions/
  82. https://www.dataversity.net/articles/data-architecture-trends-in-2025/
  83. https://www.matrixnmedia.com/data-management-in-microservices-comparison-of-database-per-service-vs-shared-database/
  84. https://www.vldb.org/pvldb/vol18/p4924-chakkappen.pdf
  85. https://www.liquibase.com/resources/guides/database-version-control
  86. https://www.cockroachlabs.com/blog/flyway/
  87. https://docs.cloud.google.com/architecture/multi-cloud-database-management
  88. https://www.vldb.org/pvldb/vol18/p4064-bachras.pdf
  89. https://arxiv.org/pdf/2504.11259
  90. https://ieeexplore.ieee.org/iel8/6287639/10820123/11008610.pdf
Add your contribution
Related Hubs
Contribute something
User Avatar
No comments yet.