The Edge of IT ITEC-200 Fall 2006

1. The Edge of IT ITEC-200 Fall 2006 Topic 3: Database Professor J. Alberto Espinosa

2. Roadmap Decision SupportDistributed CollaborationEnterprise CollaborationFinancial Managementetc. Information BusinessApplications Transaction Processing ServerAppl Client Appl ITInfrastructure DB DB Database IT Infrastrucure: - HW & SW - Database - Telecom IT &Business Business Applications IT & Business

3. Agenda • Introduction to database and database management systems (DBMS) concepts • Learn how to query databases to extract the information you need • Learn how to design and implement databases

4. Database & Database Management System (DBMS) Concepts Follow up course:ITEC-470 Databases, Data Mining & Knowledge Management

5. Definitions Database: An organized collection of “logically related” datathat can be retrieved on demand Database Management System (DBMS): Software that manages databases (i.e., define, create, update, and query databases) Acts as intermediary between business applications and physical data files “Most powerful, scalable, flexible and effective business applications rely on a well designed database and a powerful underlying DBMS”

6. The Old Way:Data files Data and program files were separate. You had to write individual programs to: define the data; upload it; update it; manipulate it; and or retrieve it Examples: Accounting, Human Resources Data Files Application Programs API1 WindowsUnix, Linux System Software INSTRUCTION SET PC, Mainframe HARDWARE

7. A Better Way: Using a DBMS A business application passes high level instructions to the DBMS. The DBMS has capabilities to do all the necessary data management: data definition, manipulation, and retrieval. So, the business application does not have to worry about low level data management functions Examples: Accounting, Human Resources,ERP, CRM Database Application API2 Oracle, Access, MS SQL Server DBMS Database API1 WindowsUnix, Linux System Software INSTRUCTION SET PC, Mainframe HARDWARE

8. Advantages of Using Databases & DBMSs • Programs independent of data structure • Less data redundancy • Better consistency in the data • More flexibility & scalability • Easier to integrate & share data • Easier to develop business applications • Easier to enforce business rules/constraints • Easier access to data by users (e.g., queries, reports, forms, etc.)

9. DBMS in a Client/Server Environment Database Server:runs “back-end” part of the DBMS to process queries and perform database management tasks Database Client:runs “front-end” part of the DBMS that provides the user interface (e.g., data entry, screen displays or presentation, report formatting, query building tools) CLIENT SERVER Database Request (ex. query) Execute query Front-EndDBMS Back-EndDBMS Response(ex. query result)

10. DBMS in a Web Server Environment Request (ex. get a price quote, place an order) Response (ex. query results with HTML-formatted product price or order confirmation notice)

11. Data Warehouse“A database that stores and consolidates current and historical data from various systems (internal and external) with tools for management reporting and sophisticated analysis—i.e., Datamining”

12. Most Common Database Models • Hierarchical (of historical interest only) • Network (of historical interest only) • Relational • Object Oriented databases (new)

13. Relational Database A database with structured data consisting of: • Tables: or “entities” Every table has a unique name Ex. Students, Courses • Fields: or “columns”, “attributes” Every field has a unique name within the table Ex. Students (StudentID, StudentName, Major, Address) Ex. Courses (CourseNo, CouseName, CreditPoints, Description) • Records: or “rows”, “tuples”, “instances” Every record is unique (has a unique field that identifies it) Ex. {“jdoe”, “John Doe”, “CS”, 5000 Forbes Ave.) Ex. {“MGMT-352-001”, “MIS”, Fall 2002, “A great course”}

14. Terminology Equivalence

15. DBMS Functions and Tools • Performs 3 main functions: • Data definition (define, create databases) • Data manipulation (data entry, updates) • Data retrieval (extraction, reports, displays) • Plus additional database tools: • Data dictionary: data about the database • Visual tools: report & form design • Data modeling & database design tools • Macros and programming languages • Internet/web features, etc. • Examples: • Oracle, DB2, Visual FoxPro, MS Access & MS SQL

16. Define, Create and Manipulate Data in Databases

17. MS Access(structured data)

18. MS Access Data Definition

19. MS Access Data Manipulation

20. Visual FoxPro Data Manipulation

21. Lotus Notes

