Using MIS 2e Chapter 5: Database Processing David Kroenke

1. Using MIS 2e Chapter 5: Database Processing David Kroenke This presentation has been modified from the original and should be downloaded from the Course Documents area in Blackboard

2. Study Questions • Q1 – What is the purpose of a database? • Q2 – What is a database? • Q3 – What are the components of a database application? • Q4 – How do database applications make databases more useful? • Q5 – How are data models used for database development? • Q6 – How is a data model transformed into a database design? • Q6a – What is normalization and why is it necessary? • Q6b – What is the fastest way to a correct design? • Q7 – What is the user’s role in the development of databases? • Q8 – What are the responsibilities for database administration? • Opposing Forces Guide: “I Prefer a Spreadsheet” Chapter 5: Database Processing

3. Q1 – What is the purpose of a database (Database Portfolio)? • Every database maintains data (adds, edits, and deletes records) in order to produce information • Various databases were developed in the Database Portfolio to provide a practical (as opposed to theoretical) appreciation of database applications • Best Realty (A “simple” database with one table) • TheUniversity Database (Switchboard) • National Bank (One-to-many relationships) • Conference Center (Many-to-many relationships) • Computer Super Store (Front End/Back End) • The SD Coffee case study illustrates the steps in database development and is the focus of the Competitive Design project. Chapter 5: Database Processing

4. Q2 – What is a database (Table, Record, and Field)? • An entity is something you want to track of • A field is a basic fact • A record is a set of fields • A table is a set of records • A database is a set of tables Chapter 5: Database Processing

5. Q2 – What is a database (Implementing 1:N Relationships) • Primary key: Field that uniquely identifies a record • Foreign key: Primary key of another table • There is a one-to-many relationship between students and e-mail and between students and visits • The primary key of the one table is a foreign key in the many table Chapter 5: Database Processing

6. Q2 – What is a database (Implementing N:M Relationships) • There is a many-to-many relationship between advisors and students • Create an additional table that has (at a minimum) the primary keys of the related tables. Add additional fields as necessary (none required in this example) • Primary key: The field (or combination of fields) that make a record unique. The primary key of the new table is the combination of the two foreign keys (alternatively an entirely new primary key may be created) Chapter 5: Database Processing

7. Q2 – What is a database (metadata)? • A database is a self-describing set of tables; i.e., metadata are contained within the database to describe the data. • Field Name, and Data Type are metadata. • Each field has multiple fieldproperties that are also considered metadata Chapter 5: Database Processing

8. Q3 – What are the components of a database application? • Database – a self-describing collection of related tables • Database Management System (DBMS) – software with which you create and retrieve data in a database • Database application – a set of forms, queries, reports, and programs that interact with the DBMS Chapter 5: Database Processing

9. Q3 – What are the components of a database application (SQL)? • Query: Find all students with Undecided major and > 30 credits • QBE (Query by Example) • SQL (Structured Query Language) • The Result Chapter 5: Database Processing

10. Q4 – How do database applications make databases more useful? Each application represents a different front end to a common back end (the inventory database) A database may have one or more applications, and each application may have one or more users. Applications have different purposes, features, and functions, but they all process the same data stored in a common database. Chapter 5: Database Processing

11. Q4 – How do database applications make databases more useful? Application programs enable database processing over the Internet. For this use, the application program serves as an intermediary between the Web server and the database. Chapter 5: Database Processing

12. Q4 – How do database applications make databases more useful? • Enterprise DBMS • These products are for large organizations with thousands of users and many different database applications. • Such DBMS products support 24/7 operations and can manage dozens of different magnetic disks with hundreds of gigabytes or more data. • IBM’s DB2, Microsoft’s SQL Server, and Oracle are examples of enterprise DBMS products. • MySQL is an open source program that is license-free • Personal DBMS • Such products are used for personal or small workgroup applications that involve fewer than 100 users, and normally fewer than 15, many with only one user. • Microsoft Access is the only available personal DBMS. Chapter 5: Database Processing

