Fundamentals of Database Systems Laboratory Manual PDF

Preview

Summary

ADDIS ABABA UNIVERSITY COLLEGE OF NATURAL & COMPUTITIONAL SCIENCE DEPARTMENT OF COMPUTER SCIENCE Laboratory Manual for the Fundamentals of Database Systems (COSC 3051) Using Microsoft SQL Server 2012 DBMS Prepared By: Surafiel Habib March 2017 i Table of Contents Acronyms ..........................

Description

ADDIS ABABA UNIVERSITY COLLEGE OF NATURAL & COMPUTITIONAL SCIENCE DEPARTMENT OF COMPUTER SCIENCE

Laboratory Manual for the Fundamentals of Database Systems (COSC 3051) Using Microsoft SQL Server 2012 DBMS

Prepared By: Surafiel Habib

March 2017

i

Table of Contents Acronyms ...........................................................................................................................................vi Objectives of the Manual ................................................................................................................. vii Required Software ........................................................................................................................... viii Introduction to Microsoft SQL Server ................................................................................................ 1 What is SQL Server? ....................................................................................................................... 1 Usage of SQL Server....................................................................................................................... 1 SQL Server Components ................................................................................................................. 3 Instance of SQL Server ................................................................................................................... 3 Advantages of Instances ............................................................................................................. 3 Installation Guide of Microsoft SQL Servers ................................................................................. 4 Installation Steps ......................................................................................................................... 5 Error messages with their possible solutions when you try to install SQL Server 2012 .......... 15 The SQL Server Setup Log Files ...................................................................................................... 20 Summary Text ............................................................................................................................... 20 Summary_engine-base_YYYYMMDD_HHMMss.txt ................................................................ 21 Summary_engine-base_YYYYMMDD_HHMMss_ComponentUpdate.txt ................................ 21 Summary_engine-base_MMDD_HHMMss_GlobalRules.txt ....................... 21 Detail.txt ........................................................................................................................................ 21 Bootstrap\Log\\Detail.txt. ............................................................................................................. 21 Detail_ComponentUpdate.txt........................................................................................................ 22 Detail_GlobalRules.txt .................................................................................................................. 22 MSI log files .................................................................................................................................. 22 ConfigurationFile.ini ..................................................................................................................... 22 Bootstrap\Log\\.SystemConfigurationCheck_Report.htm ........................................................... 22 MS SQL Server - Architecture .......................................................................................................... 23 General Architecture ..................................................................................................................... 23 Memory Architecture .................................................................................................................... 24 Data File Architecture ................................................................................................................... 25 File Groups................................................................................................................................ 25 Files ........................................................................................................................................... 26 ii

Extents....................................................................................................................................... 26 Pages ......................................................................................................................................... 26 Log File Architecture .................................................................................................................... 27 Checkpoints in SQL Server ............................................................................................................... 28 SQL Server Management Studio (SSMS)......................................................................................... 29 MS SQL Server: Login Database ...................................................................................................... 30 The SQL Language ........................................................................................................................... 34 Data Manipulation Language (DML) Vs Data Definition Language (DDL) ............................... 35 LAB 1: MS SQL Server: Database creation using Graphical User Interface of SQL Server Management Studio............................................................................................................... 37 System Databases .......................................................................................................................... 37 1.1 How to Create Database in Microsoft SQL Server 2012 using SSMS ................................... 38 1.2 Creating a Table ...................................................................................................................... 39 1.3 Editing Table Rows ................................................................................................................. 41 LAB 2: Starting writing SQL Statements using SQL Server Management Studio Command line Query Editor .......................................................................................................................... 42 Data Types of SQL Server ............................................................................................................ 42 2.1 Create Database Statement ...................................................................................................... 45 2.2 Create Table Statement ........................................................................................................... 49 SQL Alter Table Syntax ............................................................................................................ 51 Changing the Data type of a column in a Relational Database................................................. 51 LAB 3: Implementing Constraints .................................................................................................... 52 3.1 Primary Key Constraints ......................................................................................................... 52 3.1.1 Adding a primary key while creating a Table.................................................................. 52 3.1.2 Adding a primary key after a Table is created ................................................................. 52 Dropping a primary key constraint ........................................................................................... 53 3.2 Foreign Key Constraints.......................................................................................................... 53 3.2.1 SQL FOREIGN KEY Constraint on ALTER TABLE ........................................................ 54 Lab 4: Default Constraints ................................................................................................................ 55 4.1 SQL DEFAULT Constraint on CREATE TABLE ................................................................. 55 4.2 SQL DEFAULT Constraint on ALTER TABLE ................................................................... 55 4.3 DROP a DEFAULT Constraint .............................................................................................. 56 iii

