# What is a schema? The database schema is the structure behind data organization. It is the overall design of the database. It defines how the data is organised and how the relations among them are associated . --- A database schema is a way to logically group objects such as tables, views, stored procedures etc. --- ### Schema diagram If we want to make one,in a schema diagram, all database tables are designated with unique columns and special features, e.g., primary/foreign keys --- Schema diagrams have an important function because they force database developers to transpose ideas to paper. This provides an overview of the entire database ---  --- ## Why is it important * The schema gives an overview of the data in the database (for example, of the present tables and their connections). This makes it easier to keep track of what information is and is not present in the database and what it represents, not only for those who created the database, but also for those who have to use and maintain it later on. * Data integrity --- It is maintaining and assuring the accuracy and consistency of data over its entire life-cycle. Data integrity means that the data is accurate and reliable. ___ The schema contains the database integrity constraints that need to be maintained by the DBMS(database management system). Without such constraints it is up to the applications and users to guard the integrity of the data in the database, which in many cases can lead to unacceptable risks of data inconsistency. ___ # Why do we need a primary key? The role of a Primary Key (PK) is to provide a unique identifier to each row in a table. --- --- That is why it cannot be null. The PK is mandatory in order to define a Foreign Key (FK) relationship between a child table and a parent table --- --- A Unique Key is used when you want to prevent duplicate values; i.e. to impose the uniqueness constraint. It is NOT a replacement or equivalent of a PK. For instance, a table in a data warehouse (DW) may store the identifier of the row in the source system and you want to ensure that you ingest that row only once. --- --- Primary key should :: <li> uniqe</li> <li> NOT NULL</li> <li> should never change </li> --- --- # Why are primary keys important when constructing a database? --- --- Theoretical - each row in a relational database must be uniquely identified. --- --- If it isn’t, the database isn’t “relational”, and many ambiguities can happen. This can have many practical implications, such as join queries producing duplicate rows. --- Practical I - You nearly always want to look up or join rows in a table using some sort of unique identity. That unique identity may as well be the primary key. --- --- Practical II - MySQL InnoDB requires a primary key column in order to store and index data. If you don’t have an explicit PK, InnoDB will have to generate one for you. --- This key value will be “invisible” to you, but is used to organize the base table storage and is stored in indexes. And this key value is often much physically bigger than an “auto-increment” INT or BIGINT column. --- --- # Should a database table always have a primary key? always! If there is none given by the data, generate an artificial one which is technically created and secured. ---