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.
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.
Comments
Post a Comment