Worksheet 3
DDLs in T_SQL
1. Bank schema
create table Bank(
code varchar(50),
name varchar(50) not null ,
descrip varchar(50),
constraint pk_Bank primary
key(code)
)
create table Branch (
bank_code varchar(50),
branch_no varchar(50),
name varchar(50),
addr varchar(50),
constraint pk_Branch primary
key(bank_code,branch_no),
constraint fk_Branch foreign
key(bank_code) references Bank (code)
)
create table Account (
acc_no varchar(50),
balance float,
bank_code varchar(50),
branch_no varchar(50),
type_code varchar(50),
constraint pk_Account primary
key(acc_no),
constraint fk1_Account foreign
key(bank_code,branch_no)
references Branch (bank_code,branch_no),
constraint fk2_Account foreign
key(type_code) references
AccountType (code),
constraint chk_Account check (balance
>= 0)
)
create table AccountType (
code varchar(50),
name varchar(50),
decrip varchar(50),
constraint pk_AccountType primary
key(code)
)
create table Saving (
code varchar(50),
interest_rate float,
constraint pk_Saving primary key(code),
constraint fk_Saving foreign key(code)
references AccountType (code),
constraint chk_Saving check
(interest_rate >= 0)
)
create table CurrentAcc (
code varchar(50),
max_cheque float,
constraint pk_CurrentAcc primary key(code),
constraint fk_CurrentAcc foreign key(code)
references AccountType (code)
)
create table Balongs_to (
acc_no varchar(50),
cus_no varchar(50),
constraint pk_Balongs_to primary
key(acc_no,cus_no),
constraint fk_Belongs_to foreign key(acc_no)
references Account (acc_no),
constraint fk2_Belongs_to foreign key(cus_no)
references Customer (cus_no),
)
create table Customer (
cus_no varchar(50),
NIC varchar(15),
name varchar(50)not null,
addr varchar(50),
phone varchar(12),
PIN char(4),
constraint pk_Customer primary key(cus_no),
constraint chk_Customer check (PIN like
'[0-9][0-9][0-9][0-9]'),
constraint chk2_Customer check ( NIC like
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][V,X]')
)
create table Has (
acc_no varchar(50),
trans_id varchar(50),
Trans_type varchar(20),
constraint pk_Has primary key(acc_no),
constraint fk_Has foreign key(acc_no)
references Account (acc_no),
constraint fk2_Has foreign key(trans_id)
references Transactions (trans_id),
constraint chk_Has check(Trans_type
in('Credit','debit'))
)
create table Transactions (
trans_id varchar(50),
amount float,
descrip varchar(50),
executed_by varchar(50),
dateAndtime datetime default
getdate(),
constraint pk_Transactions primary
key(trans_id),
constraint chk_Transactions check(executed_by
in('ATM','Teller','Bank','Standing order','Cheque','On-Line','Other'))
)
2. Library schema
create table Book (
item_no varchar(50),
title varchar(50) not null,
decrip varchar(50),
constraint pk_Books primary key(item_no),
constraint chk_Books check(item_no like 'B%')
)
create table Written_by (
item_no varchar(50),
author_id int,
constraint pk_Written_by primary
key(item_no,author_id),
constraint fk_Witten_by foreign key(item_no)
references Books (item_no),
constraint fk2_Witten_by foreign
key(author_id) references Author (author_id)
)
create table Author (
author_id int IDENTITY(1,1),
fname varchar(50),
lname varchar(50),
other varchar(50),
constraint pk_Author primary key(author_id)
)
create table TextBooks (
item_no varchar(50),
semester varchar(20),
year int,
section varchar(50),
constraint pk_TextBooks primary
key(item_no),
constraint fk_TextBooks foreign
key(item_no) references Books (item_no),
constraint chk_TextBooks check(section
in('Lecture','Lab','Tutorial')),
constraint chk2_TextBooks check(year
between '1999' and '3000')
)
create table CopyText (
access_no varchar(50),
item_no varchar(50),
status varchar(15),
constraint pk_CopyText primary
key(access_no),
constraint fk_CopyText foreign
key(item_no) references TextBooks (item_no),
constraint chk_CopyText check(status
in('Borrowed','Stolen','Available'))
)
create table PublishedBooks (
item_no varchar(50),
edition varchar(15),
constraint pk_PublishedBooks primary
key(item_no),
constraint fk_PublishedBooks foreign
key(item_no) references Books (item_no)
)
create table PublishedBooksType (
ISBN varchar(50),
item_no varchar(50),
cover varchar(15),
published_yr int,
publisher varchar(50),
constraint pk_PublishedBooksType primary
key(ISBN),
constraint fk_PublishedBooksType foreign
key(item_no) references PublishedBooks (item_no),
constraint chk_PublishedBooksType
check(cover in('Soft Cover','Hard Cover')),
constraint chk2_PublishedBooksType
check(published_yr between 1000 and 3000)
)
create table CopyPublishedBooksType(
access_no varchar(50),
ISBN varchar(50),
status varchar(15),
constraint pk_CopyPublishedBooksType primary
key(access_no),
constraint fk_CopyPublishedBooksType foreign
key(ISBN) references PublishedBooksType (ISBN),
constraint chk_CopyPublishedBooksType
check(status in('Borrowed','Stolen','Available'))
);
create table Periodicals (
item_no varchar(50),
title varchar(50) not null,
decrip varchar(50),
frequency varchar(50),
publisher varchar(50),
type varchar(10),
constraint pk_Periodicals primary
key(item_no),
constraint chk_Periodicals check(item_no like
'P%'),
constraint chk2_Periodicals check(type
in('Magazine','Newspaper'))
)
create table PeriodicalType (
item_no varchar(50),
date datetime,
constraint pk_PeriodicalType primary
key(item_no,date),
constraint fk_PeriodicalType foreign
key(item_no) references Periodicals(item_no)
)
create table CopyPeriodicalType (
access_no varchar(50),
status varchar(15),
item_no varchar(50),
date datetime,
constraint pk_CopyPeriodicalType primary
key(access_no),
constraint fk_CopyPeriodicalType foreign
key(item_no,date) references PeriodicalType(item_no,date),
constraint chk_CopyPeriodicalType
check(status in('Borrowed','Stolen','Available'))
)
create table
SoftwareAndManuals (
item_no varchar(50),
title varchar(50) not null,
decrip varchar(50),
manufacture varchar(50),
version varchar(50),
constraint pk_SoftwareAndManuals primary
key(item_no),
constraint chk_SoftwareAndManuals
check(item_no like 'S%')
)
create table CopySoftwareAndManuals (
access_no varchar(50),
status varchar(15),
item_no varchar(50),
constraint pk_CopySoftwareAndManuals primary
key(access_no),
constraint fk_CopySoftwareAndManuals foreign
key(item_no) references SoftwareAndManuals (item_no),
constraint chk_CopySoftwareAndManuals
check(status in('Borrowed','Stolen','Available'))
)
3. SLIIT schema
create table Campus (
code varchar(50),
name varchar(50) not null,
addr varchar(50),
constraint pk_Campus primary key(code)
)
create table Batch (
id varchar(50),
groupNo varchar(50) not null,
maximum int,
section varchar(50),
code varchar(50),
prg_code varchar(50),
constraint pk_Batch primary key(id),
constraint fk_Batch foreign key(code)
references Campus (code),
constraint fk2_Branch foreign key(prg_code)
references Program (prg_code)
)
create table Program (
prg_code varchar(50),
name varchar(50),
special varchar(50),
descrip varchar(50),
constraint pk_Program primary key(prg_code),
)
create table Batch_Semester (
batch_id varchar(50),
acadamic_sem_no int,
constraint pk_Batch_Semester primary
key(batch_id,acadamic_sem_no),
constraint fk_Batch_Semester foreign key(batch_id)
references Batch(id),
constraint fk2_Batch_Semester foreign
key(acadamic_sem_no) references Acadamic_Semester (acadamic_sem_no),
)
create table Semester (
prgm_code varchar(50),
sem_no int,
semester varchar(50) not null,
year int not null,
constraint pk_Semester primary
key(prgm_code,sem_no),
constraint fk_Semester foreign key(prgm_code)
references Program (prg_code)
)
create table Acadamic_Semester (
acadamic_sem_no int,
year int,
prgm_code varchar(50),
sem_no int,
constraint pk_Acadamic_Semester primary
key(acadamic_sem_no),
constraint fk_Acadamic_Semester foreign
key(prgm_code,sem_no) references Semester (prgm_code,sem_no),
)
create table Student (
reg_no varchar(50),
name varchar(50),
address varchar(50),
phone varchar(10),
constraint pk_Student primary key(reg_no)
)
create
table Register (
reg_no
varchar(50),
batch_id
varchar(50),
acadamic_sem_no
int,
date
datetime DEFAULT GETDATE(),
constraint
pk_Register primary key(reg_no,batch_id,acadamic_sem_no),
constraint
fk_Register foreign key(reg_no) references Student (reg_no),
constraint
fk2_Register foreign key(batch_id,acadamic_sem_no) references Batch_Semester
(batch_id,acadamic_sem_no)
)
Worksheet 4
Objective: Moving Data
Export the data given in the excel sheets to the Library and Bank databases which is developed by you. You may use Data Transformation Services (Import/Export Wizard) and T-SQL scripts to do this. If you write any SQL or T-SQL scripts to load data to the tables, you should attach them to your solution.
1. Enter the data into an Excel spreadsheet
- First, enter the data into an Excel spreadsheet.
- In this example, the default spreadsheet name, Sheet1, has been retained, but if you rename the sheet (to the name you want for the table in the database, for example), that name will be automatically used in the import process (at Step 6).
Small demonstration Excel spreadsheet ready for transfer to a SQL database
2. Start the SQL Import and Export Wizard
- Next, in Windows, start the Import and Export Wizard at Start / All Programs / Microsoft SQL Server 2008/ Import and Export Data.
- The Welcome page appears. Click Next.
SQL Import and Export Wizard in the Windows Start menu
OR Right click on the you needed database in the Object Explorer, select Tasks, then Import Data from the context menu to launch the Import Wizard.
3. Chose your Excel spreadsheet as the Data Source
- In the Data Source dropdown of the Choose a Data Source page, select Microsoft Excel.
- In the Excel file path box, specify the file path to the Excel spreadsheet.
- Select the the version in the Excel version dropdown.
- Make sure that First row has column names is checked.
- Click Next.
4. Chose your SQL database as the destination
- In the Destination dropdown list, accept the default setting of SQL Server Native Client 10.0.
- In the Server name dropdown list, enter the name of the server. The example is a remote server, so the IP address and port of the server were specified.
- Chose the Authentication type. The example is a remote server, so SQL Server authentication, with a user name and password, is required.
- In the Database dropdown list, select or type the name of the database.
- Click Next.
5. Specify how to copy the data
- The default option, Copy data from one or more tables or views, works for this example.
- If you want to try the second option, Write a query to specify the data to transfer, the followinglolcode snippet may be instructive:
CAN HAS SQL?
DBASE IZ GETDB('db/demo.db')
FUNNAHS IZ DBUCKET(&DBASE&,"CAN I PLZ GET * ALL UP IN lollit")
IM IN UR FUNNAHS ITZA TITLE
VOTEZ IZ &TITLE#ups& - &TITLE#downs&
6. Select the source tables and views
- The default settings as shown work in this example.
- In the Destination column, you can specify a different name for the table in the SQL database if you choose.
- Click Preview to see how your data will appear in the destination table.
- You can click Edit Mappings to change how your data is assigned at the destination table, but it shouldn't be necessary in this example since you entered the data into the Excel spreadsheet yourself.
- Click Next.
7. Run the "Package"
The following message appears at the bottom of the Run Package page: In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation. A "package" is all of the settings that your have configured so far. In the commercial versions of SQL Server, you can save the package for reuse so you don't have to enter all of the settings the next time you run the wizard. In the Express (free) version of Microsoft SQL Server 2008 Management Studio, you must re-enter all of the settings every time you run the SQL Server Import and Export wizard.
8. Verify that the package executed successfuly
- Click Report to view useful information about the data transfer process.
- Click Close.
9. View the new table in SQL Server Management Studio
- View your new table by opening Microsoft SQL Server 2008 Management Studio at Start / All Programs / Microsoft SQL Server 2008/ Import and Export Data.
The new table, dbo.Sheet1$, in Microsoft SQL Server Management Studio
You can also use the Import and Export Wizard to export data from a table in SQL Server to an Excel spreadsheet.
Worksheet 5
--Exercise 1.
Create a view in T-SQL (called Cust_Inf containing the following
information of customers:
Customer’s number, NIC, name,
His/her account information: account number and account type’s name
Using the view above, query Mr. Sampath
Wijesinghe’s account information.
create table
Customer(
customer_number
int primary key,
name
varchar(100),
nic
varchar(10),
);
create table
Account(
account_number
varchar(100) primary key,
account_type
varchar(100),
customer_number
int,
amount
float,
constraint
fk1 foreign key (customer_number) references Customer(customer_number),
);
create view
Cust_Inf(customer_number,name,nic,account_number,account_type)
as
select
c.customer_number,c.name,c.nic,a.account_number,a.account_type
from Customer
c,Account a
where
c.customer_number = a.customer_number
select *
from
Cust_Inf
-- Exercise 2
A policy of the bank is that a single customer cannot have more than 5
accounts. Write a DML trigger to ensure that this policy is enforced. Note that
the customer can have more than 5 accounts in total from different banks.
Test your trigger with sample data.
create
trigger trigger1
on Account
for
insert,update
as
begin
declare @customer_number int
declare @count_ int
select @customer_number =
i.customer_number
from Inserted i
select @count_ = COUNT(*)
from Account
where customer_number = @customer_number
if(@count_>5)
begin
rollback transaction
end
end
-- Exercise 3
Write a stored procedure, which takes the account number and returns
the number of cheques processed for that account during the current month.
Test your stored procedure with
sample data.
create table
cheque
(
chequeNo int
primary key ,
dates
datetime,
account_number
varchar(100),
constraint
fk2 foreign key (account_number) references Account(account_number),
);
select *
from cheque
create
procedure count_cheques(@number varchar,@noOfCheque int output )
as
begin
declare @months int
select @months=month(getdate())
select @noOfCheque = COUNT(*)
from Account a,cheque c
where a.account_number=@number and
a.account_number=c.account_number and c.dates = @months
end;
drop
procedure count_cheques
-- Exercise 4:
Write a stored procedure that transfers funds between accounts. The
input parameters include account number of source account, account number of
destination account and amount to be transferred. Ensure that the transaction
occurs within a single transaction. Also, ensure that appropriate information
is updated in the Transaction table.
Test your stored procedure with
sample data.
create
procedure transactions (@sourseAccount int ,@destinationAccount int,@amount
float)
as
begin
declare @sourseAmount float
declare @destinationAmount float
select @sourseAmount=amount
from Account a
where
a.account_number=@sourseAccount
select @destinationAmount=amount
from Account b
where b.account_number=@destinationAccount
if(@sourseAmount>@amount)
begin
SET
@destinationAmount=@destinationAmount+@amount
SET @sourseAmount =
@sourseAmount-@amount
update Account
set amount=@sourseAmount
where account_number
=@sourseAccount
update Account
set
amount=@destinationAmount
where account_number
=@destinationAccount
return;
end
else
begin
rollback transaction
end
end;
