Entity-Relationship Diagrams and Basic SQL

ERD Creation

The goal of this exercise is to practice creating an Entity-Relationship Diagram (ERD) for the pcshop database.

Preliminaries

Make sure you have completed Lab 1 and you have imported pcshop.sql into your MySQL database.
Make sure you have watched the first video in the self-study area.

ERD Creation

We will use Mermaid.js or optionally draw.io to create an ERD for the pcshop database. For Mermaid.js, you can use any online editor like Mermaid Live Editor, or integrate it directly into your Markdown files if supported.

Familiarize yourself with the basic syntax of Mermaid.js for creating ER diagrams. Here’s a simple example to get you started:

erDiagram
      CUSTOMER ||--o{ ORDER : places
      ORDER ||--|{ LINE-ITEM : includes
      CUSTOMER }|..|{ DELIVERY-ADDRESS : uses

Using the pcshop.sql database schema, identify the main entities and their relationships. For draw.io, visit https://www.draw.io and choose to create a new diagram.
Begin diagramming the pcshop database by creating entities for tables like products, pcs, laptops, printers, customers, and sales. Add attributes to each entity that represent the columns in each table.
Connect the entities with relationships and define the cardinality based on the following assumptions about the database pcshop:

Assumptions: Table products lists all products: PCs, laptops, and printers. Tables pcs, laptops, and printers contain some characteristics of the PC, laptop, and printer products. Each sale in the table sales refers to a model in table products and a customer in table customers.

Once your ERD is complete, you can export it as an image or PDF from the Mermaid editor or draw.io to include in your documentation.

Practice Exercise

In your own time, use Mermaid.js or draw.io to draw an ERD for a simple library database with entities book, author, library_user, and loan. Add a few attributes (of your choice) for each of the four entities and connect the entities with links to represent the relationships between them.

When ready, if you’re using draw.io, click on Export to generate an image of your diagram. For Mermaid.js, you may simply save the text of your diagram.

SQL

The goal of this exercise is to practice single-table queries in SQL.

1. Review the slides of Week 3.
2. Login to MySQL and make sure you have the tables products, pcs, laptops, printers, customers and sales in your database.
3. Click on the SQL tab in phpMyAdmin. Here you can write and execute SQL statements.
4. Write SQL SELECT statements for performing the following queries:
- a. List the model numbers of all products made by maker B.
- b. List the model numbers of all products not made by maker B.
- c. List the model numbers of all PCs with speed >= 3.00.
- d. List the model numbers of all color laser printers.
- e. List the customer IDs of all customers who paid for a product with a visa card (debit or credit).
- f. List the first names, the last names, the city and the street address of all customers who have the letter ‘e’ either in their first name or in their last name.
- g. List all attributes of the transactions (from table sales) made between the 18th and the 20th of December 2013 (including the 18th and the 20th).
- h. List all attributes of the transactions (from table sales) made either before the 18th or after the 20th of December 2013.
- i. Assume all prices in table Laptops are in Euro. List the model numbers of all laptops with ram at least 1024. For each model, list also its price in USD. Assume that 1 USD = 0.85 EURO.

Advanced SQL

The goal of this exercise is to practice joins and subqueries in the SQL SELECT statement.

1. Review the slides of Week 4.
2. Login to MySQL and make sure you have the tables products, pcs, laptops, printers, customers and sales in your database.
3. Click on the SQL tab in phpMyAdmin. Here you can write and execute SQL statements.
4. Write SQL SELECT statements for performing the following queries. Use either a join or a subquery. If a question is asking to include attributes from one table only in the SELECT clause of the SELECT statement, try to find two solutions, one with a join, and another with a subquery.
- a. List all PC models that have been sold at least once.
- b. List the makers of laptops with speed of at least 2.00.
- c. List all pairs of PC models that have both the same speed and ram. A pair should be listed only once; e.g., list (i, j) but not (j, i).
- d. List the makers that make at least two different models of PC.
- e. List the maker(s) of the laptop(s) with the highest available speed.
- f. List the cities with customers who bought a printer.
- g. List the makers of PCs that don’t make any laptop (but may make printers)
- h. List the makers of PCs that don’t make any laptop or printer
- i. List the model numbers and prices of laptops which are cheaper than at least one PC
5. Write SQL SELECT statements which use outer joins for performing the following queries.
- a. List all makers, all model numbers they make, and the corresponding product types (i.e. the entire maker, model and type columns from table Products). For the PC models only list their speed and price as well in two additional columns. These two extra columns should contain NULL markers for laptop and printer models.
- b. List all laptop model numbers and only for those made by makers A and B list also their price. The prices of laptops not made by either A or B should be NULL.
- Hint: List the laptop model numbers made by A and B and their prices. Use the result of this query as a subquery in the FROM clause of the main query.
- c. List the names (first name and last name) of customers who haven’t bought any product. Use an outer join. Can you solve it with a subquery instead?
- Hint: In the solution that uses an outer join, use ‘IS NOT NULL’ in the WHERE clause.
Note: There are no such customers in the example database. Insert a new customer into table customers to test your solution.

--- title: "Entity-Relationship Diagrams and Basic SQL" --- ```{r setup, include=FALSE} ``` # ERD Creation *The goal of this exercise is to practice creating an Entity-Relationship Diagram (ERD) for the pcshop database.* ## Preliminaries 1. Make sure you have completed **Lab 1** and you have imported [pcshop.sql](/lab/pcshop.sql) into your MySQL database. 2. Make sure you have watched the first video in the self-study area. ## ERD Creation 3. We will use Mermaid.js or optionally draw.io to create an ERD for the pcshop database. For Mermaid.js, you can use any online editor like [Mermaid Live Editor](https://mermaid-js.github.io/mermaid-live-editor/), or integrate it directly into your Markdown files if supported. 4. Familiarize yourself with the basic syntax of Mermaid.js for creating ER diagrams. Here's a simple example to get you started: ```mermaid erDiagram CUSTOMER ||--o{ ORDER : places ORDER ||--|{ LINE-ITEM : includes CUSTOMER }|..|{ DELIVERY-ADDRESS : uses ``` 5. Using the `pcshop.sql` database schema, identify the main entities and their relationships. For draw.io, visit [https://www.draw.io](https://www.draw.io/) and choose to create a new diagram. 6. Begin diagramming the pcshop database by creating entities for tables like `products`, `pcs`, `laptops`, `printers`, `customers`, and `sales`. Add attributes to each entity that represent the columns in each table. 7. Connect the entities with relationships and define the cardinality based on the following assumptions about the database pcshop: *Assumptions:* Table **products** lists all products: PCs, laptops, and printers. Tables **pcs**, **laptops**, and **printers** contain some characteristics of the PC, laptop, and printer products. Each sale in the table **sales** refers to a model in table **products** and a customer in table **customers**. 8. Once your ERD is complete, you can export it as an image or PDF from the Mermaid editor or draw.io to include in your documentation. ## Practice Exercise 9. In your own time, use Mermaid.js or draw.io to draw an ERD for a simple library database with entities **book**, **author**, **library_user**, and **loan**. Add a few attributes (of your choice) for each of the four entities and connect the entities with links to represent the relationships between them. When ready, if you're using draw.io, click on **Export** to generate an image of your diagram. For Mermaid.js, you may simply save the text of your diagram. # SQL *The goal of this exercise is to practice single-table queries in SQL.* - **1.** Review the slides of Week 3. - **2.** Login to MySQL and make sure you have the tables products, pcs, laptops, printers, customers and sales in your database. - **3.** Click on the SQL tab in phpMyAdmin. Here you can write and execute SQL statements. - **4.** Write SQL SELECT statements for performing the following queries: - **a.** List the model numbers of all products made by maker B. - **b.** List the model numbers of all products not made by maker B. - **c.** List the model numbers of all PCs with speed \>= 3.00. - **d.** List the model numbers of all color laser printers. - **e.** List the customer IDs of all customers who paid for a product with a visa card (debit or credit). - **f.** List the first names, the last names, the city and the street address of all customers who have the letter 'e' either in their first name or in their last name. - **g.** List all attributes of the transactions (from table sales) made between the 18th and the 20th of December 2013 (including the 18th and the 20th). - **h.** List all attributes of the transactions (from table sales) made either before the 18th or after the 20th of December 2013. - **i.** Assume all prices in table Laptops are in Euro. List the model numbers of all laptops with ram at least 1024. For each model, list also its price in USD. Assume that 1 USD = 0.85 EURO. # Advanced SQL *The goal of this exercise is to practice **joins** and **subqueries** in the SQL SELECT statement.* - **1.** Review the slides of Week 4. - **2.** Login to MySQL and make sure you have the tables products, pcs, laptops, printers, customers and sales in your database. - **3.** Click on the SQL tab in phpMyAdmin. Here you can write and execute SQL statements. - **4.** Write SQL SELECT statements for performing the following queries. Use either a join or a subquery. If a question is asking to include attributes from one table only in the SELECT clause of the SELECT statement, try to find two solutions, one with a join, and another with a subquery. - **a.** List all PC models that have been sold at least once. - **b.** List the makers of laptops with speed of at least 2.00. - **c.** List all pairs of PC models that have both the same speed and ram. A pair should be listed only once; e.g., list (i, j) but not (j, i). - **d.** List the makers that make at least two different models of PC. - **e.** List the maker(s) of the laptop(s) with the highest available speed. - **f.** List the cities with customers who bought a printer. - **g.** List the makers of PCs that don't make any laptop (but may make printers) - **h.** List the makers of PCs that don't make any laptop or printer - **i.** List the model numbers and prices of laptops which are cheaper than at least one PC - **5.** Write SQL SELECT statements which use **outer joins** for performing the following queries. - **a.** List all makers, all model numbers they make, and the corresponding product types (i.e. the entire maker, model and type columns from table Products). For the PC models only list their speed and price as well in two additional columns. These two extra columns should contain NULL markers for laptop and printer models. - **b.** List all laptop model numbers and only for those made by makers A and B list also their price. The prices of laptops not made by either A or B should be NULL. - **Hint:** List the laptop model numbers made by A and B and their prices. Use the result of this query as a subquery in the FROM clause of the main query. - **c.** List the names (first name and last name) of customers who haven't bought any product. Use an outer join. Can you solve it with a subquery instead? - **Hint:** In the solution that uses an outer join, use 'IS NOT NULL' in the WHERE clause. - **Note:** There are no such customers in the example database. Insert a new customer into table customers to test your solution.