Database Project Instructions Background Information:

Group Name> company operates in the UK and US. Due to its success, is expanding throughout the world by taking advantage its own private cloud architecture. In other words, it is leveraging a cloud infrastructure it hosts in its own data centers. Its data centers exist in the major cities of Los Angeles, CA, New York, NY and London, UK.

A distributed database design is necessary that meets the needs of a new cloud-based application that is globally accessible. Your goal will be to design the IT architecture that supports the distributed database(s). This includes example servers (e.g. you can use virtual machines with proper optimizations and the supporting services(s) installed. Additionally, you will design and implement the physical database on this system(s). Once populated, you will write supporting data reports via SQL and optimize these using explain plans. Finally, your group will give professional presentations in the D2L discussion forums on your teams solution and respond to feedback for improvement.

Group Name> currently employs 11,000 personnel split between the UK/US who serve as accountants, human resource experts, systems analysts, developers, managers, testers, maintenance engineers, lawyers, chefs, and marketing experts. Records for each individual are essential, from an associated supervisor to departmental roles.

Group Name> is expanding into the Food Truck business in the US and UK. Food truck clients are recognized by various names, have various organizational structures, and tailor to different food types throughout the US and UK. The sales department refers to food truck clients by typical names while legal uses legal names. An example is the US-based Pizzeria Food Truck network. The network of food trucks have names such as Famous Hollywood Pizza in LA and Worlds Best Slice of Pizza in NYC. s legal team references its full legal name Pizzeria Food Truck, Inc. but the sales, marketing, and consultants denote it as PFT. Both common/legal names must be available to all DBMS stakeholders. Food trucks must associate with at least one larger incorporation. For example, the Famous Hollywood Pizza food truck in LA and the Worlds Best Slice of Pizza in NYC food truck associate with the US- based Pizzeria Food Truck license. All food truck network contact and relevant business information must be stored and recorded in the DBMS.

Group Name>s sales representatives must know a food trucks billing, sales, and contact information. Food trucks can have one or more contracts with to provide a variety of consulting amenities. Some clients have pre-negotiated contracts for which that are billed, these are often referred to as fixed price. Alternatively, hourly contracts exist that require labor rates/hours to calculate billing details. managers must guarantee that when maximum (cap) contact hours are past what the client agreed to, that the

food trucks are not billed for such additional hours. Rate per hour for food truck employees must be researched in the major UK and US cities as estimations for different levels of managers, chefs, network sales force, and network marketers to help promote the food trucks in their regions of operation.

Group Name> food truck employees must use timecards for all hours worked. These must associate with specific food trucks. Personnel can be assigned to many dissimilar food trucks. They can also be re-assigned throughout their food networks. For example, a chef for the Worlds Best Slice of Pizza in NYC can transfer to the Famous Hollywood Pizza food truck in LA. Similarly, a marketing analyst can be promoted from the LA region to Director of Food Truck Marketing in London. A general problem with this is the maximum hours worked in a week. Certain countries have maximum hours per week that an employee can work and therefore this needs to be recorded and monitored by supervisors. Thus, the DBMS must have a carefully planned out Human Resources portal that can manage all types of employees throughout the global company.

In addition to HR functionality, a Project Manager is assigned to every food truck in the US and UK. The project manager is responsibility for all projects associated with the food truck. For example, there may be a project to update the kitchen in the truck. There may be a project that helps market a new menu in a region the food truck operates.

Finally, contracts exist with food trucks and the global company. These contracts help the company keep track of investments in the food trucks. Contracts can consist of multiple projects. They also reduce costs. For example, a new contract could exist for all Pizzeria-based food trucks to add gluten-free pizzas. The contract will have an investment amount and maximum labor hours to implement the new pizzas across all pizza-based food trucks. If a contract specifies the maximum number of hours of labor the company is willing to invest into the food truck, the project managers of each project should agree on how to split up the max cap amounts between the two projects and maintain appropriate reporting on hours. Hours are important in the database, including contract, project, and personnel hours. Billing and invoice management systems will also need access to these hours.

These are some of the minimum requirements that the team must include when designing the distributed DBMS solution for the company. The group should decide on any additional tables necessary to support this new business venture. It must also consider the physical IT infrastructure design and associated changes. For example, the architecture of the distributed DBMS should consider physical database locations in various cities such as LA, NYC, and London to best serve the food trucks. Below are the project deliverables followed by the grading rubrics. Review these closely to assure your group meets the final project requirements.

• One APA formatted paper is necessary that includes the diagrams, screenshots, and configurations of the final solution.
• Use the example APA paper on Purdues Owl site as your template.
  • https://owl.purdue.edu/owl/research_and_citation/apa_style/apa_formattin g_and_style_guide/general_format.html
• Include an introduction, review of related literature that informs the infrastructure and database design (e.g. include best practices, design frameworks, and standards), and develop a strong conclusion to the project.
• Place all relevant artifacts including the design diagrams in appendices.
• Include supporting OS/database configurations in the appendices of the report.
• Include all query results, stored procedures, explain plans, and SQL adjustment differences per the optimization plans in the appendices of the report.
• Include the presentation/slides in an appendix.

• Design an IT infrastructure diagram that supports the distributed database across multiple countries.
  • Use proper modeling software such as Lucidchart, Microsoft Visio, draw.io.
  • Include the proper routing, switching, server, security, load balancing, and virtualization architecture necessary to support a global DBMS.
  • Utilize supporting architectural frameworks and best practices from the review of literature written report.
• Design an optimal Entity Relationship Diagram (ERD) for s global DBMS.
  • Use proper database modeling software.
  • Use crows foot notation, include all details such as attributes, attribute types, relationships, relationship descriptions, and constraints.
  • Normalize the database in the third normal form (3NF) or higher.
  • Quality will be assessed on scalability and whether it is a timeless design.