Busi2400-F18 - Busi2400 - Access Assignment PDF

Preview

Summary

Busi2400 - Access Assignment...

Description

???Access Assignment BUSI 2400: Fall 2018 by Unknown Author is licensed under

 Note: This is an individual assignment Due Dates

Part One Part Two

Section A Oct 12th @ 8:00 am Nov 2nd @ 8:00 am

Section B Oct 16th @ 2:00 pm Oct 30th @ 2:00 pm

Section C Oct 17th @ 8:00 am Oct 31 st @ 8:00 am

Part ll will be released to all section Oct 17th, 8am Access TA Office Hrs Date Oct 5th Oct 9th Oct 11th

Time 11:30 to 1:30 1:30 to 4:00 5:30 to 7:30

Room DT910B DT910B DT910B

Introduction The purpose of this assignment is to give you experience with implementing a basic data model into a relational database (using Microsoft Access as the database), performing basic data entry in Access, and understanding the structure and functionality of Structured Query Language (SQL) statements. The assignment has two parts. Part 1 involves the implementation of the data model (creating and configuring a database from an Entity-Relationship Diagram - or ERD for short) and entering data into the database. Part 2 involves understanding and executing SQL queries. Once the due date for Part 1 is reached, you will be provided with a Part 1 solution. You will then run supplied queries in order to understand the data and the relationships between them. You will then be required to answer questions in cuLearn regarding queries run on the provided solution database. Part 1 and part 2 are each valued at 5 marks. The total represents 10% of your term grade.

© 2016-2018 Robert Riordan

1

Data Model beans4all (b4a) has decided to implement an Access database to keep track of Mentors and assignments. The following ERD has been drafted by an intern. Please read carefully below:

Some context Let’s talk high level first. We have seven entities here. Here’s what they represent: 1) Mentors is all about the people who work at b4a. Thus we have some attributes (characteristics) of those who work for us in this entity. You can see for yourself what they are. 2) Skill contains data about the skills we consider important in our work. 3) Client contains data on (guess what) our clients. These are the folks who keep the lights on and pay for the Seasonal Office Bash (or SOB for short). 4) Crew is all about the aggregations of Mentors who form work teams which work on assignments for our clients. 5) Assignment records the data regarding the agreements between b4a and its clients with regard to work that our clients ask us to do. These assignments are how we get paid. 6) Segments are slices of work that are performed by Crews on an assignment. One crew might not have all the skills required to complete an assignment. We might need several crews to work on an assignment in order to complete it. Thus, Segment is a bridge table (same as an Associative Entity – see below) between Crew and Assignment that allows us to record the details of a work Segment performed by a crew or several crews or even the same crew at different times. You might think of this as analogous to a Scrum Sprint (see Chapter 4 in the textbook). 7) AssignSkill is an Associative Entity linking Mentors and Skill. AssignSkill allows us to easily manage and track the data at the intersection of the two entities it joins. The relationship between Mentors and Skill is a classic MANY: MANY relationship. In relational databases, M:M relationships are implemented as two, ONE: MANY relationships. This has the handy side effect of allowing us to record attributes about the intersection, such as whether the Mentors with the Skill has an Accreditation for it, and if so, when that accreditation was earned. This is important data when selling the skills of our mentors to our clients. It also drives billable-hour charge rates against assignments.

© 2016-2018 Robert Riordan

2

