Database Implementation Assignment for Dazzle: Scenario and Questions

As part of the formal assessment for the programme you are required to submit a Database Implementation assignment. Please refer to your Student Handbook for full details of the programme assessment scheme and general information on preparing and submitting assignments.

Learning Outcomes:

After completing the module you should be able to:

1. Demonstrate a critical understanding of the tools and techniques to support database development.
2. Justify the design and development of the application and critically evaluate the implementation and approach.
3. Design and implement a database system meeting the needs of both the user and the client.
4. Develop appropriate SQL statements in order to efficiently create, manage and interrogate data within the database application.
5. Use appropriate database development techniques to solve loosely defined

Secenario:

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, country of origin and site number 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.

Question 1 (Covers LO 2)

• 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 Also, include (and number) any appropriate constraints and a list of entity types showing their attributes and identifiers.
  
  
• 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)

• 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.
  
  
• Now produce a normalized relational model by checking each relation is in third normal form (3NF). You should clearly annotate and explain this process.
  
  
• Under what circumstances would it be necessary to de-normalize this model? (5 Marks) 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)

• 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 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.
  
  
• 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
  
  
• 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.