SQL Database Design Tutorial for Beginners | Data Analyst Portfolio Project (1/3)

👀 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
START YOUR BI JOURNEY! - learnbi.online
🎥 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
🎓 RECOMMENDED COURSES
Power BI For Business Intelligence - geni.us/powerbi-udemy
Ultimate Guide to Google Data Studio - geni.us/vbi-edu
Google Data Analytics Course - geni.us/coursera_analytics
Learn SQL - geni.us/learnsql
SKILLSHARE - 30-day free trial! - geni.us/skillshare30day
UDEMY - geni.us/udemy_vb
📕 FREE EBOOKS TO DOWNLOAD
7 Top Tips For Better Dashboard Design
geni.us/dashdes-ebook
The Skills You Need To Succeed In Business Intelligence
geni.us/biskills-ebook
🧰 BI TOOLS
Power BI - powerbi.microsoft.com/
Tableau - www.tableau.com/
Data Studio - datastudio.google.com/
📊 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/
📕THE DATA VISUALISATION BIBLE - geni.us/iJTa8
🎥 MY KZbin GEAR
My Camera - amzn.to/3J5lbeo
Most Used Lens - amzn.to/3hXoE2Q
Main Key Light - amzn.to/366KaPI
Softbox - amzn.to/35HrR46
LED Panel Kit - amzn.to/35K3Lpf
Teleprompter - amzn.to/3hWGT8p
Main Mic - amzn.to/3t1n1aK
Lavalier Mic - amzn.to/3vVUi8V
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

[@angelleal3005]

Great Tutorial ! Eager to see the parts to follow this one. Keep up the good work.

[@aiasaiascon3894]

Again ! This is a fantastic video with Clarity and Simplicity!!!! Thank you Thank you and Bravo !!!! magnifique!!!!

[@lifesuccess1901]

Just waoo..! What a systematic approach.

[@alexisluna1751]

everything ive learned from sql has come from you, GREAT WORK!

[@LearnBI]

Thanks! Glad to help

[@anexusthanatos4021]

This is incredibly informative! Sweet, concise, and powerful. Keep up the good work!

[@LearnBI]

Thanks, I’ll try!

[@ahmedroberts4883]

Great Video! I will definitely be following along. Thank you.

[@LearnBI]

You’re welcome

[@framemaze3292]

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 👍

[@LearnBI]

My pleasure

[@kennethwaynescott8469]

Just want to re-iterate the other comments. This is awesome stuff having all this straigh forward guidance in one place. Kudos!

[@LearnBI]

Thanks for your feedback, you’re very welcome!

[@Aksinia1599]

Thank you so much! Your teaching style is great, so clear and easy to understand.

[@LearnBI]

Thank you!

[@vickarkhan]

So to the point content. Amazing. Excitedly anticipating the further videos on this subject

[@LearnBI]

Great! Don't forget to head on over to learnbi.online/pizza and place a fake pizza order!

[@jam5316]

hi, where can i get the exceldata?@@LearnBI

[@andresortiz3859]

Great Video! Thank you.

[@LearnBI]

You’re welcome!

[@Igzanda135]

thanks for helping start my db journey!

[@LearnBI]

Happy to help!

[@snehalbhartiya6724]

Insanely beautiful content. 100/100

[@LearnBI]

Insanely beautiful comment 🙏🏻

[@markwilhelm1304]

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!

[@LearnBI]

Thanks for your feedback, much appreciated

[@pandipatipavan3804]

​@@LearnBIPlease provide the code files and the links in the description as soon as possible for free 🙏

[@satishb9975]

Thank you very much it was very intutive and easy to understand with concepts of the use case

[@LearnBI]

Glad it was helpful!

[@silkyslim2111]

Thanks so much!

[@LearnBI]

You're welcome!

[@AMINE-dd1qy]

really enjoyed the video thank you so much

[@LearnBI]

You’re welcome

[@GenNextAnalyst]

Thank you for the most strightforward database design! Hope you can update tutorial with cost of goods sold calculation.

[@LearnBI]

Part 2 is already available: Custom SQL Query Tutorial For Beginners | Data Analyst Portfolio Project (Part 2) kzbin.info/www/bejne/j6iWoKSnoM2inrs

[@Skibadee99]

Thanks!

[@LearnBI]

You're welcome! Thanks for your support 😁

[@Kirankumar-ml1ro]

Great video!

[@LearnBI]

Thanks!

[@0x007A]

