Microsoft Word - L1writeup partI.doc



Database Laboratory 1:Creating a Relational Database Using MS Access and Performing Queries Using Query-by-ExampleThe first objective of this activity is for you to create a relational database using Microsoft Access. The resulting database will be used in several future laboratory assignments. The second objective is to learn how to query the database using the Access query-by-example (QBE) feature. What you should turn in:? Place your final database file in the IN folder for CSC-105. This file should include all of the queries from Part II, saved in the same order as the query assignments.PART I: Creating the databaseOverviewIn brief, these are the major steps to be completed in this part of the lab:i.Create the empty database.ii. Define each table, with the associated fields (attributes) for each table. The definitions are to include primary keys, validation rules (also called constraints), and other details.iii. Define the relationships between the tables. This involves specifying foreign keys (which will be discussed in detail in class soon.)iv. Populate the tables with appropriate data.Create the Grades database1.First, create a folder called CSC105 on your USB drive, and create a folder called Lab1 inside the CSC105 folder. (You may also use your G: 2.Start MS Access. Click the Blank Database icon, under the New Blank Database heading.3.The Blank Database panel will be displayed. Navigate to your CSC105\Lab1 folder as the location for saving the new database.4.Name your database by typing gradeYourInitials into the File Name box at the bottom of the window. For example, for John Doe the name of the database would be gradeJD.accdb. If you omit the accdb extension, it will be supplied automatically when you click the Create button.5.Create the empty database on your disk by clicking the Create button.6.This will cause the main database window to be displayed. In the shutter bar on the left, choose “All Tables” from the pull-down menu if it’s not already selected. A generic table “Table1” will be displayed. An empty datasheet view tab will be displayed in the right side panel.Define each table and the associated fields1.Define the Student table first.There are many different approaches that could be used to create this table. You should feel free to deviate from the technique that will be described, if some other approach seems more obvious toyou. Also be aware that most mistakes, even design errors, can be corrected easily later. So relax(but not too much). ?a.Click on the View button in the ribbon and select Design View. When prompted, name the table “Student”. The Field Definition window will be displayed in the main panel, as shown below. For any given table that you want to define you will enter information into this box.When you begin to type the name of a field, an empty Field Properties table will replace a portion of the gray area in the bottom half of the panel. Every field will have its own Field Properties table. This is where validation rules and other properties of each field will be specified.b.Type the name of the first field, SSN. (Overwrite the default “ID” field that has been created for you.) Choose “Text” for the Data Type and enter a brief description. Make SSN the primary key for the table by clicking in the gray area at the left edge of the window, which should cause the entire row to become highlighted, and clicking the Set Primary Key button,.c.Next, define an input mask. This is used to restrict the format of certain kinds of inputs. First, set the Field Size for SSN to 11, because of the extra characters needed in the input mask.d.If you already know the details of the input mask, you can type it directly into the Input Mask row of the Field Properties window. A less error prone technique is to use a wizard to create the input mask. Whenever there is a wizard available for the currently active field, a button containing three dots, , will be displayed at the right edge of the field.Use the wizard to create the input mask for SSN. It’s fairly self-explanatory. Use the underscore (“_”) as a placeholder character, and select the option to store the data with the symbols in the mask. (This will simplify the work of creating forms and reports later.) When you are finished, the mask that you create should look like the one in the picture on the next page.e.Be sure to save the table regularly. Givef.The record type (specification) for the Student table is given on page 6 of the lab. Complete the table using these specifications. Some notes and hints:?Specifying sizes for the text fields should be straightforward.?“Long Integer” is the default type for number fields. It would be considered better design to choose “Integer” or even “Byte” when you know you are going to store smaller numbers, but if you make this change it is critical to remain consistent with the types. As we’ll soon see, certain values in one table are used to link to records in another table, and this won’t work if the types don’t match exactly.?Use the input mask wizard to specify masks for Zip, Phone and Birthdate.?Type the Gender validation rule into the “Validation Rule” box as shown in the record type. This will prevent any value other than “M” or “F” to be entered. You should also add a brief error message in the “Validation Text” box, something like “Value must be M or F”. Likewise with the Class validation rule. (Note that a wizard is also available for validation rules, but it’s often easier to simply type them in if they’re simple.)?All of the fields except for Phone and Major should be required to have a value. This is done simply by setting the value of the “Required” box to “Yes”.2.Define the Course, Grades and Sections tablesa.The record type for the Course table is given on page 6. Using your knowledge of how to set up an Access table including data types, sizes, masks, validation rules, etc., construct this table and save it.b.Use your judgment in determining whether or not a particular field must have a “Required”value or not.c.Repeat the table definition process for Grades and Sections, using the record types on page 7. d. Note that in order to identify two fields as a combined primary key (necessary in the Gradestable), you must make sure both rows are highlighted before clicking the Primary Keybutton.Define the relationships between the tablesRelationships between the tables are defined using the Relationships window. Your goal is to reproduce the relations shown below.This process will also define the foreign keys specified in the record types. (I’ll be explaining this in much more detail during lecture. In brief, a foreign key is a field in one table that explicitly links the data in that table to the data in another table. “Cournum” in the Sections table, for example, is a foreign key linking to the “Cournum” field in the Course table.)1.Click the relationships button, in the Database Tools ribbon. This should cause the empty“Relationships” panel to be displayed as a new tab, along with a “Show Table” window.2.Add windows for all the tables you have defined to the Relationships window by repeatedly clicking the Add button in the Show Table window. The order for adding the tables is not important because you can rearrange them by dragging any table diagram by its title to a new location. Do this to create the sequence Student, Grades, Sections, and Course from left to right.3.To create a relationship, use the mouse to drag a field from one table to another. For instance, drag the SSN field in Student to the SSN field in Grade. (NOTE: The dragging must always be done from the 1-side to the many-side of a relationship.) Then the following is displayed.Make sure that only the “Enforce Referential Integrity” box has been checked (unlike the image given above), to cause automatic testing for referential integrity, but no cascading of updates and deletes. (All of this to be explained later.)When the Create button is clicked, the relationship with its cardinality ratio is shown in theRelationships window.4.Repeat the above process until all the relationships have been defined.Entering data into the databaseOnce the tables have been created to meet the design specification, the database is ready to be populated with data. There are different ways of accomplishing this. Data can be entered manually; it can be imported, or it can be entered as the result of the execution of some database application. We’ll consider only the first two approaches in this lab, saving the third for later in the term.1.Manual entry involves simply typing data into the tables. Click on the tab for the Student table if it is still there. If it isn’t, bring up the list of tables in the shutter bar on the left and double-click the name of the Student table. An empty table in datasheet view will be displayed. The column headings will be the names of the fields that you defined earlier.2.Type in a single row of data. You can make it up, or enter your own information. Note how the SSN, Phone and Zip fields show you the expected format for the data. This is what the input mask does.3.Try entering a value other than 1, 2, 3 or 4 for the Class field. Try entering something other than “M” or “F” in the Gender field. What you see is the result of the Validation Rule and the Validation Text that you provided earlier.4.Try leaving blank one of the fields for which a value is required.5.Try entering the same SSN for two different rows.6.If you find that you’ve made a mistake in your table design or definition (during data entry or any other time), you can return to the “design view” in two ways. With the Datasheet View (the table) displayed, you can execute View > Design View to change the view. Or you can start from the shutter bar, right-click the name of the table, and select “Design View”.7.That’s enough practice with data entry. Delete any rows you’ve created in the Student table and close the table. (Click the X to the right of the tab.) Notice that you’re not asked to save your changes. This is because changes made to your Access database occur in real time. That is,they’re written directly to the database on your hard disk. Unlike Word files, if the power goes out while you’re working, all the data is saved.8.On the class web site are four files containing the data for your four tables. Copy these to your own Lab1 folder.9.With all of the table tabs closed, go to the External Data ribbon and click Text File in the Import panel. Browse for the files in this order: Student, Course, Sections, and Grades. This will avoid the potential problem of entering data that should reference a field in another table that does not yet contain data – a problem called a violation of referential integrity which we’ll study again later. For each file, select “Append a copy of the records to the table:” and select the appropriate table. Remember to import the files in the correct order.10. Open each table to make sure the data was imported successfully. In the Student table, click once on the plus sign (“+”) next to one of the student records. How do you interpret the result? Ask me if you don’t know.11. Move on to Part II of the lab.Details of the Table DefinitionsStudent Table InformationThe information in the following table assumes that the input masks are to be saved as part of the data in the database.Primary Key = SSN Foreign Keys = noneName of FieldCaptionData TypeSizeInput MaskValidation RuleSSNSSNText11000\-00\-0000;0;_FirstFirstText12LastLastText12StreetStreetText24CityCityText12StateStateText2ZipZipText1000000\-9999;0;_PhonePhoneText14!\(999”) ”000\-0000;0;_MajorMajorText4ClassClassNumber (LongInt)N/A=1 Or 2 Or 3 OrBirthdateBirthdateDate/TimeN/A99/99/0000;0;_AidAidYes/NoN/AGenderGenderText1=“M” Or “F”HrsAttemptedHrsAttemptedNumber (LongInt)N/AHrsEarnedHrsEarnedNumber (LongInt)N/AQualityPointsQuality PointsNumber (LongInt)N/A4NOTE: A value for the size cannot be entered for the non-Text data types.2.Define the Remaining Tables. Repeat the process you just completed (creating and describing the Student table) for the Course, Sections, and Grades tables in that order?definition information for each of the tables is listed below in the appropriate order.Course TablePrimary Key = CournumForeign Keys = noneName of Field CaptionData TypeSizeInput MaskValidation RuleCournumCourse NumberText6CournameCourse NameText30CredithrCredit HourNumber (LongInt) N/A= 2 or 4DeptDeptText4The Sections table has a foreign key, our first example of a foreign key. For now, you will enter the name of the field, cournum, into the table and ignore the fact that it is a foreign key. Later you will use the Relationships window and the field names of foreign keys to describe how the tables are related.Sections TablePrimary Key = SecidForeign Keys = Cournum (links to Cournum in Course table)Name of FieldCaptionData TypeSizeInput MaskValidation RuleSecidSection IdNumber (LongInt)N/ABetween 80 and 100CournumCourse NumberText6SemesterSemesterText6=“Fall” or “Spring” or “MayX”YearNumberNumber (LongInt)N/ABetween 2002 and 2011InstrInstructorText10Grades TableImportant Note: You must have both SocialSecurityNumber and Secid highlighted when you click the Key button to form the primary key for the table.Primary Key = SSN and Secid combinedForeign Keys = SSN (links to SSN in Student table) Secid (links to Secid in Sections table)Name of FieldCaptionData TypeSizeInput MaskValidation RuleSSNSSNText11000\-00\-0000;0;_SecidSection IdNumber (LongInt)N/ABetween 80 and 100GradeGradeText1=“A” Or “B”Or ”C” Or “D” Or “F” ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download