Scribd
Upload
4 views41 pages

MySQL Database Setup for Quotes

This document provides an introduction and tutorial on how to create a basic MySQL database with tables, fields, relationships, and data to store quotes and related information. It demonstrates how to use phpMyAdmin to set up the database structure and tables for quotes, authors, topics, and relationships between them. Screenshots of the table structures and contents are suggested for homework.

Uploaded by

ROMMEL DORIN
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
4 views41 pages

MySQL Database Setup for Quotes

This document provides an introduction and tutorial on how to create a basic MySQL database with tables, fields, relationships, and data to store quotes and related information. It demonstrates how to use phpMyAdmin to set up the database structure and tables for quotes, authors, topics, and relationships between them. Screenshots of the table structures and contents are suggested for homework.

Uploaded by

ROMMEL DORIN
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd

Intro to MySQL

1
Open the XAMPP Control Panel and start the MySQL
service. Next click on the adjacent Admin button or
open a browser and enter the following address:
[Link]

2
Click on the Databases tab, and create a new database for quotes

3
Looking at a site like [Link], we realize that
there is a database behind the scenes.

4
Brainyquote entities
• We see items on the webpage like
– Quotes
– Authors
– Topics

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

6
Attributes and relationships
• Attributes:
– An author has an occupation, a nationality, a birth
date 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)

7
Click on the database you made and start to create a table for
Author data. Choose 7 columns. Click Go.

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

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

10
Enter some author data and click Go.

11
Result of “inserting” author data.

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

13
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.

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

15
Result of adding the primary key.

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

17
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.

18
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.

19
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.

20
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


21
After making the quoteQuote table,
click on Relationship View

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

23
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.

24
We just established a “foreign
key”.

25
Result of the quote insert.

26
Browsing the quotes.

27
Add at least one more author

28
Add at least two more quotes

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

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

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

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

33
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.

34
Insert a quoteTopic (or two or three)

35
Browse the topics

36
Insert some relationships between
quotes and topics.

37
Browse quote topics

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

39
Result of Designer

40
What to submit for homework
• 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.

41

Common questions

Powered by AI

Redundancy and typographical errors can be reduced by using separate tables for common attributes such as nationalities and professions. This allows for the selection of these attributes from predefined lists, thus minimizing human input errors. By linking these lists through foreign keys, one can ensure consistency and accuracy in data entry .

The relationship view in phpMyAdmin allows users to define and visualize the connections between tables through foreign keys. By using this view, a user can specify which columns in one table reference primary keys in another table, thus establishing a relational link. This is crucial for maintaining data integrity and establishing normalized database structures where relationships between entities are clearly defined .

The Designer tab in phpMyAdmin provides a visual interface for managing and structuring databases. It allows users to see a graphical representation of the tables and their relationships, aiding in understanding complex database schemas. This visualization can help users ensure that the database is correctly normalized and that relationships are properly established, which is beneficial for both development and maintenance .

In a database, a one-to-many relationship is represented by having a foreign key in the table representing the 'many' side of the relationship that points to the primary key of the table on the 'one' side. In the context of quotes and authors, each quote is attributed to a single author, making it a one-to-many relationship: one author can have many quotes. This is beneficial as it ensures efficient storage and retrieval, allowing for easy management of quotes and their attribution to the correct authors by referencing their unique IDs .

A junction table, also known as a bridge table, is used to establish many-to-many relationships between two entities in a relational database. This table contains foreign keys that reference the primary keys of the two related tables. In the case of quotes and topics, a junction table would maintain the relationship by recording which quotes pertain to which topics, thus allowing both quotes and topics to be associated with multiple instances of each other efficiently .

Auto-incrementing primary keys improve database management efficiency by automatically generating a unique identifier for each new record, reducing the need for manual input of identifiers and eliminating the risk of human error in creating unique primary keys. This simplifies the task of ensuring uniqueness and also facilitates related operations like indexing and searching .

A bridge table is suggested for establishing relationships between quotes and topics because it efficiently manages their many-to-many relationships. In this setup, the bridge table contains composite primary keys that are combinations of the primary keys from the related tables (quotes and topics). These keys serve both as primary and foreign keys, ensuring that each connection between a quote and a topic is unique and maintaining referential integrity .

Using the date data type for historical birth and death dates might present challenges because the range supported by this data type may not go back far enough to include accurate historical data. This limitation can lead to potential inaccuracies or the inability to store dates correctly, especially if they predate the supported range .

phpMyAdmin provides tools like the Browse tab, which allows users to view and manage the data within their database tables. This feature facilitates easy navigation through records, enabling users to verify data insertion and perform data queries. Additionally, the ability to view the data's structure and content aids in ensuring accuracy and consistency across the database .

Using VARCHAR to store attributes like nationality and profession offers benefits such as flexibility in accepting variable-length strings and efficient use of space for varying lengths of data entries. However, limitations include the potential for inconsistent data entries due to variations in spelling and capitalization, and it may allow for redundant data if the same entry is repeated with slight differences .

You might also like