HOLMES
INSTITUTE
FACULTY OF
HIGHER
EDUCATION
UNDERGRADUATE
PROGRAM
Assessment Details and Submission Guidelines
Trimester T3 2018
Unit Code HS2021
Unit Title HS2021 Database Design and Use
Assessment Type Individual Assignment
Assessment Title Oriental Bank Database Design
Purpose of the assessment (with
ULO Mapping)
a) Design and implement relational database for an information system by considering business need and context of the systems.
b) Retrieve and present information from cross functional areas information using SQL tools and techniques.
Weight 15% of the total assessments
Total Marks 15
Word limit 1000-1500 words
Due Date Week 07
Submission Guidelines Report structure and assignment submission:
• Content of your report
- Prepare a report which will detail an E-R diagram.
- Explain the normalization process of the ER diagram in details.
- Write down all the assumptions you have considered while preparing the ER diagram.
- Include all the CREATE TABLE statements in the Appendix.
• All work must be submitted on Blackboard by the due date along with a completed Assignment Cover Page.
• The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body) font and 2 cm margins on all four sides of your page with appropriate section headings.
• Reference sources must be cited in the text of the report, and listed appropriately at the end in a reference list using Harvard or IEEE referencing style.
Prepared by: Dr. Mahmoud Bakkar November, 2018
Assignment Description
1. Below is the entity-relationship diagram for the banking database application, there are four relations/tables in their database describing banking application details, details of projects, work assigned for each branch and account, namely, BRANCH, ACCOUNT, CUSTOMER and TRANSACTION. The relationship diagram for these relations are given below.
(5 Marks)
A. Write one paragraph describing the E-R diagram above in your own language
B. Implement above database on Oracle 12c SQL developer. You are required to create the above 4 tables with the given attributes. Name the database as ‘OrientalBank’.
C. Enter at least 10 records data for each table in the database you created in (a) above. Here you have to enter data into all 4 tables.
D. Write 4 examples of report cards that can be generated using the ‘OrientalBank’ database.
E. Write down the shorthand representation (database schema) of the structure of a relational database for the above E-R diagram
F. What is a primary key? What is the primary key for each table in the ‘OrientalBank’ database?
2. Write SQL queries for the following questions and execute the queries on the ‘OrientalBank’ database you created above using Oracle 12c SQL developer.
(7 Marks)
A. Creating the four tables.
B. How many accounts have a balance greater than $500?
C. Display the Name, of all accounts with a balance greater than the average balance.
D. Find the ID, Name, Transaction number, date and type of the second account.
E. Find details of customers having their account in the second bank branch.
F. List the numbers and names of all customers.
G. List the complete account table.
H. List the number and name of all customers that are opened their accounts in the second branch and that have a balance less than $1,000.
I. List the number and name of all customers that are opened their accounts in the second branch or that have a balance less than $1,000.
J. List the number and name of all customers opened in the branch “Melbourne city”.
K. Find the total of the balances for all accounts.
3. Write the 1NF, 2NF, 3NF for the below data.
(3 Marks)
HEALTH HISTORY REPORT
PET ID PET NAME PET TYPE PET AGE OWNER VISIT DATE PROCEDURE
246 ROVER DOG 12 SAM COOK JAN 13/2002 01 - RABIES VACCINATION MAR 27/2002 10 - EXAMINE and TREAT WOUND APR 02/2002 05 - HEART WORM TEST
298 SPOT DOG 2 TERRY KIM JAN 21/2002 08 - TETANUS VACCINATION MAR 10/2002 05 - HEART WORM TEST
341 MORRIS CAT 4 SAM COOK JAN 23/2001 01 - RABIES VACCINATION JAN 13/2002 01 - RABIES VACCINATION
519 TWEEDY BIRD 2 TERRY KIM APR 30/2002 20 - ANNUAL CHECK UP APR 30/2002 12 - EYE WASH
Marking criteria:
Marks are allocated for each part as above:
Marking Rubric for Assignment 1 Total Marks 15
Excellent Very Good Good Satisfactory Unsatisfactory
Query writing
Skills
/2 Evidence of accurate and
well- written queries Evidence of good query writing skills. Generally relevant. Displayed reasonable query writing skills. Demonstrated
little evidence of understanding the topic.
Identification
/3 Demonstrat
ed excellent ability to think critically. Demonstrated
an ability to think critically. Demonstrated reasonable ability to think. Demonstrat ed some ability to think critically but not complete. Did not
demonstrate
ability to think critically.
Analyse
/2 Logic is clear and easy to follow. Consistency logical and convincing. Mostly consistent logical and convincing. Adequate cohesion and conviction. Arguments are confused and disjointed.
Design
/Presentation
/5 All elements are present and very well integrated. Most of the components present and integrated. Components present and mostly well integrated. Most components present. Report lacks structure.
Evaluate/
justification
/3
Excellent outstanding effort made. Extremely good effort. Good effort made but not outstanding. Made some effort. Very little attempt. Lazy effort with inaccuracies.