What Is A Database?
- [ ] Data is everywhere and comes in a wide variety of types and forms
- [ ] Most data is never recorded
- [ ] Some (most?) data has value!
- [ ] Databases store data in a structured way
- [ ] Databases are a way of making data accessible
- [ ] Different databases use different techniques, such as:
- [ ] Applying a fixed structure
- [ ] Adding context to and/or validating the data
- [ ] Making the data easily retrievable

Most organisations will have at least a couple of databases
- [ ] Staff data (name, payment details, etc.)
- [ ] Customer data (transactions, points of contact, etc.)

What Is (Not) Considered A Database?
- [ ] A hard disk is not considered to be a database
- [ ] It stores data
- [ ] Does not make locating data easy
- [ ] A filesystem can be considered a simple type of database
- [ ] Data is stored within files
- [ ] Folders/directories are used to structure how the files are stored
- [ ] Filesystems enable searching for files
- [ ] Filesystems can be local (e.g. C:\ on Windows) or network (e.g. Dropbox)
- [ ] Storage of business data is largely done within a database management system(DBMS)
- [ ] This is responsible for managing one or more databases
- [ ] Will ensure that data can be located, often done through an application
- [ ] programming interface (API)
- [ ] Is also responsible for maintaining data integrity

Why Use A Database?
- [ ] Data integrity
- [ ] Avoiding data corruption
- [ ] Transaction processing
- [ ] Performing operations on the database
- [ ] Data retrieval
- [ ] Efficiently fetching relevant data
- [ ] Analytical processing
- [ ] Enabling organisations to examine their data in bulk
- [ ] Performing analytics over data is a common use-case
- [ ] Each of these properties may vary between different DBMSes, e.g.
- [ ] Some may be faster at storing transactions at the expense of how well they can ensure data integrity
- [ ] Others may specialise at fast data retrieval but be unable to perform complex analytics

It’s All CRUD!
- [ ] Databases are often described as enabling CRUD, four fundamental operations that are performed upon data;
- [ ] Create (The addition of new values to the database)
- [ ] Read (The extraction of values from the database)
- [ ] Update (The alteration of values stored within the database)
- [ ] Delete (The removal of values from the database)

CRUD Operation Examples
- [ ] Create (Hiring a new member of staff or A purchase of a good by a customer)
- [ ] Read (View the details of all members of staff or See the last 50 purchases worth £20 or more by any customer)
- [ ] Update (Change a member of staff’s wage or Alter a customer’s payment information)
- [ ] Delete (After firing an employee or Cancelling an annual subscription)

Transactions
- [ ] Not all operations you might want to perform can be described as a single CRUD operation
- [ ] E.g. when performing a money transfer of £50 from account A to account B, a bank will want to:
- [ ] 1. Update A’s account balance by -£50, and
- [ ] 2. Simultaneously update B’s account balance by +£50
- [ ] But, if only one fails, the other shouldn’t occur, otherwise:
- [ ] If operation 1 fails, B gains £50 from the bank, A loses nothing
- [ ] If operation 2 fails, A loses £50 to the bank, B gains nothing
- [ ] This concept is called a transaction
- [ ] Most DBMS's natively support this type of functionality
- [ ] Many CRUD operations can be combined into a single transaction

The next section introduces the abbreviation ACID, which describes four
properties that databases are often designed to protect.

ACID is an acronym describing important transaction properties first fully defined in (Haerder, 1983)
- [ ] Atomicity
    - [ ] A transaction will either succeed or fail – it cannot be partially done
    - [ ] If either operation fails, the whole transaction is cancelled
- [ ] Consistency
    - [ ] A transaction cannot result in the database being inconsistent
    - [ ] Consistency varies in definition by DBMS
- [ ] Isolation
    - [ ] An executing transaction will not interfere with the execution of another
    - [ ] A DBMS should prevents simultaneous transactions from operating on the same data
- [ ] Durability
    - [ ] Once a transaction is complete, the effects of it must persist

ACID Risk Factors
- [ ] ACID compliance was traditionally at greatest risk of being violated when a
- [ ] system failure occurred
- [ ] If the DBMS crashed midway through a transaction, the database that was being altered might end up being inconsistent
- [ ] If the hard disk being used to store the database is full, but the DBMS doesn’t detect it, a transaction that stores data might not be durable
- [ ] Many DBMSes are highly resilient to individual system failures
- [ ] The cost of failure is too high
- [ ] Relatively simple to ensure on a single computer, at the expense of performance
- [ ] However, modern database usage is changing

Distributed Databases
- [ ] The volume of data that many organisations want to store has increased dramatically since the early 1990s
- [ ] Influenced by a number of factors, such as:
- [ ] Wider availability of internet access
- [ ] Rise of mobile devices and cheaper data storage
    - [ ] In 1990, 1MB of storage cost $5.28
    - [ ] In 2018, 1MB cost ~$0.000025 (McCallum, 2018)

These increases in volume can mean single large database computers are no longer sufficient
- [ ] Since they are limited in several key ways
- [ ] How much CPU is available for processing, and
- [ ] How quickly hard disks can be written to and read from
- [ ] Thus a lot of research has moved to storing databases across multiple
- [ ] computers
- [ ] Such databases are called distributed databases

Distributed Database Risks
- [ ] Moving from storing data on a single computer to multiple computers is not
- [ ] risk-free!
- [ ] Higher likelihood of communication failures
- [ ] Individual computers are more likely become unavailable whilst others are working fine
- [ ] Very hard (perhaps impossible) to make the same ACID guarantees about
- [ ] transactions
- [ ] When data is distributed, much more likely that inconsistencies will occur
- [ ] Communication failures can lead to what is sometimes called “split-brain” or
- [ ] network partition
- [ ] Both sides think they are the “real” database and behave as if the other side
- [ ] doesn’t exist
- [ ] But the benefits are tangible – and for some use cases, distributed databases
- [ ] are the only reasonable choice

Relational Databases
- [ ] In the 1970s, several different models for storing databases were proposed
- [ ] Most of these models were very restrictive in what they could represent
- [ ] Made sense for certain situations, but it was difficult to generalise them to a
- [ ] Wide variety of use-cases
- [ ] One model – the relational model – was proposed by E. F. Codd whilst working
- [ ] at IBM (Codd, 1969)
- [ ] Designed to be simple to generalise, whilst still providing useful structure to
- [ ] the data it stored
- [ ] The proposal included API language for these databases – the Structured
- [ ] Query Language (SQL)
- [ ] This relational model and SQL were quickly adopted by several different
- [ ] competing DBMSes
- [ ] Thus they became the industry standard
- [ ] Such a DBMS is often referred to as a Relational DBMS (RDBMS)


