An interior design firm would like to develop an application to computerise its operations. A client requests that the firm performs a job such as decorating a new home, redecorating rooms, locating and purchasing furniture, etc. Detail information of client such as name, address is recorded and a unique client number is generated, job information is recorded too. The firm has a number of decorators, one decorator is in charge of a job, and each decorator is recorded with number, name, skills and experiences. The firm also has a number of contractors who work on daily or hourly basis on a particular job. For each job the firm provides an estimate of the amount of time and budget required to client. A job might include several activities, such as planning furniture placement, painting, wall papering, installing floor covering, and so on. Most of the case, the decorator of a job does the planning furniture placement, and other activities are done by contractors. The contractors provide an estimate on labour cost for each activity assigned to them. An activity or job might also require materials such as paint, lumber, timber, etc. The firm has to keep track of the cost of the materials for each activity or job in order to bill the client. The database should store information of its clients, its staffs, jobs, estimated cost and actual costs of all activities and all jobs and related materials. i) You are required to draw an entity relationship diagram that will be used to implement the database for the above software system. Write out any assumptions you made. ii) Convert the ER diagram to the corresponding relational schema (set of relations). Indicating primary keys and foreign keys.
An interior design firm would like to develop an application to computerise its
operations. A client requests that the firm performs a job such as decorating a
new home, redecorating rooms, locating and purchasing furniture, etc. Detail
information of client such as name, address is recorded and a unique client
number is generated, job information is recorded too. The firm has a number
of decorators, one decorator is in charge of a job, and each decorator is
recorded with number, name, skills and experiences.
The firm also has a number of contractors who work on daily or hourly basis
on a particular job. For each job the firm provides an estimate of the amount
of time and budget required to client. A job might include several activities,
such as planning furniture placement, painting, wall papering, installing floor
covering, and so on. Most of the case, the decorator of a job does the
planning furniture placement, and other activities are done by contractors. The
contractors provide an estimate on labour cost for each activity assigned to
them. An activity or job might also require materials such as paint, lumber,
timber, etc. The firm has to keep track of the cost of the materials for each
activity or job in order to bill the client. The
of its clients, its staffs, jobs, estimated cost and actual costs of all activities
and all jobs and related materials.
i) You are required to draw an entity relationship diagram that will be used
to implement the database for the above software system. Write out any
assumptions you made.
ii) Convert the ER diagram to the corresponding relational schema (set of
relations). Indicating primary keys and foreign keys.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
