I attached in file
Allow enough time to complete this assignment, you are responsible for starting this assignment early enough to receive help in case you encounter technical issues. Bear in mind that you will have a hard time getting support if you decide to start the assignment one day before it is due. Instructions Using the business case included in this assignment, complete the following requirements: 1. Draw the ERD of the database that could be used to support the operations of the business described in the case (6 marks). The ERD should identify: · Primary keys and Foreign keys; · Entities and Attributes; · Relationships and Cardinalities (e.g. one-to-one, one-to-many). Next to the ERD in your submission, list the assumptions made when designing the ERD. If an element of the business case does not provide the information necessary to know for certain whether one design choice should be made over another, the choice you make will be accepted if it correctly reflects your assumptions. 2. Draw a flowchart diagram (2 marks). Draw a Flowchart diagram depicting processes in the case. Your diagrams must depict the following: · Main actions / processes · Main decisions · Actors (departments) with dedicated swimlanes 3. Create the data dictionary for your database (2 marks). To do this, you can use the template given with this assignment (datadictionaryTemplate.xls) 4. Implement the ERD Microsoft Access 2010/2013/2016 or LibreOffice (5 marks) Microsoft Access is installed in the SCBTM lab located in MB 12.314. LibreOffice can be downloaded from the link below (Windows or Mac): https://www.libreoffice.org/download/download/ Using the ERD and data dictionary that you created previously, complete the following steps: A. Implement the ERD and data dictionary in Microsoft Access 2010/2013/2016 or LibreOffice. The Microsoft Access Database should have all the tables, attributes, and relationships defined in your ERD and data dictionary. You should make sure that all the primary keys and foreign keys are defined (2 marks). The videos below can help you to achieve this for Microsoft Access: · Table creation: http://youtu.be/hd7XpwlBOK0 · Relationships creation: http://www.youtube.com/watch?v=xjhuhZI20Ho · The PowerPoint for the video tutorials above is named “AccessTutorial.pptx” and included with this assignment. The videos below can help you to achieve this for LibreOffice: · Case study: https://www.youtube.com/watch?v=au0KS-vOFPM&feature=youtu.be · Table and relationships creation: https://youtu.be/5GxGbc0Fljk B. Create 5 records in each table of your Microsoft Access Database. Make sure to create the records for the parent tables first (the ones with the one side), otherwise you might run into referential integrity issues (1 marks). The video below can help to achieve this for Microsoft Access: · http://www.youtube.com/watch?v=frsmRm7z7F0&feature=youtu.be The video below can help to achieve this for LibreOffice: · Records creation: https://youtu.be/5GxGbc0Fljk C. Create 2 queries in Microsoft Access that a manager could answer using this database. Your queries must each use of at least two tables (1 marks). You can watch the two tutorials below that show how to create queries in Microsoft Access. · Query example 1: http://youtu.be/1rdjkLxteGg · Query example 2: http://www.youtube.com/watch?v=iRTTujmQkYw The video below can help to achieve this for LibreOffice: · Query creation: D. Create 2 reports by using the queries designed in step 3 (1 marks). You can watch the tutorial below for Microsoft Access that shows how to create reports based on the queries of the previous step. · http://youtu.be/qx2yJoTsagU The video below can help to achieve this for LibreOffice: · Report creation: https://youtu.be/CbAHnqcsTwc Business Case:Car Rental Inc. · The firm has a number of rental agencies in several provinces. Each agency is identified by a Agency_Number and its location. · Each agency is assigned one or more employees. Attributes of employee include Employee_ID and Employee_Name. An employee must be assigned to only one rental office. · For each agency, there is always one employee assigned to manage that office. An employee may manage only agency to which s/he is assigned. · The agencies rent several classes of vehicles: compact, midsize, full size, sport utility. Each vehicle is managed by one agency. Customers might rent vehicles from the different agencies. · Vehicles are to be described by data such as: make, model, year of production, engine size, fuel type, number of passengers, registration number, purchase price, purchase date, rent price and insurance details. · There are two types of customers: privileged and casual. For the privileged category of customers special credit card facilities are provided. These customers may also book in advance (reservation) a particular car. Information stored for each booking (reservation) includes customer, car, estimated amount, date of rental and date on which the vehicle is to be returned. If a vehicle is available, the customer's details are recorded (if not stored already) and a new booking is made.These reservations can be made for any period of time up to one month. · Bookings can be cancelled if the agency is informed at least 24 hrs before the booking date. · Casual customers cannot book in advance and must pay a deposit for an estimated time of rental at the time they rent the vehicle, unless they wish to pay by credit card. All major credit cards accepted. · For each rental (for either privileged or casual), the agency keeps track of the date it was rented, the date the vehicle is returned, vehicle, customer and the rental total charged amount. · Personal details (such as name, address, telephone number, driving license, number) about each customer are kept in the database. · A customer (either privileged or casual) cannot rent a car for longer than four weeks. . · Vehicles need to be maintained and repaired and the database is required to keep track of this. All major repairs and maintenance are done by subcontractors (i.e. franchised garages), with whom the rental company has long-term agreements. Therefore the data about garages to be kept in the database includes garage names, addresses, range of services and the like. Some garages require payments immediately after a repair has been made; with others the rental company has made arrangements for credit facilities. HINT: Many-to-many relationships must be split into two one-to-many relationships. Submission You must hand in a single WORD file that includes the following: 1. ERD and Flow chart diagrams embedded (e.g. Visio Diagrams) Note: ERD diagrams in native formats will not be accepted 2. Data dictionary created in an Excel file embedded You must submit your Microsoft Access or LibreOffice Database file containing the tables, relationships, two queries and two reports in one Zip folder onto eConcordia. Note that Databases in formats other that MS Access version 2010/2013/2016 or LibreOffice will not be accepted. Submit the Zip document using the following naming format: Lastname1StudentNumer1Lastname2StudentNumber2.zip Submit the word document using the following naming format: Lastname1Firstname1Lastname2Firstname2_Assignment2.docx Important Note a. The first page of your word document must clearly identify last name, first name, and student ID b. Handwritten assignments will not be accepted Resources Drawing an ERD: There are two recommended tools for this assignment: A web based tool is LucidChart (recommended for Mac users but supported also for Windows. This is a free online diagramming tool found here: A video tutorial that shows you how to use this tool for ERDs can be found Here. Another web based tool is Visual Paradigm (recommended for Mac users but supported also for Windows. This is a free online diagramming tool found here: Examples that show how to use this tool for ERDs can be found Here. Drawing a Flow Chart: If you need to learn how to draw Flowchart diagrams, you can follow the tutorial below for the exercise in figure 6.1 in chapter 6: (Video uses Lucid Chart):https://www.youtube.com/watch?v=svxGMvNu3Xg&feature=youtu.be Microsoft Access and LibreOffice access Microsoft Access is installed in the SCBTM lab located in MB 12.314. LibreOffice can be downloaded from the link below (Windows or Mac): https://www.libreoffice.org/download/download/ The videos provided were created in MS Access 2010, there is little difference between 2010 and 2013/2016. However, you can also watch this https://www.youtube.com/watch?v=Fjqe36SDvOk&feature=youtu.be video for a MS Access 2013/2016 version of the creation of the database, records, queries and reports)