FTTI - Training, Teaching and Learning Material

FEDERAL TVET TEACHERS TRAINING INSTITUTE

FUNDAMENTALS OF DATABASE SYSTEM

na
e r
.C
D
LEARNING k
ic GUIDE # 3 tr
P a

LO 3: Creating Database and Tables (Physical Design)

Fundamental of Database System Date: October-2011 Page 1 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

INTRODUCTION Learning Guide # 3

This learning guide was developed to provide you the necessary information regarding the following
content coverage and topics –

 Overview of the Physical Database Design

 Using and Installing MS SQL Server 2005 for Physical Database Design
 Using MS SQL Server Management Studio Express in Creating Database
 Creating Tables using Wizard and SQL Script and Commands
 Attaching SQL Server Database File

This guide will also assist you to attain the learning outcome stated in the cover page. Specifically,
upon completion of this Learning Guide, you will be able to –

 Know the nature of the Physical Database Design


na
Use and Install MS SQL Server 2005 for Physical Database Design

e r
Use MS SQL Server Management Studio Express in Creating Database

.C
 Create Tables using Wizard and SQL Script and Commands

D
Attach SQL Server Database File in SQL Server 2005

ck
Learning Activities
a tri
P
1. Read the specific objectives of this Learning Guide.
2. Read the information written in the “Information Sheets 1 and 2”.
3. Accomplish the “Self-check”.
4. If you earned a satisfactory evaluation proceed to “Operation Sheet 1 to 5”. However, if your rating
is unsatisfactory, see your teacher for further instructions or go back to Learning Activity # 1.
5. Submit your accomplished Self-check. This will form part of your training portfolio.
6. Read the “Operation Sheet 1 to 5” and perform the procedures discussed.
7. Do the “LAP test” in page 15 (if you are ready) and show your output to your teacher. Your teacher
will evaluate your output either satisfactory or unsatisfactory. If unsatisfactory, your teacher shall
advice you on additional work. But if satisfactory you can proceed to Learning Guide 4.

Fundamental of Database System Date: October-2011 Page 2 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material
Information Sheet 1 Overview of Physical Database Design

Physical database design translates the logical data model into a set of SQL statements that
define the database. For relational database systems, it is relatively easy to translate from a
logical data model into a physical database.

A physical database model adds more implementation level details to the design. It is specific to
a DBMS product (e.g. MS SQL Server 2005).

na
e r
.C
D
ck
tri
What to design?
Pa
1. Designing basic database logical model objects (DBMS specific)
 Databases, tables, fields, views
 Constraints: primary key, foreign key, uniqueness, referential integrity, default, range

2. Designing additional physical level objects

 Files: storage files, partitions, etc.
 Performance: indexes, views, etc.
 Functional: triggers, scripts (stored procedures), functions,
 transactions, etc.
 Security: users, roles, permissions, etc.
Rules for translation:

 Entities become tables in the physical database.

 Attributes become columns in the physical database. Choose an appropriate data type for
each of the columns.
Fundamental of Database System Date: October-2011 Page 3 of 47
FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material
 Unique identifiers become columns that are not allowed to have NULL values. These are
referred to as primary keys in the physical database. Consider creating a unique index on the
identifiers to enforce uniqueness.

 Relationships are modeled as foreign keys.

What is SQL?

na
e r
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 4 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

Data Definition Language Statements

Data definition language (DDL) statements define, alter the structure of, and drop schema objects.
DDL statements enable you to:

 Create, alter, and drop schema objects and other database structures, including the database
itself and database users (CREATE, ALTER, DROP)
 Change the names of schema objects (RENAME)
 Delete all the data in schema objects without removing the objects' structure (TRUNCATE)
 Grant and revoke privileges and roles (GRANT, REVOKE)
 Turn auditing options on and off (AUDIT, NOAUDIT)
 Add a comment to the data dictionary (COMMENT)

DDL table statements include

na
e r
.C
 CREATE Table
 ALTER Table
D
 DROP Table
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 5 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

Syntax:

Database – Create Database [Name]

Table – Create Table [TableName] (Column Definitions, Table Constraints)

na
e r
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 6 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 7 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

Using MS SQL Server 2005 in Database Physical

Information Sheet 2
Design

Microsoft SQL Server is a relational database server, developed by Microsoft: It is software

product whose primary function is to store and retrieve data as requested by other software
applications, be it those on the same computer or those running on another computer across
a network (including the Internet).
Versions of SQL Server 2005 Available
SQL Server 2005 Express Edition

SQL Server 2005 Express provides a great means of getting started with SQL Server. It offers a
robust, reliable, stable environment that is free and easy to use. It provides the same protection and
information management provided by the more sophisticated versions of SQL Server.

