Database Design Assignment

This document guides you through designing a relational database for managing customer information, locations, and purchases. It covers best practices for table structure, relationships, and implementation using SQL and database tools, with practical prompts and step-by-step instructions.

Given Task

Imagine you are assigned to develop a database for a company to manage its customer information. The primary tables you will work with include:

• Customers: This central table will store detailed information about each customer, including fields such as name, email, and location, forming the core of the database.

• Locations: To improve the geographical understanding of your customer base, this table will contain details about customer locations, including state, country, and zip code.

• Purchases: Tracking customer purchases is essential for business insights. This table will record details such as the purchase date, amount spent, and other relevant information.

Your goal is to design well-structured tables that reflect the relationships between customers, locations, and purchases, ensuring an efficient and coherent database.

Throughout this lab, you will engage with ChatGPT to refine your design, culminating in a thoughtfully constructed table design for the customer database. Let’s begin this journey of database design and explore the potential that ChatGPT offers in organizing and managing information.

Prompts given to you to start exercise

1. Could you provide guidance on designing a database table for the company’s customers, including fields like name, email, and location? I’m looking to understand the best practices and considerations for structuring such a table effectively.
2. Could you demonstrate the structure of a database table of the company’s customers, outlining the fields like name, email, and location in a clear table format?
3. Could you advise on creating a separate table to store customer locations, specifically focusing on incorporating fields like state, country, and zip code?
4. Regarding the database structure, how should I design a separate ‘Purchases’ table to store information about customer purchases, such as the date of purchase, amount, and so on? What would be the best approach to linking this ‘purchases’ table to the main customer table for efficient data organization and retrieval?
5. Could you provide a consolidated table design that integrates all the discussed aspects, including the ‘Customers’ table with fields for name, email, and location; a separate ‘Locations’ table encompassing state, country, and zip code; and a ‘Purchases’ table containing purchase details like date and amount? How should these tables be structured and connected to ensure an efficient and organized database schema?

These are the prompts from the above automatically generated by GitHub Copilot

1. Customers Table Design: Start by designing the Customers table. Include fields such as customer_id, name, email, and any other relevant information you deem necessary. Ensure that the table is well-structured and captures essential customer details. Use best practices.
2. Locations Table Design: Next, design the Locations table. Include fields such as location_id, state, country, and zip_code. This table should provide geographical insights into customer locations.
3. Purchases Table Design: Finally, design the Purchases table. Include fields such as purchase_id, customer_id, purchase_date, amount, and any other relevant information. This table should capture details about customer purchases.
4. Relationships Between Tables: Define the relationships between the Customers, Locations, and Purchases tables. Ensure that the relationships are well-defined and reflect the connections between customer information, locations, and purchases.

How to implement the tasks using DBMS

• Install a database management system (DBMS) like MySQL, PostgreSQL, or SQLite. For this exercise, MySQL is recommended.
• Create a new database using the DBMS. Name it companyABC_db or any other suitable name.
• Design the tables based on the prompts provided.
  • Create three different tables: Customers, Locations, and Purchases.
• Define the relationships between the tables using primary and foreign keys.
  • Decide what would be your primary key for each table.
• Insert sample data into the tables to test the database schema.
  • Get help from ChatGPT or Github Copilot to generate sample data.
• Query the database to retrieve information and verify the relationships between the tables.
• Refine the database design based on the insights gained from the exercise.
• Document the database schema and relationships for future reference.
• Share your database design and insights with your team or supervisor for feedback and review.
• Iterate on the design based on feedback and further requirements.
• Implement any additional features or optimizations based on the feedback received.
• Maintain and update the database as needed to ensure data integrity and efficiency.
• Continue to refine and enhance the database design based on evolving business needs and feedback.
• Consider using database design tools or software to visualize and manage the database schema effectively.
• Explore advanced database concepts such as normalization, indexing, and optimization to improve the database performance and scalability.
• Stay updated on the latest trends and best practices in database design to ensure that your database remains efficient and secure.

Installing MySQL

1. sudo update
2. sudo install mysql-server

Starting MySQL

1. By default it should start when installed but if it does not use sudo systemctl start mysql.
2. To check the status use sudo systemctl status mysql.
3. To stop the service use sudo systemctl stop mysql.

Accessing MySQL

1. Use sudo mysql -u root to login to mysql.
2. When it starts it gives you a promt mysql to show you are in mysql.
3. You work typing after the prompt with commands like show databases;.

It is recommended to create a password for the database. To do so run this command at the prompt. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';. Replacing password with your password. Log out using EXIT; and log back in using sudo mysql -u root -p and enter your password.

Note: The command you type at mysql prompt ends with a ; and are written in uppercase.

Creating a Database

1. CREATE DATABASE companyABC_db;
2. It should return Query OK, 1 row affected.
3. To see if it has been created use SHOW DATABASES;.
4. To delete a database use DROP DATABASE companyABC_db;.

Create a Table using dbeaver GUI

• It involves connecting to a database and creating a table using the GUI.
• When you first use the GUI, it will download the right drivers for the database you are using.
• Look at the help to see how to connect to a database. And then create a table.
• If it was to be created with an script it would look like as shown below:

 1CREATE TABLE Customers (
 2    customer_id INT PRIMARY KEY,
 3    first_name VARCHAR(50),
 4    last_name VARCHAR(50),
 5    email VARCHAR(50),
 6    address Text,
 7    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 8    location_id INT,
 9    FOREIGN KEY (location_id) REFERENCES Locations(location_id)
10);

Creating a Location Table with SQL Script

1. To do so first choose a new script from dbeaver GUI.
2. Write the script as shown below:

1CREATE TABLE Locations (
2    location_id INT PRIMARY KEY,
3    state VARCHAR(50),
4    country VARCHAR(50),
5    zip_code VARCHAR(10)
6);

• The above scripts crate a table, to see the table in dbeaver, right click on the table and select View Data to see the data in the table. Or you can use the SELECT * FROM Locations; to see the data in the table. You would need to run the Query to see the data. To do so there is a green play button on the top right of the screen. Since there are no data in the table, it will return an empty table.
• Add Sample Data for Locations Table, add the following line into your script:

1INSERT INTO Locations (location_id, state, country, zip_code) VALUES
2(1, 'California', 'USA', '90001'),
3(2, 'Texas', 'USA', '73301'),
4(3, 'New York', 'USA', '10001'),
5(4, 'Ontario', 'Canada', 'M5H'),
6(5, 'Quebec', 'Canada', 'G1A');

• Run the script to add the data to the table.
• To see the data in the table, run the SELECT * FROM Locations; query.
• Once all tables are created and data added, you can run queries to see the data in the tables.

Querying the Database to verify the relationships between the tables

1. To see the tables are interconnected you can run the following query:

1SELECT c.first_name, c.last_name, l.state, l.country, l.zip_code, p.purchase_date, p.amount
2FROM Customers c
3JOIN Locations l ON c.location_id = l.location_id

• The above query will show the first name, last name, state, country, zip code, purchase date and amount of the customers.
• To see the data in the table, run the SELECT * FROM Customers; query.

Conclusion

A well-designed database is essential for efficient data management and business insights. By structuring tables for customers, locations, and purchases, and establishing clear relationships, you ensure data integrity and scalability. Leveraging tools and best practices, as outlined in this assignment, lays a strong foundation for future database development and optimization.

FAQ