Hello, this is a 3 part assignment. One part is on ERD (entity relationship diagram), the second part is on data management, and the third is on data queries.

Everything is included in the attached word document. I WILL ATTACH A Microsoft Access Database (.accdb) file once question is accepted.

There are 3 parts to this assignment

Part 1 ERD

Data is “huge” when it comes to understanding customer patterns. The more data you have, and the more analysis you are able to do, the better you will understand what makes customers tick. Below is a letter from Jeremy Farr regarding your assignment.

For the survey, please have at least 8 items, excluding account IDs and demographic data such as customer names, account IDs, addresses, telephone numbers, email addresses, etc. You can be creative about the items, which will become fields in a database. Please make sure the data can be incorporated into a database and are useful for analysis. The survey must be neatly laid out but there is no need to focus unnecessarily on the design aspects since we did not cover it in class. The objective is to have relevant survey items that can be incorporated in a database and be useful for analysis. Include a short explanation of (1) why you chose these survey items and (2) how they will be measured (if not immediately apparent and/or if applicable). The rationale for the survey items must be based on the problem given below. There is no need to actually collect the data.

For the ERD, please include at least 4 relevant entities generated from your survey items. For instance, two survey items, such as customer name and telephone number can become a single entity for customer. You may include customer as an entity. There is no need to have all possible attributes. But please have relevant/suitable ones for each entity. Some attributes can be pointless, depending on the design of your database. And we don’t want to have unnecessary ones. Generally, each entity should have at least 2 attributes. You may choose not to have a primary key for any particular attribute if it doesn’t apply. Think about the problem below and what data (and hence information) are relevant. You are to use Visio (or equivalent) and paste the ERD in a word document, along with the survey. You can be creative with the entities.

For the survey and ERD, there is no perfect answer but there are clearly solutions that are better than others.

Part 2 Data Management

Select any one functional area in your organization. Think about the data involved in the processes. Based on your selection, use an Entity Relationship Diagram (ERD) to depict the data model of the functional area. The examples covered in class may be useful in helping you select a functional area for analysis. Please be sure to select something that is manageable. Please also have 4 entities in your ERD. There is no need to have all possible attributes. But please have relevant/suitable ones for each entity. Some attributes can be pointless, depending on the design of your database. And we don’t want to have unnecessary ones.

Your ERD must be constructed using Visio (or equivalent). Paste the ERD in a word document as a picture for submission. If you do not have access to Visio, you can conceptualize it and use the computer in the lab. This will minimize the amount of time you need using the software itself.

Submit your work on D2L as a single word document (or pdf) by the due date.

Part 3: Data Queries

ManyBooks.com was developed by JK Rowling as an open marketplace for books online, a one-stop e-commerce search engine where you can search through more than 100 million new, used, rare, and out-of-print books for sale.

Ms. Rowling understands that you have expertise in databases. She needs help developing some custom queries for her sales and marketing team members. She has provided you with a sample data file, DA233_S1X-II.accdb. Specifically, what Jane needs is:

1. Create a table showing the telephone number of each author. Streamline your database so you don’t have too much redundancy. Be sure to include the input mask for the telephone number. Make sure the formats are correct. You may make up your own telephone numbers. Save the table, giving it and the fields appropriate names. Hint: The input mask can be set if you choose the text data type for telephone number. (2 pts)
2. Create relationships for the tables and enforce referential integrity. You should be able to generate the necessary relationships for all tables. Hint: To create relationships, click database tools at on the ribbon and you’ll see relationships. Drag the tables you want into the design canvas and build the relationships accordingly. You should be able to tell where the relationships are based on the fields. (2 pts)
3. A query of all authors who have written a book that costs more than $10 and is a novel. The query needs to have the author’s First Name, Last Name, Title of Book, Price, Category, and Rep displayed. Save the query as AuthorsQuery. (2 pts)
4. A query that calculates a 15 percent increase in the purchase price of each book. Sort the query on the new calculated field. Save the query as BookPricesQuery. (3 pts)
5. A query that updates the price of each book by subtracting 50 cents. Save the query as Less50CentsQuery. (3 pts)