SQL Server 2005 Workgroup Edition

a
SQL Server Workgroup Edition provides a great solution for small organizations or workgroups within

r n
larger entities. It includes a rich feature set, but is affordable and simple to work with. Other valuable

e
features include the fact that there is no limit on database size and that it supports the SQL Agent Job

.C
Scheduling Service

SQL Server 2005 Developer Edition D

ck
tri
SQL Server 2005 Developer Edition is designed specifically for developers who are building SQL

Pa
Server 2005 applications. It includes all functionality of SQL Server 2005 Enterprise Edition, but with
a special license that limits its use to development and testing.

SQL Server 2005 Standard Edition

SQL Server Standard Edition provides an affordable option for small- and medium-sized businesses.
It includes all functionality required for non-critical e-commerce, data warehousing, and line-of-
business solutions.

SQL Server 2005 Enterprise Edition

SQL Server 2005 Enterprise Edition includes all the tools that you need to manage an enterprise
database management system. It offers a complete set of enterprise management and business
intelligence features, and provides the highest levels of scalability and availability of all the SQL
Server 2005 editions.

SQL Server 2005 Mobile Edition

SQL Server 2005 Mobile Edition enables you to easily port corporate applications over to mobile
devices. SQL Server 2005 Mobile Edition offers many advantages.

Fundamental of Database System Date: October-2011 Page 8 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

Operation Sheet 1 Installing MS SQL Server 2005 Express

Installing SQL Server Express

Although installing SQL Server Express is a fairly simple process, you are asked a few
questions during the installation process that might need some clarification. The text that
follows walks you through the process of installing the SQL Server 2005 Express
database engine, and provides an explanation of the various options available to you.
Follow these steps:

Step 1. When you launch the setup program, an end-user license agreement appears. You must accept
the agreement before proceeding. After clicking to accept the agreement, click Next. The Installing
Prerequisites step of the installation process runs (see Figure 2.2).

na
e r
.C
D
ck
tri
Pa

Figure 2.2. The Installing Prerequisites step of the installation process verifies that all the
required components were installed successfully.

Fundamental of Database System Date: October-2011 Page 9 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
tri
Pa
Step 2. After verifying that all the required components were installed successfully, click Next. The
Microsoft SQL Server 2005 Setup welcome screen appears (see Figure 2.3).

Fundamental of Database System Date: October-2011 Page 10 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
Figure 2.3. The Microsoft SQL Server 2005 Setup welcome screen commences the actual
installation process.
D
c k
r i
Step 3: Click Next. SQL Server 2005 Setup tperforms
a
a System Configuration Check. The results appear
as in Figure 2.4).
P

Fundamental of Database System Date: October-2011 Page 11 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
tri
Pa
Figure 2.4. It is important to note the results of the System Configuration Check.

Step 4: Click Next to proceed to the next step of the wizard. The Registration Information step of the
installation process appears (see Figure 2.5).

Fundamental of Database System Date: October-2011 Page 12 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
tri
Figure 2.5. You must provide registration information as part of the installation process.

Pa

Step 5. Fill in the required information and click Next. The Feature Selection step of the wizard appears
(see Figure 2.6). The wizard helps you select what features are installed on your computer and how the
setup process will install them.

Fundamental of Database System Date: October-2011 Page 13 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
c k
i
Figure 2.6. The Feature Selection step of ther wizard enables you to determine what features are installed
t on your computer.
P a

Step 6: If desired, click Disk Cost to view the space available on each drive. The Disk Cost step of the
wizard appears (see Figure 2.7).

Fundamental of Database System Date: October-2011 Page 14 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
Figure 2.7. The Disk Cost dialog shows you the space available on each disk drive.

tri
Pa
7. Click Close to close the Disk Cost dialog and then Next to proceed with the wizard. The Authentication
Mode step of the wizard appears (see Figure 2.8). Here you must determine whether your server will
accept only Windows logins, or whether it will also support SQL Server logins. For more information on this
topic, seeLesson 21, "SQL Server Authentication."

Fundamental of Database System Date: October-2011 Page 15 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
Figure 2.8. The Authentication Mode step enables you to determine the type of authentication you want to

tri
accept.

Pa

8. After making your selection, click Next to proceed to the next step. The Error and Usage Report Settings
dialog appears (see Figure 2.9). Here you determine what happens when an error occurs (where SQL
Server will send error reports), and whether you want Microsoft to receive information automatically about
your feature usage.

Fundamental of Database System Date: October-2011 Page 16 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
k
Figure 2.9. The Error and Usage Report Settings dialog enables you to determine what happens when an
c
tri
error occurs.

