This is for a course called intro to databases.

In this assigment 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 might use MySQL DBMS (I am OK with other options) 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 US (All states). 5. Annual Contract Value (ACV): in dollar. 6. Initial Amount Every Entry should be double. (ex. $101.23) 7. Service start date: Automatically allocate todays 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 2 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 (We will learn this concept next week) b. Create appropriate tables corresponding to this diagram c. Create and populate the tables using MySQL (or other option). 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 West Haven, 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".