MIS 111: Computers and the inter-networked society

1. MIS 111: Computers and the inter-networked society Class 8: Data Management July 20th, 2011

2. News

3. Announcements • Grades are posted: http://www.u.arizona.edu/~jjenkins/mis111_summer2011/grades/grades.htm • Check them often to make sure I haven't made a mistake • New Office Location (E-commerce Lab conference room) • Today I’ll be there only until 11:40 • Web sites looks great! • I was hoping to give you the entire day of Friday to work on your assignment 2, but I might have to lecture a bit too.

4. Learning Objectives • Define the basic components of an ER diagram • Be able to construct a complete ER diagram based on a simple business case • Be able to critique ER diagrams

5. This week • Today: Entity Relationship Diagrams • Thursday: Relational Tables • Quiz • Friday: Assignment #1 • Meet in McClelland Hall 218

6. Why are we doing this? • Businesses can live or die by the management of their data • Businesses, especially online businesses, have a TON of data to manage • Peter Chen’s entity relationship diagrams are an extremely effective way to conceptualize data I <3 ERD

7. Our business case • Happy Hills High School has commissioned us to keep track of its operations. • They need to track information about students, teachers, and courses. • Where we’ll start: • They need to keep track of students’ id numbers, names, and email addresses.

8. Warning: notes encouraged DEFINITIONS AHEAD

9. Entity relationship Diagrams

10. Entities and entity classes • Entity – a person, place, thing or event about which information is maintained • Entity Class – an abstract representation of ALL of a particular type of entity STUDENTS

11. attributes • Attribute – a characteristic or quality of a particular entity class • Identifier– an attribute that uniquely identifies an entity class (always underlined) StudentName StudentID StudentEmail STUDENTS

12. Example: teachers • We want to represent all teachers and keep track of their names, email addresses, and salaries. TeacherName TeacherEmail TeacherID TeacherSalary TEACHERS

13. EXAMPLE: COURSES • We want to represent all courses and keep track of course numbers and course names. COURSES CourseNo CourseName

14. What’s next? • We have created the STUDENTS, TEACHERS, and COURSES entity classes. • We gave each entity class its own attributes. • We also provided each entity class with a unique identifier. • What more can we represent?

15. relationships • STUDENTSenroll in COURSES. CourseNo StudentID enroll in STUDENTS COURSES CourseName StudentEmail StudentName

16. Example: Teachers and courses • TEACHERSteachCOURSES. TeacherName TeacherEmail TeacherSalary TeacherID TEACHERS teach COURSES CourseNo CourseName

17. Let’s put the erd together TeacherEmail TeacherName TeacherSalary TeacherID StudentID TEACHERS enroll in teach STUDENTS COURSES StudentEmail CourseNo StudentName CourseName

18. Let’s keep on going: Adventure awaits

19. Cardinality • Cardinality – defines a relationship between two entity classes in terms of howmany instances of X are allowed for each instance of Y CourseNo StudentID enroll in STUDENTS COURSES CourseName StudentEmail StudentName

20. Business rules • Cardinality is often defined by certain business rules. • For example, Happy Hills High School has the following business rules: • A student must enroll in at least four classes to be considered a full-time student. • A student may enroll in no more than six courses at a time. • Course enrollment may not exceed thirty students.

21. cardinality • In a binary relationship, we must ask four questions: • 1. For each COURSE, what is the minimum number of STUDENTS I may have? • 2. For each COURSE, what is the maximum number of STUDENTS I may have? [0:30] enroll in STUDENTS COURSES

22. cardinality • 3. For each STUDENT, what is the minimum number of COURSES I may have? • For each STUDENT, what is the maximum number of COURSES I may have? [0:30] [4:6] enroll in STUDENTS COURSES

23. note • We call this in general a “many-to-many” relationship because each entity class may have more than one instance. • Many-to-many = [M:N] [0:30] [4:6] enroll in STUDENTS COURSES

24. Example: teachers teach courses • 1. For each COURSE, what is the minimum number of TEACHERS I may have? • 2. For each COURSE, what is the maximum number of TEACHERS I may have? TeacherSalary TeacherID TEACHERS [0:1] TeacherEmail TeacherName teach CourseNo CourseName COURSES

25. Example: teachers teach courses • 3. For each TEACHER, what is the minimum number of COURSES I may have? • 4. For each TEACHER, what is the maximum number of COURSES I may have? TeacherSalary TeacherID TEACHERS [0:1] TeacherEmail TeacherName teach [1:3] CourseNo CourseName COURSES

26. note • We call this in general a “one-to-many” relationship because one side may only have up to one instance and the other may have many. • One-to-Many = [1:M] TEACHERS [0:1] teach [1:3] COURSES

27. Your turn! • Prettiest Puppies & Co. operates a chain of pet stores that sell puppies to customers looking for new furry friends. The management would like to be able to keep track of which puppies are bought by which customers, as well as which stores in the chain sell which puppies. • Management would like to track puppy breeds, ages when sold, and temperaments. They would also like to keep track of each customer’s last name, gender, and zip code. Finally, they need to keep track of each store’s name and location.

28. Erd: Prettiest puppies & co. PupAgeWhenSold PupBreed sell PUPPIES STORES [1:1] [1:M] [1:M] PupTemp PupID StoreID buy StoreLoc StoreName CustID CustName [0:1] CustZip CUSTOMERS CustGender

29. One more thing… • There are different types of entity classes. • Strong entity classes are entity classes that can exist by themselves. They are existence independent. • Weak entity classes, on the other hand, are entity classes that are existence dependent. In other words, they depend upon the existence of one or more strong entities to exist.

30. Example: Doctors and patients • DOCTORS treat PATIENTS. When a PATIENT sees the DOCTOR, a TREATMENT is prescribed. • Can a TREATMENT exist without the presence of a DOCTOR and PATIENT?

31. Doctors, patients, and treatments • Weak entity classes are represented by concentric rectangles. DOCTORS treat PATIENTS TREATMENTS

32. What about the first example? • What information did we want to track??? • Which STUDENTS are enrolled in which COURSES • Which TEACHERS are teaching which COURSES TEACHERS QUESTION: Could each COURSE have existed by itself? ANSWER: Yes. The COURSE was always in the catalog and did not need either TEACHERS or STUDENTS to exist. teach STUDENTS enroll in COURSES

33. Weak entity classes and cardinality • Rule: The strong entity classes upon which the weak entity class depends must have a minimum cardinality of 1 or greater. [1: [1: DOCTORS treat PATIENTS TREATMENTS

34. Ternary relationships and cardinality • New question: For every combination of DOCTOR and PATIENT, what is the minimum/maximum number of TREATMENTS I may have? [1: [1: DOCTORS treat PATIENTS [0:M] TREATMENTS

35. Ternary relationships and cardinality • Q3 & 4. For every combination of DOCTOR and TREATMENT, what is the min/max number of PATIENTS I may have? [1: [1:1] DOCTORS treat PATIENTS [0:M] TREATMENTS

36. Ternary relationships and cardinality • Q5 & 6. For every combination of PATIENT and TREATMENT, what is the min/max number of DOCTORS I may have? [1:1] [1:1] DOCTORS treat PATIENTS [0:M] TREATMENTS

37. note • We call this in general a “one-to-one” relationship because both entity classes may only have a maximum of one instance. • One-to-one = [1:1] [1:1] [1:1] DOCTORS treat PATIENTS

38. note • Weak entity classes do NOT have identifiers.

39. Your turn • Twinkle Tones School of Music provides private lessons only by musician request. When a musician requests a private lesson, the School finds an available instructor and arranges a lesson schedule. • The School would like to track these lessons – the day of the week when they are scheduled, as well as the time they begin. • The School would also like to track musician names and email addresses, as well as instructor names and email addresses.

40. Twinkle tones private lessons MusName InsID MusID [1:1] teach INSTRUCTORS MUSICIANS [1:1] InsEmail MusEmail InsName [0:M] Day LESSONS Time

41. Tomorrow’s class • We are going to take these entity relationship diagrams and turn them into relational tables! • FYI: Microsoft Visio • Quiz! • Bring your computer with excel

42. Pay special attention to these learning objectives: • List the three rules of the Long tail. • Define Service Oriented Architecture (SOA) • List 4 benefits of SOA • List the four steps of Search Engine Optimization • List at least four HCI design principles for Web sites • List three examples of HCI applications today • Be able to critique ER diagrams