INTRO TO DATABASES Part I IS 340 BY CHANDRA S. AMARAVADI

1. INTRO TO DATABASES Part I IS 340 BY CHANDRA S. AMARAVADI

2. IN THIS PRESENTATION.. • Entity classes, entities, attributes • Database organization • file, record, attribute/field • DBMS activities and functions • DBMS components • Importance of databases

3. NEED FOR DATABASES • A church needs to maintain information on donations • A consulting company needs to manage its contacts • A doctor needs to know if a patient is allergic to a particular medication. • An airline exec needs to know occupancy on a flight.

4. BASIC DATA CONCEPTS

5. THERE ARE THINGS AND OBJECTS IN THE REAL WORLD These are called entities.

6. WE ARE MORE INTERESTED IN Eclasses customers Produce Organization machines/ parts Sales/vendors

7. ENTITY CLASSES HAVE PROPERTIES Properties are called attributes customers Produce Barcode#, price per unit, supplier, total qty machines/ parts Sales/vendors

8. DISCUSSION Classify the following as entity, entity class or attribute • John • San Francisco (as a city) • Customer • Book • Store# • Nintendo DS • Microsoft • Microsoft employees • The book “Great Expectations” • Invoice • Production

9. BASIC ORGANIZATION CONCEPTS

10. DATA VS INFORMATION Data about an entity are raw facts. Are they useful? Car driven by Bobby Redhall December 30th, 1984 Played for Cavs and Heat Born March 28th , 1986 15m albums sold

11. INFORMATION IS USEFUL IF COMPLETE Example information about a person Name: Chris Nash DOB: 2-4-1965 Profession: Engineer Address: 415 Walnut street. This describes a person more. It is called ????.

12. ANOTHER EXAMPLE Example information about a credit card transaction: Acct#: 4555950 Date: 12/5/08 Time: 4:48 pm Merchant: WIU Union Card type: Master Card Issuer: First Bank This describes a transaction more.

13. ONE ATTRIBUTE HAS A UNIQUE VALUE Which of these has a unique value? Acct#: 4555950 Date: 12/5/08 Time: 4:48 pm Merchant: WIU Union Card type: Master Card Issuer: First Bank The unique attribute is called ??? 3300 3305 3313

14. ORGANIZATION OF DATA/ INFORMATION Transaction#: 55643 Date: 12/5/10 Time: 4:08 pm Merchant: Vitales Card type: Master Card Issuer: First Bank Transaction#: 55644 Date: 12/6/10 Time: 4:10 pm Merchant: WIU Union Card type: Master Card Issuer: First Bank Observations about these two “units” of data?

15. TRADITIONAL CONCEPT OF A FILE CBT QUALITY MANAGEMENT COMMITTEE Meeting Minutes Meeting Date/time: December 2nd, 2:00 p.m. Present: Larry Wall, Ken Nimrick, Xiang Yi, Western Illinois University strives to maintain a community which values academic excellence; institutional integrity; and justice, equity, and diversity. Such an environment is essential in fostering the intellectual growth and personal development of all students. Each member of the University community shares responsibility in maintaining conditions which support the University's purpose. The Code of Student Conduct is designed to provide basic guidelines to advance the University's mission as a premier educational institution.

16. CONCEPT OF A DATABASE FILE

17. DEFINITIONS

18. DATABASE ORGANIZATION Structure/schema Attributes/field names Primary key Record Record attr. values

19. DATABASE ORGANIZATION Database – A group of related files File/table – A group of related records Record – a grouping of related field values Attribute – property e.g. hair color Schema -- This is the logical view of the database (tables and fields) Primary key – An attribute whose values are unique within a file Secondary key – Any other attribute DBMS – software program to provide controlled data access Database Files/tables Records Attr. values

20. DATA MODELS AND DBMS MODELS

21. THE DATA BASE APPROACH Entity classes File 1 cust. File 2 emp. Data base Organization Data model

22. DATA MODELS • Abstract view of the data & relationships • Captures data needs • Supports implementation Relationship between students and courses? between doctors and patients? between teams and players?

23. DEFINITIONS Entity – Individual example of person, place or thing. Entity Class – Collection of related entities. Attributes – Properties of entity classes about which we would like to collect information.

24. DATA MODELS… Three types of relationships among entity classes 1:1 For each value of A, one and only one value of B and vice versa. A B 1:M For each value of A, many values of B, but for each B only one A. A B M:N A B For each value of A, many values of B and vice versa.

