A database design to maintain contracts of a company

The first phase of the project involves the design of the database.

In this phase you need to design a database that maintain all the "Contracts" of a company. The

Contract Management System (CMS) will serve the employees, managers and other stakeholders

in the enterprise. You should use MySQL DBMS to implement the database system and show its

execution by queries.

Problem Statement:

Following are the requirements for the CMS that you are going to develop.

P-1) CMS system should maintain information about:

1. Departments - (Development, QA, UI, Design, Business Intelligence, Networking)

2. Contracts - (Premium, Gold, Diamond, Silver)

3. Managers - (Manager responsible for the contract)

4. Employees - (Employee ID)

5. Dept.- Employee

6. Dept.- Manager

P-2) A sales associate dealing with the client must capture the following information about the

contract:

1. Name of the company and the responsible of the company (First name, Last name,

middle initial) who is signing the contract.

2. Contact Number - (Count the number of digits).

3. Email-id of the company.

4. Address City, Province and Postal code – Data should be only of Canada (All

provinces).

5. Annual Contract Value (ACV): in dollar.

6. Initial Amount – Every Entry should be double. (ex. $101.23)

7. Service start date: Automatically allocate today’s date from system date to each

contract.

8. Type of service: (Cloud, On-premises).

You need to complete the database design, implement the database, collect appropriate data and

store them into the database. Make sure each table in the database has sufficient number of records such that each query results in a meaningful and reasonable size of output.

What you need to do:

a. Draw an E-R diagram for the CMS system

b. Create appropriate tables corresponding to this diagram

c. Create and populate the tables using MySQL.

d. Answer the following queries in SQL and give the result for your data:

1. Insert a new contract with ACV of $90,000, On-premises services, based in Montreal, with an initial amount of $10,000 given by "GSC Corporation".

2. Insert details of a manager "Juan Vasquez" in the database, who is managing a team of 10 developers and responsible for "On-premises" projects.

3. Provide a list of all managers supervising projects with more than $80,000 ACV.

4. Give a list of all the employees who are working on contracts with ACV of at least 85,000$.

5. List of all the contracts managed by "Juan Vasquez".

What you should hand in:

You should print and submit a report that includes the E/R model for your database design

together with reasonable assumption(s) you made; script to create and populate the tables (SQL),

this must include a list of all relational schemas/tables and attributes within each table with

appropriate data types, identify the key attributes for each table, and show the relationships

among the tables. You should hand in the printout of tuples/records in each table, the script for

the SQL queries and the output of the queries.

Note: Every document related to the project work must be printed and properly bounded with a

cover page indicating your group ID, each member’s student ID and name (official names only,

no nicknames).

Hire a Database Assignment expert for Your Project