PostgreSQL 2019 MasterClass
Welcome to the 1st Part of the course entitled: << PostgreSQL 2019 MasterClass – From Database Design to WebApp Development >>
What you will learn in this course:
You will discover the wonderful world of relational databases through a real life example.
You will understand the core concepts of data modeling.
You will learn the major aspects and features of relational databases.
You will be able to perform all the steps required to create a well-designed relational database from business process analysis to database script generation:
How to develop an Entity-Relational diagram (ERD).
How to map an ERD to a relational model.
How to apply normalization process to the relational model.
How to convert a relational model to a physical model.
How to generate and complete the database SQL script.
In this course , the process of designing a relational database is taught step-by-step, such that anybody without any prerequisites will be able to perform this process in an easy way.
WHY SHOULD YOU LEARN HOW TO DESIGN A RELATIONAL DATABASE ?
To develop a software application, you need to use a storage structure to manage data. Obviously, you can use spreadsheets or flat files, but :
What if you want to enable concurrent access by many users of the software application ?
What if you want to grant different access rights to those users?
What if you want to add over a million of records and keep a high level of performance?
What if you want to implement data integrity check?
What if you want to prevent users from typing redundant data?
In order to address the limitations encountered by spreadsheets and flat files, you have to build a well-designed database.
Database design is a fundamental step for building nearly any software application (website, web application, desktop application and mobile application).
Apart from improving the performance, well-designed databases are more scalable, more consistent, easy to maintain, and cost effective in terms of disk storage space.
To further motivate you to learn databases in terms of career development, database administrator, database manager and database developer were ranked in the top 10 highest paying big data careers in United States on july 2017 ( according to Northeastern University).
Relational Database Management Systems are important for Big data. Most companies need to rely on the data stored in relational databases to create the highest level of value to the business with big data.
NoSQL databases used in big data are also sometimes called “Not only SQL” to emphasize that they may sit alongside relational database (SQL DB) in a polyglot persistence architecture.
Also, Relational Database Management Systems are more suitable for the Central Data Warehouse role than multidimensional databases, because they are supported by many third-party vendors, they are compatible with existing standards at the organization, and they are easier to maintain and administrate.
In addition, having no data size constraints, they are mandatory when the data warehouse seems to grow to hundreds of gigabytes or larger.
As you can see, mastering the concepts and techniques of Relational Database Management Systems is very important for those who work in the field of Information systems.
Even if you are interested to the newest techniques, you need to understand the basics of data modeling in general and the fundamentals of relational databases technology in particular. This will help you to combine some features of these new technologies with some features of the RDBMS and also to perform data migration processes.
This lecture will give you a synthetic overview on databases, their purpose and the quality of the data stored.
This lecture will help you to understand the different data models performed to build databases and the concept of data abstraction.
In this lecture, you will discover the most famous data models performed by database developers.
In this lecture, you will discover the characteristics of the relational databases.
This lecture will help you to understand the structure and the features of a relational database management system.
In this lecture, you will find a clear explanation of the concept of information system.
Business process analysis
You will learn how to perform the business process analysis when gathering the system requirements.
This real example will help you to consolidate the knowledge acquired throughout the previous lecture,
The Entity-relationship diagram is the conceptual model which is the most suitable for the relational databases.
You will discover a free web-based tool used to draw an entity relationship diagram, convert it to a relational model and generate a SQL script.
You will create the entities and their relationships using the Entity-Relationship Diagram notation.
You will add the identifiers and other attributes of the entities.
You will set up the cardinalities and participation types of the entities.
Finally, you will add the attributes of the many-to-many relationships.
You will learn the main components of a relational schema.
You will learn how to map an ER diagram to a relational model.
You will learn how to apply the normal forms to the relational model.
You will be able to convert an ER-Diagram into a relational model.
Physical data model
You will learn the rules you should apply to the relational model in order to transform it into a physical data model.
You will learn to add data properties details to a relational model in order to be converted into a physical schema.
You will learn to add data constraints details to a relational model in order to be converted into a physical schema.
You will see how to install PostgreSQL11 on a Windows10 machine.
Database script generation
You will know the two methods of generating a database script.
Finally, you will be able to generate the database SQL script and create the database tables on PostgreSQL11 using PgAdmin4.