Draw an Entity Relationship (ER) diagram,Perform logical design,Create 3NF Relations

    Need Solution - Download from here

    Ques. The CQ-Council (hereafter called as council) posts a full page advertisement, in local

    newspapers once a month, displaying all the Development Plan applications (here after

    called as application) that have been received during the previous month from the

    developers/ owners of the plots under the council’s purview.

    General public members are allowed to raise objections against the applications that appear

    on those advertisements within a certain period of time, such as two weeks from the date of

    the advertisement. The council maintains the details of those objections and the respective

    application details. The council maintains all such objections even if they are from an

    anonymous member of the public.

    Solution – 


    ER diagram: it is the representation of the data that how they are related with each other.


    Capture 1212

    Screen Shot 2017-07-12 at 3.16.41 PM


    Assumptions and business rules: Business rules are used to describe the properties of an application.

    Council posts the application in the newspaper and newspaper application need the clearance from the public.
    Council first approved the application from the applicants or the owner of the lot than posts the application in the newspaper
    Council has the records for suburban areas. Some of the suburban area is divided for the recreation, community, commercial, residence.
    These lots have approved roads, street facilities and land parcels.
    Land parcels or lots have lot number and details.
    This proposal is offered by the owner/applicant to the council if council passed the proposal than they posts the advertisement in the newspaper.


    3NF:  the database is in the third normalization form if it satisfies the two conditions. First it is in second normalization form and second there is no transitive functional dependency.


    Customer (CustomerId, customerName, Street, Suburb, State, PostCode, Email)

    Invoice (InvoiceNumber, invoiceDate, Total, CustomerId)

    foreign key (CustomerId) references Customer(CustomerId)

    In third normalization form:

    Customer:  CustomerId, customerName, Email

    PostalCode: CustomerId, Street, Suburb, State, PostCode

    InvoiceCustomer: CustomerId, CustomerId

    Invoice: CustomerId, InvoiceNumber, invoiceDate

    Invoicetotal: CustomerId, Total


    Leave A Comment