Data Modeling Concepts

Data Modeling Concepts

• Data modeling is similar to building design or engineering drawings in the real world
• A data model contains:
  • Entities (things of interest that we want to store data about)
  • Attributes (information about the entities)
  • Relationships (how the entities relate to each other)

Entities

• An entity is something of interest that we want to store data about
• Entities can be physical real-world objects or conceptual things
• Examples of entities:
  • Book
  • Author
  • Story
  • News article
  • Customer
  • Address

Attributes

• An attribute is a specific characteristic or data point about an entity
• Attributes can be:
  • Qualitative (non-numeric)
  • Quantitative (numeric)
• Examples of attributes for a book entity:
  • Title
  • Number of pages
  • Author
  • Genre

Process of Creating a Data Model

• Step 1: Identify significant entities about which we want to store data
• Step 2: Create a logical data model to explain the relationships between entities
• Step 3: Determine the attributes for each entity
• Step 4: Design the database tables
• Step 5: Implement the design using Structured Query Language (SQL)

Exercise

• Identify at least 10 attributes for each of the following entities:
  • Person
  • Car
  • Country
  • Smartphone
  • Bank account
  • Class
  • Pet
  • Apartment
• Create a table with columns for:
  • Attribute
  • Data type
  • Description
  • Is it required or not?

Data Modeling Concepts

• Data modeling is a process of creating a conceptual representation of data structures and relationships.
• A data model consists of three key components: entities, attributes, and relationships.

Entities

• Entities are objects or concepts of interest that we want to store data about.
• Examples of entities include physical objects, people, and conceptual things.
• Entities can be classified into two categories: physical real-world objects and conceptual things.

Attributes

• Attributes are specific characteristics or data points about an entity.
• Attributes can be qualitative (non-numeric) or quantitative (numeric).
• Examples of attributes for a book entity include title, number of pages, author, and genre.

Process of Creating a Data Model

• The process of creating a data model involves five steps:

Step 1: Identify Entities

• Identify significant entities about which we want to store data.

Step 2: Create Logical Data Model

• Create a logical data model to explain the relationships between entities.

Step 3: Determine Attributes

• Determine the attributes for each entity.

Step 4: Design Database Tables

• Design the database tables based on the logical data model and attributes.

Step 5: Implement the Design

• Implement the design using Structured Query Language (SQL).

Database Management Systems (DBMS)

• A DBMS is a computer system that stores, organizes, manages, and processes data.
• It enables users to define, record, query, update, and manage data.
• DBMS provides functionality to create, host, and manage databases.

Relational Databases

• Relational databases store data in tables with relationships between them.
• This course focuses on relational databases, specifically MySQL.

Entities and Attributes

• Entities are real-life things about which we want to store data.
• Examples of entities include a person, a book, a car, or a building.
• Attributes are the data itself, which can be physical characteristics (e.g., weight, height, color) or other types of information.

Data Models

• A data model is a larger design that includes multiple entities and their attributes.
• It is a conceptual design that helps us organize and structure data.
• Data models are similar to building designs or plans.

Databases and Tables

• A database is a collection of entities, which are stored in tables.
• A table is a physical representation of an entity, with each attribute becoming a field within the table.
• A database can contain multiple tables, and each table can have multiple fields.

MySQL

• MySQL is a specific relational database management system (RDBMS) used for this course.
• It is a software that runs as a server, and can be accessed using a client application like MySQL Workbench.
• MySQL uses a language called SQL (Structured Query Language) to interact with the database.

SQL Basics

• SQL is a language used to interact with relational databases.
• There are six basic constructs in SQL:
  • CREATE TABLE: creates a new table.
  • ALTER TABLE: modifies an existing table.
  • DROP TABLE: deletes a table.
  • INSERT: adds new records to a table.
  • UPDATE: modifies existing records in a table.
  • DELETE: deletes records from a table.

Exercise

• Create a table about an entity called "person" with three fields: first name, last name, and gender.
• Use the CREATE TABLE command to define the table.
• Use the INSERT statement to add three records to the table.
• Use the SELECT statement to list the records.
• Use the UPDATE statement to modify the records.
• Use the DELETE statement to delete the records from the table.

Connecting to MySQL Server

• Use the username as your last name and student ID as the password.
• In the port session, leave the port number as 3306.

Configuring MySQL Workbench

• The main window is where you will type SQL commands and send them to the server.
• The left-hand side window displays databases, which can be expanded to show tables, views, stored procedures, and functions.

Creating a New Database

• To create a new database, use the SQL command CREATE DATABASE.
• The command should end with a semicolon (;).

Creating a New Table

• To create a new table, use the SQL command CREATE TABLE.
• Define the fields (attributes) of the table, including data types and lengths.

Using the Database

• To use the newly created database, use the SQL command USE.
• Once inside the database, you can create tables, insert data, and perform other operations.

Inserting Data into a Table

• To insert data into a table, use the SQL command INSERT INTO.
• Specify the columns and values to be inserted.

Retrieving Data from a Table

• To retrieve data from a table, use the SQL command SELECT.
• Example: SELECT first_name, last_name, gender FROM person;

Updating Data in a Table

• To update data in a table, use the SQL command UPDATE.
• Example: UPDATE person SET last_name = 'Do' WHERE first_name = 'Jane';

Exercise Summary

• Create a database table called "person" with three columns: first_name, last_name, and gender.
• Insert three records into the table using the INSERT command.
• Retrieve the data from the table using the SELECT command.
• Update one of the records using the UPDATE command.