Explain what is a database schema and mention one example

Database

It is used to store data in tables. Tables, known as relations, are divided into columns and rows. Columns show the information stored while rows hold the actual information.

A database is a collection of tables which are related to other tables through common columns, hence known as relational database.

Schema

It refers to the blueprint of the database. A schema shows all the tables, their relations with other tables and the columns which relate or link two or more tables.

Every table has a primary key column along with other columns. The primary key (pk) when used in other table of the same database, becomes foreign key (fk) in the other table. The pk / fk link tables in every database.

Schema can be logical and physical.

Logical schema

It defines the tables with their attributes (columns).

Physical schema

It is the actual implementation of the database which also includes structures like indexes, hash tables and others.

Example

Consider a scenario where a store needs to track data for a business. It keeps track of all its customers, products and orders. An order can contain only one product purchased by one customer.

Relations / Tables

Customers (custID, custName, custAddress, custPhone)

Products (prodID, prodName, prodDescription, prodUnitPrice, prodQOH, prodThreshold)

Orders (oID, oDate, custID, discount, total)

Foreign key custID references Customers (custID)

OrderLine (ID, oID, prodID, units, subtotal)

Foreign key oID references Orders (oID)

Foreign key prodID references Products (prodID)

Primary key of every table is shown as underlined.

Foreign key of every table is shown in italics.

The above is called the relational schema of the given scenario. It consists of all the tables with their columns or attributes. The tables are related through pk / fk.