An Interior design firm would like to have a database to represent its operations.

A client (customer) requests that the firm perform a job such as decorating a new home, redecorating rooms, locating and purchasing furniture, and so forth. Each job requested by a client, has a start date, completion date, job description and a cost estimate, and cost at completion.

Each job is broken up into activities. A job might include several activities such as painting, installing floor covering, fabricating draperies, wallpapering, constructing, and installing cabinets and so on. One of the firm’s decorators is placed in charge of each job to monitor and complete those jobs.

For completing each activity, the firm hires contractors. Each activity is completed by a contractor. The contractors hired can be a daily contractor or on hourly basis. The contractor is paid based on estimate provided by the contractor for each activity.

The company keeps track of the materials used for each activity. Each activity might require materials such as paint or lumber, and the firm keeps track of the cost of materials for each activity or job, in order to know how much is spent on each activity and through that, for each job.

Draw complete ER diagram for this example.

1) Enter the relationships in the relationships and cardinalities table below.

List of relationships and cardinalities: (feel free to add more rows or delete some, as needed).

Relationship name:			Cardinality
Parent Entity	Child Entity	Relationship Type (strong/weak for each entity)	Maximum Cardinality	Minimum Cardinality

2) Write out the relationships about how each pair of the entities are related, mentioning maximum and minimum cardinalities ?

3) Determinations/assumptions needed for above relationships to hold good ?

4) Determine the entities needed and all their attributes. (list the entities that you plan to use in your design.

5) Identify the domain (sql server data type) for each attribute for entities. (Create a datadictionary/table as shown below, that lists the entities and their attributes. If an attribute is an identifier or composite Identifier mention that fact next to the attribute(s) in parenthesis. Note down the SQL server data types against the attribute names).

Name of the Entity	Attribute (ID)	Null/NotNull or other constraints	SQL Server Data type	PK/FK/AltK (if FK specify Cascade Actions if any & constraints

6) Draw an ERD with the entities and attributes you identified. Mark maximum and minimum cardinalities on the diagram in the appropriate notation using crow’s feet notation. Stick to design aspect of ERD and do not include the keys in your ERD.