25. DISCUSSION Identify the types of the following relationships • Company -- president • Instructor -- students • Flights -- pilots • City -- convention centers • Club -- members • Team -- players • Company -- city • Books – authors • Artists -- records

26. DBMS ACTIVITIES

27. DBMS ACTIVITIES & FUNCTIONS Activities with DBMS • Define structure/schema • Enter data • modify data • query data • get reports

28. DBMS ACTIVITIES & FUNCTIONS Define structure/schema This is called data definition

29. DBMS ACTIVITIES & FUNCTIONS Enter data -- Create a data entry form Heading PRODUCT DATA ENTRY Label Product #: Description: Field Data entry form

30. DBMS ACTIVITIES & FUNCTIONS Modify data • Add • Delete • Change Records/field values

31. DBMS ACTIVITIES.. Database Retrieval

32. DBMS ACTIVITIES & FUNCTIONS Query data – can use QBE or SQL list products costing more than $2,000 Query by example (QBE) form

33. DATABASE RETRIEVAL THE STRUCTURED QUERY LANGUAGE • Each SELECT statement has three parts • SELECT, FROM and WHERE • SELECT is used to select output attributes • FROM is used to specify the tables • WHERE is used for row selection criteria SELECT <attr. list> FROM <tables> WHERE <condition1, condition 2…>

34. DATABASE RETRIEVAL.. Reservation RESULT SQL QUERY Flt# Confirm# AA1802 PA5R2 PA802 LX5R2 Select Flt#, Confirm# From Reservation Where Pname = “Smith”;

35. ANOTHER EXAMPLE SELECT Flt#, Deptime, Arrtime FROM ?? WHERE Depcity = "PHX" and Destcity = "ORD"; FLIGHTS What is the result of the query?

36. DISCUSSION Write SQL queries to list: 1) employee names. 2) Employees who live in Macomb. 3) employees who enjoy soccer. EMP.

37. THE THEORY OF RETRIEVAL Dept. file DEPTS. have EMPLOYEES Emp. file

38. MULTI-TABLE SELECT STATEMENT SELECT table1.attr1, table2.attr2 . . . . FROM table1, table2, . . . . . WHERE table1.fkey = table2.fkey AND/OR condition1 AND/OR . . . . . . . . . . . . . ; NOTE • When there are multiple tables, attr. names preceded by table name • The values of common attr. need to be equal in the WHERE part • There can be more than one condition, connected by AND or OR

39. GETTING DATA OUT.. RH Title PRODUCT LISTING PH Column Headings PRODUCT # DESCR. PRICE Detail Product # Descr. Price Fields PF Average Price Footer RF A report specification

40. GETTING DATA OUT.. PRODUCT LISTING PROD# DESCRIPTION PRICE M100 Chair $ 50.00 M150 Table $200.00 Average Price $153.00 A generated report

41. SUMMARY OF DBMS ACTIVITIES Activities with DBMS • Define structure /schema • Enter data • modify data • query data • get reports

42. DBMS ORGANIZATION

43. Major Components of DBMS D B M S Kernel Data Defn. SQL Prog. Language Interface Data Diction- ary Screen/ Report Gen. Appln. Gen. D B M S Kernel Export/Import

44. DBMS COMPONENTS.. Data definition – the facility through which schema is defined. (how new tables are created). SQL interface – the facility through which SQL commands are typed in. Programming language interface – the facility which processes SQL commands embedded in application program. Also known as the host language interface. Data dictionary – the facility that records details about the schema, reports, data entry forms etc. Screen & reports- the facility through which data entry screens and reports are created. Appln. Generation- the facility through which applications are created. Export/Import -- the facility through which files can be imported/exported in different DBMS formats. DBMS Kernel -- the actual programs which interact with the O/S and carry out data I/O.

45. USEFULNESS OF DATABASES

46. USAGE OF DATA/INFORMATION Suppose we have detailed information on each and every transaction in a store, what can we do with that? What if a customer wants to return a shirt purchased in the store? What if a manager wants to know what products were sold on a particular day?

47. IMPORTANCE OF DBMS’s Databases are used: • To store and record information e.g. bal, price, grades etc. • To retrieve information e.g. check#432 cashed? • To report information e.g. daily sales • To answer queries e.g. how many shoes were sold? Advanced uses: • To analyze trends • Identify sales prospects

48. THAT’S ALL FOLKS!