Database Design Principles And Skills

Database Design Principles And Skills

AB Database Materials – Introducing Database Design Principles and Skills
Contents Study Guide 2 Part 1 Data Design: Object, Transaction, & Category Tables 3 1.1 Relations (tables) and Tuples (rows) 3 1.2 Transactions and Objects 4 1.3 Primary Keys 4 1.4 Foreign Keys, Enforcing Relationships Between Tables 5 1.5 Category Tables 6 1.6 Creating Tables and Keys in Access 7 Create a Database 7 Begin by building a customer table: 7 Data name rules to remember 7 Add a few fields (attributes) to the design for the Customers table 7 Data types likely to be used in this course 8 Add a Sales Invoices Table 8 Connect the Transaction table to the Object table using a foreign key 9 Showing Tables in a Data Diagram 10 1.7 Creating and Connecting a Category Table 11 1.8 Data vs. Logic in Databases 12 1.9 Entering Data in Access – Referential Integrity Constraints in Action 13 Add customers to the list (an object table) 13 Add terms code to the list (a category table) 13 Adding Invoices (a Transaction Table) Involved in Foreign Key Relationships 14 Practice documenting your database: 15

Study Guide
Basic RDBMS Vocabulary

· Section 1.1

· Database: a collection of data organized to serve many applications efficiently by centralizing the data and controlling redundant data

· Database management system (DBMS): software that permits an organization to centralize data, manage them efficiently, and provide access to the stored data by application programs.

· Relational databases: organize data into 2-D tables – RDBMS = Relational DBMS

· Entity a person or place or thing (object), or event (transaction) on which we store and maintain information

· a Schema describes a Logical View of a database structure not a physical view Tables, Tuples, Attributes, and Keys are key parts of a database schema

· Attributes: specific characteristics of entities. The smallest unit of data with meaning to a user; columns, fields

· Table: Each type of entity or event gets its own table (aka. a relation)

· Record: a row in a table – corresponds to one item e.g., each customer has its own record (aka. Tuple)

· Normalization: creates small, stable, flexible, and adaptive data structures for complex groups of data. It emphasizes creating separate tables for separate entities.

· Cardinality: the number of items in one table that correspond, over time, to one item in the related table

· Sections 1.3 and 1.4

· Primary Key: An attribute (or combination of attributes) that uniquely identifies a row

· Foreign Key: A field in one table that refers to the primary key of another – a lookup field

· Referential Integrity: Rules that enforce defined relationships between tables

Object, Transaction, & Category Tables

· Objects and transactions (Section 1.2)

· An Object table holds a list of a particular kind of thing: these things exists over time and can participate in many transactions, e.g., Customers, Vendors, Projects, Employees, Students, Parts, Pizza topping, etc.

· A Transaction table holds a list of a particular kind of event which we want to record, e.g., Payments, Orders, Invoices, Additions and subtractions from inventory, etc.

· Students should be able to explain what these are and give examples of object and transaction tables properly using the data base terms including: Table, Relation, Row, Record, Tuple, Attribute, & Column

· Section 1.8 – Summary data – Summary data are attributes stored in an object table that are effected by transactions

· Category tables (Section 1.5)

· A Category table is used to assign rows in a table to one of several mutually exclusive categories.

Understand the Implications of:

· Referential integrity

· The database can require that related categories and objects exist before it will record a transaction

· Reducing redundancy, the benefits of not having redundant data are:

· Saves space: Attributes don’t have to be repeated over and over in multiple transaction records.

· Reduces errors:

· Because foreign keys enforce referential integrity, users can’t accidently make two names for the same category

· Because data is not re-entered repeatedly, typing errors are minimized

· When object record details are changed, all the corresponding transactions are implicitly updated

Skills demonstrated in Access

· Create database and tables with attributes of different types

· Use recommended naming patterns for schema elements (tables, attributes, keys, etc…)

· Create foreign key relationships to support the design patterns

· Add data to tables in Access recognizing the effect of referential integrity constraints

