Database Design Course Overview

    What is a Database?

    Database refers to an organised collection of data curated to negate inconsistencies and make data management easier. For example, a collection of information such as marks of different subjects for some students might be challenging to deal with if not for data collection to manage comprehensively. A large amount of data requires several databases, which often grow into an unmanageable amount as well. This is where DBMS assists the database in managing containing information.


    The database uses DBMS or database management system, and most prominently RDBMS, a relational database management system to maintain neat databases. RDBMS secures the database using tables and also contains the relationship between containing data. The system helps evaluate data to manage, modify, and regulate it per the user’s requirements. These systems use the SQL language to manage databases.

    Why Database Design?

    Database production follows a detailed process which requires the management system to follow a set pattern to design data tables, its storing process, regulation and implementation for the stored data. This process is known as database designing. A database design consists of detailed operations to design, develop, test, implement and maintain created database models for a reliable database system. Consistency in databases is crucial to comprehending the relation between containing data and their usefulness. 

    But why database design is important?


    Database design under the RDBM system extends diverse benefits to users. Here’s why database designing is important. 

    • Database designing works as a blueprint for data storage, defining how data would be saved in the modelled system. This will enable programmers to assess the influence of a database on an application’s performance. 

    • Database design evaluates whether the model meets user requirements or not.

    • Database designing operations allow users to assess application behaviour and how data requests will be processed in the future.

    • Database design, working as a blueprint, would enable designers to work on constraining points and resolve the relevant points for higher efficiency.

    What is the process of Database Design?

    Developers work on creating database design with the vision to deliver robust databases capable of delivering identified objectives without hindering application efficiency. To do so, certain steps are required to be followed. Database designers work on several steps using database management systems to create reliable databases.
    process of database designingLet us look at these steps that accomplish the process of database designing.

    1. Identifying objectives and purposes of the database: The data design process starts with defining the objective of the database. Deriving the purpose beforehand helps developers prepare a plan following its scope and provides a direction to the process. 

    2. Searching and organising relevant information for the database: Collect the required information to be compiled. For example, figures such as a product’s information, order number and other relevant details.

    3. Separating information using tables: Divide the information into major subjects such as product name and order name, creating a separate table for each. 

    4. Identify the information and divide it into columns: The user must identify which information must be included in every table. Every added information gets displayed as a column within the table after turning into a unique field. For example, a student’s table might contain columns such as marks and subjects. 

    5. Specify the primary key for each table: Specify the primary key for each table, which is a column or a group of many columns used to identify every unique row. An apt example would be the marks obtained by students. 

    6. Establish the relationship between each table: Identify interrelationships in each table through analysis and fill additional fields or new tables to refine the relationship if necessary. 

    7. Implement normalisation rules: Data normalisation rules are essential to check the quality of table structure. Make changes if you find any inconsistencies.

    8. Use DBMS to build the database: Follow the derived blueprint and implement the model using a database management system to design the database.

    Reassess for quality check: Assess the created design for any constraints or errors. Run several tests on the tables to evaluate their efficiency and reduce redundancy and inconsistencies.

    The Language Used to Access a Database is Called SQL

    Database management system requires users to practice specialised database languages explicitly created to access database management systems. Standard Query Language (SQL) is a domain-specific language that works with RDBMS to access, store, manage, manipulate, and maintain databases. The standard database language follows a set pattern of grammar and syntaxes to communicate with relational databases to work with query data. 


    Although a standard language, various relational database management systems create their unique subset of SQL to interact with databases and regulate their content. SQL is a predominant query language most commonly paired with the relational database management system called MySQL. Let’s learn more about MySQL to understand how the pair helps create robust databases

    Introduction to MySQL

    Initially created in 1995 for personal use, MySQL worked as a backbone for many organisations, managing its database system to ensure an easy flow of operations. MySQL is a free, open-source relational database management system which works with the standard version of the famous SQL language to support enormous databases. MySQL helps design, develop and implement database tables following interrelationships within containing information. The program implements a relational database in the computer by interacting with the operating system to regulate user access to databases and facilitate various tasks like testing database integrity. 


    Several features add to its success, being one of the top relational database management systems available in the market. 


    • MySQL is free, easy to use, and works with most operating systems.

    • MySQL’s open-source license enables programmers to access the functionality of the most expensive and robust database packages. 

    • Besides SQL, MySQL works with languages such as PHP, JAVA, C, C++, etc.

    • MySQL is comparatively faster than other RDBMSs with the ability to regulate huge data mounds, enabling up to 50 million rows in a single table and more

    Installing MySQL

    Getting one of the most popular relational database management systems is easy. Just follow the given installation steps to get started with MySQL!


    Step 1- Go to the official MySQL website and download the community server edition of the software.


    Step 2- Choose your operating system and download the relevant setup from the given options. Unzip the downloaded setup and double-click the MSI installer .exe file. Wait for the process to end until the next wizard appears.


    Step 3- As the process ends, choose the Setup Type and select the Full Type before proceeding ahead and clicking the Next button. This step will enable the installation of features such as MySQL server, MySQL Router, MySQL Shell, MySQL Connectors, MySQL Workbench and more. 


    Step 4- The next step will assess your system for the lack of requirements. If your system lacks a few features, press the Execute button to get those automatically. You can also choose to Skip to deal with it later. Press Next to proceed.


    Step 5- If you’re skipping installing certain features on the system, click Yes on the next wizard informing you of the missing requirements. The page with features needed to be installed will open up as you proceed. Press Execute to download the features. After installing the features, click on Next.


    Step 6- Next step would require the user to configure the server and router. Click on the Next button. On the next page, choose Standalone MySQL Server/ Classic MySQL Replication or InnoDB Cluster according to your requirements. 


    Step 7- Complete the Config Type and Connectivity options on the next page by choosing Config Type as ‘Development Machine’, Connectivity as TCP/IP and Port number 3306. Click Next and select your preferred authentication method.


    Step 8- Create a MySQL Root Password and click the Next button. Configure the Window service in the next step to start the server. Keep the default setup and click on Next. 


    Step 9- Apply the Server Configuration as your choice and click Execute. As the configuration completes, click on the Finish button. 


    Step 10- As the Product Configuration appears completed, maintain the default setting and click on the Next -> Finish button to complete package installation.


    Step 11- You can choose to configure the routerin the next wizard. Click on Next-> Finish and then the Next button. As you see the Connect to Server option, mention your Root Password from before. Click on Check if your connection is successful, and then click Next. 


    Step 12- Next, select applied configuration and click on Execute. Click on the Finish button on the next page.


    MySQL installation is complete as you click on the Finish button

    The Basic Commands of MySQL: Insert, Select, Update, and Delete

    MySQL comprises a list of basic commands to assist database design development and management for smooth performance. Here’s a list of basic MySQL commands.


    SELECT: The option extracts the data required from the database to show it in the results.


    UPDATE: The option records new data in a table. 


    CREATE: The option creates a table in the database. Users can specify names and columns in the table.


    INSERT: The option assists in inserting data into the containing database tables.


    ALTER: Alter allows changing the structure of any table by adding or removing columns.


    DELETE: Delete option enables users to delete the containing records in the database. 


    These are the basic commands of MySQL. Other than the fundamentals, commands like DROP, CHECK, FROM, WHERE, HAVING, GROUP BY, AVG(), ORDER BY, AS, LEFT JOIN, INNER JOIN, COUNT, FULL OUTER JOIN, and LIKE are a few other commands significant to operate on database.

    Data Definition Language (DDL) Statements in MySQL

    DDL or Data Definition Language statements is a set of SQL statements that deal with entire database schemas rather than individual rows or tables to specify how data must reside within the database. The comprising elements of this language include Create, Alter, Truncate, Drop, Rename, and Comment, regulating the logical structures of the database. These elements help create and manage a table or row in the MySQL database design process. DDL is known to be about ‘Metadata’.

    Data Manipulation Language (DML) Statements in MySQL

    DML or Data Manipulation Language statements manage data within schema objects by manipulating and modifying data. It does not manipulate database objects or schema but the containing information. Hence, it comprises the most common SQL statements such as Update, Select, Insert, Merge, Delete, Explain Plan, Call, and Lock Table. Users can insert, alter, remove or further manipulate data using the features under the DML subset statements of SQL

    Data Control Language (DCL) Statements in MySQL

    DCL or Data Control Language statements work with the database system's rights, controls, and other permissions. DCL regulates database transactions concerning security concerns. The DCL statements in MySQL comprise commands such as Grant and Revoke. These commands regulate access to the database and enable the user to grant or revoke access per the requirements.


    These SQL statements are the key to delivering valid commands to the database system to process requests and redeem required results. DDL, DML, and DCL are a few of the many subgroups of SQL in MySQL. These include Transaction Control Statements, Session Control Statements, System Control Statements and Embedded SQL Statements.

    What are the basic terms used in MySQL?

    An introduction to database design in MySQL is incomplete without learning the basic terms of MySQL. Take a look at these basic terminologies to get started with MySQL!

    • Database- Refers to a collection of data curated in tabled format. 

    • Table- Refers to a specific set of rows and columns containing information. This table follows a simple spreadsheet-like format.

    • Row- A row, also known as entry, tuple or record, is a group of relevant data related to each other. 

    • Column- Refers to a data element containing the same data. 

    • Primary Key- A primary key holds a unique value which cannot occur twice in the database. Users can easily find their relevant content table with just one primary key, appearing only once in a table. 

    • Foreign Key- Foreign key works as a linking pin between two tables. 

    • Unique Key- A combination of one or more columns, a unique key can uniquely identify a row from the table. Unique keys cannot occur twice in the table, excluding rows with a null value in key columns. Though, a table can have more than one unique key. 

    • Compound Key- A compound or composite key comprises multiple columns as a single column lacks uniqueness. A compound key can also be perceived as similar to a unique key but with more columns.  

    • Index- Index refers to a physical data structure of a database similar to the ones present in the book that help find certain information from the enormous database. 

    • Referential Integrity- Referential integrity refers to and manages the relationship between data across multiple tables. This feature ensures that the value of that foreign key always points towards a present row.

    How are Databases structured?

    After knowing what databases are, how they are designed and what platform they use to manage data, it is time to unreel how databases are structured. 


    Following the steps of database designing mentioned above, blueprints are created to follow the database model and include the objective features to optimise the structure. In order to create a systematic database, tables are introduced in the model. These tables contain several columns and rows to separate, containing information systematically, based on their relationship. A spreadsheet is a great example of understanding the tabled structure of a database. A list of data can be converted into tables using introducing different fields in separate tables. 


    For example, to insert name, last name, age and marks obtained by a student, you might want to get these fields into separate tables. Tables are divided into rows and columns where rows contain the data record and columns include data relevant to the field in rows. 

    Each column type gets relevant data types assigned to maintain consistency. Some of them are-

    • FLOAT: Contains floating-point numbers

    • CHAR: Contains a specified length of texts

    • TEXT: Contains lengthy texts

    • VARCHAR: Contains texts of variable lengths

    • INT: Contains negative or positive whole numbers

    • BLOB: Contains binary data

    • AUTONUMBER: The data type automatically generates a unique number for every column

    Each table gets specified primary keys, which work as database attributes working as a unique identifier for the entered data, so picking information from the database is not a trouble in future. Primary keys are required to be unchanging and unique so finding information is always simple. 

    While creating a database, users must use SQL's logical and physical data structures in MySQL. Test and run assessments before deploying the database to comprehend its efficiency, quality and performance level. 

    Defining a Database

    Relationships

    Relationships in database design with MySQL refer to the relation between tables. Users comprehend the cardinality of comprising data to gauge database efficiency. Cardinality refers to the qualities exhibited by two interacting elements from different tables. Each entity within a table has a potential relationship with other containing elements. 
    Relationships