The proposed ERD above has issues. First, it’s incomplete in some important ways. There is a proposed Associative Entity between Mentors and Skill (AssignSkill). This needs some work on the Primary Key(s), as you see from the ERD above. Secondly, there are also two missing fields in the Segment entity. Finally, there’s some work to do on one of the fields in the Assignment entity. Now the relationships Each analyst working for b4a is a Mentor, and thus we have the Mentor entity (which, when implemented in Access, becomes a table). The Mentor entity has MentorID as a Primary Key (PK), which is of type AutoNumber in Access. There are several other attributes included in the Mentor entity as well. Each Mentor is a member of one and only one Crew. It is these Crews which work on the Assignments that b4a has accepted with its Clients. Each Assignment must have at least one Crew working on it (but there can be many crews working on an assignment simultaneously) this is represented in the Segment entity. Next consider the Assignment-Client relationship. A client is required for an assignment, but not all clients necessarily have assignments. They might just be considering asking us to work for them, but we have their contact info in our database anyway. Thus, we have an optional relationship between these two entities. Finally, we consider the relationship between Mentor and Skill. You will thus note that in the absence of the associative entity AssignSkill, we would have a classic MANY: MANY relationship between Mentor and Skill, and this type or relationship is implemented in a relational database by creating two, ONE:MANY relationships. As such, we have the AssignSkill entity between the two. Skills are things that Mentors are particularly good at and which management would use in order to assign Mentors to Crews to work on specific assignments and to decide on billing rates. Mentors must possess at least one skill (but can have many) and any skill can be possessed by one to many mentors.

PART 1: Creating your database (5%) Task 1 (4 marks): Implement this Data Model in Access 2016 Take the initial ERD above and implement it as a relational schema using MS Access 2016. Use the material you have been taught in class, the lab videos and the lab slides as well as the textbook (Chapter 4) to accomplish this. Note that the ERD above specifies entities, attributes, data types, and some primary and foreign keys. When you implement your tables, ensure that you create appropriate keys to guarantee that the proper relationship is created and thus that referential integrity will be enforced where required.

© 2016-2018 Robert Riordan

3

Task 2 (1 mark): Create Sample Data Populate your database with sample data, satisfying the following requirements for each entity: CREW: b4a has 16 crews operating at any one time. Create the Crew table and enter the following Crews. You can use Autonumber for the PK field: 1. Acme 2. Apex 3. Apogee 4. Elite 5. Foremost 6. Optimal 7. Paramount 8. Peak 9. Peerless 10. Pinnacle 11. Summit 12. Superior 13. Supreme 14. Ultimate 15. Utmost 16. Zenith CLIENT: Enter at least 10 unique clients. Example clients might be: 1. Department of Justice, Ottawa, Government. 2. Shopify, Ottawa, Information Technology. Ensure that at least three are located in Ottawa. MENTOR: Enter the names of all your 2400 Crew members. Then make up 20 more people. Make certain there is at least one member in each of the 16 crews. SKILL: Create at least 15 skills that Mentors might be expected to possess. Example are: Database, Programming, Web Design, Leadership, Finance, Cryptocurrency, Artificial Intelligence, … ASSIGNMENT: Create at least 15 assignments. Five should have starting dates sometime in 2017, while at least 5 should have starting dates later than January 1, 2018 but prior to the end of the calendar year. The remainder should have dates in between September 1, 2018 and December 31, 2018. Make certain to provide a broad description of the assignment in the appropriate field. SEGMENT: Make sure that 5 assignments are currently being worked on (StartDate today) and that at least seven Crews are assigned to the five assignments. ASSIGNSKILL: Create at least 10 Mentors+Skill mashups. Accredited means that the mentors holding the skill has some kind of diploma, degree or certification (accreditation) in the skill (for example a Scrum Master might hold a certificate in Scrum techniques from ABC ScrumCo). Make sure that at least 5 of the Mentors+Skill pairs are accredited.

© 2016-2018 Robert Riordan

4

Part 1 Deliverable Your part 1 deliverable includes an Access database file that includes the specifications detailed above.  Upload your file through cuLearn o Under Assignments >> Access Individual Assignment o Follow the instruction to upload your file  Naming convention: Lastname_Firstname_section_CUID.accdb where: Section: A, B or C CUID is your Carleton University student identification number Example: Pilcher_Al_A_123456789.accdb  Assuming I was in section A Deductions  Zero is file isn’t uploaded by deadline  -2.5% not following naming convention NOTE: Part one is only worth 5%

