SQL Database Design Tutorial for Beginners | Data Analyst Portfolio Project (1/3)
HTML-код
- Опубликовано: 25 июл 2024
- 👀 WATCH PART 2 HERE: • Custom SQL Query Tutor...
In this video, I show you how you’d go about designing and building a relational database from scratch. I take you step-by-step through the process and show you a cool tool you can use to make your job a lot easier.
This is part 1 of a 3 part series (WATCH THE FULL PROJECT HERE IN A SINGLE VIDEO: • Data Analyst Portfolio... ) that will show you how to build an awesome project for your data analyst portfolio. It’s a project that will demonstrate to any potential employers a load of different skills that you’d be required to have for working with SQL databases. In this video, we concentrate on building a relational database in MySQL and then in the next videos we’ll concentrate on writing SQL queries to create views of the database and then build out the dashboards.
Timestamps
00:00 Intro
01:12 Project brief
02:10 Orders data
08:37 Placing fake orders
09:30 Stock control data
11:13 Staff data
12:56 Export SQL code
13:42 Creating the database
14:05 Creating the tables
15:11 Outro
🛠 TOOLS USED IN THIS VIDEO
Quick Database Diagrams - geni.us/quickdbd
Navicat - navicat.com
🏠 JOIN THE LEARN BI ACADEMY - geni.us/lbi_academy
🚀 BECOME A BI ANALYST - geni.us/lbia_biasp
🏆 TAKE THE BI CONSULTANT MASTERCLASS - geni.us/lbia_bicm
🧰 TOOLS & SKILLS
Excel For BI Analysts - geni.us/lbia_excel
SQL For BI Analysts - geni.us/lbia_sql
Power BI For BI Analysts - geni.us/lbia_powerbi
Tableau For BI Analysts - geni.us/lbia_tableau
Looker Studio For BI Analysts - geni.us/lbia_lookerstudio
📊 DATA ANALYST PROJECTS
All Projects - geni.us/lbia_projects
Ben’s Pizzeria - geni.us/pizzaproject
Hotel Reservations - geni.us/hotelproject
Multi-Channel Marketing - geni.us/multichannelproject
Call Centre - geni.us/callcentreproject
🎥 RECOMMENDED VIEWING
7 Steps to Your 1st BI Analyst Job - geni.us/7stepstobivid
Learn Basic SQL in 15 Minutes - geni.us/learnbasicsql15-1
BI For Beginners Playlist - geni.us/biforbeginners-play
📕 FREE EBOOKS
7 Top Tips For Better Dashboard Design - geni.us/dashdes-ebook
Skills You Need To Succeed In BI - geni.us/biskills-ebook
7 Steps to Your 1st BI Job - geni.us/7stepstobi-ebook
📊 DATA HUBS & WEBSITES:
Power My Analytics - geni.us/YLc9Re
Windsor.ai - geni.us/windsorai
Kaggle - www.kaggle.com/
Statista - www.statista.com/
Data World - data.world/
DISCLAIMER: Some of the above links are affiliate links where I'll earn a small commission if you make a purchase using them, at no additional cost to you. It really helps support the channel so thanks in advance if you do use them 🙏
✅ LET'S CONNECT!
BUSINESS INQUIRIES - learnbi.online/contact
CONSULTING CALL WITH ADAM - geni.us/bookacall-adamfiner
JOIN MY MAILING LIST - geni.us/PSgRsF
FACEBOOK GROUP - geni.us/learnbi-fbg
#databasedesign #sqltutorial #businessintelligence #dataanalyst
Great Tutorial ! Eager to see the parts to follow this one. Keep up the good work.
Again ! This is a fantastic video with Clarity and Simplicity!!!! Thank you Thank you and Bravo !!!! magnifique!!!!
This is incredibly informative! Sweet, concise, and powerful. Keep up the good work!
Thanks, I’ll try!
Just waoo..! What a systematic approach.
everything ive learned from sql has come from you, GREAT WORK!
Thanks! Glad to help
Thank you so much!
Your teaching style is great, so clear and easy to understand.
Thank you!
Great Video! I will definitely be following along. Thank you.
You’re welcome
Fantastic! This is easily the simplest and best introduction to databases and SQL I have seen.... Thank you so much.....please keep up the great work!
Thanks for your feedback, much appreciated
@@LearnBIPlease provide the code files and the links in the description as soon as possible for free 🙏
Great Video! Thank you.
You’re welcome!
So to the point content. Amazing. Excitedly anticipating the further videos on this subject
Great! Don't forget to head on over to learnbi.online/pizza and place a fake pizza order!
hi, where can i get the exceldata?@@LearnBI
Just want to re-iterate the other comments. This is awesome stuff having all this straigh forward guidance in one place. Kudos!
Thanks for your feedback, you’re very welcome!
Great video!
Thanks!
Insanely beautiful content. 100/100
Insanely beautiful comment 🙏🏻
Thanks so much!
You're welcome!
thanks for helping start my db journey!
Happy to help!
Thanks!
You're welcome! Thanks for your support 😁
really enjoyed the video thank you so much
You’re welcome
Thank you so much for this. I’m just starting to learn SQL, and this is invaluable. Thank you, and keep up the good work 👍
My pleasure
Thank you very much it was very intutive and easy to understand with concepts of the use case
Glad it was helpful!
Thank you for the most strightforward database design! Hope you can update tutorial with cost of goods sold calculation.
Part 2 is already available: Custom SQL Query Tutorial For Beginners | Data Analyst Portfolio Project (Part 2)
ruclips.net/video/Yw3oqqkhohY/видео.html
10:08 Make it thing more complicated! 😀 We need that
The Quick Database Diagrams tool will definitely simply creating diagrams for each table and any relationships between them in a database.
thank you!🙏
You’re welcome!
@@LearnBI can i ask a question
Do you think is better to focus on SQL in order to achieve more expertise (more engineering or programming like jobs) & money in data world or I have a naive argument here!?
If your aim is to go for data engineering or programming roles then SQL is essential. But it's also a skill that a lot of people have so isn't as valuable as knowing other less common languages. Programming isn't my field though so I'm the wrong person to ask about it. When it comes to BI, you'll need SQL, Excel and BI tools as the essentials.
@@LearnBI thank you! 🙏
I am wondering whether the direction of the arrows matters. For example, when you drag the "staff_id" in the 'rota' table towards the "staff_id" in the 'staff' table you're implying that "rota.staff_id" will be a foreign key for "staff.staff_id". Shouldn't the direction be always from the table where the selected field is "Primary Key" towards the table where it is a generic field?
I was also confused by the relation between "rota.date" and "orders.created_at". From my understanding, the relation between tables should be "one to many"; however, dates can be repeated in both rota and orders table, which makes the relation "many to many". Could you clarify this point?
All will become clear in part 2 😉
Hello, thank you for the video. I’m extremely new to this whole world and have been taking the time to learn about building a database for a business. I have one question that has been confusing me, and I’m hoping you can help. If I build a database for a business. Do they have to install it on their computer and I run all the software from there login? Or do I create it on mine and the dashboard will show just their data. Thank you, and I hope this makes sense lol
Hi Mark, an RDBMS/databases can be installed on any computer. It does not need to be on the client's computers/servers. You can even rent an instance of most RDBMS in the cloud.
Couple questions - is it important to designate the difference between partial and total participation? Strong entities and weak entities? Should customer and staff have a super class because of their similar attributes? (first name, last name)
Totally unnecessary for our use case
Thanks for your simplicity, but I have a question, why did you give address_id an int data type mean while you gave item_id VARCHAR data type?
Good video, just confusing that you have set columns as INT but set data in those columns as varchars
waiting for part 2
It won’t be for another 2-3 weeks to try and get as big a dataset as possible. Don’t forget to make a fake pizza order at learnbi.online/pizza
You might want to explain one to many relationships for FKeys
It’s a good idea but it doesn’t really have any impact on what we’re trying to achieve with this project. I’m going to cover cardinality in a video coming soon 👍🏻
Amazing video! i had a problem i tried to edit the schema with my own project and now i don't know if i erased your project :(
😱
With the full picture of all the tables, im having trouble understanding in items table what is 'SKU'. Can you explain by breaking it down that how the tables Items, Recipe, ingredients and inventory are connected to each other how are these values related? BTW Thank you and awesome tutorial ❤
SKU is a product reference number. To understand the relationship between the tables, everything is explained in the videos
Quick DBD is useful but flawed when it comes to properly establishing relationships amongst tables.
great job in explaining,although i could not find the link for the excel file. is there a link where i can download the excel file?
I’m afraid not
this video was "best video of 2023 & beyond"
i think your the messi in data analyst!
High praise indeed! Thanks!
what software are you using? and can I do this with postgresql?
Links to the tools I used are in the video description. You can choose to export the database SQL to any RDBMS including PostgreSQL
Thank you Adam for this amazing video. A question. Is the back-end tables coming from the front-end tables? Meaning is the OLAP tables that you are building coming from the OLTP tables.
I’m not sure I understand the premise of your question. There is just a single database that contains these tables. No difference between front and back end.
best? tnhks
Is the navicat new database option available even if on trail version and not premium?
The trial version is no different from the full version
where did he get data (csv) he imported to the database tables?
I created the data sets myself. You can get hold of all the csv data at learnbi.online/pizzaproject
havent got it, how to write various items in the same order, exept for creating the same order as many times as many items it has. the same about ingredients. Can it be solved?
I'm sorry but I don't understand your question 😔
Great Tutorial can u please provide the csv files he uploaded
You can get your hands on the data here: LearnBI.online/pizzaproject
Hi, please how can i get the datasets to populate the sql tables
learnbi.online/pizzaproject
What are the names of the tables? Should I copy the information? Is it possible to download the excel spreadsheet?
Thank you very much. Good Information!
The Excel data is available as a paid download from here ➡️ www.learnbi.academy/courses/pizzeria-sql-project
where can I get your videos in a good quality? In youtube it is not very clearer.
In the settings ⚙️of the video, make sure it is set to 1080p 👍🏻
how long do you reckon it would take to make a database for map related review leaving app?
I’m sorry but I don’t understand your question
@@LearnBI my apologies, I'm looking to make a database for a site that uses maps with pin locations that show the average rating out of 5 stars. It's something akin to Google maps where you can rate certain locations, how long would a database like this take do you think. I didn't go full detail but that is the just of it.
@@hogarthheathan As you can see from the video, creating the database doesn't take very long at all, it's inserting the data that would take a lot of work. If you are starting from scratch, you would need to scrape data from the web using something like Python. But that's not my wheel house.
What editor are you using?
Navicat
Please how can I access the dataset used?
learnbi.online/pizzaproject
When I import the CSV files into MySQL for orders and rota tables, it says 0 records imported, and the tables are empty. Not sure why. All the other tables import just fine.
You don’t need to import the data files one you run the.sql file because the data is contained in that. Delete the database and start again.
@@LearnBI I figured it out. It was the datetime format of the csv. Thanks.
Sir from where to get data ?
In the video I explain that I set up a fake pizza ordering page where people placed fake orders. This is what created the data.
how to mention this project in our resume?
how would we describe the project in the resume ?
If you watch all 3 parts and follow the project to its conclusion, you’ll have all the information you need to describe the project.
Come on help us out lol @@LearnBI
please help me to resolve this error when I try to create the schema with PgAdmin (PostgreSQL)
ERROR: there is no unique constraint matching given keys for referenced table "orders"
SQL state: 42830
3 things. 1) the database we created is MySQL, not Postgres 2) some errors don’t stop you from creating and using the database, 3) Google is your friend
@@LearnBI
1) I am newbie and I am getting used to PostgreSQL.
2) I cannot create the database with that error
3) I have tried to ggl but still cannot understand, I am newbie, please kindly help
The code is in MySQL language, not Postgres. They are not the same.
how can i get the data file for this project. Anyone?
learnbi.online/pizzaproject
Great explanation but only 10 free tables, unless you upgrade to pro
Hi... do you have these course on udemy? (with real project and data like in real world) please give me the link. Thank You..
I have a complete e-learning platform at www.learnbi.academy
Where's the part 2?
Link in the description
what if we don't have Navicat and don't want to pay for it?
You can use MySQL Workbench, it’s free
@@LearnBI figured it out, thanks. Going to part 2 of your project. Any plans for a new project soon?
I think Adam - that the Pizza concept is one of the most fantastic Online Promo Marketing & Email List Growth - super scheme ever!!!! very funny and very smart!!!! Huge success for me!!!! Well Done Man!!!
Thanks. And thank you for making a complete completely ridiculous pizza order that I will now have to delete from the data set. Fool.
I started watching but stopped when I realised it was mysql
Thanks for your feedback, very helpful!
this guy sounds like the Kurgesagt guy
orders
-
row_id int pk
order_id varchar(10)
created_at datetime
item_name varchar(50)
item_cat varchar(50)
item_size varchar(20)
item_price decimal(5,2)
quantity int
cust_firstname varchar(50)
cust_lastname varchar(50)
delivery boolean
delivery_address1 varchar(200)
delivery_address2 varchar(200)
delivery_city varchar(50)
delivery_zipcode varchar(20)