Week 9

1. Introduction to Databases Week 9

2. Agenda Introduction to Databases Introduction to Database Design Scope of Learning Database Vocabulary Database Characteristics Types of Databases

3. Scope of Learning • Our goal is to have a basic understanding of databases • Resources: • Slides • In class activities • No textbook is required • Videos

4. Optional Reading • Database Design for Mere Mortals • Hernandez • 1997 • Addison Wesley • ISBN: 0-201-69471-9

5. Why Learn Databases? • Stores Information • Information is a valuable commodity • Information is used for: • Marketing • Business Intelligence (BI) • Used by all kinds of businesses • Access to good information (if properly done)

6. Question What are some of the databases that you have seen or use?

7. Database Terms

8. Data According to Dictionary.com, data is “individual facts, statistics, or other items of information” • Data is never contrived (formulate)

9. Database According to Dictionary.com, a database is “a comprehensive collection of related data organized for convenient access, generally in a computer.”

10. DBMS • Database Management System • Provides tools needed to organize data in a flexible manner. It includes: • Ability to add, modify or delete data • Ability to ask questions about the data stored (queries) • Ability to produce reports

11. Database Models According to Wikipedia: A database model is the theoretical foundation of a database and fundamentally determines in which manner data can be stored, organized, and manipulated in a database system. In our words: defines the infrastructure offered by a particular database system.

12. Types of Databases

13. Many Types of Databases • File Maker Pro • Oracle • Microsoft • Excel • Access

14. Database Model Types Flat File Hierarchical Relational Database

15. Flat File • Wk9_FlatFile.xlsx • Advantages: • Most software includes free access of data files (iegoogle docs etc) • Disadvantages: • Not conducive to complicated search queries

16. Hierarchical Looks like a family tree with parent / child relationships Each table or entity can be either a parent or a child Tables are implicitly joined with each other

17. Hierarchical

18. Hierarchical

19. Relational Databases Most Common Composed of tables (each constructed like a flat file) Wk9_Northwind.accdb

20. ERD Entity Relational Diagram (ERD) Visual display of tables and their relationships for a database Used for relational modeling

21. What are Tables or Entities Employees • Used to represent things in the real world that is of significant interest to the user • For example: • employees might be an entity in a company database • Nouns in a business narrative

22. Entities Continued • Another example: • A database regarding music

23. Entities and Their Attributes • Entities represent something of importance • Entities are made up of several attributes:

24. Entities Summarized • Each entity is made up of rows and columns • Columns are called fields or attributes of a database • Rows are called records or an instance of an entity

25. Steps in Database Design • Complete a needs assessment (note this is a huge process that is not covered in this course) • Create a business narrative • Identify entities • Identify attributes & keys • Identify relationships

26. Business Narrative • Vital link between database designer and the end users • Purpose is to make clear the data requirements • Example: • Algonquin college employs many instructors to deliver various programs comprised of many different courses.

27. Starting an ERD programs instructors courses • Identify the entities Algonquin college employs many instructors to deliver various programs comprised of many different courses.

28. When the List of Entities Seems Complete • Ask about each entity: • Is it significant? • List only entities that are important to your database users and that are worth the trouble and expense of computer tabulation. • Is it generic? • List only types of things, not individual instances • Is it fundamental? • List only entities that exist independently and do not need something else to explain them.

29. Sample Business Narrative • CD Collection example • I have many cd’s by various artists. I like all types of music

30. Sample Business Narrative • Courses example: • A course can have many different learning objectives and each learning objective has various examples and a method of testing.

31. Sample Business Narrative • New Home Builders example: • Bamarack is a builder of new homes in the Ottawa area. They subcontract all the work that needs to be completed for the build of a new home. Each subcontractor has a specialized trade.

32. We Build a Business Narrative Create a business narrative for your shoe collection at home

33. Selecting Attributes • Attributes are the fields that describe an entity • Attributes can be: • A Characteristic • A Quality • A Feature • A Fact • An attribute is a non-decomposable piece of information about an entity • Attribute naming conventions: • Single word • Unique • Can’t be a reserved word

34. Attributes for Our Shoe Scenario • Identify the attributes for all entities identified for our shoe inventory database

35. Attributes • Identify the entity and attributes for the following business narrative: • ABC Property Management Inc has a building with 20 units. Some are rented, some are vacant. Some are 1 bedroom, some are 2 bedrooms. The rent varies. ABC Property Management Inc keeps track of the tenant and lease information

36. Rules defining tables, rows, and columns

37. Rule • Columns must stand alone • The order of columns within a table has no meaning in the model • The model should still be correct even if the columns are rearranged

38. Rule • Columns must have a unique name • Columns values must be unitary • A column can contain only single values, never lists • For example if you decide to treat a person’s first and last names as separate values, the names must be in separate columns not in a single Name column

39. Rule • Each column must contain data of a single type • Rows must be unique • In every row, some column must contain a unique value

40. Rule • Rows must stand alone • Each row of a table is independent and does not depend on any other row of the same table. • As a consequence, the order of the rows in a table is not significant

41. Review Tables / Entities • Tables (aka Entities) in the relational model are used to represent “things” in the real world. • Entities are made up of attributes • Attributes are pieces of data that describe the entity

42. Determining Keys for tables

43. What is a Primary Key? • The primary key of a table is the column whose values are different in every row • A field in the database that guarantees uniqueness • Speeds data retrieval • RULE: Each record in a table must have a primary key. • Wk9_FlatFile.xlsx (Relational Tab)

44. Primary Keys • Some entities have ready-made primary keys, for example: • Catalog codes • If an entity lacks a reliably unique field • Use a system-assigned (autonumber)

45. What is a Foreign Key? • A field in a table that is the primary key of another table. • Wk9_FlatFile.xlsx (Relational Tab) • A foreign key is used to indicate a relationship between 2 or more tables.

46. Hybrid / Homework • Watch YouTube Video#1 & YouTube Video #2 • Read Flat File vs Relational Database • Watch Lynda.com Relational Databases Video • Why relational databases? • Introduction to data modeling • Reviewing relationship types • In a word document: • Write a review for each video • Identify entities and attributes in the scenario provided in the hybrid document

47. Quiz Time