Jubail Motors Warehouse and Marketing Information System - Case Study

Introduction to the case study

Introduction to the case study 

Jubail Motors Warehouse is a newly established company that buys passenger motor vehicles of various types and makes in bulk from manufacturers and sells them to dealers and individuals through out the Kingdom of Saudi Arabia. At present all the company's customers are based in Saudi Arabia but there are plans to expand into the Gulf region in the near future.
 

The company wishes to develop a Sales and Marketing Information Systems that will facilitate and monitor the interaction between them and their customers.  A key objective of the system is to enable management to make decisions on when and where to deploy their small but highly motivated sales force to maximum effect. The company has thus requested the creation of a database system as a means of achieving their objective.  They have heard that the Oracle DBMS is one of the best on the market and so have decided that the system should be developed using the Oracle product SQL Plus.  
 

The Case 

The company (Jubail Motors Warehouse) sells various makes and types of passenger motor vehicles throughout Saudi Arabia.  For sales and distribution purposes, the country is divided into three regions: East, West and Central.  Each region is identified by a unique region number and is managed by a Sales Engineer.

A region consists of a number of areas each assigned a unique area number.  The number of areas per region ranges between two and five.  Area numbers are not unique to particular regions and a Sales Engineer is allocated to each area.  The Sales Engineer's name, telephone number and home address are some of the attributes of interest to the system.  

At present the company sells only three makes of vehicles: Ford, Hyundai, Nissan Sunny but this should increase to about twelve in the near future. Every vehicle make is given a unique make-number, for example: Ford's make number is 90, Hyundai's is 80, Sunny’s is 70.  Each make of vehicle is managed by a Sales Engineer.  Hence there is one Sales Manager (engineer) for all Ford vehicles, one for Hyundai vehicles and so on.  Other attributes of interest are the names and addresses of the suppliers of the vehicles.

Each make of vehicle is available in many vehicle types.  At present the company handles only three vehicle types: Bus, Van and Saloon car but there are plans to bring in other types very soon.  Again each vehicle type has a unique type number, for example 1400 for Bus, 1200 for Van and 1000 for Saloon car.  Clearly a type number and name occurs in more than one vehicle make.  Hence new vehicles, yet unregistered, are described in terms of both their make and type for sales purposes.  For example, customers order for a quantity of vehicle types of a particular make (such as 5 Ford Vans and/or 3 Hyundai Buses).  Both the stock level and price of vehicles are recorded.  Other attributes of interest include the engine size (e.g. 1.6, 2.0, 2.5 litres etc.) and the passenger capacity.
 

Each customer resides in one sales area and is identified by a unique customer number.  Customers are visited by their area Sales Engineer on an irregular basis.  The aim of the visit is to maintain customer support and to market new vehicles.  The purpose, date and duration of each visit are recorded as well as the customer's name, address, telephone number and fax number.
 

So far over 97% of orders are made directly to head office and the bulk of such orders are from dealers.  A typical order is often for a number of different vehicle types - and makes - at the same time.  The date and unique number of each order are recorded as well as the quantity of vehicles ordered.  

For the first few years of operation, the company has decided to deal in only one size of each type of vehicle.  This means that all Buses sold have the same passenger capacity, for example, 35-seaters, likewise Vans and Cars.  

Vehicles are priced according to their make and type. Ford vehicles of all types are currently the most expensive, about 10% more than the other makes.  The other makes are priced the same for the same vehicle type.    

a)Develop an Entity Relationship (ER) model to support the above scenario.  Your model should show clearly the multiplicity of each relationship between entities. (40 marks)

b)Derive a relational schema (set of normalised tables) from your ER diagram in (a).  Your schema should include relevant attributes for all the entities and identify clearly the primary and foreign keys. (20 marks)

c)Set up tables, including well-designed test data, to implement the application.  Your implementation must include all necessary data validation constraints identified in the case.  (10 marks)

d)Set up and test the following queries and reports using Oracle with SQLPLUS and/or PL/SQL.  Note that your sample data is expected to satisfy the requirements of ALL the queries below.  (30 marks)