Computer Science 101 Web Access to Databases

1. Computer Science 101Web Access to Databases ER and Relational Models

2. Entity Relationship (ER) ModelConcepts • Adesigntool • Entity - Object or thing or concept - car, person, job • Attribute- Property describing entity - name, salary • Each entity can have a value for each of its attributes.

3. Types of Attributes • Simple - atomic, non-divisible – this is what we use • Derived – value not stored, but derived from other stored attributes - Age from birthdate • Null – No value • Not applicable • Missing - exists • Not known - may or may not exist • Key attribute - Value of this attribute uniquely identifies the entity - possibly composite – student id number, social security number, apartment number with building number

4. Faculty Entity Type SSN Attribute ER-Diagram: Entities and Attributes Street City State Zip

5. EnrolledIn Advises Students Faculty Sections Students Relationships • Binary relationshipon entity sets E1, E2 : Relates certain pairs from the two entity sets.

6. Constraints on Relationship Types:Cardinality Ratios • Cardinality ratio- specifies number of relationship instances that an entity may participate in. • Possible ratios - 1:1, 1:N, M:N • M and N can be thought of as "1 or more"

7. Chairs 1 1 Faculty Departments Cardinality ratios (cont.) • A single faculty member would chair one department (at most). • A single department would be chaired by one faculty member.

8. AssignedTo Computers Departments N 1 Cardinality ratios (cont.) • A single computer would be assigned to a single department. • A single department could have multiple computers assigned to it.

9. EnrolledIn Students Sections M N Cardinality ratios (cont.) • A single student could be enrolled in multiple sections. • A single section would have multiple students enrolled in it.

10. CapitolOf 1 1 States Cities OrderedBy Orders Customers N 1 M N RecommendedFor Movies Customers Cardinality ratios (cont.)

11. ER Diagram for Department Database Name Type Title Name CNum Time Room Title Office Sem Sponsors SSN 1 Faculty Projects N Rank 1 1 N Advises Teaches WorkedOn SNum N 1 N Class Enrolled M N Sections SMajors Id N Sem N Phone N HasHad SectionOf MajorsIn 1 1 M Title N Courses Major Type M RequiredOf Abbrev M N Prerequisite

12. Our Class Database • For the students in the class, I wanted: • Names (first and last names) • Major information including multiple majors, department major is in, department chair (and information about the chair – phone, etc.) • Primary faculty advisor (and information about the advisor – phone, etc.) • Interests of various kinds and some information about the interest itself (category, maybe url, etc.)

13. Entity Types for Class Database -Students Term City State Student Id Students Zip First Name Birthdate Class Year Last Name

14. Entity Types for Class Database -Faculty Faculty Id Faculty First Name Email Phone Last Name

15. Entity Types for Class Database -Majors Major Id Majors Name Chair Department

16. Entity Types for Class Database -Interests InterestId Interests Name URL Category

17. ER Diagram for Class Database -(Without attributes) N MajorsIn Students Majors M M N N Has ChairedBy AdvisedBy 1 N 1 Faculty Interests

18. Redundancy • Redundancy in a database is when we have the same information stored multiple times. • This is BAD because • Updates and deletions must take place on all occurrences • For example, if my phone number is stored in the records of all of my advisees, then a change of phone number is a real problem

19. Relational Model –Constraint Attributes • Primary Key - special designated key – can not be null (underlined) • Foreign Key - this is an attribute in one table that matches a primary key in another table. The requirement is that a value of this foreign key must match an existing primary key value in the other table.

20. Name SSN ... Dept STUDENT FACULTY Name Id ... AdvSSN Relational Model Constraints (cont.) • Foreign Key example

21. ER  Relational: Step 1 • Create table R for each entity type. Have a column for each attribute. Choose a primary key. • Class database – create tables: Students - StudentID Faculty - FacultyID Majors - MajorID Interests - InterestID

22. N 1 AssignedTo Computers Departments ER  Relational: Step 2 • For 1:N relation type: For entity on the N side, include a foreign key for the 1 related entity on the 1 side. • Include in the Computers table, the primary key of Department as foreign key. • Computers(SerNum,Make,…,DeptNum,…)

23. Include in the Student table, the primary key of the Advisor as foreign key. N 1 AdvisedBy Students Faculty ER  Relational: Step 2Class Database • Students(StudentID,FirstName,…,AdvisorID,…)

24. Include in the Major table, the primary key of the Chair as foreign key. N 1 ChairedBy Majors Faculty ER  Relational: Step 2Class Database • Majors(MajorId, Name,…,ChairID,…)

25. ER  Relational: Step 3 • For each M:N relation R, create table S with primary keys of the two entities as attributes. • Examples • HasHad(ID,Cnum) • Prerequisite(CNum1,CNum2) • RequiredOf(Cnum,Abbrev) • EnrolledIn(ID,Snum,Cnum)

26. Leads to new table StudentMajor(StudentID, MajorID) M N MajorsIn Students Majors ER  Relational: Step 3Class Database

27. Leads to new table StudentInterest(StudentID, InterestID) M N Has Students Interests ER  Relational: Step 3Class Database

28. Informal Relational Schema forthe Class Database Students(StudentID,FirstName, LastName, ClassYear, City, State, Zip, BirthDate, AdvisorID, Term) Faculty (FacultyID, FirstName, LastName, Phone, EMail) Majors(MajorID, MajorName, Department, ChairID) Interests(InterestID, InterestName, Category, URL) StudentMajor(StudentID, MajorID) StudentInterest(StudentID, InterestID)