Database Programming

1. Database Programming Advanced Computer Programming

2. Lecture Objective • After completing this Lecture: • Students will be able to understand basic concepts of databases • Create Databases in MS Access • Create Tables in databases • Create Primary Keys • Create Relationships between tables

3. Overview • Introduction to MS Access and Databases • Hierarchy used in Access • Opening/Creating an Existing Database • Creating Tables in Database • Defining Relationships

4. Introduction • Access is a database program that enables one can store information such as a client list, products, invoices, inventory, events, and other collections of data. • In addition to entering the data in a database table, you have several tools for working with the data. • Forms for simpler data entry can be created. • You can create a query to display a set of record. • All these elements are Access objects and are stored together in the database.

5. Introduction • Microsoft Access is a powerful program to create and manage your databases. • In Access terms, a database is a collection of all the tables, queries, forms, data access pages, reports, macros, and modules that compose a complete system. • Relational refers to the fact that the tables that comprise the database relate to one another.

6. Database File Table Field Datatype Value Hierarchy that Microsoft Access uses in breaking down a database

7. Hierarchy that Microsoft Access uses in breaking down a database • Database File • This is your main file that encompasses the entire database and that is saved to your hard-drive or floppy disk. Example StudentDatabase.mdb

8. Hierarchy that Microsoft Access uses in breaking down a database • Table • A table is a collection of data about a specific topic. • There can be multiple tables in a database. • Example #1) Students • Example #2) Teachers

9. Hierarchy that Microsoft Access uses in breaking down a database • Field • Fields are the different categories within a Table. • Tables usually contain multiple fields. • Example #1) Student LastName • Example #2) Student FirstName

10. Hierarchy that Microsoft Access uses in breaking down a database • Datatypes • Datatypes are the properties of each field. • A field only has 1 datatype.

11. Opening/Creating an Existing Database • After starting Access, create a new database or open an existing database. • A database is stored as a file on your computer or on a network computer. • To work with the objects in a database, one must open the database file.

12. Creating Tables in Database • A table is a collection of data about a specific topic, such as students or contacts. • Using a separate table for each topic means that you store that data only once, which makes your database more efficient, and reduces data-entry errors. • Tables organize data into columns (called fields) and rows (called records).

13. Creating Tables in Database • Primary Key • One or more fields (columns) whose value or values uniquely identify each record in a table. • A primary key does not allow Null values and must always have a unique value. • A primary key is used to relate a table to foreign keys in other tables. • For Example., make the Soc Sec # field the primary key, meaning that every student has a social security number and no 2 are the same. • To do this, simply select the Soc Sec # field and select the primary key button

14. Defining Relationships • After setting up multiple tables in your Microsoft Access database, we need a way of telling Access how to bring that information back together again. • The first step in this process is to define relationships between your tables. • After this, one can create queries, forms, and reports to display information from several tables at once.

15. Defining Relationships • Relationship • A relationship works by matching data in key fields - usually a field with the same name in both tables. • In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. • For example, teachers can be associated with the students they're responsible for by creating a relationship between the teacher's table and the student's table using the Teacher ID fields.

16. Visual Basic 2005 Express and the Data Form Wizard • The Visual Basic 2005 Express Edition doesn't have the Data Form Wizard. • But there is still an easy way to create a project that accesses a database. • For this purpose, we're going to create a simple Address Book project. • The names and addresses will come from a Microsoft Access database.

17. Visual Basic 2005 Express and the Data Form Wizard • VB.Net allows you many ways to connect to a database or a data source. • The technology used to interact with a database or data source is called ADO.NET. • The ADO parts stands for Active Data Objects which, admittedly, doesn’t explain much. • But just like System was a Base Class (leader of a hierarchy, if you like), so is ADO. • Forming the foundation of the ADO Base Class are five other major objects: • Connection • Command • DataReader • DataSet • DataAdapter

