1 / 25

CIS 218

CIS 218. Introduction to Databases. What is a Database?. A collection of related information Examples. Key Terms

giolla
Download Presentation

CIS 218

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. CIS 218 Introduction to Databases

  2. What is a Database? • A collection of related information • Examples Key Terms Database – An organized structure, created by a relational database management system (DBMS), to allow users to store, retrieve, and report on data, usually data about a business, or a business process.

  3. Where are Databases Used? Why use a Database? • Schools • Stores • Restaurants • ??? • Accessibility • Flexibility • Efficiency • Integrity • Usability

  4. What are the Components of a Database System? • Forms • Reports • Queries

  5. What is a DBMS? • Database Management System • Functions • Create databases and tables • Read database data • Modify database data (insert, update, delete) • Enforce rules, e.g. validation • Control concurrency • Provide security • Examples • MySQL, Oracle, MS Access, MS SQL, Postgres… Key Terms DBMS – Database Management System, which stores data in related tables.

  6. What happens if Matt’s Adviser is changed to Valdez? Problems with Lists What happens if we delete Chip Marino? What happens if the Admin for Accounting changes to Joe? What do all of these blanks mean? Key Terms Redundancy – the duplication of information

  7. A Relational Database • A relational database breaks a list into several tables • One table for each theme or entity • A table is made up of columns & rows • The previous list may be divided into: • STUDENT table • ADVISER table • DEPARTMENT table

  8. columns Ummm… but now, how do we know who Lia’s adviser is? rows

  9. Putting the Pieces Together Again • A relational database breaks a list into several tables • Tables are linked by sharing a column in common

  10. Linking Columns ForeignKey Who is Lia’sadviser? Primary Key

  11. Sounds Like More Work! • A relational database is more complex than a list • So why do it? • Minimizes redundancy • Preserves complex relationships • Allows for partial data • Provides a foundation for forms and reports

  12. A Relational Database Solves the Problems of Lists Deleted Student row – No Adviser data lost Changed Advisor – No inconsistent data Inserted Advisor – No Student data required

  13. The Design Process • Define mission statement & objectives • Analyze the current database • Define tables and relationships • Determine business rules

  14. Mission • Mission statement • What is the purpose of the database? • Should be succinct • Mission objectives • What tasks can users perform against the database?

  15. Mission Statement: The purpose of the New Starz Talent Agency database is to maintain the data we generate, and to supply information that supports the engagement services we provide to our entertainers. Mission Statement: The purpose of the Whatcom County Hearing Examiner’s database is to maintain the data the examiner’s office uses to make decisions on land-use requests submitted by citizens of Whatcom County. • Mission Objectives: • Maintain complete entertainer information. • Track all engagements. • Report on monthly and annual sales. Mission Statement: The purpose of Mike’s Bikes database is to maintain the data we need to support our retail sales business and our customer-service operations. • Mission Objectives: • Keep track of all land-use requests and decisions. • Report on requests, decisions, and time-to-process. • Mission Objectives: • Maintain complete inventory information. • Maintain complete customer information. • Maintain complete supplier information. • Maintain complete employee information. • Track all customer sales.

  16. Try It • In groups of two, imagine that you manage the cafeteria at HCC. • How might a database help you in your business? • Write a Mission Statement • Write at least three Mission Objectives

  17. Analysis • Analyze the current database • Study current forms and reports • Interview users and management • Compile an initial list of fields (columns)

  18. Define Tables and Relationships • Make sure each table represents a single theme • Define keys • Identify relationships Sample ER Diagram for the NewStarz Database

  19. In case that looked too easy…

  20. Business Rules • Document limitations • The New Starz Talent Agency database will not be used to track Employee information. • Define requirements • Users should be able to search entertainers by talent area. • Enforce validation • Engagement dates must be on or after the current date.

  21. What is SQL? • Structured Query Language • Used to interact with a database • Create, modify and delete tables • View the data in a table • Add new rows to tables • Modify rows in tables • Delete rows from tables • Lots of other stuff… • Standardized (mostly) across DBMSs

  22. Viewing Data Table Database: hcc Table: student SELECT birthdate FROM student WHERE last = "Simpson"; SQL Statement

  23. Inserting a Row INSERT INTO student VALUES ('343-66-3434', 'Spuckler', 'Cletus', '1987-12-27', 3.0, 2);

  24. Modifying a Row UPDATE student SET advisor = 3 WHERE sid = '343-66-3434';

  25. Deleting a Row DELETE FROM studentWHERE sid = '343-66-3434';

More Related