Database.Schema.Table

Most of the database beginners used to get confused with database, schema and table.

Database:  

A database maintains information about various types of objects (inventory), events (transactions), people (employees), and places (warehouses).  It is an integrated collection of logically related
data elements. Rows are also called records and columns are also called fields. 
Primary Keys are a column that identifies a unique row in a table. In the table DimCustomer given below, CustomerKey is Primary Key. It can act as a foreign key in different table. CustomerKey acts as a foreign key in FactInternetSales table.  A relational database carries data in the form of tables and uses foreign keys to represent relationships

Eg:

Create Database

Method 1

• Open Microsoft SQL Management Studio.
• Expand the Microsoft SQL Server node where you want to create the database.
• Right click the Databases node and then click New Database.
• Type the database name in the dialog box, for example, ADMINISTRATION, and then click OK

Method 2

Write query

CREATE DATABASE databasename;

Tip: Make sure you have admin privilege before creating any database. Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;

Schema

A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It is a separate entity within the database. It removed the tight coupling of database objects and owners to improve the security administration of database objects. They logically group tables, procedures, views together. For Example, all customer-related objects can be included in the customer schema, all registration related information of students can be included in Registration schema etc. Also, permissions can be provided to just one schema, so that users can only see the schema they have access to and nothing else.

Create a schema

Method 1

1. In Object Explorer, expand the Databases folder.
2. Expand the database in which to create the new database schema.
3. Right-click the Security folder, point to New, and select Schema.
4. In the Schema - New dialog box, on the General page, enter a name for the new schema in the Schema name box.

Method 2

Write query inside database

CREATE SCHEMA schemaname AUTHORIZATION username

Eg 1: create a schema called STUDENT with the user STUDENT as the owner.

CREATE SCHEMA STUDENT AUTHORIZATION STUDENT

Eg 2: Create a schema EMPLOYEE that has details of employee table. Give authority on the table to user JIS. (Ran query one by one. )

CREATE SCHEMA EMPLOYEE;

CREATE TABLE EMP (EMPNO  SMALLINT NOT NULL,
                        ENAME    VARCHAR(40),
                        EPHONE INTEGER);
 GRANT ALL ON EMP TO JIS;

Table

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

ADMINISTRATION.EMPLOYEE.EMP => Database.Schema.Table