What Does It Mean for Databases To Be Relational?
- [ ] The basic idea in the relational model is there are two types of data that need to
- [ ] be stored
- [ ] Data about things (aka entities)
- [ ] Data about how different things relate to each other (aka relationships)
- [ ] What data is being stored about the entities and what relationships can exist
- [ ] between those entities is often static
- [ ] Although both of the above can change, it is generally the case they don’t
- [ ] As well as storing this data, RDBMS allow database models to be very restrictive
- [ ] about form that data is in
- [ ] This can be very beneficial when done appropriately, since it may remove
- [ ] many of the issues that can occur with storing data
- [ ] Overly restrictive data models can be self-defeating
- [ ] Can introduce a lot of needless friction for users
- [ ] What if your name is longer than the maximum provided?
- [ ] Or perhaps your surname contains an apostrophe

Relational Database Issues
- [ ] Different RDBMS' use their own different versions of SQL
- [ ] E.g. SQL Server uses Transact-SQL, whilst Oracle uses PL/SQL
- [ ] Although they are very similar, this certainly reduced the benefits of using a
- [ ] shared language
- [ ] Relational databases were perceived as being inflexible
- [ ] Not a problem in the early days of computing
- [ ] But goes against modern trends in software development
- [ ] The relational model seemed to be inefficient in the distributed setting
- [ ] Each RDBMS has its own way of approaching distributed databases
- [ ] SQL didn’t provide any obvious ways of improving the performance of queries
- [ ] Rightly or wrongly, it was perceived by many developers that relational
- [ ] databases were hard to use
- [ ] (Note that most of these points are around perception and unproven factors)

The Rise Of NoSQL?
- [ ] These issues and others has led to the resurgence of non-relational models
- [ ] These models are often collectively known as NoSQL
- [ ] Although they share the same term, the only thing they really have in
- [ ] Common is they don’t use SQL*
- [ ] Most NoSQL models are designed to be easily distributed
- [ ] They are often designed primarily for the storage of Big Data
- [ ] Flexibility is also a key element in many NoSQL products
- [ ] This has benefits, but also introduces data integrity risks
- [ ] In many cases, this can either be prevented or worked around by writing
- [ ] sufficiently flexible code to interact with the database
- [ ] It is generally recognised that there are four types of NoSQL database
- [ ] Some NoSQL products may fall into multiple categorisations

What Are The NoSQL Database Types?
- [ ] Key-value
- [ ] All stored data is referenced by a unique key, values are generally short and will have few if any restrictions on what they can be
- [ ] Related values will often share the same key prefix
- [ ] Document
- [ ] Similar a key-value database, but the values are large structured “documents”
- [ ] Allows nested data to be stored (such as lists or mappings), so behaves more similarly to software
- [ ] Column
- [ ] Stores column values together, making it more efficient when looking at all possible values for a given column
- [ ] Graph
- [ ] A very flexible model, similar in many respects to relational, but allows much more complex relationships to be represented

NoSQL Database Risks
- [ ] See the risks associated with distributed databases on slide 19
- [ ] Most NoSQL databases can be run on a single computer, but a lot of the benefits they offer are Dependent on distribution
- [ ] Lower levels of expertise, when compared to many RDBMSes
- [ ] Database Admins (DBAs) for RDBMSes are often very experienced and able to solve many issues
- [ ] Less mature software
- [ ] Many RDBMSes have been around since the 1980s – they are “battle-tested”
- [ ] In contrast, most NoSQL databases are relatively new, leading to a higher likelihood that bugs will be encountered
- [ ] Querying over flexible data formats requires flexible code
- [ ] This can dramatically increase the complexity of implementation, and reduce query performance
- [ ] Errors may not be visible until post-deployment

Abbreviations Introduced This Lesson
- [ ] DBMS – Database Management System
- [ ] RDBMS – Relational Database Management System
- [ ] API – Application Programming Interface
- [ ] CRUD – Create, Read, Update, Delete
- [ ] ACID – Atomicity, Consistency, Isolation, Durability
- [ ] SQL – Structured Query Language
- [ ] DBA – Database Administrator

Introduction to Databases

What Is A Data Model?
- [ ] A model is a description of a complex system
- [ ] Often uses mathematics to describe how things interact
- [ ] You can have different models for the same system
- [ ] Newton’s laws of motion (Newton, 1687) and Einstein’s theory of relativity (Einstein, 1905) are both mathematical models for how physical objects interact
- [ ] Both are useful:
- [ ] Einstein’s theory is more accurate
- [ ] Newton’s laws are easier to work with
- [ ] A data model describes how data is represented within a computer system
- [ ] Any system that stores data will require a data model
- [ ] It may not be explicitly described or documented, but one will exist!
- [ ] There are multiple ways of representing a data model


What Does A Data Model Contain?
- [ ] What data it can store
- [ ] Can be high level, i.e. general concepts and how they interact
- [ ] Employee, salary, bank account
- [ ] An employee can have an associated salary and bank account
- [ ] Or low level, e.g. the exact representation used
- [ ] The bank account sort code will be 3 pairs of 0-9 digits, which are separated with hyphens
- [ ] What constraints will be applied to that data
- [ ] What inherent limitations does the model enforce?
- [ ] To record a bank account, it may be the case that the associated employee must already exist
- [ ] Are there optional constraints that will be enforced?
- [ ] A salary should be a positive number, and must be above the national minimum wage


Representing Data Models
- [ ] Data models can be both large and complex
- [ ] There may be many types of concept and/or interactions between them
- [ ] Constraints can come in many forms
- [ ] Data models are often represented graphically to make them easier to comprehend
- [ ] Two commonly used formats are:
- [ ] Entity-Relationship (ER) diagrams
- [ ] Unified Modelling Language (UML) diagrams
- [ ] These two formats have different primary use-cases
- [ ] ER diagrams are usually used for relational databases
- [ ] UML diagrams are usually used for software
- [ ] We’ll be focusing on ER diagrams in this course


