Database Concepts and Relational Databases

Database Concepts

• A database is a single table or a collection of related tables.
• Database management systems (DBMS) are software used for creating, maintaining, and manipulating data within databases.
• DBMS software retrieves information from multiple tables and eliminates data redundancy.

Relational Database Basics

• Relational databases are built upon tables that are related based on common keys.
• A table, or relation, is a 2D table of data with rows (records) and named columns (attributes/fields).
• Every table needs unique names.
• Cell data or attributes, such as age or SSN, must fall into known domains/legal values, for example, 9 digits for SSN.
• Attributes should have unique names.
• Each record in a table must be unique, having no row with same values.
• A primary key is an attribute/set of attributes that uniquely identifies each row. Requires all rows to be unique
• Primary keys can be either simple (single attribute) or composite (multiple attributes).
• Columns need to result in all unique options
• A foreign key is an attribute/set of attributes in a database relation that serves as the primary key of another relation. It helps link different relations (tables) in a relational database.

SQL Basics

• SQL, or Structured Query Language, is a standard language for processing databases used to retrieve information by running queries.
• SQL statement order: SELECT, FROM, WHERE.
• SELECT is for queries relating to a single table or multiple tables.
• Columns and expressions returned from the query are listed in the SELECT statement.
• FROM is used to indicate the table(s) or location from which data comes.
• WHERE is used to indicate the conditions under which a row will be included in the result.
• Required order of included clauses: SELECT [DISTINCT] column_list, FROM table_list, [WHERE conditional expression].

Comparison and Boolean Operators

• Comparison operators in conditional expression: =, >, >=, <, !=.
• Boolean operators (AND, OR, NOT) are used in the WHERE clause to join two conditions.
• AND returns results only when all conditions are true.
• OR returns results when any condition is true.
• NOT negates the following expression, returning results where the expression is violated.
• The order of operator evaluation is NOT, then AND, then OR, but parentheses can change the default order.
• DISTINCT returns list, put after SELECT and returns unique values
• BETWEEN ... AND checks for set/list inclusion using upper bound comes last.
• IN (.......) checks if field matches an item of set/list.

Microsoft (MS) Access

• Understand how to establish relationships among tables, which is drawing relationships when a primary key is a foreign key in a different table
• Given a certain task, design/evaluate the query under “Design View" in MS Access

Supply Chain Management

• SCM is the coordination and integration of all activities and flows, including material, information, and financial, in the value system with suppliers and customers to maximize firm benefits.
• Key SCM challenges include uncertainty in demand, supply, processes, and coordination (complexity, inappropriate business processes/metrics, lack of standards).
• An efficient supply chain predicts and supplies demand efficiently at the lowest cost, maximizes capacity utilization, and selects suppliers based on quality and cost.
• A responsive supply chain quickly responds to demand changes, minimizes lead time, and selects suppliers based on speed, flexibility, and quality.
• Supply Chain Strategy - Match strategy to the features of a product or an industry.

Fashion Industry Supply Chains

• Conventional fashion industry supply chain: Suppliers --> manufacturers --> distributors --> retailers --> customers, using contract manufacturers that outsource to developing countries for cost advantages.
• Conventional supply chains may take 6 months to design a new collection and 3 more to manufacture. It can take between 3-5 months from creation to delivery.
• Drawbacks include uncertainty, quality control issues, and unpredictable demand.
• Benefits includes cost efficiency, scalability, flexibility, and speed to market.
• Zara's supply chain structure: Suppliers --> Manufacturers --> distributors --> retailers --> customers.
• Zara uses in-house or outsourced production for raw materials (fabric, dyes), with 40% of fabric produced in-house.
• Upwards of 60% of Zara's merchandise is produced in-house.
• Vertical Integration - Zara maintains control over distributors and retailers
• Items goes from idea to store in 15 days
• Smaller tweaks arrive faster (within 10 days)
• Key to fast fashion:
• Zara's vertical integration means it controls much of its supply chain (distribution and retailers)
• Using Real-Time Feedback from Stores store managers regularly send suggestions for new colors and cuts
• Designers then draw up the new styles using CAD system and send patterns to Zara factories in Spain
• The clothes move through Zara's distribution center and reach stores within 48 hours
• Technology-enabled manufacturing produces 2.5 million items per week
• Fabric is cut and dyed by robots in 23 highly-automated factories
• Half of the cloth is undyed to respond to midseason fashion shifts
• 2.5 million items are moved to 7K stores (96 countries) per week
• Items travels from 6-million-square-foot distribution center to stores within 48 hours
• Algorithms determine how many of which items in which sizes should be delivered to which stores during twice-weekly shipments
• Omnichannel: seamless and high-quality customer experiences occur within and between contact channels

