Assessment - Managing Data in Databases
Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop skills in managing data in databases and to gain understanding of data model development and implementation using a commercially available database management system development tool.
On completion of this assignment students will be able to:
a. Describe issues related to the integrity and security of database (DB) systems. (This will assess in the Assignment 1 Quiz)
b. Make an informed and critical assessment of database management systems (DBMS).
c. Develop data models and implement DB systems.
d. Demonstrate skills in building a database application using a commercially available database management system development tool.
Description of this assignment: This assignment consists of two parts:
Part: Write SQL Queries to extract data from data tables and 2 questions on normalisation.
1. Why do we need to nomalise data in databases?.Briefly explain your answer.
2. The schema for the BankDBdatabase is given below.
branch(branch_name,branch_city, assets)
customer(customer_name,customer_street, customer_city)
loan(loan_number,branch_name, amount)
borrower(customer_name,loan_number)
account(account_number,branch_name, balance)
depositor(account_number,customer_name, )
Description of the schema
branch(branch_name,branch_city, assets)-keeps track of bank branch information
customer(customer_name, customer_street, customer_city)-keeps track of personal information about customers.
loan(loan_number,branch_name, amount)keeps track of loan information of each bank branch
borrower(customer_name, loan_number)- keeps track of customers who borrow an amount of money from the bank
account(account_number, branch_name, balance)keeps track of customer accounts
depositor(account_number, customer_name)-keeps track of customers who creates accounts with the bank. This is a bridging table for customer table and the accounts table.
Write SQL queries for the following questions(do not use QBE )and execute the queries by creating the above database on MS Access. Include screen shots of the outputs and all SQL statements you used to answer following questions (2(a) - 2(e)).
(3 marks for each screen shot & remaining marks for the SQL query)
a. Display the Branch name of all banksin the city "Ringwood". Your result set should be sorted on the alphabetical order of theBranch name.
b. Display all account numbers in the account table which are created at the branch "Chase shopping" and account balance is greater than $2000.
c. Display all account numbers in the account table which are created at the branch "Chase shopping" and situated in the city "Ringwood".
(Hint Join account and branch tables)
d. Count the number of customers registered with the branch" Chase shopping".
(Hint: Join 2 tables account, depositor)
e. Create a new table named "Loans" that includes Customer_Name, branch_name and amount(loan)
Hint : you need to join 3 tables depositor,borrower and loan
3. An agency called Hired You supplies part-time/temporary staff to hotels throughout Australia. The table shown below lists the time spent by agency staff working at two hotels.
The Emp_ID is unique for each employee.
|
Emp_ID
|
Contract_No
|
HoursAssigned
PerWeek
|
Hotel_contact_No
|
HotelNo
|
HotelLocation
|
|
1177567
|
MIT1009
|
17
|
045566790
|
H12
|
Ringwood
|
|
1256788
|
MIT10010
|
20
|
056678954
|
H40
|
Melbourne CBD
|
|
2246769
|
MIT10011
|
30
|
045566790
|
H12
|
Ringwood
|
|
2254678
|
MIT10012
|
30
|
056678954
|
H40
|
Melbourne CBD
|
|
3758956
|
MIT10013
|
25
|
045566790
|
H12
|
Ringwood
|
|
4237890
|
MIT10014
|
36
|
056678954
|
H40
|
Melbourne CBD
|
a. Describe types of anomalies that may occur in the abovetable that has redundant data. Provide examples of insertion, deletion, and modification anomalies.
b. Propose a suitable solution to overcome anomalies identified in 3(a). You need to use dependency statements to show functional dependencies in this table.