Database Management System System for CQ-Council

Question: Describe about the Report for Database Management System of System for CQ-Council. Answer: Entity relationship diagram of information system for CQ-council Figure 1: ER Diagram of information system for CQ-Council (Source: Created by author) Business rules and assumptions The business rules define the organization objective and regulations, which include a complete outline boundary for access for some specific users. Every organization must follow cardinality and modality criteria for developing their information system. Here the CQ-council wants to develop an information system to manage their urban area via online applications. From this study, the developer has identified that several areas use for the commercial and residential purpose. As per their assumption, the CQ-council decided that this information system does entire plot purchasing, construction, solving applicants issues and promotion of the plots. The CQ-council wants to provide a system to record details information of followings: An owner of the plots. If an owner wants to merge, two or more plots or subdivided a plot. Separate and count all plots in a suburban area. Store all suburban areas information. Now the developer created the entity relationship diagram according to the relationship between all entities. Each relation has a business rule, which is listed below: An applicant must follow the council policy when a request for any service. An applicant can get a service after council investigation officer investigates and mark this request as a valid. The application must have valid information for plot and land lot. Plot owners must give application to merge or subdivide their plots. Each application must contain information of three boundaries of a plot. If request a construction service then the application must acknowledge before a period. An applicant can get clearance tag only if the application is complying with the councils developmental policies. 3NF Relations In this section, the developer, define the relationships in the above ER-diagram (figure 1). Here these relations are shown in a particular format, which represent the relationship between entities. OwnerOfLot (OOLID, Fullname, PhoneNumber, AccountDetails, Address) ConstructorCategory (CCID, FullName, TypeDetails) CQInspector (CQI_Code, FullName, Email, PhoneNumber) CQLandAggreement (CQLA_Code, Name, AdditionalDetails) ClearanceOfApplication (CAcode, SDcode, CQI_Code, survayResult, ApplicantStatement, EAcode) Foreign key (SDcode) references ServiceDetails (SDcode) Foreign key (CQI_Code) references CQInspector (CQI_Code) Foreign key (EAcode) references EvaluationofApplication (EAcode) ServiceDetails (SDcode, ServiceTitle, ServiceArea, TotalNumberOfWorker, Progress) Constructor (Con_ID, FullName, CCID, CI_Code, WorkStatusCode) Foreign key (CCID) references ConstructorCategory (CCID) Foreign key (CI_Code) references ConstructorInfo (CI_Code) Foreign key (WorkStatusCode) references ConstructorWorkingStatus (CWS_ID) Plot (Plot_ID, LongitudeAndLatitude, AreaName, OP_code, TotalVolume, OOLID, TypeName, CQLA_Code) Foreign key (OP_code) references ObjectionForPlot (OP_code) Foreign key (OOLID) references OwnerOfLot (OOLID) Foreign key (CQLA_Code) references CQLandAggreement (CQLA_Code) EvaluationofApplication (EAcode, Title, Declaration, PassingFlag) Sector (SectorCode, SectorVolumn, CQCA_Code, SectorDetails, SectorType, NTcode, OP_code) Foreign key (CQCA_Code) references CQCouncilAdministrator (CQCA_Code) Foreign key (NTcode) references NoticeType (NTCode) Foreign key (SectorType) references SectorType (STcode) Foreign key (OP_code) references ObjectionForPlot (OP_code) ObjectionForPlot (OP_code, ObjectionAreaName, NumberOfParameters) ConstructorInfo (CI_Code, experienceSurvayReport, Rating, TenderInfo) ConstructorWorkingStatus (CWS_ID, CurrentProjectStatus, TotalWorkerCapacity) Applications (App_ID, Subject, Purposetype, MessageBody) Notice (Ncode, FullTitle, DateTime, Description, NTCode) Foreign key (NTCode) references NoticeType (NTCode) NoticeType (NTCode, NCdetails, Rating) SectorType (STcode, TypeTitle, sectorDetails) RequireService (RS_Code, ServiceType, NumberOfUnit, OP_code) Foreign key (OP_code) references ObjectionForPlot (OP_code) Complaint (Com_ID, Date, EmergencyContact, App_ID, OOLID, plotObjectionTypeCode) Foreign key (App_ID) references Applications (App_ID) Foreign key (OOLID) references OwnerOfLot (OOLID) Foreign key (plotObjectionTypeCode) references ComplaintPlotType (plotObjectionTypeCode) ComplaintPlotType (CPTcode, TypeName, ComplaintAutority) RejectedComplaint (RC_code, Reason, CertifyAuthority, CQCA_Code, Com_ID) Foreign key (CQCA_Code) references CQCouncilAdministrator (CQCA_Code) Foreign key (Com_ID) references complaint (Com_ID) CQCouncilAdministrator (CQCA_Code, FullName, PhoneNumber, Email, DepCode) Foreign key (DepCode) references complaint (DepCode) CQdepartment (DepCode, DepartmentTitle, ServiceInfo, Type) Bibliography Coronel, C. and Morris, S., 2016. Database systems: design, implementation, management. Cengage Learning. Larman, C., 2012. Applying UML and Patterns: An Introduction to Object Oriented Analysis and Design and Interative Development. Pearson Education India.