Relational Schema Design Google Professional Data Engineer GCP

Before delving into designing a schema, lets look at the properties that makes a good Database .

ACID properties

• Atomic : Transaction is a single unit, either succeeds or fails completely.
• Consistent : DB is consistent before and after the transaction.
• Isolated : reading and writing to multiple tables at the same time.
• Durability : once transaction is committed, will remain committed even in a system failure.

For a good Relational DB schema, following are important

• Relationship between entities
• Normalisation Form

Relationship Types

One to One –

• one record is associated with one and only one record in another table.
• Both tables have same Primary Key to identify the record.
• Get complete data from both the tables by a join on this primary key .

One to Many –

• Each record in first Table have many linked records in second Table, second table rows have only one corresponding record in first Table.
• For data, put the primary key of first table as foreign key of the second table

Many To Many

• Multiple records in a table are linked to multiple records of another table.
• It is usually not allowed in RDBMS

Normalization

It reduces redundancy from a relation or set of relations in DB .

• 1 NF — There has to be a key that uniquely identifies a record/ row
• 2 NF — should be in 1 NF and every non-key column should be fully dependent on the primary key
• 3 NF — it should be in 2-NF and Non key columns are independent of each other