Why Bother Explicitly Modelling Data?
- [ ] Building explicit data models has some clear benefits
- [ ] Represents how data will be structured in an understandable way
- [ ] Facilitates communication of and discussion around that understanding
- [ ] It can be easier to determine where data requirements may be missed or have been misinterpreted
- [ ] By following a process, inadvertent mistakes can be more easily detected
- [ ] But these processes are often not completely fool-proof!
- [ ] However, strict adherence to data modelling processes can back-fire
- [ ] If a data model must exist before software implementation begins, development speed can be impacted
- [ ] In the modern NoSQL movement, data modelling is often an organic process that follows the agile development practices
- [ ] A potential drawback to this is whether those data models are then documented, or fully thought through

Types Of Relational Data Model
- [ ] The process of data modelling is sometimes broken down into three stages, as described in (Brodie, 1982)
- [ ] These stages represent different depths of models
- [ ] Conceptual data modelling
- [ ] 	Identifies what concepts the data model is about
- [ ] 	Describes how the concepts relate to each other
- [ ] Logical data modelling
- [ ] 	Identifies what information will be stored about each concept, including a unique identifier
- [ ] 	Describes the relationships identified in the conceptual data model in terms of these unique identifiers
- [ ] Physical data modelling
- [ ] 	Precisely describes how the data will be stored
- [ ] 	May introduce additional constraints that cannot be expressed in the previous two stages

Examples Of Relational Data Model
- [ ] Conceptual
    - [ ] The data model will store information about employees and salaries
    - [ ] An employee will receive a salary and two or more employees may receive the same salary amount
- [ ] Logical
    - [ ] 	Each employee will be identified by their employee ID, a salary is identified by an organisational grade
    - [ ] 	Each employees’ grade shall be kept in their employee record.
- [ ] Physical
    - [ ] 	The employee table will include a name which can consist of up to 100 characters
    - [ ] 	The grade in the employee record must match a grade appearing in the salary table, and a salary must be above national minimum wage

Developing Data Models
- [ ] The process for constructing a data model is often described in terms of how the task is approached
- [ ] Do you start from:
    - [ ] a complete list of business data requirements from which you build the models, or
    - [ ] a collection of examples of data that are needed to be stored, and work from them?
- [ ] These two approaches are known, respectively, as top-down and bottom-up
- [ ] Their aims are the same – to produce a physical data model
- [ ] In the relational database model, there are two widely known approaches
- [ ] Entity-Relationship modelling – a top-down approach
- [ ] Often done with Conceptual & Logical models, (refer to lesson covered)
- [ ] Normalisation – a bottom-up approach
- [ ] Move between a series of Normal Forms, (refer to lesson covered)

Data Modelling / Conceptual Data Modelling

What Is A Logical Data Model?
- [ ] An entity in a logical data model has more detail than a conceptual data model
- [ ] This includes identifying:
- [ ] Attributes (i.e. all the data about the entity you need to store) and
- [ ] A primary key (one or more attributes that uniquely identifies each entity)
- [ ] It represents the relationships in the conceptual data model in terms of these primary keys
- [ ] To connect two entities in a logical data model, one of the entities must have an attribute that references the other entity’s primary key
- [ ] This is known as a foreign key
- [ ] This limits the type of relationship that can appear in a logical model when compared to a conceptual data model

An Introduction To Attributes
- [ ] An attribute is data about an entity
- [ ] It must only be about the entity under consideration
- [ ] There are two main types of attribute:
- [ ] Single-valued
- [ ] An attribute that it is only ever required to store a single value for
- [ ] If the value should be changed, the previous value will be replaced
- [ ] Multi-valued
- [ ] An attribute that may require multiple values to be stored
- [ ] This may be because
- [ ] It could change over time and that history is required to be kept
- [ ] There can be any number of values for it simultaneously
- [ ] When building a logical data model
- [ ] Single-valued attributes are added to the entity
- [ ] Multi-valued attributes are added to a new entity, and a relationship is added between the original entity and the new entity

Attributes Example – Single-Valued
- [ ] An office will have an address, a monthly rental cost, an area in square meters, and a maximum capacity
- [ ] These aspects didn’t appear in the conceptual model, as they were not identified as being entities.
- [ ] Each of these values, as described, could be represented as single- valued attributes

Attributes Example – Multi-Valued
- [ ] A new requirement comes in to keep track of the office rental prices over time
- [ ] Our previous logical data model is no longer appropriate, since a change in rental cost would cause a previous value to disappear
- [ ] As the requirement asks to see the price over time, it is necessary to add additional attributes describing when the price was valid

Primary Keys
- [ ] An important aspect of the relational model is that every entity must be uniquely identifiable
- [ ] Attributes store those identifiers
- [ ] Some entities will have (one or more) attributes that uniquely identify them
- [ ] If it is a single attribute, then that attribute will likely be a good candidate to be the primary key for that entity
- [ ] If multiple attributes need to be combined to ensure uniqueness, then this would be known as a composite primary key
- [ ] If no attributes can be used to ensure uniqueness, or the use of a composite primary key is inappropriate, then it is normal to introduce a new attribute that guaranteed to be unique
- [ ] This is known as a surrogate primary key
- [ ] It is often implemented as an incrementing integer
- [ ] I.e. the first entity’s primary key will be 1, the second entity’s primary key will be 2, etc.
- [ ] Refering back to single/multi valued attributes example with the office entity, the address attribute would seem to be sufficient to ensure uniqueness

Logical Data Model Relationships
- [ ] In the conceptual model, it is sufficient to say that two entities are related
- [ ] An example would be that an Office entity can be related to a Client entity
- [ ] In the logical model, with primary keys identified for all entities, this is not sufficient, and relationships are instead described in terms of the primary keys
- [ ] How the relationships are implemented using these primary keys will depend on what types of relationships we are looking at
- [ ] For each of these types of relationship, when building a logical data model, the important question: where does the foreign key go?
- [ ] A foreign key is an attribute that references a primary key attribute
- [ ] Another way of thinking about it would be a foreign key points from the current entity to another entity thus representing the relationship between them

Conceptual Model Relationship Cardinalities
- [ ] There are two types of relationship cardinalities that can be represented in a conceptual model
- [ ] One or many
- [ ] At this point we are ignoring optionality
- [ ] Since all relationships are bi-directional, this means there are three unique ways to combine relationship cardinalities
- [ ] Examples:
- [ ] One-to-one: a person may have a unique right-hand palm print
- [ ] One-to-many: a person has one date of birth but many people may be born on that date
- [ ] Many-to-many: many parents may have many children.