Lab 5: SQL CHECK Constraint ........................................................................................................ 57 5.1 SQL CHECK Constraint on CREATE TABLE ..................................................................... 57 5.2 DROPING a CHECK Constraint ............................................................................................ 58 LAB 6: SQL Create Index Statement ................................................................................................ 59 6.1 Create an Index ........................................................................................................................ 59 LAB 7: SQL Drop statements of COLUMN, INDEX, TABLE and DATABASE .......................... 61 7.1 Dropping an existing column .................................................................................................. 61 7.2 The DROP INDEX Statement ................................................................................................. 61 7.3 The DROP TABLE Statement ................................................................................................ 62 7.4 The TRUNCATE TABLE Statement ..................................................................................... 62 LAB 8: Data Manipulation Language (DML) ................................................................................. 63 8.1 Inserting Records (INSERT SQL Command)......................................................................... 63 8.2 Updating Records (UPDATE SQL Command) ...................................................................... 64 8.3 Deleting Records (DELETE Command) ................................................................................ 64 LAB 9: Selecting Data from the Database Tables ............................................................................ 65 Attaching AdventureWorks Sample Database in to the SQL Server 2012R2 ............................. 66 Syntax of selecting data from SQL server 2012 Databases: ........................................................ 71 Order By clause ............................................................................................................................. 73 LAB 10: IN, NOTIN, LIKE and ISNULL Keywords....................................................................... 74 10.1 The use of IN keyword .......................................................................................................... 74 10.2 The use of NOT IN keyword ................................................................................................ 74 10.3 The use of LIKE keyword ..................................................................................................... 75 10.4 IS NULL Keyword ................................................................................................................ 75 LAB 11: Aggregate functions ........................................................................................................... 77 11.1 Count function ....................................................................................................................... 77 LAB 11.2 MAX and MIN functions ............................................................................................ 77 11.3 Average function ................................................................................................................... 77 LAB 12: Group by clause.................................................................................................................. 78 12.1 Having clause ........................................................................................................................ 78 12.2 Combining conditions and Boolean Operators ..................................................................... 80 12.3 The use of DISTINCT Keyword ........................................................................................... 80 iv

LAB 13: Tables Join ......................................................................................................................... 81 13.1 Joining Two Tables ............................................................................................................... 81 13.2 INNER JOIN ......................................................................................................................... 81 13.3 OUTER JOIN ........................................................................................................................ 81 13.4 Sub Query .............................................................................................................................. 82 LAB 14: View ................................................................................................................................... 83 14.1 Creating Views ...................................................................................................................... 83 14. 2 Updating a View .................................................................................................................. 85 14.3 Inserting Rows in a View ...................................................................................................... 85 14. 4 Deleting Rows in a View ..................................................................................................... 86 14. 5 Dropping Views: .................................................................................................................. 86 References: ........................................................................................................................................ 87

v

Acronyms BIDS

Business Intelligence Development Studio

CRM

Customer Relationship Management

DBMS

Database Management System

DDL

Data Definition Language

DML

Data Manipulation Language

ETL

Extract, Transform, Load

GAM

Global Allocation Map

IAX

Index Allocation Map

IBM

International Business Machines

IP

Internet Protocol

MS

Microsoft

MSI

Microsoft Software Installer

MSP

Microsoft Solution Provider

ODBMS

Object Database Management System

OS

Operating System

PFS