Part 1 Data Design: Object, Transaction, & Category Tables
1.1 Relations (tables) and Tuples (rows)
By definition, a DBMS is an RDBMS (relational database management system) because it stores data in two dimensional tables. In layman’s terms, we store lists and those lists have a relatively simple form:

Customers

CustomerID

CustomerName

[…]

PostalCode

1

Advanced Bikes

75061

2

Metro Sports

94536

3

Aerobic Exercise Ltd.

93010

Table 1.1.1 – A relation (table) of customer data from the AB (Adventure Bikes Company) point of sale system. It is simply a list of data about customers. Each row describes a single customer. One row, one customer. Of course we might keep more data like the customer’s e-mail address as well.

SalesInvoices

SalesInvoiceID

CustomerID

[…]

TotalDue

TermsCode

1

2

$70,281.88

NET15

2

3

$55,957.33

NET30

3

31

$70,163.97

NET15

Table 1.1.2 – A relation (table) of invoices. Data about the related Customer is not listed here. A program would need to look up the customer information in the Customers table.

Customer SalesInvoices Report

CustomerID

CustomerName

[…]

YTD_Total

PostalCode

1

Advanced Bikes

$112,000

75061

InvoiceID

PaidDate

TotalDue

TermsCode

647

1/5/2014

$24,000.00

NET15

658

12/20/2013

$28,000.00

NET15

CustomerID

CustomerName

[…]

YTD_Total

PostalCode

3

Aerobic Exercise Ltd.

$62,550

93010

InvoiceID

PaidDate

TotalDue

TermsCode

626

2/15/2014

$800.00

NET30

Table 1.1.3 – NOT a relation of customer data. This lists sales in addition to customers. Columns do not have the same meaning in each row.

Contrast the two lists above. All the entries in any given column in Table 1.1.1 or Table 1.1.2 “mean” the same thing. In Table 1.1.3, the second column in some rows is for CustomerName while in other rows the second column holds InvoiceID. While a relational database is intended to be used to produce reports like Table 1.1.3. Table 1.1.3 is NOT a relation and would not be stored in a single table in a relational database. In our class, we use a semantic modelling approach to data design. That is, we start with objects and transaction lists and then add what we need to support important relationships. Another approach is normalization which could be said to turn lists like the one in Table 1.1.3 into a set of simpler lists like the ones in Tables 1.1.1 and 1.1.2. The two approaches work together.

1.2 Transactions and Objects
RDBMS systems manage lists (tables, relations) of objects (people, places and things) exactly the same way that they manage lists of transactions (event records). However, from a business process perspective, transaction and object lists are subject to different patterns of use and very different risks.

Consider a list of invoices created by a point of sale system (POS), such as the one used by Adventure Bikes (AB). Each invoice can have its own date and total and each invoice is charged to one customer. The organization keeps track of the same attributes (the same kinds of details) for each recorded invoice, but the values for each invoice are different. Details are recorded once for the transaction. This data is used to record sales. Once the transaction has been completed, these data are history.

In contrast, consider a list of customers for an organization which sells things. Customers have information that rarely changes e.g., mailing address, contact names but these data are used over and over to process many transactions.

Customers are “objects” and invoices are “transactions”. Table 1.2.1 contrasts transaction and object lists.

Retail Point-Of-Sale (POS) system

Objects

Transactions

Customers (for a rewards program)

Invoices

Products (items we sell)

Purchase Orders

Class Registration and Grade Tracking System

Objects

Transactions

Courses (e.g., ACTG378)

Class Sections (ACTG378 on MW at noon Spring 2015)

Students

Enrollments (grades!)

Instructors, Classrooms….

Accounts Payable (AP system)

Objects

Transactions

Vendors

Invoices (requests for payment)

Employees (who process payables and write checks)

Checks

Table 1.2.1 Exemplary objects and transactions – most organizational systems use both kinds of lists

1.3 Primary Keys
Have you ever been confused because two people have the same name? If your circle of friends includes two people named John and someone says “John invited us to dinner” you have figure out which of the John’s you know is going to cook. You may often be able to guess who the speaker meant from the context of the conversation. Or, if this comes up a lot, your friends might start calling John Wang “JW” and John Jones “Johnny”. These things become more problematic as lists grow larger. What if a new John Wang joins your group? Your friends will have to adjust their communication patterns again.

