Reference no: EM132931344

Database Assignment

SITUATION:
Congratulations on your successful understanding of the telecommunications industry. Brad Simpson is so impressed with your understanding of the telecom industry, he would like you to build a database based on your ERD using Microsoft Access. In addition, Simpson has been storing enterprise data in Excel that he would like you to populate the Access database with. This will help Simpson understand how BNC is performing.

REQUIREMENTS:
A) Create a database using Microsoft Access according to the ERD provided below. Use proper naming for the tables and queries. Also, make sure the data types are correct for all attributes. You may create the database manually or by importing each worksheet from the excel data file.

B) Create the required relationships between tables and be sure to enforce referential integrity. Minimum cardinalities are not important in Access so just ensure that the maximum cardinality of each relationship is correct.

CREATE THE FOLLOWING QUERIES AND NAME THEM
ACCORDINGLY

C) Query C: BNC does not know where they have the most customers. Consequently, they are unable to create target marketing programs to areas where they would like to increase the number of customers. Create a query that lets BNC know the sum of customers they serve in each city.

D) Query D: The company must have a formal safety response plan to meet safety standards. Given rising water levels, BNC is concerned about their nodes near Sylvan Lake. Create a query that will generate a list of nodes associated with the location, Sylvian Lake.

E) Query E: There has been an outage at node Red West 4 and the Chief Marketing Officer would like a list of customers on that node. This will enable her to mail a gift card to the customer's address. Please provide her with a report that lists customer name and address details.

F) Query F: You are investigating the usage patterns of various mobile customers. The Chief Engineer would like to know the total mobile data usage of each customer in 2020 ordered to show the largest mobile data users first. The Chief Engineer is not interested in customer names, simply include the Customer ID to distinguish between customers.

G) Query G: The Chief Marketing Officer is not sure about Satellite TV service in the age of internet streaming. What is the current total monthly revenue for the Satellite TV service?

Please note that the names of query field headings are trivial and should not be changed from the default names when the queries are generated. Please ensure that only the required fields are presented. The query results should be nicely formatted with the appropriate information displayed but unnecessary information hidden.

Attachment:- Database.rar