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.
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.
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).
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.
(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.
(c) Under what circumstances would it be necessary to de-normalize this model?
Question 3 (Covers LO 1)
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
(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
(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:
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:
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.
Name and briefly discuss three Database Management Systems (DBMS) that could be used to implement a solution to your proposed database design.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more