One-To-One Relationships
- [ ] One-to-one relationships are relatively uncommon
- [ ] In many cases, the entities that are related by such a relationship can be combined into a single entity
- [ ] When taking a one-to-one relationship from a conceptual data model into a logical data model, you should consider if one of the two entities must exist in the database for the other to exist
- [ ] For example we can think about a person and a palm print:
- [ ] If you are making a biometric database for employees, the employee’s record will exist before you store the palm print
- [ ] If you are making a database for police crime scene evidence, then it is possible the palm print will exist before you store any details about a suspect, and vice versa
- [ ] Note that context is key
- [ ] It is rare that you can make a rule that will apply to all possible contexts when modelling data

One-To-Many Relationships
- [ ] One-to-many relationships are one of the most used relationships in a relational model
- [ ] This is in part due to how we handle many-to-many relationships – but that’s coming next!
- [ ] They are also reversible (a many-to-one relationship is just a one-to- many relationship, looked at from the other direction)
- [ ] For a one-to-many relationship, the foreign key always goes on the entity on the many-side of the relationship
- [ ] Think it as each many entity having a sign pointing back to to the one entity that it is related to
- [ ] Why can’t the foreign key go on the one entity?
- [ ] Because it can be related to many other entities, which would mean that the foreign key would have to be a multi-valued attribute

Many-To-Many Relationships
- [ ] Many-to-many relationships are vital to represent – but the relational data model only supports representing them by adding a new entity. This is often known as an associative entity
- [ ] This associative entity sits between the two original entities, and each of them will have a one-to-many relationship to this associative entity
- [ ] Thus a many-to-many relationship is represented as two one-to-many relationships going to an additional associative entity
- [ ] If the relationship between these entities is unique, then the associative entity will often use the foreign keys of the two many entities as their primary key
- [ ] Remember, this is known as a composite primary key
- [ ] Yes, a foreign key can be part of a primary key!
- [ ] If a many-to-many associative entity doesn’t have an obvious name, it is common practice to use the names of the many-entities combined

Logical Data Modelling

Normalisation is a process for creating well-designed relational data models, it can be considered a bottom-up approach.

Given we’ve been focussing on the top-down approach, why bother learning about yet another method?

This process has two primary benefits:
- [ ] Given an existing un-normalised database, it allows a physical relational data model to be constructed from it
- [ ] The top-down approach is more focussed around starting from business requirements
- [ ] It is a set of rules that describe how a good relational database should be structured
- [ ] Understanding them will help improve your database designs, even if you only use the top-down approach going forward

Database Anomalies
- [ ] One of the main goals of normalisation is to avoid three types of anomalies that may be encountered when working with un-normalised databases
- [ ] Update anomaly
- [ ] When updating a value in the database, other values which should also be updated will not be changed
- [ ] Thus the aim should be that each piece of data should have a single entry in the database, alternatively, there is no duplication of data
- [ ] Insertion anomaly
- [ ] Where it is impossible to introduce new entities into the database without adding other unrelated entities
- [ ] Deletion anomaly
- [ ] Where the deletion of an entity may cause data about other entities to be lost

Naming Conventions
- [ ] In the first three lessons, we’ve talked about entities and attributes
- [ ] This is the standard naming convention in conceptual and logical data models
- [ ] Since normalisation is often used from the starting position of already having a tabular database, we’re going to switch to the terminology of tables, columns and rows
- [ ] A table is an entity
- [ ] A column is an attribute for an entity
- [ ] A row is an instance of an entity
- [ ] The keys terminology will remain unchanged
- [ ] We still have primary and foreign keys, as well as surrogate and composite primary key


NULL, Candidate Keys And Dependencies
- [ ] When it comes to defining the different stages of normalisation, there are three concepts that are vital to understand
- [ ] They underlie most of decisions that have to be made during the normalisation process
- [ ] NULL -  In relational databases, the absence of data is represented by NULL
- [ ] Candidate Keys -  All of the groups of columns that can uniquely identify a row(I.e. any of these groups could be selected to be the entity’s primary key)
- [ ] Dependency - When the value of an column depends on the value of another column

NULL
- [ ] The NULL value is a special value in databases, used to represent the absence of data
- [ ] Has some special rules that govern how it behaves
- [ ] Any comparison involving NULL will be false (equality, less than, greater than, etc.)
- [ ] If you use NULL in a calculation, the result will be NULL
- [ ] It is not the same as zero
- [ ] E.g. a zero is equal to zero
- [ ] In a physical data model, it is usual to explicitly state if a column is allowed to contain NULL values

Candidate Keys
- [ ] These are the smallest groups of column(s) that could be used to uniquely identify each row in a table
- [ ] If a group is made up of multiple columns in order to ensure uniqueness, then this group would be a composite primary key
- [ ] If there are no possible groups of columns that uniquely identify the rows in a table, then you may need to introduce a surrogate primary key
- [ ] To be used to identify a row, a column must never be NULL
- [ ] Because one NULL is never equal another NULL
- [ ] Thus no group in the candidate keys can contain any columns which may be NULL
- [ ] The smallest groups are those that require every column they contain in order to be unique
- [ ] If only some of the columns are required, it is not the smallest group

Dependency
- [ ] A column is said to be dependant on another column if you can know the former if you know the latter
- [ ] This might be because there is some explicit mathematical relationship between the two columns
- [ ] Such as from date of birth to age
- [ ] Such dependant columns can often be removed, since they are redundant as long as the relationship is known
- [ ] Or because the two are intrinsically linked
- [ ] In the logical data model, we described it as the attribute belonging to the entity
- [ ] These columns are often what the database is intended to store, so will not be removed

Un-Normalised Database
- [ ] An un-normalised database has two properties:
- [ ] A primary key may not have been identified for the table
- [ ] It may contain multi-valued columns
- [ ] Such databases are often represented as a single table
- [ ] Since they can contain arbitrarily nested data, there’s no requirement for multiple tables

The Three Normal Forms
- [ ] When talking about normalisation it is common to refer to the three normal forms
- [ ] There are additional normal forms, but these are not widely used in practice
- [ ] They were proposed by Edgar Codd when he originally described relational databases (Codd, 1970)
- [ ] They were explicitly designed for translating existing paper databases into relational databases
- [ ] These paper databases were often un-normalised

