Paul Jacobs The iSchool University of Maryland Wednesday, Mar. 6, 2019

1. INFM 603: Session 5Introduction to Databases and Database Design Paul Jacobs The iSchool University of Maryland Wednesday, Mar. 6, 2019 This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United StatesSee http://creativecommons.org/licenses/by-nc-sa/3.0/us/ for details

2. Today’s Topics • Fundamentals of Databases • Relational Databases and Database Design • Object-Oriented Databases

3. Recap–Programming/JavaScript • Programming in JavaScript is quite like other programming (algorithms, control structures, data structures, etc.) • JavaScript is special because it is the native programming language of the browser (client side) and so can input/output to the document object (the web page) or browser window • JavaScript runs on the client! • Programs are implementations of algorithms – formally specified steps that accomplish something – and must be specified using programming language tools to the goriest detail

4. Client Web browser Web server Common gateway interface Application logic You are here! Databases and DBMS Application partition

5. Database Fundamentals - Overview • What is a database? • Purpose • Role in application development • Relationship to architecture/OO design • Some major concepts of database design • The database schema • Normalization • Entity – relationship diagrams Database Fundamentals Database Design Object-Oriented Databases

6. What is a Database? • A database is a mechanism (or system) for organizing and storing information. • Within applications, databases are modules (also known as a “tier”) that provide storage and access to widely-used objects • Within implementations, databases represent a layer that takes care of, and hides, many aspects of dealing with large quantities of information Database Fundamentals Database Design Object-Oriented Databases

7. Why do Applications Need Databases? • Persistence - In your code, data exists only when the program is running • Sharing – Information in a database can be used by different programs, applications • Many other features that go along with all that – Access control and security, concurrency control, data backup, etc. Database Fundamentals Database Design Object-Oriented Databases

8. Database Examples • Personal databases (e.g., address book) • Human resources (employee and payroll information) • Inventory and order processing • Library catalogs • Company financial data Database Fundamentals Database Design Object-Oriented Databases

9. Why We Need Databases • They encapsulate and hide a wide range of data functionality, such as: • Constraints and integrity – e.g., making sure data are correct and not corrupted • Data and file storage – e.g., details of where and how to keep data, redundancy, etc. • Security • Synchronization and concurrency control – e.g. keeping data current, avoiding mess-ups when data changes • Details of efficient access Database Fundamentals Database Design Object-Oriented Databases

10. Static vs. Dynamic Data • On-line transaction processing (OLTP) – most online systems • On-line analytical processing (OLAP) – data warehouses, business intelligence, data mining Database Fundamentals Database Design Object-Oriented Databases

11. Types of Database Systems • Flat file • Hierarchical • Network • Relational • Object-oriented • Object-relational Database Fundamentals Database Design Object-Oriented Databases

12. Relational Databases • Databases in which the primary means of organizing information is a table … • Which also support crucial elements of database functionality: • Structured query (via SQL, the structured query language) • Keys, constraints and integrity • Views • Reporting Database Fundamentals Database Design Object-Oriented Databases

13. Why Relational Databases? • Neat separation of organization from implementation • Societal factors • Widespread and entrenched availability • Trained users and implementors • Standardization of SQL and ODBC Database Fundamentals Database Design Object-Oriented Databases

14. Database Functions • Query (retrieve information)“What’s the lowest fare from Baltimore to LA on Friday?” • Update“My mobile phone # is 703-307-0467” • Report“Show total orders by month for 2015” • Analyze (“mine”)“What types of charges are most likely to be unauthorized (i.e., credit card fraud)?” • What else? Database Fundamentals Database Design Object-Oriented Databases

15. Database Design

16. Relational Database Concepts to Know • Schema – the overall design, organization and relationships in a database • Tables – the names of data entries and their relationships • Fields and columns – the types of entries themselves • Records and rows – the individual entries (instances) • Keys – fields that uniquely identify a record • Relationships – structures or groupings between fields • Data types – constraints on fields, such as strings, numbers, dates, etc. Database Fundamentals Database Design Object-Oriented Databases

17. The Database Schema • A group of related objects in the database • A data model (especially the organization that makes the data useful and the procedures for using the data) • For us, mainly the design of the tables (e.g., what goes in which tables, columns, and fields) “A contact entry has a name, address, and phone number” Database Fundamentals Database Design Object-Oriented Databases