Contextual inference (figuring out which John) is a problem for a computer. Thus, when managing computerized lists it is important that each object or transaction has its own unique identifier. We call that identifier a primary key. A primary key uniquely identifies a row in a table. We tell the database management system to require that each row in the table has a different primary key value for each row. That way, the computer is always clear about which rows go together. Consider, for example, Tables 1.3.1 and 1.3.2.

Customers

CustomerID

CustomerName

[…]

PostalCode

1

Advanced Bikes

75061

2

Metro Sports

94536

3

Aerobic Exercise Ltd.

93010

Table 1.3.1 – Our short list of customers. Each customer has its own unique row and the CustomerID is the primary key.

SalesInvoices

SalesInvoiceID

CustomerID

[…]

TotalDue

TermsCode

1

2

$70,281.88

NET15

2

3

$55,957.33

NET30

3

31

$70,163.97

NET15

Table 1.3.2 – Our invoice table. If asked what the total of invoice number 3 was, you know to look in its row. If asked who the Invoice was for, you know where to look in the list of customers. The primary keys, identify the row for a particular object or transaction. In this case the SalesInvoiceID field (column) is the primary key

What kind of data should we put into a primary key attribute? Note that our primary and foreign keys are numbers. Using text such as a name as a primary key attribute is usually not a good idea for several reasons:

· Names sometimes repeat. We may find ourselves with two items for which the dame name seems appropriate. What if we have two contracts name Ji-Min Kim? We need to keep their records separate.

· Names are harder to type consistently. Maybe we would type Ji-Min Kim one time and JiMin Kim the next. How would the programming deal with such variation? Is it an accident or is it on purpose?

· A computer can store and process numbers very quickly. Text takes more space and therefore more processing. Without going deeply into the technicalities, numbers are processed fast in databases.

Is it ever ok to store text in a primary key attribute? Sure! Many organizations include letters. For example an invoice number might always begin with a capital I. OR a mnemonic might be used so people can remember a code. For example Net30 might make a good primary key value for a list of invoice terms codes.

1.4 Foreign Keys, Enforcing Relationships Between Tables
Foreign keys help us connect the data in a row of one table to the corresponding row in another table. The CustomerID field in the SalesInvoices table is a foreign key field. The value stored in the CustomerID column in the SalesInvoices table signals which row in the Customers table applies.

We can tell the RDBMS to enforce the relationship between the foreign key column and the referred-to primary key. We do this by creating a foreign key constraint. What that means is that the RDBMS would refuse to save any rows in a table with a foreign key unless there was already a corresponding record in the referred-to table. In our small example, the database would decline to save an Invoice for CustomerID 20 unless there was a row in the Customers table with a CustomerID of 20. (see pg. 138 in the text). The database will also decline to delete customer number 20 from the customer table if there is an invoice which has a CustomerID value of 20.

That’s referential integrity. If you refer to a row in another table, it has to actually exist. In our example this can prevent problems like not knowing which customer goes with an invoice. Further, it allows us to control the process. If only a manager can add a new customer, an employee cannot create an invoice for a non-existing customer because the database won’t create the invalid invoice record. This might reduce fraud and errors.

Figure 1.4.1 – A simple data diagram depicting two tables, their attributes, their primary keys, and a foreign key relationship

We used exactly the same field name (CustomerID) as primary key in Customers and as foreign key in SalesInvoices. We didn’t have to. Imagine that we had a list of employees with primary key Emp_Nmbr. If we also wanted to record who entered the invoice, who shipped the items and who got a commission for the sale, we might add three foreign key fields Entered_By, Shipped_By, and Comissioned_By. Because people look at column names, it is good practice to use exactly the same name for a primary key column and foreign key columns which refers to it. That reduces possible confusion. But the RDBMS does not require a matching name.