First Normal Form
- [ ] The definition of a first normal form database is:
- [ ] Every table has a primary key
- [ ] No column contains multiple values
- [ ] How should you transform an un-normalised database into a first normal form database?
- [ ] First, identify a primary key for every table
- [ ] If there aren’t any suitable candidate keys, you can always add a surrogate primary key to that table
- [ ] If there are any multi-valued columns, you must create a new table that will hold the related multi-valued columns
- [ ] You may need to create multiple tables if there are multiple multi- valued columns that are not dependent on each other

Second Normal Form
- [ ] The definition of a second normal form database is:
- [ ] A database that is in first normal form
- [ ] Important! You cannot skip a normal form
- [ ] Where every non-prime column is dependent upon the whole of every candidate key
- [ ] See slide 20 if you need a refresher on what a non-prime column is
- [ ] If you only have candidate keys that are single columns, and you are in first normal form, then you are also in second normal form
- [ ] Second normal form is only relevant if you have any candidate keys that contain two or more columns

Third Normal Form
- [ ] The final normal form we will consider is the third normal form:
- [ ] A database that is in second normal form
- [ ] Again, no skipping any steps!
- [ ] Where every non-prime column is dependent upon only the candidate keys
- [ ] There is a difference between this and the second condition for second normal form, but I will admit that the difference is subtle
- [ ] In this case, we are eliminating what is known as transitive dependencies
- [ ] If column A depends on column B, and column B depends on column C, column A is described as being transitively dependent on column C

Normalisation

Data Modelling

To start, we need to install a Relational Database Management System(RDBMS) to support the practical aspects of this and the remainder of lessons. The recommendation is that you use Microsoft SQL Server Management Studio(SSMS) as all the resources have been developed on that database.

Although SQL is fairly standard you will soon discover that some commands differ between database applications. At an introductory level, and in terms of the content of this module, these differences will be minimal.

To download Microsoft SQL Server Management Studio you can visit: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16#download-ssms

What is SQL?

Structured Query Language (SQL) is a programming language specifically designed to communicate with databases. It is an industry wide standard used by most database systems.

SQL can be used in various ways to access and perform actions on the database and data: 
- [ ] Interactive SQL – Direct Input using the SQL Client application of DBMS package such as MySQL Workbench. SQL statements are entered within the application, executed and results output without having to write an application in any sort of structured language. 
- [ ] Static SQL – The developer embeds SQL that runs repeatedly in a host language. There are several options to do this: 
- [ ] Wrap the SQL inside keywords like EXEC SQL and END EXEC –For example COBOL. 
- [ ] Pass SQL statements as arguments to functions to communicate with the database via intermediate software driver or Application Programming Interface (API) such as: 
- [ ] Open Database Connectivity (ODBC) for Windows. 
- [ ] Java Database Connectivity (JDBC) for Java. 
- [ ] Use an Integrated Development Environment (IDE). 
- [ ] Dynamic SQL – The developer writes code to create SQL statements whose content cannot be predicted at development time. Examples are script or programs on web servers that dynamically communicate with the database using languages such as PERL or PHP.


SQL Commands or scripts are known as queries
- [ ] Small set of keywords used to access and modify data on database
- [ ] Simple to understand
- [ ] Powerful to use
- [ ] Basic components combined to enable complex database operations to be performed

SQL Commands per category

- [ ] Data Definition Language (DDL) - CREATE / ALTER / DROP
- [ ] Data Manipulation Language (DML) - INSERT / UPDATE / DELETE
- [ ] Data Query Language (DQL) - SELECT
- [ ] Data Control Language (DCL) - GRANT / REVOKE
- [ ] Data Administration Commands - START AUDIT / STOP AUDIT
- [ ] Transactional Control Commands - COMMIT / ROLLBACK / SAVE POINT / SET TRANSACTION

By now you should have successfully installed and opened Microsoft SSMS. You will first see a screen similar to the image for this lesson, click connect. 

SQL Basics and Installing our RDBMS

This module will cover creation of our database and populating it with data.

To begin, first download the lesson files from the public git repository for this course, found here: https://github.com/itzDanny96/Worked.Dev_DataModelling_DatabaseDesign_Course/tree/main/Creating%20our%20Database.

Once you have installed and opened Microsoft SQL Server Management Studio(SSMS) we can open the two files we just downloaded.

In SSMS go to File > Open > File > open the “SQL Basics Sample Data (Database Creation)” that you just downloaded.

Whilst you have the database creation file open, press the execute button or F5 to run the commands.

This is the first part of the SQL to add the workingdeadstore database. The USE command specifies the use of the master database to setup the database required. You should be in master to start but if not just highlight USE master and execute(press F5) to select the master database. The DROP command will delete the database if it already exists as it is not possible to have two databases with the same name. This is useful if you have made an error. It is often easier to DROP and start again than amending or correcting what you already have. Note to DROP the workingdeadstore database you need to be in the master database so may need to execute USE master first. To drop the workingdeadstore database highlight “DROP DATABASE workingdeadstore” and execute.

The CREATE command will add a new database. Highlight “CREATE DATABASE workingdeadstore” and execute to create the workingdeadstore database.

The USE command will the specify that this is the database we want to use for subsequent SQL. To select the workingdeadstore database highlight “USE workingdeadstore” and execute.

Next, the SQL adds the first table for the workingdeadstore database. When creating table you need to start with the Entities on the outside of the Logical Data Model and work inwards. This is because you can’t specific the FOREIGN KEY constraint on a child table unless the parent table has already created. In our example we need to CREATE company before we can CREATE the contact table. The CREATE TABLE command specifies the name of the table and the columns or fields that make up the table. Each column is given a name or label, the type of data stored in the column and whether it is mandatory (NOT NULL) or optional (NULL) for data to entered in the column when a
record is created. The next slide outlines the data types that can be stored in a column. The definition should align with that specified and agreed in the Logical Data Model. Finally the PRIMARY KEY constraint specifies the column or columns that will make the Primary Key or unique identifier for each record on the table.

Now we have created our database and its tables we can start adding some Data. Go to File > Open > File > open “SQL Basics Sample Data (Database Population)”

The second file includes the SQL which adds the data or records for each table. INSERT will add a new record to a table. 

First, it is required to specify the table that new record will be added to. It is recommended that you then specify the columns or fields that data will be added for. As we will see soon this is optional. This could be a subset of the fields on the table but must include any mandatory or not-null fields. If not the SQL will fail when it is executed. The fields do not need to be in the order they are specified on the table.