Zara's Online Store and Supply Chain

• Omnichannel integrates both online and offline channels
• Inventory Pooling & Flexibility fulfills online orders from either distribution centers or physical stores, reducing inventory across channels.
• Online supply chain now includes click-and-collect areas and supports in-store returns of online purchases
• Demand Aggregation: Online channels help Zara better aggregate demand, making it easier to predict trends and manage inventory allocation effectively across stores and online platforms
• Data usage is enhanced. Online channels contribute to a "data explosion" — enabling Zara to run experiments, gain insights into customer behavior, and improve forecasting and decision-making using analytics.
• Store Structure changed online. Instead of just shipping from the Global Distribution Center (DC) to stores twice a week, now shipments also go to online order prep areas
• Stores themselves can fulfill online orders and redesigned to include stock rooms, click-and-collect areas, and separate spaces for online prep.
• Reasons to build online and offline stores:
• Seamless customer experience
• Aggregates demand to manage uncertainty on demand allocation
• Enables inventory pooling and reduces inventory costs
• Creates flexibility (fulfill from store or DC)
• Reduces logistics & reverse logistics costs (click & collect, in-store returns of online orders)
• Data explosion, experiments

Data for supply chain analytics

• Sources for supply chain analytics:
• Data-driven insight for better decisions
• Prediction, Optimization, Simulation
• Point of sale (POS) system
• Social media feeds
• ERP and other enterprise software systems
• Mobile devices or GPS
• RFID (Radio Frequency Identification): An automatic identification method using radio frequency to identify objects on which small RFID tags had been attached
• Different from bar codes in: No manual scanning, thus promoting labor savings, no need to be visible on the outside of a package, less affected by dirt, moisture, or scrapes, a longer read range, and stores between 16 and 64 KB of data
• Supply Chain Analytics:
• Source Includes Supplier Risk, Product Characteristics, Sourcing Channel Options, Supplier Negotiation
• Make Includes Inventory Optimization, Capacity Constraints, Facility Location/layout, Workforce Analytics
• Move Includes Distribution & Logistics Optimization, Transportation Alternatives, Routing, Scheduling, Vehicle Maintenance
• Sell Includes Location-Based Marketing, In-Store Behavior Analysis, Customer Micro-Segmentation, Multichannel Marketing, Assortment Optimization
• Example use case: Walmart uses an integrated technology platform (e.g., Retail Link) that allows suppliers to know real-time when stores need to be restocked, eliminating the need to wait for order confirmations.

Blockchain Technology

• Blockchain is a new platform technology to verify and record transactions among an interconnected set of users.
• Each transaction is a “block,” and the cumulative set of transactions comprises the "chain".
• Key characteristics: data is not changeable, all transaction records are transparent and traceable, creating a secure and tamper-resistant “single source of truth,” with no centrally coordinating entity as transactions are broadcasted across the network to be verified & validated (decentralized trust).
• Permissioned blockchain is private and restricts users and their rights to validate transactions
• Permissionless blockchain is public for anyone to join the network to trade, validate transactions, and/or create Smart Contracts.
• Bitcoin is a digital currency that uses encryption to regulate digital coins and verify fund transfers on a decentralized blockchain network, operating without a central authority and relying on peer-to-peer technology.
• Volatility factor: lack of regulation, adoption rates, security risks (fear of hacks), speculation and investor sentiment
• Smart contracts are event-triggered, automated pieces of computer code intended to facilitate, verify, or enforce the negotiation or performance of a contract: automated payments, real-time tracking, recording actions
• Container shipping problems: Rollover problems – delays or no shows, cargo no-shows, with no efficient collateral for booking deposit.
• 300 cubits uses blockchain: Selling TEU tokens made a settlement currency that generated cash to become the the collateral for the booking deposits, Booking Deposit Module
• Challenges: Companies wouldn't adopt it, legal, need more adopters
• System adoption is slowed as it requires the coordination of different stakeholders, is a two-sided platform, and is subject to network effect.

Initial Coin Offering

