Chapter 9

1. Chapter 9 Database Management Discovering Computers Fundamental

2. Databases • My first suggestion is to read through the text book. • Lot’s of information there for you!!!

3. Database Definition • A database is a collection of data organized in a manner that allows access, retrieval, and use of that data.

4. Database Confusion • When reading the textbook, a database might look like a spreadsheet. It is NOT a spreadsheet. • The word “database” is a generic term. Similar to the word “file” • Files can be created with a word processor or a spreadsheet. A specific word processor software might be Microsoft Word. A specific spreadsheet software might be Microsoft Excel • A database is created using a DBMS tool. A specific DMBS is Oracle, or Sybase, or Microsoft Access!

5. DBMS • Microsoft Access is a DBMS that you can use on your computer. It comes in Office 2003. It’s relatively easy to learn and use. • Oracle and Sybase are two companies that make a DBMS. Their DBMS applications are for larger computers and larger amounts of data. They are more complicated to learn. Most companies use one of these two DBMS applications.

6. Database Example • What if HCC wanted to keep a list of ALL of its students? They don’t want to type up the list in a word processor or spreadsheet. If they did that, then they would have to look through ALL the students just to find one student. HCC probably has millions of students.

7. More on Database Example • This is when they would use a database. First, they would pick a specific tool (DBMS) to create the database. They might pick Microsoft Access. • This tool would guide them in entering all their data. • The DBMS would then save this information in a “special” way, that only the DBMS program could understand.

8. Database Queries • If HCC wanted to look up the information for a particular student, they would HAVE to use the DBMS tool. They would start the DBMS tool, tell it what database and table to look at (the one with the students), and then they would make a “query”. That is, they would make a request to retrieve information in the table. • The DBMS would display the information.

9. More on Query • For example, you could use your DBMS to say “hey, go get me all the students who live in Katy”. Or maybe you could say “hey, go get me all the students who are in ITSC1301”. • That is a query. However, you wouldn’t just say “Hey … go get me this”, you would have to phrase it in a way that the DBMS understands.

10. Queries • In the book they talk about Query-By-Example and Query Languages • Query-By-Example • Making a query using the menus of the DBMS • Query Languages • A special language you use to “talk” to the database to make a query • SQL is a common query language p.348 • SQL is used in most relational databases, regardless of the DBMS you purchased.

11. Quick Review • Think of the DMBS as a software program with elaborate menus • Some menus let you enter data • Some menus let you retrieve data • Some menus let you change data • All this information gets saved in database files. You need the DBMS tool to access those files!!

12. Not as easy as it sounds • Database problems: • More expensive • More complicated to use • Require computers with more hardware, memory and processing power (generally) • I personally wouldn’t use a database to hold all of my students’ information since I don’t have that many (between 100-200) • A database would be more trouble than it’s worth in my case

13. Organizing Data • Data isn’t kept in a chart, but the data is organized in that manner. • When you enter data, you generally enter a record at a time. • A record is a group of related fields (think of it as a row in a chart) • Each column is really referred to as a field. • A TABLE is a collection of records (think of it as a chart).

14. Possible HCC Tables • If HCC had a database with students and their grades, they could set it up in the following tables: • Admission’s department might have a table with students, ids, addresses, age, etc… • Math department might have a table with students, grades, math courses • Library might have a table with students and which books they have checked out

15. Redundancy • What if the Math department needed the student addresses? Well, they could keep the students’ addresses in their table, but then the addresses would also be in the science department’s tables, and the HCC admission’s tables, etc… • Instead, the addresses only have to be in one table (the HCC admission table). If the math department wants to get the address of a student, it can just refer to the other table. • This way the same data doesn’t have to be kept in 3 different places!!

16. More on Redundancy • Using a DBMS and a query, it is very easy to get information from different tables: • Query: hey, get the students from the math table who are in Algebra, and at the same time, pull their addresses from the Admissions table.

17. DBA • There is an “art” in selecting how to layout the databases (how many tables, and what to include in each table)!! • A DBA is a database administrator. • They set up the database and keep it functioning. • They use programming • If you have a lot of experience as a DBA you can make quite a bit of money.

18. Database Terms • Data Integrity – how accurate is the data? If you entered bad data (wrong address), then the database is useless. • GIGO: garbage in, garbage out • Data Security – protecting the data so it isn’t lost or misused • (having certain passwords to access database) • For example, I have no authority to access the HCC databases.

19. Database Functions • Maintaining Data • Adding data • Changing data • Deleting data • Validating data (making sure that the letter grades entered were all between A-F … a G would be an invalid grade)

20. Information Systems • For some strange reason, Information Systems is a topic that is covered at the end of this chapter. • It is a lead in to chapter 11

21. What is an Information System? • First, what is an information system? An information system is a collection of hw,sw,data, people, and procedures designed to generate information that supports the activities of users in an organization. So, what does that mean??? • Basically, it's similar to a program, but in a much larger scale. Sure, you may go to the store to buy a program/software to play solitaire, or to do your taxes, or to teach a kid to read. However, in large companies, they use more than programs, they use Information Systems.

22. Information System Example? • Online registration here at HCC isn't handled by one program, it's probably handled by an INFORMATION SYSTEM. Many things all working together to make it work.

23. Different Categories of Information Systems in Textbook • In the book they talk about • OIS (office information systems) – enhances communications, automates tasks • Continental Cool Savers is an OIS example … it emails you whenever special Continental Airfares come out • TPS (transaction processing systems) – processing day to day transactions • HCC student system • Online banking activity • Expert Systems (my favorite) • Others (OIS, DDS, MIS)

24. Summary • Understand what a database is • Understand the record versus field concept • Understand some of the terms and functions • Understand the advantages and disadvantages (as listed in book) • Understand what a query is (query-by-example versus a query language) • Know what a DBMS is versus a database • Know what SQL is • Understand what an Information System is • Know the categories of Information Systems