Next we specify the actual values that will be added for each field. These must be in the same order as the fields have been specified and the format and size of data appropriate to the field definition on the table. For example numbers as integers or decimals without quotes and text in quotes. The default colours in the ‘SQL Query Panel’ indicate the type of data. Again the SQL will fail when it is executed if the data is not specified correctly 

In our file we submit all record for each table at once, while we are using a small database for this course tt is possible to INSERT up to 1000 records in one INSERT statement by separating each set of new record values by a comma.

Finally the SQL checks the records added to each table. The SELECT command retrieves the records from the specified table. The “*” is shorthand or a wildcard for all the columns in the table.

Creating our Database

This unit will enable the learner to write SQL to display data from a single table.

Specifically the learner will be able to:
- [ ] Create a query using the SELECT statement.
- [ ] Filter the results of a query using the WHERE clause.
- [ ] Sort the results of a query using the ORDER BY clause 

The next section covers the basics of a simple query using the SELECT statement 

The simplest form of the SELECT statement is: SELECT * FROM table
A simple SELECT like this will query and display data from every column in the table mentioned in the FROM clause. The ‘*’ signifies that you want to display data from all of the columns in the table. 

The SELECT statement can be extended by replacing the * with a comma- separated list of column names. Only these columns are then displayed horizontally in the order specified. One problem with this is that you need to know the names of the columns in the table. The * syntax avoids this, but for large tables it can result in vast amounts of data being displayed. It is possible to repeat a column in a query. The benefit of which will become more apparent later in the unit 

If you misspell a column name or include a column name from another table an unknown column error message will be generated when you execute the SQL. 

A ‘virtual’ column can be created by manipulating the existing columns using an expression. In the above example we have created a ‘virtual’ column by multiplying the current sales target by 1.2. The result is displayed like a column from the table. Note that this column only exists during the life of the query; as soon as the query is complete the column disappears. This means that you can display ‘virtual’ columns but not insert or update values within them.

Note it is possible to create expressions using other arithmetic operations such as +, -, *, / and many more.

This column header or alias for the ‘virtual’ column is specified using the AS syntax. Most database manufacturers support column aliasing, but nearly all omit the use of the AS keyword as it is optional in SQL. This can lead to subtle bugs occurring in statements if a comma is accidentally omitted. For example, the statement below is syntactically correct but probably not what the author intended; it will display only the first name of each salesperson, but with the column heading ‘lname’: 

SELECT fname lname FROM salesperson 

Note that the default column header will be the column name for non- expression columns. This header may be also be renamed using the AS syntax shown above 

The BODMAS rule is followed for all expressions to determine the order they are evaluated. Brackets are calculated first, then order of or powers, division, multiplication, addition and lastly subtraction. For example the expression:
- [ ] 10 * 5 + 8 / 4
Would be resolved in the following order:
- [ ] 8 divided by 4 = 2
- [ ] 10 multiplied by 5 = 50
- [ ] Add the two together, 50 + 2 = 52

If BODMAS does not determine an exact order the remaining elements are calculated from left to right. For example:
- [ ] 6 – 3 – 2 = 1
And not:
- [ ] 6 – (3 – 2) = 6 – 1 = 5.

Using brackets or parentheses is encouraged to make the order of expressions clear. In our example code the expression as:
- [ ] (10 * 5) + (8 / 4) 

It is possible to ‘add’ text fields but the result makes no sense. Alternatively the CONCAT function can be used to combine multiple columns and text strings to create a ‘virtual’ column

SQL provides a standard set of scalar functions of which we will see more of in the course. 

SUBSTRING is an example of a function that can be used to display a section of a column by specifying the starting position and number of characters required.

Whilst SUBSTRING is typically used on text columns it can be used with other data types such as numeric and date.

COALESCE is used to display the first non-NULL value in the list specified. In this example ‘No notes entered’ will be displayed if the notes column is a NULL value. This will make the output clearer to a user rather than just displaying the value NULL. 

The default for SELECT is to display all the rows specified. The modifying clause, DISTINCT, can be used the eliminate duplicate rows and return one copy of each.

Typically to eliminate duplicates the database engine must do a sort or use other techniques of removing duplicates that do not require a sort.

The elimination occurs across all the data. As above the SQL to just display a list of unique departments that employ a salesperson is: 

SELECT DISTINCT dept_no FROM salesperson

DISTINCT also works on multiple columns. To display unique combinations of departments and counties the SQL is:

SELECT DISTINCT dept_no, county FROM salesperson

The DISTINCT keyword appears only once and applies to the entire set of columns in the list 


The next section covers how to sort the results from a simple query using the ORDER BY clause 

As noted previously the SQL standard does not specify the order in which results are displayed. This is implementation dependent and is typically driven by the order the data is physically saved on the file.

You can specify a sort order by using the ORDER BY clause. The column can be specified by its name or ordinal position in the column list. You may specify several columns and have the data sorted in ascending or descending order. An ascending sort can be specified by the option ASC after the sort column. However ascending is the default option and does not need to be specified.

If more than one column is specified in the ORDER BY clause the first column is the major sort and subsequent columns the minor sorts in the specified order.

In our example the data would be sorted by company_no first and then by contact_code for records with the same company_no. Swopping the columns the sort would be by contact_code first and then company_no.

The DESC option is used to sort records in descending order. This is placed after the corresponding sort column. As noted previous if this is omitted the default sort order is ascending.

As with the previous example swopping the sort columns will order the data by contact_code first and company_no second.

It is possible to sort by combinations of ascending and descending
columns if required.

For example to display salespeople firstly by ascending department_no and then in descending order of salary from biggest to smallest the SQL is.

SELECT * FROM salesperson ORDER BY department_no ASC, salary DESC

If a ‘virtual’ column and alias has been specified in the SELECT clause it is possible to sort the data using that alias name

The next section covers how to filter the results of a simple query using the WHERE clause 

A SELECT will display all the records on a table unless a WHERE clause is used to filter the selection. The WHERE clause is similar to an ‘IF’ statement in other programming languages. For example “Select this row IF the boolean_expression or condition is true”. The WHERE clause is used to limit the rows to just those that meet the required criteria. 