• An Initial Coin Offering (ICO) is a mechanism for new projects to raise funds by selling crypto tokens in exchange for fiat currencies. It is light or unregulated
• Initial Public Offering (IPO): A private company offers shares of stock to the public for the first time to raise capital. Investors who buy IPO shares own a portion of the company and may receive dividends and voting rights. Paid in fiat currency; Heavily regulated
• ICO Advantages: Funds can be raised at the early stage of their projects, entrepreneurs still have the full ownership as well as control of the company, is more cost effective and has more flexibility in terms of raising additional funds through digital tokens.
• To address 300cuibits’ blockchains requires attracting and addressing different stakeholders and their needs, attending industry events, adjusting resources, and promoting TEU tokens

Implementing Blockchains

• Key lessons from IT implementation: Involve key stakeholders, coordinate amongst multiple parties, integrate the blockchain with other technologies such as IoT for operational improvements.
• Challenges should be addressed by promoting TEU tokens to stakeholders, attending events, and educating on system benefits and resource benefits Challenges:
• Required coordination of many different stakeholders, resulting in slow adoption
• Two-sided platform subject to network effects required a large number of shippers and shipping companies on board
• No existing laws and standards to regulate smart contract implementation

Industry 4.0

• Convergence and application of various digital technologies: augmented reality, simulation, industrial internet, cloud, cybersecurity, advanced robotics, big data & analytics, additive manufacturing, horizontal/vertical integration.
• Internet of Things (IoT) allows for digital transformation of previously analog machine and service operations via smart connected products
• Key Elements: Intelligent Machines, Advanced Analytics, Connecting People at Work
• The convergence of hardware, software, and data has shifted how firms create value and capture value.

Smart Products

• Benefits: real time data collection, Product Differentiation, Enables subscription services, pay-per-use models, or remote diagnostics, personalize offerings
• Components: Physical (Product's mechanical and electrical parts), "Smart" parts (Sensors, microprocessors, data storage, controls, software, and an operating system and enhanced user interface), Connectivity (Ports, antennae, and protocols enabling wired or wireless connections.

Digital Twins & the Metaverse

• Digital twins are true-to-reality virtual representations of a real-world physical asset or system, continuously updated and use physical products, virtual products, and data/information connections.
• Applications of Digital Twins includes manufacturing, Autonomous Vehicles, Healthcare, Supply Chain and Logistics.
• Metaverse is a virtual shared space connected to physical world aspects, including people, places, and things
• Leyland leveraged digital twins for Business Model Innovation to capture the Total Cost of Ownership (TCO) of the vehicle through product sales
• Monetize digital twins through subscription based services and monetize data for a fee (Remotely track/manage a vehicle, report a stolen vehicle, or bring a moving vehicle to an immediate halt from the control center, Analysis of driver's behavior), Recommendation systems for sales, Reducing the time from concept to commercialization

Smart Sensors

• Not all products merit use of smart sensors
• Issues: production cost, increased system complexity, privacy concerns

Digital Transformation

• Building digital twins, replicate drilling can create digital transformation and experimentation
• Healthcare can use digital twins, specifically using a simulated digital patient
• Healthcare issues: data privacy and HIPAA compliance, patient trust, training

IT Security

• Denial-of-service (DOS) attack is a cyber attack to disrupt a machine or network resource
• Differs as it does not involve accessing or breaching
• Managing the crisis includes protect customer data and what's happening and is critical
• In crisis, keep statements truthful and avoid saying what you don't know

Business Analytics

• Organizations use data to identify new customers
• Online systems: real-time access, advanced analytics
• Predictive: machine learning algorithms, data mining
• Supervised: draw inferences
• Unsupervised: draw conclusions
• Association detection: relationships
• Support, confidence, lift
• Clustering: Similar records are grouped together based on certain characteristics Industry examples: market segmentation, finance, industry analysis
• K-Means: cluster analysis to identify groups and minimize dispersion
• Parametric and non-parametric measurements
• K-NN: classification that finds k neighboring records to create new data points
• Decision trees: splitting datasets and finding thresholds for accuracy

Artificial Intelligence

• AI: making machines think like humans
• ML is a subset
• GenAI focuses on new content
• Training: exposing algorithms; fine-tuning: giving models specialized requests
• Expensive training
• inference is cheaper

Foundation Training

• Can be patented
• Not inventions
• Mostly kept as trade secrets
• At risk of commoditization

Monetization and AI

• Revenue and competitive edge
• Open to compete
• Integrate into services and tools
• More AI innovations