Database using Command Prompt and MySql PDF

**LAB** **Database using Command Prompt and MySql** **Starting the database** **1** Open XAMPP, then start MySQL -------------------------------------------------- ------------------------------------------- **2**...

**LAB** **Database using Command Prompt and MySql** **Starting the database** **1** Open XAMPP, then start MySQL -------------------------------------------------- ------------------------------------------- **2** **Open cmd** **3** **In cmd, type cd c:\\xampp\\mysql\\bin** **4** **mysql -u root -p** **5** **Type 'root' in password** **show databases;** **use to show and check pre-existing databases** **Creating basic database** +-----------------------------------+-----------------------------------+ | **1** | **create database | | | [databaseName];** | +===================================+===================================+ | **2** | **use | | | [databaseName];** | +-----------------------------------+-----------------------------------+ | **3** | **create table | | | [tableName] (** | | | | | | **[dataName1] | | | [dataType]([dataLengt | | | h]) | | | data[properties];** | | | | | | **[dataName2] | | | [dataType]([dataLengt | | | h]) | | | [dataproperties];** | | | | | | **);** | +-----------------------------------+-----------------------------------+ | **A** | **Setting Primary Key** (declared | | | same time when declaring table | | | values) | +-----------------------------------+-----------------------------------+ | | primary key(dataName); | +-----------------------------------+-----------------------------------+ | **B** | **Setting Foreign Key** (declared | | | same time when declaring table | | | values) | +-----------------------------------+-----------------------------------+ | | foreign key(dataName) references | | | tableName(dataName); | +-----------------------------------+-----------------------------------+ **desc [tableName];** -------------------------------------------------------------------------- **show table properties like the dataName, datatype and dataProperties** +-----------------------------------+-----------------------------------+ | **4** | **insert into | | | [tableName] values(** | | | | | | **'value1',** | | | | | | **'value2';** | | | | | | **);** | +-----------------------------------+-----------------------------------+ **select \* from tableName;** ------------------------------- **show all table data** **Inserting Multiple Values Once** INSERT INTO [tableName] ([columnName], [columnName]) values -\> ('value1', 'value2'), -\> ('value1', 'value2'); -------- --------------------- **M6** **Data Management** -------- --------------------- **Database system** - allows communication of information related to a particular transaction **Data management** - practice of collecting, keeping, and using data securely, efficiently, and cost-effectively **Goal of Data Management** help people, organizations, and connected things optimize the use of data within the bounds of policy and regulation **Managing digital data** in an organization involves a broad range of tasks, policies, procedures, and practices. **The work of data management has a wide scope, covering factors such as how to:** **1** **Create, access, and update data across a diverse data tier** ------- -------------------------------------------------------------------- **2** **Store data across multiple clouds and on premises** **3** **Provide high availability and disaster recovery** **4** **Use data in growing variety of apps, analytics, and algorithms** **5** **Ensure data privacy and security** **Data Management Systems Today** **Data management systems** - built on data management platforms and can include databases, data lakes and data warehouses, big data management systems, data analytics, and more ↳ all these components work together as a **'data utility'** **to deliver the data management capabilities an organization needs** for its apps, and the analytics and algorithms that use the data originated by those apps **Autonomous database** - reducing the need for manual data management is a **key objective of a new data management technology** **Data Management Platforms** **Continuous Integration (CI)** - most critical step for continuous delivery of software ↳ development practice **where developers commit their code changes** (usually small and incremental) to a centralized source repository ↳ this repository **allows developers to capture the bugs** early and automatically before passing them on to production **Continuous Integration Pipeline** - usually involves a series of steps, **starting from code commit** to performing basic automated linting/static analysis, **capturing dependencies**, and **finally building the software** and performing some basic unit tests before creating a build artifact **Data Management Platform** - foundational system for collecting and analyzing large volumes of data across an organization **Commercial Data Platforms** - typically include software tools for management, developed by the database vendor or by third-party vendors **These data management solutions help IT teams and DBAs perform typical tasks such as:** **1** **Identifying, alerting, diagnosing, and resolving faults in database system or underlying infrastructure** ------- ------------------------------------------------------------------------------------------------------------- **2** **Allocating database memory and storage resources** **3** **Making changes in database design** **4** **Optimizing responses to database queries for faster application performance** **Big Data Management Systems** - lots and lots of data, comes in wider variety of forms than traditional data, and it's **collected at a high rate of speed** **but very complex to manage** **These systems specialize in three general areas:** **1** **Big data integration brings different types of data---from batch to streaming---and transforms it so that it can be consumed** ------- ------------------------------------------------------------------------------------------------------------------------------------------------------- **2** **Big data management stores and processes data in a data lake or data warehouse efficiently, securely, and reliably, often by using object storage** **3** **Big data analysis uncovers new insights with analytics, including graph analytics, and uses machine learning and AI visualization to build models** As big data gets bigger, so will the opportunities **Data Management Challenges** **1** **Lack of data insight** ------- ----------------------------------------------------------------------------------------------------------------------------------------------------- Data from an increasing number and variety of sources such as sensors, smart devices, social media, and video cameras is being collected and stored **2** **Difficulty maintaining data-management performance levels** Organizations are capturing, storing, and using more data all the time **3** **Challenges complying with changing data requirements** Compliance regulations are complex and multijurisdictional, and they change constantly **4** **Need to easily process and convert data** Collecting and identifying the data itself doesn't provide any value---the organization needs to process it **Data Management Challenges** **5** **Constant need to store data effectively** ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- In the new world of data management, organizations store data in multiple systems, including data warehouses and unstructured data lakes that store any data in any format in a single repository **6** **Demand to continually optimize IT agility and costs** With the availability of cloud data management systems, organizations can now choose whether keep and analyze data in on-premises environments, in the cloud, or in a hybrid mixture of the two -------- ----------------- **M7** **Data Models** -------- ----------------- **Data models** - visual representations of an enterprise\'s data elements and the connections between them ↳ represents data **structures** and their **characteristics** ↳ relatively simple representation, usually graphical, of more complex real-world data structures ↳ **output** of data modeling **Data Modeling** - first step in designing a database ↳ **process of creating** a specific **data model** for a determined problem domain ↳ an iterative, progressive process **Importance of Data Models** +-----------------------------------+-----------------------------------+ | **1** | **Facilitate interaction among | | | the designer, the applications | | | programmer, and the end user** | | | | | | **Ex. Application programmers | | | translate company policies and | | | procedures from a variety of | | | sources into appropriate | | | interfaces, reports, and query | | | screens** | +===================================+===================================+ | **2** | **Well-developed data model can | | | even foster (promote/encourage) | | | improved understanding of the | | | organization** | | | | | | **↳ applications are created to | | | manage data and to help transform | | | data into information** | | | | | | **↳ data are viewed in different | | | ways by different people** | | | | | | **Ex. Contrast the (data) view of | | | a company manager with that of a | | | company clerk** | +-----------------------------------+-----------------------------------+ | **3** | **Communication tools** | +-----------------------------------+-----------------------------------+ **Data Model Basic Building Blocks** The basic building blocks of all data models are **entities**, **attributes**, **relationships**, and **constraints** +-----------------------------------+-----------------------------------+ | **1** | **Entity** | +===================================+===================================+ | | anything (a person, place, thing | | | or an event) about which data are | | | to be collected and stored | | | | | | ↳ represents particular type of | | | object, entities are | | | "distinguishable", each entity | | | occurrence is unique and distinct | | | | | | ↳ entities may be physical | | | objects, such as customers or | | | products, but entities may also | | | be abstractions, such as flight | | | routes or musical concerts | +-----------------------------------+-----------------------------------+ | **2** | **Attributes** | +-----------------------------------+-----------------------------------+ | | characteristics of an entity | +-----------------------------------+-----------------------------------+ | **3** | **Relationship** | +-----------------------------------+-----------------------------------+ | | describes an association among | | | entities | | | | | | ↳ exists between customers and | | | agents | | | | | | ↳ Data models use **Three Types | | | of Relationships:** | | | | | | **A. One-to-many (1:M or 1..\*)** | | | - customer may generate many | | | invoice, but each invoice is | | | generated by only a single | | | customer | | | | | | **B. Many-to-many (M:N or | | | \*..\*)** - an employee may learn | | | many job skills, and each job | | | skill may be learned by many | | | employees | | | | | | **C. One-to-one (1:1 or 1..1) -** | | | each of its stores be managed by | | | a single employee | +-----------------------------------+-----------------------------------+ | **4** | **Constraint** | +-----------------------------------+-----------------------------------+ | | restriction placed on the data | | | | | | ↳ important because they help to | | | ensure data integrity | | | | | | ↳ normally expressed in the form | | | of rules | | | | | | **Ex.** | | | | | | **^1^** An employee's salary must | | | have values that | | | | | | are between 6,000 and 350,000 | | | | | | **^2^** A student's GPA must be | | | between 0.00 and | | | | | | 4.00 | | | | | | **^3^** Each class must have one | | | and only one | | | | | | teacher | +-----------------------------------+-----------------------------------+ -------- -------------------------- **M8** **Types of Data Models** -------- -------------------------- **Data Modeling** - practice of cleaning and organizing data into **visual representation** ↳ data models **act as blueprint** for building an optimized database ↳ similar to tree (DSA) **Types of Data Models** +-----------------------------------+-----------------------------------+ | **1** | **Hierarchical Model** | +===================================+===================================+ | | | +-----------------------------------+-----------------------------------+ | | **manage large amounts of data | | | for complex manufacturing | | | projects** such as the Apollo | | | rocket that landed on the moon in | | | 1969 | | | | | | ↳ developed in **1960s** | | | | | | ↳ depicts a set of **one-to-many | | | (1:M)** relationships between a | | | parent and its children segments | | | | | | ↳ each **parent** **can have many | | | children**, but each **child has | | | only one parent** | +-----------------------------------+-----------------------------------+ | | basic logical structure is | | | **represented by upside-down | | | tree**, top layer (the root) is | | | **perceived as the parent** of | | | segment directly beneath it | | | | | | ↳ contains **levels or segments** | | | | | | ↳ **segment** is equivalent of a | | | file system's record type | | | | | | **Limitations:** | | | | | | **^A^** was complex to implement | | | and difficult to manage | | | | | | **^B^** lacked structural | | | independence | | | | | | **^C^** many common data | | | relationships do not conform to | | | the 1:M form | +-----------------------------------+-----------------------------------+ +-----------------------------------+-----------------------------------+ | **2** | **Network Model** | +===================================+===================================+ | |  | +-----------------------------------+-----------------------------------+ | | created to **represent complex | | | data relationships more | | | effectively** than the | | | hierarchical model | | | | | | ↳ user perceives network database | | | as a collection of records in | | | **one-to-many (1:M) | | | relationships** | | | | | | ↳ allows record to **have more | | | than one parent** | +-----------------------------------+-----------------------------------+ | | in network database terminology, | | | **relationship is called a set** | | | | | | ↳ each set is **composed of at | | | least two record types:** an | | | **owner record** and a **member | | | record** | | | | | | **Limitations:** | | | | | | **lack of ad hoc query | | | capability** to generate the code | | | required to produce even the | | | simplest reports | +-----------------------------------+-----------------------------------+ +-----------------------------------+-----------------------------------+ | **3** | **Relational Model** | +===================================+===================================+ | | | +-----------------------------------+-----------------------------------+ | | **introduced in 1970 by E. F. | | | Codd (of IBM) in his landmark | | | paper "A Relational Model of Data | | | for Large Shared Databanks"** | | | | | | **↳ each row is called tuple | | | while each column represents an | | | attribute** | +-----------------------------------+-----------------------------------+ | | **was represented a major | | | breakthrough for both users and | | | designers** | | | | | | **↳ relational model foundation | | | is mathematical concept known as | | | relation** | | | | | | **↳ relation sometimes called a | | | table, a matrix composed of | | | intersecting rows and columns** | | | | | | **Examples:** | | | | | | **^1^ Oracle, ^2^ DB2, ^3^ | | | Microsoft SQL Server, ^4^ MySQL** | +-----------------------------------+-----------------------------------+ +-----------------------------------+-----------------------------------+ | **4** | **Entity Relationship Model** | +===================================+===================================+ | |  | +-----------------------------------+-----------------------------------+ | | **graphical representation** of | | | entities and their relationships | | | in a database structure | | | | | | ↳ **Peter Chen** first introduced | | | the **ER data model in 1976** | | | | | | ↳ normally represented in an | | | entity relationship diagram (ERD) | | | | | | ↳ based on the following | | | components: **entity, attributes, | | | and relationships** | +-----------------------------------+-----------------------------------+ +-----------------------------------+-----------------------------------+ | **5** | **Object-Oriented Model** | +===================================+===================================+ | | | +-----------------------------------+-----------------------------------+ | | both data and their relationships | | | are **contained in a single | | | structure** known as **object** | | | | | | ↳ basis for object-oriented | | | database management system | | | (OODBMS) | | | | | | ↳ reflects a **very different way | | | to define and use entities**; | | | like the relational model's | | | entity, **object is described by | | | its factual content** | +-----------------------------------+-----------------------------------+ | | **OO data model is based on the | | | following components:** | | | | | | **^1^** An **object is an | | | abstraction of a real-world | | | entity**, it may be considered | | | **equivalent to an ER model's | | | entity** | | | | | | **^2^ Attributes describe the | | | properties of an object** | | | | | | **^3^ Objects that share similar | | | characteristics are grouped in | | | classes**. | | | | | | ↳ A **class** is **collection of | | | similar objects** with shared | | | structure (attributes) and | | | behavior (methods) | | | | | | ↳ **A class's method represents a | | | real-world action** such as | | | finding, changing, printing a | | | person's name | | | | | | **^4^ Classes are organized in a | | | class hierarchy** | | | | | | **^5^ Inheritance is the ability | | | of** **an object** within the | | | class hierarchy **to inherit the | | | attributes and methods of the | | | classes** | +-----------------------------------+-----------------------------------+ -------- --------------------------------- **M9** **Degrees of Data Abstraction** -------- --------------------------------- **Data abstraction** - usually the **first step in database design** ↳ makes it possible for the developer to start from essential elements \-- data abstractions \-- and incrementally add data detail to create the final system **Degrees of Data Abstraction** **Database designer** starts with an abstract view of the overall data environment and adds details as the design comes closer to implementation --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- **Using levels of abstraction** can be **very helpful in integrating multiple views of data** as seen at different levels of an organization In the early **1970's**, the **American National Standards Institute (ANSI)** and **Standards Planning and Requirements Committee (SPARC)** defined framework for data modelling **based on degrees of data abstraction** The **ANSI/SPARC architecture** defines **three levels of data abstraction:** **^1^ External**, **^2^ Conceptual**, and **^3^ Internal** The framework has been **expanded with the addition of a physical model** to explicitly address physical-level implementation details of the internal model **Data Abstraction Levels** Four levels of data abstraction based on ANSI/SPARC architecture: +-----------------------------------+-----------------------------------+ | **1** | **External Model / View** | +===================================+===================================+ | | is the end user's view of the | | | data environment | | | | | | ↳ the term **end users** refers | | | to **people who use the | | | application programs** to | | | manipulate the data and generate | | | information | | | | | | ↳ end users usually **operate in | | | environment** in which an | | | **application has a specific | | | business unit focus** | | | | | | ↳ end users **working within | | | those business units view their | | | data subsets** as separate from | | | or external to other units within | | | the organization | | | | | | **The use of external views | | | representing subsets of the | | | database has some important | | | advantages:** | | | | | | **^1^** Makes easy to identify | | | specific data required to support | | | each business unit's operations | | | | | | **^2^** Makes the designer's job | | | easy by providing feedback about | | | the model's adequacy | | | | | | **^3^** Helps to ensure security | | | constraints in the database | | | design | | | | | | **^4^** Makes application program | | | development much simpler | +-----------------------------------+-----------------------------------+ +-----------------------------------+-----------------------------------+ | **2** | **Conceptual Model / Internal** | +===================================+===================================+ | | represents **global view of the | | | entire database** as viewed by | | | the entire organization | | | | | | ↳ **integrates all external | | | views** (entities, relationships, | | | constraints, and processes) into | | | a single global view of the | | | entire data in the enterprise | | | | | | ↳ **also known as conceptual | | | schema**, it's the basis for the | | | identification and high-level | | | description of the main objects | | | | | | ↳ most widely used conceptual | | | model is the ER model, the ERD is | | | used to graphically represent the | | | conceptual schema | | | | | | **Advantages of Conceptual | | | Model:** | | | | | | **^1^** It provides relatively | | | easily understood | | | | | | **bird's-eye view of the data | | | environment** | | | | | | **^2^** Conceptual model is | | | **independent of both software | | | and hardware,** changes in either | | | the hardware or the DBMS software | | | **will have no effect on the | | | database design** at the | | | conceptual level | +-----------------------------------+-----------------------------------+ +-----------------------------------+-----------------------------------+ | **3** | **Internal Model / DBMS Manager** | +===================================+===================================+ | | **the internal model maps the | | | conceptual model to the DBMS** | | | | | | **↳ is the representation of the | | | database as seen by the DBMS** | | | | | | **↳ requires designer to match | | | the conceptual model's | | | characteristics and constraints | | | to those of selected | | | implementation model** | | | | | | **↳ is hardware-independent | | | because it is unaffected by the | | | choice of the computer on which | | | the software is installed,** | | | | | | **↳ a change in storage devices | | | or even a change in operating | | | systems will not affect the | | | internal model** | +-----------------------------------+-----------------------------------+ +-----------------------------------+-----------------------------------+ | **4** | **Physical Model / Researcher** | +===================================+===================================+ | | operates at the **lowest level of | | | abstraction (data isn't | | | arranged)** describing the way | | | data are saved on storage media | | | such as disks or tapes | | | | | | ↳ **requires definition of both | | | physical storage devices and | | | access methods** required to each | | | data within those storage devices | | | | | | ↳ demands that **database | | | designers** who work at this | | | level **have a detailed knowledge | | | of the hardware and software used | | | to implement the database | | | design** | | | | | | ↳ is **dependent on the DBMS**, | | | **methods of accessing files**, | | | and **types of hardware storage | | | devices** supported by the | | | operating system | +-----------------------------------+-----------------------------------+ **Degrees of Data Abstraction** +-----------------------------------+-----------------------------------+ | **1** | **Physical / Reseacher** | +===================================+===================================+ | | How? | | | | | | ↳ lowest level of abstraction | | | | | | Computer / Laptop / Physical | | | Devices | +-----------------------------------+-----------------------------------+ | **2** | **Internal / DBMS Manager** | +-----------------------------------+-----------------------------------+ | | What? | | | | | | ↳ data model | | | | | | Organize / DBMS / Relationship / | | | Data / | | | | | | Information | +-----------------------------------+-----------------------------------+ | **3** | **View** | +-----------------------------------+-----------------------------------+ | | Where? | | | | | | ↳ shown to client | | | | | | Highest / Output / UI | +-----------------------------------+-----------------------------------+