22. Displaying and Printing Data From Databases:Forms and ReportsThe idea:(1) organize and store the data in the most efficient way, without data redundancy(2) retrieve the data in the way you want to see it (not necessarily how it is organized internally) using queries(3) present the results to users and managers using forms (for data entry) and report (for printouts)

23. FormsTypically used for data entry & screen displays

24. ReportsTypically used for printouts

25. Database Queries

26. Queries are DBMS Commands for: Queries are often thought of as a method to retrieve data, but queries can also be used to define and manipulate data Databases can be queried in many ways: • Proprietary DBMS commands and languages, or • Standard query methods/languages (QBE, SQL, etc.) • Data definition (define, create databases) • Data manipulation (data entry, updates) • Data retrieval (extraction, reports, displays)

27. Standard Query Methods Query by Example (QBE) • Called Design View in MS Access • Visual interface using examples of data requested • Similar to how you do searches in the library Structured Query Language (SQL) • Popular with power users • Works in most DBMS • Can embed SQL commands in programs, web scripts, etc. • English-like commands (4GL), practical • Exact, mathematical: relational algebra & matrix math

28. Query by Example (QBE) • Called Query “Design View” in MS Access • Column labels are the fields we want to retrieve • In table cells we enter “examples” of the info we want

29. 8 SQL Commands Only • Data Definition: CREATE TABLE, DROP TABLE • Data Manipulation:INSERT, UPDATE, DELETE, UNION, JOIN • Data Retrieval:SELECT (need to learn this only)

30. SQL Commands: Data Definition Example: Create & Delete Table called “Friends” CREATE TABLE Friends (FriendID integer, LastName char(24), FirstName char(24), Birthday date, Phone char(10), Notes memo); One SQL Command ; = End ofCommand DROP TABLE Friends;

31. SQL Commands: Data Manipulation • INSERT:Add new records • UPDATE: Modify existing records • DELETE: Delete records • UNION: Combine records from two tables • JOIN: Combine columns from two tables

32. SQL Commands: Data ManipulationAdd & Update Records Insert (add) a complete record (values in all fields): INSERT INTO Friends VALUES (“ae”, “Espinosa”, “Alberto”, 12/12/2002, “885-1958”, “Looks tired, needs a vacation”) Insert (add) partial record (values in some fields only): INSERT INTO Friends (FriendID, LastName, FirstName) VALUES (“ae”, “Espinosa”, “Alberto”) Update (modify) record with new values: UPDATE Friends SET LastName=“Espinosa”

33. Data Retrieval Queries: The Idea • Organize database (design, create): • In the most efficient & consistent way (internally) • Not based on how you want the data to look • Produce the “virtual” tables as you want them to look using queries How we store the data How we display the data

34. Data Retrieval in SQLThe “SELECT” Command SELECT<field list & function list>–columns to retrieve FROM <table list> –tables that contain the data WHERE condition1 –which records to retrieve AND condition2 ……. ORDER BY field1, ….. –to sort the query result SELECT can be followed by: DISTINCT (eliminates duplicate rows from result)TOP # (lists only the top # rows of result) * (lists all fields in the table)

35. Complexity of SELECT Queries • Simple Queries: Involve a single table • Complex (Join) Queries: Involve more than one table

36. Simple SELECT Queries SELECT ProdID, ProdName, Type, Price FROM Products WHERE Price>=300; SELECT Avg(Price) AS AvgPrice FROM Products WHERE Price>=120 AND Type=“Percussion”; SELECT Count(*) as TotOrders FROM Orders Click here to download this database [local copy]