Let’s think about primary and foreign key examples. Create a foreign key relationship for the payment terms of an Invoice.

NET15, NET30, and 210NET30 are good values for primary keys on the payment Terms Code table. Even though there are letters included, they are short and easily typed. Both people and computers will clearly understand.

1.5 Category Tables
Transaction and Object lists store the core information needed to support most business process information systems. Most such systems use unique identifiers and foreign keys to document and track relationships. However, good database design can do much more to help create reliable and effective information systems.

Abstractly, items in lists can often appropriately and usefully be divided into mutually exclusive categories.

For example, a particular invoice received in a point of sale can have only one payment terms code. Examples of include:

· NET15: payment due in 15 days

· 210NET30: 2% discount if paid in 10 days, otherwise payment is due within 30 days

Figure 1.5.1 Category Table Example. Segments is a category table which is used with a foreign key here to assign products to different product segments.

These different categorizations are quite important. The organization can manage cash flow and or save money by carefully considering the terms when it issues an invoice. For example, we might generally offer 30 days to pay invoices, but for amounts over $50,000 offering a 2% discount to speed up payment increases cash flow.

But what if you allowed the clerk to type in the terms on each and every invoice? Is “Net 30”, the same as “30Net” or “Due Net 30”? If you sorted those different codes in order the result would not mean very much. You could try to make sure users always type it the same way but variations will occur despite best efforts.

This problem is somewhat different from, for example, recording the shipping date for the invoice. While it is true that different ShipDate values are mutually exclusive, the number of possible values for ShipDate is essentially limitless. This situation is also somewhat different from the relationship between customers and invoices. The customers is an object. It exists and has useful attributes we want to record and use in our processes. Terms code method is a singular thing. You can’t point to a code – it is just a classification. There are likely only a few different terms codes. And we don’t want to know anything about the terms code except that one is a possible category.

Classifying records into mutually exclusive categories can be accomplished in a variety of ways in an information system. But for learning in this course we are going to explore foreign keys and referential integrity to force records for categorization. Consider Figure 1.5.1. Do you see how the foreign key constraint would make it so that only certain terms code values can be stored? If we limited new entries in the TermsCodes table, each invoice would be forced into a consistent set of categories. Configuring the database to enforce referential integrity would prevent the user from entering any values in the SalesInvoices table other than those listed in the TermsCode table. If at some point the company wants to offer a new form of payment they would simply add a new record in the TermsCode table. Important note: Valid terms and their identifying codes should be determined at a management level and access to that part of the database needs to be restricted. Relational Database Management systems have features for controlling that. Other examples:

· A Pizza table might have a foreign key “Size” to a PizzaSize table with three entries: small, medium, and large.

· A sales invoice might be categorized as retail, wholesale, or internal to ensure taxes are correctly collected or income statement numbers are properly stated.

1.6 Creating Tables and Keys in Access
Tables, attributes, primary keys, foreign keys, objects, transactions, and categories should all be somewhat familiar at this point. Let’s see how we make these tables in Access. Pay attention, you will need to do this in Part 1 of the database exercise and in your assignments.

Create a Database
· Open MS Access

· Choose File, New, Blank desktop database

· Decide where to save your new database – preferable in your area on the network. A folder on your Z: drive perhaps? You can click the browsing icon to choose a place.

· You can give it a name when you save it. “Part1Practice”

· Click Create

· Access assumes you want to make a new table and provides a temporary name for the table “Table1”

Begin by building a customer table:
· Click on the arrow beneath the View icon on the ribbon

· Choose design view

· When asked for a name, type ‘Customers’ and click ok

· This brings up the design view for the table where you can described the attributes for the table you are creating

Data name rules to remember
When you design databases in this course please keep the following rules in mind. Failing to do so will result in lower scores. These sorts of rules vary from organization to organization but here are some things to consider:

· Within an organization, consistency in the documentation of different systems promotes understanding and increases efficiency

· Including spaces in data names can create problems when writing programs that use the data – so don’t do it

· Capitalizing new words increases the readability of the data names, e.g., FirstName, LastName, CustomerID

