Information technology in business and society

1. Information technology in business and society Session 15 – Relational Databases Sean J. taylor

2. Administrativia • Assignment 3: Due tonight at midnight(AdSense in a week) • Midterm back on Thursday • Database tutorial led by Varun • Assignment 4: Posted Thursday, due Friday 3/30

3. Administrativia II • Groups:I will email a form for you to list up to five classmates and then choose the groups. You can list 0-5. • 2-way feedback:1. I will send you an anonymous survey.2. I will send you a brief summary of your current grade.

4. Learning objectives • Understand what relational databases are (or, why text files and Excel are not enough) • Identify and distinguish between the following parts of a relational database: tables, records, fields, field values • Understand three types of anomalies that arise from un-normalized data • Understand how primary keys and foreign keys are used to link tables.

5. Why are data valuable?

7. Relational Databases • Store data (insert) • Retrieve data (query) • Software applications • Operations • Analyze data (reporting capabilities)

9. Why not store data like this?

10. Field Table Record Field value Relational Databases • Databases that use a series of logically related two-dimensional tables to store their information • Tables are comprised of fields/records, which in turn contain field values Student Last Name SS# DOB Major Smith 100201122 06/11/84 IS Kim 200202222 1/1/85 FIN Davis 300201232 12/31/81 MKT Pat 999132212 3/3/88 ACC

11. Relational Databases Relational Database Tables Records Fields Field values Bytes, bits Field Student Table Last Name SS# DOB Major Smith 100201122 06/11/84 IS Kim 200202222 1/1/85 FIN Davis 300201232 12/31/81 MKT Pat 999132212 3/3/88 ACC Record Field value

12. Advantages • Consistency • We can restrict the values of certain fields (e.g. dates, integers) • We can impose other kinds of constraints (all costs must be positive, last names must be included, orders must have addresses) • Data look the same to all users at the same time. • Centralization • Many different users can edit and view the data simultaneously. Efficient sharing of information. • Efficient Querying • SQL and other query languages can be used to create complex reports quickly

13. When should you use a database instead of Excel? Should we just create multiple workbooks in Excel? • Insertion anomalies • Deletion anomalies • Update anomalies • The real power of a database: Querying • How would you answer the following question in Excel? • Find customers that spend on average $50 per book order, that live on West Coast or on the East Coast (but not in Midwest) and whose annual income is at least $150K Problems with Excel? } Data Quality Problems

14. Insertion Anomalies • Inability to insert a piece of information about an object without having to insert a (bogus) piece of information about something else • Example: Adding a new customer/book before it is orderedHow can you add the book “Harry Potter” in the file below?

15. Deletion Anomalies • The loss of a piece of information about one object when a piece of information about a different object is deleted • Example: Deleting order 2 => deleting customer Lee Sproull • Example: Deleting order 1 => deleting book “Code…”

16. Update Anomalies • A need to change the same piece of information about an object multiple times • Example: Changing Jeff Bezos address in order 1 leaves orders 6 and 8 unchanged…

17. Modeling data with Entity-Relationship Diagrams

18. Entity Relationship Diagram • The aim of an ERD is to model the data within the Information System. • Provides a CONCEPTUAL DATA MODEL:a concept of the system, independent of implementation • What data should be stored? • What relationships exist between items of data?

19. Entities An actual, real thing or person about which data might be stored is referred to as an entity. • An entity can be uniquely identified. • Organizations collect and store data about entities: • if a bank stores data about you - you are an entity • if a business stores a piece of paper called an invoice - the invoice is an entity • a library stores data about a particular book - the book is an entity

20. Relationships • Entities are associated with each other via relationships. • A relationship is a named association between two or more entity types: Player Team Plays for

21. Defining Relationships • Entity-relationship (E-R) diagram:a graphic method of representing entity classes and their relationships. • Rectangle – entity class • Dotted line – relationship • | – single relationship • O – zero or optional relationship • Crow’s foot () – multiple relationship • The types of relationships reflect the business rules applicable to the entities

22. Simple Hospital Example • In a hospital system, each ward has many patients who are cared for by nurses assigned to the specific ward. Patients may require treatment by more than one specialist doctor. A patient belongs to only one ward.

23. Simple Hospital Example • In a hospital system, each ward has many patients who are cared for by nurses assigned to the specific ward. Patients may require treatment by more than one specialist doctor. A patient belongs to only one ward.

24. Simple Hospital System • Ward has many patients (1:N) • Patients are cared for by nurses (N:M) • Ward has assigned many nurses (1:N) • Patients require treatment by one or more doctor (N:M)

25. Simple Hospital System ERD WARD DOCTOR has accommodates treats assigned NURSE PATIENT cares for

26. University Database Example A department has many Professors. A Professor belongs to only one department. The department offers many different courses, and many Professors can teach a single course. A Professor can also teach more than one course. Students enroll for many courses and courses have many students. A course belongs to only one department.

27. Fitting Data into the Relational Model Normalization

28. normalizing Amazon’s data • The process of assuring that a database can be implemented effectively as a set of two-dimensional tables • Unlike Excel though, the tables are connected • Prevents insertion, deletion and update anomalies

29. Primary keys Foreign keys Integrity constraints • A field (or group of fields in some cases) that uniquely describes each record in a table • Examples: Customer ID, ISBN, Order# • A field that is a primary key in one table and appears in a different table (though not as the primary key) • Examples: Customer ID in Orders • Rules that help ensure data quality Connecting tables together Each record should have a uniqueprimary key

30. Summary of the logical structure of your database • The tables in your database, along with each of their fields, keys • The relationships between the tables Primary key Primary key Primary key Foreign key Foreign key Database schema

31. Next Class:SQL • Do the SQL tutorial athttp://sqlzoo.net/(at least sections 0-3) • Download the “Facebook” database from Blackboard and make sure you can open it in MS Access • Interest in OSS databases? • Bring a laptop if you want.