Transcript dropping tables - Binus Repository

Matakuliah : T0413/Current Popular IT II
Tahun
: 2007
Overview Relational Databases and SQL
Pertemuan 1
AGENDA:
•Review Relational Databases
•Overview SQL
•Creating a Database
•Creating, Changing, and Dropping Tables
Book:
Mastering SQL by Martin Gruber
Sybex (2000)
Chapter : 1 - 3
2
Review Relational Databases
• What is a Relational Database?
– It is a body of persistent information stored in two-dimensional tables (by a
computer program), where the data survives the terminations of a program
or user session that created it.
• A simple way to picture a relational database is to think of an
address book. Suppose you were to format this address book as a
tables with rows and columns, each row would corresponds to a
certain individual.
3
Review Relational Databases (cont’d)
• However, what if there were more than one individual named Celia
Brock? Or Gerry Farish ?
• You should assign each person a unique identifier ( a value that is
different for each person)
• It is called Primary Key
• Table with Primary Key added  Client Table :
4
The need of Second Table
• Suppose we need to add a telephone number column to Client table.
• Most people have more than one telephone number (fax, cell phone,
etc).
• Solution : build another table
• Client_Phone table :
• Client_Phone table also contains Primary Key from Client table
• The ID_Num in Client_Phone table is called Foreign Key, because it
references the Primary Key in Client table
5
The need of Second Table (cont’d)
• If all the Foreign Keys values in Client_Phone table reference values
that are actually present in Client table, the system has Referential
Integrity.
• We also need a Primary Key for the Client_Phone table.
• A Primary or Foreign Key need not be a single column.
• We can combine the ID_Num and Phone columns as the Primary Keys
for Client_Phone table.
• As long as the combination of both columns are always unique.
• A key of more than one column is referred by :
– Multicolumn Key, or
– Composite Key, or
– Concatenated Key.
6
Joining Tables
• When we extract the information contained in the two separate
tables (Client and Client_Phone), we can link each Foreign Key
values that match the values in Primary Key.
• An operation that extracts information from database is called a
query.
• A query that extracts data from more than one table at the same
time by relating columns in one table to columns in the other(s) is
called a Join.
• Queries are implemented in SQL with the SELECT statement.
7
How a Relational Database Fits Together
•
•
•
•
Tables were grouped in a database .
It is called Schema ( a group of interrelated tables).
A database can contain any number of Schemas.
Relational systems allow you to work with the data at a higher level,
all operations on the data are handled by a program called DBMS,
which responds only to statements expressed in a high-level
language.
• DBMS = Database Management System
8
Overview SQL
• SQL is a language oriented specifically around relational databases.
• Forms of SQL:
– Interactive SQL
– Static SQL
– Dynamic SQL
• The Subdivisions of SQL:
– DDL (Data Definition Language)
– DML (Data Manipulation Language)
– DCL (Data Control Language)
9
Overview SQL (cont’d)
• Various Types of Data (datatype) can be divided into the following
categories:
–
–
–
–
Text
Numeric
Datetime
Binary, etc
• Two very important concepts,
1. Predicate : a set of criteria use to decide whether a statement will or will not
be executed against a particular row of a table.
2. Query : it is a request for information from the database. In other words, a
SELECT statement.
10
Overview SQL (cont’d)
•
Basic SQL Statements:
SELECT snum, sname, city, comm
FROM Salespeople
WHERE sname = ‘Peel’;
DELETE FROM Salespeople
WHERE snum = 1001;
11
Creating a Database
Example :
CREATE DATABASE MYDBASE
ON PRIMARY (
 OPTIONAL : primary filegroup berisi system tables
NAME = MYDBASE_DATA,
FILENAME =”C:\mssql\data\mydb.mdf”  nama file yang berlaku pada OS, ditulis full
pathname
SIZE=20MB,  besar awal dari file dengan satuan MB (default) atau KB.
MAXSIZE=40mb,  maksimum besar file yang dapat dicapai dalam MB atau KB
FILEGROWTH=1MB  pertumbuhan file dalam MB
12
Modifying a Database
Example :
ALTER DATABASE MYDBASE
MODIFY FILE (
NAME = MYDBASE_DATA,
SIZE = 60MB
);
13
Creating, Changing, and Dropping Tables
CREATING TABLES
• Tables are being defined using CREATE TABLE command
• It creates an initially empty tables
• The command defines a table name as describing a set of named
columns in a specified order.
• It also defines the data types and sizes of the columns.
• Each table must have at least one column.
• Syntax:
CREATE TABLE tablename
({columnname datatype[(size)]},…);
14
Creating, Changing, and Dropping Tables (cont’d)
• Example:
CREATE TABLE Salespeople
( snum INTEGER,
sname CHAR(10),
city CHAR(10),
comm DECIMAL
);
• Order of columns are determined by the order specified inside the
CREATE TABLE command.
• By using INSERT statement, we can insert values into the table that
we have created before.
15
Creating, Changing, and Dropping Tables (cont’d)
CHANGING TABLES
• ALTER TABLE statement is used to change the definitions in a table.
• ALTER TABLE can do the following:
– Add a column to a table
– Drop a column from a table
– Add a table constraint to a table
– Drop a constraint from a table
– Add a default value to a column
– Drop a default value from a column
16
Creating, Changing, and Dropping Tables (cont’d)
• Syntax:
ALTER TABLE tablename
{ ADD [COLUMN] column definition }
| { ALTER [COLUMN] column name
{ SET DEFAULT default option } | {DROP DEFAULT } }
| { DROP [COLUMN] column name }
| { ADD table constraint definition }
| { DROP CONSTRAINT constraint name };
• Example:
– ALTER TABLE Salespeople ADD fname CHAR(10);
– ALTER TABLE Salespeople ALTER COLUMN city ADD DEFAULT ‘London’;
17
Creating, Changing, and Dropping Tables (cont’d)
DROPPING TABLES
• Dropping tables is actually a two-steps process:
– Empty the table of any data by using the DELETE statement
– Destroy the definition of the table by using DROP TABLE statement
• Syntax:
– DROP TABLE tablename [RESTRICT | CASCADE];
• The RESTRICT or CASCADE clause refers to what happens when
other objects (such as Views) exist that depend on this table.
– If RESTRICT is specified then DROP is disallowed.
– If CASCADE is specified then dependent objects are automatically dropped as
well.
• In the end, table are no longer recognized in the schema.
18
End of
Overview Relational Databases and SQL
Thank you
19