MANAGING OBJECTS

INFO-1291

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 1

REVIEW OF LAST CLASS

In-Class Discussion:
1. What are the 3 goals of normalizing data?
2. What is 1NF? 2NF? And 3NF?
3. Why would we de-normalize data?
4. What are some methods used to de-normalize data?

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 2

THIS WEEK

 Accessing SQL Server

 Creating a Database in SQL Server
 Creating Tables
 Dropping Tables and Databases

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 3

ACCESSING MICROSOFT SQL SERVER
INFO-1291

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 4

MICROSOFT SQL SERVER

 Microsoft SQL Server is a widely used relational database management system (RDBMS).
 Each installation of SQL Server is referred to as an instance.
 There can be multiple instances of SQL Server on a single physical or virtual host. The maximum number of
instances allowed varies depending on how the instances are configured, the configuration of the overall database
environment in and organization and the resources available on the host.
 Microsoft SQL Server can be installed on Microsoft operating systems and several versions of Linux.
 This course uses a Windows Server 2022 host system with a single instance of SQL Server 2022 on it.

5
© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE
WAYS TO ACCESS MICROSOFT SQL SERVER
 Application programs (for end users not administrators in most cases).
 Desktop and mobile applications with graphical user interfaces.
 Web applications.
 SQLCMD command line program (for administrators and developers).
 May be the only tool available on a database server system.
 Requires the use of SQL statements.
 SQL Server Management Studio (for administrators and developers). Method used in this course.
 Desktop application with Graphical user interface.
 Supports SQL statements and graphical form-fill-in.
 PowerShell cmdlets (for administrators)

6
© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE
 © DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE
7
INITIAL VIEW OF SSMS
 SSMS CONNECT TO SERVER DIALOG

• Server type – The type of SQL Server-related service to

establish a connection with. The default value is Database Engine,
but another service can be selected if it is installed on the SQL
Server instance being used.
• Server name – The name and optionally a SQL Server instance
to connect to. In this course, the SQL Server system has only 1
instance, the default instance, installed on it, so the instance name
is omitted.
• Authentication – The authentication method to use. In this
course SQL Server Authentication (shown) and Windows
Authentication will be used. When SQL server Authentication is
being used, a username and password must be provided. For
Windows Authentication, the currently logged in user’s
credentials are automatically selected.
8
© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE
USERS CURRENTLY AVAILABLE

 The sa user account shown in the screenshot on the previous slide is the built-in database administrator (DBA)
account. This account is only available on a SQL Server instance if the instance was installed with Mixed Mode
authentication selected.
 Microsoft recommendation is to use only Windows Authentication for improved security, so the sa account and other
accounts local to a SQL Server instance are not usually available .
 The student account used to log onto the virtual machine is also a DBA account on this SQL Server instance.
Select the ‘Windows Authentication’ type to use this account to connect to the SQL Server instance.
 More on working with user accounts in SQL Server next week.

9
© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE
CREATING DATABASES
INFO-1291

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 10

CREATING A DATABASE

 Multiple databases can be created in a SQL Server instance

 Multiple tables can be created within a database

 In Microsoft SQL Server, there are two types of files which store database information:
 The transaction log file
 The data file

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 11

THE TRANSACTION LOG FILE

 The transaction log file is saved with a default file extension of .ldf (LDF)
 The transaction log stores a collection of database events, called transactions, such as:
 Adding data
 Updating data
 Deleting data
 Etc.
 A single transaction may include data modifications for more than table in a database.
 Each transaction log entry also includes information to track when each transaction occurred,, in what order
transactions occur, who executed the operations that produce the transactions etc.

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 12

THE DATA FILE

 The data file is saved with a default file extension of .mdf (MDF). A database may have more than one data file.
 This file holds committed data or RAW data.
 The process of committing data takes the data from the transaction log and writes it to the data file.
 A transaction is successfully completed when all data modifications within it have been committed to the
database file. If any part of a transaction cannot write data modifications to the data file, then the transaction
fails and any of the data modifications already performed are removed in a process known as rollback.
 Data files do not save as much information as the transaction log file such as:
 Date/time of an event
 Who executed the event
 Etc.

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 13

WRITING DATA TO A DATABASE

 Process for modifying data in the database:

1. User issue a query that modifies data to the database server
2. Database server writes the incoming query to the transaction
log.
3. Query complete and returns to user, possibly with error
messages.
4. At regular intervals (several times a second) the database
server issues a checkpoint that causes all uncommitted
transactions to update the data file
5. If transaction fails, failure is reported to user (not shown in the
picture).

Images Source: sqlbackuprestore.com

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 14

WRITING DATA TO A DATABASE (2)

 Since the transaction log file writes to the data file, it is

good practice to host these two files on different
physical or virtual disks whenever possible .
 More on checkpoints when we look at backing up
databases.

Images Source: sqlbackuprestore.com

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 15

AUTOGROWTH

 Both the transaction log and data files will be set to autogrow by default.
 This prevents two problems in a database’s operation:
 Avoids much of the data in a large file from being fragmented and slowing database performance.
 Avoids database failure because of full files.
 The transaction log and data files will reserve disk space based on the initial size set (set when creating a database)
 As data is added to these two files, and as the data stored in each nears the size of the file, the file will automatically