There are six basic operators that may be used in a WHERE clause to compare values and to select that rows that meet the specified condition:
- [ ] > - Greater Than
- [ ] < - Less Than
- [ ] >= - Greater Than or Equal To
- [ ] <= - Less Than or Equal To
- [ ] = - Equal
- [ ] <> - Not Equal

The values tested may be columns, constants or expressions. In addition to these basic tests we can perform more complex checks as we will seein subsequent examples.

The boolean_expression or condition may contain a function for example:

SELECT * FROM salesperson WHERE SUBSTRING(lname,1,1) = ‘P’

This will select all salespersons whose surname starts with a “P”.

Note that it is possible to sort the filtered data by using an ORDER BY clause after the WHERE clause. 

Multiple conditions can be specified in the WHERE clause. These are linked using the logical operators: AND, OR and NOT. Generally when you join conditions with an AND this will result in less rows qualifying for selection. You are saying “This must be true and also that must be true” for the record to be selected.

Alternatively when you join conditions using an OR this will result in more rows qualifying for selection. You are saying “Either this may be true or that may be true or both” for the record to be selected.

The NOT operator will select the records that do not meet the specified condition.

You can link as many conditions as you like. Care must be taken when combining conditions that they select what is actually required. Similar to the BODMAS rule there is a precedence of operators: ANDs will be evaluated first before ORs. Good practice is to use brackets to specify and clearly illustrate the logic required.

The BETWEEN option is used to specify a range of values between, and including, two limits.

The first value sets the start of the range; the second value sets the end.

Note that the AND keyword between the two values is mandatory. 

Also that the SQL Standard expects the lower end of the range to be specified first, otherwise no rows will be returned.

The SQL could be coded as follows:

SELECT * FROM salesperson WHERE salestarget >= 7 AND salestarget <=12;
or
SELECT * FROM salesperson WHERE salestarget BETWEEN 7 AND 12;

BETWEEN is a shorter and neater way of coding the logic and ensure that that range values are included in the selection if that is what is required.

There may be a requirement to only display a few of the possible rows returned, especially with a sorted query.

The TOP option enables us to answer questions like “what are our 10 most expensive products?” We can sort our results by price in a descending order and then use TOP to select to just the top 10 results.

If there are several records that match the last value selected by the TOP option they can all be displayed by adding the WITH TIES. For example if we have several product that match the tenth most expensive price then all will be displayed.

Also there is the TOP n PERCENT option that will display the n% of records for the given selection and sorted order.

A NULL value is stored where no data has been entered for given column on a record. It represents an unknown value and is different from a zero in a numeric field and a blank in a text field.

A NULL will propagate through an expression. If you add a value to a NULL value the answer will still be NULL. 

To select a NULL value the IS NULL option is used in the WHERE clause. Likewise IS NOT NULL is used to select values that are not-NULL. If the WHERE clause specifies a condition to select records that are not equal to a specified value it will only display the non-NULL values that are not equal. To also select the NULL values will require an extra condition as follows:

SELECT * FROM salesperson WHERE notes <> ‘Joined in Sept 1996’ OR notes IS NULL;

This is often referred to as 3-way logic. 

You can now continue on with the next lesson, or if you'd like to test what you have learned try out the excercises found here: https://github.com/itzDanny96/Worked.Dev_DataModelling_DatabaseDesign_Course/tree/main/Exercises/Displaying%20Data%20Exercises

Displaying Data

Functions

This unit will enable the learner to write SQL that makes use of common functions.

Specifically the learner will be able to:
- [ ] Create queries that use text, date, time, conversion and rounding functions.
- [ ] Create queries that use functions to handle NULL values.
- [ ] Create a query that uses nested functions.

Code examples will be used to explain and demonstrate the content and for leaners to imitate. Supporting exercises will provide practice opportunities. At the end of the unit there is a short quiz to review the topic.

The next section covers text functions:
Functions are stored sets of SQL statements that return a result.

Most functions are referred to as “scalar” functions and return a single value but some functions, known as “table-valued functions” return tables of data and work like parameterised views. This unit focuses on scalar functions. Table-Value Functions are beyond the scope of this course.

Functions usually require parameters to be passed to them, which normally include the name of the column that we want to perform the calculation against and possibly some other information.

We can use them anywhere that SQL is expecting an expression, that is to say in SELECT lists, WHERE clauses, ORDER BY clauses, etc.

Unlike Stored Procedures, we don’t need to tell SQL to EXECUTE a function but SQL does need to be able to work out that we are calling one, so the function’s name is always followed by an open parenthesis “(“, then the parameters we’re passing, then a close parenthesis “)”, even if we don’t have any parameters to pass to it. The brackets are not optional.

The following few pages list and discuss some of the more commonly- used functions. There are many, many more available in SQL. Some are standard to all SQL products; others are specific to particular vendors.

There are a number of text functions that manipulate the data in a specified column; some of the which require additional parameters:
- [ ] LEFT(column,n) will display the first n characters of the specified column.
- [ ] RIGHT(column,n) will display the last n characters of the specified column
- [ ] SUBSTRING(column,s,n) will display n characters of the specified column starting from positions.
- [ ] UPPER(column) will display the specified column as upper case.
- [ ] LOWER(column) will display the specified column as upper case.

Other text functions can be used to search for a given string within the data for a specified column:
- [ ] CHARINDEX(column,’string’) will display the position of the first character of the string if the string is found in the specified column or a 0 if it is not found.

The next section covers date / time functions.

There are numerous Date / Time functions. Some manipulate and display elements of date / time data values, others provide the current date and time and a final group do various date / time calculations.

There are a number of date functions that manipulate the data in a specified date / time column:
- [ ] YEAR(date_column) will display the year of the date.
- [ ] DATEPART(qq,date_column) will display the quarter of the date.
- [ ] MONTH(date_column) will display the month of the date.
- [ ] DATEPART(ww,date_column) will display the week of the date.
- [ ] DAY(date_column) will display the day of the date.
- [ ] DATENAME(WEEKDAY,date_column) will display the name of the day of the date.
- [ ] DATEPART(dw,date_column) will display the number of day in the week of the date.

Likewise there are a number of time functions that manipulate the data in a specified date / time column:
- [ ] DATEPART(hh,time_column) will display the hour of the time.
- [ ] DATEPART(mi,time_column) will display the minute of the time.
- [ ] DATEPART(ss,time_column) will display the second of the time.