· Choose meaningful names so that readers will quickly get a good idea of what is stored

· Be consistent. If one table is plural e.g., vendors, all should be plural i.e., NOT vendor. Developers and users can more quickly and accurately identify items without having to verify such details

Add a few fields (attributes) to the design for the Customers table
· By default, Access creates a field called ID of the type AutoNumber which has been designated as the primary key. Do you see the little key that appears next to the name in the design view? That means this is the primary key.

This works well with our previous discussion:

· AutoNumber fields are numeric (not text) and can therefore be efficiently procesed.

· We want every table to have a primary key so the system is helping us out.

· Whenever a new record is added to the table, Access generates the next available number as a value for this field. That can save a user time because they might otherwise have trouble figuring out what unique numbers are available.

· Change the name of the primary key field from ID to Customer_ID

· Add more fields as shown. Take special note of the Data Type of each field.

· Short text fields can hold up to 255 characters

· Currency fields are appropriate for fields that contain dollar values

Data types likely to be used in this course
· Short Text for most text fields such as names or addresses. Smaller field sizes help keep databases running fast and reduce the size of the files the database system has to store and process.

· Currency works well for dollar amounts

· Date/Time fields store time stamps which record both a date and a time. They can be tricky when you really only want to consider the date without the time. Deal with this difference if you have to but complete mastery of this is beyond the scope of this exercise.

· Number

· Note that the Field Size for a number can also be adjusted in a lower part of the design panel

· Field Size ‘Long Integer’ is appropriate for foreign key fields that point to Autonumber fields

· Long integers are also appropriate for many counts fields such as number of items in stock

· Decimal places can also be adjusted for Double and Decimal Field Sizes – sometimes quantities should be stored using decimal values

· Long Integers can hold values up to a bit over 2 billion

· Short Integers hold numbers up to 32,000

· AutoNumber a special kind of long integer where the database assigns values to new rows – but realize that you can’t ever change the values. Sometimes students become frustrated whten they have “lost” a number by adding and later deleting it.

Most all our work can usually be nicely done using those but sometimes other types are needed:

· Long Text fields may be appropriate for descriptions or other special cases

· Yes/No fields are appropraite for some tables but using them in queries can require special efforts

Key idea:

· DBMS and other computer programs have to consider data type as they manage and store data. While we don’t need to become data type experts here you should be comfortable realizing that different data types are needed and be able to list several examplary types.

Add a Sales Invoices Table
There are several ways to save a table. I suggest right clicking on the tab which names the table and choosing close. It will ask you if you want to save changes. Say Yes!

Hint: Whenever you are done working with a table, you should save it and close it. If you don’t you may find yourself getting error messages about locked data. If you just get into the habit of closing them you will avoid some frustration.

The Customers table was an object table. Now let’s add a transaction table.

· Go to the Create tab on the ribbon and doubleclick on Table

· Go to the Home tab and go into Design View (remember? The down arrow below the View icon as before)

· Name the table SalesInvoices when you save it

· Change the Primary Key name to SalesInvoiceID and note that it is an AutoNumber field – that is fine

· Click into the row below the SalesInvoiceID to create a field called CustomerID

· Note that, as in the picture, the Field Size should be set to Long Integer so that it will match up with an AutoNumber field. Some students lose time on their projects because they forget this step. Key idea: The two fields in a foreign key constraint need to match so that the database can efficiently enforce referential integrity

Add the other fields as shown:

For a Point of Sales system to work, we will need to know a lot more than this about each invoice. But it is a start.

Connect the Transaction table to the Object table using a foreign key
Often each object in a list of objects participates in many listed transactions. For example, our company may buy supplies or tools from Home Depot every week. To ensure that we only enter invoices for customers in the Customers table, we can create a foreign key constraint. We call it a constraint because it constrains what is allowed to go into the database.

Novice database designers often struggle to correctly formulate a foreign key.

Do we store the Invoice number in the customer table or the customer number in the invoice table?

If you get it backwards, you won’t be able to enter data sensibly.

