A Database is an organized collection of data, typically stored in electronic format.

Traditionally, Databases are organized by records (rows) and fields (columns) stored in tables which are stored in the Database files.

SQL stands for Structured Query Language.

A Database Management System (DBMS) is a collection of applications used to perform administrative tasks on Databases and used to interact with data stored in Databases.

A Relational Database Management System (RDBMS) is a software system designed to allow the definition, creation, querying and updating of data stored in relational Databases.

Microsoft SQL Server and MySQL are examples of relational Databases.

Databases are stored on Database servers which are dedicated physical or virtual servers that host the Database files and provide high-level performance for users who are accessing the data.

Database server hosts the DBMS system and one or more instances of SQL Server.

SQL Server Management Studio (SSMS) is the GUI (graphical user interface) used to manage SQL Server, its Databases, and the content contained within the Databases.

Normalization

Normalization is the process of organizing data in a Database that include establishing relationships between the tables.

• First normal form: No repeating groups
• Second normal form: Eliminate redundant data
• Third normal form: Eliminate data not dependent on the key
• Fourth normal form: Isolate Independent multiple relationships
• Fifth normal form: Isolate Semantically related multiple relationships

The first normal form means the data is in an entity format, which means the following conditions have been met:

• Eliminate repeating groups in individual tables
• Create separate table for each set of related data
• Identify each set of related data with primary key
• Don't use multiple fields in a single table to store similar data

The second normal form ensures each attribute describes the entity:

• Create separate tables for sets of values that apply to multiple records
• Relates these tables with a foreign key

Referential Integrity

Referential Integrity is a Database concept used to ensure that relationships between your Database tables remains synchronized during data modifications.

Referential Integrity is used to ensure the data contained in the Database remains consistent.

Tools that can be used to help with referential integrity include:

• Primary key constraint
• Foreign key constraint
• Unique constraint
• Unique Indexes
• Triggers

Primary key constraint: An attribute or set of attributes used to uniquely identify each row.

Foreign key constraint: A column or combination of columns used to establish a link between data in two tables.

Unique constraint: Allows you to enforce uniqueness in columns other than the primary key.

Unique Index: Ensures the index key contains no duplicate values and that every row in the table or view is unique in some way

Triggers: Complex T-SQL statements used to provide data integrity when table data modified.

Any of these constraints can be created as a composite key which is an index or constraint created using more than one column.

Data Types, Data Objects and DDL Statements

Data type is an attribute that specifies the type of data that an object in the Database can hold as well as the number of bytes (memory) that can be stored in the object.

Data type categories:

• Exact numerics
• Appropriate numerics
• Date and time
• Character string
• Unicode character string
• Binary string
• Large valued data types
• Large Object data types

Datatypes

• Unicode data types provide storage of international characters, such as Chinese
• Implicit data types is the automatic conversion of on data type to another by SQL Server expression evaluator to complete an operation like a comparison of two values, some data types conversions are not supported
• Explicit data type conversions require the use of the CONVERT or CAST function to convert data from one data type to another before an operation like a comparison can be completed

Dabase Objects

Database Objects is the data structure used to store or reference data, which would include tables, stored procedures, views, User-Defined functions and more.

• A table is a collection of rows and columns that is used to organize information about a single topic.
• A view is a virtual table consisting of different columns from one or more tables, stored as a query object that obtains its data from one or more tables.
• A stored procedure is a group of Transact-SQL statements that have been compiled and saved so it can run several times.
• User - Defined functions are routines that take zero or more parameters, completes an operation and returns the result of that operation.

Naming conventions for your objects is considered good practice for creating and maintaining your data base here are two common methods.

• PascalCase - The first letter of letter of the named object and subsequent concatenated word is capitalized, eg EmployeeTable.
• camelCase – The the first letter of the identifier is lowercase and the first letter of each subsequent concatenated word is capitalized, eg. employeeTable.

DDL Statements

DDL statements (Data definition language) are statements that alter the structure of the Database schema usually by creating, altering and dropping objects such as tables, views, etc.

• CREATE - is used to create new entities in SQL Server including some of the most common entities such as, Database, table, procedure, user, view, trigger, Index.
• ALTER - used to modify existing objects in SQL Server including tables, users, views, the Schema, to the Database itself.
• DROP - used to delete existing objects from the SQL Server including tables, users, views to the Database itself.