There are number of date / time functions that will provide the current date and time:
- [ ] GETDATE() will display the current date and time.
- [ ] SYSDATETIME() will display the time at which the function executes.
- [ ] GETUTCDATE() will display the current UTC date and time.

Note that these are examples of functions that have no column or parameters specified but still need the opening and closing brackets.

There are also date / time functions that do various date / time calculations:
- [ ] DATEADD(UNIT,n,date_column) will display the specified date plus n of the specified time unit.
- [ ] UNIT can be SECOND, MINUTE, HOUR, DAY, etc.
- [ ] For a full list of units see: https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql
- [ ] DATEDIFF(UNIT,date_column1,date_column2) will display the difference between the two specified dates.


The next section covers conversion functions.

Conversion functions change the data in a column from one type to another. This is often done to enable the changed data to be used in other functions that only work with data of that type:
- [ ] CAST(column AS TYPE) will convert the data in the column specified to the type specified.
- [ ] TYPE can be INT, FLOAT, DATE, TIME, etc.
- [ ] For a full list of types see: http://www.tutorialspoint.com/SQL Server/SQL Server-data-types.htm
- [ ] Note SIGNED converts a value to a SIGNED type, which is a signed 64-bit integer.
- [ ] CONVERT(column, TYPE) will convert the data in the column specified to the type specified.
- [ ] ROUND(column,n) will round the value in the column to n decimal places.
- [ ] As per normal rules of mathematics a 5 and above will round up and 0 to 5 stay the same.

The next section covers NULL functions.

NULL functions can be used to manipulate NULL values:
- [ ] ISNULL(column) will test whether a value is NULL displaying the value if it is populated or the specified ‘string’ if NULL.
- [ ] COALESCE(column1,column2,...,’String’) will display the value in the first column if it is non-NULL; if it is NULL It will check the second column if specified and display that value if it is non-NULL; 

This will continue for the number of columns specified. If all are NULL a final ‘string’ is displayed.

The next section covers nested functions.

Functions can be nested where the results of the inner most function are used as a parameter for the next outer level function. Care must be taken that functions are nested in the required order, the parameters that are being passed are the appropriate data type and format and that all the brackets balance.

You can now continue on with the next lesson, or if you'd like to test what you have learned try out some excercises found here: https://github.com/itzDanny96/Worked.Dev_DataModelling_DatabaseDesign_Course/tree/main/Exercises/Functions%20Exercises

Functions

This unit provides an introduction to maintaining data on a table both directly and using another table. 

The topics covered are
- [ ] Add a Record – Using the INSERT statement.
- [ ] Amend a Record – Using the UPDATE statement.
- [ ] Remove a Record – Using the DELETE statement. 

The next section covers adding a record using the INSERT statement.

Records can added to a table one at a time or in multiple batches using another table. The options covered are:
- [ ] Single Record
- [ ] Using / Not using Columns – Specifying or not the columns that data values will populate.
- [ ] Using NON NULL Columns – Handing mandatory and optional columns.
- [ ] Using a Function – Populating a column with a calculated value from a function.
- [ ] Multiple Records
- [ ] From another Table – Adding or copying records from another table.

INSERT will add a new record to a table.

First, it is required to specify the table that new record will be added to. It is recommended that you then specify the columns or fields that data will be added for. As we will see soon this is optional. This could be a subset of the fields on the table but must include any mandatory or not- null fields. If not the SQL will fail when it is executed. The fields do not need to be in the order they are specified on the table.

Next we specify the actual values that will be added for each field. These must be in the same order as the fields have been specified and the format and size of data appropriate to the field definition on the table. For example numbers as integers or decimals without quotes and text in quotes. The default colours in the ‘SQL Query Panel’ indicate the type of data. Again the SQL will fail when it is executed if the data is not specified correctly.

Values and intended fields can be accidentally swopped if they are of the same type. For example dept_name and manager are both VARCHAR(20) fields. It is down to the developer to make sure the order of fields and corresponding values align as the SQL will execute successfully in such circumstances and put the wrong values in the corresponding fields.

The record will be added automatically when the SQL is executed if the ‘Set Implicit Transactions’ option is off. This is the default option and can be set using SQL or by ticking the appropriate box in the Options Window. If the option is on additional SQL will be required to commit the change. This will be covered in more detail later in the unit.

A record can be added without specifying the fields on the table. The values will be added to the fields in the order they are specified on the table. Care is required to ensure the correct values are added to their corresponding fields. If insufficient values are specified the remaining fields will be populated with a ‘NULL’, default or calculated value as appropriate. If any remaining field is mandatory or non-null then the SQL will fail.


The next section covers amending a record using the UPDATE statement.

One, multiple or all records can amended on a table directly or my using another table. The options covered are:
- [ ] Single Record
- [ ] Single Column – Amending just one column on just one record on a table..
- [ ] Multiple Columns – Amending multiple columns on just onerecord on a table.
- [ ] Multiple Records – Amending many records on a table.
- [ ] All Records – Amending all records on a table.
- [ ] From another Table – Amending records based on data in another table.
 
Several fields can be amended in one UPDATE by listing the required fields and their new values separated by a comma.

A field can be amended by using a function. This could be independent of the field, or based on the current value of the field.

The next section covers removing a record using the DELETE statement

One, multiple or all records can removed from a table directly or my using another table. The options covered are:
- [ ] Single Record – Removing just one record from a table.
- [ ] Multiple Records – Removing many records from a table.
- [ ] All Records – Removing all the records from a table.
- [ ] From another Table – Removing records from a table based on data in another table.

DELETE will remove an existing record.

First, specify the table that the record will be removed from. Note there is no need to specify any fields as the whole record will be removed not just individual fields. A WHERE clause can be used to specify which records will be removed. 

Dependent on the WHERE clause multiple records can be removed. 



Maintaining Data

Database Design using SQL

The first half of the module covers Data Modelling:
• Introduction to Databases
• Data Modelling
• Conceptual Data Modelling
• Logical Data Modelling
• Normalisation

The second half of the module covers Database Design using a Relational
Database Management System (RDBMS) and Structured Query Language
(SQL):
• Introduction to RDBMS and SQL
• Displaying Data
• Functions
• Joins
• Summarised Queries
• Sub-Queries
• Maintaining Data
• Maintaining Database Objects

Data Modelling and Database Design

Course Details

Level:

Lessons:

Modules:

Ratings:

Public ADA Price:

Holder $WORK Price:

Public ADA Price:

Holder ADA Price: