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, Iris™, Lutron, Philips, Advance, GE, Pass & Seymour Legrand, Lithonia Lighting, as well as many others.
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.
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:
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.)
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.
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.
(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).
(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.
normal form (3NF). You should clearly annotate and explain this process.
Name and briefly discuss three Database Management Systems (DBMS) that could
be used to implement a solution to your proposed database design. Decide on an
appropriate DBMS that you wish to use for this project. Provide an argument for
using this system rather than others that could be used and remember to relate your
reasons to the actual scenario. Be sure to fully reference any external sources used.
Question 4 (Covers LO 4)
(a) Take each of the normalized relations and implement them as SQL tables using
your chosen DBMS from the previous task with a series of CREATE TABLE
statements. You must include all primary and foreign keys as well as any other table
or column constraints you feel are appropriate such as NOT NULL, CHECK,
UNIQUE and DEFAULT. Provide screenshots of the working code. Ensure your user
name or some other distinguishing aspect is included in the screenshot to verify it is
your code.
(b) Using appropriate sample data and your own imagination based on this case
study, populate your finished tables with at least 10 rows of data in each table.
Provide screenshots of the working code. When done, display the full contents of
each populated table to screen and take screenshots. Ensure your user name or
some other distinguishing aspect is included in the screenshot to verify it is your
code.
(c) To demonstrate that your final database is useful, write a set of realistic sample
SQL queries based on the above scenario (use your imagination for details of each
query) but they should include the following techniques:
SELECT…FROM…WHERE…
Joins (using two, three or more tables)
Ordering output (ORDER BY)
Grouping output (GROUP BY)
Aggregate functions (MIN, MAX, AVG, COUNT, SUM)
You should aim to write a minimum of ten sample queries – ranging from basic
SELECT…FROM…WHERE queries to more advanced ones using the above
techniques. Be sure to fully evidence all SQL work by taking screenshots of the
inputted code and the outputted results. Ensure your user name or some other
distinguishing aspect is included in the screenshot to verify it is your code.
Question 5 (Covers LO 5)
Describe and explain how the following database application techniques may be
employed to enhance and extend the performance of your database:
Indexes
Triggers
Stored Procedures
Ideally, you should attempt to implement (and evidence via screenshots) these
coding techniques but if that is not possible, then at least explain what they do and
what benefits they bring PLUS supplying the SQL code you would have executed.
