Database Design G 6672: Canberra Work Group (CWG) Database Project

This project outlines the design of a database system for Canberra Work Group (CWG), which requires a robust solution to manage customer data, repair services, and related information. The database design adheres to the third normal form, ensuring data integrity and minimizing redundancy. Key components include detailed entity relationship diagrams (ERD) illustrating the relationships between various entities such as Staff, Customers, Services, and Appointments. The project includes comprehensive table designs with primary and foreign key constraints, SQL code for table creation, data insertion examples, and query implementations to retrieve and manipulate data. Additionally, the design incorporates user-friendly forms for data entry and management. The bibliography lists relevant academic resources used in the design process. This database solution aims to streamline CWG's operations by providing an efficient and organized system for data management.

Running head: DATABASE DESIGN
Database Design
Name of Student-
Name of University-
Author’s Note-

1DATABASE DESIGN
1. Tables and Database
All tables are designed maintaining third normal form.
Entity Entity Type Attributes Primary
Key
Foreign
Key
StaffDet Strong
Entity
staffID 
branchID 
staffFN
staffLN
position
gender
DOB
salary
service_type
addressID 
staffphone
faxnum
CWG Strong
Entity
branchID 
service_name
service_type
price
addressID 
CWGphone

2DATABASE DESIGN
course_offered
SectionDet Weak Entity serviceID 
section_name
email
loaction
branchID 
AppointmentDe
t
Strong
Entity
appointID 
couseID
course_type
staffID 
JobDet Strong
Entity
jobID 
custID
repair_num
repair_name
lodgement_date
staffID 
Next_of_Kin Weak Entity kinID 
kinFN
kinLN
kinphone
addressID 
CustDet Strong custID 
custFN

3DATABASE DESIGN
Entity
custLN
addressID 
gender
service_required
custphone
kinID
branchID 
appointID 
property_det
AddressDet Strong
Entity
addressID 
type_of_address
streetaddr
streetname
subhurb
post_code
city
state
CourseDet Weak Entity courseID 
course_type 
course_name
course_det
course_time

4DATABASE DESIGN
price
InvoiceDet Strong
Entity
invoiceID 
custID
payment_type
card_num
card_name
date_of_payment
amount_paid

5DATABASE DESIGN
2. Entity Relationship Diagram
Figure 1: Entity Relationship Diagram
(Source: Created by Author)
3. Creating table:

6DATABASE DESIGN
AccessDet Table
AppointmentDet Table:
CourseDet Table:

7DATABASE DESIGN
CustDet Table:
CWG Table:

8DATABASE DESIGN
InvoiceDet Table:
JobDet Table:

9DATABASE DESIGN
Next_of_Kin Table:
SectionDet Table:

10DATABASE DESIGN
StaffDet Table:
Adding data in the table

11DATABASE DESIGN