UNIVERSITY OF INFORMATION TECHNOLOGY Lab Test
FACULTY OF INFORMATION SYSTEMS
Course Title: Principles of Database Systems
Class: CS5423.L22.CTTT. Duration: 60 minutes Bank Database:
Customers (customer_id, customer_name, date_of_birth, phone, address, date_became_customer)
Description: each customer has a unique id. The customer also has a name, a birthdate, a phone, an
address and a ‘date became customer’.
Account_Types (account_type_code, account_type_name).
Description: each account type has a unique code (account_type_code). The account type also has
a name. Account type name is Checking, Saving…
Accounts (account_id, date_opened, date_closed, current_balance, account_type_code, customer_id)
Description: each account has a unique id (account_i )
d . The account also has an opened date, closed
date, current balance, account type code and customer id.
Transaction_Types (transaction_type_code, transaction_name)
Description: each transaction has a unique code. The the transaction type also has a name.
Transaction_Messages (transaction_id, transaction_date, amount, location, account_id, transaction_type_code)
Description: each transaction has a unique id. The transaction also has a date, amount of money,
location, account id, transaction type code. Table Column Datatype Detail customer_id char(4) not null customer id customer_name varchar(50) customer name date_of_birth smalldatetime birthday Customers phone varchar(50) phone address varchar(100) address date_became_customer smalldatetime date became customer account_type_code char(4) not null account type code Account_Types account_type_name varchar(40) account type name account_id char(4) not null account id date_opened smalldatetime opened date date_closed smalldatetime closed date Accounts current_balance money current balance account_type_code char(4) account type code customer_id char(4) customer id transaction_type_code char(4) not null transaction type code Transaction_Types transaction_name varchar(40) transaction type name transaction_id char(10) not null transaction id Transaction_Messages transaction_date smalldatetime transaction date amount money amount of money location varchar(100) location account_id char(4) account id transaction_type_code char(4) transaction type code Using MSSQL Server A. Write DDL, DML statements:
1. Create database, create table, and define the keys (primary key, foreign key) of the table s above.
2. Add account_comment column into Accounts table.
3. Drop account_comment column from Accounts table .
4. Insert data into three table. Do not insert null data. - Customers: 2 rows - Account_Types: 1 row, - Accounts: 1 row.
5. Update customer_name, date_of_birth of a Customer (only update one row data). The new data is: - customer_name: your name.
- date_of_birth: your birthday.
B. Write integrity constraints:
6. Transaction_name of a transaction type is only deposit, withdrawal. Write a check constraint.
7. When a customer wants to withdraw or deposit money, a new row will be inserted into
Transaction_Messages table. If withdrawing money (transaction_name=’withdrawal’), the
current_balance of this account will be reduced by an amount. If depositing money
(transaction_name=‘deposit’), the current_balance of this account will be increased by an
amount. Write a trigger to insert data into Transaction_Messages table. C. Write SQL statements:
8. Write a query to display the person who became customer on Dec, 2000. Sort by customer name.
9. Find the accounts that make a transaction on both dates:1/1/2000 and 2/1/2000. Display the account_id.
10. Display the customer id, name, address who opened the account in 2006 but did not open the acount in 2007.
11. Find the cutstomers who have all type of account. Display the customer_id, customer_name.
12. Write a query for counting number of accounts per customer. Display the customer_id,
customer_name, date_of_birth, phone, numAccount.
13. Display the customer id, name and how many times this customer withdraw the money in 2020.
14. Each account type, find the accounts that have the highest current balance (current_balance).
Display the account_type_code, account_id, current_balance.