Database Concepts Design - Implementation and Management

Question: Discuss about the case study Database Concepts for Design, Implementation and Management. Answer: Entity Relationship Diagram Figure 1: Entity Relationship Diagram (Source: Created by author) Business Rule In a database system, business rules are used to describe the attributes property. Every database system maintains the business rule at the time of database creation. In this case, the business rule is manage properly. The entity relationship diagram is designed by supporting the business database rule. Some assumption made by the database developer such as NOC entity is used in this ERD diagram. Here NOC (No Objection Certificate) is used to determine that no other members do not have any objection about any lots. Also, the attributes of NOC are assumed. In the above ERD, the advertisement entity is used to store the advertiser details. Because to construct something in a lot, the people need to take permission from the council and council advertise this application to clarify the NOC. The area can be divided into many types. To maintain a properly normalized database, the developer used the different type of area entity and stored the area details into it. Same as area, lots have m any different types. The database developer used this lot type entity to store the lot details as per their types. Here the lot process entity is also assumed. To maintain the business rule the developer use lot process entity. Moreover, in the lot process entity, lot process info attribute to store the details of the processing technique and their details. Normalization advertisement Name DataType Constraints adid integer(10) PK adname varchar(255) adddate date adddetails varchar(350) addtypeid integer(10) FK (advertisetype.addareaid) advertisetypeadtypid integer(10) FK (advertisetype.adtypid) Clearanceinspector Name DataType Constraints cleinspectorid integer(10) PK cleainsname varchar(255) cleainspectorcontaact integer(15) cleainsinfo varchar(255) Advertisetype Name DataType Constraints adtypid integer(10) PK adtid integer(10) addareaid integer(10) PK/FK (area.areaid) addinfo varchar(200) addcontact integer(15) Area Name DataType Constraints areaid integer(10) PK areaname varchar(255) arealoc varchar(255) areasupervisorid integer(10) FK (areasupervisor.areasupervisorid) areadetails varchar(255) areasize varchar(255) areatypeid integer(10) FK (areatype.areatypeid) restrictarea integer(1) restrictareaid integer(10) FK (restrictareatype.resareatypid) Lotowner Name DataType Constraints lotownerid integer(10) PK lotownername varchar(255) lotownercontactno integer(10) lotownerinfo integer(10) Developer Name DataType Constraints developerid integer(10) PK developertype integer(10) FK (devtype.devtypeid) developerlotid integer(10) PK developername varchar(255) lotslotid integer(10) FK (lots.lotid) devdesid integer(10) FK (developerdescription.devdesid) Clerancesub Name DataType Constraints clearanceid integer(10) PK clearencename varchar(255) clearancearea varchar(255) clearancelot varchar(255) clearancestatus varchar(255) clearanceinfo varchar(255) Objperson Name DataType Constraints objppid integer(10) PK objpname varchar(255) objptyp varchar(255) objcontact integer(15) Objection Name DataType Constraints objid integer(10) PK objdate date objissue varchar(200) objpersonid integer(10) FK (objperson.objppid) objtypeid integer(10) FK (objtyp.objtypid) Areatype Name DataType Constraints areatypeid integer(10) PK areatypename varchar(255) areadescripton varchar(255) Dreasupervisor Name DataType Constraints areasupervisorid integer(10) PK areasupervisoridname varchar(255) areaid integer(10) supervisorcontactno integer(10) Devtype Name DataType Constraints devtypeid integer(10) PK devtypename varchar(255) devtypinfo varchar(255) Lots Name DataType Constraints lotid integer(10) PK lotname varchar(255) lotareaid integer(10) lotinfo integer(10) FK (lotprocess.lotprocessid) lotsize varchar(255) lotownerid integer(10) FK (lotowner.lotownerid) lottype integer(10) lottypeid integer(10) FK (lottype.lottypeid) Objtyp Name DataType Constraints objtypid integer(10) PK objtype varchar(150) objsub varchar(255) objautho varchar(100) Lottype Name DataType Constraints lottypeid integer(10) PK lottypename varchar(255) lottypeinfo varchar(255) lotprocessid integer(10) Lotprocess Name DataType Constraints lotprocessid integer(10) PK lotprocessname varchar(255) lotprocessinfo varchar(255) Restrictareatype Name DataType Constraints resareatypid integer(10) PK resareatypename varchar(255) resareainfo varchar(255) NOC Name DataType Constraints nocid integer(10) PK nocname varchar(255) objid integer(10) FK (Objection.objid) clearacceauthority varchar(255) nocinfo varchar(255) Clearanceresult Name DataType Constraints clearanceresid integer(10) PK clearanceresname varchar(255) cleaveryresult varchar(255) develop_permission varchar(255) cleveryid integer(10) FK (clearanceveify.cleveryid) Clearanceveify Name DataType Constraints cleveryid integer(10) PK clearanceid integer(10) FK (clerancesub.clearanceid) nocid integer(10) cleaveryresult varchar(255) reason varchar(255) veriinspectorid integer(10) FK (clearanceinspector.cleinspectorid) Restrictarea Name DataType Constraints restrictareaid integer(10) PK restrictarealoc varchar(255) restrictareasize varchar(255) restrictareatypeid integer(10) FK (restrictareatype.resareatypid) Developerdescription Name DataType Constraints devdesid integer(10) PK devreputation varchar(255) devexp varchar(255) devcontactno integer(15) Bibliography Coronel, C. and Morris, S., 2016. Database systems: design, implementation, management. Cengage Learning. Grefen, P., Pernici, B. and Snchez, G. eds., 2012. Database support for workflow management: the WIDE project (Vol. 491). Springer Science Business Media. Larman, C., 2012. Applying UML and Patterns: An Introduction to Object Oriented Analysis and Design and Interative Development. Pearson Education India. Li, J., Li, C., Luo, Y., Zhao, Y. and Mao, X., 2015. Entity Relationship Modeling Approach Based on Micro-Blog Tag. International Journal of Multimedia and Ubiquitous Engineering, 10(7), pp.157-170. Thalheim, B., 2013. Entity-relationship modeling: foundations of database technology. Springer Science Business Media.