Hd assignment On portfolio.

ER modelling practice – case studies

Construct an ER diagrams for the case studies given below. Be sure to write down any assumptions

you make or clarifications you get from your tutor. Make a note of any business rules that affect

your model. You will find these case studies more challenging than the ones given in the Basic

Structures tutorial sheet.

Case study 1 – Milk dairy

You have been asked to develop an ER model for a milk dairy. The milk dairy receives batches of milk

from a variety of suppliers which it then tests, bottles and sells. The milk dairy needs to keep a list of

the milk suppliers. For each supplier it is necessary to record the name and assign a unique ID. There

are a number of different types of milk suppliers: individual farmers, societies and chilling centres.

Many individual farmers are members of societies and this relationship must be recorded.

For each society, it is necessary to record the name of the president, secretary, and their contact

numbers. Each chilling centre has a manager and his/her contact numbers must be recorded. The

milk dairy also needs to keep a list of milk batches it receives. For each batch, it is necessary to

record the quantity (in litres) of milk received, the date it was received, and it is necessary to know

from which supplier the batch came.

When a batch of milk is received, at least two (possibly more) samples are taken from the batch. It is

necessary to keep a record of these samples. Each sample is analysed for fat percentage and SNF

percentage – it is necessary to record each of these for each sample. It is also necessary to know

which batch the sample came from.

At the end of each week, the milk dairy pays each supplier for the milk batches they supplied. For

each payment it is necessary to record the date of payment and the amount paid. It is necessary to

know which batches have been paid for in which payments. The amount paid is calculated according

to the fat percentage, the SNF percentage and the quantity of milk in each batch.

It is not necessary to keep any records about the bottling or sale of the milk.

Case study 2 – Quikfix Electronics

Quikfix Electronics is a local electronic repair shop. They repair all kinds of electronic household

goods such as televisions, video players, microwaves and stereo systems. Quikfix wants to

implement a computer-based database to track all aspects of the repair jobs.

When a customer brings an electronic item in for repair, an employee records the type of equipment

being brought in (for example, television, video player, etc), the make, model, and serial number.

The employee also records a description of the problem, that is, the reason why the equipment has

been brought in for repair. The repair job is assigned a unique number, called a Job Number. A piece

of paper with the Job Number written on it is taped to the item so that the repair job can be

identified without question. The Job Number is also given to the customer so that he or she can

quote the Job Number when enquiring over the phone or collecting the item from the shop.

Each repair job is performed by one employee and it is necessary to record which employee

performed each job. It is not, however, necessary to record which employee recorded the job

information when the item is first brought in by the customer. Each employee has a unique

employee number, and

Quikfix maintains normal contact information about each employee. Quikfix also records the

number hours taken to perform the repair job.

When an item is being repaired, it is frequently (but not always) necessary to replace certain parts.

Quikfix maintains a list of part types. Each part type has a unique part number, a description (for

example “belt kit”), and a price per unit. If parts are used during a repair, Quikfix records the

quantity of each part type used in the repair.

For each repair job, Quikfix records the total amount due (which is derived from the hours spent and

the parts used), and the date that the customer paid the amount due. Quikfix do not accept part

payments (that is, they only accept payment in full), therefore it is not necessary to record multiple

payments against a repair job.

Finally, Quikfix records and maintains contact details for each customer that comes in, specifically

address and telephone contact details.