While each entered invoice is paid by one customer, each customer can be involved in many different invoices.

So, if we tried to put the invoice number into the customer list, which invoice number would we use? The first one? The latest one? Some students have incorrectly tried to make a list in a field using commas or something. For example they might put a text field in the customer table and list the invoices there 658, 659, 801 etc.

That’s not how we do it in relational databases. Rather we put the customer number in the invoice table. It works because each invoice has only one customer. No problem.

Showing Tables in a Data Diagram
Access lets us click and drag to establish a foreign key in a database.

· First, Close the Invoices table. Remember, closing first will avoid error messages later.

· Go to the DATABASE TOOLS tab and click on Relationships

· Add both the Customers and SalesInvoices tables to the diagram. There are several ways to do this.

· When you have selected them correctly it will look something like this:

· Click on the CustomerID in the SalesInvoices table and “drag it” onto the CustomerID on the Customers table

· That brings up another menu

· Check the Enforce Referential Integrity box so that the DBMS will know to require that all invoices must be associated with a valid Customer record

· Click Create

·

· This adds a line to the diagram

· Note that the boxes have been resized and moved:

· The line is clear and not hidden under something else

· The boxes are big enough to show all the field names but no bigger

· It’s that easy to create a foreign key

Note the terminology on the Edit Relationships menu. The relationship type is One-To-Many.

This means two things:

1. For each item in the SalesInvoices table there is only one corresponding entry in the Customers table.

2. For each item in the Customers table there can be many entries in the SalesInvoices table.

We call this relationship, how many items in one for each item in the other, Cardinality.

Detailed cardinality rules can be applied in some database environments. For example you could specify 1:3 meaning that there are always exactly three entries in one table for each in the other. Or there can be 0 to as many as 5. For our purposes we will keep it simple. Relationships will be 1:1, 1:M, or M:M. 1:1 would mean there can be only one item in one list for each item in the other. 1 to many means there CAN BE (but may not be) many records in one table for each entry in the other. We will talk about M:M (Many-to-many) later.

The cardinality of most object/transaction relationships and all Category table relationships is one-to-many. A one-to-many relationship can be supported by a foreign key constraint in a RDBMS.

1.7 Creating and Connecting a Category Table
We already discussed the purpose of category tables: using the Category Table design pattern assigns each record in one table into one of several mutually exclusive categories. In the old days, car radios had buttons set up so that only one could be pressed at any time. If you press another, the previous choice was automatically un-pressed. That’s approximately what a category table accomplishes.

Let’s make one in Access:

· Go to the Create tab on the ribbon and doubleclick on Table

· Go to the Home tab and go into Design View

· Name the table TermsCode when you save it

· This time we will have two fields as shown

· Take note that the primary key this time is a Short Text field

· Think about it.

· Would a number work instead?

· Why might we like an alphanumeric (letters and numbers) code instead?

· Save and close the TermsCode table

· Next, add TermsCodeID field to the SalesInvoices Table. What data type should it be? (Short Text). Why? (Because it has to match the field we are going to link it to)

· Click on the SalesInvoices table where it shows on the left and go into design view. There are several ways to get there.

· Now make a foreign key relationship

· Go to the DATABASE TOOLS tab and choose Relationships

· Add the TermsCode table to the diagram. There are several ways to do it, dragging them on from the list on the left or right clicking on the diagram and choosing Show Table are two good ones

· Drag the foreign key field (do you know which one that is?) onto the corresponding primary key field

· Click to check the Enforce Referential Integrity box on the Edit Relationships popup menu

· Click Create then arrange things neatly. It should look something like this:

· Close the diagram

This configures the DBMS so that it can enforce the mutually exclusive categorization of invoices. The category list is controlled by the items in the terms code list.

You may have noticed that as far as the database is concerned there is no difference between the foreign key for the object/transaction relationship and the category table relationship. It is 1:M (one-to-many) in either case.

Also, please note that using a separate table and a foreign key is only one way an information system might enforce mutually exclusive categories but time does not allow for us to compare various methods. The point is for you to understand mutually exclusive categories (a common information phenomena) and to practice applying foreign keys.

