Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
CSE 2111 Modeling and Problem Solving with Spreadsheets and Databases
PART 1: DESIGNING A DATABASE
Read Pages 2-7 of this Lab. Starting on page 8, you will complete the steps to create a database, using the files you download from Simnet.
WHAT IS A DATABASE?
A database is used to store information about a person, place, or thing. Almost all information retrieved from a computer is stored in a database. There are several different types of databases or what we call database models. One of the database models used is called the relational database model. This model stores information in files, tables, records, and fields.
STEPS WHEN CREATING A DATABASE
There are several steps that should be taken when creating a new database. These steps should usually be in the following order, although sometimes that may not be the case.
Step 1 – Design and Document the database
The first four steps when creating a database entail documenting and designing the database. This document will serve as a map for creating the actual database itself.
1. Decide what information you will store in the database and document it on paper*.
2. Create a layout of the database and document it on paper*.
3. Normalize the database and document it by using software for that purpose.
4. Create a Relationship Diagram on paper*.
a. Identify the Tables, Relationships, Primary Keys and Foreign Keys.
*On Paper means to design and document the database structure (using software for that purpose or even MS Word) before creating the database.
Step 2 – Create the database using MS Office Access or any other database software.
The final steps entail the creation and setup of the database using the database software. (In this lab we will be using MS Access to create the database.)
5. Create the database structure in MS Access.
6. Create the table relationships
7. Enforce Referential Integrity in MS Access.
8. Populate the MS Access database.
a. This entails typing the information into the newly created database.
STEP 1 – DECIDE WHAT INFORMATION YOU WILL STORE IN THE DATABASE.
One of the first steps in creating a database is to decide what information you will store in the database. For example, let’s say you have started a small business using MS Word to type research papers for the students at your school. You want to create a database to store all your charges and payments so you can collect your fees in a timely manner. What information do you need to store and how will you make the information for each person unique? For the purpose of this example, we will only store the minimum amount of information needed to keep track of your charges and payments received. Figure 1.1 shows the basic information needed to create this database.
This database is a very simplistic one. Most databases you create will be much more complex. Therefore, when you are collecting the information needed for your database, you should always consult the people who actually do the work. You must know the business in order to create the database, so involve the people who do the business!
STEP 2 – CREATE THE LAYOUT OF YOUR DATABASE USING SOFTWARE FOR THAT PURPOSE
Once you have decided the information to be included in your database, you must now create the layout of the database. The layout consists of the tables, records, fields, field types, and field properties that will be included in your database. Basically, this process of designing a solid and accurate database is called Normalization. In order to normalize the database, you must group related fields into tables, while eliminating any duplicate data in the database except the primary keys and foreign keys which will relate the tables together. You also must create a primary key for most of the tables in your database. What is a primary key? A primary key is the one field that will make each record unique in your table. (Note: You can create a primary key from more than one field, but that is beyond the scope of this book.) Have you ever ordered a pizza? What is the first question usually asked when you call…Could I have your home phone number please? It’s the home phone number that makes you unique as a Client. Your social security number makes you unique in the United States, and your Student ID makes you unique at most Universities. We will create a unique field for each of our clients. Figure 1.2 displays the table I have created from the information I have decided I need to store in Figure 1.1. I have created one table called Client, which will store all of the personal information needed for each client, and all the charges and payments for each client. Each piece of this information is called a field, and all of the fields combined for one person constitute one record. Hence, if I have 3 clients in the table in Figure 1.2, I have 3 records and 39 fields. This database has not been normalized.
Why not just create one big table like the one above and call it a day? The problem is that every time a client is charged, or a payment is made, all of the information needs to be re-input for each client. This causes quite a bit of information to be duplicated and could also leave room for inaccurate information. What if the first record has our Client listed as Rebecca Simpson, and the 2nd record has her listed as Becky Simpson? Now our information is incorrect. Are Rebecca Simpson and Becky Simpson the same people? What if we have a Rebecca Simpson at PO Box 189 and a Rebecca Simpson at PO Box 198? Easy, you say, just look at the database to see if the information looks the same. That is fine with a small database, but what if you have a database with thousands of records? That would be a waste of time. Another problem this creates is wasted disk space because we are duplicating information over and over.
STEP 3 -NORMALIZE THE DATABASE
As previously stated, in order to normalize the database, you must group related fields into tables while eliminating any duplicate data in the database except the primary keys and foreign keys which will relate the tables together. You also must create a primary key for most of the tables in your database. Figure 1.3 shows the database normalized using documentation software. (There are many different forms of Normalization. This book will not detail the different forms.) This handout will not detail the different forms of normalization.)
There are four tables documented.
1. Client Table: Stores all the personal information about the client. Each client will only need one record in this table; thus duplication of information and inaccuracy has been reduced drastically. Notice the primary key is ClientID. This is the field we will use to make each record unique in the Client table.
2. Charges Table: This table stores all information on client charges. Because we may have many charges for one client, we will not have a primary key on this table. However, we will need a field that tells us what charge is related to what client. For this we will use the ClientID field. Therefore, for every Client in the Client table, we can have zero to many charges for that Client in the Charges table.
3. Payments Table: This table stores all information on client payments. Because we may have many payments for one client, we will not have a primary key on this table. However, we will need a field that tells us what payment is related to what client. For this we will use the ClientID field. Therefore, for every Client in the Client table, we can have zero to many payments for that Client in the Payments table.
4. PaymentMethod Table: This table stores all information on payment methods. The client can pay by Visa, Mastercard, Purchase Order (PO), Check, or Cash. We will document this on every payment.
STEP 4 - CREATE A RELATIONSHIP DIAGRAM TO IDENTIFY THE TABLES, RELATIONSHIPS, PRIMARY KEYS, AND THE FOREIGN KEYS
The tables have been created, but how can the database know what record in the Client table relates to records in the Charges and Payments table. In other words, how can I make sure that I can pull the correct charges and payments information for each client? This is done by creating relationships between the tables; hence we will create a relational database. We will depict this on paper (or documentation software) by creating a Relationship Diagram as shown in Figure 1.4.
Figure 1.4
It is obvious the field which makes each record in the Client table unique is the ClientID field, but what about the Charges and Payments tables? These two tables can have many charges and payments from one client; therefore, they will not have a primary key. But how do we relate the records in each table. We use the ClientID field on the Charges table to relate each charge to one client on the client table, and we use the ClientID field on the Payments table to relate each payment to one client on the Client table. Therefore, there is a one-to-many relationship between the Client table and the Charges table, and there is a one-to-many relationship between the Client table and the Payments table. The ClientID field on the Charges table is called a Foreign Key, because it will relate all the records on the Client table which use ClientID as its Primary Key. What would be the Foreign Key on the Payments table that will relate the Payments and Client table? It will be the ClientID on the Payments table.