Page Free Space

PID

Process Identification Number

R2

Release Two

RDBMS

Relational Database Management System

RDP

Remote Desktop Protocol

RTM

Release-to-Manufacturing

SP

Service Pack

SQL

Standard Query Language

SSAS

SQL Server Analysis Services

SSCM

SQL Server Configuration Manager

SSIS

SQL Server Integration Services

SSMS

SQL Server Management Studio

SSRS

SQL Server Reporting Services

TCP/IP

Transmission Control Protocol/Internet Protocol

VIA

Virtual Interface Adapter

vi

Objectives of the Manual This laboratory Manual is prepared to deliver examples and exercises for the fundamentals of Database systems (COSC 3051) course within the Microsoft SQL server 2012 Relational Database Management System tool. The main objective is to learn the basic development of a relational database application and to illustrate the concepts of SQL using simple examples. This laboratory manual has been divided up into fourteen (14) sessions. Each one contains of examples, tasks and exercises about a particular concept in SQL and how it is implemented in MS SQL Server 2012. After completion of this fourteen (14) weeks laboratory guide the students will be able to: 

Implement a Data Definition Language (DDL) to create a Relational Databases, Tables and Indexes.



Implement a Data Manipulation Language (DML) to Insert, Update, Select and Delete data’s of the Database tables.



Create queries using basic and advanced SELECT statements.



Perform Join operations on Relational Database Tables.



Implement Aggregate functions in SQL.



Write sub queries.



Create Views of the Database.

vii

Required Software The Microsoft SQL Server 2012 DBMS must be installed in the laboratory room Personal Computers.

viii

Introduction to Microsoft SQL Server Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft Software Company. It is built for the basic function of storing and retrieving data as required by other applications and can be run either on the same computer or on another across a network. SQL Server is commonly used as the backend system for websites and corporate CRMs and can support thousands of concurrent users. SQL Server comes with a number of tools to help the users on Database Administration and programming tasks.

What is SQL Server? 

It is application software, developed by Microsoft Company, which is implemented from the specification of Relational Database Management System (RDBMS).



It is also an Object Relational Database Management System (ORDBMS).



It is platform dependent.



It is both Graphical User Interface (GUI) and command based software.



It supports SQL (SE-QUE-EL) language which is an IBM product, non-procedural, common Database and case insensitive language.

Usage of SQL Server 

To create Databases.



To maintain Databases.



To analyze the data through SQL Server Analysis Services (SSAS).



To generate reports through SQL Server Reporting Services (SSRS).



To carry out ETL operations through SQL Server Integration Services (SSIS).

1

Table 1: Versions of Microsoft SQL Server

Version Name SQL Server 2017 codename vNext

RTM (no SP)

SP1

SP2

SP3

SP4

14.0.1000.169 *Latest Version (New)

SQL Server 2016

13.0.1601.5

13.0.4001.0 or 13.1.4001.0

SQL Server 2014

12.0.2000.8

12.0.4100.1 or 12.1.4100.1

SQL Server 2012

11.0.2100.60

11.0.3000.0 11.0.5058.0 or 11.1.3000.0 or 11.2.5058.0

SQL Server 2008 R2 codename Kilimanjaro

10.50.1600.1

10.50.2500.0 or 10.51.2500.0

10.50.4000.0 10.50.6000.34 or or 10.52.4000.0 10.53.6000.34

SQL Server 2008

10.0.1600.22

10.0.2531.0 or 10.1.2531.0

10.0.4000.0 or 10.2.4000.0

10.0.5500.0 or 10.3.5500.0

10.0.6000.29 or 10.4.6000.29

9.0.1399.06

9.0.2047

9.0.3042

9.0.4035

9.0.5000

8.0.194

8.0.384

8.0.532

8.0.760

8.0.2039

7.0.623

7.0.699

7.0.842

7.0.961

7.0.1063

codename Denali

codename Katmai

SQL Server 2005

12.0.5000.0 or 12.2.5000.0 11.0.6020.0 or 11.3.6020.0

11.0.7001.0...