Final Comments As a way to confirm the appropriateness of your design and in preparation for doing Part 2, use the following sample requests to create queries in Access that will provide the appropriate reports for b4a management: 1. Provide the First Name, Last Name and Hire Date of all mentors. 2. List clients by name for those in Ottawa. 3. List all data regarding assignments which are currently being worked on and which will end on or before December 31, 2018. 4. List all currently working crews and the Dollar value of the assignments upon which they are currently working. 5. List all mentors, sorted from highest to lowest hourly rate. 6. List all crews which are not currently working on an assignment (use the Find Unmatched Query Wizard). 7. List skills that are not currently possessed by any mentor’s assignment (use the Find Unmatched Query Wizard). 8. What is the total value of all assignments for Ottawa clients? (You will need to research Aggregate Functions and Group By) 9. What is the total value of this year’s (2018) assignments? 10. List all skills which are accredited, along with the mentors holding the accreditation and the year in which the accreditation was received. Practice with these queries and make sure you can make them work with your dataset. The queries above are not graded as part of the assignment. They are for you to practice on. You should then examine the SQL emanating from them so that you will understand the structure and function of SQL in the context of this database. Click SQL View and examine and understand the SQL that is produced by the Query by Design grid and/or the Query Wizard. Be comfortable with the logic and the syntax.

© 2016-2018 Robert Riordan

5

PART 2: SQL Queries (5 marks) Once the due date for the Part 1 submission has passed, you will be supplied with a working solution complete with data upon which to work (your solution might well be perfect; we’re just supplying an ideal solution so that everyone can work from the same database). The working solution will be available on cuLearn for download. At this point, a cuLearn Quiz will be available for a limited period during which you will be asked to answer multiple choice questions relating to specific SQL queries from the database. This is from where you will receive the 5 marks for Part 2 of the assignment. Here is a sample question: SELECT Crew.CrewName, Assignment.StartDate, Segment.EstimatedHours FROM Crew INNER JOIN (Assignment INNER JOIN Segment ON Assignment.AssignmentID = Segment.AssignmentID) ON Crew.CrewID = Segment.CrewID WHERE (((Crew.CrewID)=3) AND ((Assignment.StartDate)>=#9/1/2017#)); The query above will return: a. An empty results table if there are fewer than 3 assignments in the assignments table b. A syntax error c. A set of records showing CrewName, StartDate and EstimatedHours for assignments beginning on or after September 1, 2017 for Crew #3 d. A set of records showing EstimatedHours, AssignmentID and CrewID for the first 3 assignments signed after September 1, 2017 e. A set of records showing all CrewName, StartDate and EstimatedHours for all assignments signed before September 1, 2017 The answer is c. There will be 5 such queries presented individually to you in the quiz (chosen randomly from a large pool of questions). Each question will show 5 possible answers. You will have 15 minutes to complete the quiz. Once begun, you must finish the quiz in one, 15-minute period. Tips & Tricks A) When specifying a data type as Long Integer, first choose Number, then, in Design Mode, set the Field Size property to Long Integer. B) Make sure to save and close all tables before creating Relationships using the Database Tools menu, Relationships item. C) In some versions of Access it is necessary to double click the field you wish to choose when making a relationship between two tables. In some versions, you can just drag the field onto the other table. Always choose the ONE side of the relationship as the Left table and the MANY side as the Right table when creating relationships. If dragging and dropping, drag from the ONE side to the MANY side. D) Tables must contain data before Relationships can be created. E) Always select “Enforce Referential Integrity” when creating a relationship, except where an Optional relationship is specified. F) Familiarise yourself with GROUP BY, ORDER BY, AVG(), SUM(), Now(), Is Null and the various JOINS that Access generates with its various Query Wizards.

© 2016-2018 Robert Riordan

6...