Assignment

The required database should be developed according to the following stages:

1. Develop a conceptual data mode lreflecting the following requirements:

• Identification of the relations (entity types).
• Identification of relationship types and their participation and cardinality constraints.
• Identification of attributes and association of attributes with entity or relationship types.
• Determination of candidate and primary key attributes of entity types.
• Determination of specialization/generalization and categorization relationships, whenever it is appropriate.
• Enhanced Entity-Relationship (EER) diagram to reflect the requirements.

2. Develop a global logical data model based on the following requirements:

• Refinement of the conceptual model - including a refined Enhanced Entity-Relationship (EER) diagram.
• Derivation of relations from the refined conceptual model.
• Validation of logical model using normalization to BCNF.
• Validation of logical model against corresponding user transactions.
• Definition of integrity constraints including:

- Primary key constraints.

- Referential Integrity (foreign key) constraints.

- Entity integrity (NULL and default value) constraints. - Alternate key constraints.

- General constraints if any.

3. Translate the global logical data model for the MYSQL(Google Cloud) or Oracle Enterprise DBMS.

• Development of SQL code to create the entire database schema and reflect its constraints.

• Development of the case study application to interact with the database and enable the transaction requirements (Use any programming language Java, C++, Python, etc..)

4. Interact with the database and enable the transaction requirements. You need to create several sample tuples for each relation in your database.

Report: The report should include a detailed typed documentation of the project's stages, results at each stage, test data, sample output, and conclusion. For example, you should include the EER diagrams for the conceptual data model and the logical data model.

The project will be graded as follows.

Conceptual and Logical Design 40% RDBMS2 Implementation and Application 40% Report 20%

Case Study: Business Contact Interaction Tracking System

Develop a business contact interaction tracking system. Businesses must carefully record information about interaction with important business contacts. This system must include the following:

• Descriptive data for contacts, such as name, salutation, job title, employer, manager, subordinates, secretary, addresses (home, office, mailing), phone numbers (home, office, cellular, fax, secretary), email addresses, and personal comments.

• Data for events of interaction, such as phone calls, email, and postal mail. For each interaction event, record the date and comments.

Your system must also enable searches, such as

• Theinteractioneventsassociatedwithaparticularcontact(forexample,JohnSmith).

• List all the interaction events made during a particular time period (for example, between July 1, 2011 and December 31, 2011).
• List the information for allcontacts available in a particular company (for example, Company ABC).
• List the total number of phone calls made during a particular time period (for example, between January 1, 2014 and January 31, 2014).

Sample Queries

1. List all the interaction events [date, event type, comments] associated with a particular contact.

2. List all the interaction events made during a particular time period (for example, between 07/01/2014 and 12/31/2014). Use MM/DD/YYYY as input format for date.

3. List the information for all contacts available in a particular company (employer).

4. List the total number of phone calls made during a particular time period (for example, between 01/01/2012 and 03/01/2012). Use MM/DD/YYYY as input format for date.

5. List all the subordinates [name, jobtitle, officephonenumber, primaryemailaddress] of a particular contact.

6. List all contacts that have more than 2 subordinates and a secretary.

7. List all companies (employers) and the number of contacts associated with them. The list should be sorted in decreasing order of the number of contacts.

8. List all contacts with greater than 3 interaction events.

9. List total number of phone call events, total number of email events and total number of postalmailevents.

10. List all contacts with home address in a particular city.