37. Complex SELECT (Join) Queries Tables:Orders (OrderNo, ClientID, OrderDate, OrderStatus) LineItems (OrderNo, LineItem, ProdID, Qty) Table Join(2 ways): SELECTOrders.OrderNo, ClientID, LineItem, ProdID, Qty FROM Orders, LineItems WHERE Orders.OrderNo = LineItems.OrderNo; Join Condition Table Product(Don't do this!! Don’t forget the join condition): SELECTOrders.OrderNo, ClientID, LineItem, ProdID, Qty FROM Orders, LineItems;

38. Database Design

39. Database Design Issue #1:Data Integrity in Databases Two main types of data integrity: Entity Integrity: • Ensuring that every record in each table in the database can be addressed (i.e., found) -- this means that there each record has to have a unique identifier that is not duplicate or null (i.e., not blank) • Examples: every student has an AU ID; every purchase order has a unique number; every customer has an ID Referential Integrity: • Ensuring that the data that is entered in one table is consistent with data in other tables • Examples: purchase orders can only be placed by valid customers; accounting transactions can only be posted to valid company accounts

40. Database Design Issue #2:Special Fields(Keys) How do you find the record (info) you need? • Primary key (PK): • Field(s) that uniquely identifies a record(CourseNo, InstructorID) • Entity integrity = PK is not duplicate & not blank • PK can be: • A single field (e.g., UserID), or • Multi-field—i.e., “composite” (OrderNo, LineItem) • Dual key = a composite PK with only 2 fields How do you link related tables in a database? • Foreign key (FK): • A field in a table that is a PK in another table • That is, a field that “must” exist in another table • This is how referential integrity is maintained

41. Illustration: Primary and Foreign Keys PK FK PK

42. Design Issue #3:Steps in Database Design • Build a Data Model – or Entity-Relationship Diagram(ERD) – for the business application: this model describes each data element in the application and how different data elements relate to each other • Review the data model to ensure entity and referential integrity • Convert (i.e., map) the data model into a relational database design • Convert the data model into a database relationship diagram • Implement Important Data Rules, there are 3 of them: • Add update rules: (1) don’t allow or (2) cascade updates • Add delete rules: (1) don’t allow or (2) cascade updates • Add business rules: e.g., PayDate >= OrderDate; OrderQty > 100 • Normalize the database design as necessary and update the data model and design if necessary – i.e., re-organize the tables to avoid recording redundant data

43. Data Model Example (Entity Relationship Diagram--ERD): Course Registration System Course Instructors CourseNo InstructorID Teaches CourseDescription LastName Many 1 FirstName InstructorID Entities CreditPoints Telephone EMailAddr PreRequisites ClassroomNo 1 Relationships Students Includes StudentID Many Enrollment LastName FirstName Enrolls StudentID SSN CourseNo Department Many 1 College Comments Major EMailAddr

44. Entity-Relationship Diagrams (ERDs) Conceptual Data Modeling • Data-oriented modeling method that describes the data and relationships among data entities • Goal: capture meaning of the data • 2 main ERD constructs: • Entities and its attributes • Relationships between entities

45. Entity “An object, person, place, event or thing or which we want to record data” • Equivalent to a table in a database • Examples: instructors, students, classrooms, invoices, registration, machines, countries, states, etc. • Entity instance: a single occurrence of an entity Example: Espinosa, Kogod 39, MGMT-352, 001 • Entities can be identified in a requirements analysis description by following the use of NOUNS

46. Entity Representation Entity Name Peter Chen’s notation

47. Relationships • Relationships describe how two entities relate to each other • Relationships in a database application can be identified following the VERBS that describe how entities are associated with one another • Examples:studentsenroll in courses countrieshavecities, etc.

48. Cardinality • Is an important database concept that helps understand how two entities are related • Cardinality: describes how many instances of one entity can be associated with another entity • The cardinality of a relationship between two entities has two components: • Maximum Cardinality: is the maximum number of instances that can be associated with the other entity – usually either 1 or many (the exact number is rarely used) • Minimum Cardinality: is the minimum number of instances that can be associated with the other entity – usually either 0 or 1 • Symbols: 0 1 Many • Examples:1 student can only park 1 (or 0) cars – 1 to (0 or) 11 client can place (0 or ) many orders – 1 to (0 or) many1 student can enroll in (at least 1 or) many courses and a course can have (0 or) many students – (0 or) many to (1 or) many

49. ERD SYMBOLS (cont’d.) Student EmailAccount Has 1 to 1 MaximumCardinality(OUTER symbol) Student Car Parks Mandatory Optional MinimumCardinality(INNER symbol) Peter Chen’s notationusing Systems Architect software

50. ERD SYMBOLS (cont’d.) → Advises← Have Advisor Student 1 to Many MaximumCardinality 1 to Many (or None) Faculty Course Teaches Mandatory Optional MinimumCardinality Peter Chen’s (“crow’s feet”) notationusing Systems Architect software