Lesson 1 - Understanding Database Concepts
SSMS
SQL Server Management Studio (SSMS) is the primary tool to manage a server and its databases using a graphical interface.
Create DB in SSMS GUI
Scenario 1-4 1. Go to Object Explorer in the SSMS Gui 2. Right click on Databases folder icon 3. Choose "New Database" from the shortcut menu that appears 4. Fill out the "New Database" form that will appear.
Second normalization form
Second normalization form (2NF): Eliminate redundant data - each column / attribute most be dependent on all the primary keys
How do relational databases differ from flat-type databases and hierarchical databases?
[Page 4]
Flat-type database
plain-text formats (.csv, .xls). Because their purpose is to hold one record per line, they make access, performance, and queries very quick. [Page 3]
2 categories of db objects
storage and programmability
2 categories of database objects
storage and programmability [Page 7]
View objects
Are used for displaying views of tables. Views are like virtual tables.
INSERT
DML - 1 of 5 core statements Adds one or more new rows to a table or a view in SQL Server. [Page 12] INSERT INTO table_name (column1, column2, column3, ...)VALUES (value1, value2, value3, ...); If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows: INSERT INTO table_nameVALUES (value1, value2, value3, ...); [https://www.w3schools.com/sql/sql_insert.asp]
UPDATE
DML - 1 of 5 core statements Changes existing data in one or more columns in a table or view. [Page 12] UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; [https://www.w3schools.com/sql/sql_update.asp]
MERGE
DML - 1 of 5 core statements Performs insert, update, or delete operations on a target table based on the results of a join with a source table. [Page 12] -------- use MERGE to do the INSERT, UPDATE or DELETE with one statement Merge into sales1 as tab1 using (select * from Sales2) as tab2 on tab1.PersonID=tab2.PersonID when matched then update set tab1.lastname=tab2.lastname, tab1.FirstName=tab2.FirstName, tab1.Address=tab2.Address, tab1.Amount=tab2.Amount, tab1.Payment_Mode=tab2.Payment_Mode when not matched then insert values (tab2.PersonID,tab2.LastName, tab2.FirstName,tab2.Address,t ab2.Amount,tab2.payment_mode); [https://www.mssqltips.com/sqlservertip/5373/sql-server-merge-statement-usage-and-examples/]
DELETE
DML - 1 of 5 core statements Removes rows from a table or view. [Page 12] DELETE FROM table_name WHERE condition; [https://www.w3schools.com/sql/sql_delete.asp]
SELECT
DML - 1 of 5 core statements SELECT: Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server. SELECT column1, column2 .... FROM table_name WHERE condition ; [Page 12]
DDL
Data Definition Language is a subset of the Transact-SQL language; it deals with creating database objects like tables, constraints, views and stored procedures. USE, CREATE, DROP, ALTER, DELETE, TRUNCATE [Page 13]
The foreign key constraint is a(n) ________________________ identifier.
Relationship
Primary key
The primary key constraint uniquely identifies each record in a database table. The primary key must contain unique values and it cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key. [Page 8]
Third normalization form
Third normalization form (3NF): Eliminate columns not dependent on key
flat-type database
two-dimensional tables consisting of rows and columns. most commonly used in plain -text formats. purpose is to hold one record per line.
3 types MS SQL Svr files
.mdf, .ldf & .ndf .mdf - the first files created in a database and can contain user-defined objects, such as tables and views, as well as system tables that SQL Server requires for keeping track of the database. .ndf - secondary data files that are stored on separate disk for extra storage space. .ldf - transaction log files that track all the changes in the db. It can be used to recreate the db back to rights.
Types of column constraints
6 types of column constraints. Unique constraint, Check constraint, Default constraint, Not Null constraint, Primary key & Foreign key [Page 8]
Check constraint
A check constraint allows the administrator to limit the types of data a user can insert into the database. [Page 8]
db
A database (db) is an organized collection of data, typically stored in electronic format. It allows you to input, organize, and retrieve data quickly. [Page 2]quer
Default constraint
A default constraint is used to insert a default value into a column. If no other value is specified, the default value will be added to all new records. [Page 8]
Foreign Key constraint
A foreign key constraint in one table points to a primary key in another table. [Page 8] A foreign key constraint can define entity relationships on a continual basis. [Page 17] Can contain null values. This is bad standard practice. [Page 9] Foreign keys can reference columns in the same table. This is known as self-referencing keys. In query it's a self-join. [Page 9]
hierarchical
A hierarchical database is similar to a tree structure (such as a File Folder dir). Each parent table can have multiple children, but each child table can have only one parent.
Hierarchical database
A hierarchical database is similar to the tree structure of a file folder directory. In this database, each "parent" table can have multiple "children," but each child can have only one parent.
Not Null constraint
A not null constraint ensures that data is entered into a cell. In other words, the cell cannot be blank. It also means that you cannot insert a new record or update a record without adding a value to this field. [Page 8]
Relational Database
A relational database is similar to a hierarchical database in that data is stored in tables. Unlike in hierarchical databases, however, a table in a relational database can have multiple parents. [Page 4]
relational database
A relational database is similar to a hierarchical database in that data is stored in tables and any new information is automatically added to the table without the need to reorganize the table itself. Unlike tables in a hierarchical database, however, a table in a relational database can have multiple parents.
System Views
Belong to sys schema
CREATE TABLE
DDL CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ....); CREATE TABLE suppliers ( supplier_id int NOT NULL, supplier_name char(50) NOT NULL, contact_name char(50) ); [Page 14]
DELETE
DDL Remove rows from a table but does not free the space used by those rows removed. [Page 13] DELETE FROM table_name WHERE condition; Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted! [https://www.w3schools.com/sql/sql_delete.asp]
CREATE PROCEDURE
DDL - 1 of 6 core statements A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. Stored Proc are optimized for performance because they are already compiled. CREATE PROCEDURE procedure_name AS sql_statement GO; EXEC procedure_name; [https://www.w3schools.com/sql/sql_stored_procedures.asp]
ALTER
DDL - 1 of 6 core statements ALTER: Changes an existing object. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. [Page 13] ALTER TABLE table_name ADD column_name datatype; ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE table_name ALTER COLUMN column_name datatype; [https://www.w3schools.com/sql/sql_alter.asp]
CREATE
DDL - 1 of 6 core statements CREATE: Creates a SQL Server database object (table, view, or stored procedure). [Page 13 , ]
DROP
DDL - 1 of 6 core statements DROP: Removes an object from the database. [Page 13] The DROP TABLE statement is used to drop an existing table in a database. DROP TABLE table_name; [https://www.w3schools.com/sql/sql_drop_table.asp] The DROP DATABASE statement is used to drop an existing SQL database. DROP DATABASE databasename; [https://www.w3schools.com/sql/sql_drop_db.asp]
TRUNCATE
DDL - 1 of 6 core statements TRUNCATE: Removes rows from a table and frees the space used by those rows. [Page 13] TRUNCATE TABLE table_name; [https://www.w3schools.com/sql/sql_drop_table.asp]
USE
DDL - 1 of 6 core statements USE: Changes the database context. USE AdventureWorks; [Page 13]
CREATE VIEW
DDL [Page 12] In SQL, a view is a virtual table based on the result-set of an SQL statement. CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; [https://www.w3schools.com/sql/sql_view.asp]
DML
Data Manipulation Language is the language element that allows you to use the core statements SELECT, INSERT, UPDATE, DELETE, and MERGE to manipulate data in any SQL Server tables. [Page 12]
Database Servers
Databases are often found on database servers so that they can be accessed by multiple users and provide a high level of performance. One popular database server is Microsoft SQL Server. [Page 2]
First normalization form
First normalization form (1NF): Eliminate repeating groups - no dup recs. Create Primary Key. - no multiple values in a column/ attribute - one data type for a column / attribute
3 types of databases
Flat-type, hierarchial and relational
Self-referencing
Foreign keys can reference columns in the same table. This is known as self-referencing keys. In query it's a self-join. [Page 9]
index
Is a data structure that improves performance for retrieving data on a database table.
Using Help in SQL Svr
List methods
Creating DB using SSMS graphical interface
List steps
DBMS
Most users do not access a database directly. Instead, they use a database management system (DBMS) to access it indirectly. A DBMS is a collection of programs that enables you to enter, organize, and select data in a database.
Constraints
Rules for managing data input and updates Constraints are limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered.
SYSTEM TABLES
System views belong to the sys schema. Some of these system tables include the following: • sys.Tables • sys.Columns • sys.Databases • sys.Constraints • sys.Views • sys.Procedures • sys.Indexes • sys.Triggers • sys.Objects
table
Tables, used to store data, are two-dimensional objects consisting of rows and columns.
Unique constraint
The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) [Page 8]
Five Forms of Normalization
There are five normalization forms (NFs), of which we'll focus on the first three: • First normalization form (1NF): Eliminate repeating groups • Second normalization form (2NF): Eliminate redundant data • Third normalization form (3NF): Eliminate columns not dependent on key • Fourth normalization form (4NF): Isolate independent multiple relationships • Fifth normalization form (5NF): Isolate semantically related multiple relationships
3 fundamental characteristics of a db
This comparison highlights three fundamental characteristics of databases: • They are designed to store billions of rows of data. • They are limited to the computer's available hard disk space. • They are optimized to use all a computer's available memory to improve performance.
Scenario 1-2: Designing a Relational Database
You have been hired to create a relational database to support a car sales business. You need to store information on the business's employees, inventory, and completed sales. You also need to account for the fact that each salesperson receives a different percentage of their sales in commission. What tables and columns would you create in your relational database, and how would you link the tables? [Page 19]
Scenario 1-1: Considering Database Layout
You have just been hired as a database administrator for an international corporation that is a holding company for many other companies. Your first task is to design a new database infrastructure for the corporation. To begin, reflect on your activities over your first few weeks on the job. List at least one database that you have likely used either directly or indirectly and describe how each database is most likely laid out. [Page 19]
Scenario 1-3: Using Help from SQL Server 2008
You recently graduated from school and were hired as a junior database administrator. One thing you've learned over your first few months on the job is that you don't have all the answers. Thankfully, Microsoft SQL Server 2008 has an extensive help system and examples. Say you want to display help regarding use of the CREATE statement so that you can create a table. What steps would you use to find that information in SQL Server 2008's help system? [Page 19]
Scenario 1-4: Creating Databases Using the SSMS Graphical Interface
Your company, AdventureWorks, has decided to expand into interstellar travel. They have asked you to create a new database called Planets on the Microsoft SQL server using the SSMS graphical interface. What steps would you complete to create this database? [Page 19]
Considering DB layout
concepts
Designing a Relational DB
concepts
Constraints can define entity relationships between tables on a continual basis. They are also referred to as __________________ _________ constraints.
foreign key
query
inquiry into a database that returns information back from the database.
