1. Design a database to store the marketing information that allows business users to generate the reports related to Activities, Campaigns, Programs and Cost for marketing purpose. For example, this database should provide the proper data if the business wants to find out the cost of each activity and list the activities of each Campaign, etc. Normalize the database design into 3NF. Here are the relationships between Activities, Campaigns, Programs, and Invoices: a. A Program may be related to one or multiple Campaigns. b. A Program should include the program id, name, description, created date, who created, modified date and who modified the record of the program. c. A Campaign may be related to one or more Programs. d. A Campaign should include the campaign id, name, description, related program, created date, who created, modified date and who modified the record of the campaign. e. One Campaign may include many activities, and one activity can only be part of one Campaign. f. The expense of each activity is recorded in invoices, and each activity may receive more than one invoices. Note: The attributes of the Campaign and Program are listed above. You need to come up with the meaningful attributes for the entities besides those two.

Ans.

Entities: Campaign, Activity, Program, Invoice. Attributes: 1. Campaign: campaign_id (primary key) name, description, related_program_id (foreign key), created_date, created by, modified_date, modified_by. 2. Activity: activity_id (primary key), name, description, campaign_id (foreign key) expense 3. Program: program_id (primary key), name, description. 4. Invoice: invoice_id (primary key), activity_id (foreign key), expense. Normalization: To normalize the database, ensure that each entity has a unique primary key and no redundant data. First Normal Form (1NF): Each table has a primary key. There are no repeating groups or arrays in any table. Second Normal Form (2NF): Each table is in 1NF. All non-key attributes depend on the primary key. Third Normal Form (3NF): Each table is in 2NF. There are no transitive dependencies. The database design can be normalized into 3NF using the above entities and attributes. In this design, the Campaign, Activity, and Program tables are in 3NF as they have no transitive dependencies. The Invoice table is also in 3NF as all non-key attributes depend on the primary key.

NOTE:

CORRECTION: the relationship between the program and campaign is many to many. You need to create a new relation to handle this situation.