The Quick Database Diagrams tool will definitely simply creating diagrams for each table and any relationships between them in a database.

[@behrad9712]

thank you!🙏

[@LearnBI]

You’re welcome!

[@behrad9712]

@@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!?

[@LearnBI]

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.

[@behrad9712]

@@LearnBI thank you! 🙏

[@mahmoudelrasool8684]

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?

[@luvgupta7151]

this video was "best video of 2023 & beyond"

[@luvgupta7151]

i think your the messi in data analyst!

[@LearnBI]

High praise indeed! Thanks!

[@zacpaolo7845]

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)

[@LearnBI]

Totally unnecessary for our use case

[@BlakeC341]

Quick DBD is useful but flawed when it comes to properly establishing relationships amongst tables.

[@bralabala]

waiting for part 2

[@LearnBI]

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

[@renatojrf]

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?

[@LearnBI]

All will become clear in part 2 😉

[@beautyofthailand7393]

You might want to explain one to many relationships for FKeys

[@LearnBI]

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 👍🏻

[@smburhan6323]

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 ❤

[@LearnBI]

SKU is a product reference number. To understand the relationship between the tables, everything is explained in the videos

[@DanielCastillo-no5ot]

best? tnhks

[@markdebonis6803]

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

[@LearnBI]

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.

[@inyangemmanuel7323]

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?

[@LearnBI]

I’m afraid not

[@carltonseymour869]

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.

[@LearnBI]

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.

[@danielamoscoso23]

Is the navicat new database option available even if on trail version and not premium?

[@LearnBI]

The trial version is no different from the full version

[@danielcastillo9339]

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 :(

[@LearnBI]

😱

[@hogarthheathan]

how long do you reckon it would take to make a database for map related review leaving app?

[@LearnBI]

I’m sorry but I don’t understand your question

[@hogarthheathan]

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

[@LearnBI]

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

[@shaikazar2436]

Great Tutorial can u please provide the csv files he uploaded

[@LearnBI]

You can get your hands on the data here: LearnBI.online/pizzaproject

[@dehniseruby]

what software are you using? and can I do this with postgresql?

[@LearnBI]

Links to the tools I used are in the video description. You can choose to export the database SQL to any RDBMS including PostgreSQL

[@user-lo3pv8rk7t]

Great explanation but only 10 free tables, unless you upgrade to pro

[@george2326]

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)

[@worldofdata000]

Hi, please how can i get the datasets to populate the sql tables

[@LearnBI]

learnbi.online/pizzaproject

[@reforma44]

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?

[@LearnBI]

I'm sorry but I don't understand your question 😔

[@mdrahman-ju9qv]

where can I get your videos in a good quality? In youtube it is not very clearer.

[@LearnBI]

In the settings ⚙️of the video, make sure it is set to 1080p 👍🏻

[@Dipole__]

where did he get data (csv) he imported to the database tables?

[@LearnBI]

I created the data sets myself. You can get hold of all the csv data at learnbi.online/pizzaproject

[@arcaneknight9799]

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.

[@LearnBI]

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.

[@arcaneknight9799]

@@LearnBI I figured it out. It was the datetime format of the csv. Thanks.

[@vishalgoku2988]

how to mention this project in our resume? how would we describe the project in the resume ?

[@LearnBI]

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.

[@passportbro904]

Come on help us out lol ​@@LearnBI

[@temitopeolagoke]

Please how can I access the dataset used?

[@LearnBI]

learnbi.online/pizzaproject

[@devanshrajput3237]

Sir from where to get data ?

[@LearnBI]

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.

[@atnguyentien6997]

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

[@LearnBI]

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

[@atnguyentien6997]

@@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

[@LearnBI]

The code is in MySQL language, not Postgres. They are not the same.

[@arcaneknight9799]

what if we don't have Navicat and don't want to pay for it?

[@LearnBI]

You can use MySQL Workbench, it’s free

[@arcaneknight9799]

@@LearnBI figured it out, thanks. Going to part 2 of your project. Any plans for a new project soon?

[@aiasaiascon3894]

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!!!

[@LearnBI]

Thanks. And thank you for making a complete completely ridiculous pizza order that I will now have to delete from the data set. Fool.

[@ahmedfaraz9813]

how can i get the data file for this project. Anyone?

[@LearnBI]

learnbi.online/pizzaproject

[@DANNYEL20122]

Where's the part 2?

[@LearnBI]

Link in the description

[@cubsywubsy]

this guy sounds like the Kurgesagt guy

[@alexyakoveno4682]