Designing Databases More chaos… less chaos… red chaos… blue chaos

1. INFO100 and CSE100 Fluency with Information Technology Designing DatabasesMore chaos… less chaos… red chaos… blue chaos Katherine Deibel Katherine Deibel, Fluency in Information Technology

2. Monday is a Holiday • Ergo, no lab sections on Monday • On Tuesday • Lab sections are optional • Attend any that you want • No attendance taken • Consider them to be "office hours" Katherine Deibel, Fluency in Information Technology

3. Project 3B • Instructions now up • Be sure to follow directions • Multiple files to turn-in • Two WebQ quizzes • First quiz can be taken multiple times • Second quiz can be taken just once Katherine Deibel, Fluency in Information Technology

4. Early Turn-In Extra Credit • Project 3B is due on Tueday, June 5 by 10pm… No late turn-ins! • Extra credit for early turn-in • 4pts if by Monday, June 4 at 10pm • 6pts if by Sunday, June 3 at 10pm • 8pts if by Saturday, June 2 at 10pm • 10pts if by Friday, June 1 at 10pm Katherine Deibel, Fluency in Information Technology

5. INFO100 and CSE100 Fluency with Information Technology Designing DatabasesMore chaos… less chaos… red chaos… blue chaos Katherine Deibel Katherine Deibel, Fluency in Information Technology

6. Making a Database • A database is made of … • Physical database:Tables actually stored on the hard disk • Logical databases:On-the-fly virtual tables created by queries • Queries:Commands written in SQL that define how to make a logical table from physical tables • How do we make each part? Katherine Deibel, Fluency in Information Technology

7. Design Steps • Describe the data physically • What are major clusters/higher-level notions of the data? • How do they interact/relate with each other? • Identify the logical structure • Describe a role with the data • Identify what fields are needed • Write queries for just those fields Katherine Deibel, Fluency in Information Technology

8. Technologies for Design • There is a technology more powerful than computers when it comes to designing software, databases, etc.? • What is it? • Pencil and paper • Whiteboards and markers • Blackboards and chalk Katherine Deibel, Fluency in Information Technology

9. Step Away From The Machine • Why not start directly with the database software? • Application locks you in to working in a certain order • Paper-pencil gives you some flexibility • Application will force you to attend to the details and not the general goals Katherine Deibel, Fluency in Information Technology

10. Database Design (on Paper) • Sketch relational structure using Entity-Relationship (ER) Diagrams • Tables in boxes • Lines show relationship and type of relationship • Example: Specifying a 1-to-many relationship 1 Faculty ∞ Student Katherine Deibel, Fluency in Information Technology

11. Database Design (on Paper) • Outline key fields and refine the relationships with the fields Faculty Student 1 Student_ID FirstName LastName MajorID Advisor Faculty_ID FirstName LastName Department DateHired ∞ Katherine Deibel, Fluency in Information Technology

12. Database Design Paradigms • Build physical DB to avoid redundancy • Each physical table represents one entity from your ER diagram • Expect that no physical table gives any user their exact view • To build view, build a query that • Joins tables together into a ‘super’ table • Trims out the items the user wants or needs Katherine Deibel, Fluency in Information Technology

13. Defining Physical Databases • Two aspects: • Underlying data architecture • Database schemes Katherine Deibel, Fluency in Information Technology

14. Data Architecture • The underlying architecture shapes how the data is stored on the computer and how it is accessed • Concerns efficiency in both speed and storage space • Depends greatly on the purpose of and usage of the database • Not a concern for most users since DBMSs designers made these decisions already Katherine Deibel, Fluency in Information Technology

15. Database Schemes • Database schemes (schema) are metadata specification that describes the database design • Includes • Selecting data types (boolean, date, etc.) • Selecting formats (YYYY-MM-DD) Katherine Deibel, Fluency in Information Technology

16. Katherine Deibel, Fluency in Information Technology

17. The Idea of Relationship • A relationship is a correspondence between rows of one table and the rows of another table • key Student_ID is used in each table, • Find address for each student (Lives_At) • Find the student for each address (Home_Of) Katherine Deibel, Fluency in Information Technology