1.8 Data vs. Logic in Databases
In today’s information system cluttered world, the difference between data and programming logic is often blurred in the mind of the average user. People naturally conceive of their interactions by thinking of the tools they use. For example, a student might say “My order is stored on a web page”. Excel also teaches us to mix up data (values we type into cells) and formulas (logic) which perform computations on that data. Of course all of that is a matter of perspective. There is nothing wrong with these notions. But, to understand and choose wisely, information system professionals and people who need to manage information system supported processes should be able to separate data from programming logic in their minds even though the two get all mixed together in practical use.

Microsoft Access, in particular, lets us store data (it is an RDBMS) but it also supports report writing, computations, and user interactions. WE WILL ONLY BE USING ACCESS TO STORE AND RETRIEVE DATA IN THIS COURSE. We will NOT be adding calculated fields in the database, we will NOT be creating nicely formatted reports using the Access interface, and we will NOT be building forms to enter or process data. Students have enough to think about as they learn to nicely organize data into useful tables and learn to effectively retrieve data from those tables using SQL (structured Query Language.

Let’s use a data=shoes analogy to think about our database work. We will be building shelves to hold our shoes (data), putting pairs of shoes (values) on those shelves, and retrieving lists using SQL. But we will NOT be teaching the database to automatically polish the shoes, remember how many shoes there are, or even move shoes from one shelf to another. We will use SQL to generate useful lists. We might ask it “How many red shoes do we have” or ask it to “make a list of shoes by brand”. The answers will be returned but they won’t be stored in the database. We will even carefully construct the shelves so that only certain kinds of shoes will fit (data types). We will use one semi-computational feature – foreign keys. That might be like teaching the shelves not to accept shoes that still have mud on them. The point of this analogy is to get students to separate how data is stored from how it is processed. The two are related of course, but they are not the same thing.

Many systems store subtotals and other computed fields in tables in a database. In this course we will work with what we call “Summary Data” fields. A summary data field is a field in an object table whose value is affected by related transactions. Summary fields are commonly used in many business systems. In this course we will design attributes to store summary data and we will practice queries that verify the accuracy of summary data but we will not be implementing logic to update the values of summary fields. That kind of operation would be covered in software development courses. The YTDPurchases field in the Customers table is an example of summary data. We will make a place to store the summary data but we will not create logic that automatically calculates the values.

The good news is that developing data design and SQL skills builds a foundation for a vast number of future efforts and jobs. If you understand good data design, learning to use other features in Access is relatively easy. Understanding SQL and primary and foreign keys is useful no matter what RDBMS you use: Oracle, MSSQL, MySQL and others. Even non-relational data such as the kind stored in cloud systems like Salesforce or big data management tools such as Hadoop employ similar principles. These are even useful for people who won’t be developers. Good data design can make your spreadsheets more useful and less error prone and application programs such as the University’s Banner system and other ERP tools often have built in query tools in which queries can be formed. The conceptual work we do here will hold up well in a variety of circumstances.

So, let’s practice adding data to our tables. Along the way, we will illustrate how foreign keys and referential integrity let an RDBMS help an organization generate valid data.

1.9 Entering Data in Access – Referential Integrity Constraints in Action
Let’s add some data to our tables. We cannot begin with the Invoices table. Do you see why?

The two foreign keys we created (with Enforce Referential Integrity turned on) forbid entry of Invoices unless there is a corresponding customer and a corresponding terms code. Thus the database enforces a business rule. This rule addresses two important risks: invoices for fake customers could be part of a fraud scheme and invoices with unrecognized terms codes might not be properly processed resulting in extra costs or forgone discounts.

Add customers to the list (an object table)
· Double click on the Customers table on the left side of the screen

· This usually opens the database in Datasheet View

· Notice that the Customers says (New). You can’t enter a customer number because it is an AutoNumber field so the database automatically assigns the next unique number to a new row

· Enter “Bike World ” as the CustomerName, and “1211 Cruise Pl” as the Address.

· Do not enter any value in YTDPurchases. This is summary data field. We are NOT going to teach the database to calculate this value. We assume the POS software will keep this field updated.

· Once you click out of the new row you will see that Access assigns CustomerID number 1 to the row.

· Add another row for customer name “Advanced Bike Components”, address “600 Canyon Rd”

· Oops! The Sales Manager informed us this is a Vendor and not a customer, so delete that record

· Click on the little square just to the left of the CustomerID, that will highlight the row

· Press the delete key

· Add another row for customer name “Metro Sports”, address “877 Main St”

· Note that there is no CustomerID = 2, that’s because Access doesn’t reuse the number it had assigned to Advanced Bike Components. This should not be a problem – don’t let a skipped number bother you!

Add terms code to the list (a category table)
Do the same thing to add two rows to the TermsCode table.

Remember, the primary key for this table is NOT AutoNumber so you have to enter the codes.

· Add another row: TermsCodeID = “NET30”, Description = “Net 30”

· Add another row: TermsCodeID = “NET30”, Description = “A second Net 30”

· When you click into the next row this time, you will get an error message. Do you know why? Because we have defined TermsCodeID as a primary key, only unique values can be entered: two rows cannot have the same value.

· Change the TermsCodeID to “NET15” and the Description = “Net 15”

· Add another row: TermsCodeID = “210NET30”, Description = “2% discount 10 days Net 30”

Hint: Close your tables when you finish.

Adding Invoices (a Transaction Table) Involved in Foreign Key Relationships
Go into Datasheet view for the SalesInvoices table. As with the Customers table, there is an AutoNumber key field on this table, so don’t enter a value there.

· Enter the first and second row shown below. That should work fine.

· Try changing the CustomerID of the first invoice to the number 5. If you then try to move on to the next row, Access tries to save the record in the table. But it can’t because there is no Customer5. Change it to a valid customer number and go on.

· Enter another row this time do not enter a TermsCodeID. This actually works. For text fields despite the referential integrity constraint. You can save a row with no value, but you can’t enter row with an invalid value. For example, try using “XYX” as a code. That will not work. Neither will “A”, or “b”, or any other value not in TermsCode table

· Save and close the table

· Assume our organization does not want this field left blank (this is actually a good idea in many ways as we will see in a later exercise).

· Go back into datasheet view in the TermsCode table

· Add another row: TermsCodeID = “NA”, Description = “No terms specified”

· Save and close the table

· Go into Design View for the Invoices table and instruct Access to require a value for the TermsCodeID as shown at the bottom of the picture

· Save the table then go into Datasheet View for the Invoices table

· You could now change TermsCodeID to NA but you cannot leave it blank. Try it.

Practice documenting your database:
Create a data diagram with all three tables as we did before for pairs of tables. Make it neat and avoid crossing lines.

Use Snipping tool. (Start button, in the search window type “snipping”), then click the snipping tool.

Make a New snip and select a neat part of the screen. You can then paste the image into a word document.

Then, paste the data from the three tables into the Word document as well. For each table, in Datasheet view, Click the triangle as shown below and then paste the data into the Word document. It makes a Table in Word rather than pasting the data as an image. That’s good!

This is what it should look like when you paste it into your Word document:

SalesInvoices

SalesInvoiceID

CustomerID

InvoiceDate

TotalDue

TermsCodeID

1

3

2/7/2014

$500.00

NET30

2

1

2/2/2014

$745.00

210NET30

3

1

2/9/2014

$324.00

NA

The post Database Design Principles And Skills appeared first on Pro-Dissertation.

Write my Essay. Premium essay writing services is the ideal place for homework help or essay writing service. if you are looking for affordable, high quality & non-plagiarized papers, click on the button below to place your order. Provide us with the instructions and one of our writers will deliver a unique, no plagiarism, and professional paper.

Get help with your toughest assignments and get them solved by a Reliable Custom Papers Writing Company. Save time, money and get quality papers. Buying an excellent plagiarism-free paper is a piece of cake!

All our papers are written from scratch. We can cover any assignment/essay in your field of study.