Database design and development

Database design and development
John Menzies plc (JM) is a time critical logistics company with two operating divisions, Menzies
Distribution and Menzies Aviation . The divisions operate in distinct Business to Business sectors
where success depends on providing an efficient, high quality, time critical service to their
customers and partners.
The company – one of Scotland’s largest – was established in 1833 and its head office is in
Edinburgh. Today the company is an international operation with businesses in Europe, North and
Central America, South East Asia, Australasia and Africa with its core business in four main areas;
Distribution, Corporate News Delivery, Travel Industry and Marketing Services.
For the sake of this coursework we will concentrate on its UK distribution business only.
Distribution
JM distribute 7.1 million newspapers and magazines every day to 29,500 retailers in the UK and
Ireland. With almost 180 years’ experience, they are experts in timecritical
logistics.
With a fleet of around 1,750 vehicles, they cover 123,000 miles every day – the equivalent of going
around the world five times. Sophisticated software is used to ensure they use the most
economical vehicles on the best routes possible.
Details
JM maintains a list of approximately 300 publications (newspapers, weekly’s, magazines,
periodicals etc) For each publication JM has negotiated a contract with its publishers for the bulk
purchase of the publication. These are then sold onto a variety of outlets such as newsagents and
superstores.
Once a month outlets will place an order with JM for the publications they want delivered the
following month i.e. 100 copies of The Sun per day, 40 copies of PS Gamer etc. The publications
are then delivered on whatever day they are published, this can be any day of the month. The
outlet may therefore receive daily deliveries.
Any copies of the previous edition of a publication are returned to JM for credit via its vans i.e. the
van driver will deliver and collect bundles of publications
JM invoice each outlet on a weekly basis. The invoice contains charges for all publications
delivered and a credit for all returned publications.
(based on http://www.menziesdistribution.com/ )
2
Your Task
JM have now approached you and asked you to develop a database to manage the sales and
returns of all of its publications. You are to produce a portfolio that documents this development.
Your database should provide different services for different kinds of users such as customers
(the outlets), drivers, managers and any other group you feel appropriate. For example:
● Drivers need to know their delivery route for each day
● Managers can look at sales information such as the best seller each month
Your database should be governed by the following business rules (you may wish to add more);
● All customers must create an account to be able to purchase anything
● All accounts must include name, address and at least one phone number
● Records of orders are only kept for 6 months and then deleted
The case study provides only the guidelines and you are free to expand beyond the information
given. Different students may also interpret the case study differently. Therefore, it is expected
that you will introduce a number of assumptions. All assumptions (requirements) must be
clearly stated in the submission.