Database Design Terms and Concepts Flashcards
http://office.microsoft.com/en-us/access-help/database-design-basics-HA010341617.aspx?CTT=5&origin=HA101829991
A table is, most simply…
a list of rows and columns.
Row, also called…
a record. A record is a meaningful and consistent way to combine information about something.
Column, also called…
a field. A field is a single item of information - an item type that appears in every record.
What are 2 principles that guide the database design process.
1) Duplicate information (redundant data) is bad because it wastes space and increases the likelihood of errors and inconsistencies.
2) The correctness and completeness of information is important. If the db contains incorrect information, any reports that pull information from the db will also contain incorrect information and decisions based on the reports will be misinformed.
A good database design has these 4 qualities:
1) Divides your information into subject-based tables to reduce redundant data.
2) Provides Access (or other db sw) with the information it requires to join the information in the tables together as needed.
3) Helps support and ensure the accuracy and integrity of your information.
4) Accommodates your data processing and reporting needs.
List the 8 steps of the db design process:
1) Determine the purpose of your database.
2) Find and organize the information required.
3) Divide the information into tables.
4) Turn information items into columns.
5) Specify primary keys.
6) Set up the table relationships
7) Refine your design.
8) Apply the normalization rules.
Describe “Determine the Purpose of Your Database”
Write down the database purpose, how you expect to use it, and who will use it. If the database is complex or is used by many people, the purpose could be a paragraph or more and should include when and how each person will use the db. The idea is to have a well developed mission statement that can be referred to throughout the design process to help you focus on goals when you make decisions.
Describe “Find and organize the required information.”
Start with existing information, for example purchase orders in a ledger or customer information on paper forms. Gather all the documents and list each type of information (e.g. each box on the form). If you don’t have existing forms, imagine you are designing one.
Next, consider the types of reports or mailings you might want to produce from the database. E.g. you might want a product sales report to show sales by region, or an inventory summary that shows product inventory levels. You might also want to generate form letters to send to customers. Design the reports in your mind, imagine what it would look like and what information you would place on it. List each item. This helps identify other items you might want in your database that you might not already have included - for instance the mailing might have an opt-out box which you will want to include in the db. Or you might not have included a salutation field but will need it for the mailings.
Think about questions you will want the database to answer to zero in on additional items to record.
Describe “Divide the information into tables”
To divide the information into tables, choose the major entities or subjects. For instance, Customers, suppliers, products and orders. Look for redundancies - for instance if you listed each supplier and their address in the record for each product they supply and the supplier’s address changes you might forget to change the address in one or more records. By having a single record for the supplier in the Supplier table, the address needs to updating only once and will be correct for all products supplied by the vendor. This would also allow you to delete all products by a particular vendor without deleting the vendor data.
List 2 tips for determining your columns in a table.
1) Don’t include calculated data. In most cases, you should not store the result of calculations in tables. Calculations should be done each time a report is generated.
2) Store information in its smallest logical parts. For instance, do not use a single field for names rather use a field for salutation (if required), first name and last name. Addresses should be stored as Address, City, Region (or State if not international), Postal Code, Country.
Describe “Specify primary keys”
Each table should include a column or set of columns that uniquely identifies each row stored in the table such as an employee ID number or serial number. This is called the primary key and is used to associate data from multiple tables to bring the data together. You cannot have duplicate values in the primary key - you cannot use names as a primary key because names are not unique. Also, the primary key must always have a value (cannot be unassigned or unknown) and cannot be changeable. An AutoNumber column is useful as a primary key and can be named ProductID, OrderID, CustomerID for easy of use.
Describe “Create the table relationships”
To bring data in a relational database together in meaningful ways you need to define the relationships between tables.
The relationship can be one-to-many, for instance one supplier can supply any number of products. Each product will have a foreign key which is the primary key in the supplier table of the correct supplier.
A many-to-many relationship, for instance many products which can appear on many orders, requires a third table, often called a junction table. A junction table breaks down the many-to-many relationship into two one-to-many relationships. The junction table called OrderDetails contains both foreign keys OrderID and the ProductID. Neither is the primary key for this table. But together the two fields always produce a unique value for each record (we are assuming that each product is only listed once in each order). See http://officeimg.vo.msecnd.net/en-us/files/982/670/ZA001233060.gif for a visual.
When do you use a one-to-one table relationship?
If you need to record some special supplementary information, for instance about a product, that you need rarely or applies only to a few products do not record it in the main Product table because it will result in empty space for every product to which it does not apply. Instead, create a supplemental table. If the two tables have the same subjects you can probably set up the relationship using the same primary key in both tables. If the two tables have different subjects with different primary keys, choose one of the tables and insert its primary key in the other table as a foreign key.
Describe “Refine the design”
Once you have tables, field and relationships established, you should create and populate your tables with sample data and try working with the information: creating queries, adding new records, and so on. Doing this helps highlight potential problems - for example adding a column you forget during the design phase or splitting a table in two to remove duplication.
See if you can use the db to get the answers you want. Create drafts of your forms and reports and see if they show the data you expect.
List 7 items to look for when refining your database.
1) Did you forget any columns? If so, does the information belong in the existing tables or do you need to create a new table.
2) Are any columns unnecessary because they can be calculated from existing fields?
3) Are you repeatedly entering duplicate information in one of your tables? If so, you probably need to divide the table into two tables that have a one-to-many relationship.
4) Do you have tables with many fields, a limited number of records, and many empty fields in individual records? If so, think about redesigning the table so it has fewer fields and more records.
5) Has each information item been broken into its smallest useful part? If you need to report, search, sort or calculate on an item of information, put that item in its own column.
6) Does each column contain a fact about the table’s subject? If not, it belongs in a different table.
7) Are all the relationships between tables represented, either by common fields (same primary key, foreign key) or by a third table? One-to-one and one-to-many relationships require common fields, many-to-many relationships require a third table.
