1 / 12

CSE 103

CSE 103. Review Day 07 in the book What are entities ? How are they stored? What are records ? How are they stored? What are attributes ? How are they stored? What is a 1:N relationship ? M:N relationship ? 1:1 relationship ? What is a foreign key ?

lindstroma
Download Presentation

CSE 103

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. CSE 103 • Review Day 07 in the book • What are entities? How are they stored? • What are records? How are they stored? • What are attributes? How are they stored? • What is a 1:N relationship? • M:N relationship? • 1:1 relationship? • What is a foreign key? • Continue to Day 08 in the text, if you finish http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  2. Debrief Homework • Import data from the text file CanadianUniversities06.txt into a temporary table. • Construct an APPEND query to copy select data from this table into the appropriate fields in table tbl_Schools http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  3. Relational Databases • From the Day 7 reading: • What are entities? How are they stored? • What are records? How are they stored? • What are attributes? How are they stored? • What is a 1:N relationship? • M:N relationship? • 1:1 relationship? • What is a foreign key? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  4. Relationships in the music_linked_07 database • Copy the music_linked_07database and open it in Access • Similar but not identical to musicdemo • Identify relationships between the tables • Classify each as M:N, 1:N, or 1:1 • How did you find there was a relationship of any kind? • How did you know what type it was? • How is each type implemented in the database? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  5. Database Design • Using tables and relations between the tables allows us to store information with a minimum of redundancy • How do we extract information from a multi-table database? • How do multi-table queries incorporate the relations between the tables? • e.g., Which albums were released on the Sony label? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  6. Questions about JOINS • The JOIN operation is discussed on 8-2 and 8-3 of the textbook. • What are the three steps in the JOIN operation called? • What is meant by each of these terms? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  7. Pencil and Paper JOIN Exercise • Worksheet with two partial tables • tbl_Movies and tbl_MovieGenres • The JOIN table is partially complete • First operation is unfinished • Complete all steps of the JOIN operation • Which rows are left in the final JOIN table? How many columns are there? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  8. Constructing a Multi-Table Query in SQL • First operation: PRODUCT SELECT tbl_Movies.MovieID, MovieTitle, Year, tbl_MovieGenres.MovieID, GenreType, RankFROM tbl_Movies, tbl_MovieGenresWHERE (tbl_Movies.MovieID = 176658 OR tbl_Movies.MovieID = 331381 OR tbl_Movies.MovieID = 348396) AND (tbl_MovieGenres.MovieID = 176658 OR tbl_MovieGenres.MovieID = 331381 OR tbl_MovieGenres.MovieID = 348396) http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  9. Query Construction, cont. • What condition should we enforce to get only those records from the two tables that match on the common attribute?(SELECT) • How many rows are returned now? • How can we remove the duplicate column? (PROJECT) • How else can we write the FROM line? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  10. Multiple-table queries:SQL INNER JOIN … ON • FROM t1, t2 takes full product of two tables: every row in t1 matched with every row in t2 • Have to restrict WHERE clause usually • FROM t1 INNER JOIN t2 ON t1.col1 = t2.col2 takes INNER JOIN of two tables: only rows meeting the ON condition returned • Second syntax is preferred • We can leave the WHERE clause for actual restrictions on the data • The FROM line will cover relationships http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  11. Multiple Table Queries in SQL • Construct the following queries • list the Movie Titles and Years in which Actor 90715 appeared [48] • How could we start with the Actor name instead? • list the Movie Titles and Years that Director 379723 made [61] • How could we start with the Director name? • Find the movieIDs for all the movies that have Klingon as a language [13] • How could we then find these MovieTitles? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  12. Homework • Check the link on the today page for homework to do before Day 8 • Reread Days 7 and 8 to understand relationships and INNER JOINs • Read Day 11 to learn about OUTER JOINS - how they differ from INNER and their use • Also read about "self" JOINs and understand their use • Practice problems using INNER JOIN are available http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

More Related