RDBMS Table Schema in High Level Overview

What’s table in RDBMS?

Table is basically a collection of related data entries and it consists of numerous columns and rows.

Definition of the keys in the table

The main terminology when designing RDBMS database schema key are Primary Key, and Foreign Key. In this part I also introduce Composite Key, Super Key and Candidate Key because I’ve saw some articles using these terms and it’s helpful to let us understanding the concept of RDBMS.

Primary Key

• Primary key must be unique in the table.
• Identifier to identify the unique record in the table.
• Not every table has the primary key.
• The primary key can be define by composition of the column attributes.

Foreign Key

• Foreign keys not need to be unique in the table.
• New added foreign key in record must be existed from the table define it as primary key.
• The column of the referencing primary key must have the unique value table in its table.

Composite Key

• The combination of the column attributes to identify each row in the table.
• Composite key can be the primary key.

Super Key and Candidate Key

• Super Key: The super set of all combinations of the column attributes has the capability to identify the whole table.
• Candidate Key: The subset of the Super Key has no redundant attributes and is a minimal tuple representation.

Entity-Relationship Design

ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationship for a specified system.

Entity

• The unit of the object can be described by the column attributes.
• Weak Entity: The entity is dependent on another entity and doesn’t contain the key attribute it owns.

Attribute

• Key Attribute: The key attribute is used to represent the main characteristics of an entity and represents a primary key.
• Composite Attribute: An attribute that composed of many other attributes is known as a composite attribute.

Relation

The relationship is used to describe the relation between the entities.

• ER diagram

Best Practices for Database Schema Design

Naming Conventions

• Table Names:

  • Use singular nouns rather than plural nouns (i.e. use StudentName instead of StudentNames)
  • Remove unnecessary wordings from table names (for example, Department instead of DepartmentList, TableDepartments, etc).

• Security:

  • For sensitive data, such as personally identifiable information (PII) and passwords, use encryption.

• Normalization:

  • Ensure independent entities and relationships are not group together in the same table.
  • Over-normalization and under-normalization can both lead to poor performance.

• Avoid Nulls:

  • Since attributes with null values cannot form primary keys.

Transaction

A transaction is a series of action for accessing the content of database

• commit: Save the work done permanently.
• rollback: Is used to undo the work which was done.

ACID properties

• Atomicity: If any operation is performed on the data, either should be executed completely or shouldn’t be executed at all. The atomicity is important for the system like transactions in the bank system.

• Consistency: Database transaction must change affected data only in allowed way. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.

• Isolation: The trade off between the wrong data written by the concurrent transaction and operation performance by deciding the isolation level. In the case of transactions, when two or more transactions occur simultaneously, the consistency should remain maintained.

• Durability: The durability of the data should be so perfect that even if the system fails or leads to a crash, the database still survives.

Reference

[1] 5 Database Design Schema Example: Critical Practices & Designs

[2] ER Model

[3] Composite Key

[4] Candidate Key in DBMS