18. Database Schema Example Database Fundamentals Database Design Object-Oriented Databases

19. Schema Owner Constraints Tables Views Procedures Indexes More on Database Schemas Database Fundamentals Database Design Object-Oriented Databases schema objects 1) Stephens, R.K. and Plew. R.R., 2001. Database Design. SAMS, Indianapolis , IN. (with slight changes by V.G.D.)

20. Simple Table Example Database Fundamentals Database Design Object-Oriented Databases

21. Customers Publishers Inventory Authors Orders Books Database with Multiple Tables Database Fundamentals Database Design Object-Oriented Databases 1) Stephens, R.K. and Plew. R.R., 2001. Database Design. SAMS, Indianapolis , IN. (with slight changes by V.G.D.)

22. Table Example Customers Database Fundamentals Database Design Object-Oriented Databases

23. Table Example – Fields (Columns) a field Customers Database Fundamentals Database Design Object-Oriented Databases

24. Table Example – Records (Rows) Customers Database Fundamentals Database Design Object-Oriented Databases a record

25. Table Example – Primary Key Customers Database Fundamentals Database Design Object-Oriented Databases

26. Primary Keys • Unique identifiers that select rows • May be created manually or automatically (e.g. Sequence ID # vs. ISBN) Database Fundamentals Database Design Object-Oriented Databases

27. Compound Primary Key Example MovieRoles Database Fundamentals Database Design Object-Oriented Databases primary key fields

28. Table Example – Foreign Keys primary key field parent table Directors relationship child table Movies Database Fundamentals Database Design Object-Oriented Databases foreign key field

29. Why “Foreign Keys” are Important • You can’t put everything in one table! • It would be terribly cumbersome and inefficient • It would make it harder to find some data (say, personal details about directors) • It could be inconsistent and needlessly duplicative (e.g., people have names, birthdates, contact info, etc., regardless of whether they are movie directors or students) • But often you need to combine information from multiple tables – this is called a “join” • Some of the most complex of database queries • Often hidden in software that accesses databases (why?) • Good to know about (for correct design, avoiding inefficiencies and delays) Database Fundamentals Database Design Object-Oriented Databases

30. Data Types in Databases • Very similar to data types in programming • Alphanumeric (Text, Memo) • Numeric (Number, Currency, etc.) • Date/Time • Boolean (Yes/No) • However, databases are a higher level and types can be more powerful and more abstract, e.g. • Checking to make sure data is valid – is this a valid date? • Converting and reformatting – easily show the same date in multiple formats • Computation and comparison – sort by date Database Fundamentals Database Design Object-Oriented Databases

31. Tables and Records - Terminology • A table (entity, object class) describes a type of object and its associations or relationships • A record (row, instance, object) describes an individual – e.g., in the table of customers, John’s customer entry is a record • A field (column, attribute, property) is a part of a table • An association or relationship relates multiple tables, entities or classes Database Fundamentals Database Design Object-Oriented Databases

32. Relating Different Terms • Class  • Object  • Attribute  • Association  • … Database Fundamentals Database Design Object-Oriented Databases

33. Relating Different Terms • Class  Table (Entity) • Object  Record (Row) • Attribute  Column (Field, Attribute) • Association  Relationship • … Database Fundamentals Database Design Object-Oriented Databases

34. Database Normalization

35. What is Normalization? • A method for organizing data elements into tables. • Done in order to avoid • Duplication of data • Insert anomaly • Delete anomaly • Update anomaly • Database designs can be in first normal form (1NF), second normal form (2NF), and third normal form (3NF) – ideal is 3NF Database Fundamentals Database Design Object-Oriented Databases

36. Example (Unnormalized) • Table: SalesOrders (Un-normalized) • SalesOrderNo • Date • CustomerName • CustomerAddress • ClerkName • Item1Description • Item1Quantity • Item1UnitPrice • Item2Description • Item2Quantity • Item2UnitPrice • Item3Description • Item3Quantity • Item3UnitPrice • Total Database Fundamentals Database Design Object-Oriented Databases

37. Normalizing into 1NF • Separate repeating groups into new tables. • Start a new table for the repeating data. • The primary key for the repeating group is usually a composite key. Database Fundamentals Database Design Object-Oriented Databases

38. Example (1NF) Table: SalesOrders • SalesOrderNo • Date • CustomerName • CustomerAddress • ClerkName • Total Table: OrderItems • SalesOrderNo • ItemNo • ItemDescription • ItemQuantity • ItemUnitPrice Database Fundamentals Database Design Object-Oriented Databases

39. Dependencies • Functional dependency: The value of one attribute depends entirely on the value of another. • Partial dependency: An attribute depends on only part of the primary key. (The primary key must be a composite key.) • Transitive dependency: An attribute depends on an attribute other than the primary key. Database Fundamentals Database Design Object-Oriented Databases

40. Normalizing into 2NF • Remove partial dependencies. • Start a new table for the partially dependent data and the part of the key it depends on. • Tables started at this step usually contain descriptions of resources. Database Fundamentals Database Design Object-Oriented Databases

41. Example (2NF) Table: OrderItems • SalesOrderNo • ItemNo • ItemQuantity • ItemUnitPrice Table: InventoryItems • ItemNo • ItemDescription Database Fundamentals Database Design Object-Oriented Databases

42. What if we hadn’t done 2NF? • Duplication of data: ItemDescription would appear for every order. • Insert anomaly: To insert an inventory item, you must insert a sales order. • Delete anomaly: Information about the items stay with sales order records. Delete a sales order record, delete the item description. • Update anomaly: To change an item description, you must change all the sales order records that have the item. Database Fundamentals Database Design Object-Oriented Databases

43. Normalizing into 3NF • Remove transitive dependencies. • Start a new table for the transitively dependent attribute and the attribute it depends on. • Keep a copy of the key attribute in the original table. Database Fundamentals Database Design Object-Oriented Databases

44. Example (3NF) Table: SalesOrders • SalesOrderNo • Date • CustomerNo • ClerkNo • Total Table: Customers • CustomerNo • CustomerName • CustomerAddress Table: Clerks • ClerkNo • ClerkName Database Fundamentals Database Design Object-Oriented Databases

45. What if we hadn’t done 3NF? • Duplication of data: Customer and Clerk details would appear for every order. • Insert anomaly: To insert a customer or clerk, you must insert a sales order. • Delete anomaly: Information about the customers and clerks stay with sales order records. Delete a sales order record, delete the customer or clerk. • Update anomaly: To change the details of a customer or clerk, you must change all the sales order records that involve that customer or clerk. Database Fundamentals Database Design Object-OrientedDatabases

46. Example (Final Tables) Table: SalesOrders • SalesOrderNo • Date • CustomerNo • ClerkNo • Total Table: OrderItems • SalesOrderNo • ItemNo • ItemQuantity • ItemUnitPrice Table: InventoryItems • ItemNo • ItemDescription Table: Customers • CustomerNo • CustomerName • CustomerAddress Table: Clerks • ClerkNo • ClerkName Database Fundamentals Database Design Object-Oriented Databases

47. Table: SalesOrders(Un-normalized) • SalesOrderNo (PK) • Date • CustomerName • CustomerAddress • ClerkName • Item1Description • Item1Quantity • Item1UnitPrice • Item2Description • Item2Quantity • Item2UnitPrice • Item3Description • Item3Quantity • Item3UnitPrice • Total Database Fundamentals Database Design Object-OrientedDatabases

48. Table: SalesOrders(Un-normalized) • SalesOrderNo (PK) • Date • CustomerName • CustomerAddress • ClerkName • Item1Description • Item1Quantity • Item1UnitPrice • Item2Description • Item2Quantity • Item2UnitPrice • Item3Description • Item3Quantity • Item3UnitPrice • Total For 1NF Separate repeating fields Database Fundamentals Database Design Object-OrientedDatabases

49. Table: OrderItems • ItemDescription • ItemQuantity • ItemUnitPrice Table: SalesOrders • SalesOrderNo (PK) • Date • CustomerName • CustomerAddress • ClerkName • Total Database Fundamentals Database Design Object-OrientedDatabases Needs PK and relationship with the “origin” table (SalesOrders).

50. Table: OrderItems • ItemNo • ItemDescription • ItemQuantity • ItemUnitPrice Table: SalesOrders • SalesOrderNo (PK) • Date • CustomerName • CustomerAddress • ClerkName • Total Database Fundamentals Database Design Object-OrientedDatabases Generate new ID field for order items.