SQL BY VIVEK RAUT
## DATA
- Data is a collection of a distinct small unit of information.
- It can be used in a variety of forms like text, numbers, media, bytes, etc.
## DATABASE
- It is organized collection of data stored in a standardized format.
- Database Design general process…
Step 1: Define the Purpose of the Database (Requirement Analysis).
Step 2: Gather Data, organize in tables and Specify the Primary Keys.
Step 3: Create Relationships among Tables.
Step 4: Refine & Normalize the Design.
Step 5: Database Implementation
## RELATIONAL DATABASE
- A tabular database in which data is store in tables with relationships to the other tables
- A relational database is the most commonly used database. It contains several tables, and each table
has its primary key.
- Due to a collection of an organize Everything in a relational database is stored in the form of relations.
- The RDBMS database uses tables to store data. A table is a collection of related data entries and contains
rows and columns to store data.
- Each table represents some real-world objects such as person, place, or event about which information
is collected.
- The organized collection of data into a relational table is known as the logical view of the database.d set
of tables, data can be accessed easily in RDBMS.
## DBMS
- Database Management System (DBMS)
- It’s a software platform that hosts or defineIts a software platform that host or defines a database on a
hardware (computer system). It enables users to store, modify and extract information/data from the
database. Supports a query language (SQL).
## RDBMS
- Relational Database Management System (RDBMS) – It’s a database management system based on the
relational model.
## SERVER
- A computer, device or program that is dedicated to manage the network resources for carrying out some
specific tasks.
- The purpose of a server is to share data, resources and distribute works.
,## NORMALIZATION:
- Decompose larger, complex table into simpler and smaller ones
- Moves from lower normal forms to higher normal forms.
** Need for Normalization:
- In order to produce good database design
- To ensure all database operations to be efficiently performed
- Avoid any expensive DBMS operations
- Avoid unnecessary replication of information
## ATTRIBUTES
- These are characteristics of the entities. All the entities have some attributes or properties that give
them their identity.
## Types of Attributes
Simple Attribute
- Atomic values, which cannot be divided further.
- e.g. a students phone number is an atomic value of 10 digits.
Composite Attribute
- Made of more than one simple attribute.
- e.g. a students complete name may have first_name and last_name.
Derived Attribute
- Derived attributes are the attributes that do not exist in the physical database,but their values are
derived from other attributes present in the database.
- e.g. age can be derived from data_of_birth.
Single-Value Attribute
- Single-value attributes contain single value
- e.g. Social_Security_Number.
Multi-Value Attribute
- Multi-value attributes may contain more than one values.
- e.g. a person can have more than one phone number, email_address, etc.
## ENTITY-SET
Entity
- An entity can be a real-world object, a generalized class of people, places or things for which data is
collected, stored and maintained.
- E.g. Employee, Customer, Vehicle, House etc.
, ## Keys
- Key is an attribute or collection of attributes that uniquely identifies an entity among entity set.
- A field or set of fields in the records that can be used to identify the record.
- e.g. the email_id of a student makes him/her identifiable among students.
Super Key
− A set of attributes (one or more) that collectively identifies an entity in an entity set
Candidate Key
− A minimal super key is called a candidate key. An entity set may have more than one candidate key.
− Primary Key
− A primary key is one of the candidate keys chosen by the database designer to uniquely identify the
entity set.
## SQL CONSTRAINT
- SQL constraints are used to specify rules for the data in a table.
- Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and
reliability of the data in the table. If there is any violation between the constraint and the data action,
the action is aborted.
- Constraints can be column level or table level. Column level constraints apply to a column, and
table level constraints apply to the whole table.
- The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE.
Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified.
IDENTITY - Value in the field would be auto generated by the system when we insert data in
to the table.
## DATA
- Data is a collection of a distinct small unit of information.
- It can be used in a variety of forms like text, numbers, media, bytes, etc.
## DATABASE
- It is organized collection of data stored in a standardized format.
- Database Design general process…
Step 1: Define the Purpose of the Database (Requirement Analysis).
Step 2: Gather Data, organize in tables and Specify the Primary Keys.
Step 3: Create Relationships among Tables.
Step 4: Refine & Normalize the Design.
Step 5: Database Implementation
## RELATIONAL DATABASE
- A tabular database in which data is store in tables with relationships to the other tables
- A relational database is the most commonly used database. It contains several tables, and each table
has its primary key.
- Due to a collection of an organize Everything in a relational database is stored in the form of relations.
- The RDBMS database uses tables to store data. A table is a collection of related data entries and contains
rows and columns to store data.
- Each table represents some real-world objects such as person, place, or event about which information
is collected.
- The organized collection of data into a relational table is known as the logical view of the database.d set
of tables, data can be accessed easily in RDBMS.
## DBMS
- Database Management System (DBMS)
- It’s a software platform that hosts or defineIts a software platform that host or defines a database on a
hardware (computer system). It enables users to store, modify and extract information/data from the
database. Supports a query language (SQL).
## RDBMS
- Relational Database Management System (RDBMS) – It’s a database management system based on the
relational model.
## SERVER
- A computer, device or program that is dedicated to manage the network resources for carrying out some
specific tasks.
- The purpose of a server is to share data, resources and distribute works.
,## NORMALIZATION:
- Decompose larger, complex table into simpler and smaller ones
- Moves from lower normal forms to higher normal forms.
** Need for Normalization:
- In order to produce good database design
- To ensure all database operations to be efficiently performed
- Avoid any expensive DBMS operations
- Avoid unnecessary replication of information
## ATTRIBUTES
- These are characteristics of the entities. All the entities have some attributes or properties that give
them their identity.
## Types of Attributes
Simple Attribute
- Atomic values, which cannot be divided further.
- e.g. a students phone number is an atomic value of 10 digits.
Composite Attribute
- Made of more than one simple attribute.
- e.g. a students complete name may have first_name and last_name.
Derived Attribute
- Derived attributes are the attributes that do not exist in the physical database,but their values are
derived from other attributes present in the database.
- e.g. age can be derived from data_of_birth.
Single-Value Attribute
- Single-value attributes contain single value
- e.g. Social_Security_Number.
Multi-Value Attribute
- Multi-value attributes may contain more than one values.
- e.g. a person can have more than one phone number, email_address, etc.
## ENTITY-SET
Entity
- An entity can be a real-world object, a generalized class of people, places or things for which data is
collected, stored and maintained.
- E.g. Employee, Customer, Vehicle, House etc.
, ## Keys
- Key is an attribute or collection of attributes that uniquely identifies an entity among entity set.
- A field or set of fields in the records that can be used to identify the record.
- e.g. the email_id of a student makes him/her identifiable among students.
Super Key
− A set of attributes (one or more) that collectively identifies an entity in an entity set
Candidate Key
− A minimal super key is called a candidate key. An entity set may have more than one candidate key.
− Primary Key
− A primary key is one of the candidate keys chosen by the database designer to uniquely identify the
entity set.
## SQL CONSTRAINT
- SQL constraints are used to specify rules for the data in a table.
- Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and
reliability of the data in the table. If there is any violation between the constraint and the data action,
the action is aborted.
- Constraints can be column level or table level. Column level constraints apply to a column, and
table level constraints apply to the whole table.
- The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE.
Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified.
IDENTITY - Value in the field would be auto generated by the system when we insert data in
to the table.
