database schema with example

Database Schema

A database schema is a structure that represents the logical storage of the data in a database. It represents the organization of data and provides information about the relationships between the tables in a given database. In this topic, we will understand more about database schema and its types. Before understanding database schema, lets first understand what a Database is.

What is Database?

A database is a place to store information. It can store the simplest data, such as a list of people as well as the most complex data. The database stores the information in a well-structured format.

What is Database Schema?

• A database schema is the logical representation of a database, which shows how the data is stored logically in the entire database. It contains list of attributes and instruction that informs the database engine that how the data is organized and how the elements are related to each other.
• A database schema contains schema objects that may include tables, fields, packages, views, relationships, primary key, foreign key,
• In actual, the data is physically stored in files that may be in unstructured form, but to retrieve it and use it, we need to put it in a structured form. To do this, a database schema is used. It provides knowledge about how the data is organized in a database and how it is associated with other data.
• The schema does not physically contain the data itself; instead, it gives information about the shape of data and how it can be related to other tables or models.
• A database schema object includes the following:
  • Consistent formatting for all data entries.
  • Database objects and unique keys for all data entries.
  • Tables with multiple columns, and each column contains its name and datatype.
• The complexity & the size of the schema vary as per the size of the project. It helps developers to easily manage and structure the database before coding it.
• The given diagram is an example of a database schema. It contains three tables, their data types. This also represents the relationships between the tables and primary keys as well as foreign keys.

SQL Command For this database

CREATE table "BRANCH" (

    "BRANCH_NAME" VARCHAR2(4000) NOT NULL primary key,

    "BRANCH_CITY" VARCHAR2(4000),

    "ASSETS"      VARCHAR2(4000)

)

 

CREATE table "customer" (

    "customer_name" VARCHAR2(4000) NOT NULL primary key,

    "customer_street" VARCHAR2(4000),

    "customer_city"      VARCHAR2(4000)

)

 

CREATE TABLE  "LOAN"

   (           "LOAN_NUMBER" NUMBER NOT NULL ENABLE,

                "BRANCH_NAME" VARCHAR2(4000) NOT NULL ,

                "AMOUNT" NUMBER,

                 CONSTRAINT "LOAN_PK" PRIMARY KEY ("LOAN_NUMBER") ,

                 CONSTRAINT "LOAN_FK" FOREIGN KEY ("BRANCH_NAME")

                  REFERENCES  "BRANCH" ("BRANCH_NAME")

   )

CREATE TABLE  "ACCOUNT"

   (           "ACCOUNT_NUMBER" NUMBER NOT NULL ,

                "BRANCH_NAME" VARCHAR2(4000) NOT NULL ,

                "BALANCE" NUMBER,

                 CONSTRAINT "ACCOUNT_PK" PRIMARY KEY ("ACCOUNT_NUMBER") ,

                 CONSTRAINT "ACCOUNT_FK" FOREIGN KEY ("BRANCH_NAME")

                  REFERENCES  "BRANCH" ("BRANCH_NAME")

   )

CREATE TABLE  "BORROWER"

   (           "CUSTOMER_NAME" VARCHAR2(4000) NOT NULL ,

                "LOAN_NUMBER" NUMBER NOT NULL ,

                 CONSTRAINT "BORROWER_FK" FOREIGN KEY ("CUSTOMER_NAME")

                  REFERENCES  "customer" ("customer_name") ,

                 CONSTRAINT "BORROWER_FK2" FOREIGN KEY ("LOAN_NUMBER")

                  REFERENCES  "LOAN" ("LOAN_NUMBER")

   )

 

CREATE TABLE  "DEPOSITOR"

   (           "CUSTOMER_NAME" VARCHAR2(4000) NOT NULL,

                "ACCOUNT_NUMBER" NUMBER NOT NULL,

                 CONSTRAINT "DEPOSITOR_FK" FOREIGN KEY ("CUSTOMER_NAME")

                  REFERENCES  "customer" ("customer_name"),

                 CONSTRAINT "DEPOSITOR_FK2" FOREIGN KEY ("ACCOUNT_NUMBER")

                  REFERENCES  "ACCOUNT" ("ACCOUNT_NUMBER")

   )