University of Florida Introduction to Database Concepts and

University of Florida Introduction to Database Concepts and Microsoft Access HSC IT Center Training

University of Florida Database Concepts and Access u Introduction – Database – Microsoft Access u Design and Creation – Plan – – Tables Queries Forms Reports HSC IT Center Training

University of Florida Things to Do u Contact your customer support u Backup HSC IT Center Training

University of Florida What is a Database? u u u A structured collection of related data An filing cabinet, an address book, a telephone directory, a timetable, etc. In Access, your Database is your collection of related tables HSC IT Center Training

University of Florida Data vs. Information u u Data – a collection of facts made up of text, numbers and dates: Murray 35000 7/18/86 Information - the meaning given to data in the way it is interpreted: Mr. Murray is a sales person whose annual salary is $35, 000 and whose hire date is July 18, 1986. HSC IT Center Training

University of Florida Basic Database Concepts u Table – A set of related records u Record – A collection of data about an individual item u Name: Barry Harris College: Medicine Tel: 392 -5555 Field – A single item of data common to all records Name: Barry Harris HSC IT Center Training

University of Florida An Example of a Table Fields Records Name Gator. Link Phone College Graff rgraff 392 -3900 Pharmacy Harris bharris 392 -5555 Medicine Ipswich zipswich 846 -5656 PHHP HSC IT Center Training

University of Florida Design and Document Your Database u A designers best tools are a pencil and paper – It is important to plan what you are going to do u The sooner you touch the computer the sooner you’ll make a mistake – If you don’t plan you will often have to start again – Document what you are doing, will you remember what you did in three months time? HSC IT Center Training

University of Florida Questions To Ask Yourself u What have I got? – u What do I want? – u (Outputs) What do I need to do to get there? – u (Inputs) (Process) How am I going to build it? – (Application/Program) HSC IT Center Training

University of Florida Database Options v Freeware/Shareware - 1, 000’s of records v Microsoft Excel - Limit of 65, 536 Rows v Microsoft Access - 100, 000’s of records v Oracle/SQL - 1, 000, 000’s of records & allows for Multiple users HSC IT Center Training

University of Florida Why Use Access? u Familiar look and feel of Windows u Easy to start building simple databases u Can u It’s build sophisticated systems already on your computer u True relational database HSC IT Center Training

University of Florida What is a Relational Database? u A relational database is a collection of tables from which data can be accessed in many different ways without having to reorganize the database tables. – That is, once relationships are created, tables can “talk” to each other. We can link (relate) the tables to find: Which doctors are seeing a patient v Which students are in which class v Which item is selling the most on Friday’s v HSC IT Center Training

University of Florida Basic Design Rules of Relational Databases u Data is broken down into Smallest Logical Parts Putting all of the home address in one field may make for convenient data entry, but it makes it very difficult to work with the data. For example, what if I needed to sort by City or Zip Code? Pulling fields together is fairly simple, pulling them apart is very difficult. Name Gator. Link Home Address Graff rgraff 123 West Main Street, Gainesville, FL 32601 Harris Bharris 456 South 3 rd Road, Newberry, FL 32684 Ipswich zipswich 846 West 52 nd Ave, Gainesville, FL 32609 You can join fields together in queries, forms and reports. HSC IT Center Training

University of Florida Basic Design Rules of Relational Databases u Unique Field Names Access won’t let you use the same field name twice in one table but it can become confusing to people doing data entry if you are not clear. Try to keep a consistent naming convention. Name Gator. Link Addr 1 Addr 2 Graff rgraff PO Box 100012 Gainesville, FL 32610 123 West Main Street, Gainesville, FL 32601 Harris Bharris PO Box 100234 Gainesville, FL 32610 456 South 3 rd Road Newberry, FL 32684 846 West 52 nd Ave Gainesville, FL 32609 PO Box 100256 Gainesville, FL 32610 Ipswich zipswich Field names can be up to 64 characters long. (see tables documentation for more details) HSC IT Center Training

