CHAPTER 2 RELATIONAL DATABASES AND QUERIES Akhila Kondai

CHAPTER 2 – RELATIONAL DATABASES AND QUERIES Akhila Kondai akhila. kondai@mail. wvu. edu October 09, 2013

DATABASE CREATION • • • Open Access Select Blank Database For “File Name”, call it students. accdb Save to Desktop Click Create Set the Objects bar to Object Type to see “All Access Objects” 2

DESIGNING DATABASES • Organizing is the key!! • Think of all the fields that need to be stored • Group the fields into tables logically – Split things out to avoid storing multiple times • Designate primary keys in each table – Field value always unique for every record 3

DESIGNING DATABASES Related tables have a common field on which to form the relationship • Primary key in one table • Foreign key in the other table 4

PLANNING • Consider what data our database will hold • We wish to keep records on students: – Name – Address – Phone – Student number – Major … 5

DRAWING IT ALL OUT • Here are the intended fields for the student table: – First. Name, Last. Name – St. Address, City, State, Zip – Phone. Num – Student. Num What about the Major? … 6

DRAWING IT ALL OUT • Major, will be used over and over – One major to Many people • We will make a separate table for the Majors – Common field of “Major. ID” – This field will be placed in BOTH tables – Primary Key in Majors table – Foreign Key in Students table 7

CREATING THE FIRST TABLE • With Table 1 selected in the Objects bar, click the ruler to enter Design View • Name the table Student. Info and click OK 8

NEW TABLE IN DESIGN VIEW • Note that a primary key field is already created • We can change the name of this field and add others… 9

NAMING FIELDS • Fields and Tables need to be named • Pick meaningful, unique names – Can contain letters, numbers, & some special characters – Spaces generally not used • Can put capital letters at the start of words to show separation. ex: Two. Words • Replace “ID” with the field name Student. Num 10

FIELD DATA TYPES • Specify what kind of field values you want • How they can be used • What other properties the field can have Let’s look at what kinds are available… 11

FIELD DATA TYPES … 12

FIELD DATA TYPES 13

FIELD SIZES • Default field sizes that can be overwritten • Setting a Field Size tells how much space to allocate • Should be only as large as the maximum-size piece of data needing to be held • Making it too large will waste disk space as it is consumed whether filled or not. 14

STUDENTINFO TABLE FIELDS • Make the first field be of Data Type Number • Create the following additional fields: • First. Name of type Text • Last. Name of type Text • Phone. Num of type Text 15

CREATING FIELDS • • • Major. ID of type Number * St. Address of type Text City of type Text State of type Text Zip of type Text * This type must be same as Major ID for creating relationship… 16

TWEAKING THE FIELDS • Change a couple Text Field Sizes to be “ 20” • Put a description on Major. ID field • Relationship’s Foreign Key • Put a caption on Student. Num field • Student ID Number • Close the Student. Info table and save changes. 17

MAKING THE MAJORS TABLE • We said that we would like to create a separate table to have the majors listed as many students will have the same major and this will save a lot of space • Select the Create Ribbon • Choose Table • Go to Design View and name it Majors 18

MAJORS TABLE • First field will be Major. ID (Primary Key) • Create the Major. ID field of type Number • Must be Number to match the field in the other table • Description: Relationship’s Primary Key • Create a Major. Name field of type Text • Will hold the worded names of the majors … 19

ENTERING DATA IN MAJORS TABLE • Flip to Datasheet view & save the changes • Enter the following records in this table… • Close table and save 20

ENTER RECORDS IN STUDENT INFO TABLE • Open the Student. Info table in Datasheet View • Create 4 student records (make up names and such) • Make 1 of them Major. ID 1 • Make 2 of them Major. ID 2 • Make 1 of them Major ID 3 • Close the Table! 21

WHY MAKE RELATIONSHIP? ? • To: – Eliminate redundancy / Reduce wasted space – Reduce possible inconsistencies – Keep Efficient. Enter data once & reuse… 22

CREATING THE RELATIONSHIP • Make sure both tables are closed ! • Always close tables after working on them, otherwise you may get errors when things are in use ! • • Database Tools ribbon Select Relationships Double-click each table to add Close the Show Table window 23

CREATING THE RELATIONSHIP. . . • Drag Major. ID from Majors table to Major. ID in the Student. Info table • Enforce Referential Integrity • Click Create • Close the Relationships window & save 24

HOW TO CREATE QUERY? • 2 ways to create query – Query Wizard – Query Design 25

CREATE A QUERY USING QUERY WIZARD • Step 1: Create ribbon -> Queries group -> Query Wizard - Select Simple query wizard • Step 2: “Select required fields form one or more tables” - Select Table Student. Info and add all fields and then ‘finish’ • Use design view to change the query. 26

LETS INTERPRET QUERY IN DESIGN VIEW • Top: You can see the tables, so that you can add fields by dragging them. • Bottom: Several options to design your own query. 27

WHERE TO ADD CRITERIA ? ? ? • First row: Uses “AND” operator between multiple criterions. • Second row: Uses “OR” operator between multiple criterions. 28

RUN QUERY • Select Run or double click on query. 29

CREATING A QUERY USING QUERY DESIGN • Create ribbon -> Queries group -> Query Design • Add both tables and close “Show Table” • Notice the relationship showing! • Expand the Student. Info table to see all fields 1/31/2022 30

WHAT INFORMATION TO VIEW? • We want the query to show the Student number, first & last names, phone number, full address, and name of their major (not number) • We will not use the Major. ID field because we do not need to see the number • The relationship will use the common field to get the Major. Name by using the Major. ID number between tables. 1/31/2022 31

ADD FIELDS TO THE QUERY • Double click to add the fields in the order we want them to be displayed: • Do not add the Major. ID fields! • Student Info Table • Student. Num, First. Name, Last. Name, Phone. Num • St. Address, City, State, Zip Majors Table • Major. Name 1/31/2022 32

RUN THE QUERY ! • Click the ! up to run the query • Observe – The information in the specified fields of both tables showcased. Observe the Major names are correctly Displayed. This is due to the relationship between the tables. 1/31/2022 33

KEEP THE QUERY TO USE AGAIN • Close & Save the query as Student. Query • Rerun the Query • Close Access 1/31/2022 34