Relational Database Project IS 5500 – Data Analytics GROUP & INDIVIDUAL PROJECT Case: Decorative ConcreteDatabase Pages: 56-61 Due Date: On or before 8:30 PM on March 9th, 2017 Database Design– Group part Design your initial designs on paper. Each one individually works on the designs and gets together with the team members to create team designs. The first step is creation of a logical design for the database. In this step, you will identify entities/tables, their attributes, and relationships. While reading the case, identify entities/tables (hint: nouns), their attributes (hint: nouns) and relationships (hint: verbs). Also, look at the form, queries and report in the textbook and this handout to make sure that your tables include all the appropriate fields. Avoid calculated fields (ex: age, number of students, and duration of course) as actual fields in your tables. For each table, identify the primary key by underlining it. Determine the proper relationships (examples: 1:1, 1:M, M:N) among the tables and create your logical ER diagram. Use the Student Registration Database Logical Design handout given in the class as a model and design yours along the same lines. Once your group's logical design is complete, get it approved from your instructor. The second step in database design is physical design. In this step, you are factoring the specific technology (in our case RDBMS) that you are planning to use for your database implementation. Physical designs are the basis for actual implementation of the database similar to construction (or contractor) drawings of a building. Fortunately, in RDBMS, the physical designs are "almost" identical to the logical designs with few modifications. Relational technology uses foreign keys to implement relationships and can't directly implement M:N relationships. You will create transaction entities wherever M:N relationships are involved. Use your group's logical designs as basis to create the physical designs. Your physical design will have revised tables (with foreign keys), new tables (transaction entities), and a revised ER diagram. Determine the field characteristics such as type (short text, yes/no, currency etc.), length etc., which are appropriate and adequate to accommodate the data presented in the tables. Remember to use Short Text data type for fields that are not used in calculations (Ex: Customer ID). Apply input mask for fields such as phone numbers. Transaction entities will have composite keys or new primary keys. Include look up type field(s) for tables (especially for transaction tables). Check out the form in this handout to decide which field(s) is(are) look up type field(s). The final step in the physical design is populating the tables with actual data (all made up). Populating tables: Create at least 10 customers (make up all the needed info such as names, addresses etc.). The first customer must be John Doe. Your team members will later on replace the John Doe record (except the primary key) with their own names and other data (made up) while individually implementing the database in Access. Do not use names of your team/classmates, Indiana Tech staff or faculty in the database. Follow the instructions in Assignment 2A on page 58 for populating the other tables. All the installation requests are over two month period – Nov & Dec 2014. Each installation request must have only 1 job. Assume 2 customers place 1 job order each, 3 customers place at least 2 job orders, 2 customers to place 3 job orders, 1 customer to place 4 job orders and 2 customers have no Page 1 of 6You MUST do your project in MS Access 2013 job orders yet. Make sure at least 4 of these jobs have a square footage of between 1000 to 2500 square feet each. You will have at least 18 installation job orders. At least 50% of the customers will have a work performed at different locations (summer home, lake home, rental property etc., and these addresses DO NOT need to be stored in Customer table ) than the home address given in the survey (home address is stored in the Customer table in your database). The very first order must be by John Doe and John must have placed 3 jobs on three different dates. When scheduling or assigning jobs to owners (Candy and Carl), show start date and end date for each job. Each job takes more than one day so allocate between 2 to 3 days for jobs (fraction of a day is not allowed). Make sure that there are no conflicts with schedules of owners. You can always create more data than what is stated here. Use the Student Registration Database Physical Design handout uploaded in the BB as a model and design yours along the same lines. Please note that everything does not have to fit into one or two pages compactly like the handouts. Once the physical design is complete, get it approved from your instructor before proceeding to the next step – the actual implementation of database in Access. Use MS Excel to prepare your final designs. Microsoft Excel is very handy for creating ER diagrams and for importing the data into Access tables directly. See the video on BB to learn how to create an ER Diagram in Excel. Members in the group can collaborate easily by using OneDrive. Database Construction in Access – Individual part DO NOT start this part UNTIL the instructor has APPROVED your group's physical design. Everyone in your group will use the same data from the group's Excel file to do the implementation.  Create a blank database file in MS Access 2013. Name the file with your last name followed by first name. For ex: DoeJohn.accdb. Note that Access automatically adds the extension "accdb" to the end of the filename; you do not have to type accdb as an extension. Capitalize the first character of your last and first names as shown above. NO commas or spaces in the file name.  Create tables by importing the data from the Excel spreadsheet that your group has shared with you. You should NOT create your own data but use the group's data. Everyone should create his or her own database file from scratch. Using someone else's database file (even if it contains just tables) is considered as cheating. After importing data into Access tables, change the attributes of fields in the tables as per your group's physical design. Change the field type, width, etc., and designate the primary key or composite key fields. It is very important that you strictly adhere to the design specs stated in your group's physical design. Apply the input mask to fields such as telephone numbers. Include look up type fields for tables (especially transaction entity tables). It is important that you include the appropriate look up type field(s) at this time, before proceeding to relationship diagram in the next step. Finally, make sure to change the John Doe record with your information (change name, address and contact info, but leave Customer ID and other fields as is).  Create a relationship diagram (Database Tools  Relationships). You may notice that Access has already included the tables used for look type fields in the relationship window. Add the remaining tables to the window. Create relationships among the tables (as per your physical design ER diagram). For each relationship, check mark the Enforce referential Integrity box in Edit Relationship dialog box to avoid inconsistencies in the data entry later on. Every relationship in your relationship window will show 1 and ∞ to reflect 1:M relationship.  Now you are ready for the real stuff. I suggest that you launch the Microsoft Word program and create a blank document at this time. You will copy/paste the outputs (such as relationship diagram, queries etc.) from Access to Word document as you finish eachone. Page 2 of 6Remember tousethe same names for Form, Queries and Report as given inthe textbook or this handout. The instructions in the remaining pages of this handout supplement or replace the instructions given in the textbook. See the Grading Criteria for this project in the DBMS Projects folder on Blackboard. Assignment 2B: Form, Queries and Report Form: Customer Jobs Capture only the first customer information. Note: This should be your name, address, and contact information (except Customer ID, Number of Acres etc.) You do NOT have to capture the image with drop down like shown here. Create a form with a subform as shown above instead of the one given in Figure 2-3. Your field names and contents will vary. Name the form as Customer Jobs. Apply the Wisp theme to the form. Add the company logo (available on O: drive in Access Practice folder) to the form. Format the form title to Century Gothic (Detail) font type with 24 font size and bold face it. Note that "Customer ID" field is not shown in the subform (unlike textbook's example). Type is a look up type field that looks up type of concrete from another table (in mine it is Concrete Type table). Increase the row height in subform to accommodate comments that are longer than the column width. This will allow the text to be wrapped in the cell and display it in entirety. Change the properties of the subform so that NO navigation controls are displayed for it. Adjust the column widths in the subform so that all the fields (including look up type) along with their names and contents are completely visible in the form. Also, adjust the widths of fields in the main form to display their contents in full. Finally, use the Snipping Tool in Windows 7 or Print Screen to capture the form and paste it into MS Word report file. Small images are not acceptable (10% penalty). Page 3 of 6Queries: General guidelines Avoid duplicate tables in your queries. Duplicate tables create misleading reports. Make sure that all data in the columns are completely visible in the query output. Adjust the column widths to show the contents fully in the output and save the layout. You must copy/paste query output from Access to Word to reduce the number of printouts (10% penalty for individually printing query outputs). If your queries have multiple fields then in order to avoid text wrapping, change the page orientation (to landscape) and margins in the Word document. You may add annotations to explain the outputs. Query 1: Skip this query. Query 2 – 4: Do as suggested in the text. Query 2 – note that this is a parameter type query. Query 3 - Total Cost is a function of Square Footage and Price per Sq. Foot. Query 4 - sort the data by Start Date in ascending order. Do the query for December month. Query 5 (New): Create an update query called"Price Increase" to increase the Price for Square foot of all Concrete Types. When run, it should ask for the $ amount to be increased (Hint: parameter type). Create a copy of the original table and run the update on the copy table only. Please note that the copy table will not show up on the relationship diagram. For this query to work properly, you must "enable content" as discussed in the class. No need to print thisquery. Report 1: Customer Payments (In lieu of the textbook's) Create the grouping report shown in the next page instead of the one given in Figure 2-8. You will first create a query (name it "for report") and use it to base your report. The query must have a calculated field - Cost. Assume that jobs with more than 1000 square feet would receive 20% discount for the square feet that is over 1000 square feet. That is, if a job order consists of 2500 square feet, then you charge them regular rate for the first 1000 square feet and a discounted rate (20% off) for the remaining 1500 square feet (Hint: IIF function is needed in the expression for Cost in query. Note IIF is not a typo. The syntax for IIF() in Access is similar to IF() in Excel except that Access fields are included in square brackets [] ). Create the report using the Report Wizard. Make sure to randomly check if your expression (outputs in query) is working correctly using calculator or paper and pencil. Name the title of the report as Customer Payments. Apply the Wisp theme to the report. Add the company logo (available on Blackboard) to the report. Format the report title to Century Gothic (Detail) font type with 24 font size and bold face it. Wrap the column header titles (Hint: Control + Enter) as shown. Bold face the column titles - Last Name etc. Change the Can Grow property of Work Address so that the addresses are wrapped with in the cell in the report. The report should show two totals - Customer Total and Report Total. The Customer Total is a sum of all the jobs belonging to a customer. The Report Total will show up in Report Footer at the end of the report. Add the captions Customer Total and Report Total to improve readability. Make sure that the Cost column values along with all the subtotals are in Currency format with 2 decimal places. You may have to adjust the subtotal field widths to accommodate the formatting symbols and decimals. Apply the formats to Customer Total and Report Total as shown. The Report Total will be shown in a box (Format tab  Shape Outline, choose your choice of line thickness and line color). You may have to adjust the spacing and move down the controls a tad in design view of the Report Footer section so that the border around the Report Total does not interfere with Customer Total. Apply the Conditional Formatting to Customer Total control. Click on the Customer Total value control (NOT the caption) and apply Conditional Formatting. All the customers whose total is more than a certain amount (that is appropriate based on your data) are shown in bold, red font. Do not include symbols such as $ , (comma) and " " in the conditional formatting rule values (wrong entries: $10,000 or 10,000 or $10000 or "$10,000"). The sample report illustrates for >=10000 value. Note that your data will be different Page 4 of 6and hence the contents of the report. Make sure that all the data is completely visible in your report columns with no blank pages (adjust page orientation, column sizes, margins etc. if necessary to avoid printing blank pages). The order data shown in the report is grouped by Last Name and within each Customer the data are sorted in ascending order by First Name and Work Address fields. The following picture shows a partial listing of the report and your report will look at least like this. Print the report (NOT as using snipping tool, but File Print) and attach it with your paper report. Grouping order: Last Name - ascending Sorting order: First Name – ascending, Work Address - ascending Custom Navigation Pane (New) Create a custom navigation pane called Easy Navigation as shown on the side to access the queries, forms, and reports. Hide Unassigned Objects. Note that just hiding the objects in the default Navigation Pane is not treated as creating an Easy Navigation pane. No need to include a printout of the Navigation Pane in your report. Assignment 3: Making a Presentation - Skip this part Page 5 of 6Deliverables: 1. Database Designs (group responsibility - only one set for each group)  Be sure to include a cover page with your group info, names of all group members,course section and semester info (Ex: Group 2, John Doe, Jane Doe, etc., , Session Three 2016 - 2017).  Include the final Logical (ERD & attributes) and Physical (ERD, field characteristics of tables, and data) designs similar to the handouts printed copy. Handwritten final designs are not acceptable. Must be created inExcel. 2. Database Implementation (individual responsibility)  You should create your own database file from scratch by importing the data from the group's Excel file. Using someone else's database file (even if it contains "just" tables) is treated as cheating.  Use the same names for form, queries and report as given in the textbook/handout.  You need to include electronic copy outputs of relationship diagram, queries, form, and report as mentioned. o For both relationship diagram (RD) and form, you need to capture the images (using Snipping Tool) and insert them into a Word document. Before capturing, resize boxes of the tables in the RD large enough (no scroll bars) to show all the fields in them. For the RD page in Word, change the page orientation to Landscape. Crop and enlarge the images to make the images easily readable (10% penalty for smallpics). o Copy/paste the queries from Access into Word document as shown in class. o Submit the report toInstructor.  The final report will have o A cover page with your name, group number, class time and semester info (Ex: JohnDoe, Group 2, Indiana Tech, Session Three). o Submit your final report.  Present your Access file using the DBMS inclass. Page 6 of 6