designing a database an introduction for beginning vb developers copyright ©2003, tore bostrup
TRANSCRIPT
Designing a Database
An Introduction for beginning VB Developers
Copyright ©2003, Tore Bostrup
6/23/2003 Copyright ©2003, Tore Bostrup 2
What is a Database
• There are different types of databases– Relational Databases– Object Databases– Hierarchical Databases– Network Databases– Etc.
• But usually, we talk about Relational Databases
6/23/2003 Copyright ©2003, Tore Bostrup 3
What is a Relational Database
• A Relational Database is briefly described as a collection of Tables
• Each Table is made up of Rows of data that is organized in Columns– Think of an Excel Spreadsheet where the first
row contains the column names, only the columns that are named are used, and each column only contains one type of data.
6/23/2003 Copyright ©2003, Tore Bostrup 4
What makes it Relational
• “Relational” comes from the way that different tables relate to each other.– A column that exists in two tables can be used
to “relate” the two tables by storing the same value in both tables:
6/23/2003 Copyright ©2003, Tore Bostrup 5
Relationship between two Tables
OrderID CustID (Info)
3001 101 …
3002 126 …
CustID Cust Name
Cust
Addr
1 B&H …
101 PPCC …
126 ACME Comp
…
6/23/2003 Copyright ©2003, Tore Bostrup 6
Relationship Types
• One-to-One– This is not very common, and mostly used for
optimization. Usually, only one table is needed.
• One-to-Many– This is the most common relationship. For instance a
Customer may have MANY Orders, but each Order is only for ONE Customer.
• Many-to-Many– Also relatively common. For example, an Order may
contain MANY Products, and a Product may be part of MANY Orders.
6/23/2003 Copyright ©2003, Tore Bostrup 7
Some Other Concepts
• Primary Key– A Primary Key is one or more columns that
together are sufficient to identify a single row in a table.
– It is Unique within the table– It always has a value
6/23/2003 Copyright ©2003, Tore Bostrup 8
Some Other Concepts
• Foreign Key– A Foreign Key is a column in a table that
contains a copy of a value from another table in order to establish a relationship to that table.
– The Foreign Key usually refers to the Primary Key in the other table (to make sure it only points to one row).
6/23/2003 Copyright ©2003, Tore Bostrup 9
Designing the Database
• We often design the database using Diagrams called Entity-Relationship or ER Diagrams.
• Tables are shown as boxes, and Relationships are shown as lines.
Orders
Customers
Products
Customers place Orders
Orders contain Products
6/23/2003 Copyright ©2003, Tore Bostrup 10
What Tables Do I Need?
• Step 1:– What kinds of “Things” are you keeping
information about? • For starters, each kind of “thing” tends to be a
Table:– In an order system, we’d probably at least keep
information about Orders and Customers.
– We may also want a catalog of Products that can be ordered
6/23/2003 Copyright ©2003, Tore Bostrup 11
What Tables Do I Need
• Step 2:– Determine the data items that you need for
each “kind of thing”, watch out for information that can be repeated
• Multi-valued columns should be avoided• Repeated columns should be avoided• No “horizontal” repetitions should be included• “Vertical” repetitions (multiple rows with same
value in a column) should only represent a relationship to another table.
6/23/2003 Copyright ©2003, Tore Bostrup 12
Repeated Values
• A value is considered repeated only if it represents the same “measurement”. The fact that two people are the same height does not mean that the data is “repeated” in this sense.
• A repeated value that is part of the Primary Key is allowed if the Primary Key consists of multiple columns.
6/23/2003 Copyright ©2003, Tore Bostrup 13
Repeated Values
• Examples of repeated values:
•OrderNo •Line1Item •Line1Qty •Line1Price •Line2Item •Line2Qty •Line2Price
•245 •PN768 •1 •$35 •PN656 •3 •$15
•OrderNo •ItemNo •Customer •Item •Qty •Price
•245 •1 •SteelCo •PN768 •1 •$35
•245 •2 •SteelCo •PN656 •3 •$15
•246 •1 •Acme Corp •PN371 •1 •$2.99
•246 •2 •Acme Corp •PN015 •7 •$5
6/23/2003 Copyright ©2003, Tore Bostrup 14
What Tables Do I Need
• Step 3:– Once you have completed defining your
“kinds of things” and fixed any repeating data for those things, draw your relationships.
• Step 4:– Resolve Many-to-Many relationships (we will
get back to this).
6/23/2003 Copyright ©2003, Tore Bostrup 15
The Relationships
• Draw the tables
• Draw the Relationships
• Define the Relationship Types
Customers Orders
Customers Orders Products
Customers Orders Products1 N N M
Products
6/23/2003 Copyright ©2003, Tore Bostrup 16
The Many-to-Many Relationship
• Consider the Products table – the products that can be ordered. Assume it is simply a “catalog” with a list of the products available.– Each Order may contain MANY Products, and each
Product can be a part of MANY Orders.
– But how do we represent this?
6/23/2003 Copyright ©2003, Tore Bostrup 17
Representing Many-to-Many
• Remember, a Relationship is defined by keeping a copy of the value of a column from the other table, usually that other table’s Primary Key.– So each row in a table can only reference a
single row in the related table– And we aren’t supposed to repeat the data so
we shouldn’t have multiple rows for the same item.
6/23/2003 Copyright ©2003, Tore Bostrup 18
OOPS – We Need Another Table
• The solution is really pretty simple (as long as you know what to do):– Introduce an intermediate table between Orders and
Products.
– Relate it as Many-to-One to EACH of the two tables (Orders & Products)
– This table only needs the Primary Key from the two tables, and their combinations become the representation of a Many-to-Many relationship between Orders and Products
6/23/2003 Copyright ©2003, Tore Bostrup 19
The Magic Intermediary Table
• And voila – we have a Many-to-Many relationship between the Orders and the Products table.– It is common to name the table with the name of both of the
tables it “connects”. But in an order system, you will typically see this table named OrderDetail. And it will typically contain additional data such as UnitPrice and Quantity.
Orders
OrderIDCustomerIDOther…
Products
ProductIDProductNameOther…
ProductOrders
OrderIDProductID
1 M N 1
6/23/2003 Copyright ©2003, Tore Bostrup 20
Microsoft Access
• MS Access actually has many different personalities:– A Database Manager– A Database Design Tool– An Application Development Tool (like VB6)– An ad hoc Query Designer– A Report Designer– …
6/23/2003 Copyright ©2003, Tore Bostrup 21
Our Main Interests
• As VB6 Developers, our main interests in MS Access are– As a Database Design Tool – As a Database Manager for small database
applications – typically for a departmental, workgroup or single user application.
6/23/2003 Copyright ©2003, Tore Bostrup 22
Getting Started with Access
• The Database Wizard lets you create a “starter” database AND application (developed in the MS Access Application Development Environment) without any prior knowledge.
• This can be good – it gives you a head start on your design if one of the databases are appropriate for your needs.
6/23/2003 Copyright ©2003, Tore Bostrup 23
Getting Started
• The Wizards also can keep you from learning how to design your own database, and you may decide to use the Access Development Environment.– The development environment is easy to use
for simple and “standard” things, but it gets difficult to make it do things the way you want it to (you can’t quite get control over it).
– PS: That is MY opinion…
6/23/2003 Copyright ©2003, Tore Bostrup 24
MS Access Database Wizards
• Access contains several database Wizards:• Asset Tracking• Contact Management• Event Management• Expenses• Inventory Control• Ledger• Order Entry• Resource Scheduling• Service Call Management• Time and Billing
6/23/2003 Copyright ©2003, Tore Bostrup 25
To use the Database Wizard
• Open MS Access– Specify Access database wizards, pages, and projects
and Click OK
– Select the Databases tab
– Select the database type you want and click OK
– Give it a file name and click Create
– Click on Next
– Additional screens let you select which tables and what data elements you want in the database.
6/23/2003 Copyright ©2003, Tore Bostrup 26
The MS Access Order Entry DB
6/23/2003 Copyright ©2003, Tore Bostrup 27
Creating a Table in MS Access
• Open an existing database, or create a blank Database.
• You have three choices– Create table in Design view– Create table by using wizard– Create table by entering data
6/23/2003 Copyright ©2003, Tore Bostrup 28
Create table by entering data
• This allows you to simply start entering data as if you were typing into an Excel spreadsheet.
• However:– No column names are defined (they will be
called Field1, Field2, etc.)– Access makes a “guess” as to their data type
based on the data entered.
6/23/2003 Copyright ©2003, Tore Bostrup 29
Create table by using wizard
• Starting in the middle, this is similar to the Database Wizard, but it only applies to a single table – and doesn’t create any application code.– Think of it as creating a new table from
predefined table templates.
6/23/2003 Copyright ©2003, Tore Bostrup 30
Create table in Design view
• The Table Designer lets you define the column names, their data types, and other information about the columns.
6/23/2003 Copyright ©2003, Tore Bostrup 31
If You Only have VB6
• Ever since VB3 or 4, VB has included a sample application called VISBAS.– In VB6, this application is on the MSDN Library CD,
in the Samples\VB98\visbas folder.
– An executable VISBAS.EXE is installed in the same folder as VB6, and available under the VB6 menu Add-Ins > Visual Data Manager
– The executable is fine for Access 97 databases, but not for Access 2000 databases.
6/23/2003 Copyright ©2003, Tore Bostrup 32
Making VISBAS Work
• If you need to work with Access 2000 (or newer) databases, you need to recompile it with reference to Microsoft DAO 3.6 Object Library– Copy the visbas folder to you HD– Remove the Read Only setting from the visbas
folder and the files in it.– Open visbas.vbp
6/23/2003 Copyright ©2003, Tore Bostrup 33
VISBAS for Access 2000
• Contd…– Select menu Project > References…– Unselect Microsoft DAO 3.5 Object Library– Select Microsoft DAO 3.6 Object Library– Move the new reference up above the
Microsoft ActiveX Data Objects 2.5 Library (and possibly above the Microsoft Visual Basic 6.0 Extensibility)
– OK, Save Project, and Make Visdata.EXE…
6/23/2003 Copyright ©2003, Tore Bostrup 34
Using VISBAS
• Either run VISBAS.EXE or in the VB IDE, select Add-Ins > Visual Data Manager…– Create a New Database:
• Select File > New… > Microsoft Access > Version 7.0 MDB… and give the new database a name.
– Open an Existing Database:• File > Open Database… > Microsoft Access…
– In either case, you will get two windows – a Database Window and a SQL Statement window.
6/23/2003 Copyright ©2003, Tore Bostrup 35
Create a Table using VISBAS
• Right-Click in the Database Window• Select New Table
– Type in the table name in the dialog box– Click the Add Field button
• Type in the (Column) Name• Select the (data) type and size (length) of the
column, and select other settings as appropriate• The OrdinalPosition, ValidationText,
ValidationRule, and DefaultValue are optional
6/23/2003 Copyright ©2003, Tore Bostrup 36
Create a Table using VISBAS
• Click OK for each field that you add
• Click Close when you don’t want to add any more fields (columns) to the table
• You can also add Indexes. It is always good to have at least a Primary Key defined.
• When you are done, click Build the Table.