13. Q5 – How are data models used for database development (Overview)? • The developers construct a logical representation known as a data model (Entity Relationship Diagram) to describe the data and the associated relationships. • The ERD is converted to a relational database according to the work of Dr. Edgar Codd. This process is known as database design. • Foreignkeys are added as necessary to implement relationships • Additional tables are created to eliminate repeating groups • A formal (iterative) process known as normalization ensures that there is no redundancy in the finished design. • The databaseapplication is created from the finished design by adding required forms, queries, reports, and programs. • User involvement is critical throughout the entire process because the database design and associated application(s) depends on how users view their business environment. Chapter 5: Database Processing

14. Q5 – How are data models used for database development (ERD)? • A data model (Entity Relationship Diagram or ERD) is similar to blueprints for a house. It’s a logical representation that describes data and their relationships in a database. • Anentity is something the user wants to track; e.g., Student, Advisor, or Department. • Every entity has multiple attributes (fields) that describe its characteristics; e.g., StudentName, HW1, or HW2 • Every entity has a unique identifier known as the primary key • Each entity type is represented by a rectangle within the ERD Chapter 5: Database Processing

15. Q5 – How are data models used for database development (Relationships)? • The relationship between two entities is represented by a line connecting them. • An arrow (crow’s foot) at the end of one line represents a one-to-many (1:N) relationship; e.g., one department has many advisers. • An arrow at both ends denotes a many-to-many relationship (N:M); e.g., one adviser has many students and one student has many advisers 1:N N:M Chapter 5: Database Processing

16. Q5 – How are data models used for database development (User Involvement)? • Students have many majors with a different adviser for each major. Thus one adviser has many students and one student has many advisers. • Students have many majors, but only one adviser in the major department. One adviser has many students, but each student has only one adviser. • Users are the final judges as to what data the database should contain and how tables are related to one another. Chapter 5: Database Processing

17. Q5 – How are data models used for database development (Cardinalities)? • The crow’s-foot notation shows the maximum number of entities (maximum cardinality) in a relationship; e.g. 1:N (one-to-many) or N:M (many-to-many) • The minimum requirements are called minimum cardinalities. A vertical line (number 1) means at least one entity is required; an oval (a zero) means optional entry • A department is not required to have an adviser, but an adviser must have a department • An adviser is not required to have a student, but a student must have an adviser Chapter 5: Database Processing

18. Q6 – How is a data model transformed into a database design (1:N relationship)? • Each entity has its own table in the database • The primarykey is the field that uniquely identifies a record • A foreignkey is the primary key of another table; it is added in order to implement a 1:N relationship • The primary key of the “one” table becomes a foreign key in the “many” table Chapter 5: Database Processing

19. Q6 – How is a data model transformed into a database design (N:M relationship)? • Create an additional table that has (at a minimum) the primary keys of the related tables. Add additional fields as necessary (none required in this example) • The primary key of the new table is the combination of the two foreign keys (alternatively an entirely new primary key may be created) Chapter 5: Database Processing

20. Q6a – What is Normalization and why is it necessary (eliminate redundancy)? Overview: Bob’s Better Burgers is a national corporation that offers franchises for fast food restaurants. The company requires a database that will track its restaurants, the individuals who own those restaurants (franchisees), and the contracts that govern those restaurants. Normalization creates additional tables (one for each entity), adds foreign keys to implement the 1:N relationships and eliminates the anomalies • There are several problems (anomalies) in data entry with a single table: • Changes to existing records have to be made in multiple places (e.g., the franchisee Grauer moves) • Additions are difficult (e.g., can’t add a new franchisee or contract without a corresponding restaurant) • Deleting a restaurant may have unintended consequences (e.g., deleting restaurant R5 loses data for Coulter and contract C3) Chapter 5: Database Processing

21. Q6a – What is Normalization and why is it necessary (The Franchise) • Identify the physical entities • Create the required tables • Implement the relationships: • There is a 1:N relationship between franchisees and restaurants. • There is a 1:N relationship between contracts and restaurants. • All problems in data entry are gone Chapter 5: Database Processing

22. Q6a – What is Normalization and why is it necessary (The Franchise) Q: Who owns restaurant R4? What is his/her phone number? A: Glassman (212) 458-5054 Q: How many restaurants are governed by contract C1? What was the total initial fee? A: Three restaurants (R1, R2, and R4); $750,6a0 in total Chapter 5: Database Processing

