Database Design Report: ERDs, Table Instances, and Relationships

This report details the database design for an accounting firm, encompassing the creation of eleven tables: Branch, Manager, Clients, Job_type, Jobs, Accountant_specialise, Accountant, Mentor, Pay_level_detail, Current_Pay_level, and Higher_Pay_level. It outlines the relationships between these tables, including zero-to-many relationships between Jobs and Clients, Jobs and Accountants, and Jobs and Job_type. The report includes both logical and physical Entity-Relationship Diagrams (ERDs) and presents table instance charts, detailing each table's columns, keys, data types, and foreign key relationships. The report is designed to provide a structured and comprehensive approach to database design, and it is supported by a list of relevant references.

Assignment and Report Cover Sheet
1. Complete all of the details below and sign.
2. No assignment will be accepted unless this form is completed in full, signed and dated.
3. Hand this in to your lecturer in person or submit it electronically together with your assessment
STUDENT ID NUMBER: NAME OF STUDENT: (PRINT CLEARLY)
FAMILY NAME OTHER NAME(S)
UNIT CODE / UNIT NAME: NAME OF LECTURER:
TITLE/TOPIC OF ASSIGNMENT:
“I certify that the attached assignment is my own work and that any material drawn from other sources has been fully
acknowledged”.
Signed: Date:
PENALTIES FOR LATE ASSIGNMENTS
1. If a student is unable to submit a within-semester assessment task (i.e. assignment) on or by the due date, the penalty will be 5% per
working day. The mark will be zero after 5 working days. An exception may be granted if the student applies for an extension and
provides an Explained Absence form together with:
 Medical certificate (signed by lecturer and given to the Welcome Centre by student), or
 Written explanation (signed by lecturer and given to the Welcome Centre by student), in the case of personal circumstances which have the
potential to significantly affect the performance of the student.
Evidence must be submitted within 3 days (or at the next scheduled class if the lecturer is not available – an email to the lecturer
is also recommended).
2. Lecturers will undertake to provide feedback within 2 weeks of due date.
ACADEMIC MISCONDUCT AND PLAGIARISM
Academic misconduct of any form is unacceptable. Academic misconduct includes, but is not limited to:
 Plagiarism Fraudulently submitting work of another person
 Unauthorised collaboration;  Theft of other students' work
 Cheating in assessments  Any other fraudulent practices
"Plagiarism” means to knowingly or unknowingly present as one's own work the ideas or writings of another without appropriate
acknowledgment or referencing. This includes, but is not limited to:
• Paraphrasing text without adequately stating the source;
• Paraphrasing text inadequately with acknowledgment of the source;
• Copying another student's work; and
• Copying of visual representations (cartoons, line drawings, photos, paintings and software code)
All forms of cheating, plagiarism or collusion are regarded seriously and may result in penalties including
loss of marks, exclusion from the unit or cancellation of enrolment. Lecturers submit assessments int.
TURNITIN – a programme which scans and records your work to match it against electronic works of others on the inter
Further information see: Assessment Policy and/or the Academic Misconduct Policy at:
www.pibt.wa.edu.au/policie
========================= ========================= ==============
ASSIGNMENT RECEIPT BY LECTURE
To be completed by the student as proof of submission to the Lecturer.
UNIT TITLE: NAME OF STUDENT:
FAMILY NAME OTHER NAME(S)
LECTURER’S SIGNATURE: NAME OF LECTURER:
DATE: TOPIC OF ASSIGNMENT:

Page 2 of 12
Contents: -
1. Assumptions…………………………………………………………………………………………………………………..3
2. Logical ERD…………………………………………………………………………………….................................5
3. Physical ERD……………………………………………………………………………………………………………………6
4. Table instance charts……………………………………………………………………………………………………..7
5. References……………………………………………………………………………………………………………………12

Page 3 of 12
1. Assumptions: -
I made no assumptions and I create 11 tables according to the case study in this assignment and these tables are: - Branch table. Manager table. Clients table. Job_type table. Jobs table. Accountant_specialise table. Accountant table. Mentor table. Pay_level_detail table. Current_Pay_level table. Higher_Pay_level table.
Relationships between the tables: - Jobs and Clients tables have Zero to many relationships. Jobs and Accountant have zero to many relationships. Jobs and Job_type have zero to many relationships. Accountant_specialise and Job_type tables have one to many relationships. Accountant_specialise and Accountant tables have one to many relationships. Branch and Manager Tables have one to one relationships. Mentor and Accountant tables have zero to many relationships on Accountant_ID in Mentor table. Mentor and Accountant tables have one to one relationship on Mentor_Acc_ID in Mentor table. Branch and Accountant tables have one to one relationship. Current_Pay_level and accountant tables have one to one relationship. Current_Pay_level and Pay_level_detail tables have one to one relationship.

Page 4 of 12 Current_Pay_level and Higher_Pay_level tables have one to one relationship.

Page 5 of 12
2. Logical ERD: -

Page 6 of 12
3. Physical ERD: -

