1 / 19

Class Objectives – 9/10 and 9/12

Class 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

sybil
Download Presentation

Class Objectives – 9/10 and 9/12

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Class 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

  2. Help 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

  3. Questions 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?

  4. Book 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.

  5. Spreadsheet or Database for Belmont Landscapes?

  6. Why 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.

  7. 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?

  8. Vocabulary 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. Vocabulary 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. Process 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 data

  11. Contract Data

  12. Group (sub-divide) the customer data

  13. Group (sub-divide) the invoice data

  14. What is a primary key? • Definition • Examples • “Natural” vs. “Surrogate”

  15. Define keys and relationships

  16. What does it look like in Access?

  17. What is a foreign key? • Definition • Example • Depiction on diagrams

  18. What 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.

More Related