9. Pa
Click the appropriate check boxes and click Next. The Ready to Install step of the wizard appears. The
dialog shows you what components the SQL Server 2005 Express Edition Setup will install (see Figure
2.10).

Fundamental of Database System Date: October-2011 Page 17 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
k
Figure 2.10. The Install step of the wizard shows you what components will be installed.
c
tri
Pa
10. Click Install to complete the process. After a moment the Setup Progress dialog appears (see Figure 2.11).

Fundamental of Database System Date: October-2011 Page 18 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
tri
Figure 2.11. The Setup Progress dialog shows you what is happening during the setup process

Pa
11. Click Next when setup is finished. The Completing Microsoft SQL Server 2005 Setup step of the wizard
appears (see Figure 2.12).

Fundamental of Database System Date: October-2011 Page 19 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
k
Figure 2.12. The Completing Microsoft SQL Server 2005 Setup step of the wizard provides a summary of
c
tri
what happened during the setup process.

Pa

Fundamental of Database System Date: October-2011 Page 20 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material
Operation Sheet 2 Installing MS SQL Server Management Studio

Installing the database engine is generally not enough. You will have no way to manage
the server and its objects. This is where SQL Server Management Studio Express fits in.
Microsoft provides a separate installation program for SQL Server Management Studio
Express. To run it, follow these steps:

1. After the installation wizard launches, a welcome screen appears (see Figure 2.13).

na
e r
.C
D
ck
tri
Pa

Figure 2.13. The SQL Server Management Studio Express installation program begins with a welcome
screen.

2. Click Next. The License Agreement step of the wizard appears (see Figure 2.14).

Fundamental of Database System Date: October-2011 Page 21 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
Figure 2.14. You must complete the License Agreement step of the wizard before proceeding.

.C
D
k
3. Click to accept the license agreement.

i c
r appears (see Figure 2.15).
4.
t
Pa
Click Next. The Registration Information dialog

Fundamental of Database System Date: October-2011 Page 22 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
Figure 2.15. The Registration Information step of the wizard enables you to enter user and company

.C
information.

D
5.
ck
Fill in the registration information and click Next. The Feature Selection step of the wizard appears
(see Figure 2.16).
tri
Pa

Fundamental of Database System Date: October-2011 Page 23 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
Figure 2.16. The Feature Selection step of the wizard enables you to determine what features will be

.C
installed.

D
6. Select the desired features and click Next.
ck
tri
Pa
7. The Ready to Install the Program step of the wizard appears. Click Install to complete the process.

8. When the installation process is complete, the Completing the Microsoft SQL Server Management Studio
Express Setup step of the wizard appears. Click Finish.

Fundamental of Database System Date: October-2011 Page 24 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

Starting SQL Server using Management Server Studio

Operation Sheet 3
Express
Getting Started with the SQL Server Management Studio Express

SQL Server Management Studio Express is the tool that you use to manage your SQL
Server and its objects. Here are some basics that you should know before proceeding with
the lessons that follow. To get started with SQL Server Management Studio Express,
follow these steps:

Step. 1. MS SQL Server 2005 can be open by launching the SQL Server Management
Studio Studio Express in the Start Menu (see. Figure below)

na
e r
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 25 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

Step 2. When you launch SQL Server Management Studio Express, the Connect to Server dialog appears
(see Figure 2.17). Here you provide login information.

na
e r
.C
Figure 2.17. You must provide login information before using SQL Server Management Studio
Express.
D
ck
tri
Step 3. Provide the server name and the type of authentication and then click Connect. If you are successful,

Pa
you are placed in SQL Server Management Studio Express (see Figure 2.18).

Fundamental of Database System Date: October-2011 Page 26 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

a
Figure 2.18. SQL Server Management Studio Express enables you to manage all aspects of SQL Server.
n
e r
.C
Step 4. Notice that you can use the Object Explorer to expand and contract nodes, enabling you to focus on
what you want to work on (see Figure 2.19).
D
ck
tri
Pa

Figure 2.19. You can expand and contract nodes within the Object Explorer.

Fundamental of Database System Date: October-2011 Page 27 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

Step 5. If you click to select an object in the Object Explorer, summary information about that object appears
in the Summary pane (see Figure 2.20). This pane differs quite a bit depending upon what you select in the
object explorer.

na
e r
.C
D
c k
t i
r information about the object selected in the Object
Pa
Figure 2.20. The Summary pane provides
Explorer.

Step 6. When you are modifying an object, such as a table, the Properties window becomes active, enabling
you to modify object properties (see Figure 2.21).

Fundamental of Database System Date: October-2011 Page 28 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
r
Figure 2.21. The Properties window enables you to modify object properties.
e
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 29 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

Operation Sheet 4 Creating Database and Tables in SQL Server 2005