Page 7 of 12
4. Table instance charts: -
1. Manager table
Columns Name Key Type Null/Unique FK
Table
FK Column Data
type
Length Detail
Manager_ID PK U INTEGER 10
Manager_nam
e
VARCHAR 20
Address VARCHAR 100
Phone_numbe
r
VARCHAR 20
Email_ID VARCHAR 30
2. Accountant table
Columns Name Key Type Null/Unique FK Table FK Column Data type Length Detail
Accountant_ID PK U INTEGER 10
First_name VARCHAR 10
Last_name VARCHAR 10
Contact_numbe
r
VARCHAR 20
Postal_address VARCHAR 100
Email_ID VARCHAR 30
Hire_date DATE

Page 8 of 12
3. Branch table
Columns Name Key Type Null/Unique FK Table FK Column Data type Length Detail
Branch_ID PK U INTEGER 10
Manager_ID FK Manager Manager_ID INTEGER 10
Accountant_ID FK Accountant Accountant_ID INTEGER 10
Branch_name VARCHAR 50
B_address VARCHAR 100 Branch address
B_Phone_number VARCHAR 20 Phone number of branch
4. Client table
Columns Name Key Type Null/Unique FK Table FK Column Data
type
Length Detail
Client_no PK U INTEGER 10 Client number
Tax_file_no VARCHAR 20
First_name VARCHAR 50
Last_name VARCHAR 50
Contact_numbe
r
VARCHAR 20
Email_ID VARCHAR 50

Page 9 of 12
5. Job_type table
Columns Name Key Type Null/Unique FK
Table
FK Column Data type Length Detail
Job_type_no PK U INTEGER 10 Job type number
Job_type_name VARCHAR 30
Cost_per_mint DOUBLE Cost per minute
6. Jobs table
Columns Name Key
Type
Null/Unique FK Table FK Column Data
type
Length Detail
Job_ID PK U INTEGER 10
Client_ID FK Client Client_ID INTEGER 10
Accountant_ID FK Accountan
t
Accountant_ID INTEGER 10
Job_type_no FK Job_type Job_type_no INTEGER 10
Job_date DATE The date of the job
Start_time TIME The time that the job was started
Completed_time TIME The time that the job was completed
Note VARCHAR Any notes or extra details about the job
(no size limit)
Partial_paid DOUBLE
Left_amount DOUBLE
Total_cost DOUBLE

Page 10 of 12
7. Accountant_specialise table
Columns
Name
Key
Type
Null/Unique FK Table FK Column Data
type
Length Detail
Acc_speci_no PK U INTEGER 10 Accountant specialize
number
Accountant_ID FK Accountan
t
Accountant_ID INTEGER 10
Job_type_no FK Job_type Job_type_no INTEGER 10
8. Mentor table
Columns Name Key Type Null/
Unique
FK Table FK Column Data type Lengt
h
Detail
Mentor_no PK U INTEGER 10
Mentor_Acc_ID FK Accountant Accountant_ID INTEGER 10 accountant’s mentor
Accountant_ID FK Accountant Accountant_ID INTEGER 10 Accountant
9. Pay_level_detail table
Columns Name Key Type Null/Unique FK
Table
FK Column Data
type
Length Detail
Pay_level_no PK U INTEGER 10
Pay_level_nam
e
VARCHAR 50
Annual_pay DOUBLE

Page 11 of 12
Min_Exp INTEGER 5 Minimum
Experience
10. Higher_Pay_level table
Columns Name Key
Type
Null/Unique FK
Table
FK
Column
Data
type
Length Detail
Higher_Pay_level PK U INTEGER 10
Exp_in_other VARCHAR 255 previous experience in other accounting
firms
Annual_pay DOUBLE
11. Current_Pay_level table
Columns Name Key
Type
Null/Unique FK Table FK Column Data type Length Detail
Crr_Pay_level_no PK U INTEGER 10
Accountant_ID FK Accountant Accountant_ID INTEGER 10
Pay_level_no FK Pay_level_detail Pay_level_no INTEGER 10
Higher_Pay_level_n
o
FK Higher_Pay_level Higher_Pay_level INTEGER 10
Salary DOUBLE

Page 12 of 12
References
Allen, C., Chatwin, S., & Creary, C. (2004). Introduction to relational databases and SQL programming. Berkeley, Calif: Osborne/McGraw-Hill.
Connolly, T., & Begg, C. (2015). Database systems. Boston [u.a.]: Pearson.
Connolly, T., Begg, C., & Holowczak, R. (2008). Business database systems. Harlow, England: Pearson Education.
Garmany, J., Clark, T., & Walker, J. (2005). Logical database design principles. Boca Raton, Fla. [u.a.]: CRC Press.
Harrington, J., & Harrington, J. (2009). Relational database design. Burlington, MA: Morgan Kaufmann/Elsevier.
Hernandez, M. (2013). Database design for mere mortals. Harlow: Addison-Wesley.
Kelly, L. (2004). Physical Database Design Using Oracle. Boca Raton: C R C Press LLC.
Kim, H. (2012). Advances in Technology and Management. Berlin, Heidelberg: Springer Berlin Heidelberg.
Li, Q. (2008). Conceptual modeling. Berlin [u.a.]: Springer.
MacWhinney, B. (2000). The database. Hillsdale [u.a.]: Lawrence Erlbaum Ass.
Silberschatz, A., Korth, H., & Sudarshan, S. (2011). Database systems concepts. Estados Unidos: McGraw-Hill Companies, Inc.
Stephens, R., & Plew, R. (2001). Database design. Indianapolis, Ind.: Sams.