Database Design Fundamentals

Introduction

A college needs to record students, courses, and enrollments. Placing everything in one table may seem convenient, but student details repeat for every enrollment, course information appears in several rows, and routine changes can create inconsistencies.

Database design determines what information a system stores, how that information is organized, and which rules keep it valid. For a relational database, this means developing tables, relationships, keys, and constraints that support the application’s requirements.

Understanding Requirements and Business Rules

Database design begins with understanding the activities the system must support. Required questions and operations help reveal which facts must be stored before tables are defined.

For a college enrollment system, useful questions include:

• Student records: Which details identify a student, and which details may change? Two students may share a name, so names alone cannot reliably distinguish them.

• Course enrollment: Can a student take several courses? Can the same student take the same course again in another term?

• Grade recording: Does a grade belong to a student generally, or to a particular enrollment? The answer determines where Grade belongs.

• Required reports: Does the system need to show which students are enrolled in each course, keep past enrollment records, or show enrollments that do not have grades yet? Each requirement affects the information the design needs.

A business rule states a restriction or condition the system must respect. For example, “Each enrollment belongs to exactly one student” establishes a mandatory association.

Record these rules explicitly. Sample records show what currently exists; they do not establish everything the database must permit.

Conceptual Logical and Physical Design

Database design is commonly considered at three levels. Each level answers a different question, from understanding the domain to implementing its storage.

The following diagram places these levels within the wider design process.

Conceptual Design

Conceptual design identifies entities, attributes, and relationships without committing to SQL implementation details.

For the college example, the main entities are STUDENT, COURSE, and ENROLLMENT. An ER diagram can express how these concepts connect and whether participation is optional or compulsory.

Logical Design

Logical design translates the conceptual model into a relational schema. It specifies tables, columns, candidate keys, primary keys, foreign keys, and constraints.

A schema describes the database structure. The rows stored at a particular moment form its current instance.

Physical Design

Physical design addresses implementation choices such as indexes, storage organization, and access performance. These decisions depend on the selected database system and expected workload.

The three levels separate domain modeling, relational structure, and implementation concerns. Design remains iterative: validation can reveal gaps at any stage.

Organizing Tables Keys and Relationships

In a relational design, each table represents a clearly defined subject or association. A row records one occurrence, and columns describe its attributes.

A starting schema for the college example is:

Choosing Keys

A candidate key is a minimal set of attributes that uniquely identifies a row. The selected candidate key becomes the primary key. A primary key may contain one column or several columns together.

Choose identifiers that are unique, present, and preferably stable. A student’s name is unsuitable because different students can share it and names can change.

A foreign key references a key in another table or sometimes the same table to enforce a connection. In ENROLLMENT, StudentID references STUDENT and CourseID references COURSE.

For this simplified example, assume a student can enroll in each course only once. StudentID and CourseID together then identify an enrollment. Supporting repeat attempts requires distinguishing the term, course offering, or attempt.

Defining Relationship Rules

• Cardinality: Specifies the maximum number of associations. A student can have many enrollments, while each enrollment refers to one student.

• Participation: Determines whether an association is required. A student may exist before enrolling, but an enrollment must reference a student.

• Relationship attributes: Describe a particular association. Grade belongs to ENROLLMENT because it concerns one student taking one course.

Reducing Redundancy Through Normalization

Normalization examines dependencies among attributes to identify and reduce problematic redundancy. It complements ER modeling by checking the structure of individual relations.

Consider storing student names, course titles, and grades together. Asha’s name repeats for each course she takes, and the title Databases repeats for each enrolled student.

The following image separates these facts while preserving every enrollment and grade.

The dependencies explain the separation:

• StudentID determines StudentName: A student’s name belongs with the student record.

• CourseID determines CourseTitle: A course’s title belongs with the course record.

• StudentID and CourseID determine Grade: Under the stated enrollment rule, the grade belongs with the student–course association.

Keeping these facts mixed creates three familiar problems:

• Update anomaly: Changing a course title requires updating several rows. Missing one update leaves conflicting titles.

• Insertion anomaly: Recording a new course may require inventing an enrollment if course information exists only in enrollment rows.

• Deletion anomaly: Removing a course’s final enrollment may also remove the only stored information about that course.

Normalization uses normal forms, including 1NF, 2NF, and 3NF, to examine repeating structures and dependencies. A decomposition should preserve the original information when its tables are joined; preserving enforceable dependencies is also an important goal.

Normalization improves structural consistency. It does not automatically make every query faster.

Enforcing Rules and Validating the Design

A design becomes dependable when its important rules are implemented and tested.

Integrity Constraints

• Entity integrity: Primary keys uniquely identify rows and cannot contain null values. This gives each stored record a dependable identity.

• Referential integrity: Foreign keys prevent references to nonexistent records. An enrollment cannot reference a student who is absent from STUDENT.

• Domain restrictions: Data types and checks restrict permitted values. A numeric credit value might be required to fall within an agreed range.

• Mandatory and unique values: NOT NULL requires a value, while UNIQUE prevents prohibited duplicates. These constraints should reflect actual requirements rather than assumptions.

Keys and relationships help preserve accuracy, but the designer must first identify the correct information and rules.

Practical Validation

Test the design using representative operations:

• Valid operations: Add a student without an enrollment, register a student for two courses, and update a course title.

• Invalid operations: Attempt a duplicate enrollment, reference a nonexistent course, or store a prohibited grade.

• Required queries: Confirm that student schedules, course rosters, and grade reports can be produced correctly.

• Historical changes: Check what happens when names, course titles, or enrollment statuses change. Decide which past values must remain available.

Implementation also needs suitable permissions, transaction boundaries, and recovery arrangements. Indexes should follow measured access needs because they consume storage and add maintenance work.

Document the decisions and revisit them when requirements change.

Summary

Database design organizes information around application requirements and business rules. Conceptual design identifies entities and relationships, logical design defines tables and constraints, and physical design addresses implementation choices. Keys preserve identity and connections, normalization reduces problematic redundancy, and validation checks that the database supports required operations while rejecting invalid data.