23. Q6a – What is Normalization and why is it necessary (repeating groups)? Overview: The registrar seeks to implement a database that will track students and the courses they take. The initial design includes two tables, one for courses, and one for students. There is a problem with the Students table in that the records are of different lengths, due to the variable number of courses taken by each student. Normalization creates an additional Student Course table to eliminate the repeating groups Chapter 5: Database Processing

24. Q6a – What is Normalization and why is it necessary (Student Database)? • Identify the physical entities • Create the required tables • Implement the relationships: • There is a N:M relationship between students and courses that requires an extra table. • The primary key in the new table is the combination of Student Number, Course Number, and Semester • The repeating groups are gone Chapter 5: Database Processing

25. Q6a – What is Normalization and why is it necessary (Student Database)? Q: Which students took ACC101? Q: Adams (S1) and Baker (S3) Q: Which classes did Jones take? How many credits did he complete? A: Jones (student S4) took ENG16a and MTH100 for a total of 7 credits Chapter 5: Database Processing

26. Q6a – What is the fastest way to a correct design (The Money Slide)! • Identify the entities in the physical system • Create the required tables and identify the primarykey (additional fields need not be added at this time) • Identify and implement the relationships: • One-to-many (1:N): The primary key of the “one” table is a foreign key in the “many” table • Many-to-many (N:M): Create additional table that has (at a minimum) the primary keys of the related tables • Complete each table by including other fields necessary to produce the requested information • Ask questions of the database to ensure the completeness of the design “The key, the whole key, and nothing but the key”, so help me Codd” Chapter 5: Database Processing

27. Q7 – What is the user's role in the development of databases? • Your role in the database development process is to decide what data should be included and how records should relate to one another. • The best time to change a database structure is during the data modeling stage. It’s easier and cheaper to change your mind before anything is actually built. The costs of correcting the database in time and money later on may be very high. • Each entity must contain all the data you need to do your job. • Each relationship must accurately reflect the appropriate view of your business. • You must be the final judge of how well the database will serve your needs. Do not go forward until the data model is accurate. Chapter 5: Database Processing

28. Q8 – What are the responsibilities for database administration? • Most organizations have created a staff function called database administration. • Smaller organizations typically have only one person in this function; larger organizations assign several people to an office of database administration. • Thus depending on the context, the letters DBA either stand for the database administrator or for the office of database administration. • The purpose of database administration is to manage the development, operation, and maintenance of a database so as to achieve the organization’s objectives. • This function requires balancing conflicting goals: protecting the database while maximizing its availability for authorized use. Chapter 5: Database Processing

29. Q8 – What are the responsibilities for database administration? Chapter 5: Database Processing

30. Opposing Forces Guide – I Prefer a Spreadsheet • I’m not buying all this stuff about databases. • “I’ve tried them and they are too complicated to set up. You have to have professional IS people to create it and keep it running” • “A spreadsheet works just as well most of the time. When I want something, I use Excel’s Data Filter and/or pivot tables and I can usually get what I need.” Excel also has multiple database functions. • “Besides, I don’t really want to share my data with anyone. I work pretty hard to develop my client list. Why would I want to give it away?” • Advantages of Access: • Easier data entry via user forms • Report quality output • Relational databases and multiple user access! Chapter 5: Database Processing

31. Summary • A database is a collection of related tables • A Database Management System (DBMS) is software with which you create and retrieve data in a database • A database application is a set of forms, queries, and reports that interact with the DBMS • The development process consists of: • A data model (Entity Relationship Diagram) describes the data and the associated relationships in a system • Database design is the process of converting a data model into tables, relationships, and constraints. The data are stored in two-dimensional tables according to the relational model developed by Dr. Edgar Codd • Normalization is a formal process that eliminates redundancy • Structured Query Language (SQL) is an international standard for defining and processing database data. • The DBA manages the development, operation, and maintenance of a database Chapter 5: Database Processing

32. Review: Select the Appropriate Term Table – Metadata – Foreign Key – Normalization – Relational Database – Edgar Codd – DBMS – Record • The primary key of another table Foreign key • A DBMS approach in which entities are presented as two dimensional-tables Relational database • A program used to create, process, and administer a database DBMS • The individual credited with the relational database approach Dr. Edgar Codd • A technique used to simplify complex databases and eliminate data redundancy Normalization • Describes the data in a database Metadata • A set of fieldsRecord • A set of records Table Chapter 5: Database Processing