Edgy Cloud Computing Inc. is a young and ambitious cloud computing company that has been providing their customers with OLAP and OLTP networked services for the past five years. However, they are constantly struggling with customers complaints over slow response times and laggy device performance. Currently, Edgy Cloud Computing Inc. estimates that it has about 15 billion devices (mostly customers computers and laptops) connected to its cloud database processing servers. It is also concerned that given the current growth trend of smart device perfusions into the market (e.g. smart phones, smart sensors, smart homes, smart cars, smart everything, etc.) the number of networked devices which it has to provide similar services for through their cloud data could reach 55 to 75 billion by the year 2025. In this view, Edgy Cloud Computing Inc. offers to deploy extra database processing servers at the edges of the network closer to sites (physical locations) where customers computers / devices are physically located. These edge database servers will be able to service real-time OLTP requests, where simple but numerous data processing functions are simultaneously and constantly required. The database servers left in the central cloud will then mostly, be engaged with complex and demanding OLAP data requests and queries that are not time-sensitive (and wouldnt cause much of a ruckus if delayed). Each edge database server is offered to customers based on the type of OLTP processing required. Currently, Edgy Cloud Computing Inc. classifies the processing their customers require as Accounting, Point Of Sales (POS), Data Uploads, Data Downloads and Social Media. Any one or more combinations of those services may be required by customers from a physical location / city. The company stores the following information for all database servers: Name, Make, Model, Year, MAC address, Location, Site IP address and Processing specialty. A processing specialty ID and description is stored for each specialty. If more than two servers are located on site (either at the network edge or centrally in the cloud), information records on the percentage processing workload distribution between the site database servers are also stored. Each required site processing specialty elects a database which acts as the central server for that physical area location. This information is also recorded by Edgy Cloud Computing Inc. Each central cloud database server is classified based on the type of OLAP processing required. These have been identified as Image processing, Business Intelligence, Forecasting, Predicting and Recognition activities. Requests for OLAP tasks may require one or more combinations of those services and may be indirectly queried by customers from Edge database servers. Additionally, similar to Edge database servers, the company stores information on Name, Make, Model, Year, MAC address, Location, Site IP address and Processing specialty for each central cloud database server. Also, a processing specialty ID and description is stored for each specialty. Servers in the central cloud cluster are backed up less often than servers at the edges since they are concerned mostly with OLAP services and are not sensitive to data accuracy. On the other hand, Edge servers at the customers site are concerned mostly with OLTP services and are heavily dependent on data accuracy to deliver adequate services for their customers. Edgy Cloud Computing Inc. keeps a record of the version number, content, functionality, description and services update for each backup through mirror servers (mirror cluster). Mirror servers may back up one or more edge and / or cloud database servers in a single scheduled job and are centrally located. At the moment, there is no distinction over which mirror server contains backups from which other edge or cloud database server. However, every edge and cloud database server have to be mirrored to at least one mirror server in the cluster. Finally, Edgy Cloud Computing Inc. offers a variety of payment options for customers using their OLAP and OLTP services. Customers can choose to purchase a variety of plans (Daily, Monthly, Annually, etc) in the form of service passes (Platinum, Gold, Silver, etc.) or pay a single-session Pay-As-You-Go rate. Rates vary depending on the type of services requested. The company is interested in comparing the profitability of short and long-term service passes versus single session pay-as-you-go rates. So for each purchased service pass they record the customer who purchased it, the length of usage it is good for, and the purchase date. The company keeps track of their customer usage by recording the customers names, address, telephone, email, type of service used / requested, processing bandwidth required and duration of use. Customers may request for OLTP or OLAP services or both and in whatever combination they like, from wherever they are.

Provide the following: a) An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.

b) A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.

c) An Oracle SQL table create statement for the relation that you think is most critical in this system. This relation must have a primary key and at least one foreign key.