DT211 Stage 2

1
DT211 Stage 2

• Databases
• Lab 1

2
Get to know SQL Server

• SQL server has 2 parts
• A client, running on your machine, in the lab.
• You access the database from here, using SQL.
• You can store your SQL files locally or on your
  home drive.
• A server, running on a remote machine.
• This stores your table descriptions and data.
• Later you will store functions on this also.
• You can only read the databases on the server to
  which you have been granted read access.
• You can only write to or update databases to
  which you have been granted full access.
• You each have one database to which you have
  ownership access and several to which you have
  read access.

3
Connecting to SQL Server

• The server you are using this year is on the
  remote machine called CIAN.
• The IP address of this machine is
• 147.252.224.68
• Use the instructions following to connect to SQL
  Server.

4
Connecting to SQL Server

• Using the Query Analyser

5
Finding SQL Server

• Use the Start menu
• Programs
• Microsoft SQL Server 2000
• Query Analyser

6
(No Transcript)
7
Connecting

• The SQL Server is cian.comp.dit.ie
• Sometimes the name CIAN will do
• Sometimes you need to use the IP address
• The IP address is currently 147.252.224.68
• Use Windows NT authentication
• OK

8
(No Transcript)
9
Connection
When you connect first, the database will default
to the one you own.
10
Creating a table

• To create a table you must know
• The table name.
• The domain (field / column) names in the table.
• The column(s) that form the unique key to the
  table.
• The datatypes of all of the columns.

11
Datatypes in SQL Server

• The most commonly used datatypes in SQL Server
  are
• Numeric
• Followed by the full number of digits
• Followed by the number after the decimal place
• E.g. StaffId Numeric(7,0)
• E.g. CostPrice Numeric(9,2)
• Datetime
• This can be used to store a date and time. No
  further specification is required.
• E.g. Order_Date datetime
• Varchar
• This can be used to store a variable length
  string, with a maximum number of characters
  specified.
• E.g. Delivery_Address varchar(80)
• Char
• This can be used for fixed length strings
  usually short.
• E.g. CourseCode Char(5).

12
The CREATE statement

• Each table is set up on the server using the
  CREATE statement.
• The basic syntax is
• Create table (
• Column-name datatype,
• )
• denotes that this can be repeated
• Italics denote that the designer names the item.

13
Example table

• Create table BOOK
• (ISBN varchar(11),
• BookTitle varchar(80),
• Author varchar(20),
• CostPrice numeric(6,2),
• Genre char(8)
• )

14
Improving your table

• The table given above does not constrain the data
  much at all.
• To be more rigorous about the data that can go
  into the table, constraints are required.
• There are table constraints and column
  constraints.

15
Column constraints

• Unique
• This stops the user from entering the same value
  for this column twice.
• E.g. ISBN varchar(11) unique,
• Default
• This gives a default value to any field that has
  not been assigned a value
• E.g. CostPrice numeric(6,2) default 7.99
• Not Null
• This means that the value CANNOT be null
• E.g. Booktitle varchar(80) not null

16
Creating and Dropping

• Once you have created a table in your database,
  you cannot create it again.
• To delete the table, use DROP
• E.g. DROP TABLE BOOK
• This deletes the table and all its contents.

17
Todays exercises

• Connect to your database on the SQL Server on
  CIAN through the Query Analyser.
• Create a table BOOK as shown above.
• Using the browser in the Query analyser, check
  that BOOK is there.
• Drop the BOOK table.
• Put constraints on the table. Create it again.

18
Tutorial tables

• Create the tables that you devised in the
  tutorial.
• Leave them on the Server. Next week well add
  data to them.