This competency assessment assesses the following Outcome(s):
IT163-2: Create relational databases with multiple entities and relationships.
GEL-1.02: Demonstrate college-level communication through the composition of original materials in Standard English.
Module Outcomes:
Report on objects in the Microsoft Access database.
Construct tables in Microsoft Access.
Module 2 Assessment
Purpose
This assessment introduces you to the Microsoft Access database application. Using what you learned in your reading about relational databases and gathering database requirements, you will write a paper in which you demonstrate your understanding of the components of the Microsoft Access relational database system. You will also apply what you learned to translate this physical model into an actual Access database.
Assessment Instructions
This assessment will consist of two parts.
PART 1:
Before you begin developing the actual database, you need to do a bit of research. Database administrators today continually research customer’s needs when designing and developing a database. This activity will help prepare you to conduct this type of research.
You will compose an essay that fulfills the following requirements:
Define the terms database, table, record, field, and value and describe the hierarchy that exists between these terms.
Demonstrate knowledge of a relational database. Your goal is to demonstrate that you understand what a relational database is.
Describe the six database objects available in Microsoft Access.
Describe the five-step design method discussed in your reading.
The paper must be written in your own words. You are encouraged to read the assigned readings first to gain valuable insight for your paper.
PART 2:
You must have the Microsoft Access database system installed for this part.
Using your physical model, create a database in Microsoft Access. Each entity in your diagram represents a table object in your database.
Start by creating a new, blank database.
Reference:
Alexander, M., & Kusleika, D. (2019). Access 2019 bible (1st ed.). Wiley.
Chapter 2: “Getting Started with Access”
How to Create a Blank Database
Create the tables in your database. Use the table names, field names, primary key information, and data types from your physical model to design your tables.
Hints:
Use the “AutoNumber” data type to assign unique values for primary key fields.
Set the required field property to yes for fields that must have a value. For fields you had identified as nullable (allowed to be blank), you can leave the default value of no.
Reference:
Alexander, M., & Kusleika, D. (2019). Access 2019 bible (1st ed.). Wiley.
Chapter 3: “Creating Access Tables”
Creating a New Table
Changing a Table Design
Setting the Primary Key
For extra help see Optional Resources for Assessment under the Academic Tools, Library link:
Open each table you created in the design view and take a screenshot (showing the fields, data types, and field properties in the design view for each table you defined).
Include all screenshots within the same document created for the written requirements in part 1 above. Append the screen shots to the end of the document before the final references page.
Assessment Requirements
Written work should be free of spelling, grammar, and APA errors, and contain no evidence of plagiarism. Follow all APA Style guidelines, including properly formatted title and reference pages. Your paper must be at least 2 pages, double-spaced, excluding the title and reference pages.
For more information on APA Style formatting, go to Academic Writer, formerly APA Style Central, under the Academic Tools area of this course.
Assignment Details: Conceptual and Relational Modeling Background You have been hired by an organaization as an IT consultant in the Athletics department to help them organize data gathered from two Excel worksheets representing high school recruiting.
attached working sheets.
Tasks Create a single Word document. Place your name at the top of the document. Address each of the following tasks. Each task in your Word document must be numbered so that it is clear which task you are answering.
1. Using the worksheets above, write the business rules that govern the relationship between COACH and ATHLETE. Hint: Remember relationships are always bidirectional.
2. Using Visio or Lucidchart, create a conceptual model in Crow’s Foot notation to model the business rules you just identified. Make sure you properly label the entities and relationship using best practices covered. Include your name in bold in a text box on the diagram. Paste a screenshot of your completed diagram.
3. For additional data modeling practice with, recreate the conceptual model shown below, exactly as is. Replace the middle of the diagram with your name in bold in a text box. Paste a screenshot of your completed diagram.
attached a picture
Use the completed conceptual model you created in #2 and the Excel worksheets to answer all remaining questions.
4. To convert the conceptual model into a relational model, list the relations that would be present in the relational model. For each relation you list, identify the primary key and foreign keys (if there are any).
5. Using your data modeling tool, create a relational model to model the relations you just identified. Make sure you incorporate best practices covered for creating relational models.
6. Using the relational model you just created, write the relational schema correctly identifying each relation and its primary and foreign key(s) (if there are any)
7. Do all relations in your relational model exhibit entity integrity? Using course terminology, explain why or why not.
8. Do all relations in your relational model exhibit referential integrity? Using course terminology, explain why or why not
CLICK HERE TO MAKE YOUR ORDERon Conceptual and Relational Modeling Background You have been hired by an organaization as an IT consultant in the Athletics department to help them organize data gathered from two Excel worksheets representing high school recruiting.
For this discussion, you will find an article in a scholarly journal dealing with items from the reading of chapters from chapter 5 to 15 of the textbook (Gelinas, Dull, Wheeler, & Hill: Accounting Information Systems (11th ed.). Topics include Relational databases, Enterprise Risk Management, Pervasive Controls, and Business Process and Controls. Then, you will write a summary on this article.
Thread of at least 1000 – 2000 words.
Must support their assertions with at least 4 scholarly citations, and biblical references as applicable, in current APA format
Original and non-plagiarized custom papers. Our writers develop their writing from scratch unless you request them to rewrite, edit or proofread your paper.
Timely Delivery. capitalessaywriting.com believes in beating the deadlines that our customers have imposed because we understand how important it is.
Customer satisfaction. Customer satisfaction. We have an outstanding customer care team that is always ready and willing to listen to you, collect your instructions and make sure that your custom writing needs are satisfied
Privacy and safety. It’s secure to place an order at capitalessaywriting.com We won’t reveal your private information to anyone else.
Writing services provided by experts. Looking for expert essay writers, thesis and dissertation writers, personal statement writers, or writers to provide any other kind of custom writing service?
Enjoy our bonus services. You can make a free inquiry before placing and your order and paying this way, you know just how much you will pay. A verdict was rendered against three parent chaperones. How was the third parent included in the case?
Premium papers. We provide the highest quality papers in the writing industry. Our company only employs specialized professional writers who take pride in satisfying the needs of our huge client base by offering them premium writing services Relational databases, Enterprise Risk Management, Pervasive Controls, and Business Process and Controls.
For the data model Employment Agencies, copy and paste that data model graphic into your paper using MS Word. (I attached graph to the Question) In the MS Word document, list the following:
all the entities
all the attributes in each entity
the primary key for each entity
all the foreign keys
all the relationship cardinalities between entities (e.g., a professor can teach one to many classes; an individual class is taught by one and only one professor)
for each relationship between entities, specify the name of the attribute in one entity that will be used to match to an attribute in a related entity, including the specific attribute name in the second entity that is used for the matching. For instance, in a school data model: “The Student_ID attribute in the Registered_Class entity is used to match to the Student_ID attribute in the Enrolled_Student entity.”
Submit your MS Word document.
Resources:
Textbook: Module 3 – The Relational Data Model
Video: Module 3 – Relational Database Tables and Their Characteristics (3:13 min.)
Original and non-plagiarized custom papers. Our writers develop their writing from scratch unless you request them to rewrite, edit or proofread your paper.
Timely Delivery. capitalessaywriting.com believes in beating the deadlines that our customers have imposed because we understand how important it is.
Customer satisfaction. Customer satisfaction. We have an outstanding customer care team that is always ready and willing to listen to you, collect your instructions and make sure that your custom writing needs are satisfied
Privacy and safety. It’s secure to place an order at capitalessaywriting.com We won’t reveal your private information to anyone else.
Writing services provided by experts. Looking for expert essay writers, thesis and dissertation writers, personal statement writers, or writers to provide any other kind of custom writing service?
Enjoy our bonus services. You can make a free inquiry before placing and your order and paying this way, you know just how much you will pay. A verdict was rendered against three parent chaperones. How was the third parent included in the case?
Premium papers. We provide the highest quality papers in the writing industry. Our company only employs specialized professional writers who take pride in satisfying the needs of our huge client base by offering them premium writing services The Relational Data Model
ASSESSMENT BRIEF Subject Code and Name ACCT6001 Accounting Information Systems Assessment Assessment 4 – Database Application – Case Study Individual/Group Individual Learning Outcomes (b) Explain the characteristics of relational databases and their role in creation and communication of business intelligence. (c) Identify and assess IT controls, auditing, ethical, privacy and security issues with respect to information. (d) Apply technical knowledge and skills in creating information for the workplace using spreadsheets and relational databases. (e) Communicate with IT professionals, stakeholders and user groups of information systems. Submission By 11:55pm AEST/AEDT Sunday end of Week 11/Module 6.1 For Intensive class: By 11:55pm AEST/AEDT Sunday end of Week 6/Module 6.2 Weighting 30% Total Marks 100 marks Context: The aim of this assessment is to assess the student’s ability to solve business problems using database design tools and software. It also aims to enable students to think about the impacts of using IT in Businesses and communicate key issues through a written report. Many businesses depend on the accurate recording, updating and tracking of their data on a minute-to-minute basis. Employees access this data using databases. An understanding of this technology allows business professionals to be able to perform their work effectively. Submission Instructions You need to design and implement the database using Microsoft Access. (1) Provide a word document containing the following: a) Introduction – a summary of the case study b) Compare different relational databases (e.g. Microsoft Access and SQL Server) c) Database Design: E-R Diagram d) Outline how distributed database technology (e.g. Blockchain) differs from traditional databases like Microsoft Access. (2) Provide the Microsoft Access files used in creating the tables, forms, queries and reports Janie’s Pet Minding Janie is a local supplier of pet walking and sitting services for business people. The plan is to eventually grow the business to also provide online accessories to pet owners. A few years after starting, the business has experienced growing demand for the services offered. Subsequently, Janie needs an accounting system to record transactions and manage the details of customers and their pets. Due to the growth of the business, Janie wants to hire a part-time worker to develop and manage a system for processing orders. Having successfully applied for the job, you begin researching with Janie about how the company works. YOU: Tell me about how you take orders. JANIE: When a potential customer requests a job, I first take down all of the customer information, such as the customer’s: first and last name, street address, city, state, and postcode. I also request their phone number (work and mobile) along with their email address in case I have urgent matters that need to be attended. YOU: Sorry to interrupt, but can some customers have the same name? JANIE: Yes. As I was saying, then I record all of the information about their pets, such as the pet’s name, date of birth, gender, type of animal, a photo, along with details of accessories used by the pet. YOU: What other information would you like to record in the database? JANIE: Well, I then show the customers a list of the services that I offer. Here, I detail the services including day stays, walking, overnight stays and pet taxi services. Each has a different cost, which needs to be recorded in the database. When the customer is ready to order a service, I complete a form with information about the customer, pet, pickup date, and payment method (Cash, credit card, cheque, account) recorded on the top of the form. However, I often make copying mistakes and would appreciate it if I did not have to enter repetitive information each time. I then put the date at the top and usually include an order number that I have created. After that heading, I list the service(s) the customer wants, including quantity. YOU: What other information would you like to get out of the database? JANIE: I would like to be able to: • take orders quickly, because certain times are very busy for me. • show how many services are due to be performed each week. • change prices easily, because the cost of providing services is rising fast. YOU: I can create a form for you to quickly enter details about the services offered, and queries will help you with the other two tasks. I can also help you manage your business with specific queries that might help you improve the services offered to clients. Can you think of some analysis that would be helpful? JANIE: To improve my advertising campaigns and overall management of the business, I would love to see: • my best-selling services • who my best customers have been • a report of what accounts remains unpaid as well as a break down of how customers have paid (for bank reconciliation and cash management purposes). Before running the report, I would also like an easy way to update the payment records in my database. Required: (1) Why should Janie implement a Microsoft Access database for her business as opposed to another database solution such as Microsoft SQL Server? Provide justification for your answer by comparing different types of databases. (2) Create an E-R Diagram for Janie’s business using a Diagram Editor or Microsoft Access’ relationship manager. (3) Create tables using the following guidelines. (a) Enter records for at least ten customers. Use a hypothetical name, address, phone number, and e-mail address details to create customer records. Assume that the customers are located in Australian cities and states. (b) Enter at least four records for the services offered including: day stays, walking, overnight stays and pet taxi services. Assume that all apparel except headwear comes in small, medium, and large sizes. (c) Each customer should have at least one order; a few customers should place two orders. Each order should contain at least one service and some must include multiple services. (d) Appropriately limit the size of the text fields; for example, a telephone number does not need the default length of 255 characters. (4) Create forms using the following guidelines (a) Form 1: Create a form based on your Pets table. Save the form as “Form 1_Pet”. Your form should resemble that in Figure 1 and include a photo of the pet. Figure 1 (b) Form 2: Create a form and sub-form based on your Orders and Order Details tables. Save the form as “Form 1_Order”. Your form should resemble that in Figure 2. Figure 2 (c) Form 3: Create a form and save the form as “Form 3_Main Menu”. Your form should include links to each of the forms in your database by embedding a macro when a Shape is clicked as shown in Figure 3 (Note: pictures are not required). Figure 3 (d) Form 4: Create a form and save the form as “Form 4_Main Menu”. Your form should include links to each of the forms in your database by embedding a macro when a Shape is clicked as shown in Figure 4 (Note: pictures are not required but be consistent in your presentation of forms). Figure 4 (5) Create the following queries in your database (a) Query 1: Create a select query called “Query 1_Services Due” that displays a list of all services that are due to be performed within a selected date range. Your output should resemble that shown in Figure 5, although your data will be different. Figure 5 (b) Query 2: Create a parameter/update that prompts for the incremental amount of a price increase and then prompts for the service ID or service description. Save the query as “Query 2_Price Change”. Test the query by adding $0.25 to the cost of a service. View the changed price in your datasheet view of the updated table. (c) Query 3: Create a query called “Query 3_Best Selling Services”. List the service descriptions and determine how many orders and the quantities that have been received for each service. Report the amounts in columns labelled “Hours Ordered” and “Number of Orders” (Note: these column labels will require changes from the default setting provided by the query generator). Sort the output. Your output should resemble the format shown in Figure 6, but the data will be different. Figure 6 (d) Query 4: Create a query called “Query 4_Best Customers” that lists the customers’ names, phone numbers, hours ordered, number of orders and the total amounts of their orders (Note: these column labels will require changes from the default setting provided by the query generator). You will have to calculate the order amounts and sort the output. Your output should resemble the format shown in Figure 7, but the data will be different. Figure 7 (e) Query 5: Create an action/update query that change the “Paid” field of orders with a “Cash” payment method by prompting the user to enter a “from date” and “to date” range. Save the query as “Query 5_Paid Change”. Test the query by running the query for the first week of cash payments stored in the order table. View the changed values in your datasheet view of the updated table (6) Create the following reports in your database (a) Report 1: Create a report named “Report 1_Customers by Location”. The data for the report will originate from a query, in which all customers who order services between a pre-defined date range are included in the report (use the parameters and a criteria filter such “ =[StartDate] And [EndDate]+1)” to filter the query data. Then you calculate the Total sales (Price multiplied by the Quantity of services ordered in that date range). The date range for the query should be displayed at the top of the report. The report’s output should be grouped by Suburb and include headings for Street Address, First Name, Last Name, Customer Email, and Total. Include a Total for each group (suburb) displayed in a report. Make sure that all column headings and data are visible and that all money amounts are formatted properly into currency. Depending on your data, your output should resemble that shown in Figure 8. Figure 8 (b) Report 2: Create a report named “Report 2_Orders by Payment Method”. Bring the Favorite Colors query output into a report. Save the report as Favorite Colors. Make sure that all column headings and data are visible. Depending on your data, your report should resemble that in Figure 9. Figure 9 (7) Explain to Janie how distributed database technologies such as Blockchain differ from traditional databases like Microsoft Access? Learning Rubric: Assessment 1 Assessment Criteria Fail (Unacceptable) 0-49% Pass (Functional) 50-64% Credit (Proficient) 65-74% Distinction (Advanced) 75 -84% High Distinction (Exceptional) 85-100% Implementation and comparison of databases 15% Unsatisfactory explanation provided is very general, lacks detail, does not answer the question. Satisfactory explanation provided, several justifications discussed, lack detailed explanation. Good explanation provided, several justifications discussed. Explanations are detailed but some are not convincing. Very good explanation and justification. Explanations are convincing. Main problems are identified and discussed in details. Provided some examples. Excellent explanation and justifications provided. Main problems are identified and prioritised. Each problem is discussed in detail. Examples were provided for each problem. E-R Diagram 15% E-R Diagram is incorrect. More than 50% of the entities, attributes and relationships identified are incorrect. E-R diagram is complete but some attributes and relationships are incorrect. Some entities identified should not be an entity. Around 50% of attributes are missing (or incorrect) and incorrect relationships are identified. E-R diagram is complete. All entities identified are correct. Around 25% of the attributes and relationships are missing or incorrect. E-R diagram is complete. Identified entities were correct and complete. Attributes were correct and complete. One or two relationships were missing (or incorrect) E-R Diagram is complete. All entities, attributes and relationships are identified correctly. Database Tables 20% Database tables are incorrect and incomplete. E-R Diagram is not correctly translated to database tables. Majority of the tables, attributes are missing. Database table is complete. All entities are translated into tables. Some attributes of the ER Diagram are not in the table and types of attributes are incorrect. Database table is complete. All entities are translated into tables. Attributes are all present and relationships among tables are established (primary keys and Database table is complete. All entities are correctly and completely translated into tables. Attributes and types of attributes were correctly identified. All data in the table were entered. Database table is complete. All entities are correctly and completely translated into tables. Attributes and types of attributes are correctly identified. of 11 Not all entities were translated into tables. Tables are not normalised. Relationships are not translated to tables (primary keys and foreign key are incorrect). Tables are not normalised. Some data in the table were entered. foreign keys are mostly correct) All data in the table were entered. Most of the attributes have correct types. Some tables are normalised. Most table relationships are correct (primary keys and foreign keys and their relationship have been mostly correctly established). All tables are normalised to third normal form. All data in the table are entered. All table relationships are correct (primary and foreign keys and their relationships are all correctly identified) All tables are normalised to third normal form. Queries 25% SQL queries and generated are mostly incorrect. Complex queries are not answered More than 50% of the SQL queries are correct. Complex queries are incorrect. More than 75% of the SQL queries are correct. Complex queries are mostly incorrect. Almost all queries are correct (one or two incorrect queries). Some complex queries were answered. All SQL queries are correct. Can generate complex queries. Forms and Reports Unsatisfactory forms. Forms does not enter data. Reports are mostly incorrect. No report formatting used. Satisfactory forms. Some links to the tables are not functioning. Some data cannot be entered in the tables via forms. Good forms. Most data can be entered in the tables via forms. Some design were incorporated in the forms but not professional looking. Very good forms. All elements of the forms are created. All data can be entered via forms. Design were incorporated in the forms. Somewhat professional looking. Excellent form. All elements of the forms are created. All data can be entered via forms. Professional looking forms. 15% Simple design incorporated in the forms. Not professional looking. More than 50% of the reports are correct. No report formatting used. More than 75% of the reports are correct. Some report formatting used. Almost all of the reports are correct (one or two incorrect). Report formatting used. All of the reports are correct Report formatting used extensively. of 11 Blockchain (distributed) databases versus traditional (relational) databases Makes assertions about the ethical, privacy and security issues that are not justified. Some issues are justified by arguments and not merely assertions. Issues identified are appropriate. Arguments are presented to justify the issues. Issues identified are appropriate. Arguments are presented to justify the issues. Issues identified are appropriate. Arguments are presented to justify the issues. Takes into account the complexities of the issues. Other viewpoints were acknowledged. Accurately takes into account the complexities of the issues. Other viewpoints were acknowledged. Accurately takes into account the complexities of the issues and provided examples. Other viewpoints were acknowledged. 10% Justifies any conclusions reach with arguments and not merely assertions. Justifies any conclusions reached with well-developed arguments and not merely assertions. Justifies any conclusions reach with well-developed arguments and well articulated viewpoint on the issues and substantiated by research. of 11
Timely Delivery– primewritersbay.com believes in beating the deadlines that our customers have imposed because we understand how important it is.
Customer satisfaction- Customer satisfaction. We have an outstanding customer care team that is always ready and willing to listen to you, collect your instructions and make sure that your custom writing needs are satisfied
Writing services provided by experts- Looking for expert essay writers, thesis and dissertation writers, personal statement writers, or writers to provide any other kind of custom writing service?
Enjoy Please Note-You have come to the most reliable academic writing site that will sort all assignments that that you could be having. We write essays, research papers, term papers, research proposals. Explain the characteristics of relational databases and their role in creation and communication of business intelligence
ASSESSMENT 2 BRIEF Subject Code and Title MIS602 Data Modelling and Database Design Assessment Database Implementation Individual/Group Individual Length N/A Learning Outcomes The Subject Learning Outcomes demonstrated by successful completion of the task below include: b) Design solutions applying relational database techniques to complex problems and communicate these solutions to all stakeholders. Weighting 35% Total Marks 100 Marks Task Summary In this assessment, you are required to demonstrate your ability to understand the requirements for various data information requests from an existing database and develop appropriate SQL statements to satisfy those requirements. Context Being able to query a database is a fundamental skill that is required by all information systems professionals who work with relational databases. In this assessment, you will utilize the basic query skills that are typically used to extract information for analysis, reporting and data cleansing in a data management setting. Timely provisioning of key business information promotes effective communication and enhanced solution delivery. Task Instructions
Please read the attached MIS602_Assessment 2_Data Implementation_ Case study.
Examine the relational model and ER diagram and derive the SQL queries to return the required information.
Provide SQL statements and the query output for the following:
List all the customers who live in any part of CAULFIELD. List only the Customer ID, full name, date of birth and suburb (5 marks)
List all of the active staff. Show their Staff ID, full name and weekly salary assuming that they work a 38 hour week (3 marks)
Which plan has the most expensive contract to break? (5 marks)
Which brands of mobile phone does this company sell? List only the unique brand names (3 marks)
Which customer is not able to purchase a phone? Use a query to explain why. Hint: Review the customer data (3 marks)
How many of each phone plan have been sold? (5 marks)
What is the average age of an Apple phone user? (8 marks)
What are the first and most recent mobile phone purchases? (3 marks)
i. For calls made in 2018 how many calls were made on the weekend? (5 marks) ii. For calls made in 2018 how many calls were made on each day of the weekend? (3 marks) MIS602_Assessment _2_Brief_Database Implementation_Module 4.2 Page 1 of 3
Provide a listing of the utilization of each tower and its location i.e. how busy each tower is based on the number of connections. Put the busiest tower at the top of the list (3 marks)
Did any users on the ‘Large’ plan exceed their monthly allowance during August 2018? (8 marks)
The company is upgrading all their 3G towers from to 5G. i. How many towers will be upgraded? (1 mark) ii. what SQL will be needed to update the database to reflect the upgrades? (2 marks)
i. List the full name, join date, resigned date of each staff member and name, join date and resigned date of their manager (3 marks) ii. What do you observe with the data? (2 marks)
How much revenue was generated in 2017 by each plan from call charges. Format the output as currency i.e. $123.45 (8 marks)
List the customers who made phone calls longer than 200 minutes (5 marks)
Which customers have more than one mobile phone? List the customer name, suburb and state. Order by the customer name (8 marks)
Are there any mobile phone plans that are currently unused? This can be obtained a number of ways. Demonstrate this by using the following two query types i. Nested subquery (3 marks) ii. Outer join (3 marks)
List the oldest and the youngest customers in the postcodes 3000 and 3102. Show the customer full name age and suburb details (3 marks)
i. Create a view that shows the popularity of each phone colour (1 mark) ii. Use this view in a query to determine the least popular phone colour (2 marks)
The billing team is getting returned mail because of bad customer addresses. This is causing a loss in revenue. i. Review the customer data and find at least 3 issues (3 marks) ii. Provide the SQL statements to correct the data problems (2 marks) Total 100 marks
The database used for this assignment is available in ‘MIS602_Assessment 2_Database SQL Files’ folder. Please download the files from this folder.
All statements and first 10 lines of output need to be neatly presented. Please save all your SQL statements and output into a single word document and submit it via the Assessment link in the main navigation menu. Referencing It is essential that you use appropriate APA style for citing and referencing research. Please see more information on referencing here http://library.laureate.net.au/research_skills/referencing Submission Instructions Submit Assessment 2 via the Assessment link in the main navigation menu in MIS602 Database Modelling and Database Design. The Learning Facilitator will provide feedback via the Grade Centre in the LMS portal. Feedback can be viewed in My Grades. Academic Integrity Declaration I declare that except where I have referenced, the work I am submitting for this assessment task is my own work. I have read and am aware of Torrens University Australia Academic Integrity Policy and Procedure viewable online at http://www.torrens.edu.au/policies-and-forms I am aware that I need to keep a copy of all submitted material and their drafts, and I will do so accordingly. MIS602_Assessment 2_Brief_Database Implementation_Module 4.2 Page 2 of 3 Assessment Rubric Assessment Criteria Fail (Unacceptable) 0-49% Pass (Functional) 50-64% Credit (Proficient) 65-74% Distinction (Advanced) 75 -84% High Distinction (Exceptional) 85-100% ? Correct and complete queries ? Display only required information ? Demonstration of additional AQL knowledge 100% Queries are incorrectly answered with marks only sufficient to attain a F(ail). Queries correctly answered with sufficient marks to attain a (P)ass Queries correctly answered with sufficient marks to attain a (C)redit. Display only required information. Queries correctly answered with sufficient marks to attain a (D)istinction. Display only required information Display additional SQL knowledge such as column formatting, table alias, additional calculations etc Queries correctly answered with sufficient marks to attain a (HD) High Distinction. Display only required information. Display additional SQL knowledge. Well formatted queries. Results returned in a meaningful order. MIS602_Assessment 2_Brief_Data Implementation Module 4.1 Page 3 of 3
Customer satisfaction- Customer satisfaction. We have an outstanding customer care team that is always ready and willing to listen to you, collect your instructions and make sure that your custom writing needs are satisfied
Writing services provided by experts- Looking for expert essay writers, thesis and dissertation writers, personal statement writers, or writers to provide any other kind of custom writing service?
Enjoy Please Note-You have come to the most reliable academic writing site that will sort all assignments that that you could be having. We write essays, research papers, term papers, research proposals. Examine the relational model and ER diagram and derive the SQL queries to return the required information
ASSESSMENT 2 BRIEF Subject Code and Title MIS602 Data Modelling and Database Design Assessment Database Implementation Individual/Group Individual Length N/A Learning Outcomes The Subject Learning Outcomes demonstrated by successful completion of the task below include: b) Design solutions applying relational database techniques to complex problems and communicate these solutions to all stakeholders. Submission Due by 11:55pm AEST/AEDT Sunday end of Module 4.2 Weighting 35% Total Marks 100 Marks Task Summary In this assessment, you are required to demonstrate your ability to understand the requirements for various data information requests from an existing database and develop appropriate SQL statements to satisfy those requirements. Context Being able to query a database is a fundamental skill that is required by all information systems professionals who work with relational databases. In this assessment, you will utilize the basic query skills that are typically used to extract information for analysis, reporting and data cleansing in a data management setting. Timely provisioning of key business information promotes effective communication and enhanced solution delivery. Task Instructions
Please read the attached MIS602_Assessment 2_Data Implementation_ Case study.
Examine the relational model and ER diagram and derive the SQL queries to return the required information.
Provide SQL statements and the query output for the following:
List all the customers who live in any part of CAULFIELD. List only the Customer ID, full name, date of birth and suburb (5 marks)
List all of the active staff. Show their Staff ID, full name and weekly salary assuming that they work a 38 hour week (3 marks)
Which plan has the most expensive contract to break? (5 marks)
Which brands of mobile phone does this company sell? List only the unique brand names (3 marks)
Which customer is not able to purchase a phone? Use a query to explain why. Hint: Review the customer data (3 marks)
How many of each phone plan have been sold? (5 marks)
What is the average age of an Apple phone user? (8 marks)
What are the first and most recent mobile phone purchases? (3 marks)
i. For calls made in 2018 how many calls were made on the weekend? (5 marks) ii. For calls made in 2018 how many calls were made on each day of the weekend? (3 marks) MIS602_Assessment _2_Brief_Database Implementation_Module 4.2 Page 1 of 3
Provide a listing of the utilization of each tower and its location i.e. how busy each tower is based on the number of connections. Put the busiest tower at the top of the list (3 marks)
Did any users on the ‘Large’ plan exceed their monthly allowance during August 2018? (8 marks)
The company is upgrading all their 3G towers from to 5G. i. How many towers will be upgraded? (1 mark) ii. what SQL will be needed to update the database to reflect the upgrades? (2 marks)
i. List the full name, join date, resigned date of each staff member and name, join date and resigned date of their manager (3 marks) ii. What do you observe with the data? (2 marks)
How much revenue was generated in 2017 by each plan from call charges. Format the output as currency i.e. $123.45 (8 marks)
List the customers who made phone calls longer than 200 minutes (5 marks)
Which customers have more than one mobile phone? List the customer name, suburb and state. Order by the customer name (8 marks)
Are there any mobile phone plans that are currently unused? This can be obtained a number of ways. Demonstrate this by using the following two query types i. Nested subquery (3 marks) ii. Outer join (3 marks)
List the oldest and the youngest customers in the postcodes 3000 and 3102. Show the customer full name age and suburb details (3 marks)
i. Create a view that shows the popularity of each phone colour (1 mark) ii. Use this view in a query to determine the least popular phone colour (2 marks)
The billing team is getting returned mail because of bad customer addresses. This is causing a loss in revenue. i. Review the customer data and find at least 3 issues (3 marks) ii. Provide the SQL statements to correct the data problems (2 marks) Total 100 marks
The database used for this assignment is available in ‘MIS602_Assessment 2_Database SQL Files’ folder. Please download the files from this folder.
All statements and first 10 lines of output need to be neatly presented. Please save all your SQL statements and output into a single word document and submit it via the Assessment link in the main navigation menu. Referencing It is essential that you use appropriate APA style for citing and referencing research. Please see more information on referencing here http://library.laureate.net.au/research_skills/referencing Submission Instructions Submit Assessment 2 via the Assessment link in the main navigation menu in MIS602 Database Modelling and Database Design. The Learning Facilitator will provide feedback via the Grade Centre in the LMS portal. Feedback can be viewed in My Grades. Academic Integrity Declaration I declare that except where I have referenced, the work I am submitting for this assessment task is my own work. I have read and am aware of Torrens University Australia Academic Integrity Policy and Procedure viewable online at http://www.torrens.edu.au/policies-and-forms I am aware that I need to keep a copy of all submitted material and their drafts, and I will do so accordingly. MIS602_Assessment 2_Brief_Database Implementation_Module 4.2 Page 2 of 3 Assessment Rubric Assessment Criteria Fail (Unacceptable) 0-49% Pass (Functional) 50-64% Credit (Proficient) 65-74% Distinction (Advanced) 75 -84% High Distinction (Exceptional) 85-100% ? Correct and complete queries ? Display only required information ? Demonstration of additional AQL knowledge 100% Queries are incorrectly answered with marks only sufficient to attain a F(ail). Queries correctly answered with sufficient marks to attain a (P)ass Queries correctly answered with sufficient marks to attain a (C)redit. Display only required information. Queries correctly answered with sufficient marks to attain a (D)istinction. Display only required information Display additional SQL knowledge such as column formatting, table alias, additional calculations etc Queries correctly answered with sufficient marks to attain a (HD) High Distinction. Display only required information. Display additional SQL knowledge. Well formatted queries. Results returned in a meaningful order. MIS602_Assessment 2_Brief_Data Implementation Module 4.1 Page 3 of 3
Customer satisfaction- Customer satisfaction. We have an outstanding customer care team that is always ready and willing to listen to you, collect your instructions and make sure that your custom writing needs are satisfied
Writing services provided by experts- Looking for expert essay writers, thesis and dissertation writers, personal statement writers, or writers to provide any other kind of custom writing service?
Enjoy Please Note-You have come to the most reliable academic writing site that will sort all assignments that that you could be having. We write essays, research papers, term papers, research proposals. Design solutions applying relational database techniques to complex problems and communicate these solutions to all stakeholders.
