1 / 18

Mastering MySQL

Mastering MySQL. How many authors, books, and customers are in the database ? Which author wrote a certain book ? Which books were written by a certain author ? What is the most expensive book ? What is the best-selling book ? Which books have not sold this year ?

emmet
Download Presentation

Mastering 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. Mastering MySQL

  2. How many authors, books, and customers are in the database? • Which author wrote a certain book? • Which books were written by a certain author? • What is the most expensive book? • What is the best-selling book? • Which books have not sold this year? • Which books did a certain customer buy? • Which books have been purchased along with the same other books? DatabaseDesign

  3. every table will be designed around some object that you’re likely to search for a lot—an author, book, or customer, in this case • Most of the time, you’ll create an arbitrary key for this purpose, using AUTO_INCREMENT Primary Keys: The Keys to Relational Databases

  4. The process of separating your data into tables and creating primary keys is called normalization. • if you list the titles of books in the authors table as well as the books table, and you have to correct a typographic error in a title, you’ll have to search through both tables and make sure you make the same change every place the title is listed Normalization

  5. A highly inefficient design for a database table

  6. There should be no repeating columns containing the same kind of data • All columns should contain a single value • There should be a primary key to uniquely identify each row First Normal Form

  7. Second Normal Form

  8. Third Normal Form

  9. Third Normal Form

  10. Third Normal Form

  11. Third Normal Form

  12. Normalization requires spreading data across multiple tables, and this means making multiple calls to MySQL for each query. On a very popular site, if you have normalized tables, your database access will slow down considerably once you get above a few dozen concurrent users, because they will be creating hundreds of database accesses When Not to Use Normalization

  13. MySQL is called a relational database management system because its tables store not only data but the relationships among the data. There are three categories of relationships Relationships

  14. One-to-One

  15. This one-to-many relationship is also the preferred scheme to use when describing a many-to-one relationship, in which case you would normally swap the left and right tables to view them as a one-to-many relationship. One-to-Many

  16. Many-to-Many

  17. In some applications, it is vitally important that a sequence of queries runs in the correct order and that every single query successfully completes. For example, suppose that you are creating a sequence of queries to transfer funds from one bank account to another. Transactions

  18. In order to be able to use MySQL’s transaction facility, you have to be using MySQL’s InnoDBstorage engine. This is easy to do, as it’s simply another parameter that you use when creating a table. Enter the Example in 9-1. on page 215 Transaction Storage Engines

More Related