1 / 41

Intro to MySQL

Learn how to create tables, establish relationships, and insert data in MySQL. Follow the step-by-step guide with screenshots.

fenton
Download Presentation

Intro to MySQL

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. Intro to MySQL

  2. Open a browser and enter the following address: http://csc-srv1.lasalle.edu:8080/ It should lead to a log in screen. If your name is George Washington, then your username will be c240washingtong If your student id is 1234567, then your password is 1234567was (MySQL usernames can only be 16 characters long. If the above prescription was longer than 16 characters, I stopped at 16. If your name is hyphenated, I only used the first part. )

  3. You should have access to 3 items on the left: Northwind, information_schema, and a database that matches your username.

  4. Looking at a site like brainquote.com, we realize that there is a database behind the scenes.

  5. Brainyquote entities • We see items on the webpage like • Quotes • Authors • Topics

  6. Choosing a quote reveals more: some of the attributes of the entities as well as some of the relationships between entities.

  7. Attributes and relationships • Attributes: • An author has an occupation, a nationality, a birthdate and death date. • Relationships: • A quote is attributed to just one author, but an author may have many quotes attributed to him/her. (Said to be a one-to-many relationship) • A quote might pertain to many topics, and a topic has many quotes that pertain to it. (Said to be a many-to-many relationship)

  8. Click on the database with your user name and start to create a table for Author data. Choose 7 columns. Click Go.

  9. Name the fields, choose their type, decode whether of not they can be null. (I also made the id field “auto-increment” A.I.)

  10. Scroll down and find the Save button. (The Go button is to add another column.)

  11. Enter some author data and click Go.

  12. Result of “inserting” author data.

  13. Click the Browse tab to see the results so far.

  14. Some decisions • I did not insert an ID because I chose the ID field to be “auto-incremented”. An ID should be unique so that it can serve as the primary key – a field which uniquely identifies each row/record in a table. • That reminds me I forgot to make the ID into the primary key.

  15. Click on the Structure tab, check the ID field, and click on the Primary key icon.

  16. Result of adding the primary key.

  17. Other decisions • The remaining fields were chosen to have the type of varchar with lengths of 30 or 20.

  18. Could do better • It might be better to have a list of nationalities (in another table) and choose the author’s nationality from the list rather than typing it in and risking more typo’s. • The same goes for the profession field.

  19. Dates can be tricky • You might have seen in the list of field types a “date type”. This choice would seem to be better for the author’s birth date and death date. But I have seen problems arise with more “historical” dates such as these. The dates supported by the date type don’t go back as far as one might like.

  20. Now for the quote table/entity • We will want • An ID to identify the quote • The text of the quote itself • A quoteAuthorID to establish the relationship between the author and the quote This number is probably the quote’s ID number in brianyquote’s database.

  21. Creating the quoteQuote table Create table, name it, add a column if necessary For quoteID choose PRIMARY for Index and AutoIncrement. For quoteAuthorID choose INDEX for Index. (This choice is necessary for establishing the relationship.) Click Go to Add a column Click save to make the table

  22. After making the quoteQuote table, click on Relationship View

  23. Use the drop-down list to say that the quoteQuote’s quoteAuthorID will come from the quoteAuthorID field of the quoteAuthor table. Click save.

  24. With the quoteQuote table selected, click on the Insert tab. Enter the text of the quote. The quoteAuthorID comes from a drop-down which forces it to match one of the authorID’s.

  25. We just established a “foreign key”.

  26. Result of the quote insert.

  27. Browsing the quotes.

  28. Add at least one more author

  29. Add at least two more quotes

  30. Next let us make a quoteTopic table with two fields an ID (primary key and auto-incremented) and a name (varchar 30)

  31. Next we will make a bridge/junction table to establish the many-to-many relationship between quote and topic.

  32. Creating the bridge table with two fields – the combination of the two will serve as the primary key.

  33. Go to Relation view on the “bridge” table.

  34. Use the drop-down to establish the connection to the other tables. In this bridge table the fields serve as both primary keys and foreign keys.

  35. Insert a quoteTopic (or two or three)

  36. Browse the topics

  37. Insert some relationships between quotes and topics.

  38. Browse quote topics

  39. Click on the database (as opposed to a table within the database). Then click on the Designer tab.

  40. Result of Designer

  41. What to submit for lab • Make a screen capture (by clicking Print Screen or Alt+Print Screen and pasting the result into a Word document) of the structure (the Structure tab) of your tables and the contents of your tables (the Browse tab). • Also make a screen capture of the Designer.

More Related