18. Visual Basic 2005 Express and the Data Form Wizard • We’ll see just what these objects are, and how to use them, in a later section. • But we can make a start on the ADO.NET trail by creating a simple Address Book project. • All we’ll do is see how to use ADO to open up the database you downloaded, and scroll through each entry. • What we’re going to be doing is to use a Wizard to create a program that reads the database and allows us to scroll through it. • The wizard will do most of the work for us, and create the controls that allow users to move through the database.

19. Visual Basic 2005 Express and the Data Form Wizard • The Form we create will look like this when it's finished: By clicking the buttons at the top, you can scroll through the database in the image above. We'll make a start in the next part.

20. Creating a database Project in VB 2005 Express Edition • Let's make a start on our Database project. So, once you have your VB IDE open, do the following: • Click File > New Project from the menu bar • Select Windows Application, and then give it the Name AddressBook. • Click OK • Locate the Solution Explorer on the right hand side (If you can't see it, click View > Solution Explorer from the menu bar. Or press Ctrl + R on your keyboard.)

21. Creating a database Project in VB 2005 Express Edition • You should see this: • We need to select a Data Source. • So click on Data Sources at the bottom of the Solution Explorer:

22. Creating a database Project in VB 2005 Express Edition • To Add a New Data Source, click on the link. • When you do, you'll see a screen welcoming you to the Data Source Configuration Wizard, Just click Next, to get to the screen below: You want to connect to a Local database file. So select this option, and click Next. In the next step, you have to tell the Wizard where your database is. So click the Browse button, and navigate to database file.

23. Creating a database Project in VB 2005 Express Edition • The Wizard will then take a look at your database, and display all your tables, fields amd objects. e.g.: Click the Tables box, and all of the other items will have ticks in them. Notice the DataSet Name: AddressBookDataSet. Click the Finish button.

24. Creating a database Project in VB 2005 Express Edition • When you click Finish, the Wizard goes to work. • When it's done, it looks as though not much has changed. • But notice the Solution Explorer:

25. Creating a database Project in VB 2005 Express Edition • The Data Sources area of the Solution Explorer now displays information about your database. • Click the plus symbol next to tblContacts: All the Fields in the Address Book database are now showing. In the next part, we'll see how to add these fields on to the Form.

26. Creating a Form for database navigation • In the last part, you had the Data Sources window displayed. This one (if you can't see the window, click Data from the menu bar. Then click Show Data Sources):

27. Creating a Form for database navigation • To add a Field to your Form, click on one in the list. • Hold down your left mouse button, and drag it over to your form: In the image above, the FirstName field is being dragged on the Form. Notice the mouse cursor has now changed.

28. Creating a Form for database navigation • When your Field is over the Form, let go of your left mouse button. A textbox and a label will be added. • There's two other things to notice: • a navigation bar appears at the top of the form, • and a lot of strange objects have appeared in the object area at the bottom:

29. Creating a Form for database navigation • Notice the Navigation bar in blue. • Run your program by hitting the F5 key on your keyboard. • You should see this:

30. Creating a Form for database navigation • Click the Navigation arrows to scroll through the database. • When you've played around with the controls, stop the form from running, and return to Design View.

31. Creating a Form for database navigation • Drag and Drop more Fields to your form. • But don't align them yet. • We'll see an easy way to do this. • But once you've dragged the fields to your form, it might look like this:

32. Creating a Form for database navigation • To Align the Controls: • Click on a Textbox with your left mouse button • Hold down the Ctrl key on your keyboard, and select a second Textbox • With the Ctrl key still held down, click each Textbox in turn • When all Textbox are selected, click on the Format menu at the top • From the Format menu select Align > Lefts. The left edges of the Textboxes will align themselves • From the Format menu select Vertical Spacing > Make Equal. The space between each textbox will then be the same

33. Creating a Form for database navigation • With your new controls added, and nicely aligned, press F5 to run your form. • You might have something like this: Click the Navigation icons to move backwards and forwards through your database.

34. The End Questions?