Data Dictionary

1. Client

Column Name	Data Type	Size	Minimum Value	Maximum Value	Primary key	Required	Description
ClientID	Int	10	1	9999	Y	Y	Name of the client
ClientAddress	Varchar	30			N	Y	Address of the client
ClientNumber	Int	10	1		N	Y	Phone number of client.
ClientEmail	Varchar	30			N	Y	Email id of client
DateOfBirth	Date	30			N	Y	Date of client is recorded

• Invoice

Column Name	Data Type	Size	Minimum Value	Maximum Value	Primary key	Required	Description
InvoiceID	Int	10			Y	Y	Invoice id is recorded.
InvoiceDate	Date	30			N	Y	Date when the payment is made
InvoiceAmount	Varchar	30			N	Y	Payment made by the client is recorded.

• Employee

Column Name	Data Type	Size	Minimum Value	Maximum Value	Primary key	Required	Description
EmployeeID	Int	10	1	500	Yes	Y	Employee who recorded the date must be recorded.
EmpName	Char	30			N	Y	Name of the employee must be recorded.
EmpContact	Int	30			N	N	Phone number of employee.
EmpAddress	Varchar	30			N	N	Address of employee

• Branch

Column Name	Data Type	Size	Minimum Value	Maximum Value	Primary key	Required	Description
BranchID	Int	10	1		Y	Y	Branch id must be specified properly.
BranchName	Char	30			N	Y	The name of the branch is specified
BranchAddress	Varchar	25			N	N	The address of the branch is specified.

•  Repair

Column Name	Data Type	Size	Minimum Value	Maximum Value	Primary	Required	Description
RepairID	Int	10	1		Y	Y	RepairID must be specified
BranchID	Int	10	1		N (Foreign key)	Y	BranchID must be specified
Problem	Char	50			N	N	The problem of the customer must be noted properly.
Completed	Char	50			N	N	Whether the installation is 40 percent complete or fully done.

• Installation

Column Name	Data Type	Size	Minimum Value	Maximum Value	Primary	Required	Description
InstallationID	Int	10	1	9999	Y	Y	Installation id must be provided.
HomePremises	Char	25			N	Y	Home address must be specified.
Investment Unit	Char	25			N	Y	Investment unit must be specified.
Houses	Char	25			N	Y	Installation might also be done on houses.
Offices	Char	25			N	Y	Whether he installation is t be done on offices must be specified.

• WallMounted

Column Name	Data Type	Size	Minimum Value	Maximum Value	Primary	Required	Description
InstallationID	Int	10			N (Foreign Key)	Y	Installation id must be allocated
Model	Varchar	15			N	N	Model must be defined
MakeYear	Varchar	15			N	N	The make year must be written.

• Ducted

Column Name	Data Type	Size	Minimum Value	Maximum Value	Primary	Required	Description
InstallationID	Int	10	1		N (Foreign Key)	Y	InstallationID must be specified.
AirFilter	Char	10			N	Y	Air filter if ducted should be written.
MotorizedVent	Char	10			N	Y	Motorized vent if installed should be written.

• Window Mounted

Column Name	Data Type	Size	Minimum Value	Maximum Value	Primary	Required	Description
InstallationID	Int	10			N (Foreign Key)	Y	InstallationID of the product must be written
Model	Varchar	15			N	N	The model number of the product.
MakeYear	Varchar	15			N	N	The make year of the product.

Business Rules

1. Service allowed are installation and repair.
2. Payment allowed are of two types i) advance and ii) remaining payment.
3. The DateOfBirth of the person must be lesser than the system date or less than current date.
4. CQAC collects personal details such as name, pho
5. ne, and contact phone, DateOfBirth email-phone and address details.
6. CQAC employee inspect the premises and provide installation option to the customer.
7. The installation option is ducted, wall mounted and window mounted air conditioning.
8. When the installation option is finalized a purchase, order is generated by them an a copy is generated by them and a copy is given to the customer.
9. 40 percent of the total cost is calculated as advanced payment from customers. 
10. After installing the air-conditioning units CQAC performs inspection and the remaining cost is collected.
11. When CQAC receives a repair request from their customer, details are related to the problem and a possible date for a repair visit is informed to them.

Assumptions and Cardinality

1. There are only three types of Installation (Supertype), WallMounted (Sub type), Ducted (Sub type) and window Installation (Subtype)
2. Many customers invoice can be made by many customers so there exists man to many relationships, so we put a associative entity invoice/client.
3. A branch can have many employee so there is 1 to many relationships among the entity.
4. A branch can have many repair histories so there is 1 to many relationships.
5. A branch can have many clients so there exits 1 to many relationships.
6. A branch can have many installations so there exists ne to many relationships.
7. Since, branch is dependent on many entities it acts as a weak entity.
8. An invoice can be made through a single employee so there exists 1:1 reationship.