University of Florida Basic Design Rules of Relational Databases u Unique Field Names You also want to be aware of the field names across tables. For example several tables may use the Field First. Name. When you use those fields in other parts of the database things can become very confusing very quickly. DOCTOR TABLE Name Day Off PATIENT TABLE Sallye Shapiro Thursday Name Address Samuel Smith Monday Annie Adams 6831 NW 4 th Ave Sidney Samueson Friday April Appleton PO Box 456 Arnold Arlington 234 SE 45 th Road Bobbie Brown 234 Peter Pan Terrace Butch Bruce 3243 SE 4 th Terrace When these two Name fields are pulled into the same Query/Form/Report they will appear with the table name in front of the field name: Patient Table. Name HSC IT Center Training

University of Florida Basic Design Rules of Relational Databases u No Calculated or Derived Fields If we wanted to see how long an employee had been working with us, we can calculate their Length Employed by subtracting their hire date from today’s date. However, since today’s date is always changing, this data very quickly becomes stagnant. Name Gator. Link Date Hired Length Employed Graff rgraff 05/15/2000 4 yrs, 2 mo Harris bharris 02/08/1986 18 yrs, 6 mo 12/15/1999 4 yrs, 7 mo Ipswich zipswich Access will let you create calculations in queries, forms and reports. HSC IT Center Training

University of Florida Basic Design Rules of Relational Databases u Unique Records If you don’t have unique records, your database can’t tell which record you may be referring to. Name Gator. Link Phone College Graff rgraff 2 -3900 Pharmacy Graff rgraff 392 -3900 COP Graff rgraff 392 -3900 Pharmacy Harris bharris 392 -5555 Medicine Ipswich zipswich 846 -5656 PHHP HSC IT Center Training

University of Florida Primary Keys Name Gator. Link Phone College Graff rgraff 392 -3900 Pharmacy Harris bharris 392 -5555 Medicine Ipswich zipswich 846 -5656 PHHP To ensure that each record is unique in each table, we can set one field to be a Primary Key field. A Primary Key is a field that will contain no duplicates and no blank values. Looking at the table above, what would be the best Primary Key? HSC IT Center Training

University of Florida Primary Keys Name GL Phone College Graff rgraff 392 -3900 Pharmacy Harris bharris 392 -5555 Medicine Ipswich zipswich 846 -5656 PHHP While each column in this particular data set has unique data, the field that will work best for us is GL (Gator. Link). Many employees will work for the same college, have the same last name and possibly even share telephone numbers, but each employee should have a unique Gator. Link ID. When there is not a unique field in your data set, you can use an Auto. Number. Access can create incremented or random Auto. Numbers for your primary key. HSC IT Center Training

University of Florida Let’s Get Started Planning Patients Appointments HSC IT Center Training

University of Florida Opening a Database u To open a database when you start Access – From the bottom of the left panel, choose the database you wish to open, or choose More… to browse for another database u To Create a database, click on the Create a new file… option in the bottom of the left frame. u You can also use the file menu to open existing databases or create new ones. HSC IT Center Training

University of Florida The Access Database Window u Icons down the left hand side provide access to all database objects – Select the object by clicking the icon HSC IT Center Training

University of Florida Data View/Design View Datasheet View Design View HSC IT Center Training

University of Florida Navigating Fields and Records u To move through records and fields v v v u Tab Shift+Tab Enter v v v Home/End Ctrl+Home Ctrl+End v v v Page Up Page Down The Arrow Keys To move through records First Record Current Record Previous Record Last Record Next Record New Record HSC IT Center Training

University of Florida Introducing Tables u u Database is a collection of TABLES Tables store all the data HSC IT Center Training

University of Florida Introducing Queries u u u A means of asking questions (querying) of your data Can look across a number of Tables and other Queries Can perform Calculations and Combine fields HSC IT Center Training

University of Florida Introducing Forms u u u A friendlier view of the database Used for data input, menus, display and printing Can perform Calculations and Combine fields HSC IT Center Training

University of Florida Introducing Reports u Output of information in a printed report u Allows you to group and summarize data u Can perform Calculations and Combine fields u Cannot u Can Edit Data Make Labels HSC IT Center Training

University of Florida Working Together Tables Queries Employees Customers Reports Forms Customer Company Name Address City Telephone Contact Name HSC IT Center Training

University of Florida Let’s Create Our Database HSC IT Center Training
- Slides: 30