Build a MS Access Database

A company with approximately 50 employees requires a database to keep track of all employees, their skills, projects assigned, and departments worked in. Every employee has a unique number assigned by the firm, required to store his name and date of birth. If an employee is currently married to another employee of the company, the date of marriage and who is married to whom must be stored; however, no record of marriage is required if an employee’s spouse is not an employee. Each employee is given a job title (for example, engineer, secretary, and so on). An employee does the only one type of job at any given time, and we only need to retain information for an employee’s current job. • Company is organized into departments. There are 11 different departments (Research, Development, Production, Logistics, Engineering, Regulatory, Marketing, Sales, Legal, Administration, Facilities), each with a unique name. Each department has a phone number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. An employee can report to only one department. • A department controls several projects, each of which has a unique name, a unique number, and a single location. Examples of project titles: Diabetes gene therapy, 3D printed medical device implants, Bioprinting, Advanced Minimally Invasive Surgical Solutions, Medical Imaging, Wearables, Biosensors, Clinical Diagnostics. • To procure various kinds of equipment, each department deals with many vendors. A vendor typically supplies equipment to many departments. We are required to store the name and address of each vendor and the date of the last meeting between a department and vendor. • Many employees can work on a project. We store each employee’s name, social security number, address, salary, sex, and birth date. An employee is assigned to one department but may work on many projects, which are not necessarily controlled by the same department, but can only be assigned, to at most one project in a given city. For each city, we are interested in its state and population. • An employee can have many skills (preparing material requisitions, checking drawings, and so on), but she or he may use only a given set of skills on a project. For example, an employee Smith may prepare requisitions for the Diabetes Gene Therapy project and prepare requisitions as well as check drawing for Bioprinting. Employees use each skill that they possess in at least one project. Each skill is assigned a number, and we must store a short description of each skill. Projects are distinguished by the project numbers, and we must store the estimated cost of each project. We keep track of the number of hours per week than an employee works on each project. We also keep track of the direct supervisor of each employee. • Company wants to keep track of the dependents of each employee for insurance purposes. We keep each dependent’s first name, sex, birth date, and relationship to the employee. • Company also would like to keep track of their competitors working in the medical device space which offer orthopedics or sport medicine products. • They want to keep list of products offered, their clinical use, the cost, selling price, coding (for insurance), rivals market share, and how they are compared to the competitors (ranking). • They would like to keep track of current investors and prospective investors who invest in the medical device and gene therapy spaces (name, amount, dates, phone, address, emails), • They would also like to track customers, contacts, and who established the contact or customer contract. • They also want to keep track of inventions and competitors’ inventions, list of patents, names of inventors with date of invention, description, main publications.