Chapter 4:MS SQL SERVER 2008Creating Database Via TABLES

Chapter 4:MS SQL SERVER 2008Creating Database Via TABLES

Chapter 4:MS SQL SERVER 2008Creating Database via TABLES

MS SQL SERVER 2005 / 2008 – Management Studio (MS)

1. Create Database via Tables

Create Database via table generation using Unispares example.

Creating the tables and generate an Entity-Relationship Diagram within SQL Server EM environment.

At UoT jump to part d) as at the UoT you do NOT have admin rights to create DATABASES but do have the rights to create tables.

  1. Run SQL Server 2005 / 2008 – Manager Studio and expand the Databases tag. Right click in the summary dialogue box to create a new database.

  1. Type in the name of the database you want to create. The example shows the creation of a new database called UNISPARES. Type in unispares and click ok.

  1. Note the database UNISPARES is added to the SQL Server database list. Double click unispares in the summary dialogue box..

  1. Note the standard option for all databases. Double click tables.

  1. Right click to create a new table.

  1. Note the table field grid … similar to MS Access.

  1. An ERD for the following purchase order has been produced (see next page for ERD)

CASE STUDY 1 :UNISPARES PURCHASE ORDERS

Unispares is a retailer of computer equipment. Unispares use a standard Purchase Order Form when buying goods from its suppliers.

FIGURE (a) SAMPLE PURCHASE ORDER (PO) FORM

UNISPARES

PURCHASE ORDER (PO) FORM

Unispares Computer Ltd, 99 Borough Rd, Middlesbrough, TS1 3BA

tel: 01642-123456 fax: 01642-654321

PO No. 002594 PO Date: 15.12.96

Supplier#3451

Supplier Name:SELWOOD CONSULTANCY LTD

Supplier Address:KINGSDAEL HOUSE

MARINTET ROAD

THORNABY

CLEVELAND

TS17 0BB

PO DETAILS :

PARTPART DESCRIPTIONCOST£QTY

CPU012Pentium 100 16/1000175.002

PRI6214 HP Deskjet 660C125.00 3

MON023Philips 14" Colour Monitor 45.001

CON061HP Deskjet 660C Cartridge 20.004

CPU015Pentium 166 32/2000 CD700.001

KEY031Extended Keyboard 6.0010

CPU072Pentium 120 16/1000400.002

Payment Terms : COD/7days/30days/45days

In case of queries, please staff# 012 staff-name : FRED BLOGGS ext. 321

Implement the tables from the following Unispares ERD in MS SQL Server.

Purchase Order (PO) comprises of data from the following 5 tables – this process is covered in the lecture notes on ERD 07 to 11.

PURCHASE-ORDERS ( @po#, odate, s#, pt#, ct# )

SUPPLIERS ( @s#, sname, saddress, …….)

PARTS ( @p#, pdesc, …..)

PO-PARTS ( po#, p#, qty )

PAYMENT-TERMS ( @pt#, ptdesc )

CONTACT ( @ct#, cname, cext )

  1. To create the table PO (purchase orders) enter the following fields into the grid and then click on file save Table_1
  1. Replace the name Table_1 with PO and click ok.
  1. Note the new table PO.
  1. Repeat the procedure until you have all the following Unispares tables created.

PURCHASE-ORDERS ( @po#, odate, s#, pt#, ct# )

SUPPLIERS ( @s#, sname, saddress, …….)

PARTS ( @p#, pdesc, …..)

PO-PARTS ( po#, p#, qty )

PAYMENT-TERMS ( @pt#, ptdesc )

CONTACT ( @ct#, cname, cext )

  1. Note the llist of new tables created. Also note the options available when you right click on any of the table names. The Modify and Open Table work similar to MS Access.
  1. Under the Unispares database either click Database Diagrams or right click and choose new database diagram.
  1. Note you are now able to develop your ERD (data model) within this environment. Click on YES.
  1. Choose all the tables you want to add to your diagram.
  1. Arrange the tables as follows
  1. Note the options available when right clicking on a table. Change the Table View to Standard and change the table: payment terms field: pt# to nchar(10).
  1. Allocate the keys by clicking on the appropriate primary key field(s) and clicking the
  1. You may now drag and drop the keys to implement the relationships (similar to MS Access). You are also able to right click and add new tables or amend existing tables if you need to. Any changes done in the diagram section are reflected in the tables.

The following screen is displayed when the pt# from the table: payment-terms is dragged over the pt# from the table: PO.

Also note the integrity between the relationship. Go with the default settings.

  1. The following screen demonstrates the linkage between all tables.
  1. You can use the diagram to adjust the tables/columns/data-types via the diagrams. Note to avoid the following error Save changes not permitted you will need to change one of the option settings as indicate below:

Fix:

Save (Not Permitted) Dialog Box

The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created.

The following actions might require a table to be re-created:

  • Adding a new column to the middle of the table
  • Dropping a column
  • Changing column nullability
  • Changing the order of the columns
  • Changing the data type of a column

To allow saves to be permitted change the following option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

  1. Click on File Save and name the diagram UNISPARES ERD
  1. Note how SQL asks if you wish to update the live tables. Click yes.
  1. Note the tables and diagram created.
  1. You may wish to tackles the CASE STUDY relating to UniSpares. The unispares case study is also online under the case study list.
  1. You may also consider implementing any of the Library of Free Data Models

1

Mansha NawazMS SQL SERVER 2005 / 2008 – Creating Database via Tables Chapter 4:

Top View