SRI LANKA INSTITUTE OF INFORMATION TECHNOLOGY

Database Management Systems II (215)

Practical Workbook
Year 2 - Semester II 2011

Student Reg. No. Student Name Campus

: : :

Lecture-in-charge

: Prasanna S. Haddela Dept. of Information Technology, SLIIT

Instructions to students: This book must be brought for all the lab classes. The attached grading sheet complete with instructor recommendations must be handed over at the time of the viva. This book consists of three Assignments. Complete the assigned exercises as part of the assignments on a weekly basis. Demonstrate to the lab instructor that the work has been done during each practical session and complete the grading sheet. Otherwise, it will be considered as not attempted in class. All answers should be written by hand. Do not attach printed answers to the book. A copy of each completed assignment must be uploaded into the course web available at http://moodle.sliit.lk

Sri Lanka Institute of Information Technology

Laboratory Assignment 1
Session 1 Objective: Designing E-ER Models for data requirements.
Exercise 1: Bank Data Requirements Consider a company called ABC Consolidated which contains many banks under it. Each bank contains a code, name and a registration number. The code and name are unique. The bank has many branches which contain a branch number, branch name and an address. The branch number is unique for each bank. Each bank contains many accounts. An account contains an account number, balance, a customer and an account type. Each account type contains a name, a code and a description. Account code is unique each account type. Account type may be Savings or Current. The Savings account type contains an interestrate. The Current account type has max-cheques which pertains to maximum cheques that can be written per month. Each customer has a customer number, NIC number, name, address, phone and PIN. Both NIC and customer number are unique. There are many transactions processed in a bank. Each transaction has a transaction id, date & time of transaction, amount and responsible party or application for the transaction (i.e. bank, ATM, teller, cheque, etc.) and description (interest calculation, fund transfer, cheque number etc.) Each transaction has one or many accounts that it affects and the type of affect that the transaction (either a credit/debit) has on the account.

Draw an E-ER diagram for the above-mentioned requirements.

Sri Lanka Institute of Information Technology

Exercise 2: Library Data Requirements The requirements for the library catalog are as follows:

The library contains a list of Items Every item has an item number, title, and a description Every item is identified by the item number Items are classified into the following categories: books, periodicals, and software & manuals Every book has authors An author has an author_id (unique), a first name, last name and other names. An author can write many books and a book can have many authors. Every periodical has a publisher and frequency (e.g., daily, weekly, monthly, quarterly and yearly) Every software & manual has a manufacturer and a version Books are further classified as Text Books and Published Books Periodicals are classified into Magazines and NewsPapers Every Text Book has a semester, year and section (such as lecture, lab or tutorial) Every Published Book has an edition Published Book has a Published Book Type depending on publisher of the book (for example, same book can be published by different publishers such as US edition by McGrawHill and Indian edition of the book by TATA McGrawHill) Published Book Type is identified by ISBN number. Also, Published Book Type contains Publisher, Cover (softback or hardback) and Year Published. Periodical also has a Periodical Type which contains the date that the periodical was issued (for example, November issue of Sports Illustrated). The items, (i.e. Text Book, News Papers, Published Book Type, Periodical Type and Software & Manuals), can have multiple copies Copy of Item. Copy of Item is identified by the access_no. Copy of Item contains a status field which indicates whether the book is borrowed, lost, stolen or available.

Draw an E-ER diagram for the above-mentioned requirements.

Sri Lanka Institute of Information Technology

Laboratory Assignment 1 contd.

