XPXPXPClass Objectives – 9/10 and 9/12

• Learn how to design a small database Understand the goals of a database Understand the terminology of database design Learn how to read a database design diagram Know how to create a database design diagram

• Implement a small custom database in MS Access Take a database design diagram and implement the

resulting database in MS Access Create tables Create attributes with correct data types Create relationships and referential integrity constraints

1

XPXPXPHelp available

• Your books!!!!• Online help via Access and Excel• Google (or other favorite search engine)• Teaching Assistant (Robert Dittmer)

Office hours: Monday andWednesday 10AM-noon Email: rdittmer@unr.edu

• Professor (Dana Edberg) Office hours: TR 1:15-2:15PM, 4-5PM Email: dte@unr.eud

2

XPXPXPQuestions for a systems developer

• What am I trying to accomplish by using a computer? Are you: Keeping track of lots of information for organization, customer,

government, investor? Making processes more efficient requiring less labor? Facilitating decision making? Presenting information in a more readable, attractive format?

• What is the best way to accomplish your goals? Database management system? Spreadsheet? Web front end? Custom developed programs? Combination?

3

XPXPXPBook Example: Belmont Landscapes

Company provides landscape architecture services including analyzing sites, developing plans and performing construction work.

Wants to have a way to keep track of the work that will be done for customers (“contracts”) and then have a way to charge those customers (“invoices”) once the work has been completed.

4

Spreadsheet or Database for Belmont Landscapes?

5

Decision Issue

Spreadsheet (Excel)

Database (Access)

Ability to maintain accurate data

Limited. Completely dependent on user for accurate input.

Excellent. Many filters/constraints available to protect data accuracy.

Ease of showing data in different formats

Limited. Can make small color changes to basic spreadsheet.

Excellent. Very flexible. Data entered once can be viewed in many different formats.

Ease of doing calculations

Excellent. Statistics, financial functions available.

Limited. Can do calculations, but only basic math.

Difficulty of learning/using product

Excellent. Easy to learn. Difficult to learn.

Necessity for pre-planning

Not much planning required.

Must plan/design the database in advance.

XPXPXPWhy do we store data in a database?

• To protect the “integrity” of the data. Make data accurate. Reduce data redundancy.

• To make data more accessible. Enhance flexibility of data access methods. Improve speed of data access.

• To make an application more adaptable. Provide more flexibility in application development. Decrease dependence on a given visualization method.

6

Examine the contract “spreadsheet”

• Review data content.• What is the application? What is the purpose of the

stored data?• Is any of the data redundant? • Why might redundant data be a problem?

7

8

XPXPXPVocabulary lesson - 1

• Table: A two-dimensional database object used to store data.

• Row: One “entry” of data within the table. Must have a primary key that has a different value than all other rows of data within the table

• Column: A field used to store data. Must have a single data type.

• Cell: An intersection of a row and column. Can only have one data type and one value.

9

XPXPXPVocabulary lesson - 2

• Entity (when implemented it is called a “table”): A person, place, or thing about which we store data. Example is a Customer for Belmont Landscapes.

• Entity instance(also called a “record” or “row”): One instance of an entity that includes all data stored about that instance. Example is “Anthony Rodriquez” for Belmont.

• Attribute (also called a “field” or “column”): A characteristic of the entity about which we store data. Examples are Company, FirstName, Lastname for a Customer entity.

10

XPXPXPProcess for Designing Databases

• Identify all the fields needed to produce the required information Divide each piece of data into its smallest useful part

Example 1: Break up a name into first, last, initial

Example 2: Break up an address into street, city, state, zip

• Group related fields into tables. Start with the “strong” tables.

• Use an entity-relationship diagram (ERD) to depict the design

• Determine each table’s primary key

• Identify how the tables are related (or if they are related) Include a common field in related tables (foreign key)

• Determine the properties of each field Type of data: Text, date, number, etc.

Size of data

Name of data11

Contract Data

12

Group (sub-divide) the customer data

13

Group (sub-divide) the invoice data

14

XPXPXPWhat is a primary key?

• Definition• Examples• “Natural” vs. “Surrogate”

15

Define keys and relationships

16

What does it look like in Access?

17

XPXPXPWhat is a foreign key?

• Definition• Example• Depiction on diagrams

18

XPXPXPWhat is referential integrity?

• Definition: A constraint to enforce consistency between parent and child tables in a database.

• “Constraint”: A limit; a set of pre-defined rules.• Conceptual example: Contract and customer.• Implementation in Access example

Create after a table has been created through the relationship diagram.

Create while the table is being created through the use of a Lookup wizard.

19