LOW BUDGET RENTALS DATABASE ASSIGNMENT
Use your Low Budget Rental data model to create a structure for an Access database, including the tables, their fields, field properties, and relationships between tables as well as forms, queries, and reports. Look at the end of this document for some general tips on creating the database structure in Access and entering data into an Access database. There is a grading rubric in this assignment folder. Submit your file at the link in this folder in the Assignments area of Blackboard by the due date listed on the Course Schedule.
If there were problems with your data model, use the solution data model posted in the Data Model Assignment area of Blackboard to make appropriate changes to your model. You may have more attributes/fields than the solution model, and names of entities/tables and attributes/fields may be a bit different from the solution as long as you maintain the spirit of the scenario. In your database table design, choose field names that also make good column headings. Make sure all fields have reasonable values for field type, size, and required status in the database structure (see table design view).
Use the data in the data modeling assignment screen captures and the spreadsheet attached to the data model assignment to populate your tables (fill them in with sample data). Of course, as in the real world, the data may have errors, be incomplete, and may not be structured properly. Add any missing data, minimize duplication of data, and correct data that is obviously not accurate. Add enough data for you to test any queries and reports you create. Add yourself as a customer and add at least tworentals for you. You do not have to include your actual address or phone number data and definitely do not use your real credit card number. Make up data for those fields.
Make certain that the proper relationships exist between the tables. To do that, first make sure that all foreign keys have the same property settings as their respective primary key fields in the other table (data type, length, and set to required). It is helpful mentally to make them the same name, but that is not technically required.
Second, go to Database Tools in the menu ribbon and then click on Relationships. Check to see that they are correct. If you don't see any tables, right-mouse click and add your tables (Show Tables option). If your relationships were not created automatically, there may be problems in your table designs, particularly with primary/foreign key fields. Again, a pair of keys (primary/foreign) must have exactly the same data type and length. You will have to close the Relationships tab, open the affected tables in design view, fix the problems, save the changes, close the tables, and reopen the Relationships tab.
Back in the Relationship view, if the relationship lines and symbols for relationship properties are not showing (1 and infinity sign), you will have to create the relationships manually. Click on the field that connects the two tables in the table where it is a primary key. Drag the cursor to the foreign key in the other table. A pop-up window will appear (Edit Relationship dialog box). Confirm that the right fields in the right tables are being connected. Then make sure that the relationship maintains maximum data integrity by clicking all three boxes at the bottom of the dialog box. Then click on Ok.
If you get warnings during the processes above, then one or both of two things has happened. (1) You did not follow the directions above about the properties of the field common to both tables (field type, size, required status all the same). (2) You have data in your tables that does not fit those rules. You may have left a foreign key field blank or have a value in the foreign key that does not match any of the values in the primary key field in the other table. For example, you may have a Customer in the Delivery table that has no corresponding customer in the Customer table. Or you may have an Employee ID value in the Delivery table that has no corresponding Employee ID in the Employee table.
See Additional tips below on using a data model to create a database. In the second Tegrity video on data modeling, I briefly cover translating a data model into an Access database. I can make a more detailed video on this aspect of the assignment on request.
Additional Database Requirements
1. Add data for yourself as a customer with at least two rentals.
2. Create a data entry form for each table.
3. Create at least two queries, one focused on customers and one focused on rentals. Use the information in the Data Modeling Assignment scenario to review what the Low Budget Rentals manager needs. Use your general business knowledge to design useful queries.
4. Create two reports that the manager needs. You may use a query as the basis for a report only if it draws from more than one table.
5. For a bit of extra credit, add one or two additional reports or queriesthat management might find useful.
6. Save your database with a file name that follows the conventions for this course (example: TroboyKimLBR.accdb). Submit your file at the link in the Database folder in the Assignments area of Blackboard by the due date listed on the Course Schedule.
WARNING:Be sure to close Access before trying to upload your file to Blackboard. Otherwise, it might not upload properly or you might accidentally upload the scratch pad file for Access rather than your actual database file. (The scratch pad file has a slightly different file name extension: TroboyKimLBR.laccdb). I see a few of these submitted every semester, which is unfortunate because this file has nothing for me to grade in the way of partial credit.
Additional Tips: A Process for Creating a Database from a Data Model
1. Create a table for each entity.
2. Use the Design view to add all the needed fields. Remember to
a. Select the right data type.
b. In the general field properties in the lower portion of the Design view screen, set the field length appropriately (for example, the default length for text is 255, which is too large). For numeric fields, selecting the specific type of number field will set the size automatically. Examples include integer, long integer, single precision, double precision, currency, and date.
c. Set the Required property appropriately.
d. Designate one field as the primary key in most tables. Sometimes it is necessary to designate two or more fields combined as the primary key to make sure there are no duplicate values (intersection tables are likely to fall into this category). These fields are called a composite primary key.
3. From the Datasheet view, enter data for each table. Use the figure in the Data Model assignment scenario and the spreadsheet attached to that assignment as sample data. You can also modify the data in the spreadsheet to match your design and import it, appending it to your existing table.
4. Create relationships between tables, as appropriate. Remember, this only works if you have at least one field they both have in common, a primary key in one table and a foreign key in the other, and these two fields have the same data type and length. (I am repeating this because students frequently make mistakes in this area.)
a. Use the Relationships icon from either the Design tab or the Database Tools tab.
b. Add each table from the Show Table box.
c. Drag the Primary key from its table to the table in which it will be a foreign key.
d. When the Edit Relationship dialog box comes up, click all three of the relationship integrity checkboxes. You can also right-click on the relationship line to open the Edit Relationship dialog box to make sure all three checkboxes are selected.
5. Use the Wizards in the Menu Ribbon to create Forms, Queries, and Reports. Remember that the Wizards allow you to pull from two or more tables as long as those tables have relationships set up properly. There is a drop down box on the first screen in the wizard that lets you choose a second table to include.