Database Design and Analysis in Access | Full Project with queries and reports

Buy now

Database Design

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 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 each one.

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).

Queries: 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 this query.

Report 1: Customer Payments

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 and 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.

Buy now

CONTACT DETAILS

For any other questions or other tasks please feel free to contact me
via email: mhassnainjamil@gmail.com
via WhatsApp: +92-324-7042178
via skype: hassnainjamil1