1 / 47

MIS 111: Computers and the inter-networked society

MIS 111: Computers and the inter-networked society. Class 9: Database Management Systems July 21 st , 2011. Class project. Looks great! Google analytics… now what? I want you to check Google analytics and see who is visiting your Web site.

chaela
Download Presentation

MIS 111: Computers and the inter-networked society

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MIS 111: Computers and the inter-networked society Class 9: Database Management Systems July 21st, 2011

  2. Class project • Looks great! • Google analytics… now what? • I want you to check Google analytics and see who is visiting your Web site. • At the end of the semester, we’ll see who has the most visitors! • How do I get people to my site: see SEO slides; submit to Google, yahoo.

  3. Administration • Visio stencil on my Web site for ER diagrams

  4. Learning Objectives • List a few current events in information systems news • Construct and ER diagram (Recap) • Use vLookup in excel to mimic a relational database • Convert an ER diagram to a relational database scheme

  5. ER Diagram Recap

  6. What are the steps in developing an ER Diagram • 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.

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

  8. Aggregate Classes Revisited TeamName TeamId TEAMS TeacherId [1:3] [0:M] StudentId Aggregate [1:1] [2:5] Name TEACHERS STUDENTS

  9. Relational Tables

  10. Relational tables • We can use the entity relationship diagrams we design to create relational tables that can be stored in a database. • NOTE: A relationaldatabase is not just a table of information. It consists of multiple tables that reference one another.

  11. What is a relational table?? • Let’s mimic one together in Excel and vLookup • Download excel sheet from: http://www.u.arizona.edu/~jjenkins/mis111_summer2011/example%20data.xlsx

  12. A little history on the relational model Desktop My Documents My Network Places MIS 111 CMI My Pictures July 13.ppt Class Roster.doc

  13. The hierarchical model

  14. Edgar Frank "Ted" Coddwas unimpressed • In the 1960s, a man working for IBM decided that he could improve upon hierarchical data storage. • He knew that the hierarchical model led to data redundancy and confusion. • We always have to start at the top node. That makes looking for the data I need take forever! • Sometimes I end up with the same data saved in different places! If I need to update that data, I need to do it in multiple places! 1923-2003

  15. CodD’s normal forms (Rules) • 1st normal form (Rule 1) • No repeating elements or groups of elements Take from http://www.phlonx.com/resources/nf3/

  16. CodD’s normal forms (Rules) • 2nd normal form (Rule 2) • No partial dependencies on a concatenated key Take from http://www.phlonx.com/resources/nf3/

  17. CodD’s normal forms (Rules) • 3rd normal form (Rule 3) • No dependencies on non-key attributes Take from http://www.phlonx.com/resources/nf3/

  18. Some good news • ER diagrams were developed a few years after Codd • If the ER diagram was developed correctly, we are automatically in 3rd Normal Form!!!

  19. Review Some terminology

  20. Many-to-Many • 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

  21. One-to-many • 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

  22. One-to-one • 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

  23. Teachers, students, and courses • Remember this? TeacherEmail TeacherName TeacherSalary TeacherID StudentID TEACHERS [0:1] enroll in teach STUDENTS [0:30] [1:3] [4:6] COURSES StudentEmail CourseNo StudentName CourseName

  24. Steps to convert an erd to a set of relational tables • 1. For every strong entity class, create a table or relation. • The identifier becomes what is called a primary key. • The other attributes become what are called non-key elements. • We can represent the STUDENTS table as such: STUDENTS (StudentID, StudentName, StudentEmail)

  25. You try: courses • 1. For every strong entity class, create a table or relation. • The identifier becomes what is called a primary key. • The other attributes become what are called non-key elements. COURSES (CourseNo, CourseName)

  26. You try: teachers • 1. For every strong entity class, create a table or relation. • The identifier becomes what is called a primary key. • The other attributes become what are called non-key elements. TEACHERS (TeacherID, TeacherName, TeacherEmail, TeacherSalary)

  27. What we have so far • TEACHERS (TeacherID, TeacherName, TeacherEmail, TeacherSalary) • STUDENTS (StudentID, StudentName, StudentEmail) • COURSES (CourseNo, CourseName)

  28. What this would actually look like in table form

  29. Steps to convert an erd to a set of relational tables • 2. Locate any [1:M] relationships between strong entity classes. TeacherEmail TeacherSalary TeacherName TeacherID StudentID TEACHERS [0:1] enroll in teach STUDENTS [0:30] [1:3] [4:6] COURSES StudentEmail CourseNo StudentName CourseName

  30. Steps to convert an erd to a set of relational tables • 2. Locate any one-to-many relationships between strong entity classes. Put the primary key on the “1” side of the relationship into the table of the “M” side. TEACHERS (TeacherID, TeacherName, TeacherEmail, TeacherSalary) COURSES (CourseNo, CourseName, TeacherID)

  31. What this would actually look like as relational tables • “TeacherID” is now a foreign key in the COURSES table.

  32. Steps to convert an erd to a set of relational tables • 3. Locate any many-to-many relationships. Create a completely new table to represent this relationship. Use the primary keys of the participating entity classes as a new combined primary key. We now have THREE tables instead of two. COURSES (CourseNo, CourseName, TeacherID) STUDENTS (StudentID, StudentName, StudentEmail) STUDENTS_COURSES (StudentID, CourseID)

  33. What this would actually look like as relational tables

  34. Our final set of relations • STUDENTS (StudentID, StudentName, StudentEmail) • TEACHERS (TeacherID, TeacherName, TeacherEmail) • COURSES (CourseNo, CourseName, TeacherID) • STUDENTS_COURSES (StudentID, CourseNo)

  35. Another example: You try PupAgeWhenSold PupBreed sell PUPPIES STORES [1:1] [1:M] [1:M] PupTemp PupID StoreID buy StoreLoc StoreName CustID CustName [0:1] CustZip CUSTOMERS CustGender

  36. Let’s convert this erd to a set of relational tables • 1. For every strong entity class, create a table or relation. • 2. Locate any [1:M] relationships between strong entity classes. Put the primary key on the “1” side of the relationship into the table of the “M” side. • 3 . Locate any [M:N] relationships. Create a completely new table to represent this relationship. Use the primary keys of the participating entity classes as a new combined primary key.

  37. Prettiest puppies & co set of relations • PUPPIES (PupID, PupBreed, PupTemp, PupAgeWhenSold, StoreID, CustID) • STORES (StoreID, StoreName, StoreLoc) • CUSTOMERS (CustID, CustName, CustGender, CustZip)

  38. What this would actually look likes as relational tables

  39. Two more things: Weak entity classes and one-to-one relationships MusName InsID MusID [1:1] teach INSTRUCTORS MUSICIANS [1:1] MusInst InsEmail MusEmail InsName [0:M] Day LESSONS Time

  40. Steps to convert erd to sets of relational tables • 4. Locate any one-to-one relationships between entity classes. Take the primary key from one side of the relationship and place it in the table of the other entity class. INSTRUCTORS (InsID, InsName, InsEmail, MusID) MUSICIANS (MusID, MusName, MusEmail, MusInst) OR INSTRUCTORS (InsID, InsName, InsEmail) MUSICIANS (MusID, MusName, MusEmail, MusInst, InsID)

  41. Steps to convert erd to sets of relational tables • 5.For weak entity classes, create a new table or relation and add its attributes. Then, add the primary key from its corresponding strong entity class. Combine all attributes to create a new primary key. LESSONS (Day, Time, MusID, InsID)

  42. You try: doctors, patients, and treatments • Convert this ERD into a set of relational tables. DocID [1:1] [1:1] DOCTORS treat PATIENTS DocName [0:M] PatName PatID TREATMENTS Date Prscrpt

  43. Example: Doctors, patients, and treatments • 5.For weak entity classes, create a new table or relation and add its attributes. Then, add the primary key from its corresponding strong entity class. Combine all attributes to create a new primary key. DOCTORS (DocID, DocName) PATIENTS (PatID, PatName, DocID) TREATMENTS (Date, Prscrpt, PatID, DocID)

  44. Aggregates: teams • 6. Treat aggregates just like strong entity classes. Create a table or relation. • The identifier becomes what is called a primary key. • The other attributes become what are called non-key elements. • 7. Treat the aggregation as its own relationship and add foreign keys accordingly.

  45. Aggregates: Teams TEAMS [1:3] [0:M] STUDENTS Aggregate [2:5] [1:1] TEAMS (TeamID, TeamCaptain, FormationDate, TeacherID) TEAMS_STUDENTS (StudentID, TeamID) TEACHERS

  46. Phew. Everybody okay? • Quiz • Tomorrow: • I will give you / post your assignment • Paper or Visio (preferred). Stencils are on the Web site • You’ll have the rest of the class time to work on it (individual assignment). This will be due beginning of class next Wednesday. • I will be able to answer “general” questions.

More Related