Case Study: Winsor & Allsthop Conservatories (Wac)

CASE STUDY: WINSOR & ALLSTHOP CONSERVATORIES (WAC)

TASKS

The following Case Study

Derive an Entity Relationship Diagram (ERD) showing the entities of interest and associated attributes, relationships, dependency and indicate primary and foreign keys
Implement the tables in MS SQL Server 2005 by draw the ERD in MS SQL Server 2005
Populate the tables with the sample data.
Provide sample SQL code to demonstrate and understanding of DDL and DML SQL statements.

You may decide to modify the data requirements or generate additional data attributes to assist in your design of the database. Write down details of any assumptions you have made that you feel are important.

You may decide to follow the ERD’s in Answer Points.

CASE STUDY: WINSOR & ALLSTHOP CONSERVATORIES (WAC)

Winsor & Allsthop Conservatories LTD. are a large manufacturer and supplier of uPVC and hardwood conservatories. The company has eleven branches all in the West Midlands. The company is basically a family business that has greatly expanded in the last few years. As a result of this expansion the company has decided to purchase a computer system to enhance the operation of the conservatory side of the business.

Your software company has gained the contract to carry out a feasibility study for the project. You have been allocated the job of initial database design as part of the feasibility study. As a result of a number of interviews the following essential information is available for you to work from.

.... The branches carry a range of general building supplies but a main part of the business is the supply and installation of conservatories to the general public. Most branches have several conservatories on display although none of the branches have a display involving all of the conservatory ranges they manufacture. The conservatory ranges on display at different branches will need to be recorded. Thus, if a branch does not have a particular conservatory range on display they can advise a customer where they can view an example of that range.

The company supply eleven conservatory ranges, six are constructed using uPVC, the others are hardwood designs and all are double glazed. The differences between the ranges are largely to do with style and different ranges may share common panels. Obviously having ranges which share panels helps with manufacturing efficiency. Each range has a number of models available.

The smallest model in the "WESTMINSTER" range, the "W400", is only four metres long. Like all WAC conservatories, it is intended to fix to the outside wall of a house and thus needs panels to construct the three other walls. In the "W400" model each side wall involves three panels and the front wall uses three panels and a larger door panel. Five other panels are required to construct the roof. Although roof panels have a lighter construction than wall panels they are still considered to be a type of panel.

In general customers visit a branch and place an order for a conservatory. Some larger branches have an employee who has a special responsibility for conservatories. However, during a sale at any branch an employee is allocated as the branch contact for that sale. At the larger branches this may or may not be the employee with special responsibility for the conservatory business.

Details of a sale include the invoice number, the exact model of conservatory required and a list of required conservatory accessories such as roof windows, lighting units, roof fans, roof and window blinds. Further, a delivery address must be recorded for a sale which may not be the same as a customer's recorded address. Details of conservatory accessories include a unique accessory number , and appropriate description and a price. Conservatory accessories are often suitable for many models within several conservatory ranges. However, some conservatory accessories are only suitable for the larger models within a conservatory range. Thus where suitability for an accessory is limited to certain models in certain ranges, this information must be recorded.

The 'BUCKINGHAM' is an example of one of the hardwood ranges of conservatories, it is available in eight different models. Like all the conservatory ranges the models of the 'BUCKINGHAM' are a fixed depth, the distance of the outer wall from the house wall to which they are attached. The different models of the 'BUCKINGHAM' range are simply different lengths. The length of a conservatory model and its total installed price must be recorded.

Wall panels in all ranges are double glazed units, either windows or doors. There are many panel types some of which are shared between two or more conservatory ranges. Obviously different models within a conservatory range share many common panels. Attributes of a panel type include, include a description of the panel, the height, width, number of opening windows on the panel, and the individual panel price. The envisaged system should keep track of the stock of panels held at individual branches.

Panels mainly consist of a uPVC or hardwood frame and one or more double glazing glass units. The attributes of a glazing unit include the air-gap, height, width and price. All the models of a conservatory range have glazing units with the same air-gap. The system must record the type and number of glazing units used in each type of wall panel. However, there is no requirement to record stock levels of glazing units as this is already dealt with at the factory where the panels are made and branches do not keep a stock of glazing units.

Roof panels are made using a clear plastic glazing material and do not use normal glass glazing units. There is no requirement to record the different types of glazing units used in roof panels as the plastic glazing material is simply cut to size during manufacture.

All conservatories are sold as installed units. WAC does not sell conservatories as a 'Do It Yourself' kit. WAC keep a list of all the installers available and approved. When a sale is made an installer is allocated the job by the branch. As most local building forms are happy to install a WAC conservatory the list of installers is quite long. However, each branch also keeps a record of the their local preferred installers who have a track record of completing projects on time with few complaints.

The following transactions have been identified as being typical of the intended database usage.

Which panels are required for the sale 'SL6074'?
Has any customer bought more than one conservatory?
What is the total number of 'WESTMINSTER' conservatories sold by any branch that has this range on display?
For all the sales made on '22.09.96' list the model details, the customer details and the total value of the accessories involved in the sale.
Which customers have had conservatories installed by any preferred installer at the 'SOLIHULL' site?

CASE STUDY: WINSOR & ALLSTHOP CONSERVATORIES (WAC)

The WINSOR & ALLSTHOP CONSERVATORIES DatabaseDesign

CASE STUDY: WINSOR & ALLSTHOP CONSERVATORIES (WAC)

Simple Entities

Sale (sale#, date, model#, emp#,installer#, branch#, customer-name, cust-add, cust tel#, delivery-address, ...)

Could have a separate customer table if ERD matches - this just assumes they don't really keep a list of customers, just who bought a conservatory.

Glazing-unit (unit#, air-gap, glass-type, height, width, description, ...)

Panel (panel#, height, width, shape, material, description, openings, price, panel-type...)

Could have a separate panel-type table if ERD matches

Accessory (accessory#, acc-name, price, usage-comment, ...)

Range (range#, range-name, range-description, air-gap, ...)

Model (model#, range#, length, depth, total-installed-price, ...)

Employee (emp#, emp-name, ...)

Installer (installer#, ins-name, address, tel#, comment, ...)

Branch (branch#, branch-address, ...)

Complex Entities

Stock (branch#, panel#, quantity, reorder-level, reorder-quantity, ...)

Display-item (branch#, range#, date-first-displayed, display-comment, ...)

Sale-item (sale#, accessory#, quantity, item-comment, ...)

Compatible-Accessory (model#, accessory#, preference, quantity-required, ...)

Required-panel (panel#, model#, quantity-required, ...)

Panel-Glazing-Unit (panel#, unit#, quantity)

Relationship Tables

Preferred-installer (installer#, branch#, ins-comment, ...)

Responsible-emp (emp#, branch#, ...) or (emp#, branch#, ...)

Tables may include other suitable attributes

CASE STUDY: WINSOR & ALLSTHOP CONSERVATORIES (WAC)