Database Concepts

• Terminology
• Relational Database stores information in tables which are composed of rows and columns
• Columns(fields) – represent an attribute(single item of information) of the data item represented in the table
• Rows(records) – group of related fields that contain all the data about the object represented
• Table – group of related records
• Relational database can consist of one or more related tables
• Database advantages
• Data can quickly and easily be retrieved
• Data an be displayed in any order
• Allows you to control how much information you can view at one time
• Steps in Designing a database
• Identify information that  user will need from the database, identify attributes of the data(fields)
• Organize the fields into one more tables
• Establish relationships between tables
• Create any indexes for the tables
• Index is a special table created and maintained by the database management system
• Using Visual Data Manager to create a database
• To start VisData
• In VB click on Add_ins, Visual Data manager
• Or go to the directory in which VB is installed(usually C:\Program files\microsoft visual studio\VB)
• To Create a database in Vis Data
• Click on file,new,micrsoft access,, version 7.0 MDB
• Enter the name of the database
• Access creates a file called {name}.mdb
• To create a table
• Click on New Table, after right clicking in the database window
• Enter name of table in TableName: textbox
• To add fields
• Click add field button
• Type name of field
• Enter datatype
• Choose size for text fields
• Indicate whether zero length is allowed
• AllowZeroLength check box
• Click OK
• When done adding fields, click build table to save work
• To open an existing database
• In VisData click on Open Database
• Click Microsoft Access
• Choose th MDB file you want to edit
• Right click on the table you want to edit and choose edit
• ADD
• Edit
• Delete
• Using the ADO Control
• Active Data Objects
• To add control to project
• Click on project bar
• Click on Microsoft ADO Data Control 6.0(OLEDB)
• Click OK
• Double click on Adodc tool to add to current form
• Connection string property
• Indicates database to connect to
• Click on button
• Click Build button
• Choose Microsoft Jet 3.51 OLE DB Provider for file based databases or Micrsoft OLE DB Provider for ODBC Drivers for file based systems, SQL Server
• Choose Next
• Select the mdb file you previously created
• Click Ok
• To indicate the Table in the database to utilize
• Set the RecordSource Property
• Click … button
• Choose Command type
• For a table
• Choose 2-adCmdTable
• Click the Table Name
• For a query
• Choose 1-adCmdText
• Enter query(SQL select statement) in Command Text(SQL
• Binding controls to the ADO Control
• In the textbox property list, enter the name of the ADO control in the DataSource Property
• Recordset object of a ADO Control – adodc.recordset
• Methods
• AddNew – adds a new blank record
• Delete – delete the current record
• Update – saves changes made to current record
• MoveFirst – move the record pointer to the first record in the record set
• MoveNext – move the record pointer to the next record in the record set
• Refresh – used if changing recordsource property at run time
• Properties
• EOF – test for end of file of recordset, True is record poiter is positioned after last record in recordset, otherwise False
• Using ADO Grid control
• To add control to project
• Click on project bar
• Click on Microsoft ADO DataGrid Control 6.0(OLEDB)
• Click OK
• Double Click  to add to current form
• Set DataSource Property
• Set to an Ado Data control
• To retrieve fields
• Right click on control
• Click Retrieve fields
• Click “yes” to “Replace existing grid layout with new field definitions?”
• Change appearance
• Right click on control, click properties
• General Tab
• AllowAddNew
• AllowDelete
• AllowUpdate
• Column Tab
• Change display name         
• Format Tab
• Change how data is displayed
• Currency, date, number