increase in size based on the autogrowth settings defined for each type of file in the database.
 Autogrowth settings can be:
 By MB/GB – Typically used if the same amount of data is being added over the same time period.
 By Percentage – Typically used if the amount of data is increasing exponentially over time (each successive time intervale has a larger
amount of data added).

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 16

AUTOGROWTH ISSUE

 The default settings for transaction log and data files have a default maximum size of ‘Unlimited’ for the files.
 Disk drives have limited sizes, so either
 A system or database administrator must constantly monitor the amount of free disk space on the drives where the
transaction log and data files are located, or
 The default autogrowth settings for the files must be modified to set a realistic maximum size based on the disk drive the
files are stored on.

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 17

GRAPHICAL VIEW OF CREATING A DATABASE
Let’s discuss each of the numbered items

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 18

CREATE A DATABASE

 Fields identified on the New Database dialog ishown on the previous slide:
1. Database Name – (required) The name of the new database. This name must be unique in the current SQL Server instance. This
name is used by applications and administration tools to manipulate the database and its contents.
2. Data file name and other information. By default, the first data file for a database has the same name as the database. Notice that
the file type is ‘ROWS’ indicating that it is a data file.
3. Transaction Log file name and information. By default, the first transaction log file for a database has the same name as the database
with ‘_log’ appended to it. Notice that the file type is ‘LOG’ indicating that I is a transaction log file.
4. Initial file size, by default this is 8 MB. In most cases this will be increated to a more realistic data based on the size of an existing
data set or estimates from application designers/developers.
5. Current Autogrowth and maximum file size settings (modified by clicking the button to the right of the field (6).
Also note the location of the files at the right of each line. The screenshot shows the default location sspecified automatically when SQL
Server is installed. This default location is mixed in with the SQL Server application code and should always be changed for production
systems (usually during SQL Server installation).

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 19

CREATE A DATABASE (2)

 Fields identified on the Autogrowth popup identified in the screenshot 2 slides back:
6. Specify whether the selected file is to grow by a fixed amount specified in Megabytes, or by a percentage of the current file
size.
7. Select whether the selected file has a maximum size specified inMegabytes or can grow until it has consumed all available
space on the drive where it is stored.

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 20

CREATING TABLES
INFO-1291

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 21

CREATING A TABLE

 A table is made up of multiple columns, each column representing a specific piece of data (e. g a name, a date, or a
price).
 Each row (or record) in a table that contains exactly one of each of the columns defined in the table. Each of the
columns in a row contains data that describes one of the items represented by the table.
 In many cases, the rows of a table are related to rows in one or more other tables (a key part of relational databases).
 We can define the following for each column when creating a table:
 Set a column to be a Primary key (One per table)
 Accept Null values or not
 Data Type
 Identity Specifications
 And more…

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 22

SETTING A PRIMARY KEY

 As discussed in previous classes, a Primary Key is used to uniquely identify each record within a table.
 Rules for a Primary Key:
 Column must contain unique data (No duplicates)
 Column cannot accept blank (null) values
 Only one column in a table can be a Primary Key

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 23

ACCEPTING NULL VALUES

 A column can be set to accept NULL values.

 This means allow a record to be entered without specifying a value for this column (Optional)
 If set to NOT NULL, the column must contain data (Mandatory)

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 24

DATA TYPES

 Data types define the type of data that can be entered into the column. Such as:
 Varchar
 Date
 Datetime
 Int
 And much much more…
 Acts as a form of data integrity, as users cannot add data that does not match the data type.

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 25

IDENTITY SPECIFICATION

 Identity Specification is a form of auto numbering.

 Set the data type of a column to an integer
 Specify the Seed and Increments.
 The Seed is the starting number and increments specifies the increments for the next value.
 As records are entered, the first value will be the seed value
 Next record will be the seed + the increment and so on.
 It is important to note, when adding a record, you do not specify a value for a column that has an identity
specification. SQL Server must determine this value based on this configuration.

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 26

TABLE DESIGN

1. Is the Primary Key

Indicated by the Yellow Key
2. Primary Key does not accept NULLs
3. Email address is optional
4. First and Last name can be 10 characters max
5. Email address can be 50 characters max.

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 27

IDENTITY SEED AND INCREMENT

1. Selected the ID_Staff Column to set an Identity

Specification.
2. Enabled Identity Specification
3. Set the Seed and Increments to 1

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 28

DROPPING DATABASES AND TABLES
INFO-1291

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 29

DROPPING DATABASES & TABLES

 When dropping a database or table, ensure no one has a connection to the database/table.
 Dropping = Delete
 There is no undo, if you accidently delete a table or database, you must restore from your last backup!

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 30

DROPPING A TABLE
This shows the steps to drop a table (delete a table)

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 31

DROPPING A DATABASE
This shows you how to drop a database (delete a database)

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 32

TIME TO PRACTICE

1. Reference practice activity #3 under this week’s content section on FOL.

2. On your own, complete the activity.
3. Take up the activity

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 33

SUMMARY

 Today we have learned the following:

1. What makes up a Database
2. What an MDF and LDF file are used for
3. Managing MDF and LDF files, disk locations, auto growth, initial size and max size.
4. How to create a database within SQL Server
5. How to create a table
6. How to set a primary key
7. How to change the data type of a column
8. How to configure an Identity Specification
9. Accept NULL or NOT NULL values

© DEVELOPED FOR CTY/CTN @ FANSHAWE COLLEGE 34