What is a Database?

1. What is a Database? • A collection of organized information on a particular subject. • It is organized so that queries and reports based on the information can be readily performed. • Common examples of databases: • telephone book • Student records

2. Other databases - Company records - Police records - Census records - University records The organization is important to be able to retrieve information from a data base.

3. Databases and Spreadsheets Databases more for organization of large amounts of non numeric data where queries are more important then computation. Spreadsheets are more powerful computational tools.

4. . DBMS: A database management system is a collection of software for managing the database and contains software for organizing the data and verifying it, as well as software for retrieving the data through queries as well as creating reports based on the data.

5. DBMS Software • There are different DBMS software versions available: • Microsoft Access (which we’ll be using) • Microsoft SQL Server (more features than Access) • Oracle • Corel Paradox • MySQL (open-source) • a lot more…

6. A DBMS can: • -Create / Delete tables • -Modify tables:  • add records • delete records • edit records • rearrange records • change the table structure • Retrieve data from a single / multiple table(s) • find and display a record • answers queries • Createreports • formatted displays of query results or table contents

7. Tables and Records The basic organizational tool in the database is the table which is organized into records which are subdivided into fields. Conceptually it looks like: Id Lastname Firstname Age Weight 1 Smith Joe 25 70 2 Lee Jane 57 45 3 Harper Stephen 60 67 The data base may be made up of several tables of interrelated information

8. A record contains a number of fields: • Examples of fields are: • Student number (This field is called a key) • Last name • First Name • Degree Program • Address • Child of Alumnus • And field may be of different types text, number, dates etc.

9. A particularly important field is called a key. A key is a field that uniquely identifies the record. This field is called the primary key of the table. A name would not be a suitable key because it is not unique. Suitable keys are -student number -social insurance number -drivers license number

10. Structure of the Table • The structure of the table is the specification of fields for records in table. • The structure specifies the type (or kind of data) for each field.  • Different types of fields are treated differently • when they are entered • when they are displayed • when they are used 

11. Field Types • Examplesof field types: • integer (whole numbers, ex: 1, 55, 993) • currency (ex: dollars, pounds, euros) • real number (ex: 1.034, 55.3321, etc) • time (ex: 10:42) • date (03/03/09, or January 1st 2006). • string (text)

12. The Four basic operations in a database. • Design: • Create the database by defining the tables and Specifying the fields. • Data Entry:  • Adding the actual data by hand or imported from other files. Easy to make errors. • Queries:  • Ask questions about the data. • Reports: -producing a document with info

13. Video Store Database Example

14. Video Store Example • We want to create a database for a video store. • Questions: • How will information be organized? • What will we represent as tables? • What information is required?  

15. Video Store Example (2) • Decision: • Three groups of information (tables). • Videos • Customers • Rental information. • For each table: • What information would we like to maintain?   • What will be the unique primary key (or composite key)?

16. Table 1: The Video Table • Required information: • Video ID (primary key) • Title • Price • Quantity • Category

17. Example Video Table

18. Table 2: The Customer Table • Required Information:  • Membership Number (primary key) • Name • Address • Phone Number

19. Example Customer Table

20. Table 3; Rental Information TableThis is a table of transactions • Required Information:  • Membership Number • Video ID • Date Rented • Date Due • Returned Yet?

21. Example Rental Information Table

22. Introduction to Microsoft Access 2007

23. Access Database Objects • First we’ll see some objects defined in Access Databases. • Tables: store your data in rows and columns. All databases contain one or more tables. • Queries: retrieve and process your data. They can combine data from different tables, update your data, and perform calculations on your data.

24. Access Database Objects • Forms: control data entry and data views. They provide visual cues that make data easier to work with. • Reports: summarize and print your data. They turn the data in your tables and queries into documents for communicating ideas.

25. Microsoft Access in MS Office • First of all, not all versions of Microsoft Office include Access. • only the “Professional” version of office includes Access, NOT the student version. • NOT part of any version of Office for the Mac.

26. Starting Microsoft Access • As with other Microsoft Office applications, you can start Access by: • double-clicking the desktop icon of there is one. • double-clicking an Access file you have created previously. • going through the Start menu.

27. Getting Started Window in Access

28. Creating a New Database • In order to create a new database in Access we can first select what type of database we want. • Access has several built-in templates such as: • Business • Personal • Sample • Education (Faculty, student).

29. We wont be using the templates but starting from a blank database. But we could use one of the templates. For example: Education Databases • Faculty • Keep track of information about faculty members such as names, contact information, etc. • Student • Names, contact, medical information, etc.

30. Creating a Blank Database • Of course if none of these templates meet our requirements, we can create a blank database. • Click on Featuring, then the Blank Database option.

31. Creating a Blank Database (2) • After clicking the Blank Database option, a menu will show up on the right that allows you to chose a filename and location for your new blank database.

32. Access Window Toolbar Table panel Table list

33. Access Window (2) • The Microsoft Access window should look a bit familiar. • uses the same types of tabs as used by Excel 2007 and also other Microsoft 2007 office products • The table panel actually looks a lot like Excel with Rows (Records) and Columns (Fields).

34. Access Toolbars • As with the other Microsoft software we have seen Access has multiple Tabs or toolbars instead of menus. • Like we have done for the other applications, we’ll see some of the Tabs relevant to Access, the others we have seen before.

35. Create Tab • The Create tab contains options to create new: • tables • forms • reports, etc

36. External Data Tab • The External Data tab allows the database creator to importdata from other sources such as: • Excel File • Other Access file • Text files • etc…

37. Database Tools Tab • The Database Tools tab allows us to create relationships between tables or with other databases, etc.

38. Datasheet Tab • The Datasheet tab allows us to add new fields, change the type for the fields, edit relationships, etc.

39. Access Views • There are several different views you can use for your database, we’ll see the two most useful: • Datasheet View: which displays the fields and the records. This view is useful to enter records. • Design View: This view allows us to add fields, change the field type, set the Primary key. This view does not show the records. • You can switch view with the Views button.

40. Datasheet View

41. Design View

42. Tables: Fields and Records • Databases in Access contain at least one table. • Each column( e.g. Category) is a field. • Each row is a record. • A record is a collection fields. • A field is a single piece of information.

43. Tables • Tables store data • The most important building block of a database. • If the table is well designed then data should not be duplicated. • Having to duplicate data shows poor design • Table is composed of records and each record is composed of fields.

44. Attributes of the Table Fields Primary Key: the unique identifier for each record (row) Data Type: specifies what kind of data in going to be stored in each field.

45. More on Primary Keys • Primary Keys are like fingerprints: there are no two human beings with identical fingerprints. • Between different records, the fields that are not part of the primary key may be equal. • In the case of composite key, the full set is unique for each record (individually they might have duplicates).

46. More on Primary Keys (2) • When you create a table, access allows you to specify whichfield will be the primary key. • Also, Access can assign a numeric integer data increment in 1 unit automatically, every time you enter a new record. • Called ID by default. • Each record will have a unique ID number. • You can change the field name to a user friendly one such as VIDEO_ID or STUDENT_ID.

47. Assigning Primary Keys • Right click over the field you want to specify as primary key. • Click on Primary Key. • A key will appear next to the field.

48. Assigning Primary Keys (2) • You can also use the Primary Key button at in the Design toolbar.

49. Creating a Database

50. Creating a Database • We’ll demonstrate how to create a database by using the Video store example we have seen last lecture. • The Video Store database had three tables: • Video • Customer • Rental Information. • We’ll show step by step how to create this database using Microsoft Access. • We’ll first create our Video table.