Session 2 Objective: Designing E-ER Models for data requirements.
SLIIT Batch Registration Data Requirements The requirements for SLIIT batch registration are as follows: There are many campuses at SLIIT. Each campus has a code, name and an address. Code and name are unique. SLIIT offers many programs of study (e.g., Certificate in Information Technology, Diploma in Information Technology, etc.). Each program has a code (unique), name, description and specialization. There are semesters in each program of study. Each semester contains a semester number (such as 1, 2, 3, etc.), a semester (either Semester 1 or Se`mester 2) and year (Year 1, Year 2, Year 3 or Year 4). Semester number is unique for the program of study and increases monotonically with the level of study. An academic year consists of many academic semesters. Each academic semester belongs to some semester of a program of study. Academic semester consists of an academic year and an academic semester number (unique). There are many batches at SLIIT. Each batch contains an id (unique), group number (C1, M1, etc.), maximum number of students a batch can contain and a section (either Weekday or Weekend). A batch has a campus and a program of study. There may be many batches that may follow a single academic semester (for example, Semester 1, 2004 can have both M1, and M2 batches following it). A batch may follow many academic semesters during its program of study (for example, M1 batch may follow semester 1, 2004 and semester 2, 2004). There are students at SLIIT. Each student has a registration number (unique), name, address and a phone number. A student registers himself/herself to a particular batchs academic semester (For example, a student may register to M1 batch during semester 1, 2004). A date registered should be maintained.

Draw an E-ER diagram for the above-mentioned requirements (Hint: You may need to use aggregation to model the requirements).

Sri Lanka Institute of Information Technology

Laboratory Assignment 1 contd.

Session 3
Objective: Mapping E-ER Model to Relational Model Convert the E-ER diagrams to relational schema. Use model answers given during the practical session for the following data requirements (Exercises of the session 1 and session 2). 1. Bank Data Requirements 2. Library Data Requirements 3. SLIIT Batch Registration Data Requirements

Sri Lanka Institute of Information Technology

Laboratory Assignment 1 contd.

Session 4
Objective: Use of normalization to refine the relational schema For all relational schemas (presented in session 3s answers), do the following: 1. Identify the keys for each relation 2. State the functional dependencies for each relation 3. Convert the relational schema to Boyce-Codd Normal Form (BCNF)

Sri Lanka Institute of Information Technology

Laboratory Assignment 1 contd.

Session 5
Objective: DDLs in T-SQL Create the relational schemas in session 4 using T-SQL. Write your script and save your answer as Bank.sql, Library.sql and SLIIT.sql files. Use appropriate data types for columns of table. The following constraints exist for the schema. Use appropriate constraint mechanism (such as CHECK constraint, etc.) to enforce the rules stated:

Bank Schema: Ensure that the interest_rate of Savings account is a non-negative number (>= 0). Ensure that the customers PIN number is a four digit (0-9) number Ensure that the customers NIC number contains 10 digits Ensure that the customers name is not null. Ensure that the balance in an account is always non-negative number (>= 0). In the Transaction table, the default value for date&time should be the system datetime. In the Transaction table, the value for by attribute must be one of the following: ATM, Teller, Bank, Standing Order, Cheque, On-Line or Other. The type descriptive attribute of the has relationship between Transaction table and Account table should have one of the following values: credit or debit.

Library Schema: The title attribute of Books, Periodicals and Software_Manuals cannot contain a null value. Ensure that item_no of Books, Periodicals, Software_Manuals start with B, P, S respectively Use IDENTITY property for author_id attribute of Authors table to ensure that a unique id is generated for authors. Ensure that Magazine or Newspaper are the only allowed values for type attribute in Periodicals table. Ensure that the book_section attribute of Text_Books can be only Lecture, Lab, or Tutorial values. Ensure that the year attribute of Text_Books is between 1999 and 3000. Ensure that the cover attribute of Published_Book_Type can only have Soft Cover or Hard Cover values.

Sri Lanka Institute of Information Technology

Ensure that the yr_published attribute of Published_Book_Type is between 1000 and 3000. Ensure that the status attribute of Copy_Item is only Borrowed, Lost, Stolen or Available.

SLIIT Schema: Ensure that the SLIIT students name is not null. The default value for date descriptive attribute of the registers relationship should be the system date. Ensure that semester and year attributes of Semester table are not null. Ensure that groupNo of Batch table is not null.

End of Assignment 1

Sri Lanka Institute of Information Technology

Laboratory Assignment 2
Session 6
Objective: Data Entry Export the data given in the excel sheet to the database. You may use Data Transformation Services (Import/Export Wizard) to do this. If you write any scripts to load to tables, you should save them as DataEntry.sql

Sri Lanka Institute of Information Technology

Laboratory Assignment 2 contd.

Session 7
Objective: DB Functions: Views, Triggers, and Stored Procedures Exercise 1: Create a view in T-SQL (called Cust_Inf containing the following information of customers: Customers number, NIC, name, His/her account information: account number and account types name Using the view above, query Mr. Sampath Wijesinghes account information. 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. 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. 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. Save all your scripts in session7.sql

Sri Lanka Institute of Information Technology

Laboratory Assignment 2 contd.

Session 8
Objective: Views and Triggers Create the following database schema only if not exist, CREATE TABLE Software_Manuals ( item_no varchar(40) PRIMARY KEY, title varchar(100), description varchar(100), version varchar(30), manufacturer varchar(50) ); CREATE TABLE Copy_SW_Manual ( access_no integer PRIMARY KEY, item_no varchar(40) REFERENCES Software_Manuals ); CREATE VIEW VW_SW_MANUAL (title, manufacturer, version, access_no) AS SELECT SM.title, SM.manufacturer, SM.version, CSM.access_no FROM Software_Manuals SM, Copy_SW_Manual CSM WHERE SM.item_no = CSM.item_no Create an INSTEAD OF trigger (called tr_SW_VW_INSERT) on the view created above. The trigger should be fired for an INSERT statement and should do the following: i. If the inserted row contains an access_no that exists in the table, then update the version, title & manufacturer columns of Software_Manuals table with the inserted data ii. Else, if the title, manufacturer and version exists in the Software_Manuals table, insert a new row to Copy_SW_Manual table with its item information referencing the existing row of the Software_Manuals table iii. Else, insert rows to Software_Manuals and Copy_SW_Manual tables to reflect the new information.

End of Assignment 2 Sri Lanka Institute of Information Technology

Laboratory Assignment 3
Session 9
Objective: Indexes and Query Plans Note: In SQL Server 2008, by default, a clustered index is created for primary key and a nonclustered index is created for each attribute(s) having unique constraints. A. Consider the following queries: Find the PIN number given the customer number. Find all accounts belonging to the customer given the customer number Given the customer number and account number, return the balance Find the total amount of the transactions for each responsible party or application (i.e. bank, ATM, teller, cheque, etc.). Find the PIN number given the customer NIC. Write SQL statements to return the information needed. Provide sample inputs. B. Consider the query plans generated by SQL Server for the above mentioned queries. (Hint: In SQL Server 2008, you can view the execution plan by enabling Display Estimated Plan or Include Actual Execution Plan options on the query window). C. Assume that the workload given in A is frequent. Create appropriate indexes that you think will improve the performance of the above workload. (Hint. In SQL Server 2008, you can create indexes using the CREATE INDEX statement. Use Books Online and other references to find out more about SQL Servers indexes). D. Consider the query plans generated by SQL Server 2008 after the indexes. E. Write a brief summary of your findings.

Sri Lanka Institute of Information Technology

Laboratory Assignment 3 contd.

Session 10
Objective: Transactions and Concurrency Control Simulations In your bank database, create two accounts, with account numbers 1234567899, 1234567898 and place Rs. 100 in each of them. You may choose the accounts to be of any type belonging to a single customer. Now, write a script to transfer funds from the accounts. The accounts numbers and amount to transfer must be declared as variables @soucreAcc, @destAcc and @fundsToTransfer respectively. In your fund transfer script, ensure that the destination account is credited prior to deducting from the source account. You should use appropriate syntax to ensure proper transaction mechanisms for the fund transfer (i.e. BEGIN TRANSACTION/COMMIT TRANSACTION). Save your script as session9.sql. Important: It is important to set the variable XACT_ABORT (i.e. SET XACT_ABORT ON) prior to executing transactions in order to simulate Atomicity property. Also, ensure that the constraint in balance >= 0 is present in the account table. (Note: You can add constraints using ALTER TABLE). Simulation 1: Atomicity Input: @sourceAcc = 1234567899 @destAcc = 1234567898 @ fundsToTransfer = 150.00 Expected Behavior: The system should abort the transaction (constraint balance > 0 - is violated). The account balances are not changed. Verify this behavior. Briefly explain this simulation result focusing on the reason for its behavior.

Sri Lanka Institute of Information Technology

Simulation 2: Concurrency Control Open two database connections (say A and B). Load the Lab7.sql script to both applications. Use the inputs of simulation 1 for both scripts. In Database Connection A: Execute only the part of the script till the first update statement In Database Connection B: Execute only the part of the script till the first update statement. Expected Behavior: The script in B has not completed execution. The transaction is waiting for the release of locks from As script. Then complete the rest of script in A. Expected Behavior: The update in B completes Then execute a SELECT * FROM Account in A. Expected Behavior: The query executes and waits until B releases the locks Now execute the rest of Bs transaction. Expected Behavior: As select statement completes and results are output. Briefly explain these simulation results discussing the reasons for their behavior.

Sri Lanka Institute of Information Technology

Laboratory Assignment 3 contd.

Session 11
Objective: Transactions and Concurrency Control Simulations Simulation of Deadlock

In this lab assignment, you need to simulate a deadlock occurring using SQL Server 2008. Step 1: Consider a deadlock scenario using two or more transactions Step 2: Write SQL script for each transaction Step 3: Execute a part of each transaction until the deadlock occurs. (Hint: Use a separate database connection for each transaction). Hint: Use SERIALIZABLE isolation level for each transaction.

Expected Behavior: A victim transaction is aborted by the SQL Server with a similar message: Msg
1205, Level 13, State 56, Line 1

Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Explain your experiment in detail.

End of Assignment 3

Sri Lanka Institute of Information Technology

Grading Sheet
Student Reg. no. : Student name : Date Comments
Session 1

Metro Campus/Malabe Campus/ Matara Center Evaluator: W D C B A Signature

Session 2

Session 3

Session 4

Session 5

Session 6

Session 7

Session 8

Session 9

Session 10

Session 11

Final mark for the assignments:

Sri Lanka Institute of Information Technology