na
e r
.C
D
ck
tri
Pa

Exercise1: Creating a SQL Server Database

Before you can build tables, views, stored procedures, triggers, functions, and other
objects, you must create the database in which they will reside. A database is a collection
of objects that relate to one another. An example would be all the tables and other objects
necessary to build a sales order system. To create a SQL Server database, follow these
steps:

1. Right-click the Databases node and select New Database. The New Database dialog appears

Fundamental of Database System Date: October-2011 Page 30 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
2. Enter a name for the database.
.C
D
3. Enter a path for the database
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 31 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

4. Click to select the Options page and change any options as desired (see Figure 3.7).

na
e r
.C
D
ck
tri
Pa
Figure 3.7. The Options page of the New Database dialog enables you to set custom options for the
database.

Fundamental of Database System Date: October-2011 Page 32 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
tri
Pa
5. Click OK to close the New Database dialog and save the new database. The database
now appears under the list of databases (see Figure 3.8) under the Databases node of
SQL Server Management Studio. If the database does not appear, right-click the
Databases node and select Refresh.

Fundamental of Database System Date: October-2011 Page 33 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
r
Figure 3.8. The new database appears under the list of databases in the Databases node.
e
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 34 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

Exercise 2: Creating a Table (using SQL Commands/Script)

1. Right-click the Databases node and select New Query. The New Query Box appears
(see Figure 3.5).

na
e r
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 35 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

2. Type the SQL Statement in Creating the Employee Table and Click Execute Button

na
e r
. C
Next, continue typing the SQL StatementDin Creating the Project Table and Click Execute
k
3.
Button
r i c
t
Pa

Fundamental of Database System Date: October-2011 Page 36 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material
4. Lastly, type the SQL Statement in Creating the Assignment Table and Click Execute Button.
Check the on the right side under the Object Explorer if the three (3) tables is present.

na
e r
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 37 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material
Exercise 3: Creating a Table using Wizard

1. Right Click on the Tables(Menu) and Select New Table as shown in the Fig. (below)

na
e r
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 38 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material
2. Next, assign or choose the primary key as shown on Fig. (below)

na
e r
.C
D
ck
tri
Pa
3. Next, assign or choose Surrogate Key (Foreign Key) and Composite Key as shown in the Fig. (below )

Fundamental of Database System Date: October-2011 Page 39 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 40 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

4. Next, create a relationship diagram for the tables and view more metadata as shown in the following
fig. (below)

na
e r
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 41 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

5. Next, modify columns in the relationship diagram, create relationships and perform other commands if
necessary as shown in the following fig. (below)

na
e r
.C
D
ck
tri
Pa

Fundamental of Database System Date: October-2011 Page 42 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

r na
Lastly, perform referential integrity actions on theerelationship as shown in the Fig. (below)
.C
6.

D
c k
t r i
Pa

Fundamental of Database System Date: October-2011 Page 43 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

Operation Sheet 5 Attaching Database and Tables in SQL Server 2005

Attaching to the NorthWind Sample Database File

Once you have installed the sample files you must make SQL Server aware of them by
using a process called "attaching to the database file." Here are the steps involved:

1. Open up Microsoft SQL Server Management Studio Express.

2. Right-click the Databases node and select Attach. The Attach Databases dialog appears (see Figure 5.1).

na
e r
.C
D
ck
tri
Pa

Figure 5.1. The Attach Databases dialog allows you to attach to an existing database.

3. Click the Add command button. The Locate Database Files dialog appears (see Figure 5.2).

Fundamental of Database System Date: October-2011 Page 44 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
a tri
P
Figure 5.2. The Locate Database Files dialog allows you to locate and select the database file to which
you want to attach.

4. Locate the NorthWind database in the location to which the installation process copied the file (Ask you
teacher on the location of the database file)

5. Click to select the NorthWind database.

6. Click OK to close the dialog. You have returned to the Attach Databases dialog, which appears in Figure
5.3. Note: If the log file is in the same location as the database file, there is no need to separately locate
the log file.

Fundamental of Database System Date: October-2011 Page 45 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
. C
D
Figure 5.3. The Attach Databases dialog contains all of the information required to complete the

c k
attachment process.

tr i
7.
P a database now appears in the list of databases available
Click OK to complete the process. The NorthWind
on the server (see Figure 5.4).

Fundamental of Database System Date: October-2011 Page 46 of 47

FDRE-TVET Institute
FTTI - Training, Teaching and Learning Material

na
e r
.C
D
ck
tri
Figure 5.4. After attaching to a database file, it appears as one of the files available under the Databases node
of that server.

Pa

Fundamental of Database System Date: October-2011 Page 47 of 47

FDRE-TVET Institute