18. Relationships in Practice Katherine Deibel, Fluency in Information Technology

19. One-to-One Relationship • When one entry of one table can be linked with exactly one entry in another table • Example:Each US State has exactly one capitol city City CityID Name Population Mayor Area US_State 1 StateID Name CapitolCity JoinDate Population 1 Katherine Deibel, Fluency in Information Technology

20. One-to-Many Relationship • When one entry of one table can be linked with multiple entries in another table • Example: Faculty can advise multiple students Student Student_ID FirstName LastName MajorID Advisor Faculty 1 Faculty_ID FirstName LastName Department DateHired ∞ Katherine Deibel, Fluency in Information Technology

21. Many-to-Many Relationship • When multiple entries of one table can be linked with multiple entries in another table • This is usually accomplished by using an intermediary table • Example:A writer can be the author of multiple books and a book can have multiple authors Book Writer Authors 1 Book_ID Title Year Publisher 1 Writer_ID FirstName LastName Authors_ID Writer_ID Book_ID ∞ ∞ Katherine Deibel, Fluency in Information Technology

22. Designing a Database The Initial Steps Katherine Deibel, Fluency in Information Technology

23. Premise • You have been contacted by a high school to build a database for the athletics program • What do you do? Katherine Deibel, Fluency in Information Technology

24. First Steps Clients Database Designer Interviews the experts/clients Builds a list of design goals and rules for the database • Staff • Coach • Players(?) Katherine Deibel, Fluency in Information Technology

25. Initial Statement of Goals • Individuals are selected for a team • Keep track of the points awarded to each student for participating for the awarding of school letters • The Database has to keep track of student Athletes over five years with any given Athlete participating in multiple sports in a given year. Katherine Deibel, Fluency in Information Technology

26. Getting More Details • Each high school student can play for 1 to 4 years. • A player can play in 0, 1, or many sports each year • Each team has many players Katherine Deibel, Fluency in Information Technology

27. More Specific Rules • The high school has many teams. • The high school is in one division. • Each team has a gender. • Each team is in a division. • Each team has a coach. • Each team has many players. • Each student earns points for winning an event. • Each student earns a letter by earning x points in a sport in a season. • Each student may participate in zero, one, or many sports each year. • Each student may play in sports for 1-4 years. • Each student may play in zero, one, or many competitions at an event. • Each coach can coach one or many teams. Katherine Deibel, Fluency in Information Technology

28. Physical Database • Major data entities • Teams • Athletes Katherine Deibel, Fluency in Information Technology

29. Fields • The HS has many teams • The HS is in one division • Each team has a gender • Each team is in a division • Each team has a coach • A team has many players • Each student earns points for winning an event • team_name • n/a • team_gender • n/a • coach_name • one-to-many • student_pointsteam_winsevent_nameevent_outcome Katherine Deibel, Fluency in Information Technology

30. Physical Database • Major data entities • Teams • Athletes • Events Katherine Deibel, Fluency in Information Technology

31. More Fields student_id student_result_at_event student_points_to_date student_points_at_event student_first_name letters_sport_code student_middle_name letters_awarded_date student_last_name team_gender student_date_of_birth event_location team_name student_gender team_description student_address coach_name student_other_details team_other_details division_description sport_description event_name event_start_date event_end_date event_other_details Katherine Deibel, Fluency in Information Technology

32. Process Continues • You keep adding • More fields • More relationships • Perhaps you discover the need more entities (tables) • Once complete, you implement the tables • And then the logical begins Katherine Deibel, Fluency in Information Technology

33. Summary • Designing a database is more about thinking than typing • Sketch out the relationships on paper • Identify the needs of the clients • Key aspect is to separate the logical and physical aspects • Physical is about entities and their relationships • Logical is about the user Katherine Deibel, Fluency in Information Technology

34. Monday is a Holiday • Ergo, no lab sections on Monday • On Tuesday • Lab sections are optional • Attend any that you want • No attendance taken • Consider them to be "office hours" Katherine Deibel, Fluency in Information Technology