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

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 – 

(a)

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

Symbols:

(b)

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.

(c)

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.

Example:

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