Database Design and Development: CQ Air Conditioning ER Diagram

This document presents a database design and development assignment for CQ Air Conditioning Services. It begins with an Entity Relationship Diagram (ERD) illustrating entities like customers, premises, installations, agreements, payments, inspections, and repairs, along with their attributes and relationships. A comprehensive data dictionary follows, detailing each table's attributes, data types, sizes, permitted values, and key constraints. The document also outlines the business rules and assumptions governing CQ Air Conditioning's operations, clarifying processes and data requirements. These rules cover aspects like customer installations, premises associations, installation options, agreements, payment schedules, and repair processes, providing a complete overview of the database design and the underlying business logic.

Running head: DATABASE DESIGN AND DEVELOPMENT
DATABASE DESIGN AND DEVELOPMENT
Name of the Student
Name of the University
Author Note

1
DATABASE DESIGN AND DEVELOPMENT
ERD
An Entity Relationship Diagram represents the real-world objects as entities, attributes
and relationship between them (Dunn and Nel 2017). The entities and their attributes can be
identified with the help of the business process of the company. It also helps in defining the
business rules of the system.
Figure 1: CQ Air conditioning services ER Diagram
Source: created by author

2
DATABASE DESIGN AND DEVELOPMENT
Data Dictionary
Data Dictionary represents the physical schema of the design. It shows the attributes, data
types, data sizes, required attribute and description of the attributes (Malcher and Kuhn 2019).
The data dictionary for the CQ Air conditioning services is given below:
Customer Table
Attributes Data Type Size Permitted Value Required Key
CustomerID varchar 10 Yes Primary Key
Name varchar 50 Yes
Address varchar 50 No
Contact int 10 Yes
Email varchar 50 Contains ‘@’ No
Premises Table
Attributes DataType Siz
e
Permitted Value Required Key
PremisesID varchar 10 Yes Primary
Key
ContactPerson varchar 50 Yes
PremisesType varchar 50 investment units, house or
Offices
Yes
Address varchar 50 Yes

3
DATABASE DESIGN AND DEVELOPMENT
Installation-Option Table
Attributes DataType Siz
e
Permitted Value Required Key
OptionID varchar 10 Yes Primary
Key
OptionNam
e
varchar 50 Ducted, wall mounted or window
mounted
Yes
Description varchar 100 Yes
Installation Table
Attributes Data Type Size Permitted
Value
Required Key
Installation ID varchar 10 Yes Primary Key
Date Date <=currentdate Yes
PremisesID varchar 10 Yes Foreign Key
CustomerID varchar 10 Yes Foreign Key
OptionID varchar 10 Yes Foreign Key
Agreement Table
Attributes Data Type Siz
e
Permitted Value Required Key
AgreementID varchar 10 <=currentdate Yes Primary Key
Date Date Yes

4
DATABASE DESIGN AND DEVELOPMENT
Description varchar 100 No
InstallationI
D
varchar 10 Yes Foreign Key
Payment Table
Attributes Data Type Size Permitted Value Required Key
PaymentID varchar 10 Yes Primary Key
PaymentDate Date <=current date Yes
AmountPaid Decimal (8,2) 40% of total Yes
AmountDue Decimal (8,2) 60% of total Yes
PaymentMethod varchar 50 Debit/credit card or cash Yes
AgreementID varchar 10 Yes Foreign Key
Inspection Table
Attributes Data Type Size Permitted Value Required Key
Inspection ID varchar 10 Yes Primary Key
Date Date Yes
Issues varchar 100 No
InstallationID varchar 10 Yes Foreign Key
Repair Table

5
DATABASE DESIGN AND DEVELOPMENT
Attributes Data Type Size Permitted Value Required Key
RepairID varchar 10 Yes Primary Key
AppointedDate Date Yes
IssueDescription varchar 100 Yes
CustomerID varchar 10 Yes Foreign Key
Invoice Table
Attributes Data Type Size Permitted Value Required Key
InvoiceNumber varchar 10 Yes Primary Key
InvoiceDate Date Yes
AmountPaid Decimal (8,2) Yes
RepairID varchar 10 Yes Foreign Key
Business Rules and Assumptions
Business Rules and Assumptions helps in identifying the process and data requirements
of the company. It helps in System analysis and design phases. The business processes can be
observed from the documentations or the functionalities of the business (Coronel and Morris
2016). All of the business rules or CQ Air conditioning services can be listed below. Certain
assumptions are also included in the business rules to justify.
i. Each customer can request for one or many installation on their premises.
ii. Each installation can only be related with one and only one customer.
iii. Each Premises can only have one installation

6
DATABASE DESIGN AND DEVELOPMENT
iv. Each Installation can only be related to one and only one Premises.
v. Each Installation-option can be associated with one or many installation.
vi. Each installation can only be one option type.
vii. Each installation can have one and only one agreement.
viii. Each agreement can be associated with one and only one installations.
ix. Each agreement is related to one or more payments.
x. Each payment can be associated to one and only one agreement.
xi. Each installation can have one or many inspections.
xii. Each inspections can only be related to only one installations.
xiii. Each customer can request for one or more repairs.
xiv. Each repair can only be associated with one and only one customer.
xv. Each repair ha one and only one Invoice.
xvi. Each Invoice can be associated with one and only one Repair request.

7
DATABASE DESIGN AND DEVELOPMENT
Referencing
Dunn, C. and Nel, L.D., 2017. Entity Relationship Diagram Mapping.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Malcher, M. and Kuhn, D., 2019. Data Dictionary Fundamentals. In Pro Oracle Database 18c
Administration (pp. 383-406). Apress, Berkeley, CA.