Dazzle has over 40 years of experience in the residential and commercial field of the electrical distribution sector. Th

[answerhappygod]

Dazzle has over 40 years of experience in the residential
and commercial field of the electrical distribution sector. They
specialize in recessed lighting, dimming, lamps, ballasts, and much
more from quality manufacturers such as: Juno Lighting, IrisTM,
Lutron, Philips, Advance, GE, Pass & Seymour Legrand,
Lithonia Lighting, as well as many others. The company is
passionate about making your experience as a customer satisfying.
They are not a retail company and they do not sell direct to the
public. However, they are not only wholesalers but also ready to
assist you with product information, product selection, and all
your order needs. The company is authorized distributors of all the
above products and they keep many in their local inventory.
The company headquarters are in Manchester, United Kingdom. This
is where the Chief Executive and the Board are based, along with
central support functions like HR, IT, Legal etc. The CEO and Board
thus constitute the senior management and are responsible for all
strategic decision-making.
The company is global, large and complex and, as such, is
structured along its product lines, with each product category
being headed by a Product Director. Each Product Director is
responsible for all aspects of that division and thus makes all
tactical-level decision. They are based around the world. Each
Product Director reports directly to the Senior Management back in
London. There are eight Product Directors, heading up the following
product divisions:
Dimming (several different dimming options to achieve the exact
light level)
Commercial Lighting (for restaurants, housing and commercial
buildings)
Light Bulbs (LED, decorative, energy saving, spotlight and
fluorescent tubes)
Wiring Devices (wiring devices portfolio extends to door entry,
building
automation and domestic circuit protection systems)
Ballasts (the ballast regulates the current to the lamps and
provides sufficient
voltage to start the lamps.)
Circuit Breakers (Low-voltage, Magnetic, Thermal magnetic and
Magnetic-
hydraulic circuit breakers.)
Landscape Lighting (private gardens and public landscapes)
Floor Heat (for use under most floor finishes including carpet,
wood, laminate,
vinyl, tile and stone.)
Within each product division there are many different factories
and specialized manufacturing/testing facilities – some
in urban settings, others in rural areas for field testing. Each
site factory is under the control of a Site Manager who is
responsible for all local operational decision-making. Each Site
Manager reports directly to the relevant Product Director. The date
(DDMMYYYY), country of origin (UK, China, Singapore, etc.) and site
number (01, 02 etc.) of each product made is recorded for quality
control and auditing purposes and encoded into a fixed format
Product Code. The serial number of each item and product name is
also recorded.
Example Data:
Product Division (PD): Circuit Breakers
Product Name (PN): Low-voltage
Serial Number (SN): 124567898
Product Code (PC): 20022018CH07
This describes Low-voltage 234 Circuit Breakers (serial number
124567898) made on 20th February 2018 in the China at Factory
#7.
Product Division (PD): Light Bulbs Product
Name (PN): Pack 10 spotlights
Serial Number (SN): 236601547
Product Code (PC): 04052018US03
This describes a pack of 10 “spotlights” (serial number
236601547) made on 4th May 2018 in the USA at Factory #3.
Final Point
The organisation needs a database to run all the above
operations. Your job is to design and build this database. The
above is simply an outline of the company and you will need to make
your own assumptions and interpret or even extend the scenario as
you go. Use your imagination as you see fit, but you must
clearly document all assumptions and extensions.
Question 1 (Covers LO 2)
(a) Develop a semantically rich data model that captures the
above scenario in the form of an entity-relationship diagram (ERD).
You should note (and number) all assumptions you make about the
data and the reasoning behind your design choices. Also, include
(and number) any appropriate constraints and a list of entity types
showing their attributes and identifiers.
(15 Marks)
(b) Once your ERD is complete, you should look for opportunities
to use the entity sub-typing concept and thus develop an
EERD (Enhanced ERD).
(5 Marks)
Question 2 (Covers LO 3)
(a) Once you are satisfied that the EER diagram is a good
representation of the organisation’s data requirements,
produce a logical design by mapping the EER diagram to a set
of relations, showing all primary and foreign keys clearly.
(10 Marks)
(b) Now produce a normalized relational model by checking each
relation is in third normal form (3NF). You should clearly
annotate and explain this process.
(10 Marks)
(c) Under what circumstances